從一條數據說起——InnoDB行存儲數據結構

本篇博客參考掘金小冊—— "MySQL 是怎樣運行的：從根兒上理解 MySQL" 先給大家講一個故事，我剛參加工作，在一個小作坊裡面當【碼畜】（儘管現在也是），有一天老闆從我背後走過，說了一句舉世震驚的話：我看你們的資料庫和excel一樣，不就是一行行數據，人家excel還可以對單元格進行美化，還有 ...

本篇博客參考掘金小冊——MySQL 是怎樣運行的：從根兒上理解 MySQL

先給大家講一個故事，我剛參加工作，在一個小作坊裡面當【碼畜】（儘管現在也是），有一天老闆從我背後走過，說了一句舉世震驚的話：我看你們的資料庫和excel一樣，不就是一行行數據，人家excel還可以對單元格進行美化，還有各種函數，生成各種報表，你們的資料庫有什麼複雜的？我竟無力反駁。

為什麼要說這個故事呢，當然是為了引出今天的話題——InnoDB行存儲數據結構。

雖然做開發的各位，或多或少都接觸過資料庫，但是資料庫中的一行行數據到底是怎麼存儲的，存儲的格式又是什麼，就不是每個開發都知道的了，資料庫對我們而言就是一個黑盒子，你想打開這個黑盒子一探究竟嗎？【不，我不想，我只想CURD】【不，這不是你的真實想法】。當我們收了快遞，儘管我們已經知道是什麼快遞了，但是我們還是會迫不及待的拆開快遞，更何況，我們面對的是未知的事物，作為人的天性，一定是非常希望可以打開這個黑盒子，更別提充滿好奇心的程式猿了，今天我就帶著打開這神秘的黑盒子。

這次我們打開的黑盒子便是InnoDB存儲數據結構，換而言之，MySql其他的存儲引擎，如Memory，MyISAM不在本次的討論範圍。

InnoDB頁簡介

InnoDB是一個把數據存儲在硬碟的存儲引擎，即使伺服器重啟，數據依然不會丟失，而真正的數據處理是發生在記憶體中的，所以InnoDB需要把硬碟上數據載入到記憶體中，然後在記憶體中進行各種數據處理，最終在某個時機把記憶體中的數據刷新到硬碟。而硬碟的處理速度是很慢很慢的，和記憶體差的太遠了，如果InnoDB每次只從硬碟中讀取一條數據，顯然是不行的，速度會慢死，所以InnoDB會把數據分成若幹頁，以頁作為記憶體和硬碟之間交互的基本單位，說的再直白點：InnoDB讀取數據不是一行一行讀，而是以頁為最小單位讀取數據。預設情況下，一頁是16K，也就是InnoDB讀取數據的數據大小至少是16K。當然這個值是可以被修改的，因為一般情況下，也沒人會修改這個值，所以這裡我就不說明應該怎麼改了。

InnoDB行格式

之所以，文章開頭的老闆會認為資料庫和excel是一樣的，就是因為我們平時基本都是用可視化工具去管理表，去查數據，一個不懂的人乍一看，確實和excel有點像，就是一行一行數據，這些數據在硬碟上存儲格式是需要我們去探究的。

InnoDB提供了4種行格式供我們選擇，分別是Compact、Redundant、Dynamic和Compressed行格式，以後可能會有新的行格式出現，但是區別並不是很大。

我們建表的時候，可以指定某種行格式：

CREATE TABLE table_name (列信息) ROW_FORMAT=行格式名稱

也可以修改已經存在的表的行格式：

ALTER TABLE  table_name ROW_FORMAT=行格式名稱

準備工作

為了後面的故事可以順利展開，我們先來建一張表：

CREATE TABLE  hero(
`x` VARCHAR(10),
`y` VARCHAR(10) NOT NULL,
`z` CHAR(10),
`t` VARCHAR(10)
)CHARSET=ASCII, ROW_FORMAT=COMPACT;

我建了一張表，指定的行格式是COMPACT，採用的字元集是ASCII，也就是我們的中文是無法存進去的，現在我要向這張表添加兩行數據：

INSERT INTO hero(x, y, z, t) VALUES('a', 'bb', 'cccc', 'ddddd'), ('a', 'b', NULL, NULL);

現在表中的數據是這樣的：

表建好了，數據填充好了，下麵我們就來分析下在COMPACT行格式下，數據是如何存儲的吧。

COMPACT行格式

從上圖可以看到，一行數據被分為了兩個部分，一部分是記錄的額外信息，一部分是記錄的真實數據。

記錄額外信息

變長欄位位元組數列表

varchar(X)和char(X)的區別是什麼，相信大家都非常清楚，char是定長的，varchar是變長的，變長欄位中存儲多少位元組的數據不是固定的，所以InnoDB在存儲數據的時候，會把這些數據占用的真實位元組數也保存下來，也就是變長欄位是占用了兩部分空間來存儲的：

真實的數據內容
占用的位元組數

在COMPACT行格式中，把所有的變長欄位所占用的位元組數逆序排放在變長欄位位元組數列表中。

我們先前創建了一張表，還準備了兩條數據，現在我們來看下第一條數據中的變長欄位位元組數列表是什麼醬紫的。

表中有四個欄位，其中x，y，t三個欄位都是變長欄位，所以這三個欄位的位元組數需要保存在變長欄位位元組數列表，數據表採用的字元集是ascii，所以每一個字元占用的位元組數是1，下麵我們來看下第一條數據各個變長欄位所占用的位元組數：

欄位名稱	內容	占用位元組數（十進位）	占用位元組數（十六進位）
x	a	1	0x01
y	bb	2	0x02
t	ddddd	5	0x05

所以，第一行數據x，y，t三個欄位所占用的位元組數分別是1 2 5，但是InnoDB會把所占用的位元組數逆序排放，如果用16進位來表示變長欄位所占用的位元組數就是這樣的效果了：

為了更容易理解、清晰，所以我用了空格來分割，其實是沒有的。

由於數據的長度都比較小，用一個位元組就可以表示，但是如果變長欄位占用的位元組數比較多，就要用兩個位元組來表示了，到底使用一個位元組來表示，還是用兩個位元組來表示，InnoDB有著自己的一套規則。在說這個規則之前，要先說明下規則中用到的三個變數：

W：指定字元集下，一個字元最多需要占用的位元組數。比如，ascii字元集的W是1，GBK字元集的W是2，utf-8字元集的W是3。
M：最多可以存儲多少個字元，varchar(50)的M就是50。
L：實際存儲字元占用了多少位元組。

W*M：指定欄位類型、字元集下，存儲的字元串最多占用的位元組數。

下麵就是規則了：

如果M*W<=255，那麼用一個位元組表示字元串所占用的位元組數。
如果M*W>255，則分為兩種情況：
2.1 如果L<=127，則用一個位元組來表示字元串所占用的位元組數。
2.2 如果L>127，則用兩個位元組來表示字元串所占用的位元組數。

光看規則是不是覺得很繞，總結一下，該可變欄位允許存儲的最大位元組數(W*M)>255，且真實存儲的位元組數(L)超過127，就用兩個位元組來表示字元串所占用的位元組數，否則用一個位元組來表示字元串所占用的位元組數。

我們再來看看第二條數據，欄位t的值是NULL，變長欄位位元組數列表只存儲非NULL列內容占用的位元組數，所以對於第二條數據，變長欄位位元組數列表只要存儲x和y所占用的位元組數即可，填充在變長欄位位元組數列表的效果是醬紫的：

變長欄位位元組數列表不是必須的，如果一個表中所有的欄位都不是變長的，那麼就沒有變長欄位位元組數列表了。

我們建的表採用的字元集是ascii編碼的，一個字元所占用的位元組固定是1，如果我們採用utf-8字元集，一個欄位所占用的位元組就不是固定的了，而是一個範圍：1-3，所以如果我們採用這樣的字元集，char(m)雖然是定長欄位，但是也會被加入到變長欄位位元組數列表中。

NULL值列表

我待過一家公司，對錶設計有非常明確的規定，其中有一條是任何欄位都不允許為NULL，問原因，DBA只是淡淡的說了句，允許為NULL會額外占用一些空間。我也沒有繼續追究下去，就按照規定來唄。下麵我就來揭秘為什麼會有這個蛋疼的規定。

如果表中有欄位允許為NULL，InnoDB就會開闢一塊空間來標識每個欄位實際存儲的數據是不是為NULL，如果表中的欄位都不允許為NULL，那麼這塊空間就不復存在了。

那麼InnoDB開闢出來的那塊空間具體是怎麼回事呢，接下去往下看。

每個允許存儲為NULL的欄位對應一個二進位位：

如果欄位實際存儲的數據不為NULL，二進位是0。
如果欄位實際存儲的數據是NULL，二進位是1。

這裡和變長欄位位元組數列表是一樣的，是逆序排放的。

我們新建的hero表有三個欄位都允許為NULL，所以存在NULL值列表。

我們先來看第一條數據，三個欄位存儲的實際數據都不為NULL，所以用二進位來表示是醬紫的：

但是InnoDB是用整數位元組的二進位位來表示NULL值列表的，現在不足8位，所以要在高位補0，最終用二進位來表示是醬紫的：

所以，對於第一條數據，NULL值列表用十六進位表示是0x00。

我們再來看看第二條數據，其中z和t兩個欄位存儲的實際數據都是NULL，我們來看看用二進位如何來表示：

同樣的，需要高位補0：

所以，對於第二條數據，NULL值列表用十六進位表示是0x06。

我們把兩條數據的NULL值列表都填充完畢是醬紫的效果：

記錄頭信息

記錄頭信息中包含的內容很多，我先隨便列舉幾條：

delete_mask ：標識此條數據是否被刪除。
next_record：下一條數據的位置。
record_type：表示當前記錄的類型，0表示普通記錄，1表示B+樹非葉子節點記錄，2表示最小記錄，3表示最大記錄
...
還有其他的，或者更具體的解釋等以後用到了再說吧。

記錄真實數據

對於hero表來說，記錄真實數據部分除了我們定義的四個欄位，還有三個隱藏欄位，分別為：row_id、trx_id、roll_pointer，我們來看下這三個欄位是什麼。

row_id

如果我們建表的時候指定了主鍵或者唯一約束列，那麼就沒有row_id隱藏欄位了。如果既沒有指定主鍵，又沒有唯一約束，那麼InnoDB就會為記錄添加row_id隱藏欄位。row_id不是必需的，占用6個位元組。

trx_id

事務Id，表示這個數據是由哪個事務生成的。 trx_id是必需的，占用6個位元組。

roll_pointer

這條數據上一個版本的指針。roll_pointer是必需的，占用7個位元組。

關於 trx_id、roll_pointer的具體解釋，在我上一篇關於事務的博客有詳細描述過，感興趣的小伙伴可以找來看看。

VARCHAR(M)最多能存儲的數據

在講可變欄位位元組數列表的時候，講到InnoDB會有一套規則，計算是用一個位元組來表示實際存儲的位元組數，還是用兩個位元組來表示實際存儲的位元組數，但是如果存儲的字元串很長很長，用兩個位元組都無法表示，該怎麼辦呢？

我們先來看看用兩個位元組最多可以表示的位元組數是多少：

用兩個位元組最多可以表示的位元組數是65535。

我們用這個最大位元組數來試下，能不能成功創建一張表：

CREATE TABLE test_max ( test VARCHAR ( 65535 ) ) charset = ascii,
row_format = Compact

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

看到了木有，兩個位元組最多可以表示的位元組數是65535，我們用這個數字創建表竟然失敗了，更別提65536了。

為什麼失敗呢？

從報錯信息就可以知道一行數據的最大位元組數是65535，其中包含了storage overhead。問題來了，這個storage overhead是什麼呢？就是可變欄位位元組數列表、NULL值列表。

我們存儲VARCHAR(M)類型的欄位，其實可能分成了三個部分來存儲：

真實數據
真實數據占用的位元組數
NULL標識，如果不允許為NUL，這部分不需要

剛剛我們嘗試創建的表，欄位是允許為NULL的，所以會占用一個位元組來存儲NULL標識，真實的數據所占的位元組數用兩個位元組來表示，所以最多可以存儲65535-2-1=65532個位元組。

CREATE TABLE test_max ( test VARCHAR ( 65532 ) ) charset = ascii,
row_format = Compact
> OK
> 時間: 0.229s

我們新建的表採用的字元集是ascii，如果採用的是GBK或者UTF-8，VARCHAR(M)最多能存儲的數據計算方式就不一樣了：

在GBK字元集下，一個字元最多需要兩個位元組，VARCHAR(M)的最大取值就是 65532/2=32766。
在UTF-8字元集下，一個字元串最多需要三個位元組，VARCHAR(M)的最大取值就是 65532/3=21844。

我們上面所說的只是針對於一個列的計算方式，如果有多個列的話，要保證多個列所允許占用的最大位元組數+變長欄位位元組數列表所占用的位元組數+NULL值列表所占用的位元組數<=65535。

行溢出

文章開頭的時候，給大家簡單的介紹了下頁的概念，我們知道硬碟和記憶體之間交互的基本單位是頁，而頁的大小預設情況下16K，也就是16384位元組，而VARCHAR(M)最多可以存儲的遠遠不止16384位元組，這樣就出現了一個頁存放不了一條記錄的局面。

在Compact和Redundant行格式中，對於占用位元組數非常大的列，在記錄的真實數據中只會存儲一小部分數據（768個位元組），剩餘的數據分散存儲在其他的頁，為了可以找到它們，在記錄的真實數據中會記錄這些頁的地址，就像下麵醬紫：

Dynamic和Compressed行格式

Dynamic和Compressed行格式和COMPACT行格式很相近，只是在行溢出的處理方式上有所不同，溢出後，Dynamic和Compressed行格式不會在記錄的真實數據中存儲一小部分數據，而是直接記錄其他頁的地址。Dynamic和Compressed行格式的區別是Compressed格式會對頁進行壓縮以節省空間。

Redundant行格式是MySql5.0之前使用的，現在基本不會再使用，這裡就不介紹了。

本章內容到這裡就結束了，下次會介紹關於頁的詳細內容。