1.背景

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

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

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

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

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

缺點(diǎn)：

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

復(fù)合索引使用建議：?jiǎn)伪碜詈貌灰^(guò)1個(gè)復(fù)合索引，單個(gè)復(fù)合索引最好不超過(guò)3個(gè)字段。一旦超過(guò)，就需要考慮必要性和是否有其他替代方案。

3.最左匹配原則

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

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

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

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

4.字段順序的影響

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

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

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

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

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

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

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

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

但如果where條件如下呢：

from t_user where c2 = 4; 

上述語(yǔ)句是否會(huì)走索引呢?這得分幾種情況來(lái)說(shuō)明。

執(zhí)行explan查詢(xún)c1為條件的SQL語(yǔ)句：

explain select * from t_user where c1 = 1; 

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

執(zhí)行explan查詢(xún)c2為條件的SQL語(yǔ)句：

explain select c2 from t_user where c2 = 4; 

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

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

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

explain select * from t_user where c2 = 4; 

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

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

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

單一索引：

(c1)，

復(fù)合索引：

(c1，c2)。

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

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

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

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

小結(jié)：

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

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

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

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

最新評(píng)論