Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景，其优化原理也非常的简单，就是利用索引的有序性，优化器沿着索引的顺序扫描，在扫描到符合条件的M行数据后，停止扫描；看起来非常的简单，但是我经常看到很多性能较差的sql没有利用这个优化规律，下面将结合一些实际的案例来分析说明：
案例一：
一条sql执行非常的慢，执行时间为：
| root@test 02:00:44 SELECT * FROM test_order_desc WHERE END_TIME>now() ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12; +---------+-----------+------------+------+---------------------+---------------------+-------------------Data1..................................................................................................... Data2..................................................................................................... +---------+-----------+------------+------+---------------------+---------------------+-------------------12 ROWS IN SET (0.49 sec) |
执行计划如下：
| root@test_db01:53:23 EXPLAIN SELECT * FROM test_order_desc WHERE END_TIME > now() ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12; +----+-------------+----------+-------+-----------------+-----------------+---------+------+--------+----- | id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra | +----+-------------+----------+-------+-----------------+-----------------+---------+------+--------+----- | 1 | SIMPLE | test_order_desc | range | ind_hot_endtime | ind_hot_endtime | 9 | NULL | 113549 | USING WHERE; USING filesort | +----+-------------+----------+-------+-----------------+-----------------+---------+------+--------+----- |
Ind_hot_endtime索引为：
| root@test_db01:52:45:SHOW INDEX FROM test_order_desc; Ind_hot_endtime(end_time,count_num) |
在注意到sql中满足过滤条件end_time>now()的有113549行，在加上剩余的条件中含有order by，这样会造成排序的结果集非常的大，执行非常的耗费资源；于是分析sql，在sql中包括了order by desc limit这样的排序条件后，新增适当的索引满足排序的条件，同时由于有limit的限制结果集，当扫描到满足条件的行数后退出查询，那么我们来看看优化效果：
添加索引：
| root@test 02:01:06:ALTER TABLE test_order_desc ADD INDEX ind_gmt_create(gmt_create,count_num); Query OK, 211945 ROWS affected (6.71 sec) Records: 211945 Duplicates: 0 Warnings: 0 |
再次执行sql，观察其执行时间：
| root@test 02:01:35: SELECT * FROM test_order_desc WHERE END_TIME > now() ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12; +---------+-----------+------------+------+---------------------+---------------------+col2................................................................................... +---------+-----------+------------+------+---------------------+---------------------+ Data1.................................................................................. Data2.................................................................................. +---------+-----------+------------+------+---------------------+---------------------+ 12 ROWS IN SET (0.00 sec) |
