聊聊order by 是怎么实现的？

首先排序功能由 ORDER BY 实现，具体排列顺序取决于优化器的选择。若优化器认为索引排序更有效率，则使用索引排序；反之，则使用 filesort（执行计划中额外信息提示：使用 filesort）。然而，索引排序的适用情况有限，且不确定性较高，通常还是会采用 filesort。

在 filesort 排序中，如果排序的数据较少，可在内存中的 sort_buffer 上完成；否则，需借助临时文件进行排序。实际排序过程中，如果字段长度较短，可直接在 sort_buffer 中进行全字段排序并返回结果集。若字段长度较长，可能出于空间考量，采用基于 row_id 的排序，此时会进行二次回表后返回结果集。

索引

众所周知，索引具备天然的排序属性，因此在使用 ORDER BY 时，若能充分利用索引，则效率必然最佳。

MySQL 确实可以基于索引执行 ORDER BY 查询，然而这一过程是否必然使用索引完全由优化器决定。

 CREATE TABLE `t2` (
  `id` INT(11),
  `a` varchar(64) NOT NULL,
  `b` varchar(64) NOT NULL,
  `c` varchar(64) NOT NULL,
  `d` varchar(64) NOT NULL,
  `f` varchar(64) DEFAULT NULL,
  PRIMARY KEY(id),
  UNIQUE KEY `f` (`f`),
  KEY `idx_abc` (`a`,`b`,`c`)
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

假设存在如上所述的表格，在排序过程中可能会出现以下情况（由于优化器的干预，以下结果并不一定可以 100%复现。根据我的实验，在 MySQL 5.7 环境中是可以的）。

select * from t2 order by a;

-- 不走索引，使用filesort（后面介绍啥是filesort）


select * from t2 order by a limit 100;

-- 走索引


select a,b from t2 order by a limit 100;

-- 走索引


select a,b,c from t2 order by a;

-- 走索引


select a,b,c,d from t2 order by a;

-- 不走索引，使用filesort


select a,b,c,d from t2 where a = "Paidaxing" order by b;

-- 走索引


select a,b,c,d from t2 where b = "Paidaxing" order by a;

-- 不走索引，使用filesort

在使用索引字段进行排序时，优化器会根据成本评估来选择是否通过索引进行排序。经过我的多次验证，以下情况中，索引排序的概率较高：

• 查询的字段和 ORDER BY 的字段组成了一个联合索引，并且查询条件符合最左前缀匹配，使得查询可以使用索引覆盖。例如：SELECT a, b, c FROM t2 ORDER BY a;
• 查询条件中包含了 LIMIT，并且 LIMIT 的数量不是很大。（在我测试的表中，数据量为 80 万，当 LIMIT 超过 2 万多时就不再使用索引排序），例如：ORDER BY a LIMIT 100
• 虽然未遵循最左前缀匹配，但是前导列通过常量进行了查询，例如：WHERE a = "Paidaxing" ORDER BY b

filesort 排序

如果无法使用或优化器认为索引排序效率不高，MySQL 将执行 filesort 操作，以读取表中的行并对它们进行排序。

在排序过程中，MySQL 为每个线程分配一块内存用于排序，称为sort_buffer，其大小由 sort_buffer_size控制。

根据 sort_buffer_size 的大小不同，排序操作会发生在不同的位置：

• 如果排序数据量小于 sort_buffer_size，则排序在内存中完成。
• 如果排序数据量大于 sort_buffer_size，则需要利用磁盘临时文件辅助排序。

★临时文件排序采用归并排序算法，首先将需要排序的数据分配到多个临时文件中，同时进行排序操作，然后将多个排序完成的文件合并成一个结果集返回给客户端。相对于在内存中的 sort_buffer 排序，磁盘上的临时文件排序速度较慢。

除了sort_buffer_size参数之外，影响排序算法的另一个关键参数是 max_length_for_sort_data。

max_length_for_sort_data 是 MySQL 中控制用于排序的行数据的长度的一个参数，默认值为 1024 字节。如果单行长度超过该值，MySQL 就会认为单行太大，因此会采用 rowid 排序；否则，会进行全字段排序。

全字段排序

所谓全字段排序，即将要查询的所有字段都放入 sort_buffer 中，然后根据排序字段进行排序，排序完成后直接将结果集返回给客户端。

假设我们有以下查询 SQL：

select a,d,f from t2 where a = "Paidaxing" order by d;

--

因为此处涉及的字段为 a、d、f 三个，因此将满足 WHERE 条件的所有数据的 a、d、f 字段都放入 sort_buffer 中，然后根据 d 字段在 sort_buffer 中进行排序，排序完成后返回给客户端的大致过程如下：

1. 从索引 a 中取出满足条件 a = "Paidaxing" 的第一条记录的主键 ID。
2. 根据主键 ID 回表，提取 a、d、f 三个字段的值，并将它们存入 sort_buffer。
3. 继续查询下一个符合 a = "Paidaxing" 条件的记录，重复执行第 1 和第 2 步骤。
4. 在 sort_buffer 中根据 d 字段进行排序。
5. 将排序后的结果集返回给客户端。

以上过程中，如果数据在 sort_buffer 中无法全部存放，则会使用临时文件，并对临时文件进行归并排序。

全字段排序的优点在于只需要对原表进行一次回表查询（每条记录只需回表一次），排序完成后可以直接返回所需字段，因此效率较高。但其缺点在于，如果要查询的字段较多，会占用大量 sort_buffer 空间，导致可存储的数据量减少。当需要排序的数据量增大时，可能会使用临时文件，从而导致整体性能下降。

为避免这个问题，可以采用 row_id 排序的方式。

row_id 排序

这个也很好理解，即在构建 sort_buffer 时，不需要将所有要查询的字段都放进去，只需要将排序字段和主键放入即可。

select a,d,f from t2 where a = "Paidaxing" order by d;

比如这个 SQL，只需要将 d 和 id 放入 sort_buffer 中，首先按照 d 进行排序。排序完成后，根据 id 将对应的 a、d、f 几个字段查询出来，然后返回给客户端的大致过程如下：

1. 从索引 a 中取出符合条件 a = "Paidaxing" 的第一条记录的主键 ID。
2. 根据主键 ID 回表，提取 d 字段的值，并将其存入 sort_buffer。
3. 继续查询下一个符合条件 a = "Paidaxing" 的记录，重复执行第 1 和第 2 步骤。
4. 在 sort_buffer 中根据 d 进行排序。
5. 根据排序后的 id，回表查询出对应的 a、d、f 几个字段。
6. 将结果集返回给客户端。

以上的第五步，与全字段排序算法相比确实多了一次回表操作。因此，这种方案的效率肯定会稍慢一些。

如何选择

如何选择排序方式？

实际上，row_id 是 MySQL 的一种优化算法，它首先考虑使用全字段排序。只有在认为字段长度过长可能影响效率时，才会采用 row_id 排序方式。此外，如果能够利用 sort_buffer 完成排序，MySQL 就不会使用临时文件。

综上所述，MySQL 在选择排序方式时会优先考虑速度、内存和减少回表次数等因素。

好了，本章节到此告一段落。希望对你有所帮助，祝学习顺利。