8.2.1.14 按优化 Sequences

本节描述了 MySQL 何时可以使用索引满足ORDER BY子句,何时无法使用索引时使用filesort操作以及优化器可提供的关于ORDER BY的执行计划信息。

第 8.2.1.17 节“ LIMIT 查询优化”中所述,具有LIMIT和不具有LIMITORDER BY可能以不同的 Sequences 返回行。

使用索引满足 ORDER BY

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

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

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

  • 在此查询中,(key_part1, key_part2)上的索引使优化程序避免排序:
SELECT * FROM t1
  ORDER BY key_part1, key_part2;

但是,查询使用SELECT *,它选择的列可能多于* key_part1 key_part2 *。在这种情况下,扫描整个索引并查找 table 行以查找索引中未包含的列可能比扫描 table 并排序结果要昂贵。如果是这样,优化器可能不会使用该索引。如果SELECT *仅选择索引列,则将使用索引并避免排序。

如果t1InnoDBtable,则 table 主键隐式属于索引的一部分,并且该索引可用于解析此查询的ORDER BY

SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;
  • 在此查询中,* key_part1 是常量,因此通过索引访问的所有行都按 key_part2 *Sequences,并且如果WHERE子句的选择性足以使索引范围扫描比 table 扫描便宜,则(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;
  • 在接下来的两个查询中,将* key_part1 *与常量进行比较。如果WHERE子句的选择性足以使索引范围扫描比 table 扫描便宜,那么将使用索引:
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;
  • 该查询混合了ASCDESC
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
  • 用于获取行的索引与在ORDER BY中使用的索引不同:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
  • 该查询使用ORDER BY,且 table 达式中包含除索引列名称以外的术语:
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;
  • 该查询联接了许多 table,并且ORDER BY中的列并非全部来自用于检索行的第一个非恒定 table。 (这是EXPLAIN输出中的第一个没有const连接类型的 table。)

  • 该查询具有不同的ORDER BYGROUP BYtable 达式。

  • 仅在ORDER BY子句中命名的列的前缀上存在索引。在这种情况下,索引不能用于完全解析排序 Sequences。例如,如果仅对CHAR(20)列的前 10 个字节进行索引,则索引无法区分第 10 个字节之后的值,因此需要filesort

  • 索引不按 Sequences 存储行。例如,对于MEMORYtable 中的HASH索引,这是正确的。

索引的可用性可能会受到列别名的使用的影响。假设对t1.a列进行了索引。在此语句中,选择列 table 中列的名称为a。它引用t1.a,就像在ORDER BY中引用a一样,因此可以使用t1.a上的索引:

SELECT a FROM t1 ORDER BY a;

在此语句中,选择列 table 中列的名称也是a,但这是别名。它引用ABS(a),就像引用ORDER BY中的a一样,因此不能使用t1.a上的索引:

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

在下面的语句中,ORDER BY引用的名称不是选择列 table 中列的名称。但是t1中有一列名为a,因此ORDER BY指向t1.a且可以使用t1.a上的索引。 (当然,生成的排序 Sequences 可能与ABS(a)的 Sequences 完全不同.)

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

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

如果查询包含GROUP BY,但您希望避免对结果进行排序的开销,则可以通过指定ORDER BY NULL来抑制排序。例如:

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

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

Note

默认情况下,GROUP BY隐式排序(即GROUP BY列没有ASCDESC指示符)。但是,不建议依靠隐式GROUP BY排序(即在没有ASCDESC指示符的情况下进行排序)或对GROUP BY的显式排序(即对GROUP BY列使用显式ASCDESC指示符)。要产生给定的排序 Sequences,请提供ORDER BY子句。

使用文件排序满足 ORDER BY

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

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

如果结果集太大而无法容纳在内存中,则filesort操作会根据需要使用临时磁盘文件。某些类型的查询特别适合完全在内存中的filesort操作。例如,优化器可以使用filesort来有效地在内存中处理ORDER BY操作,而无需使用临时文件,该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;
通过优化影响订单

对于不使用filesort的慢ORDER BY查询,请尝试将max_length_for_sort_data系统变量降低为适合触发filesort的值。 (将此变量的值设置得太高的症状是磁盘活动过多和 CPU 活动较低的组合.)

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

  • 增加sort_buffer_size变量值。理想情况下,该值应足够大以使整个结果集适合排序缓冲区(以避免写入磁盘和合并过程),但该值至少必须足够大以容纳 15 个 Tuples。 (最多合并 15 个临时磁盘文件,并且每个文件中至少必须有一个 Tuples 在内存中.)

考虑到存储在排序缓冲区中的列值的大小受max_sort_length系统变量值的影响。例如,如果 Tuples 存储长字符串列的值,而您增加了max_sort_length的值,则排序缓冲区 Tuples 的大小也会增加,并且可能需要您增加sort_buffer_size。对于由字符串 table 达式(例如调用字符串值函数的结果)计算出的列值,filesort算法无法确定 table 达式值的最大长度,因此必须为每个 Tuples 分配max_sort_length个字节。

要监视合并通过次数(以合并临时文件),请检查Sort_merge_passes status 变量。

  • 增加read_rnd_buffer_size变量值,以便一次读取更多行。

  • 更改tmpdir系统变量以指向具有大量可用空间的专用文件系统。变量值可以列出以循环方式使用的多个路径。您可以使用此功能将负载分散到多个目录中。在 Unix 上用冒号(:)和在 Windows 上用分号(;)分隔路径。路径应命名位于不同物理磁盘上的文件系统中的目录,而不是同一磁盘上的不同分区。

ORDER BY 执行计划信息可用

使用EXPLAIN(请参阅第 8.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,
  "sort_buffer_size": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

sort_mode值提供有关排序缓冲区中 Tuples 内容的信息:

  • <sort_key, rowid>:这 table 明排序缓冲区 Tuples 是对,包含原始 table 行的排序键值和行 ID。Tuples 按排序键值排序,并且行 ID 用于从 table 中读取行。

  • <sort_key, additional_fields>:这 table 明排序缓冲区 Tuples 包含排序键值和查询所引用的列。Tuples 通过排序键值进行排序,并且列值直接从 Tuples 中读取。

  • <sort_key, packed_additional_fields>:与以前的变体一样,但其他列紧密地包装在一起,而不是使用固定长度的编码。

EXPLAIN不能区分优化器是否在内存中执行filesort。在优化器跟踪输出中可以看到内存中filesort的使用。寻找filesort_priority_queue_optimization。有关优化程序跟踪的信息,请参见MySQL 内部:跟踪优化器