《MySQL 基礎篇》十二：InnoDB 儲存引擎的資料結構

Author: ACatSmiling

Since: 2024-09-21

資料庫的儲存結構：頁

索引結構提供了高效的索引方式，不過索引資訊以及資料記錄都是儲存在檔案上的，確切說時儲存在頁結構中。另一方面，索引實在儲存引擎中實現的，MySQL 伺服器上的儲存引擎負責對錶中資料的讀取和寫入工作。不同儲存引擎中資料存放的格式一般是不同的，甚至有的儲存引擎都不用磁碟來儲存資料，比如 Memory。

磁碟與記憶體互動的基本單位：頁

InnoDB 儲存引擎將資料劃分為若干個頁，InnoDB 中頁的大小預設為 16 KB，一個頁中可以儲存多個行記錄。

InnoDB 以頁（Page）作為磁碟和記憶體互動的基本單位，一次最少從磁碟中讀取 16 KB 的內容到記憶體中，同時，一次也是最少把記憶體中的 16 KB 內容重新整理到磁碟中。即：在 MySQL 資料庫中，管理儲存空間的基本單位是頁，資料庫 I/O 操作的最小單位是頁。不論是讀取一行資料，還是讀取多行資料，都是將這些行所在的頁進行載入。

記錄是按照行來儲存的，但是資料庫的讀取並不以行為單位，否則一次讀取（也就是一次 I/O 操作）只能處理一行資料，效率會非常低。

頁結構概述

頁 a，頁 b，頁 c，一直到頁 n，這些頁可以不在物理結構上相連，只要透過雙向連結串列相關聯即可。每個資料頁中的記錄會按照主鍵值從小到大的順序組成一個單向連結串列，每個資料頁都會為儲存在它裡邊的記錄生成一個頁目錄，在透過主鍵查詢某條記錄的時候，可以在頁目錄中使用二分法快速定位到對應的槽，然後再遍歷該槽對應分組中的記錄，即可快速找到指定的記錄。

頁的大小

不同的資料庫管理系統（DBMS）的頁大小不同，MySQL 的 InnoDB 儲存引擎，預設頁的大小是 16 KB，可以透過命令檢視：

mysql> SHOW VARIABLES LIKE '%innodb_page_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

SQL Server 中頁的大小為 8 KB，Oracle 中用術語 "塊" （Block）代表頁，支援的塊大小為 2 KB，4 KB，8 KB，16 KB，32 KB 和 64 KB。

頁的上層結構

在資料庫中，除了行和頁之外，還存在區（Extent）、段（Segment）和表空間（Tablespace）的概念。行、頁、區、段、表空間的關係，如下圖所示：

• 區（Extent）是比頁大一級的儲存結構，在 InnoDB 儲存引擎中，一個區會分配 64 個連續的頁。因為 InnoDB 中的頁預設大小是 16 KB，所以一個區的大小是 64 * 16 KB = 1 MB。
• 段（Segment）是由一個或多個區組成，區在檔案系統是一個連續分配的空間（在 InnoDB 中是連續的 64 個頁），不過在段中不要求區與區之間是相鄰的。段是資料庫中的分配單位，不同型別的資料庫物件以不同的段形式存在。當建立資料表、索引的時候，會建立相對應的段，比如建立一張表時會建立一個表段，建立一個索引時會建立一個索引段。
• 表空間（Tablespace）是一個邏輯容器，表空間儲存的物件是段，在一個表空間中可以有一個或多個段，但是一個段只能屬於一個表空間。資料庫由一個或多個表空間組成，表空間從管理上可以劃分為系統表空間、使用者表空間、撤銷表空間、臨時表空間等。

頁的內部結構

頁如果按照型別劃分的話，常見的有資料頁（儲存 B+Tree 節點）、系統頁、Undo 頁和事務資料頁等。資料頁是最常使用的頁。

資料頁預設的 16 KB 大小的儲存空間，被劃分為七個部分，分別是檔案頭（File Header）、頁頭（Page Header）、最大最小記錄（Infimum + Supremum）、使用者記錄（User Records）、空閒空間（Free Space）、頁目錄（Page Directory）和檔案尾（File Tailer）。

頁結構的示意圖如下所示：

頁的這 7 個結構的作用分別如下：

名稱	佔用大小	說明
File Header	38 位元組	檔案頭，描述頁的資訊
Page Header	56 位元組	頁頭，頁的狀態資訊
Infimum + Supremum	26 位元組	最大和最小記錄，這是兩個虛擬的行記錄
User Records	不確定	使用者記錄，儲存行記錄內容
Free Space	不確定	空閒記錄，頁中還沒有被使用的空間
Page Directory	不確定	頁目錄，儲存使用者記錄的相對位置
File Tailer	8 位元組	檔案尾，校驗頁是否完整

可以把這 7 個結構分成 3 個部分。

第 1 部分：File Header（檔案頭）和File Trailer（檔案尾）

首先是檔案通用部分，也就是檔案頭和檔案尾。

File Header

作用：描述各種頁的通用資訊，比如頁的編號、上一頁、下一頁是誰等。

大小：38 個位元組。

構成：

名稱	佔用空間大小	描述
FIL_PAGE_SPACE_OR_CHKSUM	4 位元組	頁的校驗和（checksum 值）
FIL_PAGE_OFFSET	4 位元組	頁號
FIL_PAGE_PREV	4 位元組	上一個頁的頁號
FIL_PAGE_NEXT	4 位元組	下一個頁的頁號
FIL_PAGE_LSN	8 位元組	頁面被最後修改時對應的日誌序列位置（Log Sequence Number）
FIL_PAGE_TYPE	2 位元組	頁的型別
FIL_PAGE_FILE_FLUSH_LSN	8 位元組	僅在系統表空間的一個頁中定義，代表檔案至少被重新整理到了對應的 LSN 值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4 位元組	頁屬於哪個表空間

• FIL_PAGE_OFFSET：每一個頁都有一個單獨的頁號，就跟身份證號碼一樣，InnoDB 透過頁號可以唯一定位一個頁。

• FIL_PAGE_TYPE：表示頁的型別，取值如下。

  型別名稱	十六進位制	描述
  FIL_PAGE_TYPE_ALLOCATED	Ox0000	最新分配，暫未使用
  FIL_PAGE_UNDO_LOG	0x0002	Undo 日誌頁
  FIL_PAGE_INODE	0x0003	段資訊節點
  FIL_PAGE_IBUF_FREE_LIST	0x0004	Insert Buffer 空閒列表
  FIL_PAGE_IBUF_BITMAP	0x0005	Insert Buffer 點陣圖
  FIL_PAGE_TYPE_SYS	0x0006	系統頁
  FIL_PAGE_TYPE_TRX_SYS	0x0007	事務系統資料
  FIL_PAGE_TYPE_FSP_HDR	0x0008	表空間頭部資訊
  FIL_PAGE_TYPE_XDES	0x0009	擴充套件描述頁
  FIL_PAGE_TYPE_BLOB	0x000A	溢位頁
  FIL_PAGE_INDEX	0x45BF	索引頁，即資料頁

• FIL_PAGE_PREV 和 FIL_PAGE_NEXT：InnoDB 都是以頁為單位存放資料的，如果資料分散到多個不連續的頁中儲存的話， 需要把這些頁關聯起來，FIL_PAGE_PREV 和 FIL_PAGE_NEXT 就分別代表本頁的上一個頁和下一個頁的頁號。這樣透過建立一個雙向連結串列把許許多多的頁就都串聯起來了，保證這些頁之間不需要是物理上的連續，而是邏輯上的連續。

  

• FIL_PAGE_SPACE_OR_CHKSUM：代表當前頁面的校驗和（checksum）。

  • 校驗和：對於一個很長的位元組串來說，可以透過某種演算法來計算一個比較短的值來代表這個很長的位元組串，這個比較短的值就稱為校驗和。在比較兩個很長的位元組串之前，先比較這兩個長位元組串的校驗和，如果校驗和都不一樣，那麼這兩個長位元組串肯定是不同的，這樣可以省去直接比較兩個比較長的位元組串的時間損耗。檔案頭和檔案尾都有屬性 FIL_PAGE_SPACE_OR_CHKSUM。

  • 作用：InnoDB 儲存引擎以頁為單位把資料載入到記憶體中處理，如果該頁中的資料在記憶體中被修改了，那麼在修改後的某個時間需要把資料同步到磁碟中。但是如果在同步了一半的時候，出現斷電等異常情況，就會造成該頁傳輸的不完整。為了檢測一個頁是否完整（也就是在同步的時候有沒有發生同步不完全的異常情況），這時可以透過檔案尾的校驗和（checksum 值）與檔案頭的校驗和做比對，如果兩個值不相等則證明頁的傳輸有問題，需要重新進行傳輸，否則認為頁的傳輸已經完成。

  • 具體的：每當一個頁面在記憶體中修改了，在同步之前就要把它的校驗和算出來，因為 File Header 在頁面的前邊，所以校驗和會被首先同步到磁碟，當完全寫完時，校驗和也會被寫到頁的尾部，如果完全同步成功，則頁的首部和尾部的校驗和應該是一致的。如果傳輸過程發生異常，那麼在 File Header 中的校驗和就代表著已經修改過的頁，而在 File Trailer 中的校驗和代表著原先的頁（未修改之前的頁的資訊），二者不同則意味著同步中間發生了異常。這裡，校驗方式就是採用 Hash 演算法進行校驗。

• FIL_PAGE_LSN：頁面被最後修改時對應的日誌序列位置，英文名是 Log Sequence Number。

File Trailer

大小：8 個位元組。

作用：

• 前 4 個位元組代表頁的校驗和：這個部分是和 File Header 中的校驗和相對應的，是為了校驗頁的完整性的。

• 後 4 個位元組代表頁面被最後修改時對應的日誌序列位置（LSN）：這個部分也是為了校驗頁的完整性的，如果首部和尾部的 LSN 值校驗不成功的話，也說明同步過程出現了問題。

第 2 部分：User Records（使用者記錄）、Infimum + Supremum（最大最小記錄）、Free Space（空閒空間）

其次是記錄部分，頁的主要作用是儲存記錄，所以使用者記錄和最大最小記錄佔了頁結構的主要空間。

Free Space

我們自己儲存的記錄會按照指定的行格式儲存到User Records部分。但是在一開始生成頁的時候，其實並沒有 User Records 這個部分，每當我們插入一條記錄，都會從 Free Space 部分，也就是尚未使用的儲存空間中申請一個記錄大小的空間劃分到 User Records 部分，當 Free Space 部分的空間全部被 User Records 部分替代掉之後，也就意味著這個頁使用完了，如果還有新的記錄插入的話，就需要去申請新的頁了。

User Records

User Records 中的這些記錄按照指定的行格式一條一條擺在 User Records 部分，相互之間形成單連結串列。

使用者記錄裡的一條條資料如何記錄？這裡需要講講記錄行格式的記錄頭資訊。

Infimum + Supremum

記錄之間可以比較大小，對於一條完整的記錄來說，比較記錄的大小就是比較主鍵的大小。比方說插入的 4 行記錄的主鍵值分別是：1、2、3、4，這也就意味著這 4 條記錄是從小到大依次遞增。

InnoDB 規定的最小記錄與最大記錄這兩條記錄的構造十分簡單，都是由 5 位元組大小的記錄頭資訊和 8 位元組大小的一個固定的部分組成的，如圖所示：

這兩條記錄不是我們自己定義的記錄，所以它們並不存放在頁的 User Records 部分，它們被單獨放在一個稱為 Infimum + Supremum 的部分，如圖所示：

第 3 部分：Page Directory（頁目錄）、Page Header（頁頭）

Page Director

1. 為什麼需要頁目錄？

作用：在頁中，記錄是以單向連結串列的形式進行儲存的。單向連結串列的特點就是插入、刪除非常方便，但是檢索效率不高，最差的情況下需要遍歷連結串列上的所有節點才能完成檢索。因此在頁結構中專門設計了頁目錄這個模組，專門給記錄做一個目錄，然後透過二分查詢法的方式進行檢索，提升效率。

需求：根據主鍵值查詢頁中的某條記錄，如何實現快速查詢呢？

SELECT * FROM page_demo WHERE c1 = 3

方式一：順序查詢。

• 從 Infimum記錄（最小記錄）開始，沿著連結串列一直往後找，直至找到目標記錄，或者找不到。因為連結串列中各個記錄的值是按照從小到大順序排列的，所以當連結串列的某個節點代表的記錄的主鍵值大於想要查詢的主鍵值時，就可以停止查詢了，因為該節點後邊的節點的主鍵值是依次遞增的。
• 如果一個頁中儲存了非常多的記錄，順序查詢的效能很差。

方式二：使用頁目錄，二分法查詢。

1. 將所有的記錄分成幾個組，這些記錄包括最小記錄和最大記錄，但不包括標記為 "已刪除" 的記錄。

2. 第 1 組，也就是最小記錄所在的分組，只有 1 個記錄；最後一組，就是最大記錄所在的分組，會有 1 ~ 8 條記錄；其餘的組記錄數量在 4 ~ 8 條之間。這樣做的好處是，除了第 1 組（最小記錄所在組）以外，其餘組的記錄數會盡量平分。

3. 在每個組中最後一條記錄的頭資訊中會儲存該組一共有多少條記錄，作為 n_owned 欄位。

4. 頁目錄用來儲存每組最後一條記錄的地址偏移量，這些地址偏移量會按照先後順序儲存起來，每組的地址偏移量也被稱之為槽（slot），每個槽相當於指標指向了不同組的最後一個記錄。

舉例 1：

舉例 2：現在的 page_demo 表中正常的記錄共有 6 條，InnoDB 會把它們分成兩組，第一組中只有一個最小記錄，第二組中是剩餘的 5 條記錄。

• 現在頁目錄部分中有兩個槽，也就意味著我們的記錄被分成了兩個組，槽 1 中的值是 112，代表最大記錄的地址偏移量（就是從頁面的 0 位元組開始數，數 112個 位元組）；槽 0 中的值是 99，代表最小記錄的地址偏移量。注意最小和最大記錄的頭資訊中的 n_owned 屬性。
• 最小記錄的 n_owned 值為 1，這就代表著以最小記錄結尾的這個分組中只有1條記錄，也就是最小記錄本身。最大記錄的n_owned值為5，這就代表著以最大記錄結尾的這個分組中只有5條記錄，包括最大記錄本身還有我們自己插入的4條記錄。

用箭頭指向的方式替代數字，這樣更易於理解，修改後如下：

再換個角度看一下：（單純從邏輯上看一下這些記錄和頁目錄的關係）

2. 頁目錄分組的個數如何確定？

為什麼最小記錄的 n_owned 值為 1，而最大記錄的 n_owned 值為 5 呢？

InnoDB 規定：對於最小記錄所在的分組只能有 1 條記錄，最大記錄所在的分組擁有的記錄條數只能在 1 ~ 8 條之間，剩下的分組中記錄的條數範圍只能在是 4 ~ 8 條之間。

分組是按照下邊的步驟進行的：

• 初始情況下，一個資料頁裡只有最小記錄和最大記錄兩條記錄，它們分屬於兩個分組。

• 之後每插入一條記錄，都會從頁目錄中找到主鍵值比本記錄的主鍵值大並且差值最小的槽，然後把該槽對應的記錄的 n_owned 值加 1，表示本組內又新增了一條記錄，直到該組中的記錄數等於 8 個。

• 在一個組中的記錄數等於 8 個後再插入一條記錄時，會將組中的記錄拆分成兩個組，一個組中 4 條記錄，另一個 5 條記錄。這個過程會在頁目錄中新增一個槽來記錄這個新增分組中最大的那條記錄的偏移量。

3. 頁目錄結構下如何快速查詢記錄？

現在向 page_demo 表中新增更多的資料，如下：

INSERT INTO page_demo VALUES
(5, 500, 'zhou'), 
(6, 600, 'chen'), 
(7, 700, 'deng'), 
(8, 800, 'yang'), 
(9, 900, 'wang'), 
(10, 1000, 'zhao'), 
(11, 1100, 'qian'), 
(12, 1200, 'feng'), 
(13, 1300, 'tang'), 
(14, 1400, 'ding'), 
(15, 1500, 'jing'), 
(16, 1600, 'quan')

新增了 12 條記錄，現在頁裡一共有 18 條記錄了（包括最小和最大記錄），這些記錄被分成了 5 個組，如圖所示：

這裡只保留了 16 條記錄的記錄頭資訊中的 n_owned 和 next_record 屬性，省略了各個記錄之間的箭頭。

現在看怎麼從這個頁目錄中查詢記錄。因為各個槽代表的記錄的主鍵值都是從小到大排序的，所以可以使用二分法來進行快速查詢。5 個槽的編號分別是：0、1、2、3、4，所以初始情況下最低的槽就是 low = 0，最高的槽就是 high = 4。比方說想找主鍵值為 6 的記錄，過程是這樣的：

1. 計算中間槽的位置：(0 + 4) / 2 = 2，所以檢視槽 2 對應記錄的主鍵值為 8，又因為 8 > 6，所以設定 high = 2，low 保持不變。
2. 重新計算中間槽的位置：(0 + 2) / 2 = 1，所以檢視槽 1 對應的主鍵值為 4，又因為 4 < 6，所以設定 low = 1，high 保持不變。
3. 因為 high - low 的值為1，所以確定主鍵值為 6 的記錄在槽 2 對應的組中。此刻需要找到槽 2 中主鍵值最小的那條記錄，然後沿著單向連結串列遍歷槽 2 中的記錄。

但是前邊說過，每個槽對應的記錄都是該組中主鍵值最大的記錄，這裡槽 2 對應的記錄是主鍵值為 8 的記錄，怎麼定位一個組中最小的記錄呢？因為各個槽都是挨著的，可以很輕易的拿到槽 1 對應的記錄（主鍵值為 4），該條記錄的下一條記錄就是槽 2 中主鍵值最小的記錄，該記錄的主鍵值為 5。所以可以從這條主鍵值為 5 的記錄出發，遍歷槽 2 中的各條記錄，直到找到主鍵值為 6 的那條記錄即可。

由於一個組中包含的記錄條數只能是 1 ~ 8 條，所以遍歷一個組中的記錄的代價是很小的。

4. 小結：

在一個資料頁中查詢指定主鍵值的記錄的過程分為兩步：

1. 透過二分法確定該記錄所在的槽，並找到該槽所在分組中主鍵值最小的那條記錄。
2. 透過記錄的 next_record 屬性遍歷該槽所在的組中的各個記錄。

Page Header

為了能得到一個資料頁中儲存的記錄的狀態資訊，比如本頁中已經儲存了多少條記錄，第一條記錄的地址是什麼，頁目錄中儲存了多少個槽等等，特意在頁中定義了一個叫 Page Header 的部分，這個部分佔用固定的 56 個位元組，專門儲存各種狀態資訊。

名稱	佔用空間大小	描述
PAGE_N_DIR_SLOTS	2 位元組	在頁目錄中的槽數量
PAGE_HEAP_TOP	2 位元組	還未使用的空間最小地址，也就是說從該地址之後就是 Free Space
PAGE_N_HEAP	2 位元組	本頁中的記錄的數量（包括最小和最大記錄，以及標記為刪除的記錄）
PAGE_FREE	2 位元組	第一個已經標記為刪除的記錄地址（各個已刪除的記錄透過 next_record 也會組成一個單連結串列，這個單連結串列中的記錄可以被重新利用）
PAGE_GARBAGE	2 位元組	已刪除記錄佔用的位元組數
PAGE_LAST_INSERT	2 位元組	最後插入記錄的位置
PAGE_DIRECTION	2 位元組	記錄插入的方向
PAGE_N_DIRECTION	2 位元組	一個方向連續插入的記錄數量
PAGE_N_RECS	2 位元組	該頁中記錄的數量（不包括小和最大記錄，以及標記為刪除的記錄）
PAGE_MAX_TRX_ID	8 位元組	修改當前頁的最大事務 ID，該值僅在二級索引中定義
PAGE_LEVEL	2 位元組	當前頁在 B+Tree 中所處的層級
PAGE_INDEX_ID	8 位元組	索引 ID，表示當前頁屬於哪個索引
PAGE_BTR_SEG_LEAF	10 位元組	B+Tree 葉子段的頭部資訊，僅在 B+Tree 的 Root 頁定義
PAGE_BTR_SEG_TOP	10 位元組	B+Tree 非葉子段的頭部資訊，僅在 B+Tree 的 Root 頁定義

• PAGE_DIRECTION：假如新插入的一條記錄的主鍵值比上一條記錄的主鍵值大，我們說這條記錄的插入方向是右邊，反之則是左邊。用來表示最後一條記錄插入方向的狀態就是 PAGE_DIRECTION。
• PAGE_N_DIRECTION：假設連續幾次插入新記錄的方向都是一致的，InnoDB 會把沿著同一個方向插入記錄的條數記下來，這個條數就用 PAGE_N_DIRECTION 這個狀態表示。當然，如果最後一條記錄的插入方向改變了的話，這個狀態的值會被清零重新統計。

從資料頁的角度看 B+Tree 如何查詢

一棵 B+Tree 按照節點型別可以分成兩部分：

• 葉子節點，B+Tree 最底層的節點，節點的高度為 0，儲存行記錄。
• 非葉子節點，節點的高度大於 0，儲存索引鍵和頁面指標，並不儲存行記錄本身。

當我們從頁結構來理解 B+Tree 的結構的時候，可以幫助理解一些透過索引進行檢索的原理：

• B+Tree 是如何進行記錄檢索的？
  • 如果透過 B+Tree 的索引查詢行記錄，首先是從 B+Tree 的根開始，逐層檢索，直到找到葉子節點，也就是找到對應的資料頁為止，將資料頁載入到記憶體中，頁目錄中的槽採用二分查詢的方式，先找到一個粗略的記錄分組，然後再在分組中透過連結串列遍歷的方式查詢記錄。
• 普通索引和唯一索引在查詢效率上有什麼不同？
  • 建立索引的時候，可以是普通索引，也可以是唯一索引。唯一索引就是在普通索引上增加了約束性，也就是關鍵字唯一，找到關鍵字就停止檢索。而普通索引，可能會存在使用者記錄中的關鍵字相同的情況，根據頁結構的原理，當讀取一條記錄的時候，不是單獨將這條記錄從磁碟中讀出去，而是將這個記錄所在的頁載入到記憶體中進行讀取。InnoDB 儲存引擎的頁大小為 16 KB，在一個頁中可能儲存著上千個記錄，因此在普通索引的欄位上進行查詢，也就是在記憶體中多幾次 "判斷下一條記錄" 的操作，對於 CPU 來說，這些操作所消耗的時間是可以忽略不計的。因此，對一個索引欄位進行檢索，採用普通索引還是唯一索引，在檢索效率上基本上沒有差別。

InnoDB 行格式（或記錄格式）

資料以行為單位來向表中，這些記錄在磁碟上的存放方式也被稱為行格式或者記錄格式。InnoDB 儲存引擎設計了 4 種不同型別的行格式，分別是Compact、Redundant、Dynamic和Compressed行格式。

檢視 MySQL 8.0 預設的行格式：

mysql> SELECT @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic                     |
+-----------------------------+
1 row in set (0.00 sec)

# 或者
mysql> SHOW TABLE STATUS LIKE 'employees';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| employees | InnoDB |      10 | Dynamic    |  107 |            153 |       16384 |               0 |        81920 |         0 |           NULL | 2023-04-04 22:34:54 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)

指定行格式的語法

在建立或修改表的語句中指定行格式：

# 建立表
CREATE TABLE 表名 (列的資訊) ROW_FORMAT=行格式名稱

# 修改表
ALTER TABLE 表名 ROW_FORMAT=行格式名稱

示例：

mysql> CREATE TABLE record_test_table (
    -> col1 VARCHAR(8),
    -> col2 VARCHAR(8) NOT NULL,
    -> col3 CHAR(8),
    -> col4 VARCHAR(8)
    -> ) CHARSET=ascii ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO record_test_table(col1, col2, col3, col4) 
    -> VALUES
    -> ('zhangsan', 'lisi', 'wangwu', 'songhk'), 
    -> ('tong', 'chen', NULL, NULL);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

Compact 行格式

在 MySQL 5.1 版本中，預設設定為 Compact 行格式。一條完整的記錄其實可以被分為記錄的額外資訊和記錄的真實資料兩大部分。

變長欄位長度列表

MySQL 支援一些變長的資料型別，比如 VARCHAR(M)、VARBINARY(M)、TEXT 型別、BLOB型別，這些資料型別修飾列稱為變長欄位，變長欄位中儲存多少位元組的資料不是固定的，所以在儲存真實資料的時候需要順便把這些資料佔用的位元組數也存起來。在 Compact 行格式中，把所有變長欄位的真實資料佔用的位元組長度都存放在記錄的開頭部位，從而形成一個變長欄位長度列表。

注意：這裡面儲存的變長長度和欄位順序是反過來的。比如兩個 VARCHAR 欄位在表結構的順序是 a(10)，b(15)。那麼在變長欄位長度列表中儲存的長度順序就是 15，10，是反過來的。

以 record_test_table 表中的第一條記錄舉例：因為 record_test_table 表的 col1、col2、col4 列都是 VARCHAR(8) 型別的，所以這三個列的值的長度都需要儲存在記錄開頭處，注意 record_test_table 表中的各個列都使用的是 ASCⅡ 字符集（每個字元只需要 1 個位元組來進行編碼，如果是 UTF-8，則需要 3 個位元組）。

列名	儲存內容	內容長度（十進位制表示）	內容長度（十六進位制表示）
col1	'zhangsan'	8	0x08
col2	'lisi'	4	0x04
col4	'songhk'	6	0x06

又因為這些長度值需要按照列的逆序存放，所以最後變長欄位長度列表的位元組串用十六進位制表示的效果就是（各個位元組之間實際上沒有空格，用空格隔開只是方便理解）：06 04 08。

把這個位元組串組成的變長欄位長度列表填入上邊的示意圖中的效果就是：

NULL 值列表

Compact 行格式會把可以為 NULL 的列統一管理起來，存在一個標記為 NULL 值的列表中。如果表中沒有允許儲存 NULL 的列，則 NULL 值列表也不存在。

之所以要儲存 NULL 值，是因為資料都是需要對齊的，如果沒有標註出來 NULL 值的位置，就有可能在查詢資料的時候出現混亂。如果使用一個特定的符號放到相應的資料位表示 NULL 值的話，雖然能達到效果，但是這樣很浪費空間，所以直接就在行資料得頭部開闢出一塊空間專門用來記錄該行資料哪些是非空資料，哪些是空資料，格式如下：

1. 二進位制位的值為 1 時，代表該列的值為 NULL。
2. 二進位制位的值為 0 時，代表該列的值不為 NULL。

例如：欄位 a、b、c，其中 a 是主鍵，在某一行中儲存的數依次是 a = 1、b = null、c = 2。那麼 Compact 行格式中的 NULL 值列表中儲存：01。第一個 0 表示 c 不為 NULL，第二個 1 表示 b 為 NULL。這裡之所以沒有 a，是因為資料庫會自動跳過主鍵，因為主鍵肯定是非 NULL 且唯一的，在 NULL 值列表的資料中就會自動跳過主鍵。

record_test_table 插入的兩條記錄的 NULL 值列表就如下：

• 第一條記錄：

  

• 第二條記錄：

  

記錄頭資訊（5 位元組）

1. 記錄頭資訊概述

建立表 page_demo：

mysql> CREATE TABLE page_demo(
    -> c1 INT,
    -> c2 INT,
    -> c3 VARCHAR(10000),
    -> PRIMARY KEY (c1)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.03 sec)

這個表中記錄的行格式示意圖：

這些記錄頭資訊中各個屬性如下：

名稱	大小（單位：bit）	描述
預留位 1	1	沒有使用
預留位 2	1	沒有使用
delete_mask	1	標記該記錄是否被刪除
min_rec_mask	1	B+Tree 的每層非葉子節點中的最小記錄，都會新增該標記
n_owned	4	表示當前記錄擁有的記錄數
heap_no	13	表示當前記錄在記錄堆的位置資訊
record_type	3	表示當前記錄的型別，0 表示普通記錄，1 表示 B+Tree 非葉子節點記錄，2 表示最小記錄，3 表示最大記錄
next_record	16	表示下一條記錄的相對位置

簡化後的行格式示意圖：

插入資料：

mysql> INSERT INTO page_demo 
    -> VALUES
    -> (1, 100, 'song'), 
    -> (2, 200, 'tong'), 
    -> (3, 300, 'zhan'), 
    -> (4, 400, 'lisi');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

圖示如下：

編號是從 2 開始的，因為預設會有一個最小記錄和最大記錄。

2. 記錄頭資訊中各個屬性詳細分析

• delete_mask

  • 這個屬性標記著當前記錄是否被刪除，佔用 1 個二進位制位。
    • 值為 0：代表記錄並沒有被刪除。
    • 值為 1：代表記錄被刪除掉了。
  • 被刪除的記錄實際上沒有刪除，其還在真實的磁碟上。這些被刪除的記錄之所以不立即從磁碟上移除，是因為移除它們之後其他的記錄在磁碟上需要重新排列，導致效能消耗。所以只是打一個刪除標記而已，所有被刪除掉的記錄都會組成一個所謂的垃圾連結串列，在這個連結串列中的記錄佔用的空間稱之為可重用空間，之後如果有新記錄插入到表中的話，可能把這些被刪除的記錄佔用的儲存空間覆蓋掉。

• min_rec_mask

  • B+Tree 的每層非葉子節點中的最小記錄都會新增該標記，min_rec_mask 值為 1。我們自己插入的四條記錄的 min_rec_mask 值都是 0，意味著它們都不是 B+Tree 的非葉子節點中的最小記錄。

• record_type

  • 這個屬性表示當前記錄的型別，一共有 4 種型別的記錄：
    • 0：表示普通記錄。
    • 1：表示 B+Tree 非葉節點記錄。
    • 2：表示最小記錄。
    • 3：表示最大記錄。
  • 從圖中也可以看出來，我們自己插入的記錄就是普通記錄，它們的 record_type 值都是 0，而最小記錄和最大記錄的 record_type 值分別為 2 和 3。至於 record_type 為1的情況，在索引的資料結構章節講過。

• heap_no

  • 這個屬性表示當前記錄在本頁中的位置。
  • 從圖中可以看出來，我們插入的 4 條記錄在本頁中的位置分別是：2、3、4、5。
  • MySQL 會自動給每個頁里加兩個記錄，由於這兩個記錄並不是我們自己插入的，所以有時候也稱為偽記錄或者虛擬記錄。這兩個偽記錄一個代表最小記錄，一個代表最大記錄。最小記錄和最大記錄的 heap_no 值分別是 0 和 1，也就是說它們的位置最靠前。

• n_owned

  • 頁目錄中每個組中最後一條記錄的頭資訊中會儲存該組一共有多少條記錄，作為 n_owned 欄位。詳情見 Page Directory。

• next_record

  • 記錄頭資訊裡該屬性非常重要，它表示從當前記錄的真實資料到下一條記錄的真實資料的地址偏移量。比如：第一條記錄的 next_record 值為 32，意味著從第一條記錄的真實資料的地址處向後找 32 個位元組便是下一條記錄的真實資料。

  • 注意，下一條記錄指得並不是按照我們插入順序的下一條記錄，而是按照主鍵值由小到大的順序的下一條記錄。而且規定 Infimum 記錄（也就是最小記錄）的下一條記錄就是本頁中主鍵值最小的使用者記錄，而本頁中主鍵值最大的使用者記錄的下一條記錄就是 Supremum 記錄（也就是最大記錄）。下圖用箭頭代替偏移量表示 next_record。

    

演示刪除和新增操作：

• 刪除操作

  • 從表中刪除掉一條記錄，這個連結串列也是會跟著變化：

    mysql> DELETE FROM page_demo WHERE c1 = 2;
    Query OK, 1 row affected (0.01 sec)
    

  • 刪掉第 2 條記錄後的示意圖就是：

    

    • 從圖中可以看出來，刪除第 2 條記錄前後主要發生了這些變化：
      • 第 2 條記錄並沒有從儲存空間中移除，而是把該條記錄的 delete_mask 值設定為 1。
      • 第 2 條記錄的 next_record 值變為了 0，意味著該記錄沒有下一條記錄了。
      • 第 1 條記錄的 next_record 指向了第 3 條記錄。
      • 最大記錄的 n_owned 值從 5 變成了 4。
    • 所以，不論怎麼對頁中的記錄做增刪改操作，InnoDB 始終會維護一條記錄的單連結串列，連結串列中的各個節點是按照主鍵值由小到大的順序連線起來的。

• 新增操作

  • 主鍵值為 2 的記錄被刪掉了，但是儲存空間卻沒有回收，如果再次把這條記錄插入到表中，會發生什麼事呢？

    mysql> INSERT INTO page_demo VALUES (2, 200, 'tong');
    Query OK, 1 row affected (0.01 sec)
    

  • 記錄的儲存情況：

    

  • 直接複用了原來被刪除記錄的儲存空間。說明：當資料頁中存在多條被刪除掉的記錄時，這些記錄的 next_record 屬性將會把這些被刪除掉的記錄組成一個垃圾連結串列，以備之後重用這部分儲存空間。

記錄的真實資料

記錄的真實資料除了我們自己定義的列的資料以外，還會有三個隱藏列：

列名	是否必須	佔用空間	描述
row_id	否	6 位元組	行 ID，唯一標識一條記錄
transaction_id	是	6 位元組	事務 ID
roll_pointer	是	7 位元組	回滾指標

實際上這幾個列的真正名稱其實是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。

• 如果一個表沒有手動定義主鍵，則會選取一個 Unique 鍵作為主鍵，如果連 Unique 鍵都沒有定義的話，則會為表預設新增一個名為 row_id 的隱藏列作為主鍵，所以 row_id 是在沒有自定義主鍵以及 Unique 鍵的情況下才會存在的。

• 事務 ID 和回滾指標在後面的 MySQL 事務日誌章節中講解。

建立表 mytest：

mysql> CREATE TABLE mytest(
    -> col1 VARCHAR(10),
    -> col2 VARCHAR(10),
    -> col3 CHAR(10),
    -> col4 VARCHAR(10)
    -> )ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;

插入資料：

mysql> INSERT INTO mytest
    -> VALUES
    -> ('a','bb','bb','ccc'),
    -> ('d','ee','ee','fff'),
    -> ('d',NULL,NULL,'fff');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

在 Windows 作業系統下，可以選擇透過程式 UltraEdit 開啟表空間檔案 mytest.ibd 這個二進位制檔案。內容如下：

------------------------------------------------------------------------------------------
0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 00|supremum........|
0000c080 2c 00 00 00 2b 68 00 00 00 00 00 06 05 80 00 00|,...+h..........|
0000c090 00 32 01 10 61 62 62 62 62 20 20 20 20 20 20 20|.2..abbbb|
0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 00|ccc........+...|
0000c0b0 2b 68 01 00 00 00 00 06 06 80 00 00 00 32 01 10|+h...........2..|
0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66|deeeefff|
0000c0d0 03 01 06 00 00 20 ff 98 00 00 00 2b 68 02 00 00|..........+h...|
0000c0e0 00 00 06 07 80 00 00 00 32 01 10 64 66 66 66 00|........2..dfff.|
------------------------------------------------------------------------------------------

該行記錄從0000c078開始，參考下面：

---------------------------------------------------------------------
03 02 01                     /*變長欄位長度列表，逆序*/
00                              /*NULL標誌位，第一行沒有NULL值*/
00 00 10 00 2c            /*Record Header，固定5位元組長度*/
00 00 00 2b 68 00       /*RowID InnoDB自動建立，6位元組*/
00 00 00 00 06 05       /*TransactionID*/
80 00 00 00 32 01 10   /*Roll Pointer*/
61                               /*列1資料'a'*/
62 62                          /*列2資料'bb'*/
62 62 20 20 20 20 20 20 20 20/*列3資料'bb'*/
63 63 63                     /*列4資料'ccc'*/
---------------------------------------------------------------------

• 注意 1：InnoDB 每行有隱藏列 TransactionID 和 Roll Pointer。

• 注意 2：固定長度 CHAR 欄位在未能完全佔用其長度空間時，會用 0x20 來進行填充。

接著再來分析下 Record Header 的最後兩個位元組，這兩個位元組代表 next_recorder，0x2c 代表下一個記錄的偏移量，即當前記錄的位置加上偏移量 0x2c 就是下條記錄的起始位置。

第二行將不做整理，除了 RowID 不同外，它和第一行大同小異，現在來分析有 NULL 值的第三行：

---------------------------------------------------------------------
03 01                               /*變長欄位長度列表，逆序*/
06                                   /*NULL標誌位，第三行有NULL值*/
00 00 20 ff 98                  /*Record Header*/
00 00 00 2b 68 02           /*RowID*/
00 00 00 00 06 07           /*TransactionID*/
80 00 00 00 32 01 10       /*Roll Pointer*/
64                                   /*列1資料'd'*/
66 66 66                         /*列4資料'fff'*/
---------------------------------------------------------------------

第三行有 NULL 值，因此 NULL 標誌位不再是 00 而是 06，轉換成二進位制為 00000110，為 1 的值代表第 2 列和第 3 列的資料為 NULL。在其後儲存列資料的部分，使用者會發現沒有儲存 NULL 列，而只儲存了第 1 列和第 4 列非 NULL 的值。

因此這個例子很好地說明了：不管是 CHAR 型別還是 VARCHAR 型別，在 Compact 格式下 NULL 值都不佔用任何儲存空間。

Dynamic 和 Compressed 行格式

行溢位

InnoDB 儲存引擎可以將一條記錄中的某些資料儲存在真正的資料頁面之外。

很多 DBA 喜歡 MySQL 資料庫提供的 VARCHAR(M) 型別，認為可以存放 65535 位元組。這是真的嗎？如果我們使用 ASCⅡ 字符集的話，一個字元就代表一個位元組，我們看看 VARCHAR(65535) 是否可用：

mysql> CREATE TABLE varchar_size_demo(
    -> c VARCHAR(65535)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

報錯資訊表達的意思是：MySQL 對一條記錄佔用的最大儲存空間是有限制的，除 BLOB 或者 TEXT 型別的列之外， 其他所有的列（不包括隱藏列和記錄頭資訊）佔用的位元組長度加起來不能超過 65535 個位元組。

這個 65535 個位元組除了列本身的資料之外，還包括一些其他的資料，以 Compact 行格式為例，比如說我們為了儲存一個 VARCHAR(M) 型別的列，除了真實資料佔有空間以外，還需要記錄的額外資訊。

如果該 VARCHAR 型別的列沒有 NOT NULL 屬性，那最多隻能儲存 65532 個位元組的資料，因為變長欄位的長度佔用 2 個位元組，NULL 值標識需要佔用 1 個位元組：

mysql> CREATE TABLE varchar_size_demo(
    -> c VARCHAR(65532)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.04 sec)

如果有 NOT NULL 屬性，那麼就不需要 NULL 值標識，也就可以多儲存一個位元組，即 65533 個位元組：

mysql> CREATE TABLE varchar_size_demo( 
    -> c VARCHAR(65533) NOT NULL
    -> ) CHARSET=ascii ROW_FORMAT=Compact; 
Query OK, 0 rows affected (0.03 sec)

因為一個頁的大小一般是 16 KB，也就是 16384 位元組，而一個 VARCHAR(M) 型別的列就最多可以儲存 65533 個位元組，這樣就可能出現一個頁存放不了一條記錄，這種現象稱為行溢位。

在 Compact 和 Reduntant 行格式中，對於佔用儲存空間非常大的列，在記錄的真實資料處只會儲存該列的一部分資料，把剩餘的資料分散儲存在幾個其他的頁中進行分頁儲存，然後記錄的真實資料處用 20 個位元組儲存指向這些頁的地址（當然這 20 個位元組中還包括這些分散在其他頁面中的資料的佔用的位元組數），從而可以找到剩餘資料所在的頁。這稱為頁的擴充套件，舉例如下：

Dynamic 和 Compressed 行格式

在 MySQL 8.0 中，預設行格式就是 Dynamic。Dynamic、Compressed 行格式和 Compact 行格式挺像，只不過在處理行溢位資料時有分歧：

• Dynamic 和 Compressed 兩種記錄格式，對於存放在 BLOB 中的資料採用了完全的行溢位的方式。如圖，在資料頁中只存放 20 個位元組的指標（溢位頁的地址），實際的資料都存放在 Off Page （溢位頁）中。

  

• Compact 和 Redundant 兩種格式會在記錄的真實資料處儲存一部分資料（存放 768 個字首位元組）。

Compressed 行記錄格式的另一個功能就是，儲存在其中的行資料會以 zlib 的演算法進行壓縮，因此對於 BLOB、TEXT、VARCHAR 這類大長度型別的資料能夠進行非常有效的儲存。

Redundant 行格式

Redundant 是 MySQL 5.0 版本之前 InnoDB 的行記錄儲存方式，MySQL 5.0 支援 Redundant 是為了相容之前版本的頁格式。

現在把表 record_test_table 的行格式修改為 Redundant：

mysql> ALTER TABLE record_test_table ROW_FORMAT=Redundant;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

從上圖可以看到，不同於 Compact 行記錄格式，Redundant 行格式的首部是一個欄位長度偏移列表，同樣是按照列的順序逆序放置的。

欄位長度偏移列表

Compact 行格式的開頭是變長欄位長度列表，而 Redundant 行格式的開頭是欄位長度偏移列表，與變長欄位長度列表有兩處不同：

• 少了 "變長" 兩個字：Redundant 行格式會把該條記錄中所有列（包括隱藏列）的長度資訊都按照逆序儲存到欄位長度偏移列表。

• 多了 "偏移" 兩個字：這意味著計算列值長度的方式不像 Compact 行格式那麼直觀，它是採用兩個相鄰數值的差值來計算各個列值的長度。

舉例：比如第一條記錄的欄位長度偏移列表就是：

2B 25 1F 1B 13 0C 06

因為它是逆序排放的，所以按照列的順序排列就是：

06 0C 13 17 1A 24 25

按照兩個相鄰數值的差值來計算各個列值的長度的意思就是：

• 第一列（row_id）的長度就是 0x06 個位元組，也就是 6 個位元組。
• 第二列（transaction_id）的長度就是（0x0C - 0x06）個位元組，也就是 6 位元組。
• 第三列（roll_pointer）的長度就是（0x13 - 0x0C）個位元組，也就是 7 個位元組。
• 第四列（col1）的長度就是（0x1B - 0x13）個位元組，也就是 8 個位元組。
• 第五列（col2）的長度就是（0x1F - 0x1B）個位元組，也就是 4 個位元組。
• 第六列（col3）的長度就是（0x25 - 0x1F）個位元組，也就是 6 個位元組。
• 第七列（col4）的長度就是（0x2B - 0x25）個位元組，也就是 6 個位元組。

記錄頭資訊（record header）

不同於 Compact 行格式，Redundant 行格式中的記錄頭資訊固定佔用 6 個位元組（48 位），每位的含義見下表。

名稱	大小（bit）	描述
（）	1	未使用
（）	1	未使用
deleted_mask	1	該行是否已被刪除
min_rec_mask	1	B+Tree 的每層非葉子節點中的最小記錄都會新增該標記
n_owned	4	該記錄擁有的記錄數
heap_no	13	索引堆中該條記錄的位置資訊
n_fields	10	記錄中列的數量
1byte_offs_flag	1	記錄欄位長度偏移列表中每個列對應的偏移量使用 1 個位元組還是 2 個位元組表示
next_record	16	頁中下一條記錄的絕對位置

與 Compact 行格式的記錄頭資訊對比來看，有兩處不同：（下面的解釋，瞭解即可）

• Redundant 行格式多了 n_fields 和 1byte_offs_flag 這兩個屬性。
• Redundant 行格式沒有 record_type 這個屬性。

其中，n_fields代表一行中列的數量，佔用 10 位，這也很好地解釋了為什麼 MySQL 一個行支援最多的列為 1023。另一個值 1byte_offs_flags 定義了偏移列表佔用 1 個位元組還是 2 個位元組。當它的值為 1 時，表明使用 1個位元組儲存。當它的值為 0 時，表明使用 2 個位元組儲存。

1byte_offs_flag 的值是怎麼選擇的 ?

我們前邊說過每個列對應的偏移量可以佔用 1 個位元組或者 2 個位元組來儲存，那到底什麼時候用 1 個位元組，什麼時候用 2 個位元組呢？其實是根據該條 Redundant 行格式記錄的真實資料佔用的總大小來判斷的：

• 當記錄的真實資料佔用的位元組數值不大於 127（十六進位制 0x7F，二進位制 01111111）時，每個列對應的偏移量佔用 1 個位元組。

• 當記錄的真實資料佔用的位元組數大於 127，但不大於 32767（十六進位制 0x7FFF，二進位制 0111111111111111）時，每個列對應的偏移量佔用 2 個位元組。

• 有沒有記錄的真實資料大於 32767 的情況呢？有，不過此時的記錄已經存放到了溢位頁中，在本頁中只保留前 768 個位元組和 20 個位元組的溢位頁面地址。因為欄位長度偏移列表處只需要記錄每個列在本頁面中的偏移就好了，所以每個列使用 2 個位元組來儲存偏移量就夠了。

可以看出，Redundant 行格式還是比較簡單粗暴的，直接使用整個記錄的真實資料長度來決定使用 1 個位元組還是 2 個位元組儲存列對應的偏移量。只要整條記錄的真實資料佔用的儲存空間大小大於 127，即使第一個列的值佔用儲存空間小於 127，那也需要使用 2 個位元組來表示該列對應的偏移量。簡單粗暴，就是這麼簡單粗暴（所以這種行格式有些過時了）。

Redundant 行格式中 NULL 值的處理

因為 Redundant 行格式並沒有 NULL 值列表，所以 Redundant 行格式在欄位長度偏移列表中的各個列對應的偏移量處做了一些特殊處理 —— 將列對應的偏移量值的第一個位元位作為是否為 NULL 的依據，該位元位也可以被稱之為 NULL 位元位。也就是說在解析一條記錄的某個列時，首先看一下該列對應的偏移量的 NULL 位元位是不是為 1。如果為 1，那麼該列的值就是 NULL，否則不是 NULL。

這也就解釋了上邊介紹為什麼只要記錄的真實資料大於 127（十六進位制 0x7F，二進位制 01111111）時，就採用 2 個位元組來表示一個列對應的偏移量，主要是第一個位元位是所謂的 NULL 位元位，用來標記該列的值是否為 NULL。

但是還有一點要注意，對於值為 NULL 的列來說，該列的型別是否為定長型別決定了 NULL 值的實際儲存方式，我們接下來分析一下 record_test_table 表的第二條記錄，它對應的欄位長度偏移列表如下：

A4 A4 1A 17 13 0C 06

按照列的順序排放就是：

06 0C 13 17 1A A4 A4

我們分情況看一下：

• 如果儲存 NULL 值的欄位是定長型別的，比方說 CHAR(M) 資料型別的，則 NULL 值也將佔用記錄的真實資料部分，並把該欄位對應的資料使用0x00S 位元組填充。
  • 如圖第二條記錄的 c3 列的值是 NULL，而 c3 列的型別是 CHAR(10)，佔用記錄的真實資料部分 10 位元組，所以我們看到在 Redundant 行格式中使用 0x00000000000000000000 來表示 NULL 值。
  • 另外，c3 列對應的偏移量為 0xA4，它對應的二進位制實際是：10100100，可以看到最高位為 1，意味著該列的值是 NULL。將最高位去掉後的值變成了 0100100，對應的十進位制值為 36，而 c2 列對應的偏移量為 0x1A，也就是十進位制的 26。36 - 26 = 10，也就是說最終 c3 列佔用的儲存空間為 10 個位元組。
• 如果該儲存 NULL 值的欄位是變長資料型別的，則不在記錄的真實資料處佔用任何儲存空間。
  • 比如 record_test_table 表的 c4 列是 VARCHAR(10) 型別的，VARCHAR(10) 是一個變長資料型別，c4 列對應的偏移量為 0xA4，與 c3 列對應的偏移量相同，這也就意味著它的值也為 NULL，將 0xA4 的最高位去掉後對應的十進位制值也是 36，36 - 36 = 0，也就意味著 c4 列本身不佔用任何記錄的實際資料處的空間。

除了以上的幾點之外，Redundant 行格式和 Compact 行格式還是大致相同的。

總結，Redundant 行格式和 Compact 行格式的差異：

• Redundant 沒有了 NULL 值列表。

• Redundant 行格式的記錄頭資訊：

  • 多了 n_fields 和 1byte_offs_flag 這兩個屬性。
  • 沒有 record_type 這個屬性。

區、段與碎片區

為什麼要有區

B+Tree 的每一層中的頁都會形成一個雙向連結串列，如果是以頁為單位來分配儲存空間的話，雙向連結串列相鄰的兩個頁之間的物理位置可能離的非常遠。在介紹 B+Tree 索引的適用場景的時候，特別提到範圍查詢只需要定位到最左邊的記錄和最右邊的記錄，然後沿著雙向連結串列一直掃描就可以了，而如果連結串列中相鄰的兩個頁物理位置離的非常遠，就是所謂的隨機 I/O。因為磁碟的速度和記憶體的速度差了好幾個數量級，隨機 I/O 的效率非常慢，所以應該儘量讓連結串列中相鄰的頁的物理位置也相鄰，這樣進行範圍查詢的時候才可以使用所謂的順序 I/O。

引入區的概念，一個區就是在物理位置上連續的 64 個頁。因為 InnoDB 中頁的大小預設是 16 KB，所以一個區的大小是 64 * 16 KB = 1 MB。在表中資料量大的時候，為某個索引分配空間的時候就不再按照頁為單位來分配，而是按照區為單位分配，甚至在表中的資料特別多的時候，可以一次性分配多個連續的區。雖然可能造成一點點空間的浪費（資料不足以填充滿整個區），但是從效能角度看，可以消除很多的隨機 I/O，提升處理的效率。

為什麼要有段

對於範圍查詢，其實是對 B+Tree 葉子節點中的記錄進行順序掃描，而如果不區分葉子節點和非葉子節點，統統把節點代表的頁面放到申請到的區中的話，進行範圍掃描的效果就大打折扣。所以 InnoDB 對 B+Tree 的葉子節點和非葉子節點進行了區別對待，也就是說葉子節點有自己獨有的區，非葉子節點也有自己獨有的區。存放葉子節點的區的集合就算是一個段（Segment），存放非葉子節點的區的集合也算是一個段。也就是說一個索引會生成 2 個段，一個葉子節點段，一個非葉子節點段。

除了索引的葉子節點段和非葉子節點段意外，InnoDB 中還有為儲存一些特殊的資料而定義的段，比如回滾段。所以，常見的段有資料段、索引段、回滾段。資料段即為 B+Tree 的葉子節點，索引段即為 B+Tree 的非葉子節點。

在 InnoDB 儲存引擎中，對段的管理都是由引擎自身所完成，DBA 不能也沒有必要對其進行控制，這從一定程度上簡化了 DBA 對段的管理。

段其實不對應表空間中某一個連續的物理區域，而是一個邏輯上的概念，由若干個零散的頁面以及一些完整的區組成。

為什麼要有碎片區

預設情況下，一個使用 InnoDB 儲存引擎的表只有一個聚簇索引，一個索引會生成 2 個段，而段是以區為單位申請儲存空間的，一個區預設佔用 1 MB 的儲存空間，所以預設情況下，一個只存了幾條記錄的小表也需要 2 MB 的儲存空間嗎？以後每次新增一個索引都需要多申請 2 MB 的儲存空間嗎？這對於儲存記錄比較少的表簡直是極大的浪費。而這個問題的癥結在於，到現在為止介紹的區都是純粹的，也就是一個區被整個分配給某一個段，或者說區中的所有頁面，都是為了儲存同一個段的資料而存在的，即使段的資料填不滿區中所有的頁面，那餘下的頁面也不能挪作他用。

為了避免以完整的區為單位分配給某個段，造成資料量較小的表太浪費儲存空間的這種情況，InnoDB 提出了一個碎片區（fragment）的概念。在一個碎片區中，並不是所有的頁都是為了儲存同一個段的資料而存在的，而是碎片區中的頁可以用於不同的目的，比如有些頁用於段 A，有的頁用於段 B，有些頁甚至哪個段都不屬於。碎片區直屬於表空間，不屬於任何一個段。

所以，此後為某個段分配儲存空間的策略是這樣的：

• 在剛開始向表中插入資料的時候，段是從某個碎片區以單個頁面為單位來分配儲存空間的。
• 當某個段已經佔用了 32 個碎片區頁面後，就會申請以完整的區為單位來分配儲存空間。

所以，現在段不能僅定義為是某些區的集合，更精確的說法是某些零散的頁面以及一些完整的區的集合。

區的分類

區大體上可以分為 4 種型別：

• 空閒的區（FREE）：現在還沒有用到這個區中的任何頁面。
• 有剩餘空間的碎片區（FREE_FRAG）：表示碎片區中還有可用的頁面。
• 沒有剩餘空間的碎片區（FULL_FRAG）：表示碎片區中的所有頁面都被使用，沒有空閒頁面。
• 附屬於某個段的區（FSEG）：每一個索引都可以分為葉子節點段和非葉子節點段。

處於 FREE、FREE_FRAG 和 FULL_FRAG 這三種狀態的區都是獨立的，直屬於表空間，而處於 FSEG 狀態的區是附屬於某個段的。

如果把表空間比作是一個集團軍，段就相當於師，區就相當於團。一般的團都是隸屬於某個師的，就像是處於 FSEG 的區全都隸屬於某個段，而處於 FREE、FREE_FRAG 和 FULL_FRAG 這三種狀態的區，是直接隸屬於表空間，就像獨立團直接聽命于軍部一樣。

表空間

表空間可以看作是 InnoDB 儲存引擎邏輯結構的最高層，所有的資料都存放在表空間中。

表空間是一個邏輯容器，表空間儲存的物件是段，在一個表空間中可以有一個或多個段，但是一個段只能屬於一個表空間。表空間資料庫由一個或多個表空間組成，表空間從管理上可以劃分為系統表空間（System Tablespace）、獨立表空間（File-per-table Tablespace）、撤銷表空間（Undo Tablespace）和臨時表空間（Temporary Tablespace）等。

獨立表空間

獨立表空間，即每張表有一個獨立的表空間，也就是資料和索引資訊都會儲存在自己的表空間中。獨立的表空間（即：單表）可以在不同的資料庫之間進行遷移。

空間可以回收（DROP TABLE 操作可以自動回收表空間，其他情況，表空間不能自己回收），如果對於統計分析或是日誌表，刪除大量資料後可以透過ALTER TABLE tablename ENGINE=InnoDB回收不用的空間。對於使用獨立表空間的表，不管怎麼刪除，表空間的碎片不會太嚴重的影響效能，而且還有機會處理。

獨立表空間結構

獨立表空間由段、區、頁組成。

真實表空間對應的檔案大小

到資料目錄裡面檢視，會發現一個新建的表對應的 .ibd 檔案只佔用了 96 KB，才 6 個頁面大小（MySQL 5.7 中），這是因為一開始表裡面沒有資料，表空間佔用的空間很小。但是 .ibd 檔案是自擴充套件的，隨著表中資料的增多，表空間對應的檔案也逐漸增大。

檢視 InnoDB 的表空間型別

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

innodb_file_per_table=ON，這意味著每張表都會單獨儲存一個 .ibd 檔案。

系統表空間

系統表空間的結構和獨立表空間基本類似，只不過由於整個 MySQL 程序只有一個系統表空間，在系統表空間中會額外記錄一些有關整個系統資訊的頁面，這部分是獨立表空間中沒有的。

InnoDB 資料字典

每當我們向一個表中插入一條記錄的時候，MySQL 校驗過程如下：先要校驗一下插入語句對應的表存不存在；插入的列和表中的列是否符合；如果語法沒有問題的話，還需要知道該表的聚簇索引和所有二級索引對應的根頁面是哪個表空間的哪個頁面，然後把記錄插入對應索引的 B+Tree 中。所以說，MySQL 除了儲存著我們插入的使用者資料之外，還需要儲存許多額外的資訊，比如：

• 某個表屬於哪個表空間，表裡面有多少列。
• 表對應的每一個列的型別是什麼。
• 該表有多少索引，每個索引對應哪幾個欄位，該索引對應的根頁面在哪個表空間的哪個頁面。
• 該表有哪些外來鍵，外來鍵對應哪個表的哪些列。
• 某個表空間對應檔案系統上檔案路徑是什麼。
• ...

上面這些資料，並不是使用 INSERT 語句插入的使用者資料，實際上是為了更好的管理我們這些使用者資料而不得已引入的一些額外資料，這些資料也稱為後設資料。InnoDB 儲存引擎特意定義了一些列的內部系統表（Internal System Table）來記錄這些後設資料：

表名	描述
SYS_TABLES	整個 InnoDB 儲存引擎中所有的表的資訊
SYS_COLUMNS	整個 InnoDB 儲存引擎中所有的列的資訊
SYS_INDEXES	整個 InnoDB 儲存引擎中所有的索引的資訊
SYS_FIELDS	整個 InnoDB 儲存引擎中所有的索引對應的列的資訊
SYS_FOREIGN	整個 InnoDB 儲存引擎中所有的外來鍵的資訊
SYS_FOREIGN_COLS	整個 InnoDB 儲存引擎中所有的外來鍵對應列的資訊
SYS_TABLESPACES	整個 InnoDB 儲存引擎中所有的表空間資訊
SYS_DATAFILES	整個 InnoDB 儲存引擎中所有的表空間對應檔案系統的檔案路徑資訊
SYS_VIRTUAL	整個 InnoDB 儲存引擎中所有的虛擬生成列的資訊

這些系統表也被稱為資料字典，它們都是以 B+Tree 的形式儲存在系統表空間的某些頁面中，其中SYS_TABLES、SYS_COLUMNS、SYS_INDEXES和SYS_FIELDS這四個表尤為重要，稱之為基本系統表（Basic System Tables）。

SYS_TABLES 表結構：

列名	描述
NAME	表的名稱，主鍵
ID	InnoDB 儲存引擎中每個表都有一個唯一的 ID
N_COLS	該表擁有列的個數
TYPE	表的型別，記錄了一些檔案格式、行格式、壓縮等資訊
MIX_ID	已過時，忽略
MIX_LEN	表的一些額外的屬性
CLUSTER_ID	未使用，忽略
SPACE	該表所屬表空間的 ID

SYS_COLUMNS 表結構：

列名	描述
TABLE_ID	該列所屬表對應的 ID。與 POS 一起構成聯合主鍵
POS	該列在表中是第幾列
NAME	該列的名稱
MTYPE	main data type，主資料型別，比如 INT、CHAR、VARCHAR、FLOAT、DOUBLE 等
PRTYPE	precise type，精確資料型別，就是修飾資料型別的，比如是否允許 NULL 值，是否允許負數等
LEN	該列最多佔用儲存空間的位元組數
PREC	該列的精度，不過這列可能都沒有使用，預設值都是 0

SYS_INDEXES 表結構：

列名	描述
TABLE_ID	該索引所屬表對應的 ID，與 ID 一起構成聯合主鍵
ID	InnoDB 儲存引擎中每個索引都有一個唯一的 ID
NAME	該索引的名稱
N_FIELDS	該索引包含列的個數
TYPE	該索引的型別，比如聚簇索引、唯一索引、更改緩衝區的索引、全文索引、普通的二級索引等
SPACE	該索引根頁面所在的表空間 ID
PAGE_NO	該索引根頁面所在的頁面號
MERGE_THRESHOLD	如果頁面中的記錄被刪除到某個比例，就把該頁面和相鄰頁面合併，這個值就是這個比例

SYS_FIELDS 表結構：

列名	描述
INDEX_ID	該索引列所屬的索引的 ID，與 POS 一起構成聯合主鍵
POS	該索引列在某個索引中是第幾列
COL_NAME	該索引列的名稱

附錄：資料頁載入的三種方式

InnoDB 從磁碟中讀取資料的最小單位是資料頁，對於 MySQL 存放的資料，邏輯概念上稱之為表，在磁碟等物理層面而言，是按資料頁形式進行存放的，當其載入到 MySQL 中後，稱之為快取頁。如果緩衝池中沒有該頁資料，那麼緩衝池有以下三種方式讀取資料，每種方式的讀取效率也是不同的。

記憶體讀取

如果該資料存在於記憶體中，基本上執行時間在 1 ms 左右，效率比較高。

隨機讀取

如果資料沒有在記憶體中，就需要在磁碟上對該頁進行查詢，整體時間預估在 10 ms 左右，這 10 ms 中約有 6 ms 是磁碟的實際繁忙實際（包括尋道和半圈旋轉時間），有 3 ms 是對可能發生的排隊時間的估計值，另外還有 1 ms 的傳輸時間，將頁從磁碟伺服器緩衝區傳輸到資料庫緩衝區中。這 10 ms 看起來很快，但實際上對於資料庫來說消耗的時間已經很長了，因為這還只是一個頁的讀取時間。

順序讀取

順序讀取其實是一種批次讀取的方式，因為請求的資料在磁碟上往往都是相鄰儲存的，順序讀取可以批次讀取頁面，這樣的話，一次性載入到緩衝池中就不需要再對其他頁面單獨進行磁碟 I/O 操作。如果一個磁碟的吞吐量是 40 MB/S，那麼對於一個 16 KB 大小的頁來說，一次可以順序讀取 2560 （40 MB / 16 KB）個頁，相當於一個頁的讀取時間為 0.4 ms。採用批次讀取的方式，即使是從磁碟上進行讀取，效率也比從記憶體中只單獨讀取一個頁的效率要高。

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql.md