16.ORDER BY 优化

本节描述了 MySQL 何时可以使用索引来满足 ORDER BY 子句、无法使用索引时使用的文件排序操作，以及优化器提供的有关 ORDER BY 的执行计划信息。

带有和不带有 LIMIT 的 ORDER BY 可能会以不同的顺序返回行，主要有以下内容：

使用索引来满足ORDER BY
使用文件排序来满足 ORDER BY
影响 ORDER BY 优化
ORDER BY 可用的执行计划信息

使用索引来满足ORDER BY

在某些情况下，MySQL 可能会使用索引来满足 ORDER BY 子句并避免执行文件排序操作时涉及的额外排序。

即使 ORDER BY 与索引不完全匹配，只要索引的所有未使用部分和所有额外的 ORDER BY 列都是 WHERE 子句中的常量，也可以使用索引。如果索引不包含查询访问的所有列，则仅当索引访问比其他访问方法更高效时才使用索引。

假设(key_part1, key_part2)上有索引，下面的查询可能会使用该索引来解析ORDER BY部分。优化器是否确实这样做取决于如果还必须读取不在索引中的列，则读取索引是否比表扫描更有效。

在此查询中，(key_part1, key_part2) 上的索引使优化器能够避免排序：

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

但是，查询使用 SELECT *，它可能会选择比 key_part1 和 key_part2 更多的列。在这种情况下，扫描整个索引并查找表行以查找不在索引中的列可能比扫描表并对结果排序更低效。如果是这样，优化器可能不会使用索引。如果 SELECT * 仅选择索引列，则使用索引并避免排序。

如果 t1 是 InnoDB 表，则表主键隐式是索引的一部分，并且索引可用于解析此查询的 ORDER BY：

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

在此查询中，key_part1 是常量，因此通过索引访问的所有行均按 key_part2 顺序排列，并且如果 WHERE 子句的选择性足以使索引范围扫描比表扫描更高效，则 (key_part1, key_part2) 上的索引可以避免排序：

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

在接下来的两个查询中，是否使用索引与前面显示的不带 DESC 的相同查询类似：

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2 DESC;

ORDER BY 中的两列可以按相同方向（均为 ASC，或均为 DESC）或相反方向（一个 ASC，一个 DESC）排序。指数使用的一个条件是指数必须具有相同的同质性，但不必具有相同的实际方向。

如果查询混合 ASC 和 DESC，则优化器可以在列上使用索引，前提是该索引还使用相应的混合升序和降序列：

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 ASC;

如果 key_part1 为降序且 key_part2 为升序，则优化器可以在 (key_part1, key_part2) 上使用索引。如果 key_part1 为升序且 key_part2 为降序，它还可以在这些列上使用索引（通过向后扫描）。

在接下来的两个查询中，key_part1 与常量进行比较。如果 WHERE 子句的选择性足以使索引范围扫描比表扫描高效，则使用索引：

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

在下一个查询中，ORDER BY 没有命名 key_part1，但选择的所有行都有一个常量 key_part1 值，因此索引仍然可以使用：

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

在某些情况下，MySQL 无法使用索引来解析 ORDER BY，尽管它仍然可以使用索引来查找与 WHERE 子句匹配的行。例子：

该查询对不同索引使用 ORDER BY：

SELECT * FROM t1 ORDER BY key1, key2;

该查询对索引的非连续部分使用 ORDER BY：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

用于获取行的索引与 ORDER BY 中使用的索引不同：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

该查询将 ORDER BY 与包含索引列名称以外的术语的表达式结合使用：

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

该查询连接许多表，并且 ORDER BY 中的列并不全部来自用于检索行的第一个非常量表。（这是 EXPLAIN 输出中第一个没有 const 连接类型的表。）

该查询具有不同的 ORDER BY 和 GROUP BY 表达式。

仅在 ORDER BY 子句中指定的列的前缀上有索引。在这种情况下，索引不能用于完全解析排序顺序。例如，如果仅对 CHAR(20) 列的前 10 个字节建立索引，则索引无法区分超过第 10 个字节的值，并且需要进行文件排序。

该索引不按顺序存储行。例如，对于 MEMORY 表中的 HASH 索引就是如此。

用于排序的索引的可用性可能会受到列别名的使用的影响。假设列 t1.a 已建立索引。在此语句中，选择列表中的列名称为 a。它引用 t1.a，就像 ORDER BY 中对 a 的引用一样，因此可以使用 t1.a 上的索引：

SELECT a FROM t1 ORDER BY a;

在以下语句中，ORDER BY 引用的名称不是选择列表中的列名称。但是t1中有一个名为a的列，因此ORDER BY引用了t1.a，并且可以使用t1.a上的索引。（当然，最终的排序顺序可能与 ABS(a) 的顺序完全不同。）

SELECT ABS(a) AS a FROM t1 ORDER BY a;

以前（MySQL 8.2 及更低版本），GROUP BY 在某些条件下隐式排序。在 MySQL 8.3 中，这种情况不再发生，因此不再需要在末尾指定 ORDER BY NULL 来抑制隐式排序（如之前所做的那样）。但是，查询结果可能与以前的 MySQL 版本不同。要生成给定的排序顺序，请提供 ORDER BY 子句。

使用文件排序来满足 ORDER BY

如果索引不能用于满足 ORDER BY 子句，MySQL 将执行文件排序操作来读取表行并对它们进行排序。文件排序构成查询执行中的额外排序阶段。

为了获得用于文件排序操作的内存，优化器根据需要增量分配内存缓冲区，直到达到 sort_buffer_size 系统变量指示的大小。这使得用户可以将 sort_buffer_size 设置为较大的值以加速较大的排序，而不必担心小型排序会使用过多的内存。（对于 Windows 上的多个并发排序，此好处可能不会出现，因为 Windows 的多线程 malloc 较弱。）

如果结果集太大而无法放入内存，文件排序操作将根据需要使用临时磁盘文件。某些类型的查询特别适合完全内存中的文件排序操作。例如，优化器可以使用 filesort 在内存中高效地处理以下形式的查询（和子查询）的 ORDER BY 操作，而无需临时文件：

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

此类查询在仅显示较大结果集中的几行的 Web 应用程序中很常见。例子：

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

影响 ORDER BY 优化

要提高 ORDER BY 速度，请检查是否可以让 MySQL 使用索引而不是额外的排序阶段。如果这不可能，请尝试以下策略：

增加 sort_buffer_size 变量值。理想情况下，该值应该足够大，以便整个结果集适合排序缓冲区（以避免写入磁盘和合并传递）。
请考虑存储在排序缓冲区中的列值的大小受 max_sort_length 系统变量值的影响。例如，如果元组存储长字符串列的值，并且您增加 max_sort_length 的值，则排序缓冲区元组的大小也会增加，并且可能需要您增加 sort_buffer_size。要监视合并传递的数量（合并临时文件），请检查 Sort_merge_passes 状态变量。
增加 read_rnd_buffer_size 变量值，以便一次读取更多行。
更改 tmpdir 系统变量以指向具有大量可用空间的专用文件系统。变量值可以列出以循环方式使用的多个路径；可以使用此功能将负载分散到多个目录中。在 Unix 上用冒号 (:) 分隔路径，在 Windows 上用分号 (;) 分隔路径。路径应该命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。

ORDER BY 可用的执行计划信息

使用 EXPLAIN 可以检查 MySQL 是否可以使用索引来解析 ORDER BY 子句：

如果 EXPLAIN 输出的 Extra 列不包含Using filesort，则使用索引并且不执行文件排序。
如果 EXPLAIN 输出的 Extra 列包含Using filesort，则不使用索引并执行文件排序。

此外，如果执行文件排序，优化器跟踪输出将包含 filesort_summary 块。例如：

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "peak_memory_used": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

peak_memory_used表示排序过程中任意时刻使用的最大内存。该值最多但不一定与 sort_buffer_size 系统变量的值一样大。优化器增量分配排序缓冲区内存，从少量开始，然后根据需要添加更多内存，最多可达 sort_buffer_size 字节。）

sort_mode 值提供有关排序缓冲区中元组内容的信息：

<sort_key, rowid>：这表示排序缓冲区元组是包含原始表行的排序键值和行 ID 的对。元组按排序键值排序，行 ID 用于从表中读取行。
<sort_key,additional_fields>：这表示排序缓冲区元组包含排序键值和查询引用的列。元组按排序键值排序，列值直接从元组中读取。
<sort_key, Packed_additional_fields>：与之前的变体类似，但附加列紧密地打包在一起，而不是使用固定长度编码。

EXPLAIN 不区分优化器是否在内存中执行文件排序。内存中文件排序的使用可以在优化器跟踪输出中看到。

使用索引来满足ORDER BY​

使用文件排序来满足 ORDER BY​

影响 ORDER BY 优化​

ORDER BY 可用的执行计划信息​

使用索引来满足ORDER BY

使用文件排序来满足 ORDER BY

影响 ORDER BY 优化

ORDER BY 可用的执行计划信息