目标：分析语句 select * from order where amount>10 order by create_time limit 20;

分析：

mysql order.png

详解：

第一部分 order分类

MySQL有两种方式实现order by, 索引扫描排序跟文件排序(filesort)；下面我们分析这两种方式的实现。

1、索引扫描排序

我们以下边这个语句分析为例子说明

order表主键id;
select * from order order by create_time limit 20;

如果create_time上有索引，MySQL的查询数据过程,

a. 从create_time索引树取到前20个主键id集合，这时的主键id集合是根据create_time排好顺序的（根据辅助索引找到聚簇索引信息）
b. 根据主键id集合查找到数据，返回数据（根据聚簇索引拿到叶子节点的数据）

2、文件排序(filesort)

以下边这个语句分析为例子说明

order表主键id;
select * from order order by create_time limit 20;

如果create_time上没有索引，MySQL的查询数据过程,

a. 根据主键id集合查找到数据，加载到内存（假设内存足够大，后边会补充filesort排序的详细信息）（根据聚簇索引拿到叶子节点的数据）
b. 在内存中把数据按照create_time排序(filesort)，取出前20条数据返回。

3、结论

从上边的分析，我们了解到filesort会把大量数据加载到内存取排序，所以一般情况下order by子句使用索引避免filesort会提高效率，具体情况需要根据执行过程产生的数据量为标准。

第二部分 order与where配合

SQL语句中，where子句和order by子句都可以使用索引：where子句使用索引避免全表扫描，order by子句使用索引避免filesort，以提高查询效率。但是有事鱼和熊掌不可兼得，怎样取舍才更优。

1、where使用索引

order表主键id，辅助索引amount，辅助索引create_time
select * from order where amount=50 order by create_time limit 20;

a. where子句使用索引将过滤后的数据集加载到内存
b. 在内存中根据create_time排序（filesort）,然后返回前20条数据。如果索引amount的过滤效果好，虽然使用了filesort，查询效率也是很快的。
如果索引amount的过滤效果不好，那么就会加载大量的数据到内存去排序，这样查询效率就很低。

2、order by使用索引

order表主键id，辅助索引amount，辅助索引create_time
select * from order where amount=50 order by create_time limit 20;

a. order by子句使用索引将所有数据id集合。
b. 跟id集合部分的加载数据页到内存，匹配条件amount=50的数据，直到取出20条为止。如果总的数据集很大，但是数据分布均匀，查询效率还是ok的，但是分页到后边就很慢。
如果总的数据集很大，但是数据分布偏左，查询效率还是ok的，但是分页到后边就很慢。
如果总的数据集很大，但是数据分布偏右，查询效率就不ok了，但是分页到后边就很慢。

3、where与order by都使用索引

order表主键id,辅助索引create_time
select * from order where create_time>=时间戳 and create_time<=时间戳 order by create_time limit 20;

a.根据索引create_time查询到的数据范围已经排好了顺序，这样是最优的。

4、在where与order by创建联合索引

order表主键id,辅助联合索引(amount create_time)
select * from order where amount=50 order by create_time limit 20;

a.根据联合索引(amount create_time)查询到的数据已经排好了顺序。

5、总结

最好是where与order by子句使用同一个索引；如果不能兼得，就根据实际情况去分析，看在哪里使用索引会更优。

第三部分 filesort

以下是《高性能MySQL》中对filesort的介绍：
如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，可对每个独立的块使用“快速排序”进行排序，再将各个块的排序结果放到磁盘上，然后将各个排好序的块进行“归并排序”，最后返回排序结果。

1、sort buffer

如果数据量很大，超过了 sort buffer 的大小，那么是要进行临时文件排序的，也就是归并排序

2、max_length_for_sort_data

如果查询的字段长度的总和大于max_length_for_sort_data，这时候MySQL不会采用归并排序，会把主键id+排序字段放入sort buffer排序，之后再根据id回表查询出所需数据。

3、联合索引是覆盖索引

根据第二部分第4小结跟第三部分第2小结，当联合索引是覆盖索引，既排好了序，也不用回表。

第四部分参考

segmentfault.com/a/119000001…
《高性能MySQL》

MySQL order