閱讀本文大約需要 8 分鐘。
提到索引，我相信在座各位應該都會有所瞭解，在實際開發中也會經常用到。比如我們通過慢日誌查詢的時候，看到有SQL查詢比較慢的時候，會想到給哪個欄位加個索引吧。但是什麼是索引？索引又是怎麼工作的呢？
什麼是索引? 所以的出現就是為了提高資料查詢的效率，它就像書的目錄一樣，能讓你進行快速的找到某一章節，更快速的進行定位。
索引常見的模型
索引的實現方式有很多種，我們之前也進行了資料結構學習，正好在這裡可以用到。有興趣的可以進去看下
資料結構
thinkabel，公眾號：白砂
資料結構之PHP二分搜尋樹
常見的實現是雜湊表、有序陣列、搜尋樹，先看下每個資料結構的一些特點：
雜湊表
雜湊是一種以鍵值（key-value）對形式儲存的資料結構，把值放到陣列裡，用一個雜湊函式把key轉換成一個固定的值，然後把value放在這個陣列這個位置。
有序陣列
有序資料作為索引
搜尋樹
就是一個二叉搜尋樹，二叉搜尋樹的特點就是：左子樹的節點值小於父節點的值，右子樹的節點值大於父節點的值。
引發的思考：
我們知道，在經過多個key值經過雜湊函式後，會出現同一個值的情況，這種情況是雜湊衝突。那怎麼進行處理的呢？
在MySQL索引中，處理這個雜湊衝突的方法就是拉出一個連結串列。如下圖所示:
(雜湊表 示意圖)
流程是這樣的，User2 和 User4根據身份證號計算出的雜湊值是N，然後遍歷連結串列，找到要查詢的name。通過上面我們能看到，每個資料塊並不是遞增的，這樣做的好處是增加新的User時，速度是比較快的，直接往後追加。缺點也就顯而易見，不易於做區間查詢，速度是很慢的，成本太高。所以**雜湊表只適合做等值查詢的場景。**
這時你是不是會想，既然雜湊做查區間查詢很慢，那有序索引就可以解決這個問題，效率很高啊。
沒錯，僅僅看有查詢效率中，有序索引就是最好的資料結構，但是有序索引在進行插入操作時，是不是就必須要挪動後面所有的記錄，成本也隨之高了。所以有序索引只適合用於靜態儲存引擎，比如某一年的某某資訊，比較固定。
這時你會想，這兩種方式都不好，那二分搜尋樹可以吧？查詢和更新的時間複雜度都是O(log(N))。
當然，二叉樹的搜尋效率是最高的，索引不止要存在記憶體中，還要寫到磁碟上。可以想象一下，如果樹的深度比較高。樹的不同結點可能不在磁碟的同一頁中，所以每次磁碟的定址載入次數，IO就越多，自然查詢就會慢。
因此這裡就不能使用二叉樹，而是使用“N”叉樹。這個“N”取決於資料塊（也稱頁）的大小。
這些普及知識到這裡先結束了，雖然很枯燥無味，堅持就是勝利。
主鍵索引和普通索引的區別
在InnoDB中，表都是根據主鍵順序以索引形式存放的，這種儲存方式稱之為索引組織表。InnoDB 使用的是B+樹索引模型，所以資料都是存放在B+樹上的。每一個索引在InnoDB中對應一棵B+樹。
mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k)
)engine=InnoDB;
(InnoDB索引 組織結構圖)
從圖中可以看到，主鍵索引的葉子節點存的是整行資料，而非主鍵索引存放的是主鍵的值。（葉子節點之間是通過雙向連結串列進行關聯的，所以在進行區間查詢的時候是很方便的）。
我們通過主鍵id查詢一條語句的話，只需要找到id這顆B+樹，就能找到需要的資料；
但是我們通過普通索引進行查詢的話，需要先找到k的索引B+樹，因為非主鍵索引下放的是主鍵的值，我們需要在拿到這個主鍵的值去主鍵索引樹中繼續查詢。這個過程就有了一個**回表**操作。
非主鍵索引會比主鍵索引多一次掃描索引樹的操作，效率會比主鍵索引低。
索引維護引發的一些問題
既然索引是通過B+樹進行實現的，不然是樹還是陣列，都需要有維護這個樹或者陣列的操作。
還是使用上面的圖來說明吧，比如現在插入新的行ID 是 700， 只需要在R5的記錄後面插入一個新的記錄，整體結構不會有什麼改動。如果新插入的ID值是400，那就需要邏輯上挪動300後面的資料，為400空出位置。
這個時候，如果說這個R5這個資料頁滿了，根據B+數的演算法，就需要申請一個新的資料頁，然後把資料挪動過去。這個過程稱為頁分裂。
這裡借用一下手機的例子，就是比如你整理手機桌面，把一些遊戲的軟體歸納到一個盒子裡，這時盒子第一頁已經滿了，你想往這個裡面放入一個，在你放入位置後面的軟體都需要移動到新的一頁中
我們想一下，在這個分裂的過程中，自然效能會受到影響；也要想到一個點，就是在新的資料頁中，會產生一定的空間浪費。
有分裂自然也就有合併，當相鄰的兩個頁刪除了資料，利用率很低後，會將資料頁進行合併，合併的過程中也就是分裂的逆操作
思考
- 在我們日常開發中，我們應該是使用什麼作為主鍵呢？是以自增id作為主鍵 還是 以業務邏輯的欄位作為主鍵呢？
** 想象一下，使用id為主鍵的時候，建表語句中是不是有 primary key auto_increment。每次插入新資料的時候，是不指定id的，系統會在 AUTO_INCREMENT 記錄下一個的id值。**
也就是說**自增主鍵是每次插入一條記錄，都是追加操作，不會挪動其他記錄，所以不會產生葉子節點的分裂。
**
而業務邏輯做主鍵，是不能保證有序插入的，這樣寫成本的效率相對很高。
還有一點就是，每個非主鍵上的葉子節點儲存的都是主鍵的值， 主鍵長度越小，普通索引節點就越小，佔用空間就越小。從效能和空間佔用方面考慮，自增主鍵往往是比較合理的選擇。
- 在我們正常查詢中，避免不了肯定不能全部都是以主鍵為條件進行查詢的，這個時候，就會有回表操作，怎麼避免回表的操作呢？
解決這個問題呢，我們可以通過覆蓋索引的方式。前提是我們查詢的資料欄位，要是在索引上，這樣索引就已經覆蓋了我們需要的查詢需求，所以在索引上就已經獲取到我們想要的資料，自然也就不需要進行回表了。
覆蓋索引
（InnoDB 索引組織結構）
select * from T where k between 3 and 5我們執行看看這個流程：
1. 在 k 索引樹上找到 k = 3 的記錄，取得主鍵索引值為 300；
2. 在主鍵id索引樹上找到 id = 300 的 行資料 R3；
3. 在 k 索引樹取下一個值 k=5，取得 ID=500；
4. 再回到 ID 索引樹查到 ID=500 對應的 R4；
5. 在 k 索引樹取下一個值 k=6，不滿足條件，迴圈結束。
我們可以看到，回到主鍵索引樹上進行搜尋了兩次，也就是回表了兩次。這個就是回表的過程。
如果我們把SQL語句改成：
select ID from T where k between 3 and 5只需要查詢id的值，id的值已經存在k這個索引樹上，直接就可以講結果返回，不需要在進行回表查詢，所以** 索引樹 k 覆蓋了查詢需求， 這就是覆蓋索引。**
覆蓋索引可以減少樹的搜尋次數，顯著提升了查詢效能，是一種常用的效能優化手段。
最左字首原則
這裡我們會想到，沒給一個欄位都設計一個索引。是不是索引也太多了， 索引多往往也會影響查詢效能，可能還會造成空間浪費，那該怎麼辦？
我們可以使用聯合索引進行優化，B+樹這種索引結構，可以利用索引的最左字首來進行定位記錄。
舉例：
用（name，age）這個聯合索引來分析
索引項是按照索引定義裡面出現的欄位順序排序的
當你的邏輯需求是查到所有名字是 張三 的人時，可以快速定位到 ID4，然後向後遍歷得到所有需要的結果
如果要查的是名字第一個字是 張 的人，SQL 語句條件是 where name like ‘張 %’，查詢到第一個符合的記錄是 ID3，然後向後遍歷，直到不滿足條件為止
只要滿足最左字首，就可以利用索引來加速檢索，可以是聯合索引的最左 N 個欄位，也可以是字串索引的最左 M 個字元
索引下推
這是我們可能會有疑問，就是那些沒有被最左字首命中的記錄，會怎麼樣呢？
如果要查的是名字第一個字是 張 的人，SQL 語句條件是 where name like ‘張 %’，查詢到第一個符合的記錄是 ID3，然後向後遍歷，直到不滿足條件為止。這個時候會查到所有姓張的。
但是我們把條件改成：
select * from tuser where name like ‘張%’ and age=10 and ismale=1這個時候，InnoDB 在 (name,age) 索引內部就判斷了 age 是否等於 10，對於不等於 10 的記錄，直接判斷並跳過。在我們的這個例子中，只需要對 ID4、ID5 這兩條記錄回表取資料判斷，就只需要回表 2 次。
最後，求關注。如果你還想看更多優質原創文章，歡迎關注我的公眾號「白砂」。
如果我的文章對你有所幫助，還請幫忙點贊、在看、轉發一下，你的支援會激勵我輸出更高質量的文章，非常感謝！
本作品採用《CC 協議》，轉載必須註明作者和本文連結