MySQL-进阶篇 ( SQL 优化：插入 + 主键 + order by + group by + limit + count + update )

MySQL-进阶篇 ( SQL 优化 )

目录

• MySQL-进阶篇 ( SQL 优化 )
  • SQL 优化
    • 插入数据 index
      • 批量插入
      • 手动提交事务
      • 主键插入
      • 大批量插入数据
    • 主键优化
      • 页分裂
      • 页合并
      • 主键设计原则
    • order by 优化
      • Using filesort：
      • Using index：
      • 优化注意：
    • group by 优化
      • 未创建索引时：
      • 创建索引后：
      • 优化
    • limit 优化
    • count 优化
      • 一般的 count 语句
      • count 用法
      • 优化
      • 按效率排序
    • update 优化
      • 需要规避注意的问题

SQL 优化

插入数据 index

批量插入

• 一般情况下，都用批量插入写法

• 一个 index 插入多条： Insert into 某表名 values(1, 'xx'),(2, 'yy'),(3, 'xy');

  • 每一个 insert 都要与数据库连接连接、传输，所以不使用一个 insert 插入一条的语句
  • 虽此方式可以插入多条，但也不建议一个 insert 插入过千条

手动提交事务

• 数据超千条、达万条的话，使用此方式

• 在多条 insert 前开启事务，插入语句都编写完成后再统一提交：

  start transaction;

  insert ...;

  insert ...;

  ......;

  commit;

主键插入

• 不管数据量，在插入时都推荐使用顺序插入

• 主键乱序插入：3 5 9 1 6 2

• 主键顺序插入：1 2 3 5 6 9

  • 顺序性能更高

大批量插入数据

• 如果一次性需要插入大批量数据，使用 insert 语句插入性能较低，此时可以使用 MySQL 数据库提供的 load 指令进行插入：

  -- 客户端连接服务端时，加上参数：--local-infile
  mysql --local-infile -u root -p 
  -- 使用load加载的话就要加上：--local-infile为当前客户端连接服务端时需要加载本地文件。-u指定用户，-p指定密码
  
  -- 查看开关是否开启
  select @@local_infile;
  
  -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
  set global local_infile = 1;
  
  -- 执行load指令将准备好的数据加载到表结构中
  load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
  -- 这里设置加载的文件路径为 '/root/sql1.log'，加载到 tb_user 表中，字段间 ',' 分隔开，每条数据换行符 '\n' 隔开
  

  • 通过 load 指令可以一次性把本地文件数据全部加载到数据库
  • 本地文件非 SQL 语句编写，而是以一定规则写的每条数据内容

主键优化

• 在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的 ( 聚集索引 )，这种存储方式的表称为索引组织表 ( index organized table IOT )
  • 在聚集索引中，数据都是存放在页中

页分裂

• 页可以为空，也可以填充一半，也可以填充 100%。每个页包含了 2-N 行数据 ( 如果一行数据就相当于一个链表了，而且数据较大超出阈值就会导致行溢出 )，根据主键排列
• 主键顺序插入的话，就是先在第一个页中填写数据，如果第一个页满了就写第二个页中依此类推
• 主键乱序插入的话，就不是依此类推的往后插入，因为叶子节点是有序的，所以就产生了页分裂现象，举例说明：
  • 假设一页存 5 数据，现已有 page 1 ( 1、5、9、23、47 ) ——> page 2 ( 55、67、89、100、101 )
  • 此时乱序要插入 50 的话，因为本是要插入到第一个数据页 47 之后，而现有页已满，所以会开辟一个新的数据页 3，然后把第一个数据页的 50% 处的后面部分移动放到新的数据页中，然后将数据 50 填到新页的 47 之后，然后改链将此页放到页 1 后，页 2 前
  • 即：page 1 ( 1、5、9 ) ——> page 3 ( 23、47、50 ) ——> page 2 ( 55、67、89、100、101 )
  • 此现象即为页分裂现象

页合并

• 在删除一行记录时，实际上记录并没有被物理删除，只是记录被标记 ( flaged ) 为删除并且它的空间变得允许被其他记录声明使用
• 当页中被标记为删除的记录达到 MERGE_THRESHOLD ( 默认为页的 50% )，InnoDB 会开始寻找最靠近的页 ( 前或后 ) 看看是否可以将两个页合并以优化空间使用
  • MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定

主键设计原则

• 满足业务需求的情况下，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
• 尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号
• 业务操作时，避免对主键的修改

order by 优化

Using filesort：

通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序

• 没有 age 的索引，或者联合失效没用到
• explain select xx ...... order by xx; 可见执行计划的 Extra 列值为 Using filesort

Using index：

通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

• 若是创建一个 ( age, phone ) 的联合索引，再查询根据 age, phone 升序 ( 或降序 )
• explain 看执行计划的 Extra 列值为 Backward index scan; Using index
  • Backward index scan：反向扫描索引
• 若是查询时排序语句为 ...... order by phone, age; 则 Extra 列值为 Using index; Using filesort
  • 注意：where 后不管先后顺序，存在即可。但 order by 后顺序必须和索引创建时一致才符合最左前缀法则
• 若是查询时排序语句为 ...... order by age asc, phone desc; 则 Extra 列值也为 Using index; Using filesort，因为默认是都升序，所以在 age 时直接用，age 相同的要 phone 另拿出降序，所以 filesort
• 想要一个升序一个降序、还想用到索引的话，就要在创建索引时指定好：create index idx_user_age_pho_ad on tb_user(age asc, phone desc);
  • show 索引看到此联合索引的 Collation 列 A 为顺序，D 为降序
• 一旦建立了一升一降的索引，在查询时不管降序的是 age 还是 phone，只要两不相同就是用到索引了的 Using index
  • 此时索引的叶子节点就是 age 升序，age 相同的再 phone 降序填入，所以只要 age 与 phone 不同序就可以用扫描和反向扫描解决
• 如果查询时 select * ...... 就不会用到索引，Extra 列值为 Using filesort
  • 没有用到覆盖索引

优化注意：

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则
• 如果不可避免出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size ( 默认 256k )
  • show variables like 'sort_buffer_size'; 可得 value 值为 262144 ( 默认 262144 / 1024 = 256k )
  • 不更改的话，如果超出了默认就会在磁盘文件中进行排序，这样性能会较低

group by 优化

未创建索引时：

• explain select profession, count(*) from tb_user group by profession; 见执行计划中 key 是空 NULL，Extra 列为 Using temporary：使用临时表 ( 性能低 )

创建索引后：

• 创建索引：...... (profession, age, status);

• explain select profession, count(*) from tb_user group by profession; 见执行计划中 key 用到了索引，Extra 列为 Using index

• 执行语句 explain select age, count(*) from tb_user group by age; 的话，虽用到了索引，但是 Extra 却为 Using index; Using temporary ( 性能并不高 )

  • 显示使用了索引，却还是临时表 temporary，是因为只有 group by 后的 age 不满足最左前缀法则
  • 若是先 profession 筛选了的话，执行语句 explain select age, count(*) from tb_user where profession = '软件工程' group by age; 就会发现 Extra 就是 Using index，因为 where 后有 profession 和 age 符合最左前缀法则
    • 即 where 后面对于最左前缀法则的要求是存在即可，不管顺序位置

• 执行语句 explain select profession, age, count(*) from tb_user group by profession, age; 执行计划中 key 用到了索引，Extra 列为 Using index

  • 此时就不用考虑 select * 的可能了，因为没有意义

优化

• 在分组操作时，可以通过索引来提高效率
• 分组操作时，索引的使用也是要满足最左前缀法则的

limit 优化

• 大数据情况下，limit x, y 中，y 相同的情况下 x 越大耗费时间越长：例如 limit 2000000, 10，此时需要 MySQL 排序前 2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大

• 若是先查询主键 id 而非全部记录的话：select id from tb_user order by id limit 2000000, 10;，此时就获取了要返回信息的主键 id

  • 因为 SQL 语句 in 的后面不能有 limit 所以不能用 where id in ( 查 id 的 limit 语句 )
  • 但是可以把 limit 获取的 10 条 id 当作一个表，即多表联查：select * from tb_user u, (select id from tb_user order by id limit 2000000, 10) i where u.id = i.id;
  • 这个方法执行查询要比起初方法节省不少时间

• 优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

count 优化

一般的 count 语句

• 大数据情况下，查询总数比较费时，实际这是取决于 InnoDB 的处理方式
  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高 ( 有条件的话也是比较慢的 )
  • InnoDB 引擎就比较麻烦，执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累积计数
  • 优化思路：自己计数

count 用法

• count() 是一个聚集函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加一，否则不加，最后返回累计值
• 用法有：count(*)、count( 主键 )、count( 字段 )、count(1)
  • count( 字段 ) 的话就是字段非 null 就加一
    • 是 null 就不计数
  • count(1)：每返回一条记录就放一个 1 进去，在服务层对数据进行累加，是 1 非 null 就相加

优化

• count ( 主键 )：InnoDB 引擎会遍历整张表，把每行的主键 id 值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加 ( 主键不可能为空 null 所以不用判断 )
• count ( 字段 )：根据有无约束，有两种情况
  • 没有 not null 约束的话，InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null，计数累加
  • 若有 not null 约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
• count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加
  • count(0) 就是每一行放一个 0 进去，count(-1) 就是每一行放一个 -1 进去，只要不是 null 都会进行累加
• count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按效率排序

• count( 字段 ) < count( 主键 ) < count(1) < count(*)

• 所以尽量使用 count(*)

update 优化

需要规避注意的问题

• 若是加以行锁，在客户端 1 里 begin; 开启事务，然后 update 语句更改 id 为 1 的数据，不加以提交，在客户端 2 里也同上 begin; 开启事务，然后紧接着 update 语句更改 id 为 4 的数据，因为是行锁，所以此处语句执行成功，不用等待 1 的事务提交
• 同上条件，在客户端 1 里 begin; 开启事务，并 update 语句更改 name 为 xx 的数据，不加以提交，然后在客户端 2 里开启事务后，同先前用 update 语句更改 id 不同的 数据的 name 值，此时语句就阻塞无法执行了，除非客户端 1 commit; 释放了，客户端 2 的更新语句才能够执行成功
  • 因为客户端 1 update 语句中的 name 没有索引 ( 行锁只针对于有索引的主键 id )，所以此处就不是行锁了，是表锁，将整张表都锁住了，所以客户端 2 就算更新不同 id 的数据也会阻塞
  • 若是针对 name 创建一个索引的话，客户端 2 的语句就能够执行成功了
• InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则行锁就会升级为表锁，并发性能就会降低