最近運維部門的人發(fā)來郵件，告訴我們部門的頭說我們的項目存在很多慢查詢的SQL語句，頭又轉(zhuǎn)發(fā)給我的組長，組長再轉(zhuǎn)發(fā)到我這，造成慢查詢的原因很簡單就是SQL語句寫得不正確，因為用了子查詢，子查詢或者聯(lián)接，如果寫得不正確都會很容易讓掃描的記錄成指數(shù)級增長，想看例子可以到官方手冊去看看http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#explain，

于是這幾天又重新把mysql優(yōu)化的這一章節(jié)認(rèn)真地看了一遍，又學(xué)到了不少的知識。原來也看過一遍，但沒有真實環(huán)境讓你去解決問題的話看了也沒有太大的感觸，這回終于碰到問題，帶著解決問題的心去看這一章節(jié)你會發(fā)現(xiàn)這些內(nèi)容都很容易理解。像這回，數(shù)據(jù)庫的數(shù)據(jù)量只有20多萬，但寫的子查詢不正確，7584 X 740的乘積為4百多萬，花了8秒時間~可見寫正確的SQL語句是多么重要，要學(xué)習(xí)select語句的優(yōu)化，網(wǎng)上的資料多的是，mysql手冊，百度，谷歌一搜一大片，認(rèn)真看就是了。在網(wǎng)上看到了淘寶團(tuán)隊的一篇文章很不錯就轉(zhuǎn)載過來的，如果看得不太懂的話建議先去看看上面那個網(wǎng)址的內(nèi)容，然后再回來看看下面的內(nèi)容：

出自：http://www.alidba.net/index.php/archives/81

前段時間一位同事收到某開發(fā)部門一位同事求助，希望幫忙優(yōu)化一條Mysql的sql語句，大體如下：

select n.id ,nc.content
from news n force index (category1_status,category2_status,category3_status),news_content nc
where n.id=nc.id
and n.status=2 and (n.category_id_1 in (5003107,5003108)
or n.category_id_2 in (5003107,5003108)
or n.category_id_3 in (5003107,5003108)

調(diào)試的時候發(fā)現(xiàn)怎么都不能走index_merge的執(zhí)行計劃（我們所期望的），后來臨時給他們一個union的解決方案。后來下班吃完晚飯后一起找問題，發(fā)現(xiàn)即使只有單個表，也沒辦法走到index_merge的執(zhí)行計劃，不管是加提示還是不加提示，調(diào)試過程如下：

mysql> explain select n.id ,nc.content
-> from news n force index (category1_status,category2_status,category3_status),news_content nc
-> where n.id=nc.id
-> and n.status=2 and (n.category_id_1 in (5003107,5003108)
-> or n.category_id_2 in (5003107,5003108)
-> or n.category_id_3 in (5003107,5003108)
-> ) ;
+—-+————-+——-+——–+—————————————————-+———+———+————–+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————————————————-+———+———+————–+——–+————-+
| 1 | SIMPLE | n | ALL | category1_status,category2_status,category3_status | NULL | NULL | NULL | 552535 | Using where |
| 1 | SIMPLE | nc | eq_ref | PRIMARY | PRIMARY | 4 | biznews.n.id | 1 | |
+—-+————-+——-+——–+—————————————————-+———+———+————–+——–+————-+
2 rows in set (0.00 sec)
從上面可以看出，Mysql優(yōu)化器已經(jīng)識別到有三個索引可以用，但是沒有選任何一個，然后去掉其中一個參與join的表，同時去掉提示，還是不行：

mysql> explain select *
-> from news n
-> where
-> ( n.status=2 and n.category_id_1 = 5003107)
-> or ( n.status=2 and n.category_id_2 = 5003107)
-> or (n.category_id_3 =5003107 and n.status=2);
+—-+————-+——-+——+——————————————————————–+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+——————————————————————–+——+———+——+——–+————-+
| 1 | SIMPLE | n | ALL | news_ind_status,category1_status,category2_status,category3_status | NULL | NULL | NULL | 552535 | Using where |
+—-+————-+——-+——+——————————————————————–+——+———+——+——–+————-+
1 row in set (0.00 sec)

單表，加提示效果：

mysql> explain select *
-> from news n force index (category1_status,category2_status,category3_status)
-> where
-> ( n.status=2 and n.category_id_1 = 5003107)
-> or ( n.status=2 and n.category_id_2 = 5003107)
-> or (n.category_id_3 =5003107 and n.status=2);
+—-+————-+——-+——+—————————————————-+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————————————————-+——+———+——+——–+————-+
| 1 | SIMPLE | n | ALL | category1_status,category2_status,category3_status | NULL | NULL | NULL | 552535 | Using where |
+—-+————-+——-+——+—————————————————-+——+———+——+——–+————-+
1 row in set (0.00 sec)

后來，嘗試了一下去掉一個or，，發(fā)現(xiàn)正常了，執(zhí)行計劃和我們預(yù)期的一樣了：
mysql> explain select n.id ,nc.content
-> from news n force index (category1_status,category2_status),news_content nc
-> where n.id=nc.id
-> and ((n.status=2 and n.category_id_1 in (5003107,5003108))
-> or (n.status=2 and n.category_id_2 in (5003107,5003108))
-> );
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
| 1 | SIMPLE | n | index_merge | category1_status,category2_status | category1_status,category2_status | 6,6 | NULL | 54238 | Using sort_union(category1_status,category2_status); Using where |
| 1 | SIMPLE | nc | eq_ref | PRIMARY | PRIMARY | 4 | biznews.n.id | 1 | |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
2 rows in set (0.01 sec)

ok，再將之前拿掉的參與join的表加入進(jìn)來，也正常：

mysql> explain select n.id ,nc.content
-> from news n force index (category1_status,category2_status),news_content nc
-> where n.id=nc.id
-> and ((n.status=2 and n.category_id_1 in (5003107,5003108))
-> or (n.status=2 and n.category_id_2 in (5003107,5003108))
-> );
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
| 1 | SIMPLE | n | index_merge | category1_status,category2_status | category1_status,category2_status | 6,6 | NULL | 54244 | Using sort_union(category1_status,category2_status); Using where |
| 1 | SIMPLE | nc | eq_ref | PRIMARY | PRIMARY | 4 | biznews.n.id | 1 | |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
2 rows in set (0.00 sec)

莫非就是因為有三個or條件，而且三個條件都是不同的column上面，并對應(yīng)上三個不同的索引所造成的？繼續(xù)測試：
mysql> select * from t;
+——+————+———-+
| id | name | descs |
+——+————+———-+
| 1 | abc | x |
| 2 | abcd | xx |
| 3 | abcde | xxx |
| 4 | abcdef | xxxx |
| 5 | abcdefg | xxxxx |
| 6 | abcdefgh | xxxxxx |
| 7 | abcdefghi | xxxxxx |
| 8 | abcdefghij | xxxxxxx |
| 8 | a | xxxxxxx |
| 9 | ab | xxxxxxxx |
+——+————+———-+

mysql> explain select * from t where id = 3;
+—-+————-+——-+——+—————+———-+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+———-+———+——-+——+————-+
| 1 | SIMPLE | t | ref | t_id_ind | t_id_ind | 5 | const | 1 | Using where |
+—-+————-+——-+——+—————+———-+———+——-+——+————-+
1 row in set (0.00 sec)

mysql> explain select * from t where name = ‘abc’;
+—-+————-+——-+——+—————+————+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+————+———+——-+——+————-+
| 1 | SIMPLE | t | ref | t_name_ind | t_name_ind | 23 | const | 1 | Using where |
+—-+————-+——-+——+—————+————+———+——-+——+————-+
1 row in set (0.00 sec)

mysql> explain select * from t where name = ‘abc’ or id = 3;
+—-+————-+——-+————-+———————+———————+———+——+——+———————————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+———————+———————+———+——+——+———————————————–+
| 1 | SIMPLE | t | index_merge | t_id_ind,t_name_ind | t_name_ind,t_id_ind | 23,5 | NULL | 2 | Using union(t_name_ind,t_id_ind); Using where |
+—-+————-+——-+————-+———————+———————+———+——+——+———————————————–+

mysql> explain select * from t where id = 3 or descs = ‘xxx’;
+—-+————-+——-+————-+———————-+———————-+———+——+——+————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+———————-+———————-+———+——+——+————————————————+
| 1 | SIMPLE | t | index_merge | t_id_ind,t_descs_ind | t_id_ind,t_descs_ind | 5,23 | NULL | 2 | Using union(t_id_ind,t_descs_ind); Using where |
+—-+————-+——-+————-+———————-+———————-+———+——+——+————————————————+
1 row in set (0.00 sec)

mysql> explain select * from t where name = ‘abc’ or descs = ‘xxx’;
+—-+————-+——-+————-+————————+————————+———+——+——+————————————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+————————+————————+———+——+——+————————————————–+
| 1 | SIMPLE | t | index_merge | t_name_ind,t_descs_ind | t_name_ind,t_descs_ind | 23,23 | NULL | 2 | Using union(t_name_ind,t_descs_ind); Using where |
+—-+————-+——-+————-+————————+————————+———+——+——+————————————————–+
1 row in set (0.00 sec)
mysql> explain select * from t where name = ‘abc’ or id = 3 or descs = ‘xxx’;
+—-+————-+——-+——+———————————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+———————————+——+———+——+——+————-+
| 1 | SIMPLE | t | ALL | t_id_ind,t_name_ind,t_descs_ind | NULL | NULL | NULL | 10 | Using where |
+—-+————-+——-+——+———————————+——+———+——+——+————-+
1 row in set (0.00 sec)

mysql> explain select * from t force index(t_id_ind,t_name_ind) where name = ‘abc’ or id = 3 or descs = ‘xxx’;
+—-+————-+——-+——+———————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+———————+——+———+——+——+————-+
| 1 | SIMPLE | t | ALL | t_id_ind,t_name_ind | NULL | NULL | NULL | 10 | Using where |
+—-+————-+——-+——+———————+——+———+——+——+————-+
1 row in set (0.00 sec)
基本驗證了上面的想法，只要是兩個索引，都可以走index_merge，換成三個馬上就不行了，即使是強(qiáng)行指定用某兩個索引也不行，索引都能夠認(rèn)到，但 優(yōu)化器就是不使用任何一個。想一下，如果按照提示，使用了兩個索引，那么會有剩下一個條件不會走索引，那么對于該條件的過濾還是要通過表查詢，這樣，對于 mysql來說就相當(dāng)于要兩個索引的index_mereg后再讀表，而且仍然要做一次全表掃描，那還不如就作一次表掃描，Mysql最終還是選擇一次表 掃描是可以理解的。在Mysql文檔上面也說了，在提示了mysql用某一個索引后，也就相當(dāng)于告訴了mysql不要用其他的相關(guān)的一些索引。估計 Mysql也并沒有去實現(xiàn)三個索引的index_merge，實際上想想就算是實現(xiàn)了，通過讀三個索引然后做merge再去取表的記錄，其消耗可能也并不 會太小，對于Mysql的這個選擇也無可厚非。