8.2.1.13 ORDER BY 优化_MySQL 5.6 参考手册

使用索引来满足 ORDER BY

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

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

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

在此查询中，索引 on 使优化器能够避免排序： (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有序的，如果子句的选择性足以使索引范围扫描比表扫描更便宜，则索引 on 会避免排序：(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;

在接下来的两个查询中， 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没有 name 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;

查询混合ASC和 DESC：

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

用于获取行的索引与以下中使用的索引不同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 BYand GROUP BY表达式。
ORDER BY仅在子句中命名的列的前缀上有一个索引。在这种情况下，索引不能用于完全解析排序顺序。例如，如果仅对 CHAR(20)列的前 10 个字节建立索引，则索引无法区分超过第 10 个字节的值，filesort因此需要 a。
索引不按顺序存储行。例如，这适用HASH于表中的索引 MEMORY。

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

SELECT a FROM t1 ORDER BY a;

在该语句中，选择列表中的列的名称也是a，但它是别名。它ABS(a)和中的引用一样引用a，因此不能使用 ORDER BY索引：t1.a

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

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

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

默认情况下，MySQL对查询进行排序，就好像您也包含在查询中一样。如果您包含一个包含相同列列表的显式子句，MySQL 会在没有任何速度损失的情况下优化它，尽管排序仍然会发生。 GROUP BY col1, col2, ...ORDER BY col1, col2, ...ORDER BY

如果查询包含GROUP BY但您希望避免对结果进行排序的开销，则可以通过指定ORDER BY NULL. 例如：

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

优化器可能仍然选择使用排序来实现分组操作。ORDER BY NULL 禁止对结果进行排序，而不是通过分组操作确定结果之前进行的排序。

笔记

GROUP BY默认情况下隐式排序（即，在没有ASC或 DESC指定GROUP BY列的情况下），但不推荐依赖隐式 GROUP BY排序。要生成给定的排序顺序，请对列使用显式 ASC或DESC 指示符GROUP BY或提供ORDER BY子句。 GROUP BY排序是一个 MySQL 扩展，可能会在未来的版本中改变；例如，使优化器能够以它认为最有效的任何方式对分组进行排序，并避免排序开销。

使用文件排序来满足 ORDER BY

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

为了获得用于filesort操作的内存，优化器预先分配固定数量的 sort_buffer_size字节。各个会话可以根据需要更改此变量的会话值以避免过度使用内存，或根据需要分配更多内存。

如果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查询，请尝试将系统变量降低到适合触发 . （将此变量的值设置得太高的症状是高磁盘活动和低 CPU 活动的组合。） filesortmax_length_for_sort_datafilesort

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

增加 sort_buffer_size 变量值。理想情况下，该值应该足够大以使整个结果集适合排序缓冲区（以避免写入磁盘和合并过程），但至少该值必须足够大以容纳 15 个元组。（最多合并 15 个临时磁盘文件，并且每个文件必须有至少一个元组的内存空间。）
考虑到存储在排序缓冲区中的列值的大小受 max_sort_length系统变量值的影响。例如，如果元组存储长字符串列的值并且您增加的值 max_sort_length，则排序缓冲区元组的大小也会增加并且可能需要您增加 sort_buffer_size。对于作为字符串表达式结果计算的列值（例如那些调用字符串值函数的列值），该filesort算法无法确定表达式值的最大长度，因此它必须分配 max_sort_length每个元组的字节数。
要监视合并遍数（合并临时文件），请检查 Sort_merge_passes 状态变量。
增加 read_rnd_buffer_size 变量值以便一次读取更多行。
通过将列声明为仅保存存储在其中的值所需的大小，每行使用更少的 RAM。例如，比值不超过 16 个字符 CHAR(16)要好。CHAR(200)
更改tmpdir 系统变量以指向具有大量可用空间的专用文件系统。变量值可以列出多个以循环方式使用的路径；您可以使用此功能将负载分散到多个目录中。:在 Unix 上用冒号字符 ( ) 分隔路径，;在 Windows 上用分号字符 ( ) 分隔路径。路径应该命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。

ORDER BY 可用的执行计划信息

使用 EXPLAIN （参见第 8.8.1 节，“使用 EXPLAIN 优化查询”），您可以检查 MySQL 是否可以使用索引来解析ORDER BY子句：

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

此外，如果filesort执行 a，优化器跟踪输出包括一个 filesort_summary块。例如：

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

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

<sort_key, rowid>：这表示排序缓冲区元组是包含原始表行的排序键值和行ID的对。元组按排序键值排序，行 ID 用于从表中读取行。
<sort_key, additional_fields>：这表示排序缓冲区元组包含查询引用的排序键值和列。元组按排序键值排序，列值直接从元组中读取。

EXPLAIN不区分优化器是否 filesort在内存中执行。filesort在优化器跟踪输出中可以看到内存中的使用。寻找 filesort_priority_queue_optimization。有关优化器跟踪的信息，请参阅 MySQL 内部结构：跟踪优化器。