詳解MySQL的主鍵查詢為什麼這麼快

2022-04-02 13:02:40

1. 初探InnoDB行格式（ROW_FORMAT）

我們平時都是以記錄為單位向MySQL的表中插入資料的，這些記錄在磁碟中的存放的格式就是InnoDB的行格式。

為了證明我不是瞎說，舉個例子，我查詢一下本地資料庫以forward開頭的資料表的行格式

我們平時很少操作行格式，所以對這個概念可能不是很清楚。其實InnoDB儲存引擎為我們提供了4種不同的行格式

DYNAMIC（預設的行格式）
COMPACT
REDUNDANT
COMPRESSED

我們可以在建立表時指定行格式（如果不指定，預設行格式為DYNAMIC），比如我指定row_format_table表的行格式為COMPACT

mysql> CREATE TABLE row_format_table(
    -> id INT,
    -> c1 VARCHAR(10),
    -> c2 CHAR(10),
    -> PRIMARY KEY(id)
    -> ) CHARSET=utf8 ROW_FORMAT=COMPACT;

語法瞭解到這一步就可以了，接下來我們看一下4種行格式的具體表現形式，畫個圖就是

從圖中可以看出，一條完整的記錄可以分為「記錄的額外資訊」和「真實資料資訊」兩部分，4種行格式的不同也主要體現在「真實資料資訊」這一部分。也就是說，不同的行格式採用了不同的資料格式來儲存我們的真實資料，至於有什麼具體的不同，對我們這篇文章並不重要，不需要關注。

next_record表示下一條記錄的相對位置，有了這個欄位，記錄之間可以串聯成一個單連結串列，這個比較好理解，看看圖吧。至於其他的欄位資訊，我們用到的時候再介紹就好了。

注意：圖中所列欄位的排列順序（包括下文即將提及的）並非是InnoDB行格式在儲存裝置上的真實儲存順序，為了方便說明接下來的故事，此處我做了簡化，大家理解思想即可

2. 引入InnoDB頁

對於MySQL的任何儲存引擎而言，資料都是儲存在磁碟中的，儲存引擎要運算元據，必須先把磁碟中的資料載入到記憶體中才可以。

那麼問題來了，一次性從磁碟中載入多少資料到記憶體中合適呢？當獲取記錄時，InnoDB儲存引擎需要一條條地把記錄從磁碟中讀取出來嗎？

當然不行！我們知道磁碟的讀寫速度和記憶體讀寫速度差了幾個數量級，如果我們需要讀取的資料恰好執行在磁碟的不同位置，那就意味著會產生多次I/O操作。

因此，無論是作業系統也好，MySQL儲存引擎也罷，都有一個預讀取的概念。概念的依據便是統治計算機界的區域性性原理。

空間區域性性：如果當前資料是正在被使用的，那麼與該資料空間地址臨近的其他資料在未來有更大的可能性被使用到，因此可以優先載入到暫存器或主記憶體中提高效率

就是當磁碟上的一塊資料被讀取的時候，我們乾脆多讀一點，而不是用多少讀多少。

InnoDB儲存引擎將資料劃分為若干個頁，以頁作為磁碟和記憶體之間互動的最小單位。InnoDB中頁的大小預設為16KB。也就是預設情況下，一次最少從磁碟中讀取16KB的資料到記憶體中，一次最少把記憶體中16KB的內容重新整理到磁碟上。

對於InnoDB儲存引擎而言，所有的資料（儲存使用者資料的索引、各種後設資料、系統資料）都是以頁的形式進行儲存的。

InnoDB頁的種類很多，比如存放Insert Buffer資訊的頁，存放undo紀錄檔資訊的頁等，不過我們今天不關注其他亂七八糟的頁。這篇文章的主角是存放我們表中記錄的頁，姑且稱之為資料頁吧。

3. 資料頁的結構

很顯然，資料頁也會有自己的格式表示，像行格式一樣，我先列出兩個我們用到的欄位，其他的用到再說吧。

3.1 使用者記錄是如何存放的

我們實際儲存的資料表記錄會按照指定的行格式儲存到圖中的User Records部分，如果當前的資料頁是新生成的，還沒有任何記錄的話，User Records部分其實並不會存在，而是從Free Space部分申請一塊空間劃分到User Records部分，當Free Space空間全部用完（或者剩餘的空間已經不足以承載新資料）的時候，意味著當前資料頁的空間被佔滿了，如果繼續插入記錄，就需要申請新的資料頁了，示意圖如下：

要注意的是，上圖中的各條記錄之間通過next_record欄位串聯成了一個單連結串列，只不過我沒有在圖中畫出來罷了。

但是，只是串聯起來就可以了嗎？

如果讓我們來設計串聯的規則的話，我們肯定希望能夠按照某種“大小關係”來確定串聯的順序，而不是單純按照插入資料的順序，畢竟我們是學過資料結構的人啊！

可是記錄之間能比較大小嗎？能啊，這篇文章的題目就是關於主鍵啊，我們可以按照主鍵的順序，從小到大來串聯當前資料頁中的所有記錄。事實上，MySQL的設計者也確實是這麼設計的。

如果你足夠叛逆，你可能會想，你不設定主鍵的話是不是MySQL就崩了啊？

當我們沒有設定主鍵的時候，為了防止這種情況，InnoDB會優先選取一個Unique鍵作為主鍵，如果表中連Unique鍵也沒有的話，就會自動為每一條記錄新增一個叫做DB_ROW_ID的列作為預設主鍵，只不過這個主鍵我們看不到罷了。

下面我們補充一下行格式

再次強調

我畫的欄位的順序並非在儲存裝置中實際儲存的順序
只有在InnoDB實在無法確定主鍵的情況下（建立時不指定主鍵，同時沒有Unique鍵），才會新增DB_ROW_ID列

3.2 番外：為什麼推薦使用自增ID作為主鍵，而不推薦使用UUID？

說到這，順便談一談為什麼推薦使用自增ID作為主鍵，而不推薦使用UUID？

除了UUID主鍵索引佔據大量空間的問題之外，在插入資料的資源開銷上，自增ID也遠小於UUID。由於資料頁中的記錄是按照主鍵從小到大進行串聯的，自增ID決定了後來插入的記錄一定會排列在上一條記錄的後面，只需要簡單新增next_record指標就可以了；如果當前資料頁寫滿，那就放心地直接插入新的資料頁中就可以了。

而UUID不同，它的大小順序是不確定的，後來插入的記錄有可能（而且概率相當大）插入到上一條記錄之前（甚至是當前資料頁之前），這就意味著需要遍歷當前資料頁的記錄（或者先找到相關的資料頁），然後找到自己的位置進行插入；如果當前資料頁寫滿了，只能先找到適合自己位置的資料頁，然後在資料頁中遍歷記錄找到自己的合適位置進行插入。

因此使用UUID的方式插入記錄花費的時間更長。

3.3 資料頁自帶的兩條偽記錄

實際上，InnoDB的設計者在InnoDB頁中新增了兩條偽記錄，一條Infimum，一條Supremum。並且設計者規定，當前資料頁的任何使用者記錄都比Infimum大，任何使用者記錄都比Supremum小。

因為是偽記錄，所以需要和User Records中的內容區分開，所以把這兩條偽記錄放在了一個叫做Infimum+Supremum的部分，見下圖：

最終在資料頁中，使用者記錄的儲存形式就成了這個樣子：

上圖中我把真實資料資訊中的主鍵id值畫了出來，方便我們後續進行解釋。

你可能不太理解InnoDB設計者為什麼要無緣無故新增這兩個欄位，這倆貨對我們的搜尋工作看起來沒有任何好處。

沒錯，這倆貨不是方便我們在資料頁中檢索資料而新增的，他們發揮作用的戰場是MySQL的LOCK_GAP記錄鎖。啥？不懂？沒事兒，我就是提一嘴而已，對這篇文章沒啥用，具體以後再說。。。

3.4 資料頁中主鍵的高效查詢方案

到目前為止，我們已經知道了在一個資料頁中，使用者記錄是按照主鍵由小到大的順序串聯而成的單向連結串列。接下來我們要解決的就是如何在一個資料頁中根據主鍵值搜尋資料了。

如果我們執行下面這條查詢語句

SELECT * FROM row_format_table WHERE id = 4;

最簡單的辦法就是遍歷當前頁面的所有記錄，從Infimum記錄開始沿著單向連結串列進行搜尋，直到找到id為4的記錄為止。記錄數量少的時候還好說，這要是有成千上萬條，那誰能受的了。

所以InnoDb設計者想出了一種絕妙的搜尋方法，把資料頁中的所有記錄（包括偽記錄）分成若干個小組，每個小組選出組內最大的一條記錄作為“小組長”，接著把所有小組長的地址拿出來，編成目錄。

這就好比我們去學校找人，我們只知道他是幾年級的（確定資料頁），然後再問問每個班主任有沒有這個人（資料頁中的小組），而不是上來就直接遍歷整個年級的所有人。

為了使這種方案最大程度上發揮它的檢索效率（不能隨便分組，畢竟一個資料頁分成一個組或者每條記錄獨佔一個分組跟遍歷也沒什麼區別），所以InnoDB的設計者規定了如下分組方案：

Infimum偽記錄單獨分成一個組
Supremum偽記錄所在分組的記錄條數只能在1～8條之間
其餘分組的記錄條數只能在4～8條之間

規則是這樣，可是InnoDB怎麼確定每個組內有多少個組員呢？設計者又想了一個辦法，給“小組長”新增一個屬性，記錄這個組內一共有多少個組員（包括自己）。所以我們再擴充一下行格式：

小組長的n_owned值是組員的個數（包括自己），組員的n_owned值就是0。

接下來我們向表中多新增幾條資料，看看分組到底是什麼回事兒？需要注意的是，由於我們已經在表中指定了主鍵id，因此DB_ROW_ID這個引數不會再畫出來了。

上圖中的所有記錄（包括偽記錄）分成了4個小組，每個小組的“組長”被單獨提拔，單獨編製成“目錄”，InnoDB官方稱之為「槽」。槽在物理空間中是連續的，意味著通過一個槽可以很輕鬆地找到它的上一個和下一個，這一點非常重要。

槽的編號從0開始，我們查詢資料的時候先找到對應的槽，然後再到小組中進行遍歷即可，因為一個小組內的記錄數量並不多，遍歷的效能損耗可以忽略。而且每個槽代表的“組長”的主鍵值也是從小到大進行排列的，所以我們可以用二分法進行槽的快速查詢。

圖中包含4個槽，分別是0、1、2、3，二分法查詢之前，最低的槽low=0，最高的槽high=3。現在我們再來看看在這個資料頁中，我們查詢id為7的記錄，過程是怎樣的。

使用二分法，計算中間槽的位置，(0+3)/2=1，檢視槽1對應的“組長”的主鍵值為4，因為4<7，所以設定low=1，high保持不變；
再次使用二分法，計算中間槽的位置，(1+3)/2=2，檢視槽2對應的“組長”的主鍵值為8，因為8>7，所以設定high=2，low保持不變；
現在high=2，low=1，兩者相差1，已經沒有必要繼續進行二分了，可以確定我們的記錄就在槽2中，並且我們也能知道槽2對應的“組長”的主鍵是8，但是記錄之間是單向連結串列，我們無法向前遍歷。上文提到過，我們可以通過槽2找到槽1，進而找到它的“組長”，然後沿著“組長”向下遍歷直到找到主鍵為7的記錄就可以了。