mysql5.7官网直译SQL语句优化--排序优化

1.13ORDER BY Optimization 排序优化。
这部分描述当mysql能够使用一个索引去满足排序条件，filesort算法会被使用如果一个索引不能用的时候，和执行计划的信息关于优化器对于order by的可用信息。
>使用索引来排序
>优化器使用文件排序
>原始的文件排序算法
>修改后的文件排序算法
>在内存中的文件排序算法
>文件排序算法的对比
>排序优化的影响
>可用的排序执行计划信息
使用索引来排序
在一些情况下，mysql能够用索引来满足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条件的行。
例如：
1）查询使用order by条件在不同的索引上：
SELECT * FROM t1 ORDER BY key1, key2;
2）查询使用order by条件在一个索引上但是并不是连续的部分(联合索引的多个部分)：
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part1, key_part3;
3）查询混合了升序和降序：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
4）被用于查询的索引列和被用于排序的是不同的行：
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
5）查询中使用的order by条件是表达式，且表达式中包含的不是索引列的名字，而是表达式：
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;
6）查询连接了多张表，而在order by的列不是全部来自第一个非常量表，用来查询行。(这种情况下，在执行计划的输出中第一张表的连接类型不是const)
7) 查询中使用了不同的order by和group by表达式。
8）存在一个索引只是排序列上的一部分前缀，在这种情况下，所以不能完全解决排序部分，比方说，在一个char(20)的列上有一个前10字节组成的索引，那么索引是不能区分查过第十字节的部分数据的所以依然需要一个文件排序。
9）索引没有有序的存储行。例如，内存表中采用哈希索引存储的表。
可以用于排序的索引有可能会受到使用列的别名的影响。假设列t1.a是被所以的。在这种情况下，在查询展示列中的名字是a。因为查询引用了t1.a，而在order by条件中使用了a引用，所以在t1.a上的索引能够使用：
SELECT a FROM t1 ORDER BY a;
在这种语句中，在查询展示的列中是a,但它是一个别名，是ABS(a)的引用，作为引用a在排序条件中，所以在t1.a的索引不能被使用。
SELECT ABS(a) AS b FROM t1 ORDER BY a;
默认情况下，mysql排序所有group by col1,col2,....查询就好像你专门通过order by col1,col2,...在查询中排序一样。如果你包含了一个明确的排序列和列集合相同，mysql优化器不会变慢，虽然排序依然会发生。
如果查询中包含了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产生的默认排序。为了得到一个有序的排序，通过明确的ASC或者DESC标志在Group by 列上或者是提供一个order by列。group by的排序是mysql的扩展，有可能在未来的版本中改变；例如，优化器可能回按照其认为的最高效的方式排序分组，从而避免排序的开销。
使用文件排序优化
mysql有多个文件排序算法对于排序和检索结果。原始算是使用只在order by列上。修改后的算法不仅仅用在order by列上，而是用在所有查询引用的列上。而且存在一个算法对于小结果集在内存中排序通过使用排序缓存作为一个队列而不是合并文件。
优化器会选择哪一种文件排序算法来用，正常使用的是修改后的算法，除非查询中涉及到了BLOB或者是TEXT列的内容，在这种情况下，优化器会使用原始算法。对于每一个算法，排序缓存大小都是sort_buffer_size系统变量的值。
原始文件排序算法：
原始文件排序算法过程如下；
1）根据索引或者是表扫描多去所有行。忽略那些不满足where条件的行。
2）对每一行，存储在排序缓存中以一对值形式组成元组(排序的key值和行ID)
3) 如果所有的元组都放进了排序缓存，则不会生成临时文件。否则，当排序缓存填满之后，运行一个快速排序在内存中并写到临时文件中，保存一个指针到存储块。
4）重复之前的步骤直到所有的行都被读取。
5）做一个多区域合并,对多MERGEBUFF(7)区域到一个块在另一个临时文件。重复直到第一个文件中的所有块都存储在第二个文件中。
6）重复以下直到少于MERGEBUFF2(15)块留下。
7）在最后一次多个合并，只有行ID(最后一部分值对)被写进结果文件。
8）通过使用在结果文件中有序的行ID集合有序读取行数据。为了完成这个优化，读取一大块的行ID集合，对他们排序，并根据排好序的行读取到行缓存中。行缓存的大小是系统变量read_rnd_buffer_size的值。这一步的代码在sql/records.cc原文件中。
这种方法的一个问题是它会读取行两次：第一次通过where条件评估，然后得到值后再次对值对排序。而且即使是行数据在第一次就成功的访问到(例如，表扫描已经完成),第二次他们被随机的访问。(有序的keys集合已经被排序了，但是行的位置并不是有序的)
修改后的排序算法
修改后的排序算法包含了一个优化是避免两次读取行的：它记录有序的key值，而不是行id,它记录在查询中引用的列。修改后的文件排序算法是这样工作的：
1）读取where条件过滤后的行。
2）对读取到的每一行，在排序缓存中存储了有序key的值和查询中引用的列组成的一个元组。
3）当排序缓存被填满，在内存中通过key值来排序元组，并将其写在一个临时文件。
4）之后合并有序的临时文件，检索已经排好序的行数据，但是读取查询中的要求的列是直接从排序好的元组中而不是随机的第二次访问表。
用于修改后排序算法的元组比用于原排序算法更长，更少的数据被放入排序缓存。所以，可能会产生额外的I/O使得修改排序算法变慢，不是很快。为了避免速度降低，优化器只有在额外列在有序元组中的总大小没有超过max_length_for_sort_data系统变量的值。(这个变量设置的值太高的症状是高磁盘活动和第cpu活动)
修改后的排序算法包括一个额外的优化设计能够存储更多的元组数据到排序缓存：对于额外的列类型CHAR或者是VARCHAR,或者是可空的固定数据类型的，值被塞满。例如，没有压缩，一个varchar(255)列可能只占有3个字节也会占用255characters在缓存中。通过压缩，值要求只要3个characters和一个2字节长度的指示器。NUll值只要求一个标记位。
对于数据中包含了压缩的字符串比最大列长度或者是许多NUll值都短，更多的记录放入了排序缓存。这提高了自内存中排序缓存的排序，且完成基于磁盘的临时文件合并排序。
在一些特殊情况下，压缩也许是不利的：如果压缩字符串是最长的列值或者是数据中有很少的NUll值,长度指示器所占用的空间会减少放入排序缓存中的记录数，并且在内存和磁盘上的排序会比较慢。
在内存文件排序算法
对于如下形式的查询(包括子查询)，优化器能够用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;


排序缓存的尺寸是sort_buffer_size。如果对于N行的排序元素小到足以放入排序缓存(M+N行，其中M是特别的)，服务器能够避免用一个临时文件,而且能够完成一个内存排序通过将排序缓存看作是一个优先队列：
1）扫描表，从每一行数据中选择感兴趣的列集合到排序队列中。如果队列满了，去掉最后一行。
2）返回第一个来自队列的N行(如果M是特别标记，忽略第一个M行数据，然后返回之后的N行)
少了优化，服务器通过使用临时文件的排序来完成这个操作：
1）扫描表，重复这些步骤直到表的结尾：
    选择行直到填满排序缓存
    写入第一个在缓存中的N行(M+N行如果M是被特别标记的)到一个合并文件中
2）排序合并文件并且返回第一个N行。(如果M是特别标记的，忽略第一个M行，返回之后的N行)
表扫描操作的花费和队列和合并文件方法是相同的，所以优化器选择方法基于其他花费：
>队列方法涉及更多的cpu,插入行到有序队列中
>合并文件方法有I/O消费读写文件和CPU花费排序。
优化器考虑的平衡基于这些因素特别是M,N的值，和行的数量。
一个ORDER BY 条件和没有limit 也许会返回不同顺序的行集，具体讨论是8.2.1.16的limit的查询优化。
文件排序算法的优化
假设表t1有4个varchar类型a,b,c和d且优化器使用filesort对于这个查询：
SELECT * FROM t1 ORDER BY a, b;
查询排序通过a和b,但返回所有列，所以查询引用了a,b,c和d四列引用。依靠filesort算法的选择，查询执行如下：
对于原算法，排序缓存元组有这些内容：
(fixed size a value, fixed size b value,
row ID into t1)
优化器在固定尺寸值上排序。排序后，优化器读取排序元组并且用在每一个元组中的行ID读取行数据从t1中获取要选择的列的值集合。
修改后的算法没有压缩，排序缓存元组有这些内容：
(fixed size a value, fixed size b value,
a value, b value, c value, d value)
优化器排序在固定尺寸的值上，排序之后，优化器读取有序的元组，并且用值a,b,c,d来获取要查找的列值集合而不需要再读取t1。
对于修改后的用压缩算法，排序缓存元组有这些内容：
(fixed size a value, fixed size b value,
a length, packed a value, b length, packed b value,
c length, packed c value, d length, packed d value)
如果任何a,b,c,或者d是null,他们将不会放入排序缓存而不是一个位标记。
优化器排序在固定尺寸的值上，排序后，优化器读取有序元组，然后用值a,b,c和d去获取选择的值集合而不需要重新读取t1。
order by优化器的影响。
对没有使用filesort的慢排序查询，试着降低max_length_for_sort_data到一个值恰好触发一个filesort。
为了增加order by的速度，检查你是否能够使的mysql使用索引，而不是一个额外的排序阶段。如果这不可能，你能试着采用如下策略：
>增加sort_buffer_size变量的值。理想状态下，该值足够大道可以把结果集全部放入排序缓存(避免磁盘写和合并)，但是最小的值也必须足够大到可以计算15个元组。


考虑计算存储到排序缓存中的列值的大小被max_sort_length系统变量值影响。例如，如果存储元组中存储了更长的字符串列，并且你增加了max_sort_length的值，排序缓存大小也增加而且也会要求你增加sort_buffer_size的大小。对于列中字符串表达式用于计算结果值(例如这些涉及到字符串函数)，排序算法不能知道表达式值的最大长度，所以它必须允许max_sort_length字节对每一个元组。
为了监控合并的数量，检查Sort_merge_passes状态变量。
>增加read_rnd_buffer_size变量的值
>用更少的列值来声明列值足够存储他们。例如，CHAR(16)比CHAR(200)如果值不会超过16characters.
>改变tmpdir系统变量指向一个专用的文件系统其中大有量的空白空间。变量值能够列出可用的多个值用于轮询的方式；你能用这个特性将负载分散到多个目录。通过使用unix的符合：来区分路径，并且在windows的;来区分。文件系统中的路径直接定位到不同的物理磁盘，而不是一个磁盘的不同部分。
执行计划中关于ORDER BY可用信息。
对于EXPLAIN SELECT ... ORDER BY ,你能够检查mysql是否使用索引去解析这个查询。如果你在Extra列中看到了Using filesort，说明不能用索引。具体请看8.8.1通过EXPLAIN优化查询.文件排序使用一个固定长度的列存储格式类似于被用于MEMORY存储引擎。变量长度类型VARCHAR也被用于一个固定长度来存储。
如果一个文件排序完成，EXPLAIN输出包括Using filesort在Extra列。也就是优化器执行输出包括一个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>: 这表示使用了原始排序算法. 排序缓存中的数据元组是由排序key值和原始表中行的id值组成的一对值。元组通过key值来排序并且行ID用于读取表中的数据行。
><sort_key, additional_fields>: 这说明使用的是修改后的排序算法. 排序缓存有key值和查询中列的引用组成. 元组数据通过key值排序并且直接读取列值在元组中。
><sort_key, packed_additional_fields>: 说明使用了修改后的算法. 排序缓存中的数据元组由key值和查询中列值的压缩引用组成. 元组通过有序key值排序且直接从元组中读取数据。
EXPLAIN不区分使用修改和内存算法(优先级队列)。使用内存算法能够看到优化器的执行过程输出。查看filesort_priority_queue_optimization.
更多优化器的跟踪信息，请看mysql本质，追踪优化器。
到此关于order by的优化就结束了，接下来我们要说明的是1.14GROUP BY Optimization。