MySQL-07.InnoDB數據存儲結構

C-07.InnoDB數據存儲結構

1.資料庫的存儲結構：頁

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

由於InnoDB是MySQL的預設存儲引擎，所以本章剖析InnoDB存儲引擎的數據存儲結構。

1.1 磁碟與記憶體交互基本單位：頁

InnoDB將數據劃分為若幹個頁，InnoDB中頁的大小預設為16KB。

以頁作為磁碟和記憶體之間交互的基本單位，也就是一次最少從磁碟中讀取16KB的內容到記憶體中，一次最少把記憶體中的16KB內容刷新到磁碟中。也就是說，在資料庫中，不論讀一行，還是讀多行，都是將這些行所在的頁進行載入。也就是說，資料庫管理存儲空間的基本單位是頁(Page)，資料庫O操作的最小單位是頁。一個頁中可以存儲多個行記錄。

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

1.2 頁結構概述

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

1.3 頁的大小

不同的資料庫管理系統(DBMS)的頁大小不同。在MySQL的InnoDB存儲引擎中，預設頁的大小是16KB，我們可以通過下麵的命令來進行查看。

mysql> select @@global.innodb_page_size;
+---------------------------+
| @@global.innodb_page_size |
+---------------------------+
|                     16384 |
+---------------------------+
1 row in set (0.00 sec)

SQL Server中頁的大小為8KB，而在Oracle中我們用術語塊(Block)來代表頁，Oracle支持的塊大小為2KB，4KB，8KB，16KB，32KB和64KB。

1.4 頁的上層結構

另外在資料庫中，還存在著區(Extent)、段(Segment)和表空間(Tablespace)的概念。行，頁，區，段，表空間的關係如下圖

區(Extent)是比頁大一級的存儲結構，在InnoDB存儲引擎中，一個區會分配 64個連續的頁。因為InnoDB中的頁大小預設是16KB，所以一個區的大小是64*16KB= 1MB。

段(Segment)由一個或多個區組成，區在文件系統是一個連續分配的空間(在InnoDB中是連續的64個頁)，不過在段中不要求區與區之間是相鄰的。段是資料庫中的分配單位，不同類型的資料庫對象以不同的段形式存在。當我們創建數據表、索引的時候，就會相應創建對應的段，比如創建一張表時會創建一個表段，創建一個索引時會創建兩個個索引段。

表空間（Tablespace)是一個邏輯容器，表空間存儲的對象是段，在一個表空間中可以有一個或多個段，但是一個段只能屬於一個表空間。資料庫由一個或多個表空間組成，表空間從管理上可以劃分為系統表空間、用戶表空間、撤銷表空間、臨時表空間等。

2.頁的內部結構

頁如果按類型劃分的話，常見的有數據頁(保存B+樹節點)、系統頁、Undo頁和事務數據頁等。數據頁是我們最常使用的頁。

數據頁的16KB大小的存儲空間被劃分為七部分，分別是文件頭(File Header)、頁頭(Page Header)、最大最小記錄(Infimum + supermum)、用戶記錄(User Records)、空閑空間(Free Space)、頁目錄(Page Directory)和文件尾(File Tailer)。

頁結構的示意圖如下

這7部分作用分別如下

把這7個結構分為3個部分

第一部分：File Header(文件頭部)和File Tailer(文件尾部)

File Header(文件頭部)

作用：描述各種頁的通用信息。比如頁的編號，當前頁的上一頁和下一頁。大小36位元組。

FIL_PAGE_OFFSET

每一個頁都有一個唯一的頁號，InnoDB通過頁號可以定位頁。

FIL_PAGE_TYPE

代表當前頁的類型

FIL_PAGE_PREV和FIL_PAGE_NEXT

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

FIL_PAGE_SPACE_OR_CHKSUM

當前頁的校驗和

什麼是校驗和？

就是對於一個很長的位元組串來說，我們會通過某種演算法來計算一個比較短的值來代表這個很長的位元組串，這個比較短的值就稱為校驗和。
在比較兩個很長的位元組串之前，先比較這兩個長位元組串的校驗和，如果校驗和都不一樣，則兩個長位元組串肯定是不同的，所以省去了直接比較兩個比較長的位元組串的時間損耗。

文件頭部和文件尾部都有屬性：FIL_PAGE_SPACE_OR_CHKSUM

作用：InnoDB存儲引擎以頁為單位把數據載入到記憶體中處理，如果該頁中的數據在記憶體中被修改了，那麼在修改後的某個時間需要把數據同步到磁碟中。但是在同步了一半的時候斷電了，造成了該頁傳輸的不完整。

為了檢測一個頁是否完整（也就是在同步的時候有沒有發生只同步一半的尷尬情況），這時可以通過文件尾的校驗和（checksum 值）與文件頭的校驗和做比對，如果兩個值不相等則證明頁的傳輸有問題，需要重新進行傳輸，否則認為頁的傳輸已經完成。

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

FIL_PAGE_LSN

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

File Trailer(文件尾部)

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

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

第二部分：Free Space(空閑空間)、User Records(用戶記錄)和Infimum + Supermum(最小最大記錄)

Free Space(空閑空間)

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

User Records(用戶記錄)

User Records中的這些記錄按照指定的行格式一條一條擺在User Records部分，相互之間形成單鏈表。見下一小結的行格式的記錄。

Infimum + Supermum(最小最大記錄)

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

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

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

第三部分：Page Directory(頁目錄)和Page Header(頁面頭部)

Page Directory(頁目錄)

為什麼需要頁目錄？

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

需求：根據主鍵值查找頁中的某條記錄，如何實現快速查找呢？
SELECT * FROM page_demo WHERE c1 = 3;

方式1：順序查找

從Infimum記錄（最小記錄）開始，沿著鏈表一直往後找，總會找到（或者找不到），在找的時候還能投機取巧，因為鏈表中各個記錄的值是按照從小到大順序排列的，所以當鏈表的某個節點代表的記錄的主鍵值大於你想要查找的主鍵值時，你就可以停止查找了，因為該節點後邊的節點的主鍵值依次遞增。如果一個頁中存儲了非常多的記錄，這麼查找性能很差。

方式2：使用頁目錄，二分法查找

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

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

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

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

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

從這個圖中我們需要註意這麼幾點：

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

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

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

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

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

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

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

• 初始情況下一個數據頁里只有最小記錄和最大記錄兩條記錄，它們分屬於兩個分組。
• 之後每插入一條記錄，都會從頁目錄中找到主鍵值比本記錄的主鍵值大並且差值最小的槽，然後把該槽對應的記錄的n_owned值加1，表示本組內又添加了一條記錄，直到該組中的記錄數等於8個。
• 在一個組中的記錄數等於8個後再插入一條記錄時，會將組中的記錄拆分成兩個組，一個組中4條記錄，另一個5條記錄。這個過程會在頁目錄中新增一個槽來記錄這個新增分組中最大的那條記錄的偏移量。

頁目錄結構下如何快速查找記錄？

現在向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條，所以遍歷一個組中的記錄的代價是很小的。

小結：
在一個數據頁中查找指定主鍵值的記錄的過程分為兩步：

1.通過二分法確定該記錄所在的槽，並找到該槽所在分組中主鍵值最小的那條記錄。

2.通過記錄的next_record屬性遍歷該槽所在的組中的各個記錄。

Page Header(頁面頭部)

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

PAGE_DIRECTION

假如新插入的一條記錄的主鍵值比上一條記錄的主鍵值大，我們說這條記錄的插入方向是右邊，反之則是左邊。用來表示最後一條記錄插入方向的狀態就是PAGE_DIRECTION。

PAGE_N_DIRECTION

假設連續幾次插入新記錄的方向都是一致的，InnoDB會把沿著同一個方向插入記錄的條數記下來，這個條數就用PAGE_N_DIRECTION這個狀態表示。當然，如果最後一條記錄的插入方向改變了的話，這個狀態的值會被清零重新統計。

3.行格式(記錄格式)

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

查看mysql8的預設行格式

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

也可以使用如下語法查看具體表使用的行格式：

show table status like '表名'\G

3.1 指定行格式的語法

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

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.03 sec)

向表中插入兩條記錄：
INSERT INTO record_test_table(col1, col2, col3, col4) 
VALUES
('zhangsan', 'lisi', 'wangwu', 'songhk'), 
('tong', 'chen', NULL, NULL);

3.2 Compact行格式

在MySQL 5.1 版本中，預設設置為Compact行格式。一條完整的記錄其實可以被分為記錄的額外信息和記錄的真實數據兩大部分。

3.2.1 變長欄位長度列表

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表中的各個列都使用的是ascii字元集（每個字元只需要1個位元組來進行編碼）。

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

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

3.2.2 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值列表就如下：

3.2.3 記錄頭信息(Record Header)

新建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)

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

這些記錄頭信息中的各個屬性如下:

簡化後的行格式示意圖

INSERT INTO page_demo 
VALUES
(1, 100, 'song'), 
(2, 200, 'tong'), 
(3, 300, 'zhan'), 
(4, 400, 'lisi');

delete_mask

這個屬性標記著當前記錄是否被刪除，占用1個二進位位。

• 值為0：代表記錄並沒有被刪除
• 值為1：代表記錄被刪除掉了

被刪除的記錄為什麼還在頁中存儲呢？

你以為它刪除了，可它還在真實的磁碟上。這些被刪除的記錄之所以不立即從磁碟上移除，是因為移除它們之後其他的記錄在磁碟上需要重新排列，導致性能消耗。所以只是打一個刪除標記而已，所有被刪除掉的記錄都會組成一個所謂的垃圾鏈表，在這個鏈表中的記錄占用的空間稱之為可重用空間，之後如果有新記錄插入到表中的話，可能把這些被刪除的記錄占用的存儲空間覆蓋掉。

min_rec_mask

B+樹的每層非葉子節點中的最小記錄都會添加該標記，min_rec_mask值為1。插入的四條記錄的min_rec_mask值都是0，意味著它們都不是B+樹的非葉子節點中的最小記錄。

record_type

這個屬性表示當前記錄的類型，一共有4種類型的記錄：
0：表示普通記錄
1：表示B+樹非葉節點記錄
2：表示最小記錄
3：表示最大記錄

從圖中我們也可以看出來，我們自己插入的記錄就是普通記錄，它們的record_type值都是0，而最小記錄和最大記錄的record_type值分別為2和3。至於record_type為1的情況，我們在索引的數據結構章節講過。

heap_no

這個屬性表示當前記錄在本頁中的位置。從圖中可以看出來，我們插入的4條記錄在本頁中的位置分別是：2、3、4、5。

怎麼不見heap_no值為0和1的記錄呢？

MySQL會自動給每個頁裡加了兩個記錄，由於這兩個記錄並不是我們自己插入的，所以有時候也稱為偽記錄或者虛擬記錄。這兩個偽記錄一個代表最小記錄，一個代表最大記錄。最小記錄和最大記錄的heap_no值分別是0和1，也就是說它們的位置最靠前。

n_owned

頁目錄中每個組中最後一條記錄的頭信息中會存儲該組一共有多少條記錄，作為 n_owned 欄位。

詳情見page directory。

next_record

記錄頭信息里該屬性非常重要，它表示從當前記錄的真實數據到下一條記錄的真實數據的地址偏移量。

比如：第一條記錄的next_record值為32，意味著從第一條記錄的真實數據的地址處向後找32個位元組便是下一條記錄的真實數據。

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

刪除操作

從圖中可以看出來，刪除第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.00 sec)

我們看一下記錄的存儲情況：

直接復用了原來被刪除記錄的存儲空間。

說明：

當數據頁中存在多條被刪除掉的記錄時，這些記錄的next_record屬性將會把這些被刪除掉的記錄組成一個垃圾鏈表，以備之後重用這部分存儲空間。

3.2.4 記錄的真實數據

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

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

• 一個表沒有手動定義主鍵，則會選取一個Unique鍵作為主鍵，如果連Unique鍵都沒有定義的話，則會為表預設添加一個名為row_id的隱藏列作為主鍵。所以row_id是在沒有自定義主鍵以及Unique鍵的情況下才會存在的。
• 事務ID和回滾指針在後面的《第14章_MySQL事務日誌》章節中講解。

舉例：分析Compact行記錄的內部結構

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

INSERT INTO mytest
VALUES('a','bb','bb','ccc');
 
INSERT INTO mytest
VALUES('d','ee','ee','fff');
 
INSERT INTO mytest
VALUES('d',NULL,NULL,'fff');

在Windows操作系統下，可以選擇使用notepad++打開表空間文件mytest.ibd這個二進位文件，使用HEX-Editor插件進行二進位to十六進位。內容如下

第一行記錄從00010078開始 第一行記錄值('a','bb','bb','ccc') 其中c3列是定長列char(10)

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值的第三行：

第三行地址，第一行的地址00010078 + 下一行的偏移量 2c = 000100a4 + 第二行的下一行偏移量2b = 000100cf

所以第三行記錄就是從000100d0開始

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值都不占用任何存儲空間。

3.3 Dynamic和Compressed行格式

3.3.1 頁擴展現象

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

很多DBA喜歡MySQL資料庫提供的VARCHAR(M)類型，認為可以存放65535位元組。這是真的嗎？如果我們使用ascii字元集的話，一個字

符就代表一個位元組，我們看看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個位元組。

InnoDB的Compact行格式使用1到2個位元組來記錄變長欄位的長度首碼。具體使用的位元組數取決於欄位的實際長度：

• 如果欄位的最大可能長度小於或等於255位元組，則使用1個位元組來存儲長度首碼。
• 如果欄位的最大可能長度大於255位元組，則使用2個位元組來存儲長度首碼。

CREATE TABLE varchar_size_demo(
  c VARCHAR(65532)
)CHARSET=ascii ROW_FORMAT=Compact;

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

CREATE TABLE varchar_size_demo( 
  c VARCHAR(65533) not null
)CHARSET=ascii ROW_FORMAT=Compact; 

通過上面的案例，我們可以知道一個頁的大小一般是16KB，也就是16384位元組，而一個VARCHAR(M)類型的列就最多可以存儲65533個位元組，這樣就可能出現一個頁存放不了一條記錄，這種現象稱為行溢出。

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

這稱為頁的擴展，舉例如下：

3.3.2 Dynamic和Compressed行格式

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

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

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

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

3.4 Redundant行格式

3.4.1 概述

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

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

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

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

下邊我們從各個方面看一下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個位元組。

3.4.2 記錄頭信息

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

4.區、段與碎片區

4.1 為什麼要有區?

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

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

4.2 為什麼要有段？

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

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

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

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

4.3 為什麼要有碎片區？

預設情況下，一個使用InnoDB存儲引擎的表只有一個聚簇索引，一個索引會生成2個段，而段是以區為單位申請存儲空間的，一個區預設占用1M (64*16Kb= 1024Kb〉存儲空間，所以**預設情況下一個只存了幾條記錄的小表也需要2M的存儲空間麽? **以後每次添加一個索引都要多申請2M的存儲空間麽?這對於存儲記錄比較少的表簡直是天大的浪費。這個問題的癥結在於到現在為止我們介紹的區都是非常純粹的，也就是一個區被整個分配給某一個段，或者說區中的所有頁面都是為了存儲同一個段的數據而存在的，即使段的數據填不滿區中所有的頁面，那餘下的頁面也不能挪作他用。

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

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

• 在剛開始向表中插入數據的時候，段是從某個碎片區以單個頁面為單位來分配存儲空間的。

• 當某個段已經占用了32個碎片區頁面之後，就會申請以完整的區為單位來分配存儲空間。

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

4.4 區的分類

區大體上可以分為4種類型:

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

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

5.表空間

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

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

5.1 獨立表空間

獨立表空間，即每張表有一個獨立的表空間，也就是數據和索引信息都會保存在自己的表空間中。獨立的表空間(即:單表)可以在不同的資料庫之間進行遷移。

空間可以回收(DROP TABLE操作可自動回收表空間; 其他情況，表空間不能自己回收)。如果對於統計分析或是日誌表，刪除大量數據後可以通過: alter table TableName engine=innodb;回收不用的空間。對於使用獨立表空間的表，不管怎麼刪除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。

獨立表空間結構

獨立表空間由段、區、頁組成。前面已經講解過了。

真實表空間對應的文件大小

我們到數據目錄里看，會發現一個新建的表對應的.ibd文件只占用了96K，才6個頁面大小(MysQL5.7中)，這是因為一開始表空間占用的空間很小，因為表裡邊都沒有數據。不過別忘了這些.ibd文件是自擴展的，隨著表中數據的增多，表空間對應的文件也逐漸增大。

查看InnoDB的表空間類型

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

MySQL8.0預設使用的獨立表空間，每張表對應一個.ibd文件。

5.2 系統表空間

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

lnnoDB數據字典

每當我們向一個表中插入一條記錄的時候，MySQL校驗過程如下:

先要校驗一下插入語句對應的表存不存在，插入的列和表中的列是否符合，如果語法沒有問題的話還需要知道該表的聚簇索引和所有二級索引對應的根頁面是哪個表空間的哪個頁面，然後把記錄插入對應索引的B+樹中。所以說，MySQL除了保存著我們插入的用戶數據之外，還需要保存許多額外的信息，比方說:

- 某個表屬於哪個表空間，表裡邊有多少列
- 表對應的每一個列的類型是什麼
- 該表有多少索引，每個索引對應哪幾個欄位，該索引對應的根頁面在哪個表空間的哪個頁面
- 該表有哪些外鍵，外鍵對應哪個表的哪些列
- 某個表空間對應文件系統上文件路徑是什麼
- ...

上述這些數據並不是我們使用INSERT語句插入的用戶數據，實際上是為了更好的管理我們這些用戶數據而不得已引入的一些額外數據，這些數據也稱為元數據。InnoDB存儲引擎特意定義了一些列的內部系統表（internalsystem table)來記錄這些這些元數據:

這些系統表也被稱為數據字典，它們都是以B+樹的形式保存在系統表空間的某些頁面中，其中SYS_TABLES、SYS_COLUNNS、SYS_INDEXES、SYS_FIELDS這四個表尤其重要，稱之為基本系統表(basic system tables) ,我們先看看這4個表的結構:

SYS_TABLES表結構

SYS_COLUMNS表結構

SYS_INDEXES表結構

SYS_FIELDS表結構

附錄：數據頁載入的三種方式

InnoDB從磁碟中讀取數據的最小單位是數據頁。而你想得到的id =xx的數據，就是這個數據頁眾多行中的一行。

對於MySQL存放的數據，邏輯概念上我們稱之為表，在磁碟等物理層面而言是按數據頁形式進行存放的，當其載入到MySQL中我們稱之為緩存頁。

如果緩衝池中沒有該頁數據，那麼緩衝池有以下三種讀取數據的方式，每種方式的讀取效率都是不同的:

1.記憶體讀取

如果該數據存在於記憶體中，基本上指向時間在1ms左右，效率還是很高的。

2.隨機讀取

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

3.順序讀取

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

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。