mysql 5.6 limit 对ORDER BY的优化

MySQL 5.6.2,优化器能更有效的处理如下的查询（或子查询）

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

这种查询在WEB应用程序中很常见，只显示一个结果集中很少的行，比如

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;

SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

排序缓冲区的大小由sort_buffer_size 变量决定（可以使用show variables like 'sort_buffer_size' 查看）. 如果缓冲区能容纳待排序的N个元素（当M指定时为M+N），服务器通过将排序缓冲区当做一个优先队列（ priority queue）使用，完全在内存中执行排序从而避免使用归并文件排序。

扫描待排序的表，按照排序要求将从选择的行中需要的列插入队列，如果队列满了，丢弃队列中最后的一个。

返回队列中前N行，（如果M指定，丢弃前M行，返回接下来的N行）这种排序方式能在时间复杂度为N的时间里完成排序，前提是排序队列

能容纳M+N行数据， 扫描的每一行都按照排序的要求插入，那么扫描完成后，队列中的行就是有序的，我们求相应的N行数据返回就行了。

以前的版本，服务器通过使用一个归并文件来执行排序操作：

扫描待排序的表：执行如下步骤：

1）选择行直到填满排序缓冲区，

2）将缓冲区中前N行，或者（M+N行如果M指定）写入归并文件

重复上面的步骤直到扫描完全表。

排序归并文件返回前N行，如果M指定就丢弃前M行然后返回接下来的N行。这种排序算法会多次向排序文件写入N行数据，当缓冲区满后写一次，然后对归并文件排序。当然是使用归并排序啦。

这两种算法的扫描表的时间开销是一样的。优化器基于其他的开销来做选择：

队列方法需要更多的CPU资源来按照需要的顺序向队列插入行。

归并文件需要更多的IO开销来写、读文件，切还要花费CPU来排序。
优化器会根据特定的N和行的大小来考虑这些因素的平衡

参考

http://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

Posted in: MySQL practise | Tags: limit, MySQL 5.6, optimize, Order by