mysql聯(lián)合索引的使用規(guī)則

聯(lián)合索引又叫復(fù)合索引。對(duì)于復(fù)合索引:Mysql從左到右的使用索引中的字段，一個(gè)查詢可以只使用索引中的一部份，但只能是最左側(cè)部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3種組合進(jìn)行查找，但不支持 b,c進(jìn)行查找 .當(dāng)最左側(cè)字段是常量引用時(shí)，索引就十分有效。

從一道有趣的題目開(kāi)始分析：

假設(shè)某個(gè)表有一個(gè)聯(lián)合索引（c1,c2,c3,c4）以下選項(xiàng)哪些字段使用了該索引：
A where c1=x and c2=x and c4>x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=? and c5=? order by c2,c3
E where c1=? and c2=? and c5=? order by c2,c3

下面我們開(kāi)始：

首先創(chuàng)建表：

CREATE TABLE t(
c1 CHAR(1) not null,
c2 CHAR(1) not null,
c3 CHAR(1) not null,
c4 CHAR(1) not null,
c5 CHAR(1) not null
)ENGINE myisam CHARSET UTF8;

有c1到c5 5個(gè)字段，特別說(shuō)明一下 字段類(lèi)型都是定長(zhǎng)char（1）類(lèi)型，并且非空，字符集是utf8（與計(jì)算索引使用字節(jié)數(shù)有關(guān)）

創(chuàng)建索引：

alter table t add index c1234(c1,c2,c3,c4);

插入2條數(shù)據(jù)：insert into t VALUES('1','1','1','1','1'),('2','2','2','2','2')

使用MySql Explain開(kāi)始分析題目結(jié)果：

A選項(xiàng)：

結(jié)果可以看出，c1,c2,c3,c4均使用到了該索引，而我們對(duì)A結(jié)果稍作更改：

將c2條件去掉后：

根據(jù)索引最左原則，c2字段沒(méi)有使用索引，c2之后的字段都不能使用索引。下面2圖我們對(duì)比下索引最左原則：

上圖結(jié)果顯示直接使用c3是全表查詢，無(wú)法使用該索引的，所以c3字段使用索引的前提是c1，c2兩字段均使用了索引。

即是索引的最左原則（左前綴原則）。

B選項(xiàng)：

key_len長(zhǎng)度說(shuō)明c1，c2字段用到了該索引，Extra顯示并沒(méi)有使用臨時(shí)表進(jìn)行排序，說(shuō)明排序是使用了索引的，但并沒(méi)有計(jì)算在key_len值中，也沒(méi)有起到連接c4的作用，說(shuō)明索引到c3這里是斷掉的。

排序其實(shí)是利用聯(lián)合索引直接完成了的，即：使用了c1234聯(lián)合索引，就已經(jīng)使得c1下c2，c2下c3，c3下c4是有序的了，所以實(shí)際是排序利用了索引，c3字段并沒(méi)有使用該索引。（這段寫(xiě)的時(shí)候總感覺(jué)有點(diǎn)別扭，不知道我理解的對(duì)不對(duì)，還有待更深層次的研究）

C選項(xiàng)：

使用group by 一般先生成臨時(shí)文件，再進(jìn)行排序，但是字段順序?yàn)閏2，c3時(shí)，并沒(méi)有用臨時(shí)表進(jìn)行排序，而是利用索引排序好的；當(dāng)group by字段為c3，c2時(shí)，由于與索引字段順序不一致，所以分組和排序并沒(méi)有利用到索引。

由key_len長(zhǎng)度確定，只有c1一個(gè)字段使用了索引。

D選項(xiàng)：

order by 和group by 類(lèi)似，字段順序與索引一致時(shí)，會(huì)使用索引排序；字段順序與索引不一致時(shí)，不使用索引。

由key_len長(zhǎng)度確定，只有c1一個(gè)字段使用了索引。

E選項(xiàng)：

其實(shí)選項(xiàng)E的結(jié)果分析在上述ABCD的結(jié)果中都分析過(guò)了，這里只有c1，c2字段使用了該索引。

綜上所述問(wèn)題答案：

A:四個(gè)字段均使用了該索引

B:c1，c2字段使用了該索引

C:c1字段使用該索引

D:c1字段使用該索引

E:c1，c2字段使用了該索引

總結(jié)：

索引的最左原則（左前綴原則），如（c1,c2,c3,c4....cN）的聯(lián)合索引，where 條件按照索引建立的字段順序來(lái)使用（不代表and條件必須按照順序來(lái)寫(xiě)），如果中間某列沒(méi)有條件，或使用like會(huì)導(dǎo)致后面的列不能使用索引。

索引也能用于分組和排序，分組要先排序，在計(jì)算平均值等等。所以在分組和排序中，如果字段順序可以按照索引的字段順序，即可利用索引的有序特性。

到此這篇關(guān)于mysql聯(lián)合索引的使用規(guī)則的文章就介紹到這了,更多相關(guān)mysql聯(lián)合索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！