1.背景

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

2.認(rèn)識(shí)復(fù)合索引

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

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

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

缺點(diǎn)：

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

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

3.最左匹配原則

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

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

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

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

4.字段順序的影響

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

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

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也會(huì)讓它們走同樣的索引。

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

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

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

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

但如果where條件如下呢：

from t_user where c2 = 4; 

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

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

explain select * from t_user where c1 = 1; 

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

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

explain select c2 from t_user where c2 = 4; 

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

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

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

explain select * from t_user where c2 = 4; 

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

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

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

單一索引：

(c1)，

復(fù)合索引：

(c1，c2)。

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

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

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

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

小結(jié)：

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

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

別問我為什么知道，因?yàn)樵趯?shí)踐中遇到過。同一條SQL語句，查詢條件不同，有可能會(huì)走索引，也有可能不會(huì)走索引。

到此這篇關(guān)于MySQL的復(fù)合索引總結(jié)的文章就介紹到這了,更多相關(guān)MySQL復(fù)合索引內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

香港快速服務(wù)器

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。