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 //对于排序来说,多个相等条件也是范围查询
GROUP BY的优化
GROUP BY实质上是先排序后进行分组,遵照索引的最佳左前缀。
当无法使用索隐裂,考虑增大max_length_for_sort_data和sort_buffer_size的参数设置。
WHERE 高于 HAVING,能写在WHERE限定的条件就不要去HAVING限定了。