面試中常被提到的最左前綴匹配原則

2020 年 5 月 22 日
筆記
資料庫

最左前綴匹配原則：在MySQL建立聯合索引時會遵守最左前綴匹配原則，即最左優先，在檢索數據時從聯合索引的最左邊開始匹配。

　　要想理解聯合索引的最左匹配原則，先來理解下索引的底層原理。索引的底層是一顆B+樹，那麼聯合索引的底層也就是一顆B+樹，只不過聯合索引的B+樹節點中存儲的是鍵值。由於構建一棵B+樹只能根據一個值來確定索引關係，所以資料庫依賴聯合索引最左的欄位來構建。

舉例：創建一個（a,b）的聯合索引，那麼它的索引樹就是下圖的樣子。

　　可以看到a的值是有順序的，1，1，2，2，3，3，而b的值是沒有順序的1，2，1，4，1，2。但是我們又可發現a在等值的情況下，b值又是按順序排列的，但是這種順序是相對的。這是因為MySQL創建聯合索引的規則是首先會對聯合索引的最左邊第一個欄位排序，在第一個欄位的排序基礎上，然後在對第二個欄位進行排序。所以b=2這種查詢條件沒有辦法利用索引。

　　由於整個過程是基於explain結果分析的，那接下來在了解下explain中的type欄位和key_lef欄位。

　　1.type：聯接類型。下面給出各種聯接類型,按照從最佳類型到最壞類型進行排序:（重點看ref,rang,index）

　　　　system：表只有一行記錄（等於系統表），這是const類型的特例，平時不會出現，可以忽略不計
　　　　const：表示通過索引一次就找到了，const用於比較primary key 或者 unique索引。因為只需匹配一行數據，所有很快。如果將主鍵置於where列表中，mysql就能將該查詢轉換為一個const
　　　　eq_ref：唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見於主鍵或唯一索引掃描。
　　　　注意：ALL全表掃描的表記錄最少的表如t1表
　　　　ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質是也是一種索引訪問，它返回所有匹配某個單獨值的行，然而他可能會找到多個符合條件的行，所以它應該屬於查找和掃描的混合體。
　　　　range：只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了那個索引。一般就是在where語句中出現了bettween、<、>、in等的查詢。這種索引列上的範圍掃描比全索引掃描要好。只需要開始於某個點，結束於另一個點，不用掃描全部索引。
　　　　index：Full Index Scan，index與ALL區別為index類型只遍歷索引樹。這通常為ALL塊，應為索引文件通常比數據文件小。（Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬碟讀取）
　　　　ALL：Full Table Scan，遍歷全表以找到匹配的行

　　2.key_len：顯示MySQL實際決定使用的索引的長度。如果索引是NULL，則長度為NULL。如果不是NULL，則為使用的索引的長度。所以通過此欄位就可推斷出使用了那個索引。

　　　　計算規則：

　　　　1.定長欄位，int佔用4個位元組，date佔用3個位元組，char(n)佔用n個字元。

　　　　2.變長欄位varchar(n)，則佔用n個字元+兩個位元組。

　　　　3.不同的字符集，一個字元佔用的位元組數是不同的。Latin1編碼的，一個字元佔用一個位元組，gdk編碼的，一個字元佔用兩個位元組，utf-8編碼的，一個字元佔用三個位元組。

　　　　（由於我資料庫使用的是Latin1編碼的格式，所以在後面的計算中，一個字元按一個位元組算）

　　　　4.對於所有的索引欄位，如果設置為NULL，則還需要1個位元組。

接下來進入正題！！！

示例：

首先創建一個表

該表中對id列.name列.age列建立了一個聯合索引 id_name_age_index，實際上相當於建立了三個索引（id）（id_name）（id_name_age）。

下面介紹下可能會使用到該索引的幾種情況：

1.全值匹配查詢時

　　通過觀察上面的結果圖可知，where後面的查詢條件，不論是使用（id，age，name）（name，id，age）還是（age，name，id）順序，在查詢時都使用到了聯合索引，可能有同學會疑惑，為什麼底下兩個的搜索條件明明沒有按照聯合索引從左到右進行匹配，卻也使用到了聯合索引？這是因為MySQL中有查詢優化器explain，所以sql語句中欄位的順序不需要和聯合索引定義的欄位順序相同，查詢優化器會判斷糾正這條SQL語句以什麼樣的順序執行效率高，最後才能生成真正的執行計劃，所以不論以何種順序都可使用到聯合索引。另外通過觀察上面三個圖中的key_len欄位，也可說明在搜索時使用的聯合索引中的（id_name_age）索引，因為id為int型，允許null，所以佔5個位元組，name為char(10)，允許null，又使用的是latin1編碼，所以佔11個位元組，age為int型允許null，所以也佔用5個位元組，所以該索引長度為21（5+11+5），而上面key_len的值也正好為21，可證明使用的（id_name_age）索引。

2.匹配最左邊的列時

　　該搜索是遵循最左匹配原則的，通過key欄位也可知，在搜索過程中使用到了聯合索引，且使用的是聯合索引中的（id）索引，因為key_len欄位值為5，而id索引的長度正好為5（因為id為int型，允許null，所以佔5個位元組）。

　　由於id到name是從左邊依次往右邊匹配，這兩個欄位中的值都是有序的，所以也遵循最左匹配原則，通過key欄位可知，在搜索過程中也使用到了聯合索引，但使用的是聯合索引中的（id_name）索引，因為key_len欄位值為16，而(id_name)索引的長度正好為16（因為id為int型，允許null，所以佔5個位元組，name為char(10)，允許null，又使用的是latin1編碼，所以佔11個位元組）。

　　由於上面三個搜索都是從最左邊id依次向右開始匹配的，所以都用到了id_name_age_index聯合索引。

　　那如果不是依次匹配呢？

　　通過key欄位可知，在搜索過程中也使用到了聯合索引，但使用的是聯合索引中的（id）索引，從key_len欄位也可知。因為聯合索引樹是按照id欄位創建的，但age相對於id來說是無序的，只有id只有序的，所以他只能使用聯合索引中的id索引。

　　通過觀察發現上面key欄位發現在搜索中也使用了id_name_age_index索引，可能許多同學就會疑惑它並沒有遵守最左匹配原則，按道理會索引失效，為什麼也使用到了聯合索引？因為沒有從id開始匹配，且name單獨來說是無序的，所以它確實不遵循最左匹配原則，然而從type欄位可知，它雖然使用了聯合索引，但是它是對整個索引樹進行了掃描，正好匹配到該索引，與最左匹配原則無關，一般只要是某聯合索引的一部分，但又不遵循最左匹配原則時，都可能會採用index類型的方式掃描，但它的效率遠不如最做匹配原則的查詢效率高，index類型類型的掃描方式是從索引第一個欄位一個一個的查找，直到找到符合的某個索引，與all不同的是，index是對所有索引樹進行掃描，而all是對整個磁碟的數據進行全表掃描。

　　這兩個結果跟上面的是同樣的道理，由於它們都沒有從最左邊開始匹配，所以沒有用到聯合索引，使用的都是index全索引掃描。

3.匹配列前綴

　　如果id是字元型，那麼前綴匹配用的是索引，中墜和後綴用的是全表掃描。

select * from staffs where id like 'A%';//前綴都是排好序的，使用的都是聯合索引
select * from staffs where id like '%A%';//全表查詢
select * from staffs where id like '%A';//全表查詢