大家有沒有遇到過慢查詢的情況，執行一條SQL需要幾秒，甚至十幾、幾十秒的時間，這時候DBA就會建議你去把查詢的 SQL 最佳化一下，怎麼最佳化？你能想到的就是加索引吧？

為什麼加索引就查得快了？這就要從索引的本質以及他的底層原理說起。

01 索引是什麼？

那索引到底是什麼呢?你是不是還停留在大學學『資料庫原理』時老師講的“索引就像字典的目錄”這樣的概念？老師講的沒錯，但沒有深入去講。

其實索引就是一種用於快速查詢資料的資料結構，是幫助MySQL高效獲取資料的排好序的資料結構。

02 索引的好處？

舉例說明索引的好處以及是怎麼加快查詢的。

假設我們有一個表t，它有倆欄位，Col1和Col2，如下：

不加索引

不加索引的情況下，SQL： select * from t where t.col2=89 ，需要從表的第一行一行行遍歷比對col2的值是否等於89，這樣需要比對6次才能查到。這只是只有幾行記錄的表，那如果是百萬級千萬級的表呢？是不是就比較的次數就更多了，那還不得慢死。

加索引

如果col2這列加了索引，mysql內部會維護一個資料結構。假設mysql用的資料結構是紅黑樹（右子樹的元素大於根節點，左子樹的元素小於根節點）的資料結構建立索引，那就像上圖右邊那樣。這樣的話，剛才的那條SQL是不是隻需要2次磁碟IO就查到了，是不是快很多了。

這就是索引的好處。索引使用比較巧妙的資料結構，利用資料結構的特性來大大減少查詢遍歷次數。

03 索引底層資料結構的探索？

既然索引底層原理是利用一些巧妙的資料結構維護我們的資料，使得查詢效率很高，那索引底層使用的什麼資料結構呢？又是怎樣來維護我們的資料呢？下面就帶著大家一起探索一下索引的底層資料結構。

索引可選的資料結構 :

• 二叉樹
• 紅黑樹
• hash
• B-Tree

但mysql索引的底層用的並不是二叉樹和紅黑樹。因為二叉樹和紅黑樹在某些場景下都會暴露出一些弊端或者說缺點。

3.1 二叉樹

我們看一下二叉樹如果作為索引的底層資料結構在什麼樣的場景下有怎麼樣的缺點和不足。

假設把剛才的SQL改一下，用col1作為條件來查詢，SQL： select * from t where t.col1 = 6 。

假如把col1作為索引，col1這列的資料特點是從上到下依次遞增，類似於自增主鍵，那在每插入一行在維護二叉樹這樣一個資料結構的時候，我們看一下二叉樹維護成什麼樣子了。

開啟這個網址(國外的)，可以演示資料結構維護的過程。依次插入1、2、3、4、5...

透過這個網站的演示插入這些資料，我們可以看到這樣的一個二叉樹是不是一直在單邊增長，沒有左子樹。再仔細看一下和我們學過的連結串列是不是很像，也就是說 二叉樹在某些場景下退化成了連結串列。

連結串列的查詢是不是需要從頭部遍歷啊，這時候和沒加索引從表的第一行遍歷是不是沒什麼太大區別？這就是mysql索引底層沒有使用二叉樹這種資料結構的原因之一。

當二叉樹像上圖一樣退化成連結串列後，我們去查col1=6的記錄是不是從二叉樹的根節點依次遍歷，遍歷6次才能查到，和不加索引從表裡一行行的遍歷沒太大差別。這是二叉樹所謂索引底層資料結構的弊端之一。

3.2 紅黑樹

那有沒有更好的資料結構用來儲存索引，幫助我們更快的查詢呢？比方說紅黑樹或hash表。

我們先看下紅黑樹。紅黑樹是什麼？

是一種平衡二叉樹，JDK1.8的hashmap就用到了紅黑樹。

那我們把剛才的一樣的資料用紅黑樹來看一下是什麼樣的效果，同樣開啟剛才的網址，我們選擇紅黑樹。

依次插入1、2、3、4、5、6、7看一下效果，可以看到，當有單邊增長的趨勢時紅黑樹會進行一個平衡（旋轉）。這時，我們查詢col1=6的資料時，查了3次，比二叉樹又有了改進。

先告訴你mysql索引用的資料結構也不是紅黑樹，而是B+Tree（B-Tree的變種）。那為什麼MySQL也沒用紅黑樹做索引的資料結構呢？說白了紅黑樹還是有缺陷的。

紅黑樹做索引底層資料結構的缺陷

我們可以想一下，對於一些大公司特別是網際網路公司，表資料動輒數百萬數千萬，那這樣的表我們可以想象一下，現在我們只有7條記錄，樹的高度就達到了4層，那數百萬數千萬甚至上億記錄的表建立的索引它的樹高得有多高？

假如說我查詢的資料在底層的葉子節點上，一般來說都是從根節點開始查詢，假如樹的高度是50，那我要進行50次查詢，50次磁碟IO那得多慢啊這開銷已經很大了。這就是紅黑樹作為索引資料結構的弊端： 樹的高度過高導致查詢效率變慢。

那能不能做一點改造呢？我們看，紅黑樹的樹越高遍歷次數會越多，會因為樹的高度影響查詢效率。所以我們要解決的問題就是減少樹的高度，儘量控制它的高度在一個閾值範圍內。假設說不大於5，即使資料達到1千萬2千萬最多也就5次磁碟IO就找到了，5次磁碟IO也是可以接受的畢竟表資料這麼大嘛。

怎麼改造能達到這個效果呢？？？？想一下，既然樹的高度不讓增加，又想存很多資料。也就是說限制了縱向發展，那就橫向發展唄。（身高已經增長不了了，長胖還是可以的）

對於上圖的紅黑樹來說每個節點的子節點最多就2個，那基於橫向增長的思想就讓它變成3叉、4叉、5叉.....讓子節點增加，讓每一個高度可以儲存更多的索引元素，每個節點又分叉，分出來的叉又有很多個節點。那麼儲存同等數量級別的資料，橫向儲存的越多，樹高就越小了。這樣的一個改造結果就是B-Tree。

3.3 Hash

待會兒有別的問題會引入hash。

3.4 B-Tree

• 葉節點具有相同的深度，葉節點的指標為空
• 所有索引元素不重複
• 節點中的資料索引從左到右遞增排序

就這樣的一個結構。也就是說在一個節點上可以儲存更多的元素，k-v，key就是索引欄位，data就是索引欄位所在的那一行的資料或是那一行資料坐在的的磁碟檔案地址、指標，再去查詢元素的時候一次性不是Load一個小元素，而是把一個大的節點的資料一次性全部load到記憶體，然後再在記憶體裡再去比對，在記憶體裡操作是比較快的。

如果我們要查詢49這個元素，實際上是從根節點開始查詢的，它一次性將根節點這個大節點一次性load到記憶體裡，然後用要查詢的元素在這裡去比對，49大於15小於56，在15和56之間有一個節點儲存的是下一個節點的磁碟地址指向下一個節點（這個節點的索引都是大於15小於56的），然後再將這個節點一次性load到記憶體去找這個元素，然後比對就找到了。

注意，一次load節點是一次磁碟IO，是非常慢的，但是我們把它load到記憶體中之後在你記憶體裡隨機地找某一個元素是非常快的，跟一次磁碟IO這個時間消耗去比對的話幾乎可以忽略不計。

那按這種說法樹的高度越小越好，那按這種思路可不可以把一個表的資料都放到一個大的節點上？然後把這個節點一次性load到記憶體裡，我再在記憶體裡一個個去比對不行嗎？不是說記憶體裡去比較查詢元素是非常的快嘛，跟一次磁碟IO去比對快的多。不可以這樣嗎？

答案是否定的。

凡事都有個度。你想想，假如我們有幾千萬資料，在磁碟上面全部放到一個節點上去是不可能的，你的資料表是一行行插入的，存在磁碟上面幾百兆甚至幾個G，一次性load到記憶體中合適嗎？記憶體本來就有限，一次性load這麼大的資料，而且如果你學過計算機組成原理你也知道，磁碟IO跟記憶體打交道的單位是4K，一次可能讀取4K的資料，可能有時候有一些區域性讀取的原理可能會取幾十K（4的整數倍），取個16K，24K也是可以的 。但是一次互動取這麼大是搞不定的，這是計算機組成原理定的，一次磁碟IO取那麼多資料，對記憶體也是非常的浪費，而且這一次磁碟IO也是非常慢的。所以這個節點的大小設定要合適，不能太大也不能太小，mysql對這個節點大小設定的是16K，用下面這個SQL就是可以查到 show clobal status like 'Innodb_page_size' 。

為啥設定16K？為什麼不是更大的如16M呢，16K已經足夠用了。

MySQL索引選擇的不是原生的B-Tree，而是對它進行了改造，得到的是一種叫做B+Tree的資料結構

3.5 B+Tree（B-Tree變種）

• 非葉子節點不儲存data，只儲存索引（冗餘），可以放更多的索引
• 葉子節點包含所有索引欄位
• 葉子節點用指標連線，提高區間訪問的效能

和B-Tree有啥區別？

非葉子節點沒有資料，資料都挪到葉子節點，葉子節點之間還有指標，非葉子節點之間跟原來一樣沒有指標。

為啥data元素挪到葉子節點？

非葉子節點只儲存索引元素，葉子節點儲存了一份完整表的所有行的索引欄位，data元素是每個索引元素對應要查詢的行記錄的位置或行資料，這樣非葉子節點的每個節點就可以儲存更多的索引元素（等會會有一個大致的估算）。實際上非葉子節點儲存的是一些冗餘索引，看一下上圖，15/20/49，選擇的是整張表的哪些資料作為索引？選擇的是處於中間位置的，因為它要用到B+Tree一些比大小去查詢，B+Tree本質可以叫做多叉平衡樹，單看B+Tree的某一小塊他還是一個二叉樹。

還有一個特點，某一個節點的元素處於一個遞增的順序，會提取葉子節點的一些處於中間位置的資料作為冗餘索引，查詢的時候從根節點開始查詢，先把根節點載入到記憶體裡去，然後在記憶體裡去比對。

比如要查詢索引為30的資料，先在根節點跟15去比較，大於15，然後小於56，然後從他倆中間的指標查詢下一個節點把它load到記憶體，再在記憶體裡去比對，大於15，大於20，然後小於49，就根據20和49之間的指標找到下一個節點，然後loa到記憶體，去比對，不等於20下一個30，相等，OK了。

為什麼把中間的元素提取出來做冗餘元素，為的是查詢效率更高。

回到剛剛的問題，為啥要搞這些冗餘索引，而且把這些冗餘索引的data元素搞到葉子節點？也就是說B+Tree相當於與B-Tree來說我的非葉子節點是不儲存data元素的，葉子幾點才儲存data元素？

你想一下，一個節點不能太大也不能太小，就是16K，把data元素挪走以後，是不是這個節點就能存更多的冗餘索引了，意味著分叉就更多了，意味著葉子節點就能儲存更多的資料了。

假設索引欄位型別是Bigint，8bit，每兩個元素之間存的是下一個節點的地址，mysql分配的是6bit，也就是說一個索引後面配對一個節點地址，成對出現，可以算一下16K的節點可以存多少對也就是多少個索引，8b+6b=14b，16K /14b=1170個索引，葉子節點有索引有data元素，假設佔1K，那一個節點就放16K/1K=16個元素，假設樹高是3，所有節點都放滿，能放多少資料？可以算一下，1170 117016=21902400，2千多萬，mysql設定16K的大小，資料就可以存2千多萬就已經足夠了吧，既能保證一次磁碟IO不要Load太多的資料 又能保證一次load的效能，即便表的資料在幾千萬的數量也能保證樹的高度在一個可控的範圍。

可以看一下幾千萬的資料表是不是加了索引幾十毫秒幾百毫秒就出結果了，所以就解釋了幾千萬的表精確的使用索引後他的效能依舊比較高。

樹的高度只有3的情況下就能儲存2千多萬的資料，即便某一個索引在葉子節點，那也就2、3次磁碟IO就能查詢到，當然很快了。而且mysql底層的索引他的根節點，是常駐記憶體的，直接就放到記憶體的，查詢葉子節點，一個2千萬的資料放到B+Tree上面，要查詢葉子節點，就只需要2次磁碟IO就搞定了，在記憶體裡比對的時間基本可以忽略。

04 MySQL是如何儲存索引和資料的？

剛才講的原理性的比較多，現在結合具體的mysql的表不同的索引來看一下它底層到底是如何運用B+Tree來維護索引的。

4.1 索引和資料存放位置是哪？

首先問下mysql的表、資料、索引是放到哪裡的？

磁碟=》預設是安裝目錄的data檔案裡（不同版本可能有所不同），每個資料庫對應data資料夾裡的一個資料夾

我們開啟一個walking_mybatis資料庫看一下有一個user表，再開啟對應的資料夾看一下，裡面的檔名和表名有關係，然後有不同的字尾，這裡面的不同的放法和mysql的儲存引擎有關，和你選擇的哪種儲存引擎有關。

4.2 儲存引擎是修飾什麼的？

大家都知道，mysql常見的儲存引擎有InnoDB儲存引擎，MYISAM儲存引擎，那儲存引擎是形容mysql資料庫的還是某一張表的？

是表，儘管資料庫級別也有儲存引擎選項，但最終還是以表的儲存引擎為主的。

如果你用Navicat工具去建表，也許你最多就用了“欄位”這一欄去增加欄位，你可以點一下“選項”看一下，可以選擇儲存引擎。

我這邊又新建一個order表，然後選擇為MYISAM儲存引擎

在表上右鍵選擇『物件資訊』->『DDL』檢視

看一下user表的

4.3 索引和資料檔案

再來看一下這個資料庫資料夾下這倆表的資料檔案。

我們會發現，user表（InnoDB儲存引擎）對應兩個檔案，order表（MYISAM儲存引擎）對應3個檔案。其中.frm檔案是儲存的是表結構，兩個儲存引擎都一樣，而InnoDB的.ibd檔案是索引+資料，MYISAM的.MYI（I：index）和.MYD（D：data）檔案分別是索引欄位的索引結構和資料檔案，也就是說MYISAM儲存引擎的索引和資料是分開的，而InnoDB儲存引擎的資料和索引是在一個檔案裡的。

4.4 InnoDB和MYISAM的一些不同

MYISAM儲存引擎

MYISAM索引實現（非聚集）

• 索引檔案和資料檔案是分離的（非聚集）

資料、行記錄是儲存在MYD檔案，假如col1是索引欄位那麼這一列是儲存在MYI檔案裡以B+Tree的結構來組織的，然後他的葉子節點的data部分儲存的是索引所在行記錄的磁碟檔案地址，根據磁碟檔案地址指標就可以從MYD檔案裡快速的找到我們的這一行記錄。

查詢過程

所以MYISAM這個儲存引擎他的查詢的一個大致過程就是，先看條件欄位有沒有用到索引，是索引欄位就先去到索引檔案去查詢這個索引所在的那一行的磁碟檔案地址，就藉助B+Tree的特點從根節點順藤摸瓜找到磁碟檔案地址指標，然後從MYD檔案一次性定位到所找的資料，也就是說MYISAM會垮兩個檔案。

InnoDB儲存引擎

InnoDB索引實現（聚集）

• 表資料檔案本身就是按B+Tree組織的一個索引結構檔案
• 聚集索引-葉子節點包含了完整的資料記錄
• 為什麼InnoDB表必須有主鍵，並且推薦使用整型的自增主鍵？
• 為什麼非主鍵索引結構葉子幾點儲存的是主鍵值？（一致性和節省儲存空間）

用的最多的InnoDB儲存引擎是什麼樣子的呢？我們可以看到，它只有兩個檔案。.rm檔案和MYISAM一樣都是表結構檔案，.ibd檔案就是MYISAM的MYI和MYD檔案的合併，索引檔案和資料檔案都儲存到一個檔案。

InnoDB儲存引擎索引儲存結構大概是下圖這樣的，它也是一個B+Tree，但是它的葉子節點和MYISAM有點區別，它儲存的是索引所在行的所有欄位。

這個好處是是什麼？不用回表了，效能應該比MYISAM高，你看MYISAM查詢到索引所在行記錄的磁碟地址後還要回MYD檔案讀取一次。

4.5 聚集索引/非聚集索引

聚集索引/聚簇索引，葉子節點包含了完整的資料記錄，InnoDB的主鍵索引就是一個聚集索引，他的索引和資料是繫結在一起的（葉子節點）。MYISAM的是非聚集索引，索引和資料是分開儲存的。InnoDB的主鍵索引我們叫做聚集索引。

05 為什麼InnoDB表必須有主鍵，並且推薦使用整型的自增主鍵？

我們看一下這個問題 為什麼InnoDB表必須有主鍵，並且推薦使用整型的自增主鍵？

為甚innoDB表建議要有自增的主鍵，儘量建主鍵，建整形自增的？其實很簡單，設計如此，mysql設計的就是innoDB把你的資料和主鍵索引用B+Tree來組織的，沒有主鍵他的資料就沒有一個結構來儲存。

建innoDB表的時候沒有建主鍵，表也能建成功，為什麼？

不建主鍵不代表沒有主鍵，沒有建主鍵innodb會幫你選一個欄位，一個可以標識唯一的欄位，選為預設欄位，如果這個欄位唯一的話，不重複，可一鍵唯一索引的話，就會作為類似於唯一索引，用這個欄位來作為唯一索引來維護整個表的資料。如果沒有，mysql會生成一個唯一的列，類似於rowid，只不過你看不到，他會用生成的這個唯一列，維護B+Tree的結構，查資料的時候還是用B+Tree的結構去查詢。

為什麼推薦整形呢？

我們想象一下查詢過程，是把節點load到記憶體然後在記憶體裡去比較大小，也就是在查詢的過程中要不斷地去進行資料的比對。假設UUID，既不自增也不是整形。問一下，是整形的1<2比較的效率高還是字串的“abc”和“abe”比較的效率高呢？顯然是前者，因為字串的比較是轉換成ASICI一位一位的比，如果最後一位不一樣，比到最後才比較出大小，就比整形比較慢多了，儲存空間來說，整形更小。索引越節約資源越好。

為什麼是自增的呢？

我們可以看一下B-Tree的葉子節點之間是沒有指標的，B+Tree最佳化後增加了葉子節點之間的指標，如果我們遍歷資料，從當前節點遍歷完之後，就可以根據節點間的指標快速找到下一個節點去遍歷。講到這，穿插一下B+Tree為什麼要比B-Tree多一個節點間指標呢？那就講一下索引的另一種資料結構就是hash。

06 HASH索引

99.99的情況都是用B+Tree，也有些情況用hash。假設我們的索引選的是hash的資料結構，每插入一個元素會把我們的索引欄位做一次hash計算，把運算的到的結果值和這一行的所在磁碟地址做一個對映。

對索引元素的值做一次hash運算就可以在hash對映表裡快速找到這一行的磁碟檔案地址，經過一次hash就可以快速定位到索引所在行的磁碟檔案地址，hash這麼快，表有一億個資料按這種演算法，那也就可能經歷一次hash運算就可以快速找到某頁任意一行資料元素的所在的磁碟檔案地址，那比B+Tree快的多啊！就是快的多，為啥99.99的都是B+Tree不是hash呢？

hash的等值查詢比B+Tree快，上億依然很快，為啥很快卻不使用？最主要的原因是什麼？因為如果使用範圍查詢，hash就沒有用武之地了，範圍查詢也是很常用的吧，所以基本就不怎麼用hash這種資料結構。那B+Tree就很好的支撐範圍查詢嗎？

是，B+Tree可以很好的支撐。

看一下這個B+Tree的結構

剛才我們說了B+Tree的任一葉子節點內部是從左到右都是遞增的，且節點之間有一個指標（雙向的，圖不標準），

假設我們查大於20的記錄，mysql內部是怎麼查詢的？先從根節點，定位到大於20的元素，然後依次從左到右找到30，然後這個節點遍歷完了，就可以根據指標找到下一個節點的位置，因為B+Tree的特點，後面的元素全都大於20，就這樣順藤摸瓜把後面的元素全弄出來。

那B-Tree沒有這個指標的話查詢大於20 的元素那得多麻煩，先找出第一個節點中大於20的全部元素，因為還有別的節點，所以又要從根節點去遍歷找下一個葉子節點，是不是非常慢。沒有這個指標每次都要從根節點開始查詢然後合併，那是非常慢的。

07 為什麼非主鍵索引結構葉子節點儲存的是主鍵值？

為了一致性和節省儲存空間。已經維護了一套主鍵索引+資料的B+Tree結構，如果再有其他的非主鍵索引的話，索引的葉子節點儲存的是主鍵，這是為了節省空間，因為繼續存資料的話，那就會導致一份資料存了多份，空間佔用就會翻倍。另一方面也是一致性的考慮，都透過主鍵索引來找到最終的資料，避免維護多份資料導致不一致的情況。

08 聯合索引

儘量建聯合索引，少建單值索引 。剛講的都是單值索引

聯合索引的底層資料結構是什麼樣的？

多個列逐個欄位去比較，（a,b,c）

多個索引有多個B+樹結構，非主鍵索引葉子節點儲存的不是資料，而是主鍵（一致性和節省空間）