共計 3364 個字符，預計需要花費 9 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

丸趣 TV 小編給大家分享一下 MySQL 索引提高查詢效率的原因是什么，希望大家閱讀完這篇文章后大所收獲，下面讓我們一起去探討吧！

mysql 教程欄目介紹索引提高查詢效率的原因。背景

我相信大家在數據庫優化的時候都會說到索引，我也不例外，大家也基本上能對數據結構的優化回答個一二三，以及頁緩存之類的都能扯上幾句，但是有一次阿里 P9 的一個面試問我：你能從計算機層面開始說一下一個索引數據加載的流程么？（就是想讓我聊 IO）

我當場就去世了 …. 因為計算機網絡和操作系統的基礎知識真的是我的盲區，不過后面我惡補了，廢話不多說，我們就從計算機加載數據聊起，講一下換個角度聊索引。

正文

MySQL 的索引本質上是一種數據結構

讓我們先來了解一下計算機的數據加載。

磁盤 IO 和預讀：

先說一下磁盤 IO，磁盤讀取數據靠的是機械運動，每一次讀取數據需要尋道、尋點、拷貝到內存三步操作。

尋道時間是磁臂移動到指定磁道所需要的時間，一般在 5ms 以下；

尋點是從磁道中找到數據存在的那個點，平均時間是半圈時間，如果是一個 7200 轉 /min 的磁盤，尋點時間平均是 600000/7200/2=4.17ms；

拷貝到內存的時間很快，和前面兩個時間比起來可以忽略不計，所以一次 IO 的時間平均是在 9ms 左右。聽起來很快，但數據庫百萬級別的數據過一遍就達到了 9000s，顯然就是災難級別的了。

考慮到磁盤 IO 是非常高昂的操作，計算機操作系統做了預讀的優化，當一次 IO 時，不光把當前磁盤地址的數據，而是把相鄰的數據也都讀取到內存緩沖區內，因為當計算機訪問一個地址的數據的時候，與其相鄰的數據也會很快被訪問到。

每一次 IO 讀取的數據我們稱之為一頁(page)，具體一頁有多大數據跟操作系統有關，一般為 4k 或 8k，也就是我們讀取一頁內的數據時候，實際上才發生了一次 IO。

（突然想到個我剛畢業被問過的問題，在 64 位的操作系統中，Java 中的 int 類型占幾個字節？最大是多少？為什么？）

那我們想要優化數據庫查詢，就要盡量減少磁盤的 IO 操作，所以就出現了索引。

索引是什么？

MySQL 官方對索引的定義為：索引 (Index) 是幫助 MySQL 高效獲取數據的數據結構。

MySQL 中常用的索引在物理上分兩類，B- 樹索引和哈希索引。

本次主要講 BTree 索引。

BTree 索引

BTree 又叫多路平衡查找樹，一顆 m 叉的 BTree 特性如下：

樹中每個節點最多包含 m 個孩子。除根節點與葉子節點外，每個節點至少有 [ceil(m/2)] 個孩子（ceil()為向上取整）。若根節點不是葉子節點，則至少有兩個孩子。所有的葉子節點都在同一層。每個非葉子節點由 n 個 key 與 n + 1 個指針組成，其中[ceil(m/2)-1] = n = m-1。

這是一個 3 叉（只是舉例，真實會有很多叉）的 BTree 結構圖，每一個方框塊我們稱之為一個磁盤塊或者叫做一個 block 塊，這是操作系統一次 IO 往內存中讀的內容，一個塊對應四個扇區，紫色代表的是磁盤塊中的數據 key，黃色代表的是數據 data，藍色代表的是指針 p，指向下一個磁盤塊的位置。

來模擬下查找 key 為 29 的 data 的過程：

1、根據根結點指針讀取文件目錄的根磁盤塊 1。【磁盤 IO 操作 1 次】

2、磁盤塊 1 存儲 17，35 和三個指針數據。我們發現 17 29 35，因此我們找到指針 p2。

3、根據 p2 指針，我們定位并讀取磁盤塊 3。【磁盤 IO 操作 2 次】

4、磁盤塊 3 存儲 26，30 和三個指針數據。我們發現 26 29 30，因此我們找到指針 p2。

5、根據 p2 指針，我們定位并讀取磁盤塊 8。【磁盤 IO 操作 3 次】

6、磁盤塊 8 中存儲 28，29。我們找到 29，獲取 29 所對應的數據 data。

由此可見，BTree 索引使每次磁盤 I / O 取到內存的數據都發揮了作用，從而提高了查詢效率。

但是有沒有什么可優化的地方呢？

我們從圖上可以看到，每個節點中不僅包含數據的 key 值，還有 data 值。而每一個頁的存儲空間是有限的，如果 data 數據較大時將會導致每個節點（即一個頁）能存儲的 key 的數量很小，當存儲的數據量很大時同樣會導致 B -Tree 的深度較大，增大查詢時的磁盤 I / O 次數，進而影響查詢效率。

B+Tree 索引

B+Tree 是在 B -Tree 基礎上的一種優化，使其更適合實現外存儲索引結構。在 B +Tree 中，所有數據記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只存儲 key 值信息，這樣可以大大加大每個節點存儲的 key 值數量，降低 B +Tree 的高度。

B+Tree 相對于 B -Tree 有幾點不同：

非葉子節點只存儲鍵值信息，數據記錄都存放在葉子節點中，將上一節中的 B -Tree 優化，由于 B +Tree 的非葉子節點只存儲鍵值信息，所以 B +Tree 的高度可以被壓縮到特別的低。

具體的數據如下：

InnoDB 存儲引擎中頁的大小為 16KB，一般表的主鍵類型為 INT（占用 4 個字節）或 BIGINT（占用 8 個字節），指針類型也一般為 4 或 8 個字節，也就是說一個頁（B+Tree 中的一個節點）中大概存儲 16KB/(8B+8B)=1K 個鍵值（因為是估值，為方便計算，這里的 K 取值為〖10〗^3）。

也就是說一個深度為 3 的 B +Tree 索引可以維護 10^3 * 10^3 * 10^3 = 10 億 條記錄。（這種計算方式存在誤差，而且沒有計算葉子節點，如果計算葉子節點其實是深度為 4 了）

我們只需要進行三次的 IO 操作就可以從 10 億條數據中找到我們想要的數據，比起最開始的百萬數據 9000 秒不知道好了多少個華萊士了。

而且在 B +Tree 上通常有兩個頭指針，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即數據節點）之間是一種鏈式環結構。所以我們除了可以對 B +Tree 進行主鍵的范圍查找和分頁查找，還可以從根節點開始，進行隨機查找。

數據庫中的 B +Tree 索引可以分為聚集索引（clustered index）和輔助索引（secondary index）。

上面的 B +Tree 示例圖在數據庫中的實現即為聚集索引，聚集索引的 B +Tree 中的葉子節點存放的是整張表的行記錄數據，輔助索引與聚集索引的區別在于輔助索引的葉子節點并不包含行記錄的全部數據，而是存儲相應行數據的聚集索引鍵，即主鍵。

當通過輔助索引來查詢數據時，InnoDB 存儲引擎會遍歷輔助索引找到主鍵，然后再通過主鍵在聚集索引中找到完整的行記錄數據。

不過，雖然索引可以加快查詢速度，提高 MySQL 的處理性能，但是過多地使用索引也會造成以下弊端：

創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。除了數據表占數據空間之外，每一個索引還要占一定的物理空間。如果要建立聚簇索引，那么需要的空間就會更大。當對表中的數據進行增加、刪除和修改的時候，索引也要動態地維護，這樣就降低了數據的維護速度。

注意：索引可以在一些情況下加速查詢，但是在某些情況下，會降低效率。

索引只是提高效率的一個因素，因此在建立索引的時候應該遵循以下原則：

在經常需要搜索的列上建立索引，可以加快搜索的速度。在作為主鍵的列上創建索引，強制該列的唯一性，并組織表中數據的排列結構。在經常使用表連接的列上創建索引，這些列主要是一些外鍵，可以加快表連接的速度。在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，所以其指定的范圍是連續的。在經常需要排序的列上創建索引，因為索引已經排序，所以查詢時可以利用索引的排序，加快排序查詢。在經常使用 WHERE 子句的列上創建索引，加快條件的判斷速度。

現在大家知道索引為啥能這么快了吧，其實就是一句話，通過索引的結構最大化的減少數據庫的 IO 次數，畢竟，一次 IO 的時間真的是太久了。。。

總結

就面試而言很多知識其實我們可以很容易就掌握了，但是要以學習為目的，你會發現很多東西我們得深入到計算機基礎上才能發現其中奧秘，很多人問我怎么記住這么多東西，其實學習本身就是一個很無奈的東西，既然我們不能不學那為啥不好好學？去學會享受呢？最近我也在惡補基礎，后面我會開始更新計算機基礎和網絡相關的知識的。

看完了這篇文章，相信你對 MySQL 索引提高查詢效率的原因是什么有了一定的了解，想了解更多相關知識，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！

丸趣 TV 網 – 提供最優質的資源集合！