mysql order by 算法_MySQL学习——排序算法

简介

本文主要介绍当在MySQL中执行order by时，MySQL使用的排序算法。当在select语句中使用order by语句时，MySQL首先会使用索引来避免执行排序算法；在不能使用索引的情况下，可能使用 快速排序，归并排序 及 堆排序进行排序。

本文中很多地方都是翻译的MySQL官网，英语好的同学可直接查看原文

索引排序

在某些情况下，MySQL可以使用索引来满足ORDER BY子句，从而无需进行额外的排序。

即使ORDER BY与索引不完全匹配，索引也可以使用，只要索引的所有未使用部分和所有额外的ORDER BY列都是WHERE子句中的常量。 以下查询使用索引来解析ORDER BY部分：

SELECT * FROM t1

ORDER BY key_part1, key_part2;

SELECT * FROM t1

WHERE key_part1 = constant

ORDER BY key_part2;

SELECT * FROM t1

ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1

WHERE key_part1 = 1

ORDER BY key_part1 DESC, key_part2 DESC;

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;

在某些情况下，MySQL不能使用索引来解析ORDER BY，尽管它仍然可以使用索引来查找与WHERE子句匹配的行。 例如：

针对查询对不同索引使用ORDER BY(注意：此处的key1和key2是两个完全不同的索引，区别对待上文的第一个例子)：

SELECT * FROM t1 ORDER BY key1, key2;

查询在索引的非连续部分使用ORDER BY：

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part1, key_part3;

查询混合使用ASC和DESC：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

用于获取行的索引与ORDER BY中使用的索引不同(where查询已经打破超出key1所能做的)：

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

查询使用ORDER BY索引列名称以外的术语的表达式(比如sum等)：

SELECT * FROM t1 ORDER BY ABS(key);

SELECT * FROM t1 ORDER BY -key;

查询连接了许多表，ORDER BY中的列不是全部来自用于检索行的第一个非常数表。 (这是EXPLAIN输出中没有const连接类型的第一个表。)

查询使用了不同的ORDER BY和GROUP BY表达式。

索引不按顺序存储行。 例如，对于MEMORY表中的HASH索引。

排序索引的可用性可能会受到列别名的影响。 假设列t1.a被索引。 在此语句中，选择列表中列的名称为a。 它指的是t1.a，与ORDER BY中的a的引用一样，因此可以使用t1.a上的索引：

SELECT a FROM t1 ORDER BY a;

在此语句中，选择列表中列的名称也是a，但它是别名。 它指的是ABS(a)，和在ORDER BY中引用a一样，所以t1.a上的索引不能使用：

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

在以下语句中，ORDER BY引用的名称不是选择列表中列的名称。 但是t1中有一个列命名为a，所以ORDER BY指的是t1.a，可以使用t1.a上的索引。 (当然，结果的排序顺序可能与ABS(a)的顺序完全不同。)

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

默认情况下，如果在查询中指定了ORDER BY col1，col2，...，MySQL会排序所有GROUP BY col1，col2，...查询。

如果查询包含GROUP BY，但是您希望避免排序结果的开销，则可以通过指定ORDER BY NULL来禁止排序。例如：

INSERT INTO foo

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

优化器仍然可以选择使用排序来实现分组操作。 ORDER BY NULL禁止对结果进行排序，而不是通过对操作进行分组来确定结果。

注意：

默认情况下，GROUP BY隐式排序(即在没有ASC或DESC指示符的情况下)，但是依赖隐式GROUP BY排序已被弃用。 要产生给定的排序顺序，请对GROUP BY列使用显式ASC或DESC指示符，或提供ORDER BY子句。 GROUP BY排序是一个MySQL扩展，可能会在将来的版本中更改; 例如，为了使优化器以其认为最有效的方式对分组进行排序并避免排序开销。

排序算法

当order by不能使用索引进行排序时，将使用排序算法进行排序：

若排序内容能全部放入内存，则仅在内存中使用快速排序；

若排序内容不能全部放入内存，则分批次将排好序的内容放入文件，然后将多个文件进行归并排序

3.若排序中包含limit语句，则使用堆排序优化排序过程

注意：

MySQL是在5.6后引入堆排序来优化limit子句，但堆排序是非稳定的(对于相同的key值，无法保证排序后与排序前的位置一致)，所以导致分页重复的现象。为了避免这个问题，我们可以在排序中加上唯一值，比如主键id，这样由于id是唯一的，确保参与排序的key值不相同。

例：SELECT * FROM ratings ORDER BY category, id LIMIT 5;

原始文件排序算法

根据键或表扫描读取所有行。跳过不符合WHERE子句的行。

对于每一行，在排序缓冲区中存储由一对值(排序键值和行ID)组成的元组。

如果所有对都适合排序缓冲区，则不会创建临时文件。否则，当排序缓冲区变满时，在内存中运行快速排序并将其写入临时文件。保存指向排序块的指针。

重复上述步骤，直到读取所有行。

在多个MERGEBUFF(7)区域中进行多次合并到另一个临时文件中的一个块。重复，直到第一个文件的所有块都在第二个文件中。

重复以下操作，直到剩下少于MERGEBUFF2(15)个块。

在最后一次多合并时，只将行ID(值对的最后一部分)写入结果文件。

使用结果文件中的行ID按排序顺序读取行。要优化此操作，请读取大块行ID，对它们进行排序，并使用它们以排序顺序将行读入行缓冲区。行缓冲区大小是read_rnd_buffer_size系统变量值。

这种方法的一个问题是它读取行两次：一次在WHERE子句评估期间，并在排序值对之后再次。 即使第一次连续地访问了行(例如，如果表扫描完成)，则第二次被随机访问。 (排序键是有序的，但行位置不是。)

改进的文件排序算法

改进的文件排序算法包含一个优化，以避免读取行两次：它记录排序键值，但不记住行ID，它记录查询列的引用。 修改的filesort算法的工作原理如下：

读取与WHERE子句匹配的行。

对于每一行，在排序缓冲区中存储由排序键值和查询引用的列组成的元组。

当排序缓冲区变满时，通过内存中的排序键值对元组进行排序，并将其写入临时文件。

在合并排序临时文件之后，以排序顺序检索行，但是从排序的元组中直接读取查询所需的列，而不是再次访问该表。

由改进的文件排序算法使用的元组比原始算法使用的对象长，并且在排序缓冲区中有更少的匹配。 因此，额外的I/O可能使修改后的方法变得更慢，而不是更快。 为避免减速，优化器仅在排序元组中的额外列的总大小不超过max_length_for_sort_data系统变量的值时才使用改进算法。(将此变量的值设置得太高的一个症状是高磁盘活动和CPU活动低的组合。)

改进的文件排序算法包括额外的优化，旨在使更多的元组适合排序缓冲区：对于类型为CHAR或VARCHAR的其他列或任何可空固定大小的数据类型，这些值将被打包。 例如，不包装，只有3个字符的VARCHAR(255)列值在排序缓冲区中需要255个字符。 打包时，该值只需3个字符，加上两个字节的长度指示符。 NULL值只需要一个位掩码。

参考文献