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) |
