一、mysql innodDB中組合索引中范圍查詢后的條件索引會失效的原因

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

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

例如存在索引數(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ù)均滿足a=1&b>2，但是c的數(shù)據(jù)卻不是連續(xù)的，所以沒有辦法通過c索引進行過濾，所以字段c并不能夠參與到索引刷選中。

二、組合索引

1、組合索引的使用

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

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

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

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

3、示例

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

使用到索引的情況如下：

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é)，組合索引從最左開始組合，只要包含名列前茅列的查詢都會用到該組合索引。

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

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

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

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

單個索引：一個一個起作用，也就是說有三個單個索引，哪個條件查詢在前哪個起作用，其他不起作用。

結(jié)論：

一般來說，列表搜索需要多個列查詢，此時就可以使用聯(lián)合索引，都是and的關(guān)系。什么時候需要創(chuàng)建索引：

where條件會經(jīng)常出現(xiàn)的，并且當前表的數(shù)量比較大。where條件中是用and而非or的時候。組合索引比單個索引更適合，因為索引占用一定磁盤空間，也就說明有一定的開銷，如果多個單個索引比較多，那么多資源的浪費也比較多，組合索引相當于對多個列建索引，并且只建一次，and條件下非常適合。

延伸閱讀1：MySQL簡介

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