Order by desc/asc limit M是我在mysql sql優(yōu)化中經(jīng)常遇到的一種場(chǎng)景，其優(yōu)化原理也非常的簡(jiǎn)單，就是利用索引的有序性，優(yōu)化器沿著索引的順序掃描，在掃描到符合條件的M行數(shù)據(jù)后，停止掃描；看起來非常的簡(jiǎn)單，但是我經(jīng)常看到很多性能較差的sql沒有利用這個(gè)優(yōu)化規(guī)律，下面將結(jié)合一些實(shí)際的案例來分析說明：
案例一：
一條sql執(zhí)行非常的慢，執(zhí)行時(shí)間為：
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)
執(zhí)行計(jì)劃如下：
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，這樣會(huì)造成排序的結(jié)果集非常的大，執(zhí)行非常的耗費(fèi)資源；于是分析sql，在sql中包括了order by desc limit這樣的排序條件后，新增適當(dāng)?shù)乃饕凉M足排序的條件，同時(shí)由于有l(wèi)imit的限制結(jié)果集，當(dāng)掃描到滿足條件的行數(shù)后退出查詢，那么我們來看看優(yōu)化效果：
添加索引：
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
再次執(zhí)行sql，觀察其執(zhí)行時(shí)間：
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)
可以看到執(zhí)行時(shí)間已經(jīng)降到了毫秒以下,查看其執(zhí)行計(jì)劃：
root@test 02:01:42: 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 | INDEX | ind_hot_endtime | ind_gmt_create | 14 | NULL | 48 | USING WHERE |
可以看到優(yōu)化器已經(jīng)選擇了ind_gmt_create索引掃描，這樣的話就避免了對(duì)結(jié)果集進(jìn)行排序的過程，同時(shí)優(yōu)化器預(yù)估掃描14行數(shù)據(jù)就會(huì)得到滿足查詢條件的數(shù)據(jù)（END_TIME > now()），執(zhí)行計(jì)劃非常的理想。
root@127.0.0.1 : test_db 16:05:15:EXPLAIN SELECT b.*,a.*,k.* FROM instance b LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;
案例二：
root@127.0.0.1 : test_db 16:05:15:EXPLAIN SELECT b.*,a.*,k.* FROM instance b LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;
B表的idx_uid_stat_inid的索引列包括了（user_id，status，instance_no）：
我們從執(zhí)行計(jì)劃上分析來看，表的連接順序?yàn)椋篵―>r_a―>a―>k，可以看到執(zhí)行計(jì)劃的第一行中需要掃描49212行的數(shù)據(jù),同時(shí)由于status采用的是in的方式，instance_no即使在索引中也用不上，這樣就導(dǎo)致了排序使用到了臨時(shí)表,這也是導(dǎo)致sql執(zhí)行慢的原因。我們看到sql中的最后一個(gè)排序?yàn)閛rder by b.instance_no asc limit 37300,50，這里我們好像可以看到優(yōu)化的曙光，調(diào)整數(shù)據(jù)庫的索引以滿足B表的排序需求：
root@127.0.0.1 : test_db 16:05:04 ALTER TABLE instance ADD INDEX ind_user_id(user_id,instance_no);Query OK, 0 ROWS affected (0.56 sec)
調(diào)整索引后查看執(zhí)行計(jì)劃：
root@127.0.0.1 : test_db 16:09:42EXPLAIN SELECT b.*,a.*,k.* FROM instance b LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;
我們加上force index強(qiáng)制走我們新加的索引：
root@127.0.0.1 : test_db 16:10:24EXPLAIN SELECT b.*,a.*,k.* FROM instance b force INDEX (ind_user_id) LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;
可以看到在加上提示符后，使用到了我們新加的索引，掃描的行數(shù)為54580行，執(zhí)行時(shí)間：
root@127.0.0.1 : test_db 16:10:30SELECT b.*,a.*,k.* FROM instance b force INDEX (ind_user_id) LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;(0.49 sec)
原始的執(zhí)行時(shí)間：
root@127.0.0.1 : test_db 16:10:51:SELECT b.*,a.*,k.* FROM instance b LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;(1.28 sec)
總結(jié)：
Order by desc/asc limit的優(yōu)化技術(shù)有時(shí)候在你無法建立很好索引的時(shí)候，往往會(huì)得到意想不到的優(yōu)化效果，但有時(shí)候有一定的局限性，優(yōu)化器可能不會(huì)按照你既定的索引路徑掃描，優(yōu)化器需要考慮到查詢列的過濾性以及l(fā)imit的長(zhǎng)度，當(dāng)查詢列的選擇性非常高的時(shí)候，使用sort的成本是不高的，當(dāng)查詢列的選擇性很低的時(shí)候，那么使用order by +limit的技術(shù)是很有效的。
新聞熱點(diǎn)
疑難解答
圖片精選
