摘要：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)佔用的儲存空間分為兩部分：
- 真正的資料內容部分，放在對應的列
- 真實佔用的位元組數，放在變長欄位列表部分
我們拿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，每一頁中包含了多行資料以及額外的頁級後設資料。如果一頁中的資料沒有完全填滿這個空間，那麼剩餘的空間就會成為內部碎片，不能被其他行使用。
內部碎片通常在以下情況中出現：
- 固定大小的資料頁/塊：資料庫通常使用固定大小的資料頁（例如，在InnoDB中，頁的大小通常為16KB）來儲存資料。如果一頁中的資料沒有完全填滿這個空間，剩下的空間就會成為內部碎片。
- 資料更新：當一個欄位的值被更新為一個更小的值時，剩下的空間可能會成為內部碎片。資料庫可能會保留這個空間，以便在未來這個欄位的值再次增大時使用。
- 預留空間：為了提高效能，資料庫可能會預留一些空間，使得資料的插入和更新操作不需要立即重新分配空間。這些預留的空間也會成為內部碎片。
舉個例子：
我們建立一個包含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 = REPEAT('a', 10) WHERE id = 1;
在這個例子中，當我們更新data欄位的值時，原來佔用的100個字元的空間並不會立即被收縮，即使新的值只有10個字元。這就產生了內部碎片，即那些不再使用但還未被回收的空間。
這種內部碎片化的影響在實際操作中可能並不那麼明顯，因為資料庫系統會盡可能地重用這些空間。如果後續有新的資料需要更多的空間，這些內部碎片的空間就可能會被利用起來。但是如果後續資料主要進行讀操作而很少進行寫操作的情況下，內部碎片可能會成為影響資料庫效能的一個因素。
假設我們有一個包含大量資料的表，這個表目前主要進行讀操作，而寫操作則相對較少。如果這個表存在大量的內部碎片化（可能是由過去的寫操作留下的，例如更新和刪除），那麼實際儲存的資料可能只佔用了可用空間的一小部分，大量的空間被內部碎片佔用。這種情況下，資料庫需要載入更多的頁到記憶體中來獲取相同量的資料，這會增加I/O操作，從而降低讀操作的效能。
除了內部碎片之外，影響每行實際可用空間的其他因素可能包括以下幾個：
- 後設資料：前文已經介紹，每行的後設資料（包括記錄頭資訊、NULL值列表和變長欄位長度列表）都會佔用一部分空間。
- 行格式：InnoDB的行格式（COMPACT，DYNAMIC或REDUNDANT）會影響每行的實際可用空間。例如，COMPACT格式會更緊湊，因此可能會提供更多的可用空間。
- 溢位頁：對於非常大的欄位（如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型別在後設資料和內部碎片方面有些不同：
- 後設資料：由於CHAR型別是固定長度的，所以它不需要像VARCHAR型別那樣儲存額外的後設資料來表示實際的長度。這意味著對於同樣長度的字串，CHAR型別會使用更少的空間來儲存後設資料。
- 內部碎片：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格式理解就可以，更貼近實際開發。