引言

MySQL的索引機制盔性，自始至終對于我們都是一個黑盒般的存在缓醋，我們并不清楚建立索引后MySQL會發(fā)生什么，也并不清楚使用索引查詢時會如何檢索......。對于索引咱們也留下了很多很多的疑惑：

相信大家多多少少都有了解過交洗，畢竟這也是面試過程中出現(xiàn)次數(shù)較為頻繁的一個技術(shù)點。在本文中就來一窺MySQL索引底層的神秘面紗橡淑！

一构拳、MySQL索引為何使用B+樹結(jié)構(gòu)？

MySQL的索引機制中梁棠，有一點可謂是路人皆知置森，既默認使用B+Tree作為底層的數(shù)據(jù)結(jié)構(gòu)，但為什么要選擇B+樹呢符糊？有人會說樹結(jié)構(gòu)是以二分法查找數(shù)據(jù)凫海，所以會在很大程度上提升檢索性能，這點確實沒錯濒蒋，但樹結(jié)構(gòu)有那么多盐碱，MySQL為什么不選二叉樹、AVL樹沪伙、紅黑樹或B樹呢瓮顽？下面一起先聊一聊這個話題。

對于索引為什么不支持數(shù)組围橡、鏈表暖混、隊列等結(jié)構(gòu)就不做過多解釋了，因為這些結(jié)構(gòu)中的元素都是按序并排存儲翁授，如果選擇這些結(jié)構(gòu)來實現(xiàn)索引拣播，那走索引依舊等價于走全表，并未帶來查詢時的效率提升收擦，反而帶來了額外的存儲開銷贮配，這是沒有意義的。

1.1塞赂、普通SQL的全表掃描過程

想要真正理解MySQL為何選B+樹結(jié)構(gòu)之前泪勒，你必須要先了解一條普通SQL的全表掃描過程，否則你很難真正切身感受出有索引和沒索引的區(qū)別宴猾。當(dāng)然圆存，這里就不再以偽邏輯的形式講解全表掃描了，而是真正的為大家講解MySQL全表掃描的實際過程仇哆。以下面這張用戶表為例：

首先假設(shè)表中不存在任何索引沦辙，此時來執(zhí)行下述這條SQL：

因為表中不具備索引，所以這里會走全表掃描的形式檢索數(shù)據(jù)讹剔，但不管是走全表亦或索引油讯，本質(zhì)上由于數(shù)據(jù)都存儲在磁盤中详民，因此首先都會觸發(fā)磁盤IO，所以先來說說磁盤尋道的過程：

如果磁盤不是SSD類型撞羽，大致長上面這個樣子阐斜，里面有一個個的盤面和磁針，當(dāng)發(fā)生磁盤IO時诀紊，首先會根據(jù)給出的磁盤地址谒出，在盤面上尋道。這個尋道過程是怎么回事呢邻奠？就跟小時候VCD笤喳、DVD放光碟類似，盤面會開始轉(zhuǎn)圈圈碌宴，在盤面上有一個磁道的概念杀狡，當(dāng)轉(zhuǎn)到了對應(yīng)的地址時，磁道和磁針會相互吸引贰镣，然后以一上一下的方式讀取0呜象、1二進制數(shù)據(jù)，最終從磁盤中將目標(biāo)地址中的數(shù)據(jù)讀取出來碑隆。

磁盤尋道的大致過程如上恭陡，具體的細節(jié)沒寫出來，重點是大家要感受這個過程即可上煤。

當(dāng)走全表掃描時休玩，會發(fā)生磁盤IO，但是磁盤尋道是需要有一個地址的劫狠，這個地址最開始就是本地表數(shù)據(jù)文件中的起始地址拴疤，也就是從表中的第一行數(shù)據(jù)開始讀，讀到數(shù)據(jù)后會載入內(nèi)存独泞，然后MySQL-Server會根據(jù)SQL條件對讀到的數(shù)據(jù)做判斷呐矾，如果不符合條件則繼續(xù)發(fā)生磁盤IO讀取其他數(shù)據(jù)（如果表比較大，這里不會以順序IO的形式走全表檢索懦砂，而是會觸發(fā)隨機的磁盤IO）凫佛。

那來看一下，上面給出的用戶表中孕惜，「黑熊」這條數(shù)據(jù)位于表的第五行，那這里會發(fā)生五次磁盤IO嗎晨炕？答案是NO衫画，為什么呢？因為OS瓮栗、MySQL中都有一個優(yōu)化措施削罩，叫做局部性讀取原理瞄勾。

1.1.1、局部性原理

局部性原理的思想比較簡單弥激，比如目前有三塊內(nèi)存頁x进陡、y、z是相連的微服，CPU此刻在操作x頁中的數(shù)據(jù)趾疚，那按照計算機的特性，一般同一個數(shù)據(jù)都會放入到物理相連的內(nèi)存地址上存儲以蕴，也就是當(dāng)前在操作x頁的數(shù)據(jù)糙麦，那么對于y，z這兩頁內(nèi)存的數(shù)據(jù)也很有可能在接下來的時間內(nèi)被操作丛肮，因此對于y赡磅，z這兩頁數(shù)據(jù)則會提前將其載入到高速緩沖區(qū)（L1/L2/L3），這個過程叫做利用局部性原理“預(yù)讀”數(shù)據(jù)宝与。

但是一次性到底預(yù)讀多大的數(shù)據(jù)放入到高速緩沖區(qū)中呢焚廊？

這個是由緩存行大小決定的，比如因特爾的MESI協(xié)議中习劫，緩存行的默認大小為64k咆瘟，也就是說在因特爾的CPU中，一次性會將“當(dāng)前操作數(shù)據(jù)”附近的64K數(shù)據(jù)（16頁數(shù)據(jù)）提前載入進高速緩沖區(qū)榜聂。

OK~搞疗，上述內(nèi)容講的是操作系統(tǒng)高速緩沖區(qū)的知識，在CPU中利用局部性原理须肆，提前將數(shù)據(jù)從內(nèi)存先放入L1/L2/L3三級緩沖區(qū)中匿乃，主要是為了減小CPU寄存器與內(nèi)存之間的性能差異。

OK~豌汇，由于CPU寄存器和內(nèi)存之間的性能差異太大幢炸，所以逐個讀數(shù)據(jù)的形式會導(dǎo)致CPU工作期間的大量時間會處于等待數(shù)據(jù)狀態(tài)，所以利用局部性原理將數(shù)據(jù)“預(yù)讀”到高速區(qū)拒贱。而對于MySQL而言宛徊，亦是同理，存儲數(shù)據(jù)的磁盤和內(nèi)存之間的性能差異也是巨大的逻澳，因為MySQL也會利用局部性原理闸天，提前“預(yù)讀”數(shù)據(jù)。

這是什么意思呢斜做？其實就是指MySQL一次磁盤IO不僅僅只會讀取一條表數(shù)據(jù)苞氮，而是會讀取多條數(shù)據(jù)，那到底讀多少條數(shù)據(jù)呢瓤逼？在InnoDB引擎中笼吟，一次默認會讀取16KB數(shù)據(jù)到內(nèi)存库物。

1.1.2、全表掃描過程

回到前面分析全表掃描的階段贷帮，由于MySQL中會使用局部性原理的思想戚揭，所以對于給出的用戶表數(shù)據(jù)而言，可能只需發(fā)生一次磁盤IO就能將前五條數(shù)據(jù)全部讀到內(nèi)存撵枢，然后會在內(nèi)存中對本次讀取的數(shù)據(jù)逐條判斷民晒，看一下每條數(shù)據(jù)的姓名字段是否為「黑熊」：

如果發(fā)現(xiàn)不符合SQL條件的行數(shù)據(jù)，則會將當(dāng)前這條數(shù)據(jù)放棄诲侮，同時在本次SQL執(zhí)行過程中會排除掉這條數(shù)據(jù)镀虐，不會對其進行二次讀取。

如果發(fā)現(xiàn)當(dāng)前的數(shù)據(jù)符合SQL條件要求沟绪，則會將當(dāng)前數(shù)據(jù)寫入到結(jié)果集中刮便，然后繼續(xù)判斷其他數(shù)據(jù)。

當(dāng)本次磁盤IO讀取到的所有數(shù)據(jù)全部篩選完成后，緊接著會看一下表中是否還有其他數(shù)據(jù)，如果還有則繼續(xù)觸發(fā)磁盤IO檢索數(shù)據(jù)往堡，如果沒有則將內(nèi)存中的結(jié)果集返回。

有人或許會疑惑搜贤，為什么這里已經(jīng)讀到了符合條件的數(shù)據(jù)，還需要繼續(xù)發(fā)生磁盤IO呢钝凶？因為表中的字段沒有建立唯一索引或唯一約束仪芒，因此MySQL不確定是否還有其他同名的數(shù)據(jù)，所以需要將整個表全部掃描一遍耕陷，才能得到最終結(jié)論掂名。

好的，到這里就將MySQL全表掃描的過程講明白了哟沫，緊著來看看全表掃描有什么問題呢饺蔑？

其實按目前的情況來看，似乎不會有太大的問題嗜诀，因此表中數(shù)據(jù)不多猾警，一次磁盤IO幾乎就能讀完。但思考一下隆敢，如果當(dāng)表的數(shù)據(jù)量變?yōu)榘偃f級別发皿、千萬級別呢？假設(shè)表中一條數(shù)據(jù)大小為512Bit拂蝎，一次磁盤IO也只能讀32條雳窟，假設(shè)表中有320w條數(shù)據(jù)，一次全表就有可能會觸發(fā)10W次磁盤IO，每次都需要在硬件上讓那個盤面轉(zhuǎn)啊轉(zhuǎn)封救，其過程的開銷可想而知.....

因此建立索引的原因就在于此處，為了避免查詢時走全表掃描捣作，因此全表掃描的開銷會隨著數(shù)據(jù)量增長而越來越大誉结。

1.2、索引為何不選擇二叉樹券躁？

數(shù)據(jù)結(jié)構(gòu)與算法惩坑，這門學(xué)科從誕生到現(xiàn)在，自始至終都讓人難以理解也拜，但國外有一個比較厲害的程序員以舒，為了幫助他人更好的理解數(shù)據(jù)結(jié)構(gòu)，自己搭建了一個數(shù)據(jù)結(jié)構(gòu)的動畫演示平臺慢哈，里面提供了非常多豐富的數(shù)據(jù)結(jié)構(gòu)類型蔓钟，我們在其中能以動畫的形式觀測數(shù)據(jù)結(jié)構(gòu)的變化。

回歸話題本身卵贱，全表掃描由于走的是線性查詢滥沫，因此數(shù)據(jù)越多，開銷越大键俱，此時先來看看二叉搜索樹兰绣。

Binary Search Tree二叉搜索樹是遵守二分搜索法實現(xiàn)的一種數(shù)據(jù)結(jié)構(gòu)，咱們先來看看這種數(shù)據(jù)結(jié)構(gòu)為何不適合用來做索引結(jié)構(gòu)呢编振？

上圖是我提前構(gòu)建的二叉樹缀辩，其中存在6個節(jié)點，按咱們前面給出的案例踪央，「黑熊」這條數(shù)據(jù)位于表的第五行臀玄，那假設(shè)以二叉樹作為索引結(jié)構(gòu)，想要定位到第五行數(shù)據(jù)杯瞻，需要經(jīng)過幾次磁盤IO呢镐牺？來看動圖演示效果：

從動畫中可以明顯看到，想要查到第五條數(shù)據(jù)魁莉，需要經(jīng)過五次查詢睬涧，由于樹結(jié)構(gòu)在磁盤中存儲的位置也不連續(xù)，因此無法利用局部性原理讀取后續(xù)的節(jié)點旗唁，所以最終需要發(fā)生五次磁盤IO才能讀取到數(shù)據(jù)畦浓。

二叉樹不適合作為索引結(jié)構(gòu)的原因：

①如果索引的字段值是按順序增長的，二叉樹會轉(zhuǎn)變?yōu)殒湵斫Y(jié)構(gòu)检疫。

②由于結(jié)構(gòu)轉(zhuǎn)變成了鏈表結(jié)構(gòu)讶请，因此檢索的過程和全表掃描無異。

③由于樹結(jié)構(gòu)在磁盤中，各節(jié)點的數(shù)據(jù)并不連續(xù)夺溢，因此無法利用局部性原理论巍。

1.3、索引為何不選擇紅黑樹风响？

上面簡單的分析二叉樹后嘉汰，很明顯的可以看出，二叉樹并不適合作為索引結(jié)構(gòu)状勤，那接下來再看看大名鼎鼎的Red-Black Tree紅黑樹：

同樣提前先構(gòu)建好了六個節(jié)點鞋怀，相較于之前的二叉樹，紅黑樹則進一步做了優(yōu)化持搜，它是一種自適應(yīng)的平衡樹密似，會根據(jù)插入的節(jié)點數(shù)量以及節(jié)點信息，自動調(diào)整樹結(jié)構(gòu)來維持平衡葫盼，從樹的高度上來看残腌，明顯比之前的6層減少到了4層，那此時再來看看檢索的過程：

由于樹變矮了剪返，其效果也很明顯废累，在紅黑樹中只需要經(jīng)過三次查找，就能定位到第五個節(jié)點脱盲，似乎看起來還不錯對嘛邑滨？但MySQL為啥不用這顆名聲遠揚的紅黑樹呢？

紅黑樹不適合作為索引結(jié)構(gòu)的原因：

①雖然對比二叉樹來說钱反，樹高有所降低掖看，但數(shù)據(jù)量一大時，依舊會有很大的高度面哥。

②每個節(jié)點中只存儲一個數(shù)據(jù)哎壳，節(jié)點之間還是不連續(xù)的，依舊無法利用局部性原理尚卫。

對于上述兩個缺點羅列得很明白归榕，其本質(zhì)上的原因就在于：單個節(jié)點中只能存儲一個數(shù)據(jù)，因此一方面樹會隨著數(shù)據(jù)量增長越來越高吱涉，第二方面也無法利用局部性原理減少磁盤IO刹泄。

那是不是把紅黑樹稍微改造一下，讓其單個節(jié)點中可存儲多個數(shù)據(jù)是不是可以了呢怎爵？B樹就是這樣干的特石，一起來看看。

1.4鳖链、索引為何不選擇B-Tree姆蘸？

在前面提到過，將紅黑樹稍微改造一下，讓其單節(jié)點可容納多個數(shù)據(jù)逞敷，就能在很大程度上改善其性能狂秦，事實上B-Tree就是這么做的，B樹可以理解成紅黑樹的一個變種推捐，如下：

此時給B樹結(jié)構(gòu)也添加了6個節(jié)點故痊，此時觀測上述結(jié)構(gòu)，一方面樹僅有2層玖姑，同時一個節(jié)點中也存儲了多個數(shù)據(jù)，再來看看B樹的查找過程：

哦吼吼吼~慨菱，完美完美焰络，兩次就查到了數(shù)據(jù)，同時一個節(jié)點中還能存儲多個數(shù)據(jù)符喝，可充分利用局部性原理闪彼，讓一次磁盤IO讀取多個數(shù)據(jù)！协饲！但有人可能會問畏腕，上面的一個節(jié)點也只存兩個數(shù)據(jù)啊，沒太大的區(qū)別似乎茉稠。但你要這么想就錯了描馅，B樹中一個節(jié)點可容納的數(shù)據(jù)個數(shù)，可以自己控制的而线，例如：

在這里將Max.Degree更改后铭污，B樹單節(jié)點的容量也會隨之更改，從上圖中可清晰看到一個節(jié)點同時將6個數(shù)據(jù)全存儲進去了膀篮，這也就代表著只需要一次磁盤IO就能檢索出5這個數(shù)據(jù)嘹狞，是不是很完美？先別急著下定律誓竿，畢竟咱們目前是站在索引設(shè)計者的角度來看待問題的磅网，此時雖然看起來很美好了，但想一個問題：

MySQL由于是關(guān)系型數(shù)據(jù)庫筷屡，因此經(jīng)常會碰到范圍查詢的需求涧偷，舉例：

SELECT * FROM zz_user WHERE ID BETWEEN 2 AND 5;

比如上述這條SQL語句，需要查詢表中ID在2~5的所有數(shù)據(jù)速蕊，那也就代表著需要查四條數(shù)據(jù)嫂丙，在這里因為2~5在同一個節(jié)點中，因此僅觸發(fā)一次IO就可拿到數(shù)據(jù)规哲，但實際業(yè)務(wù)中往往不會有這么小的范圍查詢跟啤，假設(shè)此時是查ID=2~1000之間的數(shù)據(jù)呢？這么多數(shù)據(jù)定然不會在一個節(jié)點中，因此這里又會觸發(fā)多次磁盤IO隅肥！

B樹不適合作為索引結(jié)構(gòu)的原因：

雖然對比之前的紅黑樹更矮竿奏，檢索數(shù)據(jù)更快，也能夠充分利用局部性原理減少IO次數(shù)腥放，但對于大范圍查詢的需求泛啸，依舊需要通過多次磁盤IO來檢索數(shù)據(jù)。

1.5秃症、索引為何要選擇B+Tree候址？

上面聊到的B-Tree相對來說已經(jīng)較為完美了，但最后也談到：它并不適合用于范圍查詢种柑，但這種查詢需求在關(guān)系型數(shù)據(jù)庫中又很常見岗仑，那這里怎么優(yōu)化呢？一起來看看B+Tree：

相較于B樹而言聚请，B+樹的結(jié)構(gòu)又出現(xiàn)了新的變化荠雕，一方面節(jié)點分為了葉節(jié)點和葉子節(jié)點兩類，這里先有這兩個概念即可驶赏，后續(xù)介紹這兩類節(jié)點在索引中的作用炸卑。B+樹中除開節(jié)點分為兩類外，還有一個最大的變化就是：最下面的一排節(jié)點之間煤傍，都存在一個單向指針盖文，指向下一個節(jié)點所在的位置，這也是B+樹對B樹的最大改造點：

前面講過患久，由于B樹不適合于大范圍查詢操作椅寺，因此B+樹中多了個指針，當(dāng)需要做范圍查詢時蒋失，只需要定位第一個節(jié)點返帕，然后就可以直接根據(jù)各節(jié)點之間的指針，獲取到對應(yīng)范圍之內(nèi)的所有節(jié)點篙挽，也就是只需要發(fā)生一次IO荆萤，就能夠確定所查范圍之內(nèi)的所有數(shù)據(jù)位置。

OK~铣卡，到現(xiàn)在為止链韭，B+樹以接近完美的形式解決之前其他數(shù)據(jù)結(jié)構(gòu)中的所有問題，因此B+Tree正式成為了MySQL默認的索引結(jié)構(gòu)煮落，因此對于MySQL索引為何要選擇B+Tree的原因大家應(yīng)該也懂了敞峭，MySQL的設(shè)計者在研發(fā)時，也絕對是對比了多種數(shù)據(jù)結(jié)構(gòu)后蝉仇，逐步推導(dǎo)其缺陷旋讹，然后采用更好的數(shù)據(jù)結(jié)構(gòu)代替殖蚕，從而最終推導(dǎo)出了B+Tree。

OK~沉迹，接下來再說一下前面拋出的一個問題：葉節(jié)點和葉子節(jié)點在MySQL索引中的作用睦疫。

要弄明白這個問題，首先得搞清楚葉節(jié)點和葉子節(jié)點是什么鞭呕？其實很簡單：

B+Tree上面這些節(jié)點則被稱為葉節(jié)點蛤育，在MySQL中不會存儲數(shù)據(jù)，僅存儲指向葉子節(jié)點的指針葫松，這樣做的好處在于能夠讓一個葉節(jié)點中存儲更多的元素瓦糕，從而確保樹的高度不會由于數(shù)據(jù)增長而變得很高。

同時腋么，B+Tree最下面這排節(jié)點則被稱為葉子節(jié)點刻坊，這些節(jié)點中會存儲實際的數(shù)據(jù)，例如聚簇索引中就直接存儲對應(yīng)的行數(shù)據(jù)党晋，非聚簇索引中則存儲指向主鍵/聚簇索引的字段值。同時每個葉子節(jié)點之間都有一根單向指針指向下一個節(jié)點徐块，從而使得最下面的一排葉子節(jié)點之間又形成了一個單向鏈表結(jié)構(gòu)未玻，方便范圍取值。

1.5.1胡控、B+Tree結(jié)構(gòu)為何會存在葉節(jié)點呢扳剿？

其實在之前的數(shù)據(jù)結(jié)構(gòu)中，從來沒有葉節(jié)點的這個概念出現(xiàn)昼激，每個節(jié)點信息在整棵樹結(jié)構(gòu)中只會存儲一份庇绽，但為什么B+樹中會用葉節(jié)點，同時冗余一份節(jié)點信息呢橙困？因為你從前面的B+Tree結(jié)構(gòu)中瞧掺，也能明顯觀測到2、3凡傅、4辟狈、5節(jié)點都會出現(xiàn)了兩次。在這里如果想要搞明白為什么要冗余節(jié)點夏跷，你得想明白一個問題：

能不能將所有的索引數(shù)據(jù)哼转、表數(shù)據(jù)全部放入到一個節(jié)點中存儲呢？這樣樹的高度永遠為1呀槽华，是不是只需要經(jīng)過一次磁盤IO耙悸？

其實乍一聽似乎有道理猫态，實則是行不通的佣蓉，因為一次磁盤IO讀取的數(shù)據(jù)量是有限制的披摄，如果將所有的數(shù)據(jù)全放入到一個節(jié)點中存儲，那一次磁盤IO只能讀取節(jié)點的一部分數(shù)據(jù)偏螺，將整個節(jié)點讀完行疏，本質(zhì)上就和之前走一次全表沒區(qū)別了。

理解這個點之后套像，再來看看拋出的問題：B+Tree為何會有葉節(jié)點冗余數(shù)據(jù)呢酿联？

因為B+Tree的每個節(jié)點大小會有限制，所以如果將數(shù)據(jù)存儲在葉節(jié)點上夺巩，會導(dǎo)致單個樹節(jié)點存的索引鍵很少贞让。但如果樹的葉節(jié)點不存實際的行數(shù)據(jù)，就代表單個節(jié)點可以存更多的索引鍵，單個節(jié)點存的越多也就代表著樹的高度會越小答朋，樹的高度越小就等價于查詢時會發(fā)生的磁盤IO次數(shù)越少笨使，IO次數(shù)越少就相當(dāng)于數(shù)據(jù)檢索速度會更快，到這里相信大家應(yīng)該能明白為什么會有葉節(jié)點冗余索引鍵了销部。

但索引中除開索引鍵外，也必須要存數(shù)據(jù)制跟，如果不存數(shù)據(jù)索引就失去了意義舅桩，因此B+tree最下面一排的葉子節(jié)點，其中就會存儲對應(yīng)的索引鍵與行數(shù)據(jù)/聚簇索引字段值雨膨。

一句話來概述擂涛，B+Tree的葉節(jié)點僅是作為一個“過渡者”的角色，主要是為了提升索引效率的聊记，實際的數(shù)據(jù)會保存在最下面的葉子節(jié)點中撒妈，葉節(jié)點中僅有一個指針指向罷了。

1.5.2排监、千萬級別的表B+Tree會有多高狰右？

搞清楚B+Tree的一些疑惑后，此時來倒推一個問題舆床，MySQL中一張千萬級別的數(shù)據(jù)表挟阻，如果基于自增ID的主鍵字段建立B+樹索引，那此時樹會有多高呢峭弟？有人或許會認為附鸽，雖然B+Tree結(jié)構(gòu)很優(yōu)異，但千萬級別的表至少有1000W條數(shù)據(jù)瞒瘸，再怎么樣應(yīng)該也有幾十坷备、幾百的樹高吧？但實際上答案會讓你大吃一驚情臭。

想要科學(xué)的弄懂這個問題省撑，那必須建立在實際的依據(jù)上來計算赌蔑，想要計算出樹高，首先得有三個值：

①索引字段值的大小竟秫。

②MySQL中B+Tree單個節(jié)點的大小娃惯。

③MySQL中單個指針的大小。

如何計算索引字段值的大小呢肥败？

這點要依據(jù)字段所使用的數(shù)據(jù)類型來決定趾浅。假設(shè)此時表的自增ID，創(chuàng)建表時使用的int類型馒稍，int類型在計算機中占4Bytes皿哨，那此時基于ID字段建立主鍵索引時，B+Tree每個節(jié)點的索引鍵大小就為4Bytes纽谒。

如何得知MySQL中B+樹單個節(jié)點的大小呢证膨？

對于索引單個節(jié)點的容量是多少呢？在MySQL中默認使用引擎的一頁大小作為單節(jié)點的容量鼓黔，假設(shè)此時表的存儲引擎為InnoDB央勒，就可以通過下述這條命令查詢：

從上述查詢結(jié)果來看，InnoDB引擎的一頁大小為16384Bytes澳化，也就是16KB订歪，此時也就代表著B+Tree的每個節(jié)點容量為16KB。

MySQL中的指針是多大呢肆捕？

一般來說，操作系統(tǒng)的指針為了方便尋址盖高，一般都與當(dāng)前的操作系統(tǒng)位數(shù)對應(yīng)慎陵，例如32位的系統(tǒng)，指針就是32bit/4Bytes喻奥，64位的操作系統(tǒng)指針則為64bit/8Bytes席纽，但由于64bit的指針尋址范圍太大，目前的計算機根本用不上這么大的尋址范圍撞蚕，因此在MySQL-InnoDB引擎的源碼中润梯，單個指針被縮小到6Bytes大小。

千萬級別的索引樹高計算

從上述三條可得知：單個索引節(jié)點容量為16KB甥厦，主鍵字段值為4B纺铭，指針大小為6B，一個完整的索引信息是由主鍵字段值+指針組成的刀疙，也就是4+6=10B舶赔，那此時先來計算一下單個節(jié)點中可存儲多少個索引信息呢？

16KB / 10B ≈ 1638個谦秧。

那此時來計算一下竟纳，對于一顆高度為2的B+樹撵溃，根節(jié)點可存儲1638個葉子節(jié)點指針，也就代表著B+Tree的第二層有1638個葉子節(jié)點锥累，因為葉子節(jié)點要存儲實際的行數(shù)據(jù)缘挑，假設(shè)表中每行數(shù)據(jù)為1KB，這也就是代表著一個葉子節(jié)點中可存儲16條行數(shù)據(jù)桶略，那么一顆高度為2的B+樹可存儲的索引信息為：1638 * 16 = 26208條數(shù)據(jù)语淘。

再來算算樹高為3的B+樹可以存多少呢？因為最下面一排才是葉子節(jié)點删性，此時樹高為3亏娜，也就代表著中間一排是葉節(jié)點，只存儲指針并不存儲數(shù)據(jù)蹬挺，而每個節(jié)點可容納1638個索引鍵+指針信息维贺，因此計算過程是：1638 * 1638 * 16 = 42928704條。

是不是很令你驚訝巴帮？樹高為3的B+Tree溯泣，竟然可以存儲四千多萬條數(shù)據(jù)，也就代表著千萬級別的表榕茧，走索引查詢的情況下垃沦，大致只需要發(fā)生三次磁盤IO即可獲取數(shù)據(jù)。

當(dāng)然用押，上述的這個數(shù)據(jù)是基于主鍵為int類型肢簿、表的一行數(shù)據(jù)為1KB來計算的，實際情況中會不一樣蜻拨，因為主鍵有可能是bigint類型或其他類型池充，而一行數(shù)據(jù)也可能不僅僅只有1KB。因此對于一張實際的千萬級別表缎讼，它的主鍵索引實際樹高有多少收夸，你結(jié)合主鍵的數(shù)據(jù)類型以及一行數(shù)據(jù)的大小，也可以計算出來血崭，它同時不會太高卧惜。

對實際的千萬表索引樹高感興趣的，我提供一個計算公式：索引鍵大小=索引字段類型所占的空間夹纫、一行表數(shù)據(jù)大小=所有表字段的類型+隱藏字段（20Bytes）所占大小總和咽瓷，得到這兩個值之后，再套入前面的例子中既可得知舰讹。

看到這里忱详，對于索引憑啥那么快？為啥能夠提升查詢性能跺涤？相信大家也有了答案匈睁，畢竟索引樹高才是個位數(shù)监透，發(fā)生的磁盤IO次數(shù)也那么少，檢索數(shù)據(jù)的速度不快才來了個鬼~

不過B+Tree中的每個索引頁中航唆，還會存儲頁頭（頁號胀蛮、指針、偽記錄等）糯钙、頁目錄粪狼、頁尾等信息，大概一共占用128KB左右任岸，因此想要真正的計算出來接近實際情況的索引樹高再榄，還需要把這點考慮在內(nèi)~

1.5.3、前綴索引為何能提升索引性能享潜？

因為前綴索引可以選用一個字段的前N個字符來創(chuàng)建索引困鸥，相較于使用完整字段值做為索引鍵，前綴索引的索引鍵剑按，顯然占用的空間更少疾就，一個索引鍵越小，代表一個B+Tree節(jié)點中可以存儲更多的索引鍵艺蝴，等價于樹高會越小猬腰，也就代表磁盤IO更少，檢索數(shù)據(jù)時自然效率更高猜敢。

二姑荷、建立索引時那些不為人知的內(nèi)幕

弄明白了索引的底層數(shù)據(jù)結(jié)構(gòu)后，再一起來聊一聊創(chuàng)建索引后會發(fā)生什么事情呢缩擂？一般我們都會以下述方式創(chuàng)建索引：

在咱們通過SQL命令創(chuàng)建索引時鼠冕，MySQL首先會判斷一下當(dāng)前表的存儲引擎，索引機制本身是由存儲引擎層提供實現(xiàn)的撇叁，不同的引擎實現(xiàn)的索引也不同，因此創(chuàng)建索引時第一步就會先判斷存儲引擎畦贸，然后根據(jù)不同的存儲引擎創(chuàng)建索引陨闹，這里重點聊一下常用的MyISAM、InnoDB薄坏。

2.1趋厉、常用存儲引擎的數(shù)據(jù)存儲

首先為了能夠?qū)嶋H觀察到兩個引擎之間的區(qū)別，分別使用MyISAM胶坠、InnoDB兩個引擎創(chuàng)建兩張表：

上述過程中創(chuàng)建了兩張表：zz_myisam_index君账、zz_innodb_index，分別使用了不同的引擎沈善，而MySQL中對于所有的數(shù)據(jù)都會放入到磁盤中存儲乡数，因此先來找一下這兩張表的本地位置椭蹄，默認位于C:\ProgramData\MySQL\MySQL Server 5.x\data這個目錄中，在這里保存著所有已創(chuàng)建的數(shù)據(jù)庫磁盤文件净赴，首先從這里面找到對應(yīng)的數(shù)據(jù)庫并進入目錄绳矩，如下：

2.1.1、使用MyISAM引擎的表

zz_myisam_index這張表是使用MyISAM引擎的表玖翅，在磁盤中有三個文件：

zz_myisam_index.frm：該文件中存儲表的結(jié)構(gòu)信息翼馆。

zz_myisam_index.MYD：該文件中存儲表的行數(shù)據(jù)。

zz_myisam_index.MYI：該文件中存儲表的索引數(shù)據(jù)金度。

也就是說应媚，MyISAM引擎的表數(shù)據(jù)和索引數(shù)據(jù)，是分別放在兩個不同的磁盤文件中存儲的猜极，這也意味著MyISAM引擎并不支持聚簇索引中姜，因為聚簇索引要求表數(shù)據(jù)和索引數(shù)據(jù)一起存儲在同一塊空間，而MyISAM的.MYI索引文件中魔吐，存儲的是表數(shù)據(jù)所在的地址指針扎筒。

2.1.2、使用InnoDB引擎的表

zz_innodb_index這張表是使用InnoDB引擎的表酬姆，在磁盤中僅有兩個文件：

zz_innodb_index.frm：該文件中存儲表的結(jié)構(gòu)信息嗜桌。

zz_innodb_index.ibd：該文件中存儲表的行數(shù)據(jù)和索引數(shù)據(jù)。

因為InnoDB引擎中辞色，表數(shù)據(jù)和索引數(shù)據(jù)都一起放在.ibd文件中骨宠，也就代表著索引數(shù)據(jù)和表數(shù)據(jù)是處于同一塊空間存儲的，這符合聚簇索引的定義相满，因此InnoDB支持聚簇索引层亿。

同時也正由于這個原因，所以如果使用InnoDB引擎的表未主動創(chuàng)建聚簇索引立美，它會自動選擇表中的主鍵字段匿又，作為聚簇索引的字段。但如果表中未聲明主鍵字段建蹄，那則會選擇一個非空唯一索引來作為聚簇索引碌更。但如果表中依舊沒有非空的唯一索引，InnoDB則會隱式定義一個主鍵來作為聚簇索引（這個列在上層是不可見的洞慎，是一個按序自增的值）痛单。

OK~，搞明白兩種常用引擎的底層存儲區(qū)別后劲腿，接下來再聊聊手動創(chuàng)建索引后究竟會發(fā)生什么旭绒？

2.2、手動創(chuàng)建索引后發(fā)生的事情

當(dāng)手動創(chuàng)建索引后，MySQL會先看一下當(dāng)前表的存儲引擎是誰挥吵，接著會判斷一下表中是否存在數(shù)據(jù)重父，如果表中沒有數(shù)據(jù)，則直接構(gòu)建一些索引的信息蔫劣，例如索引字段是誰坪郭、索引鍵占多少個字節(jié)、創(chuàng)建的是啥類型索引脉幢、索引的名字歪沃、索引歸屬哪張表、索引的數(shù)據(jù)結(jié)構(gòu).....嫌松，然后直接寫入對應(yīng)的磁盤文件中沪曙，比如MyISAM的表則寫入到.MYI文件中，InnoDB引擎的表則寫入到.ibd文件中萎羔。

上述這個過程液走，是表數(shù)據(jù)為空時，創(chuàng)建索引會干的工作贾陷，還算比較簡單缘眶，但當(dāng)表中有數(shù)據(jù)時，過程也一樣嗎髓废？NO巷懈，會多出很多步驟。

當(dāng)表中有數(shù)據(jù)時慌洪，首先MySQL-Server會先看一下目前要創(chuàng)建什么類型的索引顶燕，然后基于索引的類型對索引字段的值，進行相應(yīng)的處理冈爹，比如：

唯一索引：判斷索引字段的每個值是否存在重復(fù)值涌攻，如果有則拋出錯誤碼和信息。

主鍵索引：判斷主鍵字段的每個值是否重復(fù)频伤、是否有空值恳谎，有則拋出錯誤信息。

全文索引：判斷索引字段的數(shù)據(jù)類型是否為文本憋肖，對索引字段的值進行分詞處理因痛。

前綴索引：對于索引字段的值進行截取工作，選用指定范圍的值作為索引鍵瞬哼。

聯(lián)合索引：對于組成聯(lián)合索引的多個列進行值拼接婚肆，組成多列索引鍵租副。

........

根據(jù)索引類型做了相應(yīng)處理后坐慰，緊接著會再看一下當(dāng)前索引的數(shù)據(jù)結(jié)構(gòu)是什么？是B+Tree、Hash亦或是其他結(jié)構(gòu)结胀，然后根據(jù)數(shù)據(jù)結(jié)構(gòu)對索引字段的值進行再次處理赞咙，如：

B+Tree：對索引字段的值進行排序，按照順序組成B+樹結(jié)構(gòu)糟港。

Hash：對索引字段的值進行哈希計算攀操，處理相應(yīng)的哈希沖突，方便后續(xù)查找秸抚。

.......

到這一步為止速和，已經(jīng)根據(jù)索引結(jié)構(gòu)，對索引字段的值處理好了剥汤，此時就會準備將內(nèi)存中處理好的字段數(shù)據(jù)颠放，寫入到本地相應(yīng)的磁盤文件中，但如果此時為InnoDB引擎吭敢，那在寫入前還會做最后一個判斷碰凶，也就是判斷當(dāng)前的索引是否為主鍵/聚簇索引：

如果當(dāng)前創(chuàng)建索引的字段是主鍵字段，則在寫入時重構(gòu).ibd文件中的數(shù)據(jù)鹿驼，將索引鍵和行數(shù)據(jù)調(diào)整到一塊區(qū)域中存儲欲低。

當(dāng)然，如果這里創(chuàng)建的不是主鍵/聚簇索引畜晰，或者目前是MyISAM引擎砾莱，則意味著現(xiàn)在需要創(chuàng)建的是非聚簇索引，因此會先會為每個索引鍵（索引字段值）尋找相應(yīng)的行數(shù)據(jù)舷蟀，找到之后與索引鍵關(guān)聯(lián)起來恤磷，不過InnoDB、MyISAM引擎兩者之間的非聚簇索引也會存在些許差異野宜，所以在這里也會有一點點不同：

InnoDB：因為有聚簇索引存在扫步，所以非聚簇索引在與行數(shù)據(jù)建立關(guān)聯(lián)時，存放的是主鍵/聚簇索引的字段值匈子。

MyISAM：由于表數(shù)據(jù)在單獨的.MYD文件中河胎，因此可以直接以指針的形式關(guān)聯(lián)起來抡驼。

也就是說踏揣，InnoDB引擎中的非聚簇索引尝抖，都是主鍵/聚簇索引的“附庸”键痛，因此每個索引信息中是以「索引鍵：聚簇字段值」這種形式關(guān)聯(lián)的连锯。

但MyISAM引擎中由于表數(shù)據(jù)和索引數(shù)據(jù)都是分開存儲的谱煤，所以MyISAM的每個非聚簇索引都是獨立的贾铝，因此每個索引信息則是以「索引鍵：行數(shù)據(jù)的地址指針」這種形式關(guān)聯(lián)汇恤。

由于MyISAM引擎的非聚簇索引唾那，關(guān)聯(lián)的是行數(shù)據(jù)的指針访锻，而InnoDB引擎關(guān)聯(lián)的是聚簇索引的索引鍵，所以InnoDB的非聚簇索引在查詢時需要回表，再查一次聚簇索引才能得到數(shù)據(jù)期犬。而MyISAM每個非聚簇索引都能直接獲取到行數(shù)據(jù)的地址河哑，可以直接根據(jù)指針獲取數(shù)據(jù)，從整體而言龟虎，MyISAM檢索數(shù)據(jù)的效率會比InnoDB快上不少璃谨。

到這里，索引鍵和行數(shù)據(jù)關(guān)聯(lián)好之后鲤妥，就會開始根據(jù)引擎的不同佳吞，將內(nèi)存中的索引信息分別寫入到不同的磁盤文件中。寫完完成后棉安，B+Tree的根節(jié)點會放到內(nèi)存中維護容达，以便于后續(xù)索引查詢時再次從磁盤讀取根節(jié)點信息。

到這里為止垂券，大家也應(yīng)該明白了為什么創(chuàng)建表之后花盐，立馬建索引會很快，但當(dāng)表中有不少數(shù)據(jù)時創(chuàng)建索引會很慢的原因菇爪，就是因為表中有數(shù)據(jù)時算芯，創(chuàng)建索引要做一系列判斷、處理工作凳宙。

OK~熙揍，最后再放上一個聚簇索引和非聚簇索引的結(jié)構(gòu)區(qū)別：

在上圖中給出了一張用戶表，然后基于ID字段建立主鍵/聚簇索引氏涩，基于name字段建立普通/非聚簇索引届囚，最終索引結(jié)構(gòu)如圖中所示。

在InnoDB聚簇索引的示意圖中是尖，由于不方便畫出每行數(shù)據(jù)意系，就用row_data代替行數(shù)據(jù)。

在InnoDB非聚簇索引中饺汹，每個索引信息中存儲聚簇索引的ID值蛔添。

MyISAM非聚簇索引中，每個索引信息中則直接存儲行數(shù)據(jù)的指針兜辞。

當(dāng)然迎瞧，這里也是畫出的偽結(jié)構(gòu)，因為不可能按照MySQL單節(jié)點16KB的尺寸1:1還原逸吵，畢竟畫不下這么大（實際MySQL對于上述這些數(shù)據(jù)凶硅，一個節(jié)點就全放下了）。

索引鍵的大小會隨著值長度變化嗎扫皱？

這個問題很有趣足绅，比如現(xiàn)在基于一個int類型的字段建立了一個索引压语，但目前的字段值是1，按理來說這個值只占1bits對不對编检？那在索引鍵中，或數(shù)據(jù)庫中占多少呢扰才？答案是4Bytes/32bits允懂，這是因為一個int類型在操作系統(tǒng)中就會占用32bit空間，不會根據(jù)實際值而減少占用空間衩匣。

但大家也都知道蕾总，數(shù)據(jù)庫中還有不少文本類型，例如varchar類型琅捏，它是固定的長度嗎生百？并不是，它是一個變長類型柄延，而非一個定長類型蚀浆，也就是一個varchar字段值，占用的空間會隨著實際所存儲的數(shù)據(jù)而變化搜吧。

所以對于一個索引鍵的大小是否會發(fā)生變化市俊，這要取決于你是基于什么字段類型建立的索引，如果是定長類型就不會變化滤奈，但如果是變長類型就會隨之發(fā)生改變摆昧。

三、索引內(nèi)部查詢與維護的過程

建立索引時會發(fā)生的內(nèi)部過程蜒程，上一段落已經(jīng)闡述明白了绅你，接著再來說說查詢SQL執(zhí)行時，如果選中了索引昭躺，索引內(nèi)部的檢索過程是什么樣的呢忌锯？也包括當(dāng)寫類型的SQL更改表中數(shù)據(jù)后，MySQL又會如何維護索引的內(nèi)部結(jié)構(gòu)呢领炫？

3.1汉规、聚簇索引查找數(shù)據(jù)的過程

在《SQL執(zhí)行篇》中聊到過，當(dāng)查詢SQL來到MySQL后驹吮，經(jīng)過一系列處理后针史，最終會來到優(yōu)化器，此時會由優(yōu)化器來為SQL語句選擇出一個合適的索引碟狞，當(dāng)然啄枕，你也可以手動強制指定索引。那當(dāng)SQL命中索引時族沃，索引內(nèi)部是如何查找對應(yīng)的行數(shù)據(jù)的频祝？

工作線程執(zhí)行查詢SQL時泌参，首先會先看一下當(dāng)前索引的結(jié)構(gòu)，如果是Hash索引就很簡單了常空，直接對索引字段的值進行哈希計算沽一，然后直接根據(jù)哈希值，從索引中找到相應(yīng)的索引信息漓糙，最后獲取數(shù)據(jù)即可铣缠。

但如果索引結(jié)構(gòu)是默認的B+Tree呢？內(nèi)部又會發(fā)生什么工作昆禽？

如果當(dāng)前SQL使用的是主鍵/聚簇索引蝗蛙，比如：

此時首先會根據(jù)條件字段，去內(nèi)存中找到聚簇索引的根節(jié)點醉鳖，然后根據(jù)節(jié)點中記錄的地址去找次級的葉節(jié)點捡硅，最后再根據(jù)葉節(jié)點中的指針地址，找到最下面的葉子節(jié)點盗棵，從而獲取其中的行數(shù)據(jù)壮韭，動畫過程如下：

B+Tree結(jié)構(gòu)的索引似乎查找過程也并不復(fù)雜對不對？但有一個細節(jié)點需要注意纹因，B+Tree的單個節(jié)點可存儲多個數(shù)據(jù)泰涂，也就是當(dāng)磁盤IO發(fā)生后，MySQL一次讀取的數(shù)據(jù)中有多個索引信息辐怕，此時MySQL會如果查找單個節(jié)點中的索引信息呢逼蒙？全部判斷一次嘛？

其實并不會全部判斷一次寄疏，因為B+Tree是一種有序的數(shù)據(jù)結(jié)構(gòu)是牢，小的會放左邊，大的會放右邊陕截，單個節(jié)點中的索引信息驳棱，同樣遵循這個原理。既然單個節(jié)點中的數(shù)據(jù)也是有序的农曲，所以MySQL同樣會采用二分查找法去檢索數(shù)據(jù)社搅。對于單個節(jié)點中的索引信息，先從索引中間開始查詢乳规，然后判斷一下當(dāng)前SQL中ID=12這個條件形葬，是大于還是小于最中間的索引鍵，小于則去節(jié)點左邊取中間的索引鍵繼續(xù)判斷暮的，大于則去右邊.....笙以，以此類推直至定位到單節(jié)點中對應(yīng)索引鍵為止。

OK冻辩，如果是范圍取值猖腕，比如取ID>=2的所有數(shù)據(jù)拆祈，則會先定位到ID=2的索引鍵，然后通過葉子節(jié)點之后的指針倘感，直接將2之后的數(shù)據(jù)全部取出放坏。

聚簇索引中，定位到了索引鍵老玛，即代表著取到了數(shù)據(jù)淤年，畢竟索引和行數(shù)據(jù)是一起存儲的。

3.2逻炊、非聚簇索引查找數(shù)據(jù)的過程

相較于聚簇索引而言，非聚簇索引前面的步驟都是相同的犁享，僅是最后一步有些許不同罷了余素，非聚簇索引經(jīng)過一系列查詢步驟后，最終會取到一個聚簇索引的字段值炊昆，然后再做一次回表查詢桨吊，也就是再去聚簇索引中查一次才能取到數(shù)據(jù)。

如果是MyISAM引擎凤巨，則直接根據(jù)索引樹中記錄的指針地址视乐，直接觸發(fā)磁盤IO再次讀取數(shù)據(jù)即可。

3.3敢茁、寫SQL執(zhí)行時索引的維護過程

前面分析了查詢SQL執(zhí)行時佑淀，索引查找數(shù)據(jù)的過程，那當(dāng)出現(xiàn)增彰檬、刪伸刃、改SQL時呢？索引會怎么維護呢逢倍？其實這里也要分索引類型捧颅，如果是Hash結(jié)構(gòu)的索引，直接增刪改對應(yīng)的索引鍵即可较雕，但B+Tree結(jié)構(gòu)的索引碉哑，因為要內(nèi)部節(jié)點是有序的，所以需要維護有序性亮蒋。

也就是代表著扣典，插入、更改慎玖、刪除數(shù)據(jù)時激捏，都會對B+Tree索引造成影響。

但先說清一個誤區(qū)凄吏，表中不同的索引在本地有不同的索引記錄远舅，比如ID闰蛔、Name字段分別建立了兩個索引，那么就會有兩顆不同的索引樹寫入到本地磁盤文件中图柏。

3.3.1序六、插入數(shù)據(jù)時索引的變化

B+Tree索引是有序的，對于這點在前面已經(jīng)反復(fù)提到了蚤吹，但如果索引字段是數(shù)值類型例诀，例如int、bigint裁着、long等繁涂，本身就能區(qū)分大小順序，此時可以直接做排序工作二驰。但如果是基于字符串或其他類型的字段建立索引呢扔罪？又該如何排序呀？其實對于這個問題也并不難回答桶雀，大家還記得在建庫建表時矿酵，干的一件事情嘛？

在創(chuàng)建庫表時矗积，咱們通常都會指定一個排序規(guī)則全肮，而這個規(guī)則就是MySQL對非數(shù)值類型字段的排序規(guī)則，比如字符串類型的字段棘捣，MySQL就會基于該規(guī)則對值先做計算處理辜腺，然后得到一個數(shù)值用于排序。

當(dāng)然乍恐，具體排序處理的過程暫且不去糾結(jié)哪自，重點只需搞清楚一點：數(shù)據(jù)庫中任何字段都能排序即可。

OK~禁熏，那此時像數(shù)據(jù)庫中插入一條數(shù)據(jù)時壤巷，還是以之前的用戶表為例，比如：

同樣假設(shè)用戶表上有兩個索引瞧毙，一是基于自增ID建立的主鍵索引胧华，第二個則是基于姓名字段建立的普通索引。當(dāng)表中插入這條數(shù)據(jù)后宙彪，索引又會發(fā)生什么變化呢矩动？咱們分開聊聊。

主鍵/聚簇索引的變化

因為主鍵索引字段释漆，也就是ID字段是順序遞增的悲没，因此只需要在本地索引文件的B+Tree結(jié)構(gòu)中，按照樹結(jié)構(gòu)找到最后的位置男图，將當(dāng)前插入的ID:6作為索引鍵示姿，以當(dāng)前插入的行數(shù)據(jù)作為索引值甜橱，然后插入到最后的節(jié)點中即可。如下：

按序遞增的索引維護栈戳，就是這么簡單~

普通/非聚簇索引的變化

因為姓名字段本身的數(shù)據(jù)類型是字符串岂傲，與數(shù)值型字段天生的有序不同，字符串類型是無序的子檀，因此首先需要根據(jù)已經(jīng)配置好的排序規(guī)則镊掖，先對插入的name：棕熊這個值進行計算，然后根據(jù)計算出的值褂痰，決定當(dāng)前數(shù)據(jù)在B+Tree中的索引位置亩进，計算好之后再執(zhí)行插入工作，過程如下：

相較于主鍵字段的順序ID缩歪，插入字符串類型的name值會復(fù)雜一些归薛，因為從這里可以明顯看到，插入的“棕熊”數(shù)據(jù)經(jīng)過計算后驶冒，它并不排最后面苟翻，而是排中間韵卤，所以要將這個值插入到對應(yīng)的位置骗污，此時樹的節(jié)點就會發(fā)生裂變，后續(xù)的所有葉子節(jié)點都需要往后移動沈条，這個開銷是較大的需忿。

同時，在插入索引信息時蜡歹，會以“棕熊”作為索引鍵屋厘，以ID：6作為索引值，然后一同插入月而，也就是要與行數(shù)據(jù)建立關(guān)聯(lián)（MyISAM引擎則是行數(shù)據(jù)的地址指針）汗洒。

3.3.2、刪除數(shù)據(jù)時索引的變化

例如上述這條刪除語句父款，當(dāng)執(zhí)行后則會先根據(jù)ID在索引樹中查找索引信息溢谤，然后先刪除非聚簇索引上的索引信息，緊接著再去聚簇索引上刪除主鍵索引信息和行數(shù)據(jù)憨攒。

過程大致是相同的世杀，就不再制作動圖演示其過程了，重點要記住的是：先刪非聚簇索引信息肝集，再刪聚簇索引的信息瞻坝，因為聚簇索引上存放著行數(shù)據(jù)，如果先把聚簇索引刪了杏瞻，就無法找到非聚簇索引上的信息了所刀。

3.3.3衙荐、更改數(shù)據(jù)時索引的變化

對于上述這條修改語句，索引維護的過程相信大家自己也能推測出來勉痴，畢竟修改的本質(zhì)就是先刪再插入赫模，首先在聚簇索引上查到ID=6這條數(shù)據(jù)，獲取原本的name字段值：“棕熊”蒸矛，然后以該值去非聚簇索引上找到對應(yīng)的索引信息瀑罗，然后將這個索引信息刪除掉，緊接著再插入一個“狗熊”的索引信息雏掠。

當(dāng)非聚簇索引更新完成后斩祭，緊接著會去更新聚簇索引，聚簇索引就不會刪數(shù)據(jù)了乡话，因為聚簇索引上保存著行數(shù)據(jù)摧玫。

首先會在聚簇索引上根據(jù)ID=6找到對應(yīng)的行數(shù)據(jù)，然后將行數(shù)據(jù)中的name字段更新為“狗熊”绑青。

至此诬像，對于寫SQL執(zhí)行后，索引的維護過程就做了簡單分析闸婴，實際上也并不難坏挠。

PS：實際索引更新數(shù)據(jù)時，具體的過程也會復(fù)雜一些邪乍，會牽扯到鎖機制降狠，也包括會判斷修改的新值與原值的大小，如果大小相同則直接在原空間做修改（直接插入覆蓋）庇楞，如果不同才會先刪再改榜配。

3.4忽孽、主鍵為何推薦使用自增整數(shù)ID贞远？

我曾推薦大家使用自增的整數(shù)ID作為主鍵，而并不是使用隨機的UUID這類字符串等類型浴捆，這是為何呢睛驳？因為觀察前面name索引字段的插入過程烙心，能夠很明顯的觀察到一個現(xiàn)象，字符串是無序的柏靶，當(dāng)使用字符串作為主鍵字段時弃理，在插入數(shù)據(jù)的時候會頻繁破壞原有的樹結(jié)構(gòu)，造成樹分裂以及后續(xù)節(jié)點的挪動屎蜓，一兩個條數(shù)據(jù)插入倒還沒關(guān)系痘昌，但是每一條插入的數(shù)據(jù)都有可能導(dǎo)致樹的結(jié)構(gòu)調(diào)整一次，這個過程的開銷可想而知.....

但是自增的整數(shù)ID就不會有這個問題，因為插入的ID本身就是按序遞增的辆苔，因此插入的每一條新數(shù)據(jù)算灸，都會直接放到B+Tree最后的節(jié)點中存儲。

同時驻啤，除開上述原因外菲驴，還有一個原因就是UUID比整數(shù)自增ID長，UUID至少占位32字節(jié)骑冗，但int類型只占4字節(jié)赊瞬，存儲一個UUID的空間，可以存8個自增整數(shù)ID贼涩。也就代表著單個節(jié)點中巧涧，能存儲的自增ID會比UUID多很多，單個節(jié)點存儲的索引鍵越多.....（后面這一排就不講了遥倦，前面復(fù)述過兩次了谤绳，大家應(yīng)該也懂哈~）

四、索引原理篇總結(jié)

到目前而言袒哥，對于MySQL的索引底層實現(xiàn)缩筛，大多數(shù)內(nèi)容就全面講明白了，從最開始的全表掃描過程堡称，到磁盤IO實現(xiàn)瞎抛、局部性原理、索引為什么默認是B+Tree結(jié)構(gòu)粮呢、建立索引后發(fā)生的一系列事情婿失、寫類型的SQL對索引的影響.....等一系列內(nèi)容進行了深入剖析钞艇。

聚簇索引和非聚簇索引的根本區(qū)別：

聚簇索引中啄寡，表數(shù)據(jù)和索引數(shù)據(jù)是按照相同順序存儲的，非聚簇索引則不是哩照。

聚簇索引在一張表中是唯一的挺物，只能有一個，非聚簇索引則可以存在多個飘弧。

聚簇索引在邏輯+物理上都是連續(xù)的识藤，非聚簇索引則僅是邏輯上的連續(xù)。

聚簇索引中找到了索引鍵就找到了行數(shù)據(jù)次伶，但非聚簇索引還需要做一次回表查詢痴昧。

InnoDB-非聚簇索引與MyISAM-非聚簇索引的區(qū)別：

InnoDB中的非聚簇索引是以聚簇索引的索引鍵，與具體的行數(shù)據(jù)建立關(guān)聯(lián)關(guān)系的冠王。

MyISAM中的非聚簇索引是以行數(shù)據(jù)的地址指針赶撰，與具體的行數(shù)據(jù)建立關(guān)聯(lián)關(guān)系的。

一般來說，由于MyISAM引擎中的索引可以根據(jù)指針直接獲取數(shù)據(jù)豪娜，不需要做二次回表查詢餐胀，因此從整體查詢效率來看，會比InnoDB要快上不少瘤载。