程式設計師必須瞭解的知識點——你搞懂mysql索引機制了嗎？

一、索引是什麼

MySQL官方對索引的定義為：索引（Index）是幫助MySQL 高效 獲取資料的資料結構，而MYSQL使用的資料結構是：B+樹

在這裡推薦大家看一本書，《深入理解計算機系統的書》

1.1 區域性性原理

程式和資料的存取都有聚整合群的傾向，在一個時間段內，僅使用其中一小部分，在最近的將來將用到的資訊很可能與現在正在使用的資訊在空間地址上是臨近的（稱空間區域性性），或者最近存取過的程式程式碼和資料，很快又被存取的可能性很大（稱時間區域性性）。

1.2 磁碟預讀

預讀的長度一般為頁（page）的整數倍
頁是記憶體的邏輯塊，作業系統往往將主記憶體和磁碟儲存區分割成連續的大小相等的塊，每個儲存塊稱為一頁（在許多作業系統中，頁大小通常為4K），主記憶體和磁碟以頁為單位交換資料

1.3 簡介

在使用資料庫中，通常資料庫查詢是資料庫的最主要功能之一。但每種查詢演演算法都只能應用於特定的資料結構之上。

• 例如二分查詢要求被檢索資料有序
• 而二元樹查詢只能應用於二叉查詢樹上，但是資料本身的組織結構不可能完全滿足各種資料結構（例如，理論上不可能同時將兩列都按順序進行組織），所以，在資料之外，資料庫系統還維護著滿足特定查詢演演算法的資料結構，這些資料結構以某種方式參照（指向）資料，這樣就可以在這些資料結構上實現高階查詢演演算法。這種資料結構，就是索引。

索引一般以檔案形式儲存在磁碟上，索引檢索需要磁碟I/O操作。所以評價一個資料結構作為索引的優劣最重要的指標就是在查詢過程中磁碟I/O操作次數的漸進複雜度。

1. 索引是幫助 MYSQL 高效獲取資料的資料結構
2. 索引儲存在檔案系統中
3. 索引的 檔案儲存形式與儲存引擎有關
4. 索引檔案的結構：hash、二元樹、B樹、B+樹

二、索引的分類

2.1 hash

id	name
1	手機
2	電腦
3	平板

這裡有一個mysql資料檔案，有Id和name兩個列，如果我們用hash格式儲存的話（hash表），我們只要計算出某一個列的hash值，把它按照按照陣列的長度取一個模，就可以取到從0-7n個下標的位置，這樣的話效率其實是比較高的，但是用hash表儲存，它具備一定的缺點 ：

1. 利用hash儲存的話需要將所有的資料檔案新增到記憶體中，比較耗費記憶體空間
2. 如果所有的查詢都是等值查詢，那麼hash確實很快，但是在企業或者實際工作環境中範圍查詢的資料更多，而不是等值查詢，因為hash就不太適合了，因此在mysql裡面並沒有選擇hash儲存的格式

2.2 二元樹

索引格式：

對於樹有他是有一個更新跌過的順序在裡面，不要一上來就看結構，先是瞭解什麼樹，樹都是由一個樹根，然後有n多個分支組成，這些分支就是一些樹形結構，多你有多個樹分支（多元素）的時候，這個時候查詢效率就會比較低，因此就有了二元樹的東西，二元樹為什麼會好用一點，因為二元樹它是都有兩個分支，但是兩個分支的話，會導致一個效果，就是每次我們在查詢資料的時候，類似於二分查詢的，但是二元樹也有自己不太好的地方，大家可以看我們上圖中的二元樹的索引格式，在左邊的節點會比較短一點（只需要讀三次），而右邊的節點會長很多（需要讀五次），會導致樹的深度比較深，每一次樹的節點讀取，都會有一次IO，深度越高，IO越高，會影響我們資料讀取的效率，因此也有了（平衡二元樹）和（紅黑樹）

平衡二元樹： 維護一個平衡，就是左子樹和右子樹高度之差，不能大於1，但是對於我們上面的格式就不太適合，因為他已經超過1了，但是AVL樹也會有一個問題就是調整的次數太頻繁了，它裡面涉及到了一個操作就是旋轉，一種左旋，一個右旋，為了保持平衡需要N多次的旋轉，這樣的旋轉其實是很浪費時間的，每次新增或者刪除的時候，都要經歷N多次旋轉，效率太低了

推薦大家一個網站，可以直接看到AVL樹操作過程，有不瞭解的同學可以去看一看，很形象：AVL Trees (Balanced binary search trees)

紅黑樹： 本身也是一個平衡樹，但是它從中間做了一個權衡，就是損失一部分平衡的效能，但是又保持了相對的平衡，它做了這樣一個操作，就是最長子樹的高度，只要不超過最短子樹的兩倍，就可以了，同時在紅黑樹中它引入了紅和黑兩個節點資訊，有了這些資訊它可以幫助我們做一個平衡，在AVL樹有旋轉保持平衡，而紅黑樹有了旋轉和變色兩種來保持平衡，紅黑樹是AVL樹的進階，它損失了一部分平衡的效能，但是維護了我們插入和刪除資料的高效，雖然它損失了一部分效能，但是它依然是一個平衡樹，既然是平衡樹，他最長子樹，不超過最短子樹的兩倍，那意味著如果最短子樹是 4 ，那麼最長子樹就是8，這樣在們查詢資料的時候，又不是一個二分查詢了，效率又會變低

無論是二元樹還是紅黑樹，都會因為樹的深度過深而造成IO次數變多，影響資料的讀取的效率，最重要的就是減少IO

IO是我們IT行業中的一個瓶頸，一個是磁碟IO一個是網路IO，我們作為軟體開發，是沒有辦法去調整硬體方面的瓶頸，只能從從程式裡面減少我們的IO量，我們有兩個方向，一個是減少IO的次數，一個是減少IO的量，從這兩個方面去解決，比如說原來我們讀取資料要讀10次，現在只要讀取一次，這樣的IO量就少了10倍，原來我們需要讀1MB的資料，現在只要讀1KB的資料，
這也就是為什麼我們在寫mysql查詢語句的時候不推薦使用select * from ，因為這樣的查詢會查詢到N多個欄位，本來我只要兩個欄位，但是給了我30個欄位，這樣會導致IO量增加了，因此我們就會去考慮，關於索引的次數能不能減少，因此下面就引出了我們的——B樹

2.3 B樹

B樹的特點：

• 所有的鍵值分佈在整顆樹中
• 搜尋有可能在非葉子結點結束，在關鍵字全集內做一次查詢，效能逼近二分查詢
• 每個節點最多擁有m個子樹
• 根節點至少有2個子樹
• 分支節點至少擁有m/2顆子樹（除根節點和葉子節點外都是分支節點）
• 所有葉子節點都在同一層，每個節點最多可以有m-1個key，並且以升序排列

B樹結構說明：

範例圖說明：
每個節點佔用一個磁碟塊，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指標，指標儲存的是子節點所在磁碟塊的地址，兩個關鍵詞劃分成的三個範圍域對應三個指標指向的子樹的資料的範圍域。以根節點為列，關鍵字為16和34，p1指標指向的子樹的資料範圍小於16，P2指標指向的子樹的資料範圍為16-34，P3指標指向的子樹的資料範圍大於34
查詢關鍵字（28）過程：

1. 根據節點找到磁碟塊1，讀取記憶體【磁碟I/O操作第1次】
2. 比較關鍵字28在區間（16,34）找到磁碟塊1的指標P2
3. 根據P2指標找到磁碟塊3，讀入記憶體【磁碟I/O操作第2次】
4. 比較關鍵字28在區間（25,31），找到磁碟塊3的指標P2
5. 根據P2指標找到磁碟塊8，讀取記憶體，【磁碟I/O操作第3次】
6. 在磁碟塊8中的關鍵字列表找到關鍵字28

缺點：

• 每個節點都有key，同時也包含data，而每個頁儲存空間是有限的，如果data比較大的話會導致每個節點儲存的key數量變小
• 當儲存的資料量很大的時候會導致深度較大，增大查詢時磁碟IO次數，進而影響查詢效能

2.4 B+樹

B+Tree 是在BTree 的基礎之上做的一種優化，變化如下：

• B+Tree 每個節點可以包含更多的節點，這個做的 原因有兩個，第一個原因是為了降低樹的高度，第二個原因是將資料範圍變為多個區間，區間越多，資料檢索的越快
• 非葉子節點儲存key（1,2,3磁碟都是儲存的key），葉子節點儲存key和資料
• 葉子節點兩兩指標相互連線（符合磁碟的預讀特性）順序查詢效能更高

如果當前磁碟塊下沒有其他節點，就是 葉子節點，反之就是 非葉子節點

結構圖：

注意：在B+Tree上有兩個頭指標，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有的葉子節點（即資料節點）之間是一種鏈式環結構，因此可以對B+Tree進行兩種查詢運算，一種是對於主鍵的範圍查詢和分頁查詢，另一種是從根節點開始，進行隨機查詢。

三、mysql的儲存引擎

3.1 mysql innoDB (葉子節點直接放置資料)

id	name
1	電腦
2	手機
3	冰箱
4	空調
5	風扇
6	彩電

3.1 mysql innoDB (葉子節點直接放置資料)

存放的是對應的行記錄

1、InnoDB是通過B+Tree結構對主鍵建立索引，然後葉子節點中儲存記錄，如果沒有主鍵，那麼會選擇唯一鍵，如果沒有唯一鍵，那麼會生成一個6位的row_id來作為主鍵
2、如果建立索引的鍵是其他欄位，那麼在葉子節點中儲存的是該記錄的主鍵，然後在通過主鍵索引找到對應的記錄

在name上建立索引

在name列上存放的是ID，然後通過ID去找到對應的key和資料

3.1 mysql MyISAM

下面0X0022其實就是地址，顯示根據我們的ID，找到我們的地址，然後通過地址去找到對應的表對應的資料

四、索引的分類

mysql索引的五種型別：主鍵索引、唯一索引、普通索引和全文索引、組合索引。通過給欄位新增索引可以提高資料的讀取速度，提高專案的並行能力和抗壓能力

• 主鍵索引：

  主鍵是一種唯一性索引，但它必須指定為PRIMARY KEY，每個表只能有一個主鍵

• 唯一索引

  索引列的所有值都只能出現一次，即必須唯一，值可以為空

• 普通索引

  基本的索引型別，值可以為空，沒有唯一性的限制

• 全文索引

  全文索引的索引型別為FULLTEXT，全文索引可以在 varchar、char、text型別的列上建立

• 組合索引

  多列值組成的一個索引，專門用於組合搜尋

五、mysql的儲存引擎

1	MyISAM	InnoDB
索引型別	非聚簇索引	聚簇索引
支援事務	否	是
支援表鎖	是	是
支援行鎖	否	是
支援外來鍵	否	是
支援 全文索引	是	是(5.6後支援)
使用操作型別	大量select	大量insert、delete、update

小結

寫這篇文章的時候，小農的公司群訊息不斷，因為專案中有問題需要我去解決，今天的mysql索引機制就到這裡了，對於本文中有不懂或者疑問的地方，歡迎同學們在下面留言，小農看見了會第一時間回覆大家，謝謝，大家加油~