摘要：varchar(M) 能存多少個字元，為什麼提示最大16383？innodb怎麼知道varchar真正有多長？記錄為NULL，innodb如何處理？某個列數據占用的位元組數非常多怎麼辦？影響每行實際可用空間的因素有哪些？本篇圍繞innodb預設行格式dynamic來說說原理。 本文分享自華為雲社 ...
摘要：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格式理解就可以，更貼近實際開發。
