Mysql优化_ORDER BY和GROUP BY 的优化讲解（单路排序和双路排序）

最新推荐文章于 2022-12-27 07:00:00 发布

weixin_30387423

最新推荐文章于 2022-12-27 07:00:00 发布

阅读量366

点赞数

文章标签：数据库数据结构与算法

原文链接：http://www.cnblogs.com/wt645631686/p/8320525.html

版权

ORDER BY 子句尽量使用Index方式排序，避免使用FileSort方式排序，尽可能在索引列上外城排序操作，遵照索引键的最佳左前缀。如果不在索引列上，FileSort有两种算法，Mysql就要启动双路排序和单路排序。

什么是双路排序和单路排序？

双路排序：Mysql4.1之前是使用双路排序，字面的意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在buffer进行排序，再从磁盘读取其他字段。文件的磁盘IO非常耗时的，所以在Mysql4.1之后，出现了第二种算法，就是单路排序。

单路排序：从磁盘读取查询所需要的所有列，按照ORDER BY在buffer对它进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存里。

但是，问题来了，有可能单路排序算法一次拿不出数据，那么就还比双路排序更消耗IO，效率更慢！

什么情况下会导致单路排序失效呢？
在sort_buffer中，单路排序要比双路排序占很多空间，因为单路排序把所有的字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能读取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取sort_buffer容量大小，再次排序...从而多次I/O。偷鸡不成蚀把米。
比如：内存就是2M，一次查1000条数据刚好，也就是最大1000条数据，但是一次要查5000条，那么不够了，照这样需要查5次刚好，如果把2M改为10M，那么就刚好了

提高ORDER BY速度的技巧
1：ORDER BY时不要使用SELECT *，只查需要的字段。
a：当查询的字段大小综合小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法---单路排序，否则用老算法---多路排序。假设只需要查10个字段，但是SELECT *会查80个字段，那么就容易把sort_buffer缓冲区用满。
b：两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size大小。
2：增大sort_buffer_size参数大小
不管用哪种算法，提高这个参数都会提高效率。当然要根据系统能力去提高，因为这个参数是针对每个进程的。
3：增大max_length_for_sort_data参数大小
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

为排序使用索引
MYSQL两种排序方式：文件排序和扫描有序索引排序
Mysql能为排序和查询使用相同的索引。也就是创建索引先把数据排序了，查询的时候再利用索引，一举两得。

KEY a_b_c(a,b,c)
order by 能使用索引最左前缀

ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC

如果where使用缩印的最左前缀定义为常量，则order by 能使用索引

WHERE a = const ORDER BY b,c
WHERE a = const AND b = const ORDER BY c
WHERE a = const AND b > const ORDER BY b,c

不适用索引进行排序

ORDER BY a ASC,b DESC, c DESC //排序不一致
WHERE g = const ORDER BY b,c //丢失a索引
WHERE a = const ORDER BY c //丢失b索引
WHERE a = const ORDER BY a,d //d不是索引的一部分
WHERE a in (....) ORDER BY b,c //对于排序来说，多个相等条件也是范围查询