偏向于业务的(MySQL)DBA或者业务的开发者来说，order by 排序是一个常见的业务功能，将结果根据指定的字段排序，满足前端展示的需求。然而排序操作也是经常出现慢查询排行榜的座上宾。本文将从原理和实际案例优化，order by 使用限制等几个方面来逐步了解order by 排序。
二 原理
在了解order by 排序的原理之前，强烈安利两篇关于排序算法的文章 《归并排序的实现》 《经典排序算法》。MySQL 支持两种排序算法，常规排序和优化，并且在MySQL 5.6版本中 针对order by limit M,N 做了特别的优化，这里列为第三种。
2.1 常规排序
a 从表t1中获取满足WHERE条件的记录
b 对于每条记录，将记录的主键+排序键(id,col2)取出放入sort buffer
c 如果sort buffer可以存放所有满足条件的(id,col2)对，则进行排序；否则sort buffer满后，进行排序并固化到临时文件中。(排序算法采用的是快速排序算法)
d 若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的
e 循环执行上述过程，直到所有满足条件的记录全部参与排序
f 扫描排好序的(id,col2)对，并利用id去捞取SELECT需要返回的列(col1,col2,col3)
g 将获取的结果集返回给用户。
从上述流程来看，是否使用文件排序主要看sort buffer是否能容下需要排序的(id,col2)对，这个buffer的大小由sort_buffer_size参数控制。此外一次排序需要两次IO，一次是捞(id,col2),第二次是捞(col1,col2,col3)，由于返回的结果集是按col2排序，因此id是乱序的，通过乱序的id去捞(col1,col2,col3)时会产生大量的随机IO。对于第二次MySQL本身一个优化，即在捞之前首先将id排序，并放入缓冲区，这个缓存区大小由参数read_rnd_buffer_size控制，然后有序去捞记录，将随机IO转为顺序IO。
2.2 优化排序
常规排序方式除了排序本身，还需要额外两次IO。优化的排序方式相对于常规排序，减少了第二次IO。主要区别在于，放入sort buffer不是(id,col2),而是(col1,col2,col3)。由于sort buffer中包含了查询需要的所有字段，因此排序完成后可以直接返回，无需二次捞数据。这种方式的代价在于，同样大小的sort buffer，能存放的(col1,col2,col3)数目要小于(id,col2)，如果sort buffer不够大，可能导致需要写临时文件，造成额外的IO。当然MySQL提供了参数max_length_for_sort_data，只有当排序元组小于max_length_for_sort_data时，才能利用优化排序方式，否则只能用常规排序方式。
2.3 优先队列排序
为了得到最终的排序结果，无论怎样，我们都需要将所有满足条件的记录进行排序才能返回。那么相对于优化排序方式，是否还有优化空间呢？5.6版本针对Order by limit M，N语句，在空间层面做了优化，加入了一种新的排序方式:优先队列，这种方式采用堆排序实现。堆排序算法特征正好可以解limit M，N 这类排序的问题，虽然仍然需要所有元素参与排序，但是只需要M+N个元组的sort buffer空间即可，对于M，N很小的场景，基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素。
三 优化
通过上面的原理分析，我们知道排序的本质是通过一定的算法(耗费cpu 运算,内存,临时文件IO)将结果集变成有序的结果集。如何优化呢？答案是分两个方面利用索引的有序性(MySQL的B+ 树索引是默认从小到大递增排序)减少排序,最好的方式是直接不排序。
-
create table t1(
-
id int not null primary key ,
-
key_part1 int(10) not null,
-
key_part2 varchar(10) not null default '',
-
key_part3
-
key idx_kp1_kp2(key_part1,key_part2,key_part4)，
-
key idx_kp3(id)
- ) engine=innodb default charset=utf8
-
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
-
SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
-
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
-
SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC;
-
SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
-
SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC;
- SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2
无法利用到索引排序的情况，其实我觉得这是本文的重点，对于广大开发同学而言，记住那种不能利用索引排序会更简单些。
-
1 最常见的情况 用来查找结果的索引(key2) 和 排序的索引(key1) 不一样,where a=x and b=y order by id;
-
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
-
2 排序字段在不同的索引中，无法使用索引排序
-
SELECT * FROM t1 ORDER BY key1,key2;
-
3 排序字段顺序与索引中列顺序不一致，无法使用索引排序，比如索引是 key idx_kp1_kp2(key_part1,key_part2)
-
SELECT * FROM t1 ORDER BY key_part2, key_part1;
-
4 order by中的升降序和索引中的默认升降不一致，无法使用索引排序
-
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
-
5 ey_part1是范围查询，key_part2无法使用索引排序
-
SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;
-
5 rder by和group by 字段列不一致
-
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 group by key_part4;
-
6 索引本身是无序存储的，比如hash 索引，不能利用索引的有序性。
-
7 order by字段只被索引了前缀 ，key idx_col(col(10))
-
select * from t1 order by col ;
- 8 对于还有join的关联查询，排序字段并非全部来自于第一个表，使用explain 查看执行计划第一个表 type 值不是const 。
-
1 加大 max_length_for_sort_data 参数的设置
在 MySQL 中,决定使用老式排序算法还是改进版排序算法是通过参数max_length_for_sort_data来决定的。当所有返回字段的最大长度小于这个参数值时,MySQL 就会选择改进后的排序算法,反之,则选择老式的算法。所以,如果有充足的内存让MySQL 存放须要返回的非排序字段,就可以加大这个参数的值来让 MySQL 选择使用改进版的排序算法。
2 去掉不必要的返回字段
当内存不是很充裕时,不能简单地通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法,否则可能会造成 MySQL 不得不将数据分成很多段,然后进行排序,这样可能会得不偿失。此时就须要去掉不必要的返回字段,让返回结果长度适应 max_length_for_sort_data 参数的限制。
同时也要规范MySQL开发规范，尽量避免大字段。当有select 查询列含有大字段blob或者text 的时候,MySQL 会选择常规排序。
"