MySQL ORDER BY是如何执行的

2020-05-15  本文已影响0人  月饮沙

本文问题

  1. order by是否可以使用索引，在什么情况下使用索引？
  2. 在哪些情况下，order by无法使用索引执行？
  3. filesort有几种类型？过程是怎样的？
  4. 哪些参数会影响order by的执行过程？
  5. 如何查看order by的执行计划？
  6. 如何查看filesort是否在内存中执行？
  7. 在什么情况下可以考虑增加sort_buffer_size？
  8. MySQL8.0对ORDER BY操作做了哪些额外的优化？

使用索引的ORDER BY

B TREE索引是有序排列的。所以在执行ORDER BY操作时，可以使用索引来避免使用额外的排序操作。

即使ORDER BY没有精确地与索引相匹配，只要所有索引未使用的部分和所有ORDER BY中额外的列在WHERE子句中是一个常量。如果索引不包含查询中访问的索引列，只有当索引访问效率高于其他访问模式时才使用索引。

不一定使用索引的情况

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

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;

使用索引的情况

SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;

无法使用索引的情况

这里的无法使用索引是指不使用索引进行排序（ORDER BY）操作。但是仍然可能使用索引来检索和WHERE子句匹配的行

语句的问题

索引的问题

使用文件排序的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的性能，检查是否能否使用索引，来避免额外的排序操作，如果无法使用索引，尝试以下策略：

相关系统变量

ORDER BY执行计划

可以通过EXPLIAN查看ORDER BY是否使用了索引：

"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值提供了关于排序缓冲区中元组内容的信息：

EXPLAIN无法区分filesort是否在内存中执行。可以通过优化器跟踪输出来查看filesort的内存使用。查看filesort_priority_queue_optimization

问题答案

  1. order by是否可以使用索引，在什么情况下使用索引？
    B树索引是有序排列的，对于InnoDB表，主键（聚集索引）存储了行的数据，其他的辅助索引中存储了辅助索引指定的列以及主键列。当通过辅助索引检索时，首先通过辅助索引获取到主键的值，然后再通过主键获取其他不在辅助索引中的列值。
    因为索引是有序的，所以在ORDER BY排序的时候可以使用索引来进行优化，当ORDER BY的列被索引覆盖时，就可能会使用索引。分为以下几种情况：
  1. 在哪些情况下，order by无法使用索引执行？
  1. filesort有几种类型？过程是怎样的？
    3种。sort_key + rowid / additional_fields / packed_additional_fields
    过程：首先要扫描所有符合条件的记录，保存到排序缓冲区中（大小等于sort_buffer_size），如果超过了排序缓冲区的大小，就保存到临时磁盘文件中，然后对缓冲区中的记录进行排序，将排序后的结果返回给客户端
    这里的记录有三种类型，首先必然要将要排序的列保存到排序缓冲区中，然后，根据max_length_for_sort_data的大小，决定是将所有要查询的列保存到排序缓冲区中，还是只将主键保存到排序缓冲区中。如果只将主键保存到缓冲区中，之后还需要根据主键再回表查询一次其他要查询的列值。
  2. 哪些参数会影响order by的执行过程？
    sort_buffer_size：如果使用filesort，该参数影响是否在内存中进行排序。
    max_length_for_sort_data:影响文件排序时，是否将所有要查询的列都存储到缓冲区中。也会间接影响是否在内存中进行排序。如果要查询的列的长度大于max_length_for_sort_data，只将要排序的列和主键存储到缓冲区中，这种情况下占用的缓冲区较小，可能会将原本的磁盘文件排序变更为内存文件排序。
    max_sort_length：影响字符串的排序结果，只对字符串的前max_sort_length字节值排序并忽略剩余的部分。
  3. 如何查看order by的执行计划？
    EXPLIAN，结果中没有Using filesort表示排序使用了索引，结果包含Using filesort表示没有使用索引，使用filesort。
  4. 如何查看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的类型
}
  1. 在什么情况下可以考虑增加sort_buffer_size？
    在MySQL8.0.12以前，当在SHOW GLBOAL STATUS的输出中，显示每秒有很多Sort_merge_passes，表示有很多磁盘的filesort，在这种情况下可以考虑增加sort_buffer_size。
  2. 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的全局变量设置为一个较大的值，这样可以满足需要大的缓冲区的排序操作，也不会增加服务器内存负担，不会降低小排序的性能。
上一篇下一篇

猜你喜欢

热点阅读