聯(lián)合索引查詢原理及生效規(guī)則

一般都是設(shè)計聯(lián)合索引，很少用單個字段做索引，因為還是要盡可能讓索引數(shù)量少，避免磁盤占用太多，影響增刪改性能。

有個表存儲學生成績，id是自增主鍵，包含學生班級、學生姓名、科目名稱、成績分數(shù)四個字段，平時查詢，可能比較多的就是查找某個班的某個學生的某個科目的成績。

所以，我們可以針對【學生班級，學生姓名，科目名稱】建立一個聯(lián)合索引。

有兩個數(shù)據(jù)頁：

第一個數(shù)據(jù)頁里有三條數(shù)據(jù)，每條數(shù)據(jù)都包含聯(lián)合索引的三個字段的值和主鍵值，數(shù)據(jù)頁內(nèi)部按序排：先按班級排序，若一樣則按姓名排序，若再一樣，則按科目名排序。所以數(shù)據(jù)頁內(nèi)部都是按照三個字段值排序，組成單鏈表。

數(shù)據(jù)頁之間有序。第二個數(shù)據(jù)頁里的三個字段的值一定都＞上一個數(shù)據(jù)頁里三個字段的值，比較方法也是按班級名稱、學生姓名、科目名依次比較，數(shù)據(jù)頁間組成雙向鏈表

索引頁里就是兩條數(shù)據(jù)，分別指向兩個數(shù)據(jù)頁，索引存放的是每個數(shù)據(jù)頁里最小的那個數(shù)據(jù)的值，大家看到，索引頁里指向兩個數(shù)據(jù)頁的索引項里都是存放了那個數(shù)據(jù)頁里最小的值！

索引頁內(nèi)部的數(shù)據(jù)頁是組成單向鏈表有序的，如你有多個索引頁，索引頁之間也有序，組成雙向鏈表。

假設(shè)搜索：1班+張小強+數(shù)學的成績，你可能寫

select * from student_score where class_name='1班' and student_name='張小強' and subject_name='數(shù)學'

涉及索引使用規(guī)則，where條件里的幾個字段都是等值查詢且where條件里的幾個字段名稱和順序也跟你的聯(lián)合索引一樣！此時就是等值匹配規(guī)則，上面的SQL百分百可以用聯(lián)合索引查詢。

查詢過程

首先到索引頁找，索引頁里有多個數(shù)據(jù)頁的最小值記錄，在索引頁二分查找，先根據(jù)【班級名稱】找6班對應(yīng)數(shù)據(jù)頁，定位到其所在數(shù)據(jù)頁

在數(shù)據(jù)頁內(nèi)部本身也是單向鏈表，直接二分查找，先找6班，發(fā)現(xiàn)幾條數(shù)據(jù)都是6班，此時就按張三姓名來二分查找，此時會發(fā)現(xiàn)多條數(shù)據(jù)都是張小強，接著就按科目名稱數(shù)學二分查找。

定位到下圖中的一條數(shù)據(jù)，6班的張三數(shù)學，其對應(yīng)id=127：

然后就根據(jù)主鍵id=127到聚簇索引里按照一樣的思路，從索引根節(jié)點開始二分查找迅速定位下個層級的頁，再不停找，很快就可以找到id=127的那條數(shù)據(jù)，然后從里面提取所有字段，包括分數(shù)，就可以了。

總結(jié)

如上就是聯(lián)合索引的查找過程以及全值匹配規(guī)則，假設(shè)你的SQL語句的where條件里用的幾個字段的名稱和順序，都跟你的索引里的字段一樣，同時你還是用等號在做等值匹配，那么直接就會按照上述過程來找。

聯(lián)合索引就是依次按照各個字段來進行二分查找，先定位到第一個字段對應(yīng)的值在哪個頁里，然后如果第一個字段有多條數(shù)據(jù)值都一樣，就根據(jù)第二個字段來找，以此類推，一定可以定位到某條或者某幾條數(shù)據(jù)。

索引使用規(guī)則

有了聯(lián)合索引后，SQL怎么寫才能讓他的查詢使用索引？

等值匹配規(guī)則

where語句中的幾個字段名稱和聯(lián)合索引的字段完全一樣，而且都是基于等號的等值匹配，那百分百會用上我們的索引。即使你where語句里寫的字段的順序和聯(lián)合索引里的字段順序不一致，也沒關(guān)系，MySQL會自動優(yōu)化為按聯(lián)合索引的字段順序去找。

最左側(cè)列匹配

假設(shè)我們聯(lián)合索引是KEY(class_name, student_name, subject_name)，那不一定必須要在where語句里根據(jù)三個字段來查，其實只要根據(jù)最左側(cè)的部分字段來查，也可以。

比如你可以寫

select * from student_score where class_name='' and student_name=''

但是假設(shè)你寫一個

select * from student_score where subject_name=''

就不行了，因為聯(lián)合索引的B+樹里，必先按class_name查，再按student_name查，不能跳過前面兩個字段，直接按最后一個subject_name查。

若如下SQL：

select * from student_score where class_name='' and subject_name=''

那么只有class_name的值可以在索引里搜索，剩下的subject_name是沒法在索引里找的，道理同上。

所以在建立索引的過程中，你必須考慮好聯(lián)合索引字段的順序，以及你平時寫SQL的時候要按哪幾個字段來查。

最左前綴匹配原則

如果你要用like語法來查，比如

select * from student_score where class_name like '1%'

查找所有1打頭的班級的分數(shù)，那么也是可以用到索引的。

因為你的聯(lián)合索引的B+樹里，都是按照class_name排序的，所以你要是給出class_name的確定的最左前綴就是1，然后后面的給一個模糊匹配符號，那也是可以基于索引來查找的，這是沒問題的。

但是你如果寫class_name like ‘%班’，在左側(cè)用一個模糊匹配符，那他就沒法用索引了，因為不知道你最左前綴是什么，怎么去索引里找啊？

范圍查找規(guī)則

這個意思就是說，我們可以用select * from student_score where class_name>‘1 班’ and class_name<'5班’這樣的語句來范圍查找某幾個班級的分數(shù)。

這個時候也是會用到索引的，因為我們的索引的最下層的數(shù)據(jù)頁都是按順序組成雙向鏈表的，所以完全可以先找到’1 班’對應(yīng)的數(shù)據(jù)頁，再找到’5班’對應(yīng)的數(shù)據(jù)頁，兩個數(shù)據(jù)頁中間的那些數(shù)據(jù)頁，就全都是在你范圍內(nèi)的數(shù)據(jù)了！

但 是 如 果 你 要 是 寫 select * from student_score where class_name>‘1 班 ’ and class_name<‘5 班 ’ and student_name>’’，這里只有class_name是可以基于索引來找的，student_name的范圍查詢是沒法用到索引的！

這也是一條規(guī)則，就是你的where語句里如果有范圍查詢，那只有對聯(lián)合索引里最左側(cè)的列進行范圍查詢才能用到索引！

等值匹配+范圍匹配的規(guī)則

如果你要是用

select * from student_score where class_name='1班' and student_name>'' and subject_name<''

那么此時你首先可以用class_name在索引里精準定位到一波數(shù)據(jù)，接著這波數(shù)據(jù)里的student_name都是按照順序排列的，所以student_name>’‘也會基于索引來查找，但是接下來的subject_name<’'是不能用索引的。

綜上，一般寫SQL都是：

用聯(lián)合索引的最左側(cè)的多個字段來進行等值匹配+范圍搜索

或基于最左側(cè)的部分字段來進行最左前綴模糊匹配

或基于最左側(cè)字段來進行范圍搜索

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應(yīng)法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。