MySQL索引優化的現實案例剖析。本站提示廣大學習愛好者：（MySQL索引優化的現實案例剖析）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL索引優化的現實案例剖析正文
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)
可以看到履行時光曾經降到了毫秒以下,檢查其履行籌劃：
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 |
可以看到優化器曾經選擇了ind_gmt_create索引掃描，如許的話就防止了對成果集停止排序的進程，同時優化器預估掃描14行數據就會獲得知足查詢前提的數據（END_TIME > now()），履行籌劃異常的幻想。
[email protected] : 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;
案例二：
[email protected] : 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）：
我們從履行籌劃上剖析來看，表的銜接次序為：b—>r_a—>a—>k，可以看到履行籌劃的第一行中須要掃描49212行的數據,同時因為status采取的是in的方法，instance_no即便在索引中也用不上，如許就招致了排序應用到了暫時表,這也是招致sql履行慢的緣由。我們看到sql中的最初一個排序為order by b.instance_no asc limit 37300,50，這裡我們似乎可以看到優化的曙光，調劑數據庫的索引以知足B表的排序需求：
[email protected] : 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)
調劑索引後檢查履行籌劃：
[email protected] : test_db 16:09:42 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;
我們加上force index強迫走我們新加的索引：
[email protected] : test_db 16:10:24 EXPLAIN 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;
可以看到在加上提醒符後，應用到了我們新加的索引，掃描的行數為54580行，履行時光：
[email protected] : test_db 16:10:30 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; (0.49 sec)
原始的履行時光：
[email protected] : 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)
總結：
Order by desc/asc limit的優化技巧有時刻在你沒法樹立很好索引的時刻，常常會獲得意想不到的優化後果，但有時刻有必定的局限性，優化器能夠不會依照你既定的索帶路徑掃描，優化器須要斟酌到查詢列的過濾性和limit的長度，當查詢列的選擇性異常高的時刻，應用sort的本錢是不高的，當查詢列的選擇性很低的時刻，那末應用order by +limit的技巧是很有用的。
