面試官：MySQL 中的 varchar 最多能存盤多少個字符？大部分人都會答錯。。。

1. InnoDB是干嘛的？

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

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

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

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

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

?

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

?

注意：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，

1. 對于變長型別VARCHAR(M)來說，這種型別表示能存盤最多M個字符（注意是字符不是位元組） 所以這個型別能表示的字串最多占用的位元組數就是M × W，
2. 假設它實際存盤的字串占用的位元組數是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這個邊界值的，

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

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

?

有人說，允許存盤的最大位元組數M × W <= 255，即允許存盤的最大字符數 <= ?255 / 4? = 「63」個時，varchar占用的真實位元組數L僅分配1個位元組就能表示，這個結論正確嗎？
?顯然錯誤，因為這里255 / 「4」，你怎么知道每個存盤的一個字符是4個位元組呢？難道全部存的emoji表情？不存字母漢字啥的？InnoDB在讀記錄的變長欄位長度串列時先查看表結構，如果某個變長欄位允許存盤的最大位元組數不大于255時，只用1個位元組來表示真實資料占用的位元組，

?

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

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

?

有人說，實際存盤位元組L <= 127，即「實際存盤字符」 <= ?127 / 4? = 「31」個時，varchar占用的真實位元組數L僅分配1個位元組就能表示，這個結論正確嗎？
?顯然錯誤，因為這里127 / 「4」，你怎么知道實際存盤的一個字符是4個位元組呢？難道全部存的emoji表情？不存字母漢字啥的？

?

如果實際存盤位元組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值，

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

1. 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 某個列資料占用的位元組數非常多怎么辦？——dynamic行格式的溢位列

如果某個列中存盤的資料占用的位元組數非常多，該列就可能稱為溢位列，

對于占用存盤空間非常多的列，在記錄真實資料時，「該列只會用20位元組空間」，而這20位元組的空間不存盤資料，因為資料都分散存盤在其他幾行中了，這20位元組的空間存盤的是分散行的地址和占用的位元組數，分散行記錄是單鏈表連接的結構，

?

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

來源：https://blog.csdn.net/qq_34115899/article/details/117524328

近期熱文推薦：

1.1,000+ 道 Java面試題及答案整理(2022最新版)

2.勁爆！Java 協程要來了，，，

3.Spring Boot 2.x 教程，太全了！

4.別再寫滿屏的爆爆爆炸類了，試試裝飾器模式，這才是優雅的方式！！

5.《Java開發手冊（嵩山版）》最新發布，速速下載！

覺得不錯，別忘了隨手點贊+轉發哦！

標籤：Java

上一篇：C語言型別(上)

下一篇：基于Apache組件，分析物件池原理