MySQL的ORDERBY...LIMIT优化思路

 

一 常规思路

互联网展示内容的时候,通常需要分页显示内容, 这就要用到如下格式的语句:

SELECT <obj_list> FROM <
single_table
> ORDER BY <order_list> LIMIT n

对于ORDERBY子句,通常需要按指定的对象进行排序;如果ORDERBY子句加上LIMIT子句,则在排序结果的基础上,输出结果集的n行.这种方式，意味着在获取表的数据时，采取的是全表扫描的方式。

二 改进思路一

对于形如下面的SQL格式，如果order_list是索引列，则可以利用索引（索引必须是支持有序的索引，如hash索引不支持有序，则不可以被用于优化），有序获取数据，避免耗时的排序操作。

SELECT <obj_list> FROM <table_list> ORDER BY <order_list>

至于是选取索引进行优化，还是选取排序，则取决于代价估算模型。查询优化器会对二者进行一个计算，最终结果选取代价最小的。

三 改进思路二

如果目标列obj_list是索引的一部分，且和order_list 属于同一个索引，且order_list 至少是索引的前缀部分，则只读取索引就可以完成排序，不需要先读取索引然后根据索引定位数据文件中元组的位置再读取到数据（这种方式是“改进思路一”中通常选取的方式），只读取索引的方式通常称为“只读取索引”简称“只读索引”。

如果存在“只读索引”的优化可能，则通过代价估算模型计算得到的基于索引的代价值，可能会更小，更有利于被选取。这是因为：

1 可以避免对结果集做文件级别的排序（MySQL的实现排序的算法，可以参见《数据库查询优化器的艺术：原理解析与sql性能优化》一书408页，双路排序算法，单路排序算法）

2 当找到n个记录的时候，可以方便的停止继续查找节约了IO和CPU消耗

四 改进思路三

对于带有LIMIT子句的ORDERBY格式，可以根据LIMIT进行进一步的优化。改进的方式，可以借鉴“改进思路一”和“改进思路二”。

MySQL一旦发现得到的元组数已经达到LIMIT的限制，则终止排序，这就是说，在排序的过程中，检查已经获得的元组条数是否已经达到LIMIT的限制。这样的方式，依赖于排序算法。常规的排序，只有在全部数据排序完成后，才能从有序的结果中截取一段以保证结果的正确。但是，MySQL可以不等排序全部完成就能获取出正确的一段范围，这是因为MySQL的排序算法采取的是多路归并排序算法。

如果MySQL只基于索引就能完成数据的有序获取，则更有利于ORDERBY...LIMIT格式的SQL语句优化。

五 改进思路四

常规的改进方式，是基于代价模型，比较排序和利用索引的代价大小，选取代价较少的方式。但是，MySQL在基于代价的同时，也在基于索引的情况下，采取了启发式用于优化“ ORDER BY <order_list> LIMIT n”句式。包括2种情况（每种情况都存在可以使用的索引）：

1 情况一：如果数据的获取方式，是“quick select”，且LIMIT的n值极小，则直接采取索引进行优化。这是在make_join_select()函数中实现的（这种决定出现偏差的可能性存在，如果读取索引耗费的时间较多，则不通过代价模型得到基于索引的优化方式可能不如基于代价得到的优化方式靠谱）。

2 情况二：根据代价模型确定是选取索引还是选取文件排序进行排序操作。这依靠test_if_cheaper_ordering()函数完成。test_if_cheaper_ordering()函数用于计算基于索引的代价和基于文件排序的代价。

六 MySQL未来改进的新思路

由于基于启发式规则的优化方式，可能带来负面效应，而基于代价的估算方式尽管能得到较优的优化方法，但是计算代价需要花费时间。所以，未来的MySQL版本，可能会提供一个参数，用以打开或关闭本文所讲情况下的基于索引的启发式优化规则。

七 示例

CREATE TABLE `t1` (  `id1` bigint(20) NOT NULL,  `id2` bigint(20) NOT NULL,  `id3` bigint(20) NOT NULL,  `c1` bigint(20) DEFAULT NULL,  `c2` bigint(20) NOT NULL,  `seqid` bigint(20) NOT NULL AUTO_INCREMENT,  `time_stamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  PRIMARY KEY (`id1`,`id2`),  UNIQUE KEY `ix_seq` (`seqid`),  KEY `ix_ts` (`time_stamp`)) ENGINE=InnoDB AUTO_INCREMENT=1782 DEFAULT CHARSET=utf8

导入或插入一定数据，然后比较如下查询执行计划。区别在于LIMIT子句的值一个是5一个是6，得出的执行计划不同（注意不同的数据不同的数据量，因LIMIT值不同而得到不同的执行计划，需要进行多次试验才能发现）。

 

mysql> EXPLAIN SELECT * FROM t1 ORDER BY seqid limit 5;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | ix_seq | 8       | NULL |    5 | 20480.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
 
mysql> EXPLAIN SELECT * FROM t1 ORDER BY seqid limit 6;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1024 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)