[[386048]]

MySQL 對(duì)于主鍵索引的維護(hù)是最簡(jiǎn)單的，就是根據(jù)主鍵去維護(hù)一個(gè) B+ 樹(shù)，因?yàn)橹麈I的特點(diǎn)一般是遞增的，也就是說(shuō)是有序的，所以 MySQL 在維護(hù)的時(shí)候只需要將記錄依次往數(shù)據(jù)頁(yè)中追加即可，數(shù)據(jù)頁(yè)滿了就繼續(xù)添加到下一個(gè)數(shù)據(jù)頁(yè)。且每一條記錄是完整的，即所有的列的值都維護(hù)。

但是對(duì)于非主鍵索引，在維護(hù) B+ 樹(shù)的時(shí)候，會(huì)根據(jù)聯(lián)合索引的字段依次去判斷。

假設(shè)聯(lián)合索引為：name + address + age，那么 MySQL 在維護(hù)該索引的 B+ 樹(shù)的時(shí)候，首先會(huì)根據(jù) name 進(jìn)行排序，name 相同的話會(huì)根據(jù)第二個(gè) address 排序，如果 address 也一樣，那么就會(huì)根據(jù) age 去排序，如果 age 也一樣，那么就會(huì)根據(jù)主鍵字段值去排序（主鍵不可能是一樣的），且對(duì)于非主鍵索引，MySQL 在維護(hù) B+ 樹(shù)的時(shí)候，僅僅是維護(hù)索引字段和主鍵字段。

另外 B+ 樹(shù)的結(jié)構(gòu)大致是這樣子的：

這里數(shù)據(jù)的維護(hù)過(guò)程就不再詳細(xì)贅述了，不清楚的朋友可以看上一篇文章

今天，我們就來(lái)一起看看對(duì)于 MySQL 的查詢有哪些基本的原則。

因?yàn)橹灰诹私庠瓌t的基礎(chǔ)之上，才能夠?qū)懗龇项A(yù)期的 SQL，才能知道自己的 SQL 到底有沒(méi)有使用到索引。這是一個(gè)最最基本的原則。

本文因?yàn)橹v的是一些原則，所以很多東西不是很好畫(huà)圖，但是能畫(huà)我一定給大家畫(huà)。

等值匹配原則

我們現(xiàn)在已經(jīng)知道了如果是【主鍵索引】，在插入數(shù)據(jù)的時(shí)候是根據(jù)主鍵的順序依次往后排列的，一個(gè)數(shù)據(jù)頁(yè)不夠就會(huì)分裂到另外一個(gè)數(shù)據(jù)頁(yè)，然后再通過(guò)索引頁(yè)來(lái)維護(hù)數(shù)據(jù)頁(yè)。

數(shù)據(jù)頁(yè)之間是通過(guò)雙向鏈表來(lái)維護(hù)的，索引頁(yè)如果過(guò)多就會(huì)往上分裂（就像上面這張圖），以此類(lèi)推，這樣就形成了由組件組成的 B+ 樹(shù)結(jié)構(gòu)，即【聚簇索引】

但是問(wèn)題是我們不僅建立了主鍵索引，同時(shí)也建立了非主鍵索引，那這時(shí)候非主鍵索引是如何維護(hù)的呢？

因?yàn)閷?duì)于主鍵索引是不可能重復(fù)的，所以在保存到數(shù)據(jù)頁(yè)的時(shí)候是直接追加插入的（我們默認(rèn)主鍵是自增的）

至于非主鍵一般是可以重復(fù)的，假設(shè)此時(shí)某個(gè)聯(lián)合索引字段的值真的都是一樣的，那該怎么辦？

那就像上面開(kāi)頭說(shuō)的，此時(shí)只能按照主鍵字段來(lái)排序，這就是為什么非主鍵索記錄在保存的時(shí)候還保存一個(gè)主鍵字段的作用。

另外剛剛上面也說(shuō)了，索引如果建立太多會(huì)占用太多的空間，因?yàn)镸ySQL 會(huì)為每個(gè)索引維護(hù)一顆 B+ 樹(shù)，畢竟非主鍵的字段一方面不一定是遞增的，另一方面可能是重復(fù)的。所以就基于這點(diǎn)，那些頻繁增刪的字段一定不適合來(lái)做索引。

好了，我們還是要回到剛剛說(shuō)的 name+age 的聯(lián)合索引假設(shè)我們現(xiàn)在有一條這樣的 SQL 

SELECT * FROM student WHERE name='wx' AND age=1 

像這種 WHERE 后面的條件是聯(lián)合索引的并且是聯(lián)合索引中的字段的順序排列的，且全是使用等于號(hào)條件，我們稱這種為：等值匹配；這個(gè)是非常重要的一個(gè)原則。

最左前綴匹配原則

假設(shè)現(xiàn)在有這樣的幾條記錄： 

classId=1,name=wx,age=1,id=1;   
classId=1,name=xq,age=2,id=2;   
classId=1,name=wx,age=1,id=3;   
classId=2,name=zs,age=3,id=4; 

根據(jù)上面說(shuō)的（classId,name,age）聯(lián)合索引他們是這樣子保存在數(shù)據(jù)頁(yè)中的。

首先根據(jù) classId 字段值排序。

如果 classId 字段值一樣，那么就根據(jù)第二個(gè) name 字段值排序。

如果name 字段值也一樣，那么就根據(jù) age 字段值一樣，如果 age 字段值也一樣，那么就根據(jù)主鍵字段值排序。

然后在查找的時(shí)候，因?yàn)槟悻F(xiàn)在條件是 calssId 和 name，所以 MySQL 是能夠通過(guò) classId 很快定位到一批數(shù)據(jù)的。

因?yàn)檫@個(gè)條件就是 MySQL 維護(hù) B+ 樹(shù)的第一條件（即先根據(jù) classId 排序），然后同理，name 是MySQL 維護(hù)B+樹(shù)的第二個(gè)條件（即根據(jù) name 排序），所以此時(shí)哪怕你 age 條件不添加，使用到索引classId 和 name 的索引一定是沒(méi)問(wèn)題的，但是如果你這么查詢 

SELECT * FROM student WHERE  age=1 

這樣子就不行了，因?yàn)?MySQL 會(huì)根據(jù)你建立的聯(lián)合索引。

首先是根據(jù) classId 查詢，然后是根據(jù) name，然后再根據(jù) age。

如果你直接跳過(guò)前面的兩個(gè)字段，那么這樣子跟全表掃描是沒(méi)有區(qū)別的，因?yàn)镸ySQL 此時(shí)根本就無(wú)法確認(rèn) age 在哪里，只能一個(gè)一個(gè)去掃描了。

同理，如果你WHERE 條件后面是 classId=xx，然后是 age=xx 此時(shí)這種情況下 classId 是可以使用到索引的，因?yàn)?B+ 樹(shù)維護(hù)的第一個(gè)字段就是 classId。

但是 age 卻無(wú)法使用到索引查詢了，因?yàn)?name 是無(wú)法定位的，所以此時(shí)只能是根據(jù)滿足 classId 的記錄再做一次全掃描。這規(guī)則叫：最左前綴匹配原則；

如果你想不明白最左匹配原則，那我來(lái)做個(gè)類(lèi)比再來(lái)介紹下，我們假設(shè)classId,name,age，這三個(gè)組成的聯(lián)合索引就好比是三層樓，classId是第一層,name是第二層，age是第三層。

假設(shè)你想要到第三層，是不是必須要要從第一層開(kāi)始爬，然后是第二層，然后是第三層；你可以就爬到第一層，剩下兩層不爬也沒(méi)關(guān)系，這就對(duì)應(yīng)你可以就使用 classId來(lái)做等值查詢，剩下的字段不使用都沒(méi)關(guān)系；

同理，你可以從第一層爬，然后再爬到第二層，不爬第三層，這就好比是你使用 classId,name去查詢一樣，亦或者你依次從第一層爬到第二層再爬到第三層都是可以的，也就是你使用classId,name,age這三個(gè)字段依次去做等值查詢。到此這一切都是 OK 的。

但是如果你不想爬第一層，你想跳過(guò)第一層，直接從第二層開(kāi)始爬，可能嗎？

顯然是不可能的，這也就是說(shuō)查詢的時(shí)候跳過(guò) classId 直接查詢name，這樣子就根本無(wú)法使用到索引。調(diào)過(guò) name 查詢age 也是同理，直接跳過(guò)一二層直接從第三層開(kāi)始，也就是說(shuō)調(diào)過(guò)classId 和name直接查詢age也是無(wú)法使用到索引的.

這下你應(yīng)該徹底明白最左匹配的原則了吧？以下的原則最基礎(chǔ)的條件就是需要滿足：最左前綴匹配原則。

范圍查找規(guī)則

范圍查找規(guī)則，相信這個(gè)也是大家最經(jīng)常使用的原則了，例如像下面的SQL 

SELECT * FROM student WHERE classId > 1 AND classId < 4 

因?yàn)榇藭r(shí)由聯(lián)合索引（classId,name,age）構(gòu)建出來(lái)的 B+ 樹(shù)中的數(shù)據(jù)是根據(jù) classId,name,age 去排序的。

所以此時(shí)是能夠根據(jù) classId 查詢到一個(gè)范圍中的數(shù)據(jù)的，雖然他們可能不在同一個(gè)數(shù)據(jù)頁(yè)中，但是我們說(shuō)過(guò)了，數(shù)據(jù)頁(yè)之間是通過(guò)雙向鏈表進(jìn)行連接的。所以 此時(shí)針對(duì) classId 的范圍查找依舊是能走索引的。繼續(xù)看如果條件是這樣子的 

SELECT * FROM student WHERE classId > 1 AND classId < 4 and name > a AND name < x 

你是不是覺(jué)得前面的 classId 是符合范圍查找的，然后在查詢出來(lái)的結(jié)果中繼續(xù)范圍查找 name。

但實(shí)際上并不是這樣子的，因?yàn)槲覀冋f(shuō)了聯(lián)合索引（classId,name,age）是按照 calssId、name、age 依次去排序的，因?yàn)榇藭r(shí) classId 的順序確定以后，是不需要根據(jù) name 排序的，也就是說(shuō)在 classId 的范圍內(nèi) name 是無(wú)序的，聽(tīng)不明白？沒(méi)關(guān)系，看我畫(huà)圖

現(xiàn)在我們依次插入的數(shù)據(jù)是上面的四條，因?yàn)樵诓迦氲臅r(shí)候是可以直接根據(jù) classId 就能夠確定下這四條記錄的順序了。

所以此時(shí)是根本不會(huì)去管后面的 name 或者是 age 是什么順序的，或許你可能覺(jué)得如果他們的 class Id 一樣呢？

好，我就來(lái)一點(diǎn)一點(diǎn)排除你心中的疑惑，看下面這張圖

我們假設(shè)第三條記錄的 calssId 和第二條記錄的 classId 字段值是一樣的，那這個(gè)時(shí)候才會(huì)去根據(jù) name 判斷。

結(jié)果發(fā)現(xiàn) aa 是小于 zz 的，這樣就會(huì)把 name 值更小的排在前面，但是為什么我上面還會(huì)說(shuō)按照 calssId 排序好了以后 name 是無(wú)序的呢？

因?yàn)槲覀冋f(shuō)的第二種情況（classId 相同）是屬于特殊情況，我們不能使用特殊的情況來(lái)下一般性的結(jié)論，age 同理。

所以記住了：針對(duì)于范圍查找只要聯(lián)合索引的最左側(cè)列有效，其他的都無(wú)法使用到索引（既然無(wú)法使用到索引，那么只能是走全表掃描）

等值匹配+范圍查找

假設(shè)我們有這樣的一條 SQL

SELECT * FROM student WHERE classId = 1 AND name > a AND name < x 

首先 calssId 是走索引的，其次 name 也是走索引的。

為什么？你怎么前后說(shuō)的有矛盾？剛剛才說(shuō)了范圍的之后第一個(gè)列才能走索引，現(xiàn)在卻說(shuō) name 也走索引，name 明明是第二列。

[[386049]]

聽(tīng)我慢慢道來(lái)，首先范圍查找只有第一個(gè)列走索引單純針對(duì)的范圍查找，具體原因我已經(jīng)詳細(xì)的解釋了，但是現(xiàn)在如果使用聯(lián)合索引中的第一個(gè)條件去做等值匹配，第二個(gè)去使用范圍查詢走索引是沒(méi)問(wèn)題的，看下面的圖

現(xiàn)在我們首先定位的是 classId=1 這些記錄，這些一定是確定的，但是在 MySQL維護(hù) B+ 樹(shù)的時(shí)候，是沒(méi)法根據(jù) classId=1 的記錄來(lái)直接進(jìn)行排序的。

因?yàn)榇藭r(shí)的 classId 都是1，換句話說(shuō)，三條記錄的 classId 都是1，MySQL 根本無(wú)法確定誰(shuí)在前面，誰(shuí)在后面。

所以此時(shí)就需要根據(jù) name 去繼續(xù)判斷，結(jié)果也就是上面圖的樣子。

根據(jù) name 發(fā)現(xiàn)是能夠確定記錄順序的，所以在 classId 等于 1 的記錄中的所有的 name 都是有序的。

這就是為什么等值后面可以范圍的原因（但是一條貫穿始終的原則是：必須是滿足最左匹配原則，也就是前面的記錄必須是確定的，這樣子才能繼續(xù)對(duì)后面的數(shù)據(jù)判斷）。此時(shí)此刻你是不是想大聲的喊一句

[[386050]]

到此為止，你現(xiàn)在是否能夠根據(jù)建立的索引來(lái)判斷你的 SQL 是否使用到了索引，使用到了哪些索引了呢。

是不是想趕緊的寫(xiě)幾個(gè)SQL 試試？

別急，一定要看完總結(jié)部分。

Order By + limit 優(yōu)化

上面說(shuō)道的一些都是最最基本的查詢的一些原則，但是想要實(shí)際運(yùn)用，這里是必須要學(xué)習(xí)的，因?yàn)槲覀兤綍r(shí)寫(xiě)sql的時(shí)候絕對(duì)離不開(kāi)分頁(yè)。

而分頁(yè)基本是也排序組合使用的，所以我們也將這個(gè)放在一起在說(shuō)。

假設(shè)現(xiàn)在對(duì)name，age，adderss 這三個(gè)字段創(chuàng)建聯(lián)合索引，且在查詢的時(shí)候 SQL 語(yǔ)句是這樣子的:

SELECT name,age,address FROM student ORDER BY  name,age,address LIMIT 10 

你如果這么寫(xiě)那 MySQL 就能明白了啊，你是想根據(jù) name,age,address聯(lián)合索引進(jìn)行排序，然后在取前10條記錄，且取的記錄的字段在維護(hù)聯(lián)合索引的 B+ 樹(shù)中都是有的，那么此時(shí)就需要再去進(jìn)行回表到聚簇索引中查詢了。

另外 ORDER BY后面的字段的方式一定要一致，也就是說(shuō)要么全是升序，要么全是降序，不能有的升序有的降序。

說(shuō)白了就是一般對(duì)什么字段排序就對(duì)哪些字段建立索引，但是升序降序不要混用。

其實(shí)對(duì)于 MySQL 的優(yōu)化看到這里相信大家或多或少也發(fā)現(xiàn)了，優(yōu)化真的沒(méi)有所以的規(guī)律和套路，因?yàn)樽詈玫膬?yōu)化是結(jié)合實(shí)際的業(yè)務(wù)區(qū)做調(diào)整。沒(méi)有一蹴而就的方式和一勞永逸的方法。

分組查詢優(yōu)化

其實(shí)分組查詢優(yōu)化和上面的Order By + limit 優(yōu)化差不多，基本是一個(gè)道理，例如有這樣的 SQL 

SELECT count(*) FROM student GROUP BY NAME 

如果不對(duì) NAME 建立索引，那么就是將所有的數(shù)據(jù)查詢出來(lái)，放在一個(gè)臨時(shí)文件中，然后按照分組的字段將數(shù)據(jù)一組一組的分好。

然后再去執(zhí)行聚合操作（這里就是count(*)操作），這樣子很顯然效率是很低的，所以我們肯定是需要對(duì) NAME 去建立索引的。

這是不是不明白為什么需要會(huì)有臨時(shí)文件？

根據(jù) group by 的語(yǔ)義邏輯，是按照name去做統(tǒng)計(jì)，因?yàn)榇藭r(shí)name并沒(méi)有索引，所以按照name去分組首先需要得到一個(gè)根據(jù)name排序的數(shù)據(jù)啊，所以我們就需要有一個(gè)臨時(shí)表，來(lái)記錄并統(tǒng)計(jì)結(jié)果。

也就是說(shuō)我們需要的不就是一個(gè)排好序的結(jié)果嗎？那直接對(duì)name建立索引就可以了。

假設(shè)我們是根據(jù)name建立好了索引，因?yàn)榇藭r(shí)name已經(jīng)是被排好序的了，這個(gè)時(shí)候就可以拿到 group by 的結(jié)果，不需要臨時(shí)表，也不需要再額外排序。

也就是說(shuō)，如果語(yǔ)句執(zhí)行過(guò)程可以一邊讀數(shù)據(jù)，一邊直接得到結(jié)果，是不需要額外內(nèi)存的，否則就需要額外的內(nèi)存，來(lái)保存中間結(jié)果。

結(jié)束語(yǔ)

索引查詢基本原則總結(jié)

我們一般寫(xiě) SQL 基本都是【等值 + 范圍】的，這個(gè)是最常見(jiàn)的條件搜索，像這樣子的情況一定要建立好索引，建立索引的根本依據(jù)就是要明白【MySQL 是如何幫我們維護(hù)非主鍵索引的】。

MySQL 是如何幫我們維護(hù)非主鍵索引的

其實(shí)在開(kāi)頭我已經(jīng)強(qiáng)調(diào)過(guò)了，但是為了讓大家在鞏固下，我這里在來(lái)說(shuō)一遍。

對(duì)于主鍵索引，MySQL 就是根據(jù)主鍵字段進(jìn)行排序（一般主鍵字段我們都設(shè)置為自增的，否則真的是在給自己找麻煩，假設(shè)主鍵不是自增的，這還會(huì)導(dǎo)致頁(yè)分裂的發(fā)生，這樣就很降低性能了）；

而對(duì)于非主鍵索引（我們一般指聯(lián)合索引）MySQL 同樣會(huì)為我們維護(hù)一個(gè)B+ 樹(shù)，只不過(guò)這顆B+的葉子結(jié)點(diǎn)（即數(shù)據(jù)頁(yè)）上面的保存的數(shù)據(jù)僅僅是索引字段數(shù)據(jù)和主鍵數(shù)據(jù)。

假設(shè)有聯(lián)合索引 name、address、age，這樣在插入數(shù)據(jù)的時(shí)候，MySQL 首先會(huì)根據(jù)name進(jìn)行排序，name一樣就根據(jù)address 排序，address 字段值一樣再根據(jù) age 字段值排序。

age 字段值還一樣，就根據(jù)主鍵字段排序。

這也是為什么會(huì)維護(hù)主鍵字段的原因。

另外為什么對(duì)于非主鍵字段只維護(hù)索引列？因?yàn)榫鄞厮饕ㄍㄟ^(guò)維護(hù)主鍵字段的B+樹(shù)）中已經(jīng)有全部記錄的值，如果其他的索引再維護(hù)所有的字段，這樣就是在浪費(fèi)空間。