MySQL中的order by

前言

一次查询中，想实现type为2的数据优先展示在顶部，type不为2的数据按preset排序输出，然后有了如下语句：

select * from rules where type = 2
union all
select * from rules where type != 2 order by preset

结果是这个样子，并没有按照预想的那样，type为2的数据优先展示。

接着尝试着加上括号，：

(select * from rules where type = 2)
union all
(select * from rules where type != 2 order by preset)

这次的结果对了，实现了目标。

猜测MySQL对未加括号的语句进行了优化，即union之后共用order，最后通过explain计划，对比前后的执行记录，得到验证。

(select * from rules where type = 2
union all
select * from rules where type != 2) order by preset

基于上述经历，对order by的执行原理稍微提了些兴趣。

参数说明

图片和更加详细文档可以参考官方文档

1. sort_buffer_size：该参数是MySQL 为排序开辟的内存大小。

注意：当排序的数据量小于 sort_buffer_size值时，排序就在内存中完成。如果排序数据量太大，内存放不下，则需要利用磁盘临时文件辅助排序。

2. number_of_tmp_files：排序过程中用到的临时文件数。当需要利用磁盘临时文件时，通过该参数可以进行展示。
3. sort_mode：提供有关排序缓冲区中元组内容的信息。它的取值有如下三个：

<sort_key, rowid>，缓冲区内容为参与排序的字段和row id。
<sort_key, additional_fields>，缓冲区内容不仅有参与排序的字段、row id还有需要查询的字段。

<sort_key, packed_additional_fields>，内容和第二点一样，只不过对字符串做了紧凑处理，如varchar(255)，但实际上，是按照真正存储时的长度进行处理。

5. max_length_for_sort_data：从MySQL 8.0.20开始，该变量被弃用，它充当决定使用哪个文件排序算法（全字段排序 、 rowid 排序）的索引值大小的阈值。
   

原理

以如下该查询为例子，其中click_date 上使用了索引。

explain select * from reports_city where click_date = '2019-09-24'  order by rule_id limit 10; 

针对全字段排序（filesort）

（1）首先是初始化 sort_buffer_size（默认是262144字节）
（2）从索引 click_date 找到第一个满足 2019-09-24条件的主键 id，然后回表取出整行。
（3）存入 sort_buffer 中，从索引 click_date 取下一个记录的主键 id，重复步骤 2，直到 click_date 的值不满足查询条件为止。
（4）对 sort_buffer 中的数据按照字段 rule_id做快速排序,按照排序结果取前 10行返回给客户端。

我们可以结合explain和trace执行路由进行查询，其中路由查看可以通过如下命令：

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

select * from reports_city where click_date = '2019-09-24'  order by rule_id limit 10; 

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

注意，查询 OPTIMIZER_TRACE 这个表会用到临时表，而临时表在不同的引擎下，展示的数据会有些差别。可以通过internal_tmp_disk_storage_engine 进行设置，但是，在8.0.16及之后不再支持该参数且都为innodb。

MySQL 8.0.16, internal_tmp_disk_storage_engine is not supported, and internal temporary tables on disk are always handled by InnoDB.

rowId排序

filesort排序是根据click_date一行一行的查出来，然后放到sort_buffer中，这样由于sort_buffer_size的限制，但超过这个限制时，则不能采用filesort排序了。（内存有限）

但实际查询时，总数据量是未知的，不能说去查一遍然后统计大小，再决定用不用filesort排序。这里就涉及到另一个参数了：max_length_for_sort_data，单行数据的长度，超过该长度时则不用filesort排序了，通过调整该参数值，我们可以使我们的查询使用到rowId排序。

其原理如下：

（1）同样先初始化 sort_buffer_size
（2）从索引 click_date 找到第一个满足 2019-09-24条件的主键 id，到主键 id 索引取出整行，将 rule_id，id这两个字段存入 sort_buffer 中
（3）从索引 click_date 取下一个记录的主键 id；重复步骤 直到不满足条件
（4）对 sort_buffer 中的数据按照字段 rule_id进行排序；遍历排序结果，取前 10 行，并按照 id 的值回表查出相应字段返回给客户端。

“无排序”

这里的“无排序”不是真的无排序，而是说再查询时不用再费力气把数据排一遍。这里就需要借助索引的优势了。

以本文的查询为例子，我在click_data和rule_id上建立覆盖索引，在这样的一个B+树上，每个click_data下的rule_id已经排好序了，此时只需要根据id进行回表查询，将数据返回给客户端即可。

（完）