MySQL的varchar存盤原理：InnoDB記錄存盤結構

摘要：varchar(M) 能存多少個字符，為什么提示最大16383？innodb怎么知道varchar真正有多長？記錄為NULL，innodb如何處理？某個列資料占用的位元組數非常多怎么辦？影響每行實際可用空間的因素有哪些？本篇圍繞innodb默認行格式dynamic來說說原理，

本文分享自華為云社區《MySQL的varchar水真的太深了——InnoDB記錄存盤結構》，作者：磚業洋__ ，

1. InnoDB是干嘛的？

InnoDB是一個將表中的資料存盤到磁盤上的存盤引擎，

2. InnoDB是如何讀寫資料的？

InnoDB處理資料的程序是發生在記憶體中的，需要把磁盤中的資料加載到記憶體中，如果是處理寫入或修改請求的話，還需要把記憶體中的內容重繪到磁盤上，

讀寫磁盤的速度非常慢，和記憶體讀寫差了幾個數量級，所以當我們想從表中獲取某些記錄時，InnoDB存盤引擎將資料劃分為若干個頁，以頁作為磁盤和記憶體之間互動的基本單位，InnoDB中頁的大小默認為 16 KB，也就是在一般情況下，一次最少從磁盤中讀取16KB的內容到記憶體中，或者一次最少把記憶體中的16KB內容重繪到磁盤中，

所以當你用postman測驗一個HTTP分頁查詢介面（每頁10條資料）時，發現第一次列印耗時300 ~ 400ms，往后不停的查找下一頁10條資料時都是30 ~ 40ms，原因就是第一次請求介面時，讀資料庫的時候需要讀磁盤，從磁盤加載16KB的資料到記憶體，往后HTTP請求每次查10條資料的時候都是從記憶體中獲取，沒有再讀磁盤，除非在記憶體中的16KB的資料中找不到，才會再次讀磁盤獲取下一個16KB的資料到記憶體中，（我們不討論mysql 8.0舍棄的查詢快取特性，我測驗過mysql 5.7中關閉了查詢快取，也仍然是第一次慢，后續查詢很快，查詢時間相差大概10倍的樣子）

溫馨提示：分頁查詢和資料庫的一頁16KB中的"頁"是兩個概念，

總結：由于磁盤I/O速度相對記憶體來說較慢，因此第一次查詢可能會比較耗時，一旦資料被加載到記憶體中，后續的查詢就可以直接從記憶體中讀取資料，這樣的速度要比從磁盤讀取資料快得多，這就解釋了為什么第一次查詢可能會比后續的查詢慢，

查看磁盤和記憶體之間進行資料交換的頁有多大

注意：innodb_page_size變數在服務器運行程序中不可以更改，只能在第一次初始化MySQL資料目錄時指定，所以頁在運行時的大小不可更改，

3. varchar疑問千千萬——InnoDB行格式

看到這里，你一定有著和我相同的疑問，比如varchar(255)后面這個最大長度應該怎么選擇呢？為什么不能varchar(65535)而最大只能varchar(16383)呢？我來帶你看！

我們平時是以記錄為單位來向表中插入資料的，這些記錄在磁盤上的存放方式也被稱為行格式或者記錄格式，行格式有4種，分別是Dynamic、Compact、Redundant和Compressed

MySQL 5+默認行格式都是Dynamic， 在MySQL 5 和 MySQL 8經過驗證確實是的，

SHOW VARIABLES LIKE "innodb_default_row_format"

大家在業務中和平時使用中都幾乎沒有修改過或者注意過InnoDB行格式，那么我就只重點講默認行格式dynamic，讓大家更深層次理解平時開發中的varchar，

請記住這個表結構，后面會圍繞這個來講

CREATE TABLE test ( 
c1 VARCHAR(10), 
c2 VARCHAR(10) NOT NULL, 
c3 CHAR(10), 
c4 VARCHAR(10)) CHARSET = utf8mb4;

現在業務資料庫字符集都是utf8mb4，我就以這個來講，把理解難度降到最低，

INSERT INTO test ( c1, c2, c3, c4 )
VALUES('aaaa', '你好啊', 'cc', 'd'),('eeee', 'fff', NULL, NULL);

現在，表中的記錄就是這樣

3.1 dynamic——innodb默認行格式

關于記錄的額外資訊這部分，是服務器為了描述這條記錄而不得不額外添加的一些資訊，這些額外資訊分為3類，分別是變長欄位長度串列、NULL值串列和記錄頭資訊，

在這里我只講變長欄位長度串列、NULL值串列，因為記錄頭資訊非常的繞和本篇沒多大關系，

3.2 innodb怎么知道varchar真正有多長？——變長欄位長度串列

一些變長的資料型別，比如VARCHAR(M)、各種TEXT型別，各種BLOB型別，變長資料型別的欄位中存盤多少位元組的資料是不固定的，在存盤真實資料的時候需要把這些資料占用的位元組數也存起來，

就像設計String型別，不僅僅是存放真實資料的char陣列，還有length變數去記錄字串長度，又比如input輸入框最大限制500字，但是你還得有一個變數去統計真實在輸入框內有多少字符，同理，varchar也有記錄真實資料長度的變數（假設為L，后文沿用方便描述），L表示varchar真實占用的位元組數，innodb最多分配2個位元組去表示這個L，就像unsigned short型別，2個位元組，暫存器最多只有16位來讓你存這個長度，所以L記錄范圍是2^16 - 1 = 65535，

這些變長欄位(比如varchar)占用的存盤空間分為兩部分：

1. 真正的資料內容部分，放在對應的列
2. 真實占用的位元組數，放在變長欄位串列部分

我們拿test表中的第一條記錄來舉個例子，因為test表的c1、c2、c4列都是VARCHAR(10)型別的，說明最大10個字符，所以這三個列的值的長度都需要保存在記錄開頭處，因為test表中的各個列都使用的是utf8mb4字符集，每個字符最大需要4個位元組來進行編碼（不使用utf8而是utf8mb4是因為可能存盤emoji表情，如果只是文字，utf8就足夠），來看一下第一條記錄各變長欄位內容的長度：

怎么確定這些欄位有多少位元組？

比如這里c2的"你好啊"，使用如下sql可以確定

SELECT LENGTH(c2) from test where c1='aaaa';

各變長欄位資料占用的位元組數按照列的順序逆序存放！！

由于第一行記錄中c1、c2、c4列中的字串都比較短，也就是說varchar真實占用的位元組數比較小，L用1個位元組(8個bit位) 就可以表示，但是如果varchar真實占用的位元組數比較多，L可能就需要用2個位元組(16個bit位) 來表示，到底varchar能存多少位元組呢？繼續往下看，

3.3 varchar(M) 能存多少個字符，為什么提示最大16383？

首先要理解varchar(M)的M是說字符個數，而不是位元組，

為什么不能varchar(20000)之類的，是20000個字符放不下嗎？

為什么提示只能最大16383個字符呢？這個數字是怎么算出來的？

這個我就得和你好好嘮嗑了！

varchar是變長的，varchar(64) 能存放0~64個字符不等，并不一定是存了最大64個字符，誰知道這個型別到底存了幾個字符呢？innodb設計的時候，就已經考慮到了，不過是用位元組作為單位，后續我們可以根據對應字符集轉變為字符來理解，innodb必須記錄變長欄位varchar真實占用的位元組數L，前面說過了，innodb最多分配2個位元組(16個bit位)的空間去記錄這個L，

InnoDB有它的一套規則，我們引入W、M和L這幾個符號：

1.假設某個字符集中最多需要W位元組來表示一個字符

• utf8mb4字符集中的W就是4
• utf8字符集中W就是3
• gbk字符集中的W就是2
• ascii字符集中的W就是1，

2.對于變長型別VARCHAR(M)來說，這種型別表示能存盤最多M個字符（注意是字符不是位元組）
所以這個型別能表示的字串最多占用的位元組數就是M × W，

3.假設它實際存盤的字串占用的位元組數是L，

來看極限邊界情況，innodb為了記錄一下varchar真實存盤多少個位元組，最多分配2個位元組的空間去記錄，2個位元組16個位元位，全部為1，最大能記錄的數字是2^16-1是65535個，innodb最大能記錄varchar占用的位元組數就是65535個，utf8mb4字符集一個字符是最大是4個位元組，65535 / 4 = 16383.75，只要varchar字符數不超過16383個，innodb就可以記錄真實占用的長度L，再多就記錄不了了！所以就能解釋剛剛的圖了，varchar(20000)不行，最大也就16383個字符

但是！這里強調是有但是的！

行最大長度是65535位元組，行里面有很多東西，包括變長欄位串列、NULL值串列、記錄頭資訊，你得考慮該欄位如果允許為NULL，NULL值串列會占用一個位元組(只要沒超過8個欄位)，每一列欄位的變長欄位實際長度會花費1~2個位元組，如果該欄位的資料太大，會變成溢位列，該欄位的資料會分成很多行存盤（后面會講，你可以看完NULL值串列和溢位列后再回來看這個例子），所以即便提示16383個字符，你也絕對不可能存到16383，

我做了個測驗

create table t2 ( name varchar(16383))charset=utf8mb4;

不斷往這個欄位添加字符保存測驗，最后發現，這些字符總長度到極限也就是48545位元組，

如果超過就會報錯

這里48545個位元組，再多一個字符就會報錯，遠不到65535位元組，差了1W多位元組，主要是因為溢位列的原因，資料分散在不同的行中，所以，很長的資料，建議往text型別考慮，這個現象可以看出，varchar(M)的M很大，實際是達不到M這個邊界值的，

我使用的是英文字母測驗而不是中文字符，大部分不是4位元組的，所以能夠存盤更多的字符，如果考慮到額外的元資料，實際能夠存盤的VARCHAR字符數會更少，關于影響每行的實際可用空間有哪些因素，請接著往下看后面小節，

下面說明一下規則(講解中字符集用utf8mb4，W=4)

規則一：如果允許存盤的最大位元組數M × W <= 255，varchar占用的真實位元組數L只分配1個位元組來表示，

有人說，允許存盤的最大位元組數M × W <= 255，即允許存盤的最大字符數 <= ?255 / 4? = 63個時，varchar占用的真實位元組數L僅分配1個位元組就能表示，這個結論正確嗎？

顯然錯誤，因為這里255 / 4，你怎么知道每個存盤的一個字符是4個位元組呢？難道全部存的emoji表情？不存字母漢字啥的？

實際上不是所有的字符都會占用W個位元組，例如，在utf8mb4字符集中，一個英文字母只占用1個位元組，而一個emoji表情符號會占用4個位元組，因此，“最多M個字符”并不意味著總是需要M × W個位元組，
InnoDB在讀記錄的變長欄位長度串列時先查看表結構，如果某個變長欄位允許存盤的最大位元組數不大于255時，只用1個位元組來表示真實資料占用的位元組，

規則二：如果允許存盤的最大位元組數M × W > 255，則分為兩種情況：

如果實際存盤位元組L <= 127，varchar占用的真實位元組數L僅分配1個位元組就能表示，(? … ?表示向下取整)

有人說，實際存盤位元組L <= 127，即實際存盤字符 <= ?127 / 4? = 31個時，varchar占用的真實位元組數L僅分配1個位元組就能表示，這個結論正確嗎？

還是錯誤，道理和上面一樣，

如果實際存盤位元組L > 127，varchar占用的真實位元組數L需要分配2個位元組才能表示，

另外需要注意的是，變長欄位串列只存盤非NULL的列的長度，

表記錄是這樣的

對于第二條記錄，c4列值為NULL，所以只存盤c1和c2列即可，

第一條記錄的變長欄位長度串列部分占用3位元組空間，因為有c1、c2、c4列，且內容都很少，每列真實占用位元組數用1個位元組可以表示，加起來就是3個位元組，第二條記錄變長欄位長度串列部分占用2位元組，

當然，并不是所有記錄都有這個變長欄位長度串列部分，比方說表中所有的列都不是變長的資料型別或者 所有列的值都是NULL 的話，這一部分就不需要有，實際業務開發中，幾乎沒有不使用varchar的，所以實際開發中的記錄都會有變長欄位長度串列部分

3.4 記錄為NULL，innodb如何處理？——NULL值串列

能仔細看到這里，你肯定是個高手了，如果你和我一樣開發規范中不推薦NULL，一般都寫NOT NULL，其實記錄中就不存在NULL值串列了，也節省了空間，

如果表中的某些列可能存盤NULL值，把這些NULL值都放到記錄的真實資料中存盤會很占地方，所以dynamic行格式把這些值為NULL的列統一管理起來，存盤到NULL值串列中，它的處理程序是這樣的：

1.統計表中允許存盤NULL的列有哪些，

主鍵列、被NOT NULL修飾的列都是不可以存盤NULL值的，所以在統計的時候不會把這些列算進去，比方說表test的3個列c1、c3、c4都是允許存盤NULL值的，而c2列是被NOT NULL修飾，不允許存盤NULL值，

2.如果表中沒有允許存盤 NULL 的列，則 NULL值串列也不存在了，否則將每個允許存盤NULL的列對應一個二進制位，二進制位按照列的順序逆序排列，二進制位的值為1時，代表該列的值為NULL，為0時，代表該列的值不為NULL，因為表test的c1、c3、c4都是允許存盤NULL值的允許為NULL的列，所以這3個列和二進制位的對應關系就是這樣：

3.NULL值串列必須用整數個位元組的位表示，如果使用的二進制位個數不是整數個位元組，則在位元組的高位補0，

也就是說，表test只有3個欄位允許為NULL，對應3個二進制位，不足1位元組，那么就在高位補0即可，

以此類推，如果表中有9個欄位都允許為NULL，那么這個記錄的NULL值串列就需要2個位元組來表示，高位元組高位補0，

對于第一條記錄，c1、c3、c4都不為NULL，對應的為進制位為0，十六進制表示就是0x00

對于第二條記錄，c3、c4都是NULL，對應的二進制位為1，十六進制表示就是0x06

這兩條記錄在填充了NULL值串列后示意圖如下：

3.5 為什么varchar(16383)存不到理論字符16383，影響每行實際可用空間的因素有哪些？

在utf8mb4字符集下，VARCHAR(16383)代表的是最多可以存盤16383個字符，由于utf8mb4編碼下，一個字符最多占用4個位元組，所以理論上VARCHAR(16383)最多可以占用 16383 * 4 = 65532位元組，但是還需要考慮到InnoDB的元資料和內部碎片等空間，由于這些額外的開銷，無法在一個VARCHAR(16383)欄位中存盤16383個字符，

內部碎片：內部碎片主要是由于資料庫頁（Page）或塊（Block）的固定大小導致的，InnoDB的頁大小通常設定為16KB，每一頁中包含了多行資料以及額外的頁級元資料，如果一頁中的資料沒有完全填滿這個空間，那么剩余的空間就會成為內部碎片，不能被其他行使用，

內部碎片通常在以下情況中出現：

1. 固定大小的資料頁/塊：資料庫通常使用固定大小的資料頁（例如，在InnoDB中，頁的大小通常為16KB）來存盤資料，如果一頁中的資料沒有完全填滿這個空間，剩下的空間就會成為內部碎片，
2. 資料更新：當一個欄位的值被更新為一個更小的值時，剩下的空間可能會成為內部碎片，資料庫可能會保留這個空間，以便在未來這個欄位的值再次增大時使用，
3. 預留空間：為了提高性能，資料庫可能會預留一些空間，使得資料的插入和更新操作不需要立即重新分配空間，這些預留的空間也會成為內部碎片，

舉個例子：

我們創建一個包含VARCHAR欄位的表：

CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
 data VARCHAR(100)
);

然后我們插入一行資料，其中data欄位填充了100個字符：

INSERT INTO test (data) VALUES (REPEAT('a', 100));

接下來我們更新這行資料，將data欄位的值改為只有10個字符：

UPDATE test SET data = https://www.cnblogs.com/huaweiyun/archive/2023/05/15/REPEAT('a', 10) WHERE id = 1;

在這個例子中，當我們更新data欄位的值時，原來占用的100個字符的空間并不會立即被收縮，即使新的值只有10個字符，這就產生了內部碎片，即那些不再使用但還未被回收的空間，

這種內部碎片化的影響在實際操作中可能并不那么明顯，因為資料庫系統會盡可能地重用這些空間，如果后續有新的資料需要更多的空間，這些內部碎片的空間就可能會被利用起來，但是如果后續資料主要進行讀操作而很少進行寫操作的情況下，內部碎片可能會成為影響資料庫性能的一個因素，

假設我們有一個包含大量資料的表，這個表目前主要進行讀操作，而寫操作則相對較少，如果這個表存在大量的內部碎片化（可能是由過去的寫操作留下的，例如更新和洗掉），那么實際存盤的資料可能只占用了可用空間的一小部分，大量的空間被內部碎片占用，這種情況下，資料庫需要加載更多的頁到記憶體中來獲取相同量的資料，這會增加I/O操作，從而降低讀操作的性能，

除了內部碎片之外，影響每行實際可用空間的其他因素可能包括以下幾個：

1. 元資料：前文已經介紹，每行的元資料（包括記錄頭資訊、NULL值串列和變長欄位長度串列）都會占用一部分空間，
2. 行格式：InnoDB的行格式（COMPACT，DYNAMIC或REDUNDANT）會影響每行的實際可用空間，例如，COMPACT格式會更緊湊，因此可能會提供更多的可用空間，
3. 溢位頁：對于非常大的欄位（如BLOB和TEXT型別），InnoDB可能會將資料存盤在單獨的溢位頁中，而不是直接在資料行中，這可以使得資料行保持較小的大小，但也會增加存盤和檢索這些欄位的復雜性，

以下是一些主要的元資料：

• 記錄頭資訊：每一行記錄在InnoDB中都有一個記錄頭，包含了一些元資料，如記錄型別、下一個記錄的位置等，記錄頭的大小通常為5-7位元組，
• NULL值串列：如果表中的欄位允許NULL值，InnoDB會為每一行記錄維護一個NULL值串列，用于標記哪些欄位的值為NULL，每一個可以為NULL的欄位會在這個串列中占用1位（不是1位元組），所以，如果有n個欄位可以為NULL，那么NULL值串列就需要n位，即?n/8?位元組（向上取整），
• 變長欄位長度串列：對于變長欄位（如VARCHAR、VARBINARY、TEXT和BLOB型別），InnoDB需要存盤每個欄位實際值的長度，如果欄位的最大可能長度不超過255位元組，那么這個長度值會占用1個位元組；如果欄位的最大可能長度超過255位元組，那么長度值可能會占用1個位元組（如果實際長度不超過127位元組）或2個位元組（如果實際長度超過127位元組），

通常來說，內部碎片和元資料可能會對每行的實際可用空間產生最大的影響，

注意：CHAR型別和VARCHAR型別在元資料和內部碎片方面有些不同：

1. 元資料：由于CHAR型別是固定長度的，所以它不需要像VARCHAR型別那樣存盤額外的元資料來表示實際的長度，這意味著對于同樣長度的字串，CHAR型別會使用更少的空間來存盤元資料，
2. 內部碎片：CHAR型別由于是固定長度的，可能會產生內部碎片，比如，如果定義了一個CHAR(100)欄位，但實際上只存盤了10個字符的字串，那么剩下的90個字符的空間就會被浪費，這就是內部碎片，另一方面，VARCHAR型別只會使用實際所需的空間，因此內部碎片會較少，

所以，盡管CHAR型別不需要存盤長度的元資料，但它可能會因為固定長度的特性而產生更多的內部碎片，

在MySQL中，任何型別的列都可以被宣告為NULL或NOT NULL，所以CHAR型別也可以有NULL值串列，

3.6 某個列資料占用的位元組數非常多怎么辦？——dynamic行格式的溢位列

在MySQL 5.7及之后的版本中，默認的行格式是DYNAMIC，在DYNAMIC行格式中，如果一個欄位的大小超過了頁面的可用空間，該欄位就會被存盤為溢位列，

這里是一個例子：

CREATE TABLE `big_data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `data` longblob,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

在這個表中，data列的型別是longblob，這意味著它可以存盤的資料長度最大達到4GB，如果你插入一個大于16KB的資料到這個表，那么data列的資料就會被作為溢位列處理，

INSERT INTO big_data (data) VALUES (REPEAT('a', 17000));

在這個例子中，插入的資料長度超過了16KB，所以data列的資料會被作為溢位列處理，在原始的表中，data列只會存盤一個20位元組的指標，這個指標指向實際資料的存盤位置，

這樣的設計可以確保每個頁內的資料都保持在合理的大小范圍內，避免了由于單個欄位資料過大導致的頁分裂等問題，從而提高了整體的存盤效率和查詢性能，同時，對于讀取溢位列的資料，雖然可能需要額外的磁盤I/O，但只要資料的訪問是順序的，通常這個開銷并不會太大，

后續：如果大家對innodb存盤結構其他行格式感興趣，或者我沒說的記錄頭資訊，可以去閱讀《MySQL是怎樣運行的》一書，我和書中不同的是，書中講的Compact格式，字符集是ascii，我選用的是平時開發中用到的默認dynamic格式，字符集是utf8mb4，對于書中比較難理解的點都舉出了例子，字符集變化后所有的資料我在文中和圖中都有重新計算，大家平時或許沒關注過行格式，那么就是按照dynamic格式理解就可以，更貼近實際開發，

點擊關注，第一時間了解華為云新鮮技術~

標籤：其他

上一篇：架構師日記-從資料庫發展歷程到資料結構設計探析

下一篇：返回列表