本文问题
-
order by是否可以使用索引，在什么情况下使用索引？ - 在哪些情况下，
order by无法使用索引执行？ -
filesort有几种类型？过程是怎样的？ - 哪些参数会影响
order by的执行过程？ - 如何查看
order by的执行计划？ - 如何查看
filesort是否在内存中执行？ - 在什么情况下可以考虑增加
sort_buffer_size？ - MySQL8.0对
ORDER BY操作做了哪些额外的优化？
使用索引的ORDER BY
B TREE索引是有序排列的。所以在执行ORDER BY操作时，可以使用索引来避免使用额外的排序操作。
即使ORDER BY没有精确地与索引相匹配，只要所有索引未使用的部分和所有ORDER BY中额外的列在WHERE子句中是一个常量。如果索引不包含查询中访问的索引列，只有当索引访问效率高于其他访问模式时才使用索引。
不一定使用索引的情况
- 查找整个表并按照索引列排序
对于查询整个表的操作，MySQL会衡量索引扫描（根据辅助索引找到主键，再根据主键，获取其他不在索引中的列的内容）效率是否高于全表扫描并排序，选择效率更高的方式，可能不会使用索引。
SELECT * FROM t1
ORDER BY key_part1, key_part2;
- 索引的一部分在
WHERE子句中，另一部分在ORDER BY子句中
key_part1是一个常量，所以所有的行可以通过key_part2索引顺序访问。如果WHERE子句的选择度较高，使用索引范围扫描的效率高于全表扫描，就可以使用索引。
SELECT * FROM t1
WHERE key_part1 = constant
ORDER BY key_part2;
SELECT * FROM t1
WHERE key_part1 > constant
ORDER BY key_part1 ASC;
SELECT * FROM t1
WHERE key_part1 < constant
ORDER BY key_part1 DESC;
SELECT * FROM t1
WHERE key_part1 = constant1 AND key_part2 > constant2
ORDER BY key_part2;
使用索引的情况
- 查找主键和辅助索引中的列并按索引排序
对于InnoDB表，由于辅助索引中存储的是主键的记录，查询主键和辅助索引中的列并按照辅助索引排序，可以使用辅助索引。
SELECT pk, key_part1, key_part2 FROM t1
ORDER BY key_part1, key_part2;
无法使用索引的情况
这里的无法使用索引是指不使用索引进行排序（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 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key; - 语句具有不同的
ORDER BY和GROUP BY表达式
因为默认情况下，GROUP BY something隐含的包括了ORDER BY something进行排序。如果不需要排序，可以使用ORDER BY NULL; - 在多表联结查询中，
ORDER BY中的列不是
索引的问题
- 用来获取行数据的索引和
ORDER BY中的索引不一致
SELECT * FROM t1 WHERE key2=constant ORDER BY key1; -
ORDER BY中的列只有一个前缀索引。
前缀索引只包括列中的部分内容，无法使用前缀索引进行排序。 - 索引不按照顺序存储记录，例如
MEMORY存储引擎的HASH索引
使用文件排序的ORDER BY
如果ORDER BY无法使用索引，MySQL会执行文件排序（filesort）操作：读取表中的每一行并进行排序。filesoft可以被视为是语句执行过程中的额外排序操作。
为了获取filesort操作的内存，优化器分配固定的sort_buffer_size大小的内存。sort_buffer_size是会话级的，可以动态修改。
如果结果集太大，内存容纳不下全部结果，filesort操作会使用临时磁盘文件。一些类型的查询特别适合使用内存filesort操作。例如：优化器可以使用内存filesort来
SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;
如何提高ORDER BY的性能
对于未使用filesort的ORDER BY，可以降低max_length_for_sort_data的值来触发文件排序。（max_length_for_sort_data过高可能导致磁盘活动较高但CPU利用率较低）
要提高ORDER BY的性能，检查是否能否使用索引，来避免额外的排序操作，如果无法使用索引，尝试以下策略：
- 增加
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的值，以便一次读取更多行 - 更改
tmpdir变量，将其指向具有更大可用空间的专用文件系统。
相关系统变量
-
max_length_for_sort_data
决定使用哪种filesort算法的索引值的临界点。 -
max_sort_length
在排序数据值时，使用多少个字节进行排序。数据库只排序值的前max_sort_length字节值并忽略剩余部分。这意味着，在GROUP BY,ORDER BY和DISTINCT操作中，在max_sort_length字节之后的不同会被认为是相同的。 -
sort_buffer_size
在MySQL8.0.12以前的版本中，每个必须执行排序的操作都会获取这么大的排序缓冲区。排序缓冲区不是特定于某个存储引擎的。排序缓冲区的大小至少需要可以容纳15个元组。此外，增加max_sort_length的大小可能会需要增加sort_buffer_size的大小。
如果在SHOW GLBOAL STATUS的输出中显示每秒有很多Sort_merge_passed，可以考虑增加sort_buffer_size的大小来提高ORDER BY和GROUP BY的速度。
优化器会尝试计算出必须使用多少空间，但是会分配更多空间（最大限制）。将其设置为大于全局所需的大小将减慢大多数此类查询的速度。最好只当会话需要大值的时候，为会话变量增加该值大小。
在MySQL8.0.12及以后的版本中，排序缓冲区是动态增量分配的，sort_buffer_size是最大内存大小限制。
ORDER BY执行计划
可以通过EXPLIAN查看ORDER BY是否使用了索引：
- 如果
EXPLIAN输出的Extra列不包括Using filesort，使用了索引 - 如果
EXPLAIN输出的Extra列包括Using 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值提供了关于排序缓冲区中元组内容的信息：
-
<sort_key , rowid>
排序缓冲区元组是包括排序键值和每个表的行ID的一堆值。元组按照键值排序并且使用row ID来中表中读取行 -
<sort_key, additional_fields>
排序缓冲区元组是包括排序键值和查询中相关的列。元组按照键值排序，并且直接从元组中读取列值 -
<sort_key, packed_additional_fields>
上一个的变体，只是其他列packed tightly togethe而不是使用固定长度编码
EXPLAIN无法区分filesort是否在内存中执行。可以通过优化器跟踪输出来查看filesort的内存使用。查看filesort_priority_queue_optimization
问题答案
-
order by是否可以使用索引，在什么情况下使用索引？
B树索引是有序排列的，对于InnoDB表，主键（聚集索引）存储了行的数据，其他的辅助索引中存储了辅助索引指定的列以及主键列。当通过辅助索引检索时，首先通过辅助索引获取到主键的值，然后再通过主键获取其他不在辅助索引中的列值。
因为索引是有序的，所以在ORDER BY排序的时候可以使用索引来进行优化，当ORDER BY的列被索引覆盖时，就可能会使用索引。分为以下几种情况：
-
ORDER BY的列完全被索引覆盖，并且查询的列在该索引中或者额外包括主键。这种情况下，由于索引中也包括了主键的值，可以直接使用索引来进行查询。 -
ORDER BY的列在索引中，但是查询的列包括了其他不在该索引中的列值。这种情况下，优化器会对比索引扫描和全表扫描的效率，选择认为更高效的方式，不一定会使用索引。
- 在哪些情况下，
order by无法使用索引执行？
- 索引无序时
- 索引不是顺序存储记录的，比如索引是哈希结构的
- 索引是一个前缀索引，只对一个字符串的前几个字符进行了索引，这种情况下索引不是完全按照列值顺序排列的
- 要查询的列不符合多列索引的最左前缀原则
- 语句无法使用索引时
- 索引冲突
-
ORDER BY中的列不在同一个索引之中 -
ORDER BY和WHERE子句中的列在不同的索引中 -
ORDER BY和GROUP BY的列不一致
-
- 语句问题无法使用索引
-
ORDER BY中使用了表达式
-
-
ORDER BY中混合使用了ASC和DESC - 多表联结查询，
ORDER BY中的列不全都来自于第一个用来查询数据的非恒定表。
- 索引冲突
-
filesort有几种类型？过程是怎样的？
3种。sort_key + rowid / additional_fields / packed_additional_fields
过程：首先要扫描所有符合条件的记录，保存到排序缓冲区中（大小等于sort_buffer_size），如果超过了排序缓冲区的大小，就保存到临时磁盘文件中，然后对缓冲区中的记录进行排序，将排序后的结果返回给客户端
这里的记录有三种类型，首先必然要将要排序的列保存到排序缓冲区中，然后，根据max_length_for_sort_data的大小，决定是将所有要查询的列保存到排序缓冲区中，还是只将主键保存到排序缓冲区中。如果只将主键保存到缓冲区中，之后还需要根据主键再回表查询一次其他要查询的列值。 - 哪些参数会影响
order by的执行过程？
sort_buffer_size：如果使用filesort，该参数影响是否在内存中进行排序。
max_length_for_sort_data:影响文件排序时，是否将所有要查询的列都存储到缓冲区中。也会间接影响是否在内存中进行排序。如果要查询的列的长度大于max_length_for_sort_data，只将要排序的列和主键存储到缓冲区中，这种情况下占用的缓冲区较小，可能会将原本的磁盘文件排序变更为内存文件排序。
max_sort_length：影响字符串的排序结果，只对字符串的前max_sort_length字节值排序并忽略剩余的部分。 - 如何查看
order by的执行计划？
EXPLIAN，结果中没有Using filesort表示排序使用了索引，结果包含Using filesort表示没有使用索引，使用filesort。 - 如何查看
filesort是否在内存中执行？
使用优化器跟踪
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* 结果 */
"filesort_summary": {
"rows": 100, # 要查询的结果行数
"examined_rows": 100, #实际查询的行数，如果类型是sort_key,rowid，由于排序后还需要使用rowid再去表中查询其他列的内容，可能比要查询的结果行数更多
"number_of_tmp_files": 0, #使用的临时文件数量，大于0表示排序在磁盘中执行
"sort_buffer_size": 25192, #MySQL8.0.12以前 排序缓冲区大小
# "peak_memory_used": 25192, # MySQL8.0.12及以后，表示在排序过程中使用的最大内存值
"sort_mode": "<sort_key, packed_additional_fields>" #filesort的类型
}
- 在什么情况下可以考虑增加
sort_buffer_size？
在MySQL8.0.12以前，当在SHOW GLBOAL STATUS的输出中，显示每秒有很多Sort_merge_passes，表示有很多磁盘的filesort，在这种情况下可以考虑增加sort_buffer_size。 -
MySQL8.0对ORDER BY操作做了哪些额外的优化？
更改了sort_buffer_size的作用
在MySQL8.0.12以前，为每个排序操作固定的分配sort_buffer_size大小的排序缓冲区， 在这种情况下，如果将sort_buffer_size的全局变量值设置的过大，会分配额外的内存，可能会降低其他操作的性能。从MySQL8.0.12开始，按照实际大小分配排序缓冲区直到sort_buffer_size大小。可以将sort_buffer_size的全局变量设置为一个较大的值，这样可以满足需要大的缓冲区的排序操作，也不会增加服务器内存负担，不会降低小排序的性能。
网友评论