說說MySQL索引相關

你的益達_發表於2019-04-15
MySql索引

前言

關於索引，這是一個非常重要的知識點，同樣，在面試的時候也會被經常的問到；

本文描述了索引的結構，介紹了InnoDB的索引方案等知識點，感興趣的可以看一下；

引入

本文參考文章：MySQL的索引

回顧

在上篇文章中我們說到 InnoDB的資料頁結構 ,瞭解到了InnoDB資料頁的 7 個組成部分，知道了各個資料頁可以組成一個雙向連結串列，而每個資料頁中的記錄又可以組成一個單向連結串列 （按照大小排序），每個資料頁都會為儲存在它裡邊兒的記錄生成一個頁目錄，在通過主鍵查詢某條記錄的時候可以在頁目錄中使用二分法快速定位到對應的槽，然後再遍歷該槽對應分組中的記錄即可快速找到指定的記錄。也瞭解到了在頁中各個部分的作用是啥，如果沒看的，建議回去看一下。

附上地址： InnoDB的資料頁結構

索引

起步

首先，我們先來了解一下如果沒有索引的話，當我們查詢一條記錄的時候是怎樣進行的，當然，我們就說精準匹配的時候，先附上一句SQL語句：

SELECT column FROM table WHERE column = xxx;
複製程式碼

上面這個型別的語句是我們常用的，也比較簡單，下面我們來看一下：

在一頁中查詢

假設這個表中的資料量比較小，只有一頁的資料，這個時候的查詢分為以下情況：

  • 當條件為主鍵
    • 這個過程我們在上篇文章已經說過了，通過頁結構中的Page Directory ，通過二分法快速定位到對應的槽，然後再遍歷該槽對應分組中的記錄即可快速找到指定的記錄。
  • 當條件是其它列
    • 當條件是其它列不是主鍵的時候，資料頁中是沒有對應非主鍵的而建立頁目錄的，所以無法像主鍵那樣通過二分查詢定位，只能通過最笨的方法，直接遍歷整個資料頁一條一條的進行匹配。當然，這種方法的效率就不說了。

在多頁中查詢

上面的情況是一種假設，但真實的情況還是需要現在整個居多，一個表中的記錄一般都是有很多的資料頁組成的，同時，在多個資料頁中的查詢方式是這樣的：

  • 首先需要找到該記錄對應的頁
    • 上面假設的是隻有一頁的資料，所以我們根據對應主鍵的而建立的頁目錄進行查詢。但是現在沒有針對頁的頁目錄，所以我們不能快速的定位到記錄所在的頁，就只能從第一頁開始進行遍歷進行慢慢從查詢，這樣一說可能您看著就頭皮發麻了，要是很多記錄怎麼辦？那得等到什麼時候？
  • 從頁中查詢對應的記錄。
    • 這個過程我們就不再說了

準備

我們先準備一個表：

mysql> CREATE TABLE index_demo(
    ->     c1 INT,
    ->     c2 INT,
    ->     c3 CHAR(1),
    ->     PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.03 sec)
mysql>
複製程式碼

這個表使用Compact行格式來實際儲存記錄的。為了我們理解上的方便，我們簡化了一下index_demo表的行格式示意圖：

說說MySQL索引相關

先介紹一下上面幾個部分代表的含義：

  • record_type : 記錄頭資訊的一項屬性，表示記錄的型別，0表示普通記錄、2表示最小記錄、3表示最大記錄、1我們還沒用過，等會再說～
  • next_record : 記錄頭資訊的一項屬性，表示下一條地址的偏移量，為了方便大家理解，我們都會用箭頭來表明下一條記錄是誰。
  • 資料列 ：就是各個資料列的值，其中我們用橘黃色的格子代表c1列，深藍色的格子代表c2列，紅色格子代表c3列。
  • 其它資訊 ：除了上述 3 種資訊以外的所有資訊，包括其他隱藏列的值以及記錄的額外資訊。

但放入一些記錄之後的在頁的圖如下：

說說MySQL索引相關

一個簡單的索引方案

剛才說了，為什麼找記錄對應的頁的時候需要依次遍歷查詢呢？因為沒有對應頁的目錄，沒有的話怎麼辦呢？建一個不就行了？我們來看看。

我們知道一頁中的記錄是按照大小進行依次連結的單向連結串列，所以，我們使用頁建立目錄也需要遵守同樣的規則，所以我們首先需要保障第二頁的記錄的主鍵值是大於第一頁的。所以就有了一個前提了。

  • 下一個資料頁的主鍵值必須大於上一個頁中的主鍵值。

為了下面我們更好的說明，我們先做一個假設：

假設我們的每個資料頁最多能存放 3 條記錄（實際不是），有了這個假設之後我們向index_demo表插入 3 條記錄：

mysql> INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql>
複製程式碼

那麼頁中的圖如下：

說說MySQL索引相關

按主鍵進行大小排序的單向連結串列；

上面我們做了假設，一個頁中最多隻能放三條記錄，這個時候我們再插入一條資料：

mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
Query OK, 1 row affected (0.00 sec)

mysql>
複製程式碼

這個時候應該重新再分配一個頁：

說說MySQL索引相關

咦？怎麼分配的頁號是28呀，不應該是11麼？需要注意的一點是，新分配的資料頁編號可能並不是連續的，也就是說我們使用的這些頁在儲存空間裡可能並不挨著。

上面我們也說了，要先建立目錄需要遵守規則，上面圖中的頁明顯沒有，所以需要進行移動，過程如下:

說說MySQL索引相關

這個過程表明了在對頁中的記錄進行增刪改操作的過程中，我們必須通過一些諸如記錄移動的操作來始終保證這個狀態一直成立：下一個資料頁的主鍵值必須大於上一個頁中的主鍵值。

  • 目錄建立

上面說到條件已經滿足了，下面需要進行目錄的建立了。

我們接著往表中插入資料，得到以下的結構：

說說MySQL索引相關

注：資料頁的編號可能並不是連續的

現在我們來針對每一個頁來建立目錄項，每個目錄項包含以下兩個部分：

  • 頁目錄中的一部分是儲存的該頁中最小的主鍵值，我們用key來表示；
  • 頁目錄中的另一部分是頁號，我們用page_no表示；

見下圖：

說說MySQL索引相關

看到這樣的圖了，大家再來想想，我們只需要把幾個目錄項在物理儲存器上連續儲存，比如把他們放到一個陣列裡，就可以實現根據主鍵值快速查詢某條記錄的功能了。比方說我們想找主鍵值為20的記錄，我們來看一下查詢記錄的過程：

  • 先從目錄項中根據二分法快速確定出主鍵值為20的記錄在目錄項3中（因為 12 < 20 < 209），它對應的頁是頁9。
  • 再根據前邊說的在頁中查詢記錄的方式去頁9中定位具體的記錄。

針對資料頁做的簡易目錄就搞定了，怎麼樣？這樣是不是好多了，可能大家也知道了，沒錯，這個目錄也被我們稱作 索引 。

InnoDB的索引方案

上面的方案是一個簡單的索引方案，因為我們假設所有目錄項都可以在物理儲存器上連續儲存，這樣的方案存在幾個問題：

  • InnoDB是使用頁作為管理儲存空間的基本單位，最多隻能保證16KB的連續儲存空間，當表中的記錄慢慢增多的時候就需要非常大的連續的儲存空間才能把所有的目錄放下。
  • 再從我們常做的操作來分析，當我們對錶中的記錄進行增刪的時候，假設我們對以上頁28中的記錄進行刪除，那麼頁28頁就沒有存在的必要了，同時目錄2也一樣沒有存在的必要，這個時候就需要把目錄2後面的目錄項往前移動，影響這麼大可不是什麼好辦法。

忠於以上的情況，我們需要有更好的方式。

設計InnoDB的大叔們需要一種可以靈活管理所有目錄項的方式。他們靈光乍現，忽然發現這些目錄項其實長得跟我們的使用者記錄差不多，只不過目錄項中的兩個列是主鍵和頁號而已，所以他們複用了之前儲存使用者記錄的資料頁來儲存目錄項，為了和使用者記錄做一下區分，我們把這些用來表示目錄項的記錄稱為目錄項記錄。那InnoDB怎麼區分一條記錄是普通的使用者記錄還是目錄項記錄呢？別忘了記錄頭資訊裡的record_type屬性，它的各個取值代表的意思如下：

  • 0：普通的使用者記錄
  • 1：目錄項記錄
  • 2：最小記錄
  • 3：最大記錄

原來這個值為1的record_type是這個意思呀，我們把前邊使用到的目錄項放到資料頁中的樣子就是這樣：

說說MySQL索引相關

我們來說一下目錄項記錄和使用者記錄的區別：

  1. 目錄項記錄的record_type值是 1，而普通使用者記錄的record_type值是 0。
  2. 目錄項記錄只有主鍵值和頁的編號兩個列，而普通的使用者記錄的列是使用者自己定義的，可能包含很多列，另外還有InnoDB自己新增的隱藏列。

除了上述幾點外，這兩者就沒啥差別了，它們用的是一樣的資料頁，頁的組成結構也是一樣一樣的（就是我們前邊介紹過的 7 個部分），都會為主鍵值生成Page Directory（頁目錄）以加快在頁內的查詢速度。

所以現在根據某個主鍵值去查詢記錄的步驟可以大致拆分成下邊兩步，以查詢主鍵為20的記錄為例（因為都是從一個頁中通過主鍵查某條記錄，所以都可以使用Page Directory通過二分法而實現快速查詢）：

  • 先到儲存目錄項記錄的頁中通過二分法快速定位到對應目錄項，因為12 < 20 < 209，所以定位到對應的記錄所在的頁就是頁9 。
  • 從頁9中根據二分法快速定位到主鍵值為20的使用者記錄（這個過程不再多說）。

雖然說目錄項記錄中只是儲存主鍵值和對應的頁號，由於一個頁中只有16KB的大小，能存放的目錄項記錄也是有限的，所以當一個頁儲存目錄項滿了之後再有的話就需要再來一個儲存目錄項記錄的頁。

為了大家更好的理解如何新分配一個目錄項記錄頁的過程，我們假設一個儲存目錄項記錄的頁最多隻能存放 4 條目錄項記錄（請注意是假設哦，真實情況下可以存放好多條的），所以如果此時我們再向上圖中插入一條主鍵值為320的使用者記錄的話，那就需要一個分配一個新的儲存目錄項記錄的頁嘍：

說說MySQL索引相關

以上圖中，由於我們新增了一條記錄，所以得到了一個新的資料頁，裡面存放的是資料記錄，又因為頁30的頁目錄記錄儲存滿了（上面做了假設，假設每頁最多隻能儲存4條），所以有了頁32來存放頁31對應的目錄項。

因為儲存目錄項記錄的頁不止一個，所以如果我們想根據主鍵值查詢一條使用者記錄大致需要 3 個步驟：

  1. 確定目錄項記錄頁；
    1. 我們現在的儲存目錄項記錄的頁有兩個，即頁30和頁32，又因為頁30表示的目錄項的主鍵值的範圍是[1, 320)，頁32表示的目錄項的主鍵值不小於320，所以主鍵值為20的記錄對應的目錄項記錄在頁30中。
  2. 通過目錄項記錄頁確定使用者記錄真實所在的頁；
    1. 在一個儲存目錄項記錄中定位一條目錄項記錄的方式說過了（通過二分查詢進行定位，找到對應的頁）。
  3. 在真實儲存使用者記錄的頁中定位到具體的記錄；
    1. 不多說了。

那麼問題來了，在這個查詢步驟的第 1 步中我們需要定位儲存目錄項記錄的頁，但是這些頁在儲存空間中也可能不挨著，如果我們表中的資料非常多則會產生很多儲存目錄項記錄的頁，那我們怎麼根據主鍵值快速定位一個儲存目錄項記錄的頁呢？

其實也簡單，為這些儲存目錄項記錄的頁再生成一個更高階的目錄，就像是一個多級目錄一樣，大目錄裡巢狀小目錄，小目錄裡才是實際的資料，所以現在各個頁的示意圖就是這樣子：

說說MySQL索引相關

如圖，我們生成了一個儲存更高階目錄項的頁33，這個頁中的兩條記錄分別代表頁30和頁32，如果使用者記錄的主鍵值在[1, 320)之間，則到頁30中查詢更詳細的目錄項記錄，如果主鍵值不小於320的話，就到頁32中查詢更詳細的目錄項記錄。

隨著表中記錄的增加，這個目錄的層級會繼續增加，如果簡化一下，那麼我們可以用下邊這個圖來描述它：

說說MySQL索引相關

其實這是一種組織資料的形式，或者說是一種資料結構，它的名稱是B+樹。

因為我們把資料頁都存放到B+樹這個資料結構中了，所以我們也把我們的資料頁稱為節點。從圖中可以看出來，我們的實際使用者記錄其實都存放在 B + 樹的最底層的節點上，這些節點也被稱為葉子節點或葉節點，其餘的節點都是用來存放目錄項的，這些節點統統被稱為內節點或者說非葉節點。其中最上邊的那個節點也稱為根節點。

從圖中可以看出來，一個B+樹的節點其實可以分成好多層，設計InnoDB的大叔們為了討論方便，規定最下邊的那層，也就是存放我們使用者記錄的那層為第0層，之後依次往上加。上邊我們做了一個非常極端的假設，存放使用者記錄的頁最多存放 3 條記錄，存放目錄項記錄的頁最多存放 4 條記錄，其實真實環境中一個頁存放的記錄數量是非常大的，假設，假設，假設所有的資料頁，包括儲存真實使用者記錄和目錄項記錄的頁，都可以存放1000條記錄，那麼：

  • 如果B+樹只有 1 層，也就是隻有 1 個用於存放使用者記錄的節點，最多能存放1000條記錄。
  • 如果B+樹有 2 層，最多能存放1000×1000=1000000條記錄。
  • 如果B+樹有 3 層，最多能存放1000×1000×1000=1000000000條記錄。
  • 如果B+樹有 4 層，最多能存放1000×1000×1000×1000=1000000000000條記錄。

你的表裡能存放1000000000000條記錄麼？所以一般情況下，我們用到的B+樹都不會超過 4 層，那我們通過主鍵去查詢某條記錄最多隻需要做 4 個頁面內的查詢，又因為在每個頁面內有所謂的Page Directory（頁目錄），所以在頁面內也可以通過二分法實現快速定位記錄。

聚簇索引

上面所說的B+樹，我們知道了B+樹本身就是一個目錄，或者說它本身就是一個索引，它有以下特點：

  • 使用記錄主鍵值的大小進行記錄和頁的排序，這包括三個方面的含義：
    • 頁內的記錄是按照主鍵的大小順序排成一個單向連結串列；
    • 各個存放使用者記錄的頁也是根據頁中記錄的主鍵大小順序排成一個雙向連結串列；
    • 各個存放目錄項的頁也是根據頁中記錄最小值的主鍵大小順序排成一個雙向連結串列；
  • B+樹的葉子節點儲存的是完整的使用者記錄。
    • 所謂完整的使用者記錄，就是指這個記錄中儲存了所有列的值。

我們把具有這兩種特性的B+樹稱為聚簇索引，所有完整的使用者記錄都存放在這個聚簇索引的葉子節點處；

換句話說主鍵索引就是聚簇索引；

聚簇索引並不需要我們在MySQL語句中顯式的去建立，InnoDB儲存引擎會自動的為我們建立聚簇索引。另外有趣的一點是，在InnoDB儲存引擎中，聚簇索引就是資料的儲存方式（所有的使用者記錄都儲存在了葉子節點），也就是所謂的索引即資料。

二級索引

上面也說到了聚簇索引是針對主鍵值時才能發揮作用，那麼當索引為其它列的時候，又是怎樣的呢？難道只能從頭到尾沿著連結串列依次遍歷記錄麼？

不，我們可以多建幾棵B+樹，不同的B+樹中的資料採用不同的排序規則。比方說我們用c2列的大小作為資料頁、頁中記錄的排序規則，再建一棵B+樹，效果如下圖所示：

說說MySQL索引相關

這個B+樹與上邊介紹的聚簇索引有幾處不同：

  • 使用記錄c2列的大小進行記錄和頁排序
    • 頁內是按照c2列的大小進行排序形成的一個單向連結串列。
    • 各個存放使用者記錄的頁也是根據頁中記錄的c2列大小順序排成的一個雙向連結串列。
    • 各個存放目錄項的頁根據頁中記錄的c2列的大小排成的雙向連結串列。
  • B+樹的葉子節點並不是完整的使用者記錄，而是c2列+主鍵這兩個列的值
  • 目錄項記錄不再是主鍵+頁號，而是c2列+頁號

所以如果我們現在想通過c2列的值查詢某些記錄的話就可以使用我們剛剛建好的這個B+樹了，以查詢c2列的值為4的記錄為例，查詢過程如下：

  • 確定目錄項記錄頁
    • 根據根頁面，也就是頁44，可以快速定位到目錄項記錄所在的頁為頁42（因為2 < 4 < 9）。
  • 通過目錄項記錄頁確定使用者記錄真實所在的頁。
    • 在頁42中可以快速定位到實際儲存使用者記錄的頁，但是由於c2列並沒有唯一性約束，所以c2列值為4的記錄可能分佈在多個資料頁中，又因為2 < 4 ≤ 4，所以確定實際儲存使用者記錄的頁在頁34和頁35中。
  • 在真實儲存使用者記錄的頁中定位到具體的記錄。
    • 到頁34和頁35中定位到具體的記錄。
  • 但是這個B+樹的葉子節點中的記錄只儲存了c2和c1（也就是主鍵）兩個列，所以我們必須再根據主鍵值去聚簇索引中再查詢一遍完整的使用者記錄。

大家可能頁看到了，當最後定位到對應記錄的時候，得到的是一個主鍵，而得到主鍵後仍然需要到聚簇索引中再查一遍，這個過程也被稱為回表 。也就是根據c2列的值查詢一條完整的使用者記錄需要使用到2 棵 B+ 樹！！！

可能您會想，為什麼需要回表呢？直接查出來不行嗎？

當然可以，但是您想想，一個表中，每當我們建立一個索引就需要把記錄拷貝一份到B+樹，是不是太浪費儲存空間了。因為這種按照非主鍵列建立的B+樹需要一次回表操作才可以定位到完整的使用者記錄，所以這種B+樹也被稱為二級索引 或者輔助索引 。

聯合索引

我們有時候也會使用多個列做聯合索引，也就是同時為多個列建立索引，比方說我們想讓B+樹按照c2和c3列的大小進行排序，這個包含兩層：

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

為c2和c3列建立的索引的示意圖如下：

說說MySQL索引相關

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

以 c2 和 c3 列的大小為排序規則建立的B+樹稱為聯合索引，它的意思與分別為 c2 和 c3 列建立索引的表述是不同的，不同點如下：

  • 建立聯合索引只會建立如上圖一樣的 1 棵B+樹。
  • 為 c2 和 c3 列建立索引會分別以c2和c3列的大小為排序規則建立 2 棵B+樹。

總結

  • 對於InnoDB儲存引擎來說，在單個頁中查詢某條記錄分為兩種情況：
    1. 以主鍵為搜尋條件，可以使用Page Directory通過二分法快速定位相應的使用者記錄。
    2. 以其他列為搜尋條件，需要按照記錄組成的單連結串列依次遍歷各條記錄。
  • 沒有索引的情況下，不論是以主鍵還是其他列作為搜尋條件，只能沿著頁的雙連結串列從左到右依次遍歷各個頁。
  • InnoDB儲存引擎的索引是一棵B+樹，完整的使用者記錄都儲存在B+樹第0層的葉子節點，其他層次的節點都屬於內節點，內節點裡儲存的是目錄項記錄。InnoDB的索引分為兩大種：
    • 聚簇索引
      • 以主鍵值的大小為頁和記錄的排序規則，在葉子節點處儲存的記錄包含了表中所有的列(索引既資料)。
    • 二級索引
      • 以自定義的列的大小為頁和記錄的排序規則，在葉子節點處儲存的記錄內容是列 + 主鍵 ，所以每次查詢的資料都會先得到主鍵，而得到主鍵後仍然需要到聚簇索引中再查一遍，這個過程也被稱為回表 。也就是根據c2列的值查詢一條完整的使用者記錄需要使用到2 棵 B+ 樹！！！

最後

最後說一下，本文的參考文章： MySQL的索引 。

本文的很多內容也是來自這篇文章，本人只在文章中插入了有關自己對於文章的理解，如果說的不對，還望指教。

大家也可以去看一下原文。

相關文章