文章目录

• 一、慢查询优化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;