介绍

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

 

一个ORDER BY有和没有 LIMIT可能以不同的顺序返回行：

 

使用索引来优化ORDER BY

使用filesort来优化ORDER BY

影响ORDER BY优化

可用的执行计划信息收集

使用索引来优化ORDER BY

在某些情况下，MySQL可能会使用索引来优化一个 ORDER BY子句，并避免执行filesort 操作时涉及的额外排序。

 

即使索引与order by子句与索引不完全匹配一样可以使用索引，只要索引未被使用的部分和ORDER BY额外的列包含在WHERE条件里。假如索引不包含查询中的所有访问到的列，仅当索引访问比其他访问方法代价更低时才使用索引。

 

假设有一个索引(key_part1, key_part2),以下查询可能会使用索引解决ORDER BY的部分。如果还必须读取索引中不存在的列，优化程序是否实际执行此操作取决于读取索引是否比表扫描更有效。

 

在下面这个查询中，这个索引(key_part1, key_part2)启用优化程序，避免排序：

 

  SELECT * FROM t1

    ORDER BY key_part1, key_part2;

1

2

然而，这个查询使用SELECT *, 他可能使用比(key_part1 and key_part2)更多的列。在这种情况下，扫描整个索引，然后再去获取全部数据，可能比获取全部数据然后在排序的代价更高。所以，这个优化器可能不适用索引。假如将SELECT * 变成只查询索引字段,查询将使用索引并且避免排序。

 

假如t1使用InnoDB引擎，这个表的主键字段是这个索引的隐式字段，所以可以通过索引优化ORDER BY：

 

  SELECT pk, key_part1, key_part2 FROM t1

    ORDER BY key_part1, key_part2;

1

2

在下面这个查询中，key_part1是一个常量值，所以通过索引访问的所有行的key_part2是有序的，并且(key_part1, key_part2)避免了排序，因为where条件有足够的选择性来执行ndex range scan，所以使用索引的代价比table scan的代价小。

 

  SELECT * FROM t1

    WHERE key_part1 = constant

    ORDER BY key_part2;

1

2

3

在下面了两个查询中，是否使用索引类似于前面没有DESC的相同查询

 

  SELECT * FROM t1

    ORDER BY key_part1 DESC, key_part2 DESC;

  

  SELECT * FROM t1

    WHERE key_part1 = constant

    ORDER BY key_part2 DESC;

1

2

3

4

5

6

ORDER BY后可以跟两个相同的描述符（全是ASC或者全是DESC），或者跟两个不同的描述符（一个ASC一个DESC），索引使用的条件是索引必须具有相同的同质性，但不一定要求有相同的方向。

 

假如一个查询混合了ASC和DESC，优化器可以使用一个和列相同的混合了ASC和DESC的索引：

 

  SELECT * FROM t1

  ORDER BY key_part1 DESC, key_part2 ASC;

1

2

优化器可以在（key_part1，key_part2）上使用索引， 如果 key_part1是降序并且 key_part2正在升序。它还可以在这些列上使用索引（使用向后扫描），如果它key_part1是升序和key_part2降序。

 

在下面两个查询中，key_part1的条件是常量值，假如WHERE条件具有足够的选择性使用索引的范围扫描比全表扫描的代价更小，那么下面的SQL将使用索引

 

  SELECT * FROM t1

    WHERE key_part1 > constant

    ORDER BY key_part1 ASC;

  

  SELECT * FROM t1

    WHERE key_part1 < constant

    ORDER BY key_part1 DESC;

1

2

3

4

5

6

7

在下面这个查询中，ORDER BY不包含key_part1，不过有key_part1 = constant1这个条件，所以仍然使用索引：

 

  SELECT * FROM t1

    WHERE key_part1 = constant1 AND key_part2 > constant2

    ORDER BY key_part2;

1

2

3

在某些情况下，MYSQL无法使用索引解决ORDER BY的问题，但是假如可以通过WHERE条件减少查询的数量，MYSQL依然会使用索引：

 

下面这个查询的ORDER BY 使用不同的索引：

 

  SELECT * FROM t1 ORDER BY key1, key2;

1

下面这个ORDER BY使用索引的不同部分：

 

  SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

1

用于获取行的索引与ORDER BY中使用的索引不同

 

  SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

1

ORDER BY包含一个索引列之外的表达式：

 

  SELECT * FROM t1 ORDER BY ABS(key);

  SELECT * FROM t1 ORDER BY -key;

1

2

一个包含很多表的JOIN操作，ORDER BY中的列并非全部来自用于检索行的第一个非常量表。（在第一个表的EXPALIN输出中，不会包含const这个join类型）。

 

一个查询包含不同的ORDER BY和GROUP BY表达式。

 

在ORDER BY的条件中只有列的前缀包含索引。在这种情况下，索引不能完全解决排序问题。举例，在char(20)的字段中只有前10个有索引，索引无法区分超过10个字节的值，而且必须使用filesort。

 

索引可能不按照顺序存储行。比如，在MEMORY表中的HASH索引。

 

列的别名可能影响在排序时的索引的使用。假如t1.a被定义为索引，在这个声明中，select语句中的是a，他指代的是t1.a，和ORDER BY的引用一样，所以这个t1.a的索引可以使用。

 

SELECT a FROM t1 ORDER BY a;

1

在这个声明中，查询的列仍然是a但是他是ABS(a)的别名，然后ORDER BY a的意思是ORDER BY ABS(a)，所以他无法使用索引：

 

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

1

下面这个例子中，ORDER BY中依赖的字段并没有在SELECT中的别名不相同，所以他仍然可以使用索引：

 

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

1

在以前的版本中，GROUP BY可能包含隐式排序，在MYSQL8.0中，这个已经不会再发生，因此，不再需要在末尾指定ORDER BY NULL来抑制隐式排序，但是现在的版本结果可能和以前版本不同，假如有需要请指定ORDER BY条件。

 

使用filesort优化ORDER BY

假如索引无法满足ORDER BY的条件，MySQL会使用filesort操作读取表中的行并进行排序。filesort是构成一个额外的阶段在所执行的查询中。

 

为了获得filesort操作的内存，从MySQL 8.0.12开始，优化器根据需要逐步分配内存缓冲区，最大为sort_buffer_size设置的大小。而不是像以前的版本中，提前分配 sort_buffer_size 的指定大小的固定的内存缓冲区。这个参数 sort_buffer_size 可以设置更大的值来跑更大的排序，而不用担心较小的排序占用较大的内存。（对于Windows上的多个并发排序，可能不会出现此优势，因为它具有较弱的多线程malloc。）

 

一个filesort可能需要申请临时的硬盘空间，假如排序的数据太大以至于内存无法装下的时候。有些类型的排序操作非常适用于内存中的filesort操作。举个例子，优化器可以在内存中有效的处理filesort操作，而不需要临时文件，以下形式的查询（和子查询）的ORDER BY操作：

 

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

1

此类查询在Web应用程序中很常见，只显示较大结果集中的几行。例子：

 

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;

SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

1

2

影响ORDER BY优化

对于一个没有使用filesort的比较慢的ORDER BY 查询，可以尝试降低max_length_for_sort_data的值，用来适当的触发filesort。（将这个值设置的过高的表现是过高的磁盘活动以及很低的CPU使用率。）

 

为了提高ORDER BY的速度，检查是否可以让MySQL使用索引而不是额外的排序阶段。假如无法使用索引，可以尝试以下方法：

 

提高 sort_buffer_size 的值。理想情况下应该使值足够大，让所有的排序操作在内存中的排序缓冲区中进行（避免写入磁盘和合并传递）。

 

考虑到存储在排序缓冲区中的列值的大小受max_sort_length系统变量值的影响。例如：如果元祖存储的长字符串的列的值，并且增加了max_sort_length，那么排序缓冲区的元组也需要增加，并且可能需要提高sort_buffer_size的值。

 

监控merge passes的数量（用于合并临时文件），并且检查Sort_merge_passes变量的值。

 

提高read_rnd_buffer_size的变量的值可以同时读取更多的行数。

 

将tmpdir系统变量更改为指向具有大量可用空间的专用文件系统。这个变量可以同时循环使用多个路径。您可以使用此功能将负载分散到多个目录中。在UNIX中使用(:)分割，在WINDOWS使用(;)分割。这个路径应该是本地多个不同的物理磁盘，而不是同一个磁盘的不同目录。

 

可用的执行计划信息收集

在EXPLAIN，可以判断ORDER BY是否使用了索引：

 

在EXPLAIN的Extra的输出中不包含 filesort ，那么索引被使用并且没有使用filesort。

在EXPLAIN的Extra的输出中包含 filesort ，那么索引未被使用并且使用了filesort。

此外，如果filesort被使用，优化器的trace输出包含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>"

}

1

2

3

4

5

6

7

peak_memory_used表示排序期间任何一次使用的最大内存。这个值需要增加，但并不需要与sort_buffer_size一样大。在MySQL 8.0.12之前，输出显示sort_buffer_size，指示sort_buffer_size的值。（在MySQL 8.0.12之前，优化器总是为sort缓冲区分配sort_buffer_size字节。从8.0.12开始，优化器逐步分配排序缓冲区内存，从较少的值并且慢慢增加，最多为sort_buffer_size字节。）

 

sort_mode值提供有关排序缓冲区中元组内容的信息：

 

<sort_key, rowid>: 这表示排序缓冲区元组是包含原始表行的排序的key值和行rowid。通过key排序然后通过rowid查询数据。

<sort_key, additional_fields>:这表示排序缓冲区元组是包含原始表行的排序的key值和查询引用的列。元组按排序键值排序，列值直接从元组中读取。

<sort_key, packed_additional_fields>: 与前一个版本一样，但是其他列紧密排列在一起，而不是使用固定长度编码。

EXPLAIN不区分优化器是否在内存中执行了filesort。

--------------------- 

作者：刺青LOVELESS 

来源：CSDN 

原文：https://blog.csdn.net/ciqingloveless/article/details/83866364 

版权声明：本文为博主原创文章，转载请附上博文链接！