ORDER BY 优化

来自泡泡学习笔记
BrainBs（讨论 | 贡献）2023年8月18日 (五) 09:19的版本
(差异) ←上一版本 | 最后版本 (差异) | 下一版本→ (差异)
跳到导航 跳到搜索

本节描述了当MySQL可以使用索引来满足ORDER BY子句时，如果无法使用索引则使用的filesort操作，以及有关ORDER BY的优化器可用的执行计划信息。

具有和不具有LIMIT的ORDER BY可能返回不同顺序的行。

使用索引来满足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是降序的，它也可以使用这些列上的索引（进行反向扫描）。

在接下来的两个查询中，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 join类型的第一个表）。

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

  • 在ORDER BY子句中仅对列的前缀存在索引。在这种情况下，索引无法完全解析排序顺序。例如，如果仅索引了一个CHAR(20)列的前10个字节，索引无法区分超过第10个字节的值，需要进行filesort操作。

  • 索引没有按顺序存储行。例如，对于MEMORY表中的HASH索引是成立的。

索引可用于排序可能受到列别名的影响。假设列t1.a被索引。在这个语句中，选择列表中的列名是a。它指的是t1.a，ORDER BY中对a的引用也是指的t1.a，所以t1.a上的索引可以被使用： 

SELECT a FROM t1 ORDER BY a;

在这个语句中，选择列表中的列名也是a，但它是别名。它指的是ABS(a)，ORDER BY中对a的引用也是指的ABS(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 5.7及更低版本），GROUP BY在某些条件下会隐式排序。在MySQL 8.0中，不再发生隐式排序，因此不再需要在末尾指定ORDER BY NULL来抑制隐式排序（如之前所做的）。然而，查询结果可能与之前的MySQL版本不同。为了得到给定的排序顺序，请提供ORDER BY子句。

使用filesort来满足ORDER BY

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

为了获取filesort操作的内存，从MySQL 8.0.12开始，优化器根据需要逐步分配内存缓冲区，最多分配到sort_buffer_size系统变量指示的大小，而不是事先分配固定数量的sort_buffer_size字节，这是在MySQL 8.0.12之前所做的。这使得用户可以将sort_buffer_size设置为较大的值，以加快较大的排序，而不用担心小规模排序会使用过多的内存。（在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优化

对于不使用filesort的缓慢ORDER BY查询，尝试降低max_length_for_sort_data系统变量的值，以触发filesort。（设置该变量值过高的症状是磁盘活动较高，而CPU活动较低。）这个技术仅适用于MySQL 8.0.20之前的版本。从8.0.20开始，由于优化器的更改使其过时且无效，max_length_for_sort_data已被弃用。

为了提高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，则使用了索引并且不执行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系统变量的值，但不一定与其相等。在MySQL 8.0.12之前，输出显示的是sort_buffer_size，指示sort_buffer_size的值。（在MySQL 8.0.12之前，优化器总是为排序缓冲区分配sort_buffer_size字节。从8.0.12开始，优化器根据需要逐步分配sort缓冲区内存，从较小的数量开始，并在需要时增加，最多分配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。

取自“http://bubblestudy.info/wiki/index.php?title=ORDER_BY_优化&oldid=1944”