通过优化排序
本节描述了MySQL何时可以使用索引满足ORDER BY
子句,无法使用索引时使用的filesort
操作,以及有关优化程序的执行计划信息ORDER BY
。
一个ORDER BY
有和没有LIMIT
可能以不同的顺序返回行,在讨论第8.2.1.19,“LIMIT查询优化”。
- 使用索引满足ORDER BY
- 使用文件排序满足ORDER BY
- 通过优化影响订单
- ORDER BY执行计划信息可用
使用索引满足ORDER BY
在某些情况下,MySQL可能使用索引来满足ORDER BY
子句,并避免执行filesort
操作时涉及的额外排序。
即使ORDER BY
索引与索引不完全匹配,也可以使用索引,只要索引的所有未使用部分和所有额外的ORDER BY
列在WHERE
子句中都是常量即可。如果索引不包含查询访问的所有列,则仅当索引访问比其他访问方法便宜时才使用索引。
假设在上有一个索引,以下查询可以使用该索引来解析零件。优化器是否实际上这样做取决于如果还必须读取索引中没有的列,则读取索引是否比表扫描更有效。(key_part1,key_part2)
ORDER BY
在此查询中,索引on 使优化器避免排序:
(key_part1,key_part2)
SELECT *FROM t1ORDER BY key_part1, key_part2;但是,查询使用
SELECT *
,它可能选择的列多于key_part1
和key_part2
。在这种情况下,扫描整个索引并查找表行以查找索引中未包含的列可能比扫描表并对结果进行排序要昂贵。如果是这样,优化器可能不会使用索引。如果SELECT *
仅选择索引列,则将使用索引并避免排序。如果
t1
是InnoDB
表,则表主键隐式属于索引的一部分,并且该索引可用于解析ORDER BY
此查询:SELECT pk, key_part1, key_part2FROM t1ORDER BY key_part1, key_part2;在此查询中,它
key_part1
是常量,因此通过索引访问的所有行都是key_part2
有序的,并且如果子句具有足够的选择性以使索引范围扫描比表扫描便宜,则索引on 可以避免排序:(key_part1,key_part2)
WHERE
SELECT *FROM t1WHERE key_part1 = constantORDER BY key_part2;在接下来的两个查询中,是否使用索引与
DESC
前面没有显示的相同查询类似:SELECT *FROM t1ORDER BY key_part1DESC , key_part2DESC ;SELECT *FROM t1WHERE key_part1 = constantORDER BY key_part2DESC ;中的两列
ORDER BY
可以沿相同方向(两个ASC
或两个DESC
)排序,也可以沿相反方向(一个ASC
,一个DESC
)排序。使用索引的条件是索引必须具有相同的同质性,但不必具有相同的实际方向。如果查询混合使用
ASC
和DESC
,并且索引还使用相应的混合升序和降序列,则优化器可以在列上使用索引:SELECT *FROM t1ORDER BY key_part1DESC , key_part2ASC ;如果降序和升序
key_part1
,优化器可以在(key_part2
)上使用索引。如果它是升序还是降序,它也可以在这些列上使用索引(向后扫描)。请参见“降序索引”key_part1
key_part2
key_part1
key_part2
在接下来的两个查询中,
key_part1
将其与常量进行比较。如果WHERE
子句的选择性足以使索引范围扫描比表扫描便宜,那么将使用索引:SELECT *FROM t1WHERE key_part1 > constantORDER BY key_part1ASC ;SELECT *FROM t1WHERE key_part1 < constantORDER BY key_part1DESC ;在下一个查询中,
ORDER BY
不会命名key_part1
,但是所有选择的行都有一个常key_part1
量值,因此仍可以使用索引:SELECT *FROM t1WHERE key_part1 = constant1 AND key_part2 > constant2ORDER BY key_part2;
在某些情况下,MySQL 不能使用索引来解析ORDER BY
,尽管它仍然可以使用索引来查找与该WHERE
子句匹配的行。例子:
该查询用于
ORDER BY
不同的索引:SELECT *FROM t1ORDER BY key1, key2;该查询
ORDER BY
对索引的非连续部分使用:SELECT *FROM t1WHERE key2=constantORDER BY key1_part1, key1_part3;用于获取行的索引与在中使用的索引不同
ORDER BY
:SELECT *FROM t1WHERE key2=constantORDER BY key1;该查询使用
ORDER BY
的表达式包含除索引列名称以外的术语:SELECT *FROM t1ORDER BY ABS(key);SELECT *FROM t1ORDER BY -key;- 该查询联接了许多表,并且中的列
ORDER BY
并非全部来自用于检索行的第一个非恒定表。(这是EXPLAIN
输出中没有const
联接类型的第一个表。) - 查询具有
ORDER BY
和GROUP BY
表达式。 - 仅在
ORDER BY
子句中命名的列的前缀上存在索引。在这种情况下,索引不能用于完全解析排序顺序。例如,如果仅CHAR(20)
索引一列的前10个字节,则索引无法区分第10个字节之后的值,filesort
因此需要a。 - 索引不按顺序存储行。例如,对于表中的
HASH
索引来说就是这样MEMORY
。
使用列别名可能会影响索引排序的可用性。假设该列t1.
已建立索引。在此语句中,选择列表中列的名称为。它指的是t1.
,如同在参考在ORDER BY
,所以上的索引t1.
可用于:
SELECT aFROM t1ORDER BY ;
在此语句中,选择列表中列的名称也为,但它是别名。它指的是ABS(a)
,如同在参考在ORDER BY
,所以上的索引t1.
不能使用:
SELECT ABS( )AS aFROM t1ORDER BY ;
在下面的语句中,ORDER BY
引用的名称不是选择列表中列的名称。但是t1
named中有一个列,因此可以使用ORDER BY
Refers t1.
和on上的索引t1.
。(当然,生成的排序顺序可能与的排序顺序完全不同ABS(a)
。)
SELECT ABS( )AS bFROM t1ORDER BY ;
以前(MySQL 5.7及更低版本),GROUP BY
在某些条件下隐式排序。在MySQL 8.0中,这种情况不再发生,因此ORDER BY NULL
不再需要在末尾进行指定以禁止隐式排序(如前所述)。但是,查询结果可能与以前的MySQL版本不同。要产生给定的排序顺序,请提供一个ORDER BY
子句。
使用文件排序满足ORDER BY
如果不能使用索引来满足ORDER BY
子句,则MySQL执行filesort
读取表行并对它们进行排序的操作。A 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
,请尝试将max_length_for_sort_dat
系统变量降低为适合触发的值filesort
。(将此变量的值设置得太高的症状是高磁盘活动和低CPU活动的组合。)此技术仅在MySQL 8.0.20之前适用。从8.0.20版本开始,max_length_for_sort_dat
由于优化程序的更改而使它过时且没有效果,因此不推荐使用。
为了提高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
(请参见“使用EXPLAIN优化查询”),可以检查MySQL是否可以使用索引来解析ORDER BY
子句:
- 如果输出的
Extr
列EXPLAIN
不包含Using filesort
,则使用索引,filesort
而不执行a。 - 如果输出的
Extr
列EXPLAIN
包含Using filesort
,则不使用索引并filesort
执行a。
此外,如果filesort
执行a,则优化器跟踪输出将包含一个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_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
。有关优化程序跟踪的信息,请参见 MySQL内部:跟踪优化程序。