通過 EXPLAIN 淺析數(shù)據(jù)庫查詢優(yōu)化方法

EXPLAIN 思維導圖

image

知識點

explain 或者 desc 命令

獲取Myswl如何執(zhí)行SELECT語句的信息寂诱，包括在SELECT語句執(zhí)行過程中表如何連接和連接的順序直奋。

• index：

這種類型表示是mysql會對整個該索引進行掃描。

要想用到這種類型的索引邮府，對這個索引并無特別要求帜羊，只要是索引晴裹，或者某個復合索引的一部分，mysql都可能會采用index類型的方式掃描。

缺點是效率不高败徊，mysql會從索引中的第一個數(shù)據(jù)一個個的查找到最后一個數(shù)據(jù)，直到找到符合判斷條件的某個索引掏缎。

• ref：

這種類型表示mysql會根據(jù)特定的算法快速查找到某個符合條件的索引皱蹦，而不是會對索引中每一個數(shù)據(jù)都進行一 一的掃描判斷煤杀，也就是平常理解的使用索引查詢會更快的取出數(shù)據(jù)。而要想實現(xiàn)這種查找沪哺，索引卻是有要求的沈自，要實現(xiàn)這種能快速查找的算法，索引就要滿足特定的數(shù)據(jù)結構辜妓。

索引字段的數(shù)據(jù)必須有序才能實現(xiàn)這種類型的查找枯途，才能利用到索引。

• Using index

列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的籍滴，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候酪夷。

• Using where

使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。

在一般稍大的系統(tǒng)中孽惰，基本盡可能的減少join 晚岭，子查詢等等。mysql就使用最簡單的查詢勋功，這樣效率最高。至于 join 等狂鞋，可以放在應用層去解決片择。

• Using temporary

優(yōu)化。MYSQL需要創(chuàng)建一個臨時表來存儲結果骚揍，這通常發(fā)生在對不同的列集進行ORDER BY上字管，而不是GROUP BY上

• Using filesort

優(yōu)化。MYSQL需要進行額外的步驟來發(fā)現(xiàn)如何對返回的行排序疏咐。它根據(jù)連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行

執(zhí)行計劃中什么情況下會出現(xiàn)using filesort和using tempatory纤掸？

通常當查詢中出現(xiàn)連接（JOIN）且連接中的所有列都來自連接的第一個表時，就會出現(xiàn)using filesort浑塞。

除此之外的所有情況借跪，mysql都會先將連接結果放到臨時表中，然后在所有的連接都結束后酌壕，再進行文件排序（實際上是快速排序）掏愁，不管這種排序是在內存中還是在硬盤中進行的。

即使查詢中出現(xiàn)limit 子句卵牍，也會在排序結束后才做分頁處理果港，所以實際上臨時表和需要排序的數(shù)據(jù)量依舊會非常大。

當執(zhí)行計劃中出現(xiàn)使用臨時表的情況時糊昙，首先要檢查一下是不是ORDER BY 和GROUP BY 語句中字段的問題辛掠。

如果調整過字段還是出現(xiàn)使用臨時表的情況，考慮聯(lián)合索引來覆蓋這些字段，并盡可能的使用小結果集驅動大結果集萝衩。

explain 示例代碼

 mysql > explain select A.id , A.title , B.title 
 from base_content A 
 left join base_categories B on A.catid = B.id 
 left join base_sections C on A.sectionid = C.id
 order by B.id ;  
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+  
| id | select_type | table | type    | possible_keys | key      | key_len | ref                      | rows   | Extra                            |  
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+  
|  1 | SIMPLE       | A      | ALL     | NULL           | NULL     | NULL     | NULL                     | 46585 | Using temporary ; Using filesort |  
|  1 | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . catid      |      1 |                                 |  
|  1 | SIMPLE       | C      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . sectionid |      1 | Using index                      |  
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+  
3 rows in set ( 0.00 sec )  

更改排序字段后：

mysql > explain select A.id , A.title , B.title 
from base_content A 
left join base_categories B on A.catid = B.id 
left join base_sections C on A.sectionid = C.id 
order by A.id ;  
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+  
| id | select_type | table | type    | possible_keys | key      | key_len | ref                      | rows   | Extra           |  
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+  
|  1 | SIMPLE       | A      | ALL     | NULL           | NULL     | NULL     | NULL                     | 46585 | Using filesort |  
|  1 | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . catid      |      1 |                |  
|  1 | SIMPLE       | C      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . sectionid |      1 | Using index     |  
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+  

第一個使用了Using temporary回挽，而第二個沒有用呢？

因為如果有ORDER BY子句和一個不同的GROUP BY子句猩谊，或者如果ORDER BY或GROUP BY中的字段都來自其他的表而非連接順序中的第一個表的話千劈，就會創(chuàng)建一個臨時表了。

那么牌捷，對于上面例子中的第一條語句墙牌，我們需要對base_categories的id進行排序，可以將SQL做如下改動：

mysql > explain select B.id , B.title , A.title 
from base_categories A 
left join base_content B on A.id = B.catid 
left join base_sections C on B.sectionid = C.id 
order by A.id ;  
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+  
| id | select_type | table | type    | possible_keys | key        | key_len | ref                      | rows | Extra           |  
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+  
|  1 | SIMPLE       | A      | ALL     | NULL           | NULL       | NULL     | NULL                     |    18 | Using filesort |  
|  1 | SIMPLE       | B      | ref     | idx_catid      | idx_catid | 4        | joomla_test . A . id         | 3328 |                |  
|  1 | SIMPLE       | C      | eq_ref | PRIMARY        | PRIMARY    | 4        | joomla_test . B . sectionid |    1 | Using index     |  
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+  
3 rows in set ( 0.00 sec )  

這樣我們發(fā)現(xiàn)暗甥，不會再有Using temporary了喜滨，而且在查詢base_content時，查詢的記錄明顯有了數(shù)量級的降低淋袖，這是因為base_content的idx_catid起了作用鸿市。

結論：

為提高查詢效率，應盡量對第一個表的索引鍵進行排序

擴展：

Using filesort 字面意思：使用文件進行排序或中文件中進行排序即碗。

這是不正確的，當我們試圖對一個沒有索引的字段進行排序時陌凳，就是filesoft剥懒。它跟文件沒有任何關系，實際上是內部的一個快速排序合敦。 

我們再看：

mysql > explain select A.id , A.title , B.title 
from base_content A , base_categories B , base_sections C 
where A.catid = B.id and A.sectionid = C.id 
order by C.id ;  
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+  
| id | select_type | table | type    | possible_keys          | key          | key_len | ref                  | rows   | Extra        |  
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+  
|  1 | SIMPLE       | C      | index   | PRIMARY                | PRIMARY      | 4        | NULL                 |      1 | Using index |  
|  1 | SIMPLE       | A      | ref     | idx_catid , idx_section | idx_section | 4        | joomla_test . C . id     | 23293 | Using where |  
|  1 | SIMPLE       | B      | eq_ref | PRIMARY                | PRIMARY      | 4        | joomla_test . A . catid |      1 | Using where |  
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+  
3 rows in set ( 0.00 sec )  

我們會發(fā)現(xiàn)Using filesort沒有了,而這條語句中C表的主鍵對排序起了作用初橘。
而盡管在上面的語句中也是對第一個表的主鍵進行排序，卻沒有得到想要的效果（第一個表的主鍵沒有用到）充岛，這是為什么呢保檐？

實際上以上運行過的所有l(wèi)eft join的語句中，第一個表的索引都沒有用到崔梗，盡管對第一個表的主鍵進行了排序也無濟于事夜只。不免有些奇怪！

測試：

于是我們繼續(xù)測試了下一條SQL：

mysql > explain select A.id , A.title , B.title 
from base_content A 
left join base_categories B on A.catid = B.id 
left join base_sections C on A.sectionid = C.id 
where A.id < 100 ;  
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+  
| id | select_type | table | type    | possible_keys   | key      | key_len | ref                      | rows | Extra        |  
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+  
|  1 | SIMPLE       | A      | range   | PRIMARY         | PRIMARY | 4        | NULL                     |    90 | Using where |  
|  1 | SIMPLE       | B      | eq_ref | PRIMARY         | PRIMARY | 4        | joomla_test . A . catid      |    1 |             |  
|  1 | SIMPLE       | C      | eq_ref | PRIMARY         | PRIMARY | 4        | joomla_test . A . sectionid |    1 | Using index |  
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+  
3 rows in set ( 0.05 sec )  

再次進行排序操作的時候蒜魄，Using filesoft也沒有再出現(xiàn)

mysql > explain select A.id , A.title, B.title 
from base_content A 
left join base_categories B on A.catid = B.id 
left join base_sections C on A.sectionid = C.id 
where A.id < 100 
order by A.id ;  
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+  
| id | select_type | table | type    | possible_keys | key      | key_len | ref                      | rows | Extra        |  
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+  
|  1 | SIMPLE       | A      | range   | PRIMARY        | PRIMARY | 4        | NULL                     |  105 | Using where |  
|  1 | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . catid      |    1 |             |  
|  1 | SIMPLE       | C      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . sectionid |    1 | Using index |  
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+  
3 rows in set ( 0.00 sec )  

結論：

對where條件里涉及到的字段扔亥，Mysql會使用索引進行搜索，而這個索引的使用也對排序的效率有很好的提升

測試

分別讓以下兩個SQL語句執(zhí)行200次：

select A.id , A.title , B.title 
from base_content  A 
left join base_categories B on A.catid = B.id 
left join base_sections C on A.sectionid = C.id  

select A.id, A.title, B.title 
from base_content A , base_categories B, base_sections C 
where A.catid = B.id and   A.sectionid = C.id 

select A.id , A.title , B.title 
from base_content A 
left join base_categories B on A.catid = B.id
left join base_sections C on   A.sectionid = C.id 　 
order by rand () 
limit 10 

select A.id from   base_content A 
left join base_categories B on B.id = A.catid 
left join base_sections C on A.sectionid = C.id
order by A.id  

結果是第(1)條平均用時27s 谈为，第(2)條平均用時54s 旅挤，第(3)條平均用時80s ，第(4)條平均用時3s 伞鲫。

用explain觀察第(3)條語句的執(zhí)行情況粘茄，會發(fā)現(xiàn)它創(chuàng)建了temporary臨時表來進行排序。

知識點：

1. 對需要查詢和排序的字段要加索引秕脓。
2. 盡量少地連接表柒瓣。left join 比普通連接查詢效率要高儒搭，注意觀察索引是否起了作用。
3. 排序盡量對第一個表的索引字段進行嘹朗，可以避免mysql創(chuàng)建臨時表师妙，這是非常耗資源的。
4. 對where條件里涉及到的字段屹培，應適當?shù)靥砑铀饕ǎ@樣會對排序操作有優(yōu)化的作用。
5. 如果說在分頁時我們能先得到主鍵褪秀，再根據(jù)主鍵查詢相關內容蓄诽，也能得到查詢的優(yōu)化效果。
6. 避免使用order by rand()媒吗。在執(zhí)行過程中用show processlist查看仑氛，會發(fā)現(xiàn)第(3)條有Copying to tmp table on disk。
7. Slow queries 檢查一下是哪些語句降低的Mysql 的執(zhí)行效率闸英，并進行定期優(yōu)化锯岖。

優(yōu)化GROUP BY語句

如果查詢包括GROUP BY 但想要避免排序結果的消耗，則可以指定ORDER By NULL禁止排序

例如：

explain select id, sum(moneys) from sales2 group by id \G     
explain select id, sum(moneys) from sales2 group by id order by null \G   

比較發(fā)現(xiàn)第一條語句會比第二句在Extra:里面多了Using filesort.而恰恰filesort是最耗時的甫何。

優(yōu)化ORDER BY語句

在某些情況中出吹，MySQL可以使用一個索引來滿足ORDER BY子句，而不需要額外的排序辙喂。WHERE 條件和 ORDER BY使用相同的索引捶牢，并且ORDER BY的順序和索引順序相同，并且ORDER BY的字段都是升序或者都是降序巍耗。

例如：

SELECT * FROM t1 ORDER BY key_part1,key_part2,....:  
SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC,key_part2 DESC;  
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;  

但是以下的情況不使用索引：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;  
--ORDER by的字段混合ASC 和 DESC  

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;  
--用于查詢行的關鍵字與ORDER BY 中所使用的不相同 

SELECT * FROM t1 ORDER BY key1, key2;  
--對不同的關鍵字使用ORDER BY  

優(yōu)化LIMIT分頁

當需要分頁操作時秋麸，通常會使用LIMIT加上偏移量的辦法實現(xiàn)，同時加上合適的ORDER BY字句炬太。

如果有對應的索引灸蟆，通常效率會不錯，否則娄琉，MySQL需要做大量的文件排序操作次乓。

當偏移量非常大的時候，比如：LIMIT 20000 20這樣的查詢孽水，MySQL需要查詢120020條記錄然后只返回20條記錄票腰，前面的20000條都將被拋棄，這樣的代價非常高女气。

優(yōu)化這種查詢一個最簡單的辦法就是盡可能的使用覆蓋索引掃描杏慰，而不是查詢所有的列。然后根據(jù)需要做一次關聯(lián)查詢再返回所有的列。

測試：

select film_id, description 
from base_film
order by title limit 50,5;

如果這張表非常大缘滥，那么這個查詢最好改成下面的樣子：

select film.film_id, film.description 
from base_film INNER JOIN(
    select film_id from base_film order by title limit 50,5
) as tmp USING(film_id);

這里的延遲關聯(lián)將大大提升查詢效率轰胁，讓MySQL掃描盡可能少的頁面，獲取需要訪問的記錄后在根據(jù)關聯(lián)列回原表查詢所需要的列朝扼。

有時候如果可以使用書簽記錄上次取數(shù)據(jù)的位置赃阀，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用OFFSET擎颖，比如下面的查詢：

select id from t limit 10000, 10;
select id from t where id > 10000 limit 10;

其他優(yōu)化的辦法還包括使用預先計算的匯總表榛斯，或者關聯(lián)到一個冗余表，冗余表中只包含主鍵列和需要做排序的列搂捧。

優(yōu)化UNION

MySQL處理UNION的策略是先創(chuàng)建臨時表驮俗，然后再把各個查詢結果插入到臨時表中，最后再來做查詢允跑。因此很多優(yōu)化策略在UNION查詢中都沒有辦法很好的時候王凑。經常需要手動將WHERE、LIMIT聋丝、ORDER BY等字句“下推”到各個子查詢中索烹，以便優(yōu)化器可以充分利用這些條件先優(yōu)化。

除非確實需要服務器去重弱睦，否則就一定要使用UNION ALL术荤，如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項每篷，這會導致整個臨時表的數(shù)據(jù)做唯一性檢查，這樣做的代價非常高端圈。當然即使使用ALL關鍵字焦读，MySQL總是將結果放入臨時表，然后再讀出舱权，再返回給客戶端矗晃。雖然很多時候沒有這個必要，比如有時候可以直接把每個子查詢的結果返回給客戶端宴倍。

特定類型查詢優(yōu)化

優(yōu)化COUNT()查詢

COUNT()有兩種不同的作用张症，其一是統(tǒng)計某個列值的數(shù)量，其二是統(tǒng)計行數(shù)鸵贬。

統(tǒng)計列值時俗他，要求列值是非空的，它不會統(tǒng)計NULL阔逼。如果確認括號中的表達式不可能為空時兆衅，實際上就是在統(tǒng)計行數(shù)。

最簡單的就是當使用COUNT(*)時，并不是我們所想象的那樣擴展成所有的列羡亩，實際上摩疑，它會忽略所有的列而直接統(tǒng)計行數(shù)。

我們最常見的誤解也就在這兒畏铆，在括號內指定了一列卻希望統(tǒng)計結果是行數(shù)雷袋，而且還常常誤以為前者的性能會更好。

但實際并非這樣辞居，如果要統(tǒng)計行數(shù)楷怒，直接使用COUNT(*)，意義清晰速侈，且性能更好率寡。

有時候某些業(yè)務場景并不需要完全精確的COUNT值，可以用近似值來代替倚搬，EXPLAIN出來的行數(shù)就是一個不錯的近似值冶共，而且執(zhí)行EXPLAIN并不需要真正地去執(zhí)行查詢，所以成本非常低每界。通常來說捅僵，執(zhí)行COUNT()都需要掃描大量的行才能獲取到精確的數(shù)據(jù)，因此很難優(yōu)化眨层，MySQL層面還能做得也就只有覆蓋索引了庙楚。如果不還能解決問題，只有從架構層面解決了趴樱，比如添加匯總表馒闷，或者使用redis這樣的外部緩存系統(tǒng)。

優(yōu)化關聯(lián)查詢

在大數(shù)據(jù)場景下叁征，表與表之間通過一個冗余字段來關聯(lián)纳账，要比直接使用JOIN有更好的性能。如果確實需要使用關聯(lián)查詢的情況下捺疼，需要特別注意的是：

確保ON和USING字句中的列上有索引疏虫。在創(chuàng)建索引的時候就要考慮到關聯(lián)的順序。當表A和表B用列c關聯(lián)的時候啤呼，如果優(yōu)化器關聯(lián)的順序是A卧秘、B，那么就不需要在A表的對應列上創(chuàng)建索引官扣。沒有用到的索引會帶來額外的負擔翅敌，一般來說，除非有其他理由醇锚，只需要在關聯(lián)順序中的第二張表的相應列上創(chuàng)建索引（具體原因下文分析）哼御。

確保任何的GROUP BY和ORDER BY中的表達式只涉及到一個表中的列坯临，這樣MySQL才有可能使用索引來優(yōu)化。
要理解優(yōu)化關聯(lián)查詢的第一個技巧恋昼，就需要理解MySQL是如何執(zhí)行關聯(lián)查詢的看靠。當前MySQL關聯(lián)執(zhí)行的策略非常簡單，它對任何的關聯(lián)都執(zhí)行嵌套循環(huán)關聯(lián)操作液肌，即先在一個表中循環(huán)取出單條數(shù)據(jù)挟炬，然后在嵌套循環(huán)到下一個表中尋找匹配的行，依次下去嗦哆，直到找到所有表中匹配的行為為止谤祖。然后根據(jù)各個表匹配的行，返回查詢中需要的各個列老速。

太抽象了粥喜？以上面的示例來說明，比如有這樣的一個查詢：

select A.xName, b.yName
from A INNER JOIN B USING(c)
WHERE A.xName IN (5,6)

假設MySQL按照查詢中的關聯(lián)順序A橘券、B來進行關聯(lián)操作额湘，那么可以用下面的偽代碼表示MySQL如何完成這個查詢：

outer_iterator = select A.xName, A.c 
from A 
where A.xName IN(5,6);
outer_row = outer_iterator.next;
while(outer_row){
    inner_iterator = SELECT B.yName from B WHERE B.c = outer_row.c;
    inner_row = inner_iterator.next;
    while(outer_row) {
        outpur[inner_row.yName, outer_row.xx];
        inner_row = inner_iterator.next;
    }
    outer_row = outer_iterator.next;
}

可以看到，最外層的查詢是根據(jù)A.xName旁舰，A.c上如果有索引的話锋华，整個關聯(lián)查詢也不會使用。再看內層的查詢箭窜，很明顯B.c上如果有索引的話毯焕，能夠加速查詢，因此只需要在關聯(lián)順序中的第二張表的相應列上創(chuàng)建索引即可磺樱。

1纳猫、MySQL不會使用索引的情況：非獨立的列
“獨立的列”是指索引列不能是表達式的一部分，也不能是函數(shù)的參數(shù)竹捉。比如：

select * from t where id + 1 = 15

其等價于 id = 14续担，但是MySQL無法自動解析這個表達式，使用函數(shù)是同樣的道理活孩。

2、前綴索引

如果列很長乖仇，通澈度澹可以索引開始的部分字符，這樣可以有效節(jié)約索引空間乃沙，從而提高索引效率起趾。

3、多列索引和索引順序

在多數(shù)情況下警儒，在多個列上建立獨立的索引并不能提高查詢性能训裆。

因為MySQL不知道選擇哪個索引的查詢效率更好眶根，所以在老版本，比如MySQL5.0之前就會隨便選擇一個列的索引边琉，而新的版本會采用合并索引的策略属百。

示例：

在一張電影演員表中，在actor_id和film_id兩個列上都建立了獨立的索引变姨，然后有如下查詢：

select film_id, actor_id 
from film_actor 
where actor_id = 1 or film_id = 1

老版本的MySQL會隨機選擇一個索引族扰，但新版本做如下的優(yōu)化：

select film_id, actor_id from film_actor where actor_id = 1 
union all
select film_id, actor_id from film_actor where film_id and actor_id <> 1

當出現(xiàn)多個索引做相交操作時（多個AND條件），通常來說一個包含所有相關列的索引要優(yōu)于多個獨立索引定欧。
當出現(xiàn)多個索引做聯(lián)合操作時（多個OR條件）渔呵，對結果集的合并、排序等操作需要耗費大量的CPU和內存資源砍鸠，特別是當其中的某些索引的選擇性不高扩氢，需要返回合并大量數(shù)據(jù)時，查詢成本更高爷辱。所以這種情況下還不如走全表掃描录豺。

結論：

如果發(fā)現(xiàn)有索引合并（Extra字段出現(xiàn)Using union），檢查查詢和表結構托嚣，檢查索引（或許一個包含所有相關列的多列索引更適合）巩检。

多列索引時索引的順序對于查詢是至關重要的，應該把選擇性更高的字段放到索引的前面示启，這樣通過第一個字段就可以過濾掉大多數(shù)不符合條件的數(shù)據(jù)兢哭。

索引選擇性

索引選擇性是指不重復的索引值和數(shù)據(jù)表的總記錄數(shù)的比值，選擇性越高查詢效率越高夫嗓，因為選擇性越高的索引可以讓MySQL在查詢時過濾掉更多的行迟螺。
唯一索引的選擇性是1，這是最好的索引選擇性舍咖，性能也是最好的矩父。

示例：

select * from base_payment where staff_id = 2 and customer_id = 785

是應該創(chuàng)建(staff_id,customer_id)的索引還是應該顛倒一下順序？

selct count(distinct staff_id)/count(*) as staff_id_selectivity,
        count(distinct customer_id)/count(*) as customer_id_selectivity,
        count(*) from base_payment

哪個字段的選擇性更接近1就把哪個字段索引前面排霉，多數(shù)情況下使用這個原則沒有任何問題窍株，但仍然注意你的數(shù)據(jù)中是否存在一些特殊情況。

示例：

比如要查詢某個用戶組下有過交易的用戶信息：

select user_id from base_trade 
where user_group_id = 1 and trade_amount > 0

MySQL為這個查詢選擇了索引(user_group_id,trade_amount)攻柠，看起來沒有任何問題球订。

但實際情況是這張表的大多數(shù)數(shù)據(jù)都是從老系統(tǒng)中遷移過來的，由于新老系統(tǒng)的數(shù)據(jù)不兼容瑰钮，所以就給老系統(tǒng)遷移過來的數(shù)據(jù)賦予了一個默認的用戶組冒滩。
這種情況下，通過索引掃描的行數(shù)跟全表掃描基本沒什么區(qū)別浪谴，索引也就起不到任何作用开睡。

經驗法則可以指導我們開發(fā)和設計因苹，但實際業(yè)務場景下的某些特殊情況可能會摧毀你的整個設計。

4篇恒、避免多個范圍條件

實際開發(fā)中扶檐，我們會經常使用多個范圍條件，比如想查詢某個時間段內登錄過的用戶：

select USER.* from base_user USER
where login_time > '2019-01-01' 
and age between 18 and 30

這個查詢有一個問題：它有兩個范圍條件婚度，login_time列和age列蘸秘，MySQL可以使用login_time列的索引或者age列的索引，但無法同時使用它們蝗茁。

5醋虏、覆蓋索引

如果一個索引包含或者說覆蓋所有需要查詢的字段的值，那么就沒有必要再回表查詢哮翘，這就稱為覆蓋索引颈嚼。

覆蓋索引可以極大的提高性能，因為查詢只需要掃描索引會帶來許多好處：

• 索引條目遠小于數(shù)據(jù)行大小饭寺，如果只讀取索引阻课，極大減少數(shù)據(jù)訪問量
• 索引是有按照列值順序存儲的，對于I/O密集型的范圍查詢要比隨機從磁盤讀取每一行數(shù)據(jù)的IO要少的多

6艰匙、使用索引掃描來排序

MySQL有兩種方式可以生產有序的結果集

• 其一是對結果集進行排序的操作限煞，
• 其二是按照索引順序掃描得出的結果自然是有序的。

如果type列的值為index表示使用了索引掃描來做排序员凝。掃描索引本身很快署驻，因為只需要從一條索引記錄移動到相鄰的下一條記錄。

但如果索引本身不能覆蓋所有需要查詢的列健霹，那么就不得不每掃描一條索引記錄就回表查詢一次對應的行旺上。
這個讀取操作基本上是隨機I/O，因此按照索引順序讀取數(shù)據(jù)的速度通常要比順序地全表掃描要慢糖埋。

知識點：

• 索引最好既能夠滿足排序宣吱，又滿足查詢。只有當索引的列順序和ORDER BY子句的順序完全一致瞳别，并且所有列的排序方向也一樣時征候，才能夠使用索引來對結果做排序。
• 如果查詢需要關聯(lián)多張表祟敛，則只有ORDER BY子句引用的字段全部為第一張表時倍奢，才能使用索引做排序。
• ORDER BY子句和查詢的限制是一樣的垒棋，都要滿足最左前綴的要求，其他情況下都需要執(zhí)行排序操作痪宰，而無法利用索引排序叼架。

示例

有一種情況例外畔裕，就是最左的列被指定為常數(shù)

// 最左列為常數(shù)，索引：(date,staff_id,customer_id)

select staff_id,customer_id from base_staff where date = '2015-06-01' order by staff_id,customer_id

7乖订、冗余和重復索引

立即刪除扮饶。冗余索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引。比如有一個索引(A,B)乍构，再創(chuàng)建索引(A)就是冗余索引甜无。

大多數(shù)情況下都應該盡量擴展已有的索引而不是創(chuàng)建新索引，冗余索引經常發(fā)生在為表添加新索引時哥遮，比如有人新建了索引(A,B)岂丘，但這個索引不是擴展已有的索引(A)。

但有極少情況下出現(xiàn)性能方面的考慮需要冗余索引眠饮，比如擴展已有索引而導致其變得過大奥帘，從而影響到其他使用該索引的查詢。

8仪召、定期檢查寨蹋，可刪除長期未使用的索引

只有當索引幫助提高查詢速度帶來的好處大于其帶來的額外工作時，索引才是有效的扔茅。所以 explain 后再提測是一種美德已旧。

原文鏈接：http://www.reibang.com/p/ea23c90282eb