這可能是史上最通俗易懂的 MySQL 索引和 B+Tree 講解了！
你知道的越多，不知道的就越多，業(yè)余的像一棵小草！
你來，我們一起精進！你不來，我和你的競爭對手一起精進！
編輯：業(yè)余草
blog.csdn.net/b_x_p
推薦：https://www.xttblog.com/?p=5290
正確的創(chuàng)建合適的索引，是提升數(shù)據(jù)庫查詢性能的基礎。在正式講解之前，對后面舉例中使用的表結構先簡單看一下：
create?table?user
(
????id?????bigint??not?null?comment?'id'?primary?key,
????name???varchar(200)?null?comment?'name',
????age????bigint???????null?comment?'age',
????gender?int??????????null?comment?'gender',
????key?(name)
);
索引是什么及工作機制？
索引是為了加速對表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散存儲的數(shù)據(jù)結構。其工作機制如下圖：
上圖中，如果現(xiàn)在有一條 sql 語句 select * from user where id = 40，如果沒有索引的條件下，我們要找到這條記錄，我們就需要在數(shù)據(jù)中進行全表掃描，匹配id = 13的數(shù)據(jù)。
如果有了索引，我們就可以通過索引進行快速查找，如上圖中，可以先在索引中通過id = 40進行二分查找，再根據(jù)定位到的地址取出對應的行數(shù)據(jù)。
MySQL 數(shù)據(jù)庫為什么要使用 B+TREE 作為索引的數(shù)據(jù)結構？
二叉樹為什么不可行
對數(shù)據(jù)的加速檢索，首先想到的就是二叉樹，二叉樹的查找時間復雜度可以達到O(log2(n))。下面看一下二叉樹的存儲結構：
二叉樹搜索相當于一個二分查找。二叉查找能大大提升查詢的效率，但是它有一個問題：二叉樹以第一個插入的數(shù)據(jù)作為根節(jié)點，如上圖中，如果只看右側，就會發(fā)現(xiàn)，就是一個線性鏈表結構。如果我們現(xiàn)在的數(shù)據(jù)只包含1, 2, 3, 4就會出現(xiàn)
以下情況：
如果我們要查詢的數(shù)據(jù)為 4，則需要遍歷所有的節(jié)點才能找到 4，即，相當于全表掃描，就是由于存在這種問題，所以二叉查找樹不適合用于作為索引的數(shù)據(jù)結構。
平衡二叉樹為什么不可行
為了解決二叉樹存在線性鏈表的問題，會想到用平衡二叉查找樹來解決。下面看看平衡二叉樹是怎樣的：
平衡二叉查找樹定義為：節(jié)點的子節(jié)點高度差不能超過 1,如上圖中的節(jié)點 20，左節(jié)點高度為 1，右節(jié)點高度 0，差為 1，所以上圖沒有違反定義，它就是一個平衡二叉樹。保證二叉樹平衡的方式為左旋，右旋等操作，至于如何左旋右旋，可以自行去搜索相關的知識。
如果上圖中平衡二叉樹保存的是 id 索引，現(xiàn)在要查找id = 8的數(shù)據(jù)，過程如下：
把根節(jié)點加載進內存，用 8 和 10 進行比較，發(fā)現(xiàn) 8 比 10 小，繼續(xù)加載 10 的左子樹。
把 5 加載進內存，用 8 和 5 比較，同理，加載 5 節(jié)點的右子樹。
此時發(fā)現(xiàn)命中，則讀取 id 為 8 的索引對應的數(shù)據(jù)。
索引保存數(shù)據(jù)的方式一般有兩種：
數(shù)據(jù)區(qū)保存 id 對應行數(shù)據(jù)的所有數(shù)據(jù)具體內容。
數(shù)據(jù)區(qū)保存的是真正保存數(shù)據(jù)的磁盤地址。
到這里，平衡二叉樹解決了存在線性鏈表的問題，數(shù)據(jù)查詢的效率好像也還可以，基本能達到O(log2(n))， 那為什么 mysql 不選擇平衡二叉樹作為索引存儲結構，他又存在什么樣的問題呢？
搜索效率不足。一般來說，在樹結構中，數(shù)據(jù)所處的深度，決定了搜索時的 IO 次數(shù)（MySql 中將每個節(jié)點大小設置為一頁大小，一次IO讀取一頁 / 一個節(jié)點）。如上圖中搜索id = 8的數(shù)據(jù)，需要進行 3 次 IO。當數(shù)據(jù)量到達幾百萬的時候，樹的高度就會很恐怖。
查詢不不穩(wěn)定。如果查詢的數(shù)據(jù)落在根節(jié)點，只需要一次 IO，如果是葉子節(jié)點或者是支節(jié)點，會需要多次 IO 才可以。
存儲的數(shù)據(jù)內容太少。沒有很好利用操作系統(tǒng)和磁盤數(shù)據(jù)交換特性，也沒有利用好磁盤 IO 的預讀能力。因為操作系統(tǒng)和磁盤之間一次數(shù)據(jù)交換是以頁為單位的，一頁大小為 4K，即每次 IO 操作系統(tǒng)會將 4K 數(shù)據(jù)加載進內存。但是，在二叉樹每個節(jié)點的結構只保存一個關鍵字，一個數(shù)據(jù)區(qū)，兩個子節(jié)點的引用，并不能夠填滿 4K 的內容。幸幸苦苦做了一次的 IO 操作，卻只加載了一個關鍵字。在樹的高度很高，恰好又搜索的關鍵字位于葉子節(jié)點或者支節(jié)點的時候，取一個關鍵字要做很多次的 IO。
那有沒有一種結構能夠解決二叉樹的這種問題呢？有，那就是多路平衡查找樹。
多路平衡查找樹(Balance Tree)
B Tree 是一個絕對平衡樹，所有的葉子節(jié)點在同一高度，如下圖所示：
上圖為一個2-3樹（每個節(jié)點存儲 2 個關鍵字，有3路），多路平衡查找樹也就是多叉的意思，從上圖中可以看出，每個節(jié)點保存的關鍵字的個數(shù)和路數(shù)關系為：關鍵字個數(shù) = 路數(shù) – 1。
假設要從上圖中查找id = X的數(shù)據(jù)，B TREE 搜索過程如下：
取出根磁盤塊，加載40和60兩個關鍵字。
如果X等于40，則命中；如果X小于40走P1；如果40 < X < 60走P2；如果X = 60，則命中；如果X > 60走P3。
根據(jù)以上規(guī)則命中后，接下來加載對應的數(shù)據(jù)， 數(shù)據(jù)區(qū)中存儲的是具體的數(shù)據(jù)或者是指向數(shù)據(jù)的指針。
為什么說這種結構能夠解決平衡二叉樹存在的問題呢？
B Tree 能夠很好的利用操作系統(tǒng)和磁盤的交互特性， MySQL 為了很好的利用磁盤的預讀能力，將頁大小設置為16K，即將一個節(jié)點（磁盤塊）的大小設置為16K，一次IO將一個節(jié)點（16K）內容加載進內存。這里，假設關鍵字類型為 int，即4字節(jié)，若每個關鍵字對應的數(shù)據(jù)區(qū)也為4字節(jié)，不考慮子節(jié)點引用的情況下，則上圖中的每個節(jié)點大約能夠存儲（16 * 1000）/ 8 = 2000個關鍵字，共2001個路數(shù)。對于二叉樹，三層高度，最多可以保存7個關鍵字，而對于這種有2001路的B樹，三層高度能夠搜索的關鍵字個數(shù)遠遠的大于二叉樹。
這里順便說一下：在B Tree保證樹的平衡的過程中，每次關鍵字的變化，都會導致結構發(fā)生很大的變化，這個過程是特別浪費時間的，所以創(chuàng)建索引一定要創(chuàng)建合適的索引，而不是把所有的字段都創(chuàng)建索引，創(chuàng)建冗余索引只會在對數(shù)據(jù)進行新增，刪除，修改時增加性能消耗。
B樹確實已經很好的解決了問題，我先這里先繼續(xù)看一下B+Tree結構，再來討論BTree和B+Tree的區(qū)別。
先看看 B+ Tree 是怎樣的，B+Tree是B Tree的一個變種，在B+Tree中，B樹的路數(shù)和關鍵字的個數(shù)的關系不再成立了，數(shù)據(jù)檢索規(guī)則采用的是左閉合區(qū)間，路數(shù)和關鍵個數(shù)關系為1比1，具體如下圖所示：
如果上圖中是用ID做的索引，如果是搜索X = 1的數(shù)據(jù)，搜索規(guī)則如下：
取出根磁盤塊，加載1，28，66三個關鍵字。
X <= 1 走P1，取出磁盤塊，加載1，10，20三個關鍵字。
X <= 1 走P1，取出磁盤塊，加載1，8，9三個關鍵字。
已經到達葉子節(jié)點，命中1，接下來加載對應的數(shù)據(jù)，圖中數(shù)據(jù)區(qū)中存儲的是具體的數(shù)據(jù)。
B TREE和B+TREE區(qū)別是什么？
B+Tree 關鍵字的搜索采用的是左閉合區(qū)間，之所以采用左閉合區(qū)間是因為他要最好的去支持自增id，這也是mysql的設計初衷。即，如果id = 1命中，會繼續(xù)往下查找，直到找到葉子節(jié)點中的1。
B+Tree 根節(jié)點和支節(jié)點沒有數(shù)據(jù)區(qū)，關鍵字對應的數(shù)據(jù)只保存在葉子節(jié)點中。即只有葉子節(jié)點中的關鍵字數(shù)據(jù)區(qū)才會保存真正的數(shù)據(jù)內容或者是內容的地址。而在B樹種，如果根節(jié)點命中，則會直接返回數(shù)據(jù)。
在B+Tree中，葉子節(jié)點不會去保存子節(jié)點的引用。
B+Tree葉子節(jié)點是順序排列的，并且相鄰的節(jié)點具有順序引用的關系，如上圖中葉子節(jié)點之間有指針相連接。
MySQL為什么最終要去選擇B+Tree？
B+Tree是B TREE的變種，B TREE能解決的問題，B+TREE也能夠解決（降低樹的高度，增大節(jié)點存儲數(shù)據(jù)量）
B+Tree掃庫和掃表能力更強。如果我們要根據(jù)索引去進行數(shù)據(jù)表的掃描，對B TREE進行掃描，需要把整棵樹遍歷一遍，而B+TREE只需要遍歷他的所有葉子節(jié)點即可（葉子節(jié)點之間有引用）。
B+TREE磁盤讀寫能力更強。他的根節(jié)點和支節(jié)點不保存數(shù)據(jù)區(qū)，所以根節(jié)點和支節(jié)點同樣大小的情況下，保存的關鍵字要比B TREE要多。而葉子節(jié)點不保存子節(jié)點引用，能用于保存更多的關鍵字和數(shù)據(jù)。所以，B+TREE讀寫一次磁盤加載的關鍵字比B TREE更多。
B+Tree排序能力更強。上面的圖中可以看出，B+Tree天然具有排序功能。
B+Tree查詢性能穩(wěn)定。B+Tree數(shù)據(jù)只保存在葉子節(jié)點，每次查詢數(shù)據(jù)，查詢IO次數(shù)一定是穩(wěn)定的。當然這個每個人的理解都不同，因為在B TREE如果根節(jié)點命中直接返回，確實效率更高。
MySQL B+Tree具體落地形式
這里主要講解的是MySQL根據(jù)B+Tree索引結構不同的兩種存儲引擎（MYISAM 和 INNODB）的實現(xiàn)。
首先找到MySQL保存數(shù)據(jù)的文件夾，看看MySQL是如何保存數(shù)據(jù)的：
mysql>?show?variables?like?'%datadir%';
+---------------+------------------------+
|?Variable_name?|?Value??????????????????|
+---------------+------------------------+
|?datadir???????|?/usr/local/mysql/data/?|
+---------------+------------------------+
進入到這個目錄下，這個目錄下保存的是所有數(shù)據(jù)庫，再進入到具體的一個數(shù)據(jù)庫目錄下。就能夠看到MySQL存儲數(shù)據(jù)和索引的文件了。
這里我創(chuàng)建了兩張表，user_innod和user_myisam，分別指定索引為innodb和myisam。對于每張表，MySQL會創(chuàng)建相應的文件保存數(shù)據(jù)和索引，具體如下：
-rw-rw----.?1?mysql?mysql??????8652?May??3?21:11?user_innodb.frm
-rw-rw----.?1?mysql?mysql?109051904?May??7?21:26?user_innodb.ibd
-rw-rw----.?1?mysql?mysql??????8682?May?16?18:27?user_myisam.frm
-rw-rw----.?1?mysql?mysql?????????0?May?16?18:27?user_myisam.MYD
-rw-rw----.?1?mysql?mysql??????1024?May?16?18:27?user_myisam.MYI
從圖中可以看出：
MYISAM存儲引擎存儲數(shù)據(jù)庫數(shù)據(jù)，一共有三個文件：
Frm：表的定義文件。
MYD：數(shù)據(jù)文件，所有的數(shù)據(jù)保存在這個文件中。
MYI：索引文件。
Innodb存儲引擎存儲數(shù)據(jù)庫數(shù)據(jù)，一共有兩個文件(沒有專門保存數(shù)據(jù)的文件)：
Frm文件：表的定義文件。
Ibd文件：數(shù)據(jù)和索引存儲文件。數(shù)據(jù)以主鍵進行聚集存儲，把真正的數(shù)據(jù)保存在葉子節(jié)點中。
MyISAM存儲引擎
說明：為了畫圖簡便，下面部分圖使用在線數(shù)據(jù)結構工具進行組織數(shù)據(jù)，組織的B+Tree為右閉合區(qū)間，但不影響理解存儲引擎數(shù)據(jù)存儲結構。
在MYISAM存儲引擎中，數(shù)據(jù)和索引的關系如下：
「如何查找數(shù)據(jù)的呢？」
如果要查詢id = 40的數(shù)據(jù)：先根據(jù)MyISAM索引文件（如上圖左）去找id = 40的節(jié)點，通過這個節(jié)點的數(shù)據(jù)區(qū)拿到真正保存數(shù)據(jù)的磁盤地址，再通過這個地址從MYD數(shù)據(jù)文件（如上圖右）中加載對應的記錄。
如果有多個索引，表現(xiàn)形式如下：
所以在 MYISAM 存儲引擎中，主鍵索引和輔助索引是同級別的，沒有主次之分。
Innodb存儲引擎
Innodb主鍵索引為聚集索引，首先簡單理解一下聚集索引的概念：數(shù)據(jù)庫表行中數(shù)據(jù)的物理順序和鍵值的邏輯順序相同。
Innodb以主鍵索引來聚集組織數(shù)據(jù)的存儲，下面看看Innodb是如何組織數(shù)據(jù)的。
如上圖中，葉子節(jié)點的數(shù)據(jù)區(qū)保存的就是真實的數(shù)據(jù)，在通過索引進行檢索的時候，命中葉子節(jié)點，就可以直接從葉子節(jié)點中取出行數(shù)據(jù)。mysql5.5版本之前默認采用的是MyISAM引擎，5.5之后默認采用的是innodb引擎。
「在innodb中，輔助索引的格式如下圖所示？」
如上圖，主鍵索引的葉子節(jié)點保存的是真正的數(shù)據(jù)。而輔助索引葉子節(jié)點的數(shù)據(jù)區(qū)保存的是主鍵索引關鍵字的值。
假如要查詢name = C 的數(shù)據(jù)，其搜索過程如下：
先在輔助索引中通過C查詢最后找到主鍵id = 9.
在主鍵索引中搜索id為9的數(shù)據(jù)，最終在主鍵索引的葉子節(jié)點中獲取到真正的數(shù)據(jù)。
所以通過輔助索引進行檢索，需要檢索兩次索引。
之所以這樣設計，一個原因就是：如果和MyISAM一樣在主鍵索引和輔助索引的葉子節(jié)點中都存放數(shù)據(jù)行指針，一旦數(shù)據(jù)發(fā)生遷移，則需要去重新組織維護所有的索引。
把Innodb 和 MYISAM區(qū)別放在一張圖中看，就如下所示：
創(chuàng)建索引的幾大原則
列的離散型
離散型的計算公式：count(distinct column_name):count(*)，就是用去重后的列值個數(shù)比個數(shù)。值在 (0,1] 范圍內。離散型越高，選擇型越好。
如下表中各個字段，明顯能看出Id的選擇性比gender更高。
mysql>?select?*?from?user;
+----+--------------+------+--------+
|?id?|?name?????????|?age??|?gender?|
+----+--------------+------+--------+
|?20?|?君莫笑???????|???15?|??????1?|
|?40?|?蘇沐橙???????|???12?|??????0?|
|?50?|?張楚嵐???????|???25?|??????1?|
|?60?|?諸葛青???????|???27?|??????1?|
|?61?|?若有人兮?????|???38?|??????0?|
|?64?|?馮寶寶???????|???18?|??????0?|
+----+--------------+------+--------+
「為什么說離散型越高，選擇型越好？」
因為離散度越高，通過索引最終確定的范圍越小，最終掃面的行數(shù)也就越少。
最左匹配原則
對于索引中的關鍵字進行對比的時候，一定是從左往右以此對比，且不可跳過。之前講解的id都為int型數(shù)據(jù)，如果id為字符串的時候，如下圖：
當進行匹配的時候，會把字符串轉換成ascll碼，如abc變成97 98 99，然后從左往右一個字符一個字符進行對比。所以在sql查詢中使用like %a 時候索引會失效，因為%表示全匹配，如果已經全匹配就不需要索引，還不如直接全表掃描。
最少空間原則
前面已經說過，當關鍵字占用的空間越小，則每個節(jié)點保存的關鍵字個數(shù)就越多，每次加載進內存的關鍵字個數(shù)就越多，檢索效率就越高。創(chuàng)建索引的關鍵字要盡可能占用空間小。
聯(lián)合索引
單列索引：節(jié)點中的關鍵字[name]
聯(lián)合索引：節(jié)點中的關鍵字[name, age]
可以把單列索引看成特殊的聯(lián)合索引，聯(lián)合索引的比較也是根據(jù)最左匹配原則。
聯(lián)合索引列的選擇原則
經常用的列優(yōu)先（最左匹配原則）
離散度高的列優(yōu)先（離散度高原則）
寬度小的列優(yōu)先（最少空間原則）
實例分析
下面簡單舉例平時經常會遇到的問題：
如，平時經常使用的查詢sql如下：
select?*?from?users?where?name?=??
select?*?from?users?where?name?=???and?age?=??
為了加快檢索速度，為上面的查詢sql創(chuàng)建索引如下：
create?index?idx_name?on?users(name)
create?index?idx_name_age?on?users(name,?age)
在上面解決方案中，根據(jù)最左匹配原則，idx_name為冗余索引， where name = ?同樣可以利用索引idx_name_age進行檢索。冗余索引會增加維護B+TREE平衡時的性能消耗，并且占用磁盤空間。
覆蓋索引
如果查詢的列，通過索引項的信息可直接返回，則該索引稱之為查詢SQL的覆蓋索引。覆蓋索引可以提高查詢的效率。
如上圖，如果通過name進行數(shù)據(jù)檢索：
select?*?from?users?where?name?=??
需要需要在name索引中找到name對應的Id，然后通過獲取的Id在主鍵索引中查到對應的行。整個過程需要掃描兩次索引，一次name，一次id。
如果我們查詢只想查詢id的值，就可以改寫SQL為：
select?id?from?users?where?name?=??
因為只需要id的值，通過name查詢的時候，掃描完name索引，我們就能夠獲得id的值了，所以就不需要再去掃面id索引，就會直接返回。
當然，如果你同時需要獲取age的值：
select?id，age?from?users?where?name?=??
這樣就無法使用到覆蓋索引了。
知道了覆蓋索引，就知道了為什么sql中要求盡量不要使用select *，要寫明具體要查詢的字段。其中一個原因就是在使用到覆蓋索引的情況下，不需要進入到數(shù)據(jù)區(qū)，數(shù)據(jù)就能直接返回，提升了查詢效率。在用不到覆蓋索引的情況下，也盡可能的不要使用select *，如果行數(shù)據(jù)量特別多的情況下，可以減少數(shù)據(jù)的網(wǎng)絡傳輸量。當然，這都視具體情況而定，通過select返回所有的字段，通用性會更強，一切有利必有弊。
總結
索引列的數(shù)據(jù)長度滿足業(yè)務的情況下能少則少。
表中的索引并不是越多越好，冗余或者無用索引會占用磁盤空間并且會影響增刪改的效率。
Where 條件中，like 9%， like %9%， like%9，三種方式都用不到索引。后兩種方式對于索引是無效的。第一種9%是不確定的，決定于列的離散型，結論上講可以用到，如果發(fā)現(xiàn)離散情況特別差的情況下，查詢優(yōu)化器覺得走索引查詢性能更差，還不如全表掃描。
Where條件中IN可以使用索引， NOT IN 無法使用索引。
多用指定查詢，只返回自己想要的列，少用select *。
查詢條件中使用函數(shù)，索引將會失效，這和列的離散性有關，一旦使用到函數(shù)，函數(shù)具有不確定性。
聯(lián)合索引中，如果不是按照索引最左列開始查找，無法使用索引。
對聯(lián)合索引精確匹配最左前列并范圍匹配另一列，可以使用到索引。
聯(lián)合索引中，如果查詢有某個列的范圍查詢，其右邊所有的列都無法使用索引。