前大眾點評資深研發專家對Mysql索引的解析與底層資料結構的解刨

爛豬皮發表於2018-04-16

1：Mysql索引是什麼

mysql索引: 是一種幫助mysql高效的獲取資料的資料結構，這些資料結構以某種方式引用資料，這種結構就是索引。可簡單理解為排好序的快速查詢資料結構。如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然後從下往下找到y字母，再找到剩下的sql。

1.1：索引分類

單值索引:一個索引包含1個列 create index idx_XX on table(f1) 一個表可以建多個。唯一索引: 索引列的值必須唯一，但允許有空值 create unique index idx_XX on table(f1) 複合索引: 一個索引包含多個列如：create index idx_XX on table(f1,f2，..)

1.2：索引結構

BTree Hash索引 full-text全文索引:

1.3：什麼情況建立索引

主鍵自動建立唯一索引頻繁作為查詢條件的欄位因該建立索引查詢中與其他表關聯的欄位，外來鍵關係建立索引頻繁更新的欄位不適合建立索引 where條件裡用不到的欄位不建立索引單鍵/複合索引的選擇（高併發下傾向複合）查詢中排序的欄位因建立索引查詢中統計或分組欄位

1.4：什麼情況建不建立索引

頻繁增刪改的表表記錄太少資料重複且分佈平均的表欄位。（重複太多索引意義不大）

2：Mysql索引為什麼要用B+Tree實現

2.1：B+樹在資料庫索引中的應用

目前大部分資料庫系統及檔案系統都採用B-Tree或其變種B+Tree作為索引結構

1）在資料庫索引的應用

在資料庫索引的應用中，B+樹按照下列方式進行組織：

① 葉結點的組織方式。B+樹的查詢鍵是資料檔案的主鍵，且索引是稠密的。也就是說，葉結點中為資料檔案的第一個記錄設有一個鍵、指標對，該資料檔案可以按主鍵排序，也可以不按主鍵排序；資料檔案按主鍵排序，且 B +樹是稀疏索引，在葉結點中為資料檔案的每一個塊設有一個鍵、指標對；資料檔案不按鍵屬性排序，且該屬性是 B +樹的查詢鍵，葉結點中為資料檔案裡出現的每個屬性K設有一個鍵、指標對，其中指標執行排序鍵值為 K的記錄中的第一個。

② 非葉結點的組織方式。B+樹中的非葉結點形成了葉結點上的一個多級稀疏索引。每個非葉結點中至少有ceil( m/2 ) 個指標，至多有 m 個指標。

2）B+樹索引的插入和刪除

①在向資料庫中插入新的資料時，同時也需要向資料庫索引中插入相應的索引鍵值，則需要向 B+樹中插入新的鍵值。即上面我們提到的B-樹插入演算法。

②當從資料庫中刪除資料時，同時也需要從資料庫索引中刪除相應的索引鍵值，則需要從 B+樹中刪除該鍵值。即B-樹刪除演算法

2.2： 索引在資料庫中的作用

在資料庫系統的使用過程當中，資料的查詢是使用最頻繁的一種資料操作。

最基本的查詢演算法當然是順序查詢（linear search），遍歷表然後逐行匹配行值是否等於待查詢的關鍵字，其時間複雜度為O（n）。但時間複雜度為O（n）的演算法規模小的表，負載輕的資料庫，也能有好的效能。但是資料增大的時候，時間複雜度為O（n）的演算法顯然是糟糕的，效能就很快下降了。

好在電腦科學的發展提供了很多更優秀的查詢演算法，例如二分查詢（binary search）、二叉樹查詢（binary tree search）等。如果稍微分析一下會發現，每種查詢演算法都只能應用於特定的資料結構之上，例如二分查詢要求被檢索資料有序，而二叉樹查詢只能應用於二叉查詢樹上，但是資料本身的組織結構不可能完全滿足各種資料結構（例如，理論上不可能同時將兩列都按順序進行組織），所以，在資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實現高階查詢演算法。這種資料結構，就是索引。

索引是對資料庫表中一個或多個列的值進行排序的結構。與在表中搜尋所有的行相比，索引用指標指向儲存在表中指定列的資料值，然後根據指定的次序排列這些指標，有助於更快地獲取資訊。通常情況下，只有當經常查詢索引列中的資料時，才需要在表上建立索引。索引將佔用磁碟空間，並且影響數據更新的速度。但是在多數情況下，索引所帶來的資料檢索速度優勢大大超過它的不足之處。

2.3：為什麼使用B-Tree（B+Tree）

1.檔案很大，不可能全部儲存在記憶體中，故要儲存到磁碟上

2.索引的結構組織要儘量減少查詢過程中磁碟I/O的存取次數（為什麼使用B-/+Tree，還跟磁碟存取原理有關。）

3.區域性性原理與磁碟預讀，預讀的長度一般為頁（page）的整倍數，（在許多作業系統中，頁得大小通常為4k）

4.資料庫系統巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入，(由於節點中有兩個陣列，所以地址連續)。而紅黑樹這種結構，h明顯要深的多。由於邏輯上很近的節點（父子）物理上可能很遠，無法利用區域性性

二叉查詢樹進化品種的紅黑樹等資料結構也可以用來實現索引，但是檔案系統及資料庫系統普遍採用B-/+Tree作為索引結構。

一般來說，索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。這樣的話，索引查詢過程中就要產生磁碟I/O消耗，相對於記憶體存取，I/O存取的消耗要高几個數量級，所以評價一個資料結構作為索引的優劣最重要的指標就是在查詢過程中磁碟I/O操作次數的漸進複雜度。換句話說，索引的結構組織要儘量減少查詢過程中磁碟I/O的存取次數。為什麼使用B-/+Tree，還跟磁碟存取原理有關。

區域性性原理與磁碟預讀

由於儲存介質的特性，磁碟本身存取就比主存慢很多，再加上機械運動耗費，磁碟的存取速度往往是主存的幾百分分之一，因此為了提高效率，要儘量減少磁碟I/O。為了達到這個目的，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個位元組，磁碟也會從這個位置開始，順序向後讀取一定長度的資料放入記憶體。這樣做的理論依據是電腦科學中著名的區域性性原理：

當一個資料被用到時，其附近的資料也通常會馬上被使用。

程式執行期間所需要的資料通常比較集中。

由於磁碟順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對於具有區域性性的程式來說，預讀可以提高I/O效率。

預讀的長度一般為頁（page）的整倍數。頁是計算機管理儲存器的邏輯塊，硬體及作業系統往往將主存和磁碟儲存區分割為連續的大小相等的塊，每個儲存塊稱為一頁（在許多作業系統中，頁得大小通常為4k），主存和磁碟以頁為單位交換資料。當程式要讀取的資料不在主存中時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續執行。

我們上面分析B-/+Tree檢索一次最多需要訪問節點：

h =

資料庫系統巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入。為了達到這個目的，在實際實現B- Tree還需要使用如下技巧：

每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也儲存在一個頁裡，加之計算機儲存分配都是按頁對齊的，就實現了一個node只需一次I/O。

B-Tree中一次檢索最多需要h-1次I/O（根節點常駐記憶體），漸進複雜度為O（h）=O（logmN）。一般實際應用中，m是非常大的數字，通常超過100，因此h非常小（通常不超過3）。

綜上所述，用B-Tree作為索引結構效率是非常高的。

而紅黑樹這種結構，h明顯要深的多。由於邏輯上很近的節點（父子）物理上可能很遠，無法利用區域性性，所以紅黑樹的I/O漸進複雜度也為O（h），效率明顯比B-Tree差很多。

3:Mysql索引如何實現

1）主鍵索引：

MyISAM引擎使用B+Tree作為索引結構，葉節點的data域存放的是資料記錄的地址。下圖是MyISAM主鍵索引的原理圖：

（圖myisam1）

這裡設表一共有三列，假設我們以Col1為主鍵，圖myisam1是一個MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引檔案僅僅儲存資料記錄的地址。

2）輔助索引（Secondary key）

在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重複。如果我們在Col2上建立一個輔助索引，則此索引的結構如下圖所示：

同樣也是一顆B+Tree，data域儲存資料記錄的地址。因此，MyISAM中索引檢索的演算法為首先按照B+Tree搜尋演算法搜尋索引，如果指定的Key存在，則取出其data域的值，然後以data域的值為地址，讀取相應資料記錄。

MyISAM的索引方式也叫做“非聚集”的，之所以這麼稱呼是為了與InnoDB的聚集索引區分。

4：InnoDB索引實現

雖然InnoDB也使用B+Tree作為索引結構，但具體實現方式卻與MyISAM截然不同。

第一個重大區別是InnoDB的資料檔案本身就是索引檔案。從上文知道，MyISAM索引檔案和資料檔案是分離的，索引檔案僅儲存資料記錄的地址。而在InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域儲存了完整的資料記錄。這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引。

上圖是InnoDB主索引（同時也是資料檔案）的示意圖，可以看到葉節點包含了完整的資料記錄。這種索引叫做聚集索引。因為InnoDB的資料檔案本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵，這個欄位長度為6個位元組，型別為長整形。

第二個與MyISAM索引的不同是InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。例如，下圖為定義在Col3上的一個輔助索引：

這裡以英文字元的ASCII碼作為比較準則。聚集索引這種實現方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。

瞭解不同儲存引擎的索引實現方式對於正確使用和優化索引都非常有幫助，例如知道了InnoDB的索引實現後，就很容易明白為什麼不建議使用過長的欄位作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調的欄位作為主鍵在InnoDB中不是個好主意，因為InnoDB資料檔案本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時資料檔案為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增欄位作為主鍵則是一個很好的選擇。

4：程式設計師進階方法

以上是我總結出的Mysql索引底層資料結構剖析，但在此，我還想給大家一種學習方法，讓大家不單單在理論有所收穫，還能在工作實踐中收穫更多。我推薦的這種方法。

不管你是面對目前流行的技術不知從何下手，需要突破技術瓶頸的可以學。
不管你是在公司待久了，過得很安逸，但跳槽時面試碰壁。需要在短時間內進修、跳槽拿高薪的可以學。
不管你是沒有工作經驗，但基礎非常紮實，對java工作機制，常用設計思想，常用java開發框架掌握熟練的，可以學。（小白就不要學了，先學好基礎）
不管你是覺得自己很牛B，一般需求都能搞定。但是所學的知識點沒有系統化，很難在技術領域繼續突破的可以學。

在此我向大家推薦一個交流學習群：575745314 （加群可以學習程式設計師進階方法）裡面會分享一些資深架構師錄製的視訊錄影：有Spring，MyBatis，Netty原始碼分析，高併發、高效能、分散式、微服務架構的原理，JVM效能優化這些成為架構師必備的知識體系。還能領取免費的學習資源，目前受益良多

以下是程式設計師的進階方法：

一、原始碼分析