以前面的市民表为例，假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄。
select city,name,age from t where city='杭州' order by name limit 1000 ;
这个语句看上去逻辑很清晰，但是你了解它的执行流程吗？今天，我就和你聊聊这个语句是怎么执行的，以及有什么参数会影响执行的行为。
全字段排序
为避免全表扫描，我们需要在city字段加上索引。这之后，用explain命令查看这个语句的执行情况。
图1 使用explain命令查看语句的执行情况
- “Using filesort”表示需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。
为了说明这个SQL查询语句的执行过程，我们先来看一下city索引的示意图。
图2 city字段的索引示意图
图中，满足city=’杭州’条件的行，是从IDX到ID(X+N)的这些记录。通常，这个语句执行流程如下：
- 初始化sort_buffer，确定放入name、city、age这三个字段；
- 从索引city找到第一个满足city=’杭州’条件的主键id，也就是图中的ID_X；
- 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
- 从索引city取下一个记录的主键id；重复3、4直到city的值不满足查询条件，对应的主键id也就是图中的ID_Y；
- 对sort_buffer中的数据按照字段name做快速排序；
- 按照排序结果取前1000行返回给客户端。
我们暂且把这个排序过程，称为全字段排序，执行流程如下，下一篇文章中我们还会用到这个排序。
图3 全字段排序
图中“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序。这取决于排序所需的内存和参数sort_buffer_size。
- sort_buffer_size：MySQL为排序开辟的内存(sort_buffer)的大小。当要排序的数据量小于sort_buffer_size，排序就在内存中完成。但当太大内存放不下，则不得不利用磁盘临时文件辅助排序。
你可以用下面介绍的方法，来确定一个排序语句是否使用了临时文件。
- 通过查看 OPTIMIZER_TRACE 的结果来确认的，你可以从 number_of_tmp_files中看到是否使用了临时文件。number_of_tmp_files：排序过程中使用的临时文件数。
图4 全排序的OPTIMIZER_TRACE部分结果
你一定奇怪，为什么需要12个文件？
number_of_tmp_files不等于0：内存放不下了需要使用外部排序。外部排序一般使用归并排序算法 ——> 可以这么简单理解：**MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。**number_of_tmp_files=0：表示数据量不超过buffer_size，排序可以直接在内存中完成。
rowid排序
上面算法只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。
- 但这个算法有一个问题：如果查询要返回的字段很多（即，单行很大）的话，那么sort_buffer里面要放的字段数太多，这样内存里能同时放下的行数很少，要分成很多个临时文件，排序性能很差。
那么，如果MySQL认为排序的单行长度太大会怎么做呢？
接下来，我来修改一个参数，让MySQL采用另外一种算法：SET max_length_for_sort_data = 16;
- max_length_for_sort_data：专门控制用于排序的行数据的长度。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。
city、name、age 三个字段的定义总长度是36，我把max_length_for_sort_data设置为16，计算过程有什么改变。
新算法放入sort_buffer的字段：只有要排序的列（即name字段）和主键id。但这时，排序结果就因为少了city和age字段的值，不能直接返回了。整个执行流程就变成如下所示：
- 初始化sort_buffer，确定放入两个字段：name和id；
- 从索引city找到第一个满足city=’杭州’条件的主键id，也就是图中的ID_X；
- 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；
- 从索引city取下一个记录的主键id；重复3、4直到不满足city=’杭州’条件为止，即图中的ID_Y；
- 对sort_buffer中的数据按照字段name进行排序；
- 遍历排序结果，取前1000行，并按id的值回到原表中取出city、name和age字段返回给客户端。
这个执行流程的示意图如下，我把它称为rowid排序。
图5 rowid排序
对比图3的全字段排序流程图你会发现：rowid排序多访问了一次表t的主键索引，就是步骤7。
根据这个说明过程和图示，思考：这个时候执行select @b-@a，结果会是多少呢？
- 首先，图中的examined_rows的值还是4000，表示用于排序的数据是4000行。
- 但是select @b-@a这个语句的值变成5000了。因为这时候除了排序过程外，在排序完成后，还要根据id去原表取值。由于语句是limit 1000，因此会多读1000行。
全字段排序 VS rowid排序
- 采用rowid排序：适用于内存小。因为该排序一次可以排序更多行，但需要再回到原表去取数据。
- 如果MySQL认为内存足够大，会优先选择全字段排序：把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
这就体现了MySQL的设计思想：如果内存够，就多利用内存，尽量减少磁盘访问。 对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会被优先选择。 这个结论看上去有点废话的感觉，但是你要记住它，下一篇文章我们就会用到。
看到这，你就了解了：MySQL做排序是一个成本比较高的操作。那么你会问，是不是所有的order by都需要排序操作呢？如果不排序就能得到正确的结果，那系统消耗会小很多，语句的执行也会更快。
- 其实，并不是所有的order by语句，都需要排序操作的。从上面分析的执行过程，我们可以看到，MySQL之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。
- 你可以设想下，如果能够保证从city这个索引上取出来的行，天然就是按照name递增排序的话，是不是就可以不用再排序了呢？确实是这样的。
所以，我们可以在这个市民表上创建一个city和name的联合索引，对应的SQL语句是：alter table t add index city_user(city, name);
作为与city索引的对比，我们来看看这个索引的示意图。
图7 city和name联合索引示意图
在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足city=’杭州’的记录，并额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要city是杭州，name的值就一定有序。
这样整个查询过程的流程就变成了：
- 从索引(city,name)找到第一个满足city=’杭州’条件的主键id；
- 到主键id索引取出整行，取name、city、age三个字段的值来作为结果集的一部分直接返回；
从索引(city,name)取下一个记录主键id；重复2、3，直到查到第1000条记录，或者不满足city=’杭州’条件时循环结束。
<br />图8 引入(city,name)联合索引后，查询语句的执行计划<br />可以看到，这个查询过程不需要临时表，也不需要排序。接下来，我们用explain的结果来印证一下。<br /><br />图9 引入(city,name)联合索引后，查询语句的执行计划
- 从图中可以看到，Extra字段中没有Using filesort了，也就是不需要排序了。
- 而且由于(city,name)这个联合索引本身有序，所以这个查询也不用把4000行全都读一遍，只要找到满足条件的前1000条记录就可以退出了。也就是说，在我们这个例子里，只需要扫描1000次。
我们再往前讨论，这个语句的执行流程有没有可能进一步简化呢？《5. 深入浅出索引（下）》覆盖索引
**覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。**
按覆盖索引的概念，我们可以再优化这个查询语句的执行流程。
创建一个city、name和age的联合索引，对应SQL语句：
alter table t add index city_user_age(city, name, age);
这时，对于city字段的值相同的行来说，还是按照name字段的值递增排序的，此时的查询语句也就不再需要排序了。这样整个查询语句的执行流程就变成了：
- 从索引(city,name,age)找到第一个满足city=’杭州’条件的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回；
从索引(city,name,age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；重复执行步骤2，直到查到第1000条记录，或者是不满足city=’杭州’条件时循环结束。
<br />图10 引入(city,name,age)联合索引后，查询语句的执行流程<br />然后，我们再来看看explain的结果。<br /><br />图11 引入(city,name,age)联合索引后，查询语句的执行计划
- Extra字段里面多了“Using index”：使用了覆盖索引，性能上会快很多。
- 当然，这里并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定。
小结
- 介绍了MySQL里面order by语句的几种算法流程。
- 在开发时，不可避免地会使用到order by语句。你心里要清楚每个语句的排序逻辑是怎么实现的，还要能够分析出在最坏情况下，每个语句的执行对系统资源的消耗，这样才能不犯低级错误。
思考题
city_name(city, name)是联合索引。要查杭州、苏州所有的市民的姓名，并按名字排序，显示前100条记录。如果SQL这么写 ：
select * from t where city in ('杭州',"苏州") order by name limit 100;
- 那么，这个语句执行的时候会有排序过程吗，为什么？
- 如果业务端代码由你来开发，需要实现一个在数据库端不需要排序的方案，你会怎么实现呢？
- 进一步地，如果有分页需求，要显示第101页，也就是说语句最后要改成 “limit 10000,100”， 你的实现方法又会是什么呢？
答：
虽然有(city,name)联合索引，对于单个city内部，name是递增的。但是由于这条SQL语句不是要单独地查一个city的值，而是同时查了”杭州”和” 苏州 “两个城市，因此所有满足条件的name就不是递增的了。也就是说，这条SQL语句需要排序。
那怎么避免排序呢？
这里，我们要用到(city,name)联合索引的特性，把这一条语句拆成两条语句，执行流程如下：
- 执行select * from t where city=“杭州” order by name limit 100; 这个语句是不需要排序的，客户端用一个长度为100的内存数组A保存结果。
- 执行select * from t where city=“苏州” order by name limit 100; 用相同的方法，假设结果被存进了内存数组B。
- 现在A和B是两个有序数组，然后你可以用归并排序的思想，得到name最小的前100值，就是我们需要的结果了。
如果把这条SQL语句里“limit 100”改成“limit 10000,100”的话，处理方式其实也差不多，即：要把上面的两条语句改成写：
select from t where city=”杭州” order by name limit 10100;
和
select from t where city=”苏州” order by name limit 10100。
这时候数据量较大，可以同时起两个连接一行行读结果，用归并排序算法拿到这两个结果集里，按顺序取第10001~10100的name值，就是需要的结果了。
当然这个方案有一个明显的损失，就是从数据库返回给客户端的数据量变大了。
所以，如果数据的单行比较大的话，可以考虑把这两条SQL语句改成下面这种写法：
select id,name from t where city=”杭州” order by name limit 10100;
和
select id,name from t where city=”苏州” order by name limit 10100。
然后，再用归并排序的方法取得按name顺序第10001~10100的name、id的值，然后拿着这100个id到数据库中去查出所有记录。
上面这些方法，需要你根据性能需求和开发的复杂度做出权衡。
若有收获，就点个赞吧
0 人点赞
