一步步帶你設計MySQL索引資料結構

JAVA旭陽發表於2022-12-08
MySql索引資料結構

前言

MySQL的索引是一個非常重要的知識點，也基本上是面試必考的一個技術點，所以非常重要。那你瞭解MySQL索引的資料結構是怎麼樣的嗎？為什麼要採用這樣的資料結構？

現在化身為MySQL的架構師，一步步迭代設計出MySQL的索引結構，保證你再也忘記不了索引的結構了，輕鬆透過面試。

索引介紹

MySQL表中儲存的資料量非常大，可能有上億條記錄，如果一條條去匹配，就是所謂的全表掃描，會非常的慢。那麼有什麼辦法呢？

想想我們生活中的例子，比如新華字典，我們有一個目錄，目錄根據拼音排序，內容包含了漢字位於字典中具體的的頁碼。聰明的你肯定也想到了，我們也可以借鑑這種思想，建立一個MySQL的目錄，叫做“索引”。

所以你對“索引”做了抽象和定義：索引（Index）是幫助MySQL高效獲取資料的資料結構。

索引是在儲存引擎中實現的，因此每種儲存引擎的索引不一定完全相同，MySQL有InnoDB、MyISAM、Memory等儲存引擎，你想了下，就拿最常用的InnoDB作為儲存引擎設計索引。

索引設計目標

你現在拼命轉動大腦，開始去思考如何設計出這樣的一個索引結構。你就在腦子裡想，索引設計中需要解決哪些問題，以及要達成什麼樣的目標。

  1. 我要怎麼樣才能在索引目錄（資料結構）中快速找到具體的某條資料記錄呢？那麼這個資料結構需要有順序規律，我按照這個規律就可以定位到具體的某條資料。
  2. MySQL中的資料中的記錄如何能夠快速找到呢？是不是可以將記錄進行排序，然後根據 二分法 快速找到對應的資料記錄。
  3. MySQL中架構老大一開始定義資料是按照資料頁存放的，每個資料頁預設是16kb, 每次滿了，就會重新有新的一頁。我的索引目錄資料應該也是放到頁中，而且索引的資料儘量少些，這樣每頁可以放更多的目錄資訊。
  4. 我怎麼樣才能查詢效率最高呢？其實每次慢都是慢在磁碟IO上，我再後面設計中一定要減少磁碟IO的訪問，越少訪問磁碟IO越好。
  5. 磁碟中的空間還是不連續的啊，那我還得有個指標去連線下一條記錄的位置。

帶著這些問題和思考，你開始設計啦。

索引設計迭代

你想著我就拿一個例子具象化的思考設計索引。

下面是一個新建的表：

CREATE TABLE demo(
	c1 INT,
	c2 INT,
	c3 CHAR(1),
	PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

行記錄的格式簡化如下：

我們只在示意圖裡展示記錄的這幾個部分：

  • record_type：記錄頭資訊的一項屬性，表示記錄的型別， 0 表示普通記錄、 2 表示最小記錄、 3 表示最大記錄、 1 暫時還沒用過，下面講。
  • next_record：記錄頭資訊的一項屬性，表示下一條地址相對於本條記錄的地址偏移量，我們用箭頭來表明下一條記錄是誰。
  • 各個列的值：這裡只記錄在 index_demo 表中的三個列，分別是 c1 、 c2 和 c3 。
  • 其他資訊：除了上述3種資訊以外的所有資訊，包括其他隱藏列的值以及記錄的額外資訊。

把一些記錄放到頁裡的示意圖就是：

注意：一頁可以存放16kb的資料，並不是圖上的3條資料，這裡只是一個示例。

迭代一

我們為什麼要遍歷所有的資料頁或者記錄？因為各個頁中的記錄並沒有規律，不知道這條資料出現在哪個資料頁中。那麼如何快速定位要查詢的資料在哪個資料頁中呢？我們需要建立一定的規律，如下：

  1. 下一個資料頁中使用者記錄的主鍵值必須大於上一個頁中使用者記錄的主鍵值。

  • 頁中的資料根據主鍵按順序排序
  • 不同頁中的資料，下一頁資料大於上一頁資料
  • 新分配的資料頁編號可能並不是連續的。它們只是透過維護者上一個頁和下一個頁的編號而建立了 連結串列 關係
  1. 給所有的頁建立一個目錄項

  • key表示目錄中最小的主鍵值。
  • page_on表示對應的頁碼。

查詢主鍵值為 20 的記錄，具體查詢過程分兩步：

  1. 先從目錄項中根據二分法快速確定出主鍵值為 20 的記錄在 目錄項3 中（因為 12 < 20 < 209 ），它對應的頁是頁9。

  2. 再根據前邊說的在頁中查詢記錄的方式去頁9中定位具體的記錄。

迭代二

迭代一中的目錄項是怎麼儲存的呢？我們是不是也可以用行記錄格式儲存到資料頁中呢。答案是肯定的，我們透過行記錄格式中的record_type等於1表示是目錄記錄，如下圖所示：

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

現在以查詢主鍵為 20 的記錄為例，根據某個主鍵值去查詢記錄的步驟就可以大致拆分成下邊兩步：

  1. 先到儲存目錄項記錄的頁，也就是頁30中透過二分法快速定位到對應目錄項，因為 12 < 20 < 209 ，所以定位到對應的記錄所在的頁就是頁9。

  2. 再到儲存使用者記錄的頁9中根據二分法快速定位到主鍵值為20的使用者記錄。

迭代三

隨著資料量變多，勢必一個目錄項存放不下，因為一頁只有16kb大小，就會分裂出多頁，如下圖所示：

那麼現在查詢主鍵值為 20 的記錄，流程如下：

  1. 我們現在的儲存目錄項記錄的頁有兩個，即頁30和頁32 ，又因為頁30表示的目錄項的主鍵值的 範圍是 [1, 320) ，頁32表示的目錄項的主鍵值不小於 320 ，所以主鍵值為 20 的記錄對應的目錄項記錄在頁30中。

  2. 透過目錄項記錄頁確定使用者記錄真實所在的頁。

  3. 在真實儲存使用者記錄的頁中定位到具體的記錄。

迭代四

如果我們表中的資料非常多則會產生很多儲存目錄項記錄的頁，如果直接這麼查，也是很慢，我們是不是可以針對目錄項記錄的頁再生成一個更高階的目錄，就像是一個多級目錄一樣，如下圖所示：

那麼現在查詢主鍵值為 20 的記錄，流程如下：

  1. 生成了一個儲存更高階目錄項的頁33，這個頁中的兩條記錄分別代表頁30和頁32, 主鍵20的記錄在 [1, 320) 之間，則到頁30中查詢更詳細的目錄項記錄。

  2. 在頁30中透過二分法查詢主鍵為20記錄的使用者記錄頁碼。

  3. 在真實儲存使用者記錄的頁中定位到具體的記錄。

迭代小結

以上這個資料結構就是我們索引最終的資料結構，B+樹， 圖形描述如下：

  • 所有的葉子節點存放全量的使用者記錄資訊，包含所有的欄位。
  • 所有的目錄節點只存放索引欄位、主鍵以及對應的頁碼資訊，要求資訊越少越好，因為一頁最多16kb，只有目錄資訊越少，每頁存放的資訊越多，樹的層級就越小，樹的層級越小，那麼和磁碟的IO就越少，查詢就會越快。一般來說，B+樹4層，就可以存放上億資料了。

索引結構總結

聚簇索引

我們按照前面的迭代推演出了基於主鍵的索引結構，是一顆B+樹，我們把這種索引叫做聚簇索引。

特點：

  • 聚簇索引中的葉子節點存放了使用者記錄的全部資料，它就是innoDB中資料存放的格式，即資料即聚簇索引，聚簇索引即資料，這也是聚簇索引名字的由來吧，資料和索引聚集在一起。
  • InnoDB要求表必須有主鍵。如果沒有顯式指定，則MySQL系統會自動選擇一個可以非空且唯一標識資料記錄的列作為主鍵。如果不存在這種列，則MySQL自動為InnoDB表生成一個隱 含欄位作為主鍵，這個欄位長度為6個位元組，型別為長整型，這樣始終就會有一個聚簇索引。

非聚簇索引

既然有了聚簇索引，那麼肯定有非聚簇索引，非聚簇索引也叫二級索引或者輔助索引。

它是在什麼場景出現的呢？比如我們想以別的列作為搜尋條件，總不能是從頭到尾沿著連結串列依次遍歷記錄一遍，肯定要慢死了。這時候就需要建立非聚簇索引，那它的索引結構和聚簇索引有什麼區別呢？

  • 索引目錄的內容由3部分組成，索引列的值+主鍵值+頁碼，透過索引列的值+主鍵值唯一確定新插入的列是在哪個頁中，也可以唯一確定從那個頁中查詢。
  • 索引的葉子節點存放內容為索引列的值+主鍵值。

那可能你有疑問了，只有主鍵值，我要查記錄的其他資訊怎麼辦呢？

我們根據這個以c2列大小排序的B+樹只能確定我們要查詢記錄的主鍵值，所以如果我們想根 據c2列的值查詢到完整的使用者記錄的話，仍然需要到 聚簇索引 中再查一遍，這個過程稱為 回表 。也就 是根據c2列的值查詢一條完整的使用者記錄需要使用到 2 棵B+樹！

回表的過程會耗時，為什麼不直接存放所有的資料記錄呢？

如果把完整的使用者記錄放到葉子結點是可以不用回表。但是太佔地方了，相當於每建立一課B+樹都需要把所有的使用者記錄再都複製一遍，這就有點太浪費儲存空間了。

聯合索引

聯合索引是一種特殊的非聚簇索引，那麼它的資料結構又是怎麼樣的呢？

比方說我們想讓B+樹按照c2和c3列的大小進行排序，為c2和c3建立的索引的示意圖如下：

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

索引優點和缺點

我們在瞭解了索引的資料結構以後，就更加明白索引的優缺點了。

優點

  1. 提高資料查詢的效率，降低資料庫的IO成本。
  2. 透過建立唯一索引，可以保證資料庫表中每一行資料的唯一性。
  3. 在使用分組和排序子句進行資料查詢時，可以顯著減少查詢中分組和排序的時間，降低了CPU的消耗。

缺點

  1. 建立索引和維護索引要耗費時間，並且隨著資料量的增加，所耗費的時間也會增加。
  2. 索引需要佔磁碟空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間
  3. 降低更新表的速度。當對錶中的資料進行增加、刪除和修改的時候，索引也要動態地維護，這樣就降低了資料的維護速度。
  4. 索引中的資料都是有序的，比如插入一條主鍵較小的資料，勢必導致其他資料進行移動，頁碼發生調整，這種現象也叫做頁分裂，這也是為什麼推薦主鍵要求自增。

總結

本為讓你親身作為一個MySQL架構師的身份，一步步帶你理解MySQL中索引的資料結構，現在是不是理解的很透徹了，如果對你有幫助的話，請留下一個贊吧。

更多學習資料請移步：程式設計師成神之路

相關文章