一、mysql innodDB中組合索引中范圍查詢(xún)后的條件索引會(huì)失效的原因

表建立聯(lián)合索引(a,b,c)，查詢(xún)條件a=1,b>2,c=3，此時(shí)c條件的索引會(huì)失效。簡(jiǎn)單點(diǎn)來(lái)說(shuō)，該組合索引是一個(gè)以a字段排序而b與c相對(duì)有序的B+樹(shù)，引擎可以通過(guò)二分查找定位到a=1的數(shù)據(jù)，b在a=1確定得情況下是有序的（所以b的有序是相對(duì)的），依然可以通過(guò)二分查找取出所有b大于2的數(shù)據(jù)，但這些數(shù)據(jù)的b字段可能有很多個(gè)不同的值，所以c字段是無(wú)序的，無(wú)法用二分查找來(lái)查詢(xún)c=3的數(shù)據(jù)，故c用不到索引。

當(dāng)進(jìn)行范圍查詢(xún)操作時(shí)，MySQL需要對(duì)組合索引中的非范圍查詢(xún)條件逐個(gè)進(jìn)行檢查，以過(guò)濾掉不符合要求的記錄，直至掃描到范圍查詢(xún)條件時(shí)，由于范圍查詢(xún)操作具有不連續(xù)的特點(diǎn)，因此這個(gè)范圍查詢(xún)條件之后的記錄數(shù)據(jù)量會(huì)變得非常大，通常會(huì)超過(guò)MySQL的最大行數(shù)限制，這就導(dǎo)致了MySQL放棄使用這個(gè)組合索引，而改為全表掃描（full table scan）來(lái)獲取結(jié)果。這也是為什么范圍查詢(xún)操作容易導(dǎo)致MySQL查詢(xún)性能降低的原因之一。

例如存在索引數(shù)據(jù)如：(a=1,b=3,c=3)，(a=1,b=4,c=0)，(a=1,b=4,c=3)，(a=1,b=4,c=0)。上述數(shù)據(jù)均滿(mǎn)足a=1&b>2，但是c的數(shù)據(jù)卻不是連續(xù)的，所以沒(méi)有辦法通過(guò)c索引進(jìn)行過(guò)濾，所以字段c并不能夠參與到索引刷選中。

二、組合索引

1、組合索引的使用

本文中組合索引的定義為（MySQL）：

ALTER TABLE table_name ADD INDEX (col1,col2,col3);

2、組合索引的本質(zhì)

當(dāng)創(chuàng)建(col1,col2,col3)組合索引時(shí)，相當(dāng)于創(chuàng)建了(col)單列索引，(clo1,clo2)組合索引以及(col1,col2,col3)組合索引想要索引生效，只能使用col1和col1,col2和col1,col2,col3三種組合；當(dāng)然，col1,col3組合也可以，但實(shí)際上只用到了col1的索引，col3并沒(méi)有用到。

3、示例

組合索引相當(dāng)于一個(gè)按照姓氏——名字的一個(gè)電話簿，只能先確定姓氏才可以命中索引，下列可以正確命中組合索引的語(yǔ)句（ = 和IN直接的字段都可以亂序，MySQL的查詢(xún)優(yōu)化器可以?xún)?yōu)化成索引識(shí)別的形式）

使用到索引的情況如下：

col_a = "some value"col_a = "some value" and col_b = "some value"col_a = "some value" and col_c = "some value"col_a = "some value" and col_b = "some value" and col_c = "some value"col_a = "some value" and col_c = "some value" and col_b = "some value"col_b = "some value" and col_a = "some value" and col_c = "some value"col_b = "some value" and col_c = "some value" and col_a = "some value"col_c = "some value" and col_a = "some value" and col_b = "some value"col_c = "some value" and col_b = "some value" and col_a = "some value"

未使用索引的情況：

col_b = "some value"col_c = "some value"col_b = "some value" and col_c = "some value"col_c = "some value" and col_b = "some value"

總結(jié)，組合索引從最左開(kāi)始組合，只要包含名列前茅列的查詢(xún)都會(huì)用到該組合索引。

4、為什么要使用組合索引

減少開(kāi)銷(xiāo)：建一個(gè)聯(lián)合索引(col1,col2,col3)，實(shí)際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個(gè)索引。每多一個(gè)索引，都會(huì)增加寫(xiě)操作的開(kāi)銷(xiāo)和磁盤(pán)空間的開(kāi)銷(xiāo)。對(duì)于大量數(shù)據(jù)的表，使用組合索引會(huì)大大的減少開(kāi)銷(xiāo)。覆蓋索引：對(duì)組合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過(guò)遍歷索引取得數(shù)據(jù)，而無(wú)需回表，這減少了很多的隨機(jī)io操作。減少io操作，特別的隨機(jī)io其實(shí)是dba主要的優(yōu)化策略。所以，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。效率高：索引列越多，通過(guò)索引篩選出的數(shù)據(jù)越少。

5、SQL中組合索引和普通索引區(qū)別

組合索引中列的順序非常重要，從左原則：a,ab,ba,abc。

單個(gè)索引：一個(gè)一個(gè)起作用，也就是說(shuō)有三個(gè)單個(gè)索引，哪個(gè)條件查詢(xún)?cè)谇澳膫€(gè)起作用，其他不起作用。

結(jié)論：

一般來(lái)說(shuō)，列表搜索需要多個(gè)列查詢(xún)，此時(shí)就可以使用聯(lián)合索引，都是and的關(guān)系。什么時(shí)候需要?jiǎng)?chuàng)建索引：

where條件會(huì)經(jīng)常出現(xiàn)的，并且當(dāng)前表的數(shù)量比較大。where條件中是用and而非or的時(shí)候。組合索引比單個(gè)索引更適合，因?yàn)樗饕加靡欢ù疟P(pán)空間，也就說(shuō)明有一定的開(kāi)銷(xiāo)，如果多個(gè)單個(gè)索引比較多，那么多資源的浪費(fèi)也比較多，組合索引相當(dāng)于對(duì)多個(gè)列建索引，并且只建一次，and條件下非常適合。

延伸閱讀1：MySQL簡(jiǎn)介

MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB 公司開(kāi)發(fā)，屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是較好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)) 應(yīng)用軟件之一。