MySQL的varchar水真的太深了，你真的會用嗎？

相關(guān)閱讀：一款神仙接私活低代碼平臺，吊到不行（附源碼）

來源：liuchenyang0515.blog.csdn.net/article/details/117524328

1. InnoDB是干嘛的？

2. InnoDB是如何讀寫數(shù)據(jù)的？

讀寫磁盤的速度非常慢，和內(nèi)存讀寫差了幾個數(shù)量級，所以當(dāng)我們想從表中獲取某些記錄時，InnoDB存儲引擎將數(shù)據(jù)劃分為若干個頁，以頁作為磁盤和內(nèi)存之間交互的基本單位，InnoDB中頁的大小默認為 16 KB。也就是在一般情況下，一次最少從磁盤中讀取16KB的內(nèi)容到內(nèi)存中，或者一次最少把內(nèi)存中的16KB內(nèi)容刷新到磁盤中。

所以當(dāng)你用postman測試一個分頁查詢接口時，發(fā)現(xiàn)第一次打印耗時300 ~ 400ms，往后不停的查找下一頁都是30 ~ 40ms，原因就是第一次請求接口時，讀數(shù)據(jù)庫的時候需要讀磁盤，從磁盤加載16KB的數(shù)據(jù)到內(nèi)存，往后下一頁的數(shù)據(jù)都是從內(nèi)存中獲取，沒有再讀磁盤，除非在內(nèi)存中的16KB的數(shù)據(jù)中找不到，才會再次讀磁盤獲取下一個16KB的數(shù)據(jù)到內(nèi)存中。

我們不討論mysql 8.0舍棄的查詢緩存特性，我測試過mysql 5.7中關(guān)閉了查詢緩存，也仍然是第一次慢，后續(xù)查詢很快，查詢時間相差大概10倍的樣子

溫馨提示：分頁查詢和數(shù)據(jù)庫的一頁16KB中的"頁"是兩個概念。

注意：innodb_page_size變量在服務(wù)器運行過程中不可以更改，只能在第一次初始化MySQL數(shù)據(jù)目錄時指定。所以頁在運行時的大小不可更改。

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

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

MySQL 5+默認行格式都是Dynamic， 在MySQL 5 和 MySQL 8經(jīng)過驗證確實是的。

SHOW VARIABLES LIKE "innodb_default_row_format"

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

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

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

3.1 dynamic——innodb默認行格式

關(guān)于記錄的額外信息這部分，是服務(wù)器為了描述這條記錄而不得不額外添加的一些信息，這些額外信息分為3類，分別是變長字段長度列表、NULL值列表和記錄頭信息。

3.2 innodb怎么知道varchar真正有多長？——變長字段長度列表

一些變長的數(shù)據(jù)類型，比如VARCHAR(M)、各種TEXT類型，各種BLOB類型，變長數(shù)據(jù)類型的字段中存儲多少字節(jié)的數(shù)據(jù)是不固定的，在存儲真實數(shù)據(jù)的時候需要把這些數(shù)據(jù)占用的字節(jié)數(shù)也存起來。

就像設(shè)計String類型，不僅僅是存放真實數(shù)據(jù)的char數(shù)組，還有l(wèi)ength變量去記錄字符串長度。又比如input輸入框最大限制500字，但是你還得有一個變量去統(tǒng)計真實在輸入框內(nèi)有多少字符。同理，varchar也有記錄真實數(shù)據(jù)長度的變量（假設(shè)為L，后文沿用方便描述），L表示varchar真實占用的字節(jié)數(shù)，innodb最多分配2個字節(jié)去表示這個L，就像unsigned short類型，2個字節(jié)，寄存器最多只有16位來讓你存這個長度，所以L記錄范圍是2^16 - 1 = 65535。

這些變長字段(比如varchar)占用的存儲空間分為兩部分：

• 真正的數(shù)據(jù)內(nèi)容部分，放在對應(yīng)的列
• 真實占用的字節(jié)數(shù)，放在變長字段列表部分

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

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

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

各變長字段數(shù)據(jù)占用的字節(jié)數(shù)按照列的順序逆序存放??！

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

首先要理解varchar(M)的M是說字符個數(shù)，而不是字節(jié)。

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

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

varchar是變長的，varchar(64) 能存放0~64個字符不等，并不一定是存了最大64個字符，誰知道這個類型到底存了幾個字符呢？

innodb設(shè)計的時候，就已經(jīng)考慮到了，不過是用字節(jié)作為單位，后續(xù)我們可以根據(jù)對應(yīng)字符集轉(zhuǎn)變?yōu)樽址麃砝斫?，innodb必須記錄變長字段varchar真實占用的字節(jié)數(shù)L。前面說過了，innodb最多分配2個字節(jié)(16個bit位)的空間去記錄這個L。另外，搜索公眾號互聯(lián)網(wǎng)架構(gòu)師后臺回復(fù)“2T”，獲取一份驚喜禮包。

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

1. 假設(shè)某個字符集中最多需要W字節(jié)來表示一個字符

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

• ascii字符集中的W就是1。

2. 對于變長類型VARCHAR(M)來說，這種類型表示能存儲最多M個字符（注意是字符不是字節(jié)）

• 所以這個類型能表示的字符串最多占用的字節(jié)數(shù)就是M × W。

3. 假設(shè)它實際存儲的字符串占用的字節(jié)數(shù)是L。

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

但是！這里強調(diào)是有但是的！

行最大長度是65535字節(jié)，行里面有很多東西，包括變長字段列表、NULL值列表、記錄頭信息。你得考慮該字段如果允許為NULL，NULL值列表會占用一個字節(jié)(只要沒超過8個字段)，每一列字段的變長字段實際長度會花費1~2個字節(jié)，如果該字段的數(shù)據(jù)太大，會變成溢出列，該字段的數(shù)據(jù)會分成很多行存儲（后面會講，你可以看完NULL值列表和溢出列后再回來看這個例子）。所以即便提示16383個字符，你也絕對不可能存到16383。

我做了個測試

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

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

規(guī)則一：如果允許存儲的最大字節(jié)數(shù)M × W <= 255，varchar占用的真實字節(jié)數(shù)L只分配1個字節(jié)來表示。

有人說，允許存儲的最大字節(jié)數(shù)M × W <= 255，即允許存儲的最大字符數(shù) <= ?255 / 4? = 63個時，varchar占用的真實字節(jié)數(shù)L僅分配1個字節(jié)就能表示。這個結(jié)論正確嗎？

顯然錯誤，因為這里255 / 4，你怎么知道每個存儲的一個字符是4個字節(jié)呢？難道全部存的emoji表情？不存字母漢字啥的？

InnoDB在讀記錄的變長字段長度列表時先查看表結(jié)構(gòu)，如果某個變長字段允許存儲的最大字節(jié)數(shù)不大于255時，只用1個字節(jié)來表示真實數(shù)據(jù)占用的字節(jié)。

如果實際存儲字節(jié)L <= 127，varchar占用的真實字節(jié)數(shù)L僅分配1個字節(jié)就能表示。(? … ?表示向下取整)

有人說，實際存儲字節(jié)L <= 127，即實際存儲字符 <= ?127 / 4? = 31個時，varchar占用的真實字節(jié)數(shù)L僅分配1個字節(jié)就能表示。這個結(jié)論正確嗎？

顯然錯誤，因為這里127 / 4，你怎么知道實際存儲的一個字符是4個字節(jié)呢？難道全部存的emoji表情？不存字母漢字啥的？

如果實際存儲字節(jié)L > 127，varchar占用的真實字節(jié)數(shù)L需要分配2個字節(jié)才能表示。

另外需要注意的是，變長字段列表只存儲非NULL的列的長度。

表記錄是這樣的

當(dāng)然，并不是所有記錄都有這個變長字段長度列表部分，比方說表中所有的列都不是變長的數(shù)據(jù)類型或者 所有列的值都是NULL 的話，這一部分就不需要有。實際業(yè)務(wù)開發(fā)中，幾乎沒有不使用varchar的，所以實際開發(fā)中的記錄都會有變長字段長度列表部分

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

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

如果表中的某些列可能存儲NULL值，把這些NULL值都放到記錄的真實數(shù)據(jù)中存儲會很占地方，所以dynamic行格式把這些值為NULL的列統(tǒng)一管理起來，存儲到NULL值列表中，它的處理過程是這樣的：

1.統(tǒng)計表中允許存儲NULL的列有哪些。

主鍵列、被NOT NULL修飾的列都是不可以存儲NULL值的，所以在統(tǒng)計的時候不會把這些列算進去。比方說表test的3個列c1、c3、c4都是允許存儲NULL值的，而c2列是被NOT NULL修飾，不允許存儲NULL值。另外，搜索公眾號互聯(lián)網(wǎng)架構(gòu)師后臺回復(fù)“2T”，獲取一份驚喜禮包。

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

也就是說，表test只有3個字段允許為NULL，對應(yīng)3個二進制位，不足1字節(jié)，那么就在高位補0即可。

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

3.5 某個列數(shù)據(jù)占用的字節(jié)數(shù)非常多怎么辦？——dynamic行格式的溢出列

后續(xù)

如果大家對innodb存儲結(jié)構(gòu)其他行格式感興趣，或者我沒說的記錄頭信息，可以去閱讀《MySQL是怎樣運行的》一書，我和書中不同的是，書中講的Compact格式，字符集是ascii，我選用的是平時開發(fā)中用到的默認dynamic格式，字符集是utf8mb4，字符集變化后所有的數(shù)據(jù)我在文中和圖中都有重新計算。大家平時或許沒關(guān)注過行格式，那么就是按照dynamic格式理解就可以，更貼近實際開發(fā)。

-End-