騰訊云數據庫負責人林曉斌說過：“我們面試 MySQL 同事時只考察兩點，索引和鎖”。
言簡意賅，MySQL 索引的重要性不言而喻。MySQL 索引歷經了多個版本的迭代，從語法到底層數據結構都有很多改變。
MySQL 索引，我們真的了解么？好了，今天我們一起來看看 MySQL 索引的前世今生，一起聊聊索引的那些事兒。
什么是索引？
在關系數據庫中，索引是一種單獨的、物理的對數據庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。
索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。
當表中有大量記錄時，若要對表進行查詢：
MySQL 5.5 以后 儲引擎使用的索引數據結構主要用：B+Tree；本篇文章帶大家以 B+Tree 前世今生為主線來聊一聊。
Mark：B+Tree 可以對 =，，IN，以及不以通配符開始的 LIKE 使用索引。（MySQL 5.5 后）
這些事實或許會顛覆你的一些認知，比如在你讀過的其他文章或書中。以上這些都屬于“范圍查詢”，都是不走索引的！
沒錯，早在 5.5 以前，優化器是不會選擇通過索引搜索的，優化器認為這樣取出的行多與全表掃描的行，因為還要回表查一次嘛，可能會涉及 I/O 的行數更多，被優化器放棄。
經過算法（B+Tree）優化后，支持對部分范圍類型的掃描（得利與 B+Tree 數據結構的有序性）。
該做法同時也違反了最左前綴原則，導致范圍查詢后的條件無法用到聯合索引，我們在后面詳細說明。
索引的優缺點
索引的優點如下：
索引的缺點如下：
因此應該只為最經常查詢和最經常排序的數據列建立索引。（MySQL 里同一個數據表里的索引總數限制為 16 個）
數據庫存在的意義之一就是是解決數據存儲和快速查找的。那么數據庫的數據存在哪？沒錯，是磁盤，磁盤的優點是啥？便宜！缺點呢？相比內存訪問速度慢。
那么你知道 MySQL 索引主要使用的數據結構么？B+樹！你脫口而出。
那 B+樹是什么樣的數據結構？MySQL 索引又是為什么選擇了 B+樹呢？
其實最終選用 B+樹是經歷了漫長的演化：
二叉排序樹?→?二叉平衡樹?→?B-Tree（B樹）?→?B+Tree（B+樹）
有小伙伴問我“B 樹跟 B-樹有什么區別”？這里普及一下，MySQL 數據結構只有B-Tree（B 樹）和 B+Tree（B+樹），多只是讀法不同罷了，“B-Tree” 一般統稱為 B 樹，你叫他 B-樹也行！
還有小伙伴提到的紅黑樹，是編程語言中的存儲結構，不是 MySQL 的；如 Java 的 就是用的鏈表加紅黑樹。
好了，今天就帶著大家一起看一下演化成 B+樹的過程吧。
B+Tree 索引的前世今生
①二叉排序樹
理解 B+樹之前，簡單說一下二叉排序樹，對于一個節點，它的左子樹的孩子節點值都要小于它本身，它的右子樹的孩子節點值都要大于它本身。
如果所有節點都滿足這個條件，那么它就是二叉排序樹。（此處可以串一下二分查找的知識點）
上圖是一顆二叉排序樹，你可以嘗試利用它的特點，體驗查找 9 的過程：
一共比較了 4 次，那你有沒有想過上述結構的優化方式？
②AVL 樹（自平衡二叉查找樹）
上圖是 AVL 樹，節點個數和值均和二叉排序樹一摸一樣。
再來看一下查找 9 的過程：
一共比較了 3 次，同樣的數據量比二叉排序樹少了一次，為什么呢？因為 AVL 樹高度要比二叉排序樹小，高度越高意味著比較的次數越多；不要小看優化的這一次，假如是 200w 條數據，比較次數會明顯地不同。
你可以想象一下一棵 100 萬節點的平衡二叉樹，樹高 20。一次查詢可能需要訪問 20 個數據塊。在機械硬盤時代，從磁盤隨機讀一個數據塊需要 10 ms 左右的尋址時間。
也就是說，對于一個 100 萬行的表在哪里可以創建索引文件，如果使用二叉樹來存儲，單獨訪問一個行可能需要 20 個 10 ms 的時間，這個查詢可真夠慢的！
③B 樹（ Tree）多路平衡查找樹，多叉的
B 樹是一種多路自平衡搜索樹，它類似普通的二叉樹，但是 B 樹允許每個節點有更多的子節點。
B 樹示意圖如下：
B 樹的特點如下：
為了提升效率，要盡量減少磁盤 I/O 的次數。實際過程中，磁盤并不是每次嚴格按需讀取，而是每次都會預讀。
磁盤讀取完需要的數據后，會按順序再多讀一部分數據到內存中，這樣做的理論依據是計算機科學中注明的局部性原理：
B-Tree 借助計算機磁盤預讀機制：每次新建節點的時候，都是申請一個頁的空間，所以每查找一個節點只需要一次 I/O；因為實際應用當中，節點深度會很少，所以查找效率很高。
那么最終版的 B+樹是如何做的呢？
④B+Tree (B+樹是 B 樹的變體，也是一種多路搜索樹)
從圖中也可以看到，B+樹與 B 樹的不同在于：
因此，B+Tree 可以對 =，，IN，以及不以通配符開始的 LIKE 使用索引。
B+ 樹的優點，比較的次數均衡，減少了 I/O 次數，提高了查找速度，查找也更穩定：
要知道的是，你每次創建表，系統會為你自動創建一個基于 ID 的聚集索引（上述 B+樹），存儲全部數據。
你每次增加索引，數據庫就會為你創建一個附加索引（上述 B+樹），索引選取的字段個數就是每個節點存儲數據索引的個數，注意該索引并不存儲全部數據。
為什么 MySQL 索引選擇了 B+樹而不是 B 樹？
原因有如下兩點：
程序員，你應該知道的索引知識點
①回表查詢
比如你創建了 name， age 索引 ，查詢數據時使用了：
select?*?from?table?where?name?='陳哈哈'?and?age?=?26;
由于附加索引中只有 name 和 age，因此命中索引后，數據庫還必須回去聚集索引中查找其他數據，這就是回表，這也是你背的那條：少用 * 的原因。
②索引覆蓋
結合回表會更好理解，比如上述 索引，有查詢：
select?name，?age?from?table?where?name?='陳哈哈'?and?age?=?26;
此時 的字段 name，age 在索引 中都能獲取到，所以不需要回表，滿足索引覆蓋，直接返回索引中的數據，效率高。是 DBA 同學優化時的首選優化方式。
③最左前綴原則
B+樹的節點存儲索引順序是從左向右存儲，在匹配的時候自然也要滿足從左向右匹配。
通常我們在建立聯合索引的時候，也就是對多個字段建立索引，相信建立過索引的同學們會發現，無論是 還是 MySQL 都會讓我們選擇索引的順序。
比如我們想在 a，b，c 三個字段上建立一個聯合索引在哪里可以創建索引文件，我們可以選擇自己想要的優先級，a、b、c，或者是 b、a、c 或者是 c、a、b 等順序。
為什么數據庫會讓我們選擇字段的順序呢？不都是三個字段的聯合索引么？這里就引出了數據庫索引的最左前綴原理。
在我們開發中經常會遇到明明這個字段建了聯合索引，但是 SQL 查詢該字段時卻不會使用索引的問題。
比如索引 ：（a，b，c）是 a，b，c 三個字段的聯合索引，下列 sql 執行時都無法命中索引 的。
select?*?from?table?where?c?=?'1';
select?*?from?table?where?b?='1'?and?c?='2';
以下三種情況卻會走索引：
select?*?from?table?where?a?=?'1';
select?*?from?table?where?a?=?'1'?and?b?=?'2';
select?*?from?table?where?a?=?'1'?and?b?=?'2'??and?c='3';
從上面兩個例子大家是否闊以看出點眉目？
是的，索引 ：（a，b，c），只會在（a）、（a，b）、（a，b，c）三種類型的查詢中使用。
其實這里說的有一點歧義，其實（a，c）也會走，但是只走 a 字段索引，不會走 c 字段。
另外還有一個特殊情況說明下，下面這種類型的也只會有 a 與 b 走索引，c 不會走。
select?*?from?table?where?a?=?'1'?and?b?>?'2'??and?c='3';
像上面這種類型的 sql 語句，在 a、b 走完索引后，c 已經是無序了，所以 c 就沒法走索引，優化器會認為還不如全表掃描 c 字段來的快。
最左前綴：顧名思義，就是最左優先，上例中我們創建了 a_b_c 多列索引，相當于創建了（a）單列索引，（a，b）組合索引以及（a，b，c）組合索引。
因此，在創建多列索引時，要根據業務需求，where 子句中使用最頻繁的一列放在最左邊。
④索引下推優化
還是索引 ，有如下 sql：
select?*?from?table?where?name?like?'陳%'?and?age?>?26；
該語句有兩種執行可能：
顯然第 2 種方式回表查詢的行數較少，I/O 次數也會減少，這就是索引下推。所以不是所有 like 都不會命中索引。
使用索引時的注意事項
①索引不會包含有 null 值的列
只要列中包含有 null 值都將不會被包含在索引中，復合索引中只要有一列含有 null 值，那么這一列對于此復合索引就是無效的。所以我們在數據庫設計時建議不要讓字段的默認值為 null。
②使用短索引
對串列進行索引，如果可能應該指定一個前綴長度。
例如，如果有一個 char（255）的列，如果在前 10 個或 20 個字符內，多數值是惟一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和 I/O 操作。
③索引列排序
查詢只使用一個索引，因此如果 where 子句中已經使用了索引的話，那么 order by 中的列是不會使用索引的。
因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引。
④like 語句操作
一般情況下不推薦使用 like 操作，如果非使用不可，如何使用也是一個問題。like “%陳%” 不會使用索引而 like “陳%”可以使用索引。
⑤不要在列上進行運算
這將導致索引失效而進行全表掃描，例如：
SELECT?*?FROM?table_name?WHERE?YEAR(column_name)<2017;
⑥不使用 not in 和 操作
這不屬于支持的范圍查詢條件，不會使用索引。
我的體會
曾經，我一度以為我很懂 MySQL。
剛入職那年，我還是個孩子，記得第一個需求是做個統計接口，查詢近兩小時每隔 5 分鐘為一時間段的網站訪問量， 中一共返回 24 個值，當時菜啊，寫了個接口循環二十四遍，發送 24 條 SQL 去查（捂臉）。
由于那個接口，被技術經理嘲諷表示他寫的 SQL 比我吃的米都多。雖然我們山東人基本不吃米飯，但我還是羞愧不已。
然后經理通過調用一個 函數分組查詢處理一下，就 OK 了，效率是我的幾十倍吧。
從那時起，我就定下目標，深入 MySQL 學習，萬一日后有機會嘲諷回去？
筒子們，MySQL 路漫漫，其修遠兮。永遠不要眼高手低，一起加油，希望本文能對你有所幫助。
END