mysql-排序原理与优化

业务中经常需要对某个字段进行排序，我们SQL就用order by，本文就分析一下order by工作执行的原理，以及如何用索引来进行优化。

我们以一句下面这句简单的SQL来进行展开，name字段没有加索引，city字段加了索引。

1

EXPLAIN select city,name,age from t where city='杭州' order by name  ;

city字段建了索引，执行计划也是走了city的索引，extra有 using filesort表示需要排序，MySQL会为每个线程分配一块排序用的内存，sort_buffer。

sort_buffer的大小是由参数sort_buffer_size决定的，如果我们需要排序的数据大小超过了这个值，那在不能单在内存中完成排序了，需要借助磁盘临时文件完成。

上面这句SQL的查询流程大概是这样的：

1. 根据city的索引，在city的索引树上找到city=’杭州’的数据，并取id；
2. 回表，从索引组织表上找到对应的数据，取出整行，取 name、city、age 三个字段的值；
3. 将找到的对应的数据行放入sort_buffer中；
4. 依次循环，直到city不满足city=’杭州’；
5. 对sort_buffer的数据按照name排序，并将结果集返回给客户端。

上面这种会把整行所有的数据都放入sort_buffer，这种称为全字段排序。但整行放入sort_buffer，很可能放不下而使用外存辅助排序，为了解决这个情况还有一种rowid排序，就是放入sort_buffer的只有排序字段和id。

MySQL怎么知道要用rowid排序呢？有一个参数max_length_for_sort_data，单行数据超过了这个大小的话就用rowid排序。

rowid排序只将排序字段(name)和id放入sort_buffer，然后排序之后，再从索引组织表中按id取对应的字段数据返回给客户端。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

无论全字段排序还是rowid排序，都是成本比较高的操作，回想一下我们为什么要进行排序？如果原来的数据就是有序的，是不是就可以避免排序这步高成本的操作？

如果能够保证从 city 这个索引上取出来的行，天然就是按照 name 递增排序的话，就可以不用再排序了。

那针对上面的查询，我们建立city和name的联合索引，那么这颗索引树结果如下。

这样整个查询过程的流程就变成了：

1. 从索引 (city,name) 找到第一个满足 city=’杭州’条件的主键 id；
2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
3. 从索引 (city,name) 取下一个记录主键 id；
4. 重复步骤 2、3，直到遍历结束，或者是不满足 city=’杭州’条件时循环结束

或者我们可以建立city、name 和 age 的联合索引，这样直接走覆盖索引，不会有回表的操作。

这样通过索引我们就优化了排序的方式，执行计划extra也不会有 using filesort了。

看下面两句SQL，这两句SQL是不一样的，第一句SQL会走全表扫描，第二句会走create_time的索引，这是因为第一句SQL没有limit 需要对所有的数据排序，那执行器觉得这样不走索引，少了回表效率更高，所以走的全表扫描。

有没有limit会多很多查询的查询计划都有影响，group by 等也是同样的，因为没有用limit 那我们基本都需要遍历所有数据，这时候就很有可能避免回表而不走索引了。

1
2

select * from T order by create_time;
select * from T order by create_time limit 100;