MySQL索引优化实战总结

MySQL索引优化实战总结

分页查询优化

问题: 当对数据量较大的表进行分页查询时，哪怕只是查询少量的数据，MySQL也会把前面所有的数据全部查询出来，然后截取想要的数据，导致查询的效率较低。

• 优化方案：
  • 根据自增并且连续的主键进行排序的分页查询
    • 解决方案：select * from employees where id > 90000 limit 5;
    • 主要优化点：只查询满足条件的数据，多余数据过滤掉
    • 存在问题：当数据有删除操作导致数据不在连续时，获取的数据结果回和limit 90000,5 结果不一致
  • 根据非主键的字段进行排序的分页字段
    • 解决方案：让排序时返回的字段尽可能少，所以排序查询只返回主键，然后根据主键进行关联查询出所需所有字段；实际可以理解为排序字段一般都会添加索引，索引分页查询
      时先进行覆盖索引查询提高效率，然后关联查询出所需字段。
      select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
    • 主要优化点：原来排序使用的是using filesort，优化后使用的是索引排序

join关联查询优化

• 基础定义，什么是驱动表，什么是被驱动表？
  • 驱动表：执行器先执行的表就是驱动表
  • 被驱动表：根据关联字段在另一张表中取出满足条件的行，另外一张表就是被驱动表
  • 一般使用小表做驱动表，大表为被驱动表
• join关联查询常用两种算法
  • 嵌套循环连接算法（NLJ）
    • 使用场景：被驱动表的关联字段存在索引
    • 使用流程：
      • 从驱动表中选择一行数据（如果驱动表存在过滤条件，则先进行数据过滤，然后取出一行数据）
      • 从上一步的数据中取出关联字段，到被驱动表中进行查找
      • 在被驱动表中获取到所需要的数据，和驱动表取得数据进行合并，作为结果返回客户端
      • 重复上面三步直到获取完驱动表的所有数据
      • 总结：假如驱动表（100）和被驱动表（1000）条数据，则总共扫描数据100+100行
  • 基于块的嵌套循环连接算法（BLJ）
    • 使用场景：被驱动表的关联字段不存在索引，该情况不适合使用NLJ算法，因为NLJ算法没有索引的情况下扫描行数大概为100+100*100，并且为磁盘扫描效率极低。
    • 使用流程：
      • 把驱动表的所有数据取出来，放到join_buffer里面
      • 把被驱动表中的每一条数据取出来，跟join_buffer中的数据进行
      • 返回满足条件的数据
      • 总结：假如驱动表（100）和被驱动表（1000）条数据，总共扫描100+1000行数据并且进行了100*1000次内存运算。
  • 关联查询优化原则
    • 关联字段尽量添加索引：尽量使用NLJ算法进行查询
    • 小表驱动大表: 因为NLJ算法中，驱动表需要把所有数据查询出来,被驱动表会走索引查询,因此驱动表的数据量尽量使用小表；在BLJ算法中如果驱动表数据过大无法一次
      放入join_buffer中,则需要分段放到join_buffer中，但是这种情况会导致被驱动表重复遍历多次，因此驱动表最好选择小表。

in和exists优化

• 原则：小表驱动大表，即小的数据集驱动大的数据集
• in和exists原理说明：
  • in：驱动表是in 后面跟的查询语句，先查询出in后面的结果，然后从结果集里面选择满足添加的数据;

  例如：select * from A where id in (select id from B) 驱动表是表B ，被驱动表是表A;所以当B表数据小于A表时，可以使用这种语句进行查询。

  • exists:驱动表是exists前面的查询语句，exists后面的条件只是用来过滤前面查询到的结果集;

  例如：select * from A where exists (select 1 from B where B.id = A.id) 驱动表是表A，被驱动表是表B，所以当表A数据小于表B数据时，可以使用这种语句进行查询。

count(*),count(1),count(字段),count(id)性能对比

• 字段有索引：count(*)~count(1)>count(字段)>count(id):字段有索引，索引是二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(id);
• 字段无索引：count(*)~count(1)>count(id)>count(字段):字段无索引，count(字段)无法命中索引，count(id)命中索引，所以count(id)>count(字段);
• count(1)和count(字段)执行过程相似，不过count(1)不需要取出字段进行统计，count(字段)需要取出字段，所以count(1)>count(字段);
• count(*)在MySQL里面进行了特殊优化，不需要取值，而是按行累加，效率很高；
• MySQL内部对count(id)进行了优化，count(id)不走主键索引而是使用的二级索引