[[442711]]

本文轉(zhuǎn)載自微信公眾號「程序新視界」，作者二師兄。轉(zhuǎn)載本文請聯(lián)系程序新視界公眾號。

背景

最近頻繁出現(xiàn)慢SQL導(dǎo)致系統(tǒng)性能問題，于是決定針對索引進行一些優(yōu)化。一些表結(jié)構(gòu)本身已經(jīng)有了不少索引，如果再繼續(xù)添加索引，勢必會影響到插入數(shù)據(jù)的性能。那么，是否可以使用組合索引來達到目的呢?這篇文章咱們來一探究竟。

認識復(fù)合索引

如果where條件中使用到多個字段，并且需要對多個字段建立索引，此時就可以考慮采用復(fù)合索引(組合索引)。比如查詢地址時需要輸入省、市，那么在省、市上建立索引，當數(shù)據(jù)量大時會明顯提高查詢速度。

組合索引有啥優(yōu)勢呢?

• 減少查詢開銷：建立復(fù)合索引(c1,c2,c3)，實際上相當于建立了(c1),(c1,c2),(c1,c2,c3)三個索引。對于大表來說，可以極大減少開銷。
• 覆蓋索引：MySQL可以直接通過遍歷索引取得數(shù)據(jù)，而無需回表，減少了很多的隨機io操作。
• 效率高：索引列越多，通過索引篩選出來的數(shù)據(jù)就越少，從而提升查詢效率。

缺點：

• 索引字段越多，創(chuàng)建的索引越多，每個索引都會增加磁盤空間的開銷;
• 索引越多對查詢效率提升越高，但對需要更新索引的增刪改操作會有效率影響;

復(fù)合索引使用建議：單表最好不要超過1個復(fù)合索引，單個復(fù)合索引最好不超過3個字段。一旦超過，就需要考慮必要性和是否有其他替代方案。

最左匹配原則

復(fù)合索引遵從最左匹配原則，顧名思義，在組合索引中，最左側(cè)的字段優(yōu)先匹配。因此，在創(chuàng)建組合索引時，where子句中使用最頻繁的字段放在組合索引的最左側(cè)。

輔助索引是B+樹實現(xiàn)的，雖然可以指定多個列，但是每個列的比較優(yōu)先級不一樣，寫在前面的優(yōu)先比較高。一旦出現(xiàn)遺漏，在B+樹上就無法繼續(xù)搜索了(通過補齊等措施解決的除外)，因此是按照最左連續(xù)匹配來的。既然是在B+樹上搜索，對于條件的比較自然是要求精確匹配(即"="和"IN")。

在where子句中用到兩個字段c1和c2，那么創(chuàng)建索引時，兩個字段的順序應(yīng)該是(c1，c2)還是(c2，c1)呢?

正確的做法是：把重復(fù)值最少的放前面。比如，95%的值都不重復(fù)，則可考慮放最前面。

字段順序的影響

復(fù)合索引遵從最左匹配原則，那么在where查詢條件中的字段是否也需要按照索引的順序來寫呢?

比如，復(fù)合索引為(c1,c2,c3)，下面兩個查詢條件是否會對索引有影響呢?

select * from t_user where c1 = 1 and c2 = 4; 
select * from t_user where c2 = 4 and c1 = 1; 

看到有文章提出第一條SQL語句的效率更高，是否可信?兩種查詢方式條件一樣，結(jié)果也應(yīng)該一樣，正常來說Mysql也會讓它們走同樣的索引。

通過Mysql的查詢優(yōu)化器explain分析上述兩個條語句，會發(fā)現(xiàn)執(zhí)行計劃完全相同。也就是說：SQL語句中的字段順序并不需要與復(fù)合索引字段順序一致，查詢優(yōu)化器會自動調(diào)整順序。

如果說有效率影響，那么也就是查詢優(yōu)化器矯正順序的影響吧，幾乎可以忽略不計。

單字段是否可以觸發(fā)索引?

對于復(fù)合索引為(c1,c2,c3)，相當于(c1),(c1,c2),(c1,c2,c3)三個索引，如果查詢條件中只有c1，很顯然是會走索引的。

但如果where條件如下呢：

from t_user where c2 = 4; 

上述語句是否會走索引呢?這得分幾種情況來說明。

執(zhí)行explan查詢c1為條件的SQL語句：

explain select * from t_user where c1 = 1; 

上述語句走的索引類型為：ref。ref類型表示Mysql會根據(jù)特定的算法快速查找到符合條件的索引，而不會對索引中每一個數(shù)據(jù)都進行掃描判斷。這種類型的索引為了快速查出數(shù)據(jù)，索引就需要滿足一定的數(shù)據(jù)結(jié)構(gòu)。

執(zhí)行explan查詢c2為條件的SQL語句：

explain select c2 from t_user where c2 = 4; 

上述語句走的索引類型為：index。index類型表示Mysql會對整個索引進行掃描，只要是索引或索引的一部分Mysql就可能會采用index方類型的方式掃描。由于此種方式是一條數(shù)據(jù)一條數(shù)據(jù)查找，性能并不高。

在這個例子中，對查詢的字段有一定的要求，where中條件為c2，select中查詢出的字段也只能是c2，才會走index類型的索引。

如果將c2換成*或其他字段：

explain select * from t_user where c2 = 4; 

上述語句會發(fā)現(xiàn)，不再走index索引，而是走全表掃描了。這也從側(cè)面說明了Mysql為什么要講最左匹配原則了。

所以結(jié)論是：如果單個字段為復(fù)合索引的首個字段，則會正常走索引;如果單個字段是復(fù)合索引的其他字段，且僅有該字段出現(xiàn)在select后面，則會走index類型索引;而其他情況，則走全表掃描。

復(fù)合索引可以替代單一索引嗎?

單一索引：(c1)，復(fù)合索引：(c1，c2)。

當c1作為查詢條件時，單一索引和復(fù)合索引查詢速度幾乎一樣，甚至比復(fù)合索引還要略快。

如果僅用復(fù)合聚集索引的非起始列(c2)作為查詢條件的話，復(fù)合索引是不起任何作用的。

對于一張表來說，如果有復(fù)合索引(c1，c2)，則無需再建單一索引(c1)。

如果已經(jīng)存在單一索引(c1)，因查詢所需，可添加復(fù)合索引(c1，c2)來提升效率。

小結(jié)

本篇文章整理了Mysql復(fù)合索引使用時所需注意的一些知識點，在使用時可以通過explain來查看一下你的SQL語句是否走了索引，走了什么索引。

但還要了解的是：Mysql的執(zhí)行計劃和查詢的實際執(zhí)行過程并不完全吻合。

別問我為什么知道，因為在實踐中遇到過。同一條SQL語句，查詢條件不同，有可能會走索引，也有可能不會走索引。