MySQL Order By工作原理

表t的结构见MySQL索引规划。

explain select a, b from t where a > 1000 and a < 10000 order by b desc limit 1000;

Extra中包含Using filesort表示需要排序，在排序时，MySQL会为每个线程分配一块内存区域用于排序，称之为sort_buffer。

全字段排序过程

上述语句的排序过程如下：

1. 初始化sort_buffer，确认放入a，b两个字段
2. 从索引a上找到第一个满足条件的主键id
3. 拿着该ID去主键索引上取出该行，然后Server层取出a,b两个字段的值，放入sort_buffer中
4. 从索引a上找到下一个满足条件的主键id
5. 重复步骤3、4z直到不满足查询条件为止
6. 对sort_buffer中数据按照b进行快速排序
7. 按照排序结果取前1000条返回给客户端

排序过程的发生位置？

上述排序过程可能在内存中完成，也可能需要使用外部排序，主要取决于排序所需要的内存和参数sort_buffer_size。

什么是sort_buffer_size？

sort_buffer_size是MySQL为排序开辟的内存大小，如果排序的数据量小于sort_buffer_size，排序就在内存中进行，相反内存如果放不下的话，就需要借助磁盘临时文件进行排序。

如何查看是否使用了临时文件进行排序？

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';

/* 执行语句 */
select a, b from t where a > 1000 and a < 10000 order by b desc limit 1000;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

上图来源于MySQL官网：

• number_of_tmp_files表示的是排序过程中使用的临时文件数，外部排序使用的是归并排序算法
• examined_rows：表示参与排序的行数
• sort_mode中packed_additional_fields：表示排序过程对字符串进行紧凑处理，就是在排序过程中按照字符串的实际长度来分配空间

rowid排序

上述的全字段排序优点是在我们整个过程中我们只对原表数据扫描了一遍，其他都在sort_buffer或者临时文件中进行，但是全字段排序也有弊端：

• 如果查询要返回的字段很多（或者某个字段是大字段），那么sort_buffer中能够存放的行数会很少，排序用到的临时文件数将会很多，排序性能很差。这种情况下MySQL会采用另一种排序方式。

MySQL如何知道需要使用rowid排序？

MySQL可以通过max_length_for_sort_data参数来进行控制，如果单行的长度超过该值，MySQL会认为该行很大，需要切换到rowid算法。

rowid排序过程

1. 初始化sort_buffer，确认放入a，b两个字段
2. 从索引a上找到第一个满足条件的主键id
3. 拿着该ID去主键索引上取出该行，然后Server层取出吧，id两个字段的值，放入sort_buffer中
4. 从索引a上找到下一个满足条件的主键id
5. 重复步骤3、4直到不满足查询条件为止
6. 对sort_buffer中数据按照b进行快速排序
7. 按照排序结果取前1000条返回，并按照id的值回到原表上取出a和b两个字段返回给客户端

根据索引直接返回

假设我们order by时有索引正好符合我们的要求，此时就不需要再借助内存或临时文件进行排序，而是直接利用有序遍历索引树直接返回结果。