8.2.1.14 按优化 Sequences
本节描述了 MySQL 何时可以使用索引满足ORDER BY
子句,何时无法使用索引时使用filesort
操作以及优化器可提供的关于ORDER BY
的执行计划信息。
如第 8.2.1.17 节“ LIMIT 查询优化”中所述,具有LIMIT
和不具有LIMIT
的ORDER 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 *
仅选择索引列,则将使用索引并避免排序。
如果t1
是InnoDB
table,则 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;
- 该查询混合了
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
,且 table 达式中包含除索引列名称以外的术语:
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;
-
该查询联接了许多 table,并且
ORDER BY
中的列并非全部来自用于检索行的第一个非恒定 table。 (这是EXPLAIN输出中的第一个没有const连接类型的 table。) -
该查询具有不同的
ORDER BY
和GROUP BY
table 达式。 -
仅在
ORDER BY
子句中命名的列的前缀上存在索引。在这种情况下,索引不能用于完全解析排序 Sequences。例如,如果仅对CHAR(20)列的前 10 个字节进行索引,则索引无法区分第 10 个字节之后的值,因此需要filesort
。 -
索引不按 Sequences 存储行。例如,对于
MEMORY
table 中的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
列没有ASC
或DESC
指示符)。但是,不建议依靠隐式GROUP BY
排序(即在没有ASC
或DESC
指示符的情况下进行排序)或对GROUP BY
的显式排序(即对GROUP BY
列使用显式ASC
或DESC
指示符)。要产生给定的排序 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 内部:跟踪优化器。