索引最左匹配原則

LonZyuan 發表於 2021-12-04

索引最左字首匹配原則

介紹:在建立聯合索引時,都遵循從左往右的優先順序,最左優先,當出現範圍查詢(> < between like等等)時停止匹配。

首先需要了解索引常用的資料結構,B+樹,網上資料眾多,不再贅述

參考連結:https://blog.csdn.net/jiang_wang01/article/details/113739230

表準備

現在建一個很簡單的表:

索引最左匹配原則

然後加入索引(基數都為5):

索引最左匹配原則

加入資料:

索引最左匹配原則

1-5都是按照數字順序, 而6-10加入了重複資料

SQL語句執行

1.無範圍查詢

SELECT id,`name`,b_id,order_no,`age` 
FROM `t_test_suo`
WHERE b_id = 1 AND order_no = 1 AND `age` = 1

索引最左匹配原則

索引三個欄位都走了

2.有範圍查詢

範圍查詢在中間

SELECT id,`name`,b_id,order_no,`age` 
FROM `t_test_suo`
WHERE b_id = 1 AND order_no <3 AND `age` = 1

索引最左匹配原則

這是最左字首匹配原則就出現了,age也就是第三個欄位沒有走上索引,並且索引型別也變為了range(範圍索引)

範圍查詢在第一位

SELECT id,`name`,b_id,order_no,`age` 
FROM `t_test_suo`
WHERE b_id < 4 AND order_no =3 AND `age` = 1

索引最左匹配原則

結果依舊是依據最左字首匹配原則

為什麼會有最左字首匹配原則

大概描述一下在B+樹結構下,最下面連結串列的順序:

  (1,1,1), (2,2,2), (3,3,3) --> (4,4,4), (5,5,5), (6,6,6) --> (7,6,6), (8,7,7), (9,6,6) --> (9,8,8)

括號裡面分別就是(b_id, order_no, age)

 

可以發現,b_id作為第一位,順序始終都是正確的,而order_no作為第二位,在(8,7,7), (9,6,6)出現了順序錯誤,因為b_id優先排列,所以order_no出錯。

聰明的小夥伴其實應該聯想到了order by的實現

索引最左匹配原則

order by中最前優先排列,如果相同,再看第二位,如果第二位相同,再看第三位。

 

所以可以思考得出:

一:範圍查詢在第一位

WHERE b_id < 4 AND order_no =3 AND age = 1,b_id就已經把第一個範圍給找到了,不再需要order_no再走索引去找對應值了,而是在已經確定的範圍中查詢即可。

二:範圍查詢在中間

WHERE b_id = 1 AND order_no <3 AND age = 1,b_id走索引找到了=1的資料,然後order_no走索引查到了範圍,那麼age就在此範圍中查詢值即可。

 

歡迎各路大佬指正