【Mysql】InnoDB 中的聚簇索引、二級索引、聯合索引

接上一篇內容。

一、聚簇索引

其實之前內容中介紹的 B+ 樹就是聚簇索引。

這種索引不需要我們顯示地使用 INDEX 語句去創建，InnoDB 引擎會自動創建。另外，在 InnoDB 引擎中，聚簇索引就是數據的存儲方式。

它有 2 個特點：

特點 1

使用記錄主鍵值的大小進行記錄和頁的排序。

其中又包含了下面 3 個點：

頁（包括葉節點和內節點）內的記錄按照主鍵的大小順序排成一個單向鏈表。頁內記錄劃分為若干組，每個組中主鍵值最大的記錄在頁內的偏移量被當做槽依次存放在頁目錄中。我們可以通過二分法快速定位主鍵值等於某個值的記錄。
各存放用戶記錄的頁也是根據頁中用戶記錄的主鍵大小順序排成一個雙向鏈表。
各存放目錄項記錄的頁分為不同層級。在同一層級中的頁，也是根據頁中目錄項記錄的主鍵大小順序排成一個雙向鏈表。

特點 2

B+樹的葉子節點存儲的是完整的用戶記錄。

這裡完整的用戶記錄就是指，這個記錄中存儲了所有的列的值（包括隱藏列）。

二、二級索引

聚簇索引只能在我們搜索主鍵值時才能發揮作用，因為 B+ 樹中的數據都是按照主鍵進行排序。

如果現在我用「別的列」作為搜索條件，怎麼辦？

答案：再建一個 B+ 樹，用這個「別的列」（非主鍵列）的值大小作為排序規則。

比如之前的內容都是以 c1 列為主鍵，現在用 c2 列再來創建一個 B+ 樹：

看起來跟之前的聚簇索引沒啥區別啊？實際上還是存在不同的：

使用記錄 c2 列的大小進行記錄和頁的排序。細分的 3 點與上面聚簇索引介紹的一樣，只不過上面是主鍵，這裡是用的 c2 列（非主鍵）。
B+ 樹的葉子節點存儲的不是完整的用戶記錄，只有c2 列 + 主鍵這2個列的值。
目錄項記錄中不再是主鍵 + 頁號，變成了c2 列 + 頁號。

另外需要注意的是，因為 c2 列不是主鍵，所以沒有唯一性約束，可能存在多條滿足搜索條件的數據。

現在根據條件 c2 = 4 來查找數據記錄，過程如下：

確定第一條符合 c2 = 4 的目錄項所在頁，也就是頁 42。
到頁 42 中，進一步確定第一條符合條件的記錄所在的用戶記錄頁。因為 2 < 4 <= 4，所以可能存在頁 34 或 35 中。
先到頁 34 中定位第一條滿足 c2 = 4 的用戶記錄，如果有就不需要再到頁 35 中繼續定位了。
在頁 34 中定位到第一條記錄。因為這條用戶記錄不完整，所以拿到這條記錄的主鍵，再到聚簇索引中找到完整的用戶記錄。

上面最後一步，通過攜帶主鍵資訊到聚簇索引中重新定位完整的用戶記錄的過程也叫回表。

回表後，再回到這顆新的 B+ 樹，找到剛才那個第一個符合條件的記錄，並沿著記錄的單向鏈表向後繼續搜索其他也滿足 c2 = 4 的記錄，每找到一條就繼續回表操作，重複這個過程。

這種以非主鍵列的大小為排序規則而建立 B+ 樹需要執行回表操作才可以定位到完整的用戶記錄，這種 B+樹就稱為二級索引或者輔助索引。

為什麼要回表？直接把完整用戶記錄都放葉子節點不就可以了？

沒錯，思路沒問題。但是這樣操作就相當於每建立一顆 B+ 樹都把所有的用戶記錄複製一遍，太浪費存儲空間。

三、聯合索引

我們可以同時為多個列建立索引，比如 c2 列和 c3 列，以這 2 個列的大小為排序規則建立的 B+ 樹索引就稱為聯合索引，也稱為符合索引或多列索引。

這裡的按照 c2 和 c3 列大小進行排序，需要注意兩點：

先把各個記錄和頁按照 c2 列進行排序。
在記錄的 c2 列都相同的情況下，再採用 c3 列進行排序。

現在，給c2 和 c3 建立聯合索引，如圖所示：

需要注意的是：

每條目錄項記錄都是由 c2、c3、頁號這 3 部分組成。各記錄先按照 c2 列的值進行排序，如果記錄的 c2 列相同，則按照 c3 列進行排序。
B+ 樹葉子節點的用戶記錄由 c2、c3、和主鍵c1 列組成。

本質上，聯合索引也是一個二級索引，只不過它的索引列包括 c2、c3 這2個列。

本文參考書籍：
小孩子4919 《mysql是怎樣運行的》

Tags: 把蘋果咬哭的不規律日常資料庫