前言：

眾所周知MySQL聯(lián)合索引遵循最左前綴匹配原則，在少數(shù)情況下也會(huì)不遵循（有興趣，可以翻一下上篇文章）

創(chuàng)建聯(lián)合索引的時(shí)候，建議優(yōu)先把區(qū)分度高的字段放在第一列。

至于怎么統(tǒng)計(jì)區(qū)分度，可以按照下面這種方式。

創(chuàng)建一張測(cè)試表，用來(lái)測(cè)試：

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `a` int NOT NULL,
  `b` int NOT NULL,
  `c` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='測(cè)試表';

統(tǒng)計(jì)每個(gè)字段的區(qū)分度：

select 
    count(distinct a)/count(*), 
    count(distinct b)/count(*),
    count(distinct c)/count(*)
from test;

值越大，區(qū)分度越高，優(yōu)先放在第一列。

很多人不知道聯(lián)合索引在B+樹中是怎么存儲(chǔ)的？我簡(jiǎn)單畫一下。

比如在（a,b）字段上面創(chuàng)建聯(lián)合索引，存儲(chǔ)結(jié)構(gòu)類似下面這樣：

葉子節(jié)點(diǎn)存儲(chǔ)全部數(shù)據(jù)，用雙鏈表指針相連，數(shù)據(jù)都是先按a字段排序，a字段的值相等時(shí)再按b字段排序。

a字段的值是全局有序的，分別有1,1,1,2,2,2。

b字段的值是全局無(wú)序的，分別有1,3,5,1,3,5，只有在a字段的值相等時(shí)才呈現(xiàn)出局部有序。

所以在進(jìn)行SQL查詢的時(shí)候，如果where條件中沒有a字段，只有b字段，是無(wú)法用到索引的，像下面這樣：

select * from test where b=1;

像有些文章上面說(shuō)的，在（a,b）兩個(gè)字段上創(chuàng)建聯(lián)合索引，就會(huì)創(chuàng)建兩個(gè)索引，分別是（a）和（a,b），這其實(shí)是一種不恰當(dāng)?shù)谋硎?，雖然結(jié)果是對(duì)的。

下面做幾道聯(lián)合索引的經(jīng)典面試題，試一下大家掌握的怎么樣？

第一題：

下面這條SQL，該怎么創(chuàng)建聯(lián)合索引？

SELECT * FROM test WHERE a = 1 and b = 1 and c = 1;

你以為的答案是（a,b,c），其實(shí)答案是6個(gè)，abc三個(gè)的排列組合，（a,b,c）、（a,c,b）、（b,a,c）、（b,c,a）、（c,a,b）、（c,b,a）。

MySQL優(yōu)化器為了適應(yīng)索引，會(huì)調(diào)整條件的順序。

再給面試官補(bǔ)充一句，區(qū)分度高的字段放在最前面，大大加分。

第二題：

下面這條SQL，該怎么創(chuàng)建聯(lián)合索引？

SELECT * FROM test WHERE a = 1 and b > 1 and c = 1;

考察的知識(shí)點(diǎn)是： 聯(lián)合索引遇到范圍匹配會(huì)停止，不會(huì)再匹配后面的索引字段。

所以答案應(yīng)該是：（a,c,b）和 （c,a,b）。

當(dāng)創(chuàng)建（a,c,b）和 （c,a,b）索引的時(shí)候，查詢會(huì)用到3個(gè)字段的索引，效率更高。

怎么判斷是用到了3個(gè)字段的索引，而不是只用到前兩個(gè)字段的索引呢？

有個(gè)非常簡(jiǎn)單的方法，看執(zhí)行計(jì)劃的索引長(zhǎng)度。

由于int類型的字段占4個(gè)字節(jié)，3個(gè)字段長(zhǎng)度剛好是12個(gè)字節(jié)。

第三題：

下面這條SQL，該怎么創(chuàng)建聯(lián)合索引？

SELECT * FROM test WHERE a in (1,2,3) and b > 1;

答案是（a,b）。in條件查詢會(huì)被轉(zhuǎn)換成等值查詢，可以驗(yàn)證一下：

可以看到用到了兩個(gè)字段的索引。

所以我們?cè)谄綍r(shí)做開發(fā)，盡量想辦法把范圍查詢轉(zhuǎn)換成in條件查詢，效率更高。

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