学习篇-mysql-优化09-慢查询优化order by详解

一、慢查询优化order by详解

• order by 排序

  • order by排序可利用索引进行优化，order by子句中只要是索引的前导列都可以使索引生效，可以直接在索引中排序，不需要在额外的内存或者文件中排序。

  • 不能利用索引避免额外排序的情况，例如：排序字段中有多个索引，排序顺序和索引键顺序不一致（非前导列）

    • 查询字段需要在排序字段包含里包含
    • 排序字段的索引必须满足前导列
      • 比如复合索引idx_idc_name_age
      • order by后面的字段，可以是idc,name，可以是idc,age，但是不能是name,age 必须是以idc开头，最左匹配原则

  • mysql对于不能利用索引避免排序的SQL,数据库不得不自己实现排序功能以满足用户需求，此时SQL的执行计划中会出现“using filesort”,这里需要注意的是filesort并不意味着就是文件排序，其实也有可能是内存排序，这个主要有sort_buffer_size参数与结果集大小确定。mysql内部实现排序主要有3种方式，常规排序，优化排序和优先队列排序，主要涉及3种排序算法：快速排序、归并排序和堆排序

  • order by 常规排序算法

    • 步骤：

      1. 从表t1中获取满足where条件的记录

      2. 对于每条记录，将记录的主键+排序键（id,col2）取出放入sort buffer

      3. 如果sort buffer 可以存放所有满足条件的（id,col2）对，则进行排序；否则sort buffer满后，进行排序并固化到临时文件中。（排序算法采用的是快速排序算法）

      4. 若排序中产生了大量的临时文件，需要利用归并排序算法，保证临时文件中记录是有序的

      5. 循环执行上述过程，直到所有满足条件的记录全部参与排序

      6. 扫描排好序的（id,col2）对，并利用id去捞取select需要返回的列（col1,col2,col3）,这边就是两次IO，一次排序id,一次读取具体的列

      7. 将获取的结果集返回给用户

    • 演示

      • 表结构

        

      • 结果

        

    • 优化参数设置

      • sort_buffer_size默认256k

        show variables like '%sort_buffer_size%';
        

      • read_rnd_buffer_size默认256k

        show variables like '%read_rnd_buffer_size%';
        

  • order by 优化排序算法

    • 常规排序方式除了排序本身，还需要额外两次IO。
    • 优化的排序方式相对于常规排序，减少了第二次IO。主要区别在于，放入sort buffer不是（id,col2），而是（col1,col2,col3）。由于sort buffer中包含了查询需要的所有字段，因此排序完成后可以直接返回，无需二次捞数据。
    • 这种方式的代价在于，同样大小的sort buffer，能存放的（col1,co2,col3）数目要小于（id,col2）,如果sort buffer不够大，可能导致需要写临时文件，造成额外的IO。影响参数：max_length_for_sort_data低于4k的话，使用优化排序。

  • order by 优化队列排序算法

    • 5.6几之后的版本针对order by limit M,N 语句，在空间层面做了优化，加入了一种新的排序方式–优先队列，这种方式采用堆排序实现。堆排序算法特征正好可以解limit M,N 这类排序的问题，虽然仍然需要所有元素参与排序，但是只需要M+N个元组的sort buffer空间即可，对于M,N很小的场景，基本不会因为sort buffer 不够而导致需要临时文件进行归并排序的问题。对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素。

  • order by排序不一致的问题

    • mysql5.6发现分页出现了重复记录，5.7之后的版本查询正常

    • 原因分析及解决方案

      • 针对limit M,N的语句采用了优先队列，而优先队列采用堆实现，比如上述的例子order by idc limit 0,3 需要采用大小3的大顶堆；limit 3,3需要采用大小为6的大顶堆。由于idc为3的记录有3条，而堆排序是非稳定的（对于相同的key值，无法保证排序后与排序前的位置一致），所以导致分页重复的想象。为了避免这个问题，可以在排序中加唯一值，比如主键id，这样由于id是唯一的，确保参与排序的key值不相同。

  • order by排序是否能用上索引

    • 分别在查询字段、where条件、排序字段上做出各种可能的组合，主要就是看有无索引，索引在以上三个关注点上的生效情况

    • 一般情况，一个sql只能生效一个索引，如果where条件占用了索引，那么排序就没有索引

      • 表结构

      

      • 情景一：

      // where 条件中的主键占用，导致order by 无索引可用
      explain select idc,name from t3 where id>2 and id<10 order by idc,name,id
      

      

      • 情景二:

      // 去掉where 条件
      explain select idc,name from t2 order by idc,name,id;
      

      

      • 情景三：

      // where 条件没有索引
      explain select idc,name from t2 where name ='zhangsan' order by idc,name,id;
      

      

      • 情景四：

      // 添加无索引查询字段
      explain select idc,name,nickname from t2 where name ='zhangsan' order by idc,name,id;
      

      

      • 情景五：

      // order by 去掉前导列
      explain select idc,name from t2 where name ='zhangsan' order by name,id;