?2016-2026 Matools All rights reserved,
粵ICP備17059708號
最左前綴匹配原則：在MySQL建立聯(lián)合索引時會遵守最左前綴匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時從聯(lián)合索引的最左邊開始匹配。
要想理解聯(lián)合索引的最左匹配原則，先來理解下索引的底層原理。索引的底層是一顆B+樹，那么聯(lián)合索引的底層也就是一顆B+樹，只不過聯(lián)合索引的B+樹節(jié)點中存儲的是鍵值。由于構(gòu)建一棵B+樹只能根據(jù)一個值來確定索引關(guān)系，所以數(shù)據(jù)庫依賴聯(lián)合索引最左的字段來構(gòu)建。
舉例：創(chuàng)建一個（a,b）的聯(lián)合索引，那么它的索引樹就是下圖的樣子。
?
可以看到a的值是有順序的，1，1，2，2，3，3，而b的值是沒有順序的1，2，1，4，1，2。但是我們又可發(fā)現(xiàn)a在等值的情況下，b值又是按順序排列的，但是這種順序是相對的。這是因為MySQL創(chuàng)建聯(lián)合索引的規(guī)則是首先會對聯(lián)合索引的最左邊第一個字段排序，在第一個字段的排序基礎(chǔ)上，然后在對第二個字段進(jìn)行排序。所以b=2這種查詢條件沒有辦法利用索引。
由于整個過程是基于explain結(jié)果分析的，那接下來在了解下explain中的type字段和key_lef字段。
1.type：聯(lián)接類型。下面給出各種聯(lián)接類型,按照從最佳類型到最壞類型進(jìn)行排序:（重點看ref,rang,index）
system：表只有一行記錄（等于系統(tǒng)表），這是const類型的特例，平時不會出現(xiàn)，可以忽略不計
const：表示通過索引一次就找到了，const用于比較primary?key?或者?unique索引。因為只需匹配一行數(shù)據(jù)，所有很快。如果將主鍵置于where列表中，mysql就能將該查詢轉(zhuǎn)換為一個const
eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵?或?唯一索引掃描。
注意：ALL全表掃描的表記錄最少的表如t1表
ref
：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質(zhì)是也是一種索引訪問，它返回所有匹配某個單獨值的行，然而他可能會找到多個符合條件的行，所以它應(yīng)該屬于查找和掃描的混合體。
range
：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了那個索引。一般就是在where語句中出現(xiàn)了bettween、<、>、in等的查詢。這種索引列上的范圍掃描比全索引掃描要好。只需要開始于某個點，結(jié)束于另一個點，不用掃描全部索引。
index
：Full?Index?Scan，index與ALL區(qū)別為index類型只遍歷索引樹。這通常為ALL塊，應(yīng)為索引文件通常比數(shù)據(jù)文件小。（Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬盤讀取）
ALL：Full?Table?Scan，遍歷全表以找到匹配的行
2.key_len：
顯示MySQL實際決定使用的索引的長度。如果索引是NULL，則長度為NULL。如果不是NULL，則為使用的索引的長度。所以通過此字段就可推斷出使用了那個索引。
計算規(guī)則：
1.定長字段，int占用4個字節(jié)，date占用3個字節(jié)，char(n)占用n個字符。
2.變長字段varchar(n)，則占用n個字符+兩個字節(jié)。
3.不同的字符集，一個字符占用的字節(jié)數(shù)是不同的。Latin1編碼的，一個字符占用一個字節(jié)，gdk編碼的，一個字符占用兩個字節(jié)，utf-8編碼的，一個字符占用三個字節(jié)。
?。ㄓ捎谖覕?shù)據(jù)庫使用的是Latin1編碼的格式，所以在后面的計算中，一個字符按一個字節(jié)算）
4.對于所有的索引字段，如果設(shè)置為NULL，則還需要1個字節(jié)。
接下來進(jìn)入正題?。?！
示例：
首先創(chuàng)建一個表
?該表中對id列.name列.age列建立了一個聯(lián)合索引
id_name_age_index，實際上相當(dāng)于建立了三個索引（id）（id_name）（id_name_age）。
下面介紹下可能會使用到該索引的幾種情況：
1.全值匹配查詢時
通過觀察上面的結(jié)果圖可知，where后面的查詢條件，不論是使用（id，age，name）（name，id，age）還是（age，name，id）順序，在查詢時都使用到了聯(lián)合索引，可能有同學(xué)會疑惑，為什么底下兩個的搜索條件明明沒有按照聯(lián)合索引從左到右進(jìn)行匹配，卻也使用到了聯(lián)合索引？
這是因為MySQL中有查詢優(yōu)化器explain
，所以sql語句中字段的順序不需要和聯(lián)合索引定義的字段順序相同，查詢優(yōu)化器會判斷糾正這條SQL語句以什么樣的順序執(zhí)行效率高，最后才能生成真正的執(zhí)行計劃，所以不論以何種順序都可使用到聯(lián)合索引。另外通過觀察上面三個圖中的key_len字段，也可說明在搜索時使用的聯(lián)合索引中的（id_name_age）索引，因為id為int型，允許null，所以占5個字節(jié)，name為char(10)，允許null，又使用的是latin1編碼，所以占11個字節(jié)，age為int型允許null，所以也占用5個字節(jié)，所以該索引長度為21（5+11+5），而上面key_len的值也正好為21，可證明使用的（id_name_age）索引。
2.匹配最左邊的列時
?
該搜索是遵循最左匹配原則的，通過key字段也可知，在搜索過程中使用到了聯(lián)合索引，且使用的是聯(lián)合索引中的（id）索引，因為key_len字段值為5，而id索引的長度正好為5（因為id為int型，允許null，所以占5個字節(jié)）。
由于id到name是從左邊依次往右邊匹配，這兩個字段中的值都是有序的，所以也遵循最左匹配原則，通過key字段可知，在搜索過程中也使用到了聯(lián)合索引，但使用的是聯(lián)合索引中的（id_name）索引，因為key_len字段值為16，而(id_name)索引的長度正好為16（因為id為int型，允許null，所以占5個字節(jié)，name為char(10)，允許null，又使用的是latin1編碼，所以占11個字節(jié)）。
由于上面三個搜索都是從最左邊id依次向右開始匹配的，所以都用到了id_name_age_index聯(lián)合索引。
那如果不是依次匹配呢？
通過key字段可知，在搜索過程中也使用到了聯(lián)合索引，但使用的是聯(lián)合索引中的（id）索引，從key_len字段也可知。因為聯(lián)合索引樹是按照id字段創(chuàng)建的，但age相對于id來說是無序的，只有id只有序的，所以他只能使用聯(lián)合索引中的id索引。
通過觀察發(fā)現(xiàn)上面key字段發(fā)現(xiàn)在搜索中也使用了id_name_age_index索引，可能許多同學(xué)就會疑惑它并沒有遵守最左匹配原則，按道理會索引失效，為什么也使用到了聯(lián)合索引？因為沒有從id開始匹配，且name單獨來說是無序的，所以它確實不遵循最左匹配原則，然而從type字段可知，它雖然使用了聯(lián)合索引，但是它是對整個索引樹進(jìn)行了掃描，正好匹配到該索引，與最左匹配原則無關(guān)，一般只要是某聯(lián)合索引的一部分，但又不遵循最左匹配原則時，都可能會采用index類型的方式掃描，但它的效率遠(yuǎn)不如最做匹配原則的查詢效率高，index類型類型的掃描方式是從索引第一個字段一個一個的查找，直到找到符合的某個索引，與all不同的是，index是對所有索引樹進(jìn)行掃描，而all是對整個磁盤的數(shù)據(jù)進(jìn)行全表掃描。
? 這兩個結(jié)果跟上面的是同樣的道理，由于它們都沒有從最左邊開始匹配，所以沒有用到聯(lián)合索引，使用的都是index全索引掃描。
3.匹配列前綴
如果id是字符型，那么前綴匹配用的是索引，中墜和后綴用的是全表掃描。
select * from staffs where id like 'A%';//前綴都是排好序的，使用的都是聯(lián)合索引 select * from
staffswhere id like '%A%';//全表查詢 select * from staffs where id like '%A';//全表查詢
4.匹配范圍值
? 在匹配的過程中遇到<>=號，就會停止匹配，但id本身就是有序的，所以通過possible_keys字段和key_len
字段可知，在該搜索過程中使用了聯(lián)合索引的id索引（因為id為int型，允許null，所以占5個字節(jié)），且進(jìn)行的是rang范圍查詢。
由于不遵循最左匹配原則，且在id<4的范圍中，age是無序的，所以使用的是index全索引掃描。
? 不遵循最左匹配原則，但在數(shù)據(jù)庫中id<2的只有一條（id），所以在id<2的范圍中，age是有序的，所以使用的是rang范圍查詢。
? 不遵循最左匹配原則，而age又是無序的，所以進(jìn)行的全索引掃描。
5.準(zhǔn)確匹配第一列并范圍匹配其他某一列
由于搜索中有id=1，所以在id范圍內(nèi)age是無序的，所以只使用了聯(lián)合索引中的id索引。
?
熱門工具 換一換