MySQL数据库如何进行排序(Sort)操作?

参考原始文档后, 自已总结一下, 有不对的地方请指正. 在MySQL中进行排序有两种算法, 以4.1版本为分水岭, 在此之前排序时的数据只包括ORDER BY中的列和一个记录的指针(Sort A), 在4.1之后多了一种改进的方法, 排序的数据中可以包括SELECT中的列和ORDER BY中的列(Sort B)

Sort A中最主要的坏处是需要进行二次扫表, 第一次是将ORDER BY的列及记录指针读到SORT Buffer中, 然后排序, 如果要排序的数据很多, 不能在内存中完成, 则可能会用到临时表(tmpdir)空间. 排完序后再根据记录指针将记录读取到READ RND Buffer中, 这一步可能会很慢, 因为这里进行的读取大都是随机读, 而不是顺序读.

Sort B中在第一次扫描表时将SELECT中的列和ORDER BY的列读到SORT Buffer中, 然后排序, 如果要排序的数据很多, 不能在内存中完成, 则可能会用到临时表(tmpdir)空间. 排完序后, 就不需要进行二次读表了, 因为所有的列都已经在第一次中读出来了. 可以想象, 如果SELECT中的字段很多, 记录很长, 那么一个Sort Buffer中存放的记录数就少了, 对于同样数量的记录, 就需要进行更多次的排序了, 有可能引起效率的降底.

在MySQL中max_length_for_sort_data变量用于控制何时采用Sort A, 何时采用Sort B, 当SELECT中的列和ORDER BY中的列的长度超过这个设置时采用Sort A, 而在这个以内时采用Sort B. 但依据Oracle上的经验, 如果这个SQL最后要返回大量记录, 那么应当选用Sort B, 我们可以在会话级更改设置, 如果返回的记录很少, 那么可以优先采用Sort A, 因为随机读取的成本实在是比较高的.

这些Buffer都会在SQL运行结束时自动释放. 还有一个问题时, 如果排序中用到了临时文件, 读取时是不是受到Read Buffer Size的控制?

是会受到read_buffer_size的控制。

转自：http://www.anysql.net/mysql/how_mysql_do_sort.html