关于ORDER BY

2010-06-03 14:57:51

      在mysql优化当中，explain的extra项经常会出现Using temporary，Using filesort这个两个词汇。Using temporary毫无疑问是用到了临时表，而人们对于Using filesort往往会理解错误，认为是“文件排序”，其实这个不能从字面意思理解。参考资料HOW MYSQL EXECUTES ORDER BY 。通过老外的介绍咱们可以看出：其实只要不是通过索引排序的都是filesort，也就是quicksort。而且如果排序生成的buffer大于@@sort_buffer_size ，就每次排序其中的一部分，最后通过排序-合并来生成最终的排序结果。

     filesort有两种情况：

     (1) 所排序的元素包含所有的表中需要的数据块。排序结果是“线性”的(也就是说等到filesort完毕以后就不会从原数据表里面取数据)。

     (2) 当遇到有blob，text这样的变长的数据时，则filesort的结果是所取得的数据行的一个标志，排序结果就会根据这个标志去原数据表中取得相对应的数据。

 

目前为止mysql有以下三种排序方式：(以下图片均引用 HOW MYSQL EXECUTES ORDER BY )

Method	EXPLAIN output
Use index-based access method that produces ordered output	no mention of filesort
Use filesort() on 1st non-constant table	“Using filesort” in the first row
Put join result into a temporary table and use filesort() on it	“Using temporary; Using filesort” in the first row

第一种模式：(使用索引)

 

这种方法比较好理解，首先通过索引对tbl1表进行排序然后再join其它的表。select tb1.* from tb1,tb2 where tb1.key = const and tb1.nokey = const order by tb1.key;

 

第二种模式：(对tbl1表进行filesort以后再join其它表)

 

老外这么描述：he second method can be used when all ORDER BY elements refer to the first table in the join order. In this case, we can filesort() the first table and then proceed to execute the join。

 

select tb1.* from tb1,tb2 where tb1.key = const and tb1.nokey = const order by tb1.nokey;

 

第三种模式：

 

 

select tb1.* from tb1,tb2 where tb1.key = const and tb1.nokey = const order by tb2.nokey;

 

优化排序的服务器设置：

1 加大max_length_for_sort_data。当所有返回字段的最大长度小于这个值时，mysql就会选用改进后的排序算法，否则就使用改进之前的排序算法。

2 增大sort_buffer_size。为了让mysql尽量减少在排序过程中对需要排序的数据进行分段。