MySQL :: MySQL 8.4 参考手册 :: 10.2.1.16 ORDER BY 优化

版本 8.4

9.0
8.0
5.7

8.0 日语

MySQL 8.4 参考手册 / ... / ORDER BY 优化

10.2.1.16 ORDER BY 优化

本节介绍 MySQL 何时可以使用索引来满足 ORDER BY 子句、当无法使用索引时使用的 filesort 操作，以及优化器提供的有关 ORDER BY 的执行计划信息。

带有和不带有 LIMIT 的 ORDER BY 可能会以不同的顺序返回行，如第 10.2.1.19 节“LIMIT 查询优化” 中所述。

使用索引满足 ORDER BY

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

即使 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 pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, 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 为降序，则它也可以使用这些列上的索引（使用向后扫描）。请参阅第 10.3.13 节“降序索引”。
在接下来的两个查询中，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 个字节的值，因此需要进行 filesort。
索引不按顺序存储行。例如，对于 MEMORY 表中的 HASH 索引就是这种情况。

列别名的使用可能会影响排序索引的可用性。假设列 t1.a 已建立索引。在此语句中，选择列表中列的名称为 a。它指的是 t1.a，ORDER BY 中对 a 的引用也是如此，因此可以使用 t1.a 上的索引。

SELECT a FROM t1 ORDER BY a;

在此语句中，选择列表中列的名称也是 a，但它是别名。它指的是 ABS(a)，ORDER BY 中对 a 的引用也是如此，因此不能使用 t1.a 上的索引。

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

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

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

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

使用文件排序满足 ORDER BY

如果无法使用索引来满足 ORDER BY 子句，MySQL 将执行 filesort 操作，该操作读取表行并对其进行排序。filesort 构成了查询执行中的一个额外排序阶段。

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

如果结果集太大而无法放入内存，filesort 操作将根据需要使用临时磁盘文件。某些类型的查询特别适合完全在内存中进行 filesort 操作。例如，优化器可以使用 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（请参阅第 10.8.1 节，“使用 EXPLAIN 优化查询”），您可以检查 MySQL 是否可以使用索引来解析 ORDER BY 子句。

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

此外，如果执行了 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 不会区分优化器是否在内存中执行 filesort。在内存中使用 filesort 可以在优化器跟踪输出中看到。查找 filesort_priority_queue_optimization。有关优化器跟踪的信息，请参阅 MySQL 内部结构：跟踪优化器。