從一條資料說起——InnoDB行存盤資料結構

本篇博客參考掘金小冊——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在存盤資料的時候，會把這些資料占用的真實位元組數也保存下來，也就是變長欄位是占用了兩部分空間來存盤的：

1. 真實的資料內容
2. 占用的位元組數

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

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

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

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

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

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

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

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

下面就是規則了：

1. 如果M*W<=255，那么用一個位元組表示字串所占用的位元組數，
2. 如果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值串列都填充完畢是醬紫的效果：

記錄頭資訊

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

1. delete_mask ：標識此條資料是否被洗掉，
2. next_record：下一條資料的位置，
3. 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之前使用的，現在基本不會再使用，這里就不介紹了，

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

標籤：MySQL

上一篇：mysql skip-name-resolve 的解釋

下一篇：mysql備份與恢復