MySQL :: MySQL 8.3 Reference Manual :: 10.2.1.16 ORDER BY Optimization

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / ORDER BY Optimization

10.2.1.16 ORDER BY 优化

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

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

使用索引来满足 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 pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;
```
在这个查询中，key_part1 是常量，因此通过索引访问的所有行都在 key_part2 顺序中，索引 (key_part1, key_part2) 可以避免排序，如果 WHERE 子句足够选择性以使索引范围扫描比表扫描更便宜：
```
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，优化器可以使用索引，如果索引也使用对应的混合升序和降序列：
```
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 中的引用一样，因此可以使用 t1.a 的索引：

SELECT a FROM t1 ORDER BY a;

在该语句中，选择列表中的列名也是 a，但它是别名。它引用 ABS(a)，就像 ORDER BY 中的引用一样，因此不能使用 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.2 及更低版本），GROUP BY 在某些情况下隐式排序。在 MySQL 8.3 中，这不再发生，因此不再需要在结尾指定 ORDER BY NULL 来抑制隐式排序（如之前所做的那样）。然而，查询结果可能与之前的 MySQL 版本不同。要产生给定的排序顺序，请提供 ORDER BY 子句。

使用 filesort 满足 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 字节。)

The sort_mode 值提供了排序缓冲区元组的内容信息：

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

EXPLAIN 不区分优化器是否在内存中执行文件排序。可以在优化器跟踪输出中看到内存文件排序的使用。查找 filesort_priority_queue_optimization。有关优化器跟踪的信息，请参阅 MySQL Internals：优化器跟踪。

PREV HOME UP NEXT