max_length_for_sort_data 详解

MySQL文件排序有两种算法
第一种排序算法
1）读取 固定长度的排序列 + rowid组成元组，放入sort buffer
2) 如果sort buffer满， 根据排序列执行一次quicksort, 将其写入临时文件
3）重复1 2 步骤直到文件结束，
4）对临时文件执行归并排序
5）根据排序好的rowid 读取查询需要的行

第二种算法，
1）读取<固定长度的排序列, 需要返回的列> 组成元组，放入sort buffer
2) 如果sort buffer满， 根据排序列执行一次quicksort, 将其写入临时文件
3）重复1 2 步骤直到文件结束，
4）对临时文件执行归并排序
5）从排好序的临时文件中读取需要返回的列即可

这两种排序算法的区别
1）算法1 会读取两次表， 且第二次是随机读取的
2）算法2的元组比算法1要长，导致它需要多次向临时文件写入内容，增加IO操作，当需要返回的列的总长度很长时尤其明显。
MySQL根据max_length_for_sort_data变量来确定使用哪种算法，默认值是1024字节，如果需要返回的列的总长度大于max_length_for_sort_data，使用第一种算法，否则使用第二种算法。

order by 优化
order by 能使用两种索引来避免文件排序，1）order by 的列全出现在索引中 2）order by中使用了索引的一部分，where中出现索引的另一部分且where中的部分是常量。
比如：
SELECT * FROM t1
WHERE key_part1 = constant
ORDER BY key_part2;

参考：
http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html

Posted in: MySQL practise | Tags: max_length_for_sort_data, MySQL