MySQL的varchar水真的太深了，你真的會(huì)用嗎？
點(diǎn)擊關(guān)注公眾號(hào)，Java干貨及時(shí)送達(dá)
來(lái)源：liuchenyang0515.blog.csdn.net/article/details/117524328
1. InnoDB是干嘛的？
InnoDB是一個(gè)將表中的數(shù)據(jù)存儲(chǔ)到磁盤上的存儲(chǔ)引擎。
2. InnoDB是如何讀寫數(shù)據(jù)的？
InnoDB處理數(shù)據(jù)的過(guò)程是發(fā)生在內(nèi)存中的，需要把磁盤中的數(shù)據(jù)加載到內(nèi)存中，如果是處理寫入或修改請(qǐng)求的話，還需要把內(nèi)存中的內(nèi)容刷新到磁盤上。
讀寫磁盤的速度非常慢，和內(nèi)存讀寫差了幾個(gè)數(shù)量級(jí)，所以當(dāng)我們想從表中獲取某些記錄時(shí)，InnoDB存儲(chǔ)引擎將數(shù)據(jù)劃分為若干個(gè)頁(yè)，以頁(yè)作為磁盤和內(nèi)存之間交互的基本單位，InnoDB中頁(yè)的大小默認(rèn)為 16 KB。也就是在一般情況下，一次最少?gòu)拇疟P中讀取16KB的內(nèi)容到內(nèi)存中，或者一次最少把內(nèi)存中的16KB內(nèi)容刷新到磁盤中。
所以當(dāng)你用postman測(cè)試一個(gè)分頁(yè)查詢接口時(shí)，發(fā)現(xiàn)第一次打印耗時(shí)300 ~ 400ms，往后不停的查找下一頁(yè)都是30 ~ 40ms，原因就是第一次請(qǐng)求接口時(shí)，讀數(shù)據(jù)庫(kù)的時(shí)候需要讀磁盤，從磁盤加載16KB的數(shù)據(jù)到內(nèi)存，往后下一頁(yè)的數(shù)據(jù)都是從內(nèi)存中獲取，沒有再讀磁盤，除非在內(nèi)存中的16KB的數(shù)據(jù)中找不到，才會(huì)再次讀磁盤獲取下一個(gè)16KB的數(shù)據(jù)到內(nèi)存中。
我們不討論mysql 8.0舍棄的查詢緩存特性，我測(cè)試過(guò)mysql 5.7中關(guān)閉了查詢緩存，也仍然是第一次慢，后續(xù)查詢很快，查詢時(shí)間相差大概10倍的樣子
溫馨提示：分頁(yè)查詢和數(shù)據(jù)庫(kù)的一頁(yè)16KB中的"頁(yè)"是兩個(gè)概念。
注意：innodb_page_size變量在服務(wù)器運(yùn)行過(guò)程中不可以更改，只能在第一次初始化MySQL數(shù)據(jù)目錄時(shí)指定。所以頁(yè)在運(yùn)行時(shí)的大小不可更改。
3. varchar疑問(wèn)千千萬(wàn)——InnoDB行格式
看到這里，你一定有著和我相同的疑問(wèn)，比如varchar(255)后面這個(gè)最大長(zhǎng)度應(yīng)該怎么選擇呢？為什么不能varchar(65535)而最大只能varchar(16383)呢？我來(lái)帶你看！
我們平時(shí)是以記錄為單位來(lái)向表中插入數(shù)據(jù)的，這些記錄在磁盤上的存放方式也被稱為行格式或者記錄格式。行格式有4種，分別是Dynamic、Compact、Redundant和Compressed
MySQL 5+默認(rèn)行格式都是Dynamic， 在MySQL 5 和 MySQL 8經(jīng)過(guò)驗(yàn)證確實(shí)是的。
SHOW?VARIABLES?LIKE?"innodb_default_row_format"
大家在業(yè)務(wù)中和平時(shí)使用中都幾乎沒有修改過(guò)或者注意過(guò)InnoDB行格式，那么我就只重點(diǎn)講默認(rèn)行格式dynamic，讓大家更深層次理解平時(shí)開發(fā)中的varchar。
請(qǐng)記住這個(gè)表結(jié)構(gòu)，后面會(huì)圍繞這個(gè)來(lái)講
CREATE?TABLE?test?(?
c1?VARCHAR(10),?
c2?VARCHAR(10)?NOT?NULL,?
c3?CHAR(10),?
c4?VARCHAR(10))?CHARSET?=?utf8mb4;
現(xiàn)在業(yè)務(wù)數(shù)據(jù)庫(kù)字符集都是utf8mb4，我就以這個(gè)來(lái)講，把理解難度降到最低。
INSERT?INTO?test?(?c1,?c2,?c3,?c4?)
VALUES('aaaa',?'你好啊',?'cc',?'d'),('eeee',?'fff',?NULL,?NULL);
現(xiàn)在，表中的記錄就是這樣
3.1 dynamic——innodb默認(rèn)行格式
關(guān)于記錄的額外信息這部分，是服務(wù)器為了描述這條記錄而不得不額外添加的一些信息，這些額外信息分為3類，分別是變長(zhǎng)字段長(zhǎng)度列表、NULL值列表和記錄頭信息。
在這里我只講變長(zhǎng)字段長(zhǎng)度列表、NULL值列表。因?yàn)橛涗涱^信息非常的繞和本篇沒多大關(guān)系。
3.2 innodb怎么知道varchar真正有多長(zhǎng)？——變長(zhǎng)字段長(zhǎng)度列表
一些變長(zhǎng)的數(shù)據(jù)類型，比如VARCHAR(M)、各種TEXT類型，各種BLOB類型，變長(zhǎng)數(shù)據(jù)類型的字段中存儲(chǔ)多少字節(jié)的數(shù)據(jù)是不固定的，在存儲(chǔ)真實(shí)數(shù)據(jù)的時(shí)候需要把這些數(shù)據(jù)占用的字節(jié)數(shù)也存起來(lái)。
就像設(shè)計(jì)String類型，不僅僅是存放真實(shí)數(shù)據(jù)的char數(shù)組，還有l(wèi)ength變量去記錄字符串長(zhǎng)度。又比如input輸入框最大限制500字，但是你還得有一個(gè)變量去統(tǒng)計(jì)真實(shí)在輸入框內(nèi)有多少字符。同理，varchar也有記錄真實(shí)數(shù)據(jù)長(zhǎng)度的變量（假設(shè)為L(zhǎng)，后文沿用方便描述），L表示varchar真實(shí)占用的字節(jié)數(shù)，innodb最多分配2個(gè)字節(jié)去表示這個(gè)L，就像unsigned short類型，2個(gè)字節(jié)，寄存器最多只有16位來(lái)讓你存這個(gè)長(zhǎng)度，所以L記錄范圍是2^16 - 1 = 65535。
這些變長(zhǎng)字段(比如varchar)占用的存儲(chǔ)空間分為兩部分：
真正的數(shù)據(jù)內(nèi)容部分，放在對(duì)應(yīng)的列 真實(shí)占用的字節(jié)數(shù)，放在變長(zhǎng)字段列表部分
我們拿test表中的第一條記錄來(lái)舉個(gè)例子。因?yàn)閠est表的c1、c2、c4列都是VARCHAR(10)類型的，說(shuō)明最大10個(gè)字符，所以這三個(gè)列的值的長(zhǎng)度都需要保存在記錄開頭處，因?yàn)閠est表中的各個(gè)列都使用的是utf8mb4字符集，每個(gè)字符最大需要4個(gè)字節(jié)來(lái)進(jìn)行編碼（不使用utf8而是utf8mb4是因?yàn)榭赡艽鎯?chǔ)emoji表情，如果只是文字，utf8就足夠），來(lái)看一下第一條記錄各變長(zhǎng)字段內(nèi)容的長(zhǎng)度：
怎么確定這些字段有多少字節(jié)？
比如這里c2的"你好啊"，使用如下sql可以確定
SELECT?LENGTH(c2)?from?test?where?c1='aaaa';
各變長(zhǎng)字段數(shù)據(jù)占用的字節(jié)數(shù)按照列的順序逆序存放??！
由于第一行記錄中c1、c2、c4列中的字符串都比較短，也就是說(shuō)varchar真實(shí)占用的字節(jié)數(shù)比較小，L用1個(gè)字節(jié)(8個(gè)bit位) 就可以表示，但是如果varchar真實(shí)占用的字節(jié)數(shù)比較多，L可能就需要用2個(gè)字節(jié)(16個(gè)bit位) 來(lái)表示。到底varchar能存多少字節(jié)呢？繼續(xù)往下看。
3.3 varchar(M) 能存多少個(gè)字符，為什么提示最大16383？
首先要理解varchar(M)的M是說(shuō)字符個(gè)數(shù)，而不是字節(jié)。
為什么不能varchar(20000)之類的，是20000個(gè)字符放不下嗎？
為什么提示只能最大16383個(gè)字符呢？這個(gè)數(shù)字是怎么算出來(lái)的？
這個(gè)我就得和你好好嘮嗑了！
varchar是變長(zhǎng)的，varchar(64) 能存放0~64個(gè)字符不等，并不一定是存了最大64個(gè)字符，誰(shuí)知道這個(gè)類型到底存了幾個(gè)字符呢？
innodb設(shè)計(jì)的時(shí)候，就已經(jīng)考慮到了，不過(guò)是用字節(jié)作為單位，后續(xù)我們可以根據(jù)對(duì)應(yīng)字符集轉(zhuǎn)變?yōu)樽址麃?lái)理解，innodb必須記錄變長(zhǎng)字段varchar真實(shí)占用的字節(jié)數(shù)L。前面說(shuō)過(guò)了，innodb最多分配2個(gè)字節(jié)(16個(gè)bit位)的空間去記錄這個(gè)L。
InnoDB有它的一套規(guī)則，我們引入W、M和L這幾個(gè)符號(hào)：
假設(shè)某個(gè)字符集中最多需要W字節(jié)來(lái)表示一個(gè)字符 utf8mb4字符集中的W就是4 utf8字符集中W就是3 gbk字符集中的W就是2 ascii字符集中的W就是1。 對(duì)于變長(zhǎng)類型VARCHAR(M)來(lái)說(shuō)，這種類型表示能存儲(chǔ)最多M個(gè)字符（注意是字符不是字節(jié)） 所以這個(gè)類型能表示的字符串最多占用的字節(jié)數(shù)就是M × W。 假設(shè)它實(shí)際存儲(chǔ)的字符串占用的字節(jié)數(shù)是L。
來(lái)看極限邊界情況，innodb為了記錄一下varchar真實(shí)存儲(chǔ)多少個(gè)字節(jié)，最多分配2個(gè)字節(jié)的空間去記錄，2個(gè)字節(jié)16個(gè)比特位，全部為1，最大能記錄的數(shù)字是2^16-1是65535個(gè)，innodb最大能記錄varchar占用的字節(jié)數(shù)就是65535個(gè)，utf8mb4字符集一個(gè)字符是最大是4個(gè)字節(jié)，65535 / 4 = 16383.75，只要varchar字符數(shù)不超過(guò)16383個(gè)，innodb就可以記錄真實(shí)占用的長(zhǎng)度L，再多就記錄不了了！所以就能解釋剛剛的圖了，varchar(20000)不行，最大也就16383個(gè)字符
但是！這里強(qiáng)調(diào)是有但是的！
行最大長(zhǎng)度是65535字節(jié)，行里面有很多東西，包括變長(zhǎng)字段列表、NULL值列表、記錄頭信息。你得考慮該字段如果允許為NULL，NULL值列表會(huì)占用一個(gè)字節(jié)(只要沒超過(guò)8個(gè)字段)，每一列字段的變長(zhǎng)字段實(shí)際長(zhǎng)度會(huì)花費(fèi)1~2個(gè)字節(jié)，如果該字段的數(shù)據(jù)太大，會(huì)變成溢出列，該字段的數(shù)據(jù)會(huì)分成很多行存儲(chǔ)（后面會(huì)講，你可以看完NULL值列表和溢出列后再回來(lái)看這個(gè)例子）。所以即便提示16383個(gè)字符，你也絕對(duì)不可能存到16383。
我做了個(gè)測(cè)試
create?table?t2?(?name?varchar(16383))charset=utf8mb4;
不斷往這個(gè)字段添加字符保存測(cè)試，最后發(fā)現(xiàn)，這些字符總長(zhǎng)度到極限也就是48545字節(jié)。
如果超過(guò)就會(huì)報(bào)錯(cuò)
這里48545個(gè)字節(jié)，再多一個(gè)字符就會(huì)報(bào)錯(cuò)，遠(yuǎn)不到65535字節(jié)，差了1W多字節(jié)。主要是因?yàn)橐绯隽械脑?，?shù)據(jù)分散在不同的行中，所以，很長(zhǎng)的數(shù)據(jù)，建議往text類型考慮。這個(gè)現(xiàn)象可以看出，varchar(M)的M很大，實(shí)際是達(dá)不到M這個(gè)邊界值的。
下面說(shuō)明一下規(guī)則(講解中字符集用utf8mb4，W=4)
規(guī)則一：如果允許存儲(chǔ)的最大字節(jié)數(shù)M × W <= 255，varchar占用的真實(shí)字節(jié)數(shù)L只分配1個(gè)字節(jié)來(lái)表示。
有人說(shuō)，允許存儲(chǔ)的最大字節(jié)數(shù)M × W <= 255，即允許存儲(chǔ)的最大字符數(shù) <= ?255 / 4? = 63個(gè)時(shí)，varchar占用的真實(shí)字節(jié)數(shù)L僅分配1個(gè)字節(jié)就能表示。這個(gè)結(jié)論正確嗎？
顯然錯(cuò)誤，因?yàn)檫@里255 / 4，你怎么知道每個(gè)存儲(chǔ)的一個(gè)字符是4個(gè)字節(jié)呢？難道全部存的emoji表情？不存字母漢字啥的？
InnoDB在讀記錄的變長(zhǎng)字段長(zhǎng)度列表時(shí)先查看表結(jié)構(gòu)，如果某個(gè)變長(zhǎng)字段允許存儲(chǔ)的最大字節(jié)數(shù)不大于255時(shí)，只用1個(gè)字節(jié)來(lái)表示真實(shí)數(shù)據(jù)占用的字節(jié)。
規(guī)則二：如果允許存儲(chǔ)的最大字節(jié)數(shù)M × W > 255，則分為兩種情況：
如果實(shí)際存儲(chǔ)字節(jié)L <= 127，varchar占用的真實(shí)字節(jié)數(shù)L僅分配1個(gè)字節(jié)就能表示。(? … ?表示向下取整)
有人說(shuō)，實(shí)際存儲(chǔ)字節(jié)L <= 127，即實(shí)際存儲(chǔ)字符 <= ?127 / 4? = 31個(gè)時(shí)，varchar占用的真實(shí)字節(jié)數(shù)L僅分配1個(gè)字節(jié)就能表示。這個(gè)結(jié)論正確嗎？
顯然錯(cuò)誤，因?yàn)檫@里127 / 4，你怎么知道實(shí)際存儲(chǔ)的一個(gè)字符是4個(gè)字節(jié)呢？難道全部存的emoji表情？不存字母漢字啥的？
如果實(shí)際存儲(chǔ)字節(jié)L > 127，varchar占用的真實(shí)字節(jié)數(shù)L需要分配2個(gè)字節(jié)才能表示。
另外需要注意的是，變長(zhǎng)字段列表只存儲(chǔ)非NULL的列的長(zhǎng)度。
表記錄是這樣的
對(duì)于第二條記錄，c4列值為NULL，所以只存儲(chǔ)c1和c2列即可。
第一條記錄的變長(zhǎng)字段長(zhǎng)度列表部分占用3字節(jié)空間，因?yàn)橛衏1、c2、c4列，且內(nèi)容都很少，每列真實(shí)占用字節(jié)數(shù)用1個(gè)字節(jié)可以表示，加起來(lái)就是3個(gè)字節(jié)，第二條記錄變長(zhǎng)字段長(zhǎng)度列表部分占用2字節(jié)。
當(dāng)然，并不是所有記錄都有這個(gè)變長(zhǎng)字段長(zhǎng)度列表部分，比方說(shuō)表中所有的列都不是變長(zhǎng)的數(shù)據(jù)類型或者 所有列的值都是NULL 的話，這一部分就不需要有。實(shí)際業(yè)務(wù)開發(fā)中，幾乎沒有不使用varchar的，所以實(shí)際開發(fā)中的記錄都會(huì)有變長(zhǎng)字段長(zhǎng)度列表部分
3.4 記錄為NULL，innodb如何處理？——NULL值列表
能仔細(xì)看到這里，你肯定是個(gè)高手了。如果你和我一樣開發(fā)規(guī)范中不推薦NULL，一般都寫NOT NULL，其實(shí)記錄中就不存在NULL值列表了，也節(jié)省了空間。
如果表中的某些列可能存儲(chǔ)NULL值，把這些NULL值都放到記錄的真實(shí)數(shù)據(jù)中存儲(chǔ)會(huì)很占地方，所以dynamic行格式把這些值為NULL的列統(tǒng)一管理起來(lái)，存儲(chǔ)到NULL值列表中，它的處理過(guò)程是這樣的：
1.統(tǒng)計(jì)表中允許存儲(chǔ)NULL的列有哪些。
主鍵列、被NOT NULL修飾的列都是不可以存儲(chǔ)NULL值的，所以在統(tǒng)計(jì)的時(shí)候不會(huì)把這些列算進(jìn)去。比方說(shuō)表test的3個(gè)列c1、c3、c4都是允許存儲(chǔ)NULL值的，而c2列是被NOT NULL修飾，不允許存儲(chǔ)NULL值。
2.如果表中沒有允許存儲(chǔ) NULL 的列，則 NULL值列表也不存在了，否則將每個(gè)允許存儲(chǔ)NULL的列對(duì)應(yīng)一個(gè)二進(jìn)制位，二進(jìn)制位按照列的順序逆序排列。二進(jìn)制位的值為1時(shí)，代表該列的值為NULL，為0時(shí)，代表該列的值不為NULL。因?yàn)楸韙est的c1、c3、c4都是允許存儲(chǔ)NULL值的允許為NULL的列，所以這3個(gè)列和二進(jìn)制位的對(duì)應(yīng)關(guān)系就是這樣：
3.NULL值列表必須用整數(shù)個(gè)字節(jié)的位表示，如果使用的二進(jìn)制位個(gè)數(shù)不是整數(shù)個(gè)字節(jié)，則在字節(jié)的高位補(bǔ)0。
也就是說(shuō)，表test只有3個(gè)字段允許為NULL，對(duì)應(yīng)3個(gè)二進(jìn)制位，不足1字節(jié)，那么就在高位補(bǔ)0即可。
以此類推，如果表中有9個(gè)字段都允許為NULL，那么這個(gè)記錄的NULL值列表就需要2個(gè)字節(jié)來(lái)表示，高字節(jié)高位補(bǔ)0。
對(duì)于第一條記錄，c1、c3、c4都不為NULL，對(duì)應(yīng)的為進(jìn)制位為0，十六進(jìn)制表示就是0x00
對(duì)于第二條記錄，c3、c4都是NULL，對(duì)應(yīng)的二進(jìn)制位為1，十六進(jìn)制表示就是0x06
這兩條記錄在填充了NULL值列表后示意圖如下：
3.5 某個(gè)列數(shù)據(jù)占用的字節(jié)數(shù)非常多怎么辦？——dynamic行格式的溢出列
如果某個(gè)列中存儲(chǔ)的數(shù)據(jù)占用的字節(jié)數(shù)非常多，該列就可能稱為溢出列。
對(duì)于占用存儲(chǔ)空間非常多的列，在記錄真實(shí)數(shù)據(jù)時(shí)，該列只會(huì)用20字節(jié)空間，而這20字節(jié)的空間不存儲(chǔ)數(shù)據(jù)，因?yàn)閿?shù)據(jù)都分散存儲(chǔ)在其他幾行中了。這20字節(jié)的空間存儲(chǔ)的是分散行的地址和占用的字節(jié)數(shù)。分散行記錄是單鏈表連接的結(jié)構(gòu)。
后續(xù)
如果大家對(duì)innodb存儲(chǔ)結(jié)構(gòu)其他行格式感興趣，或者我沒說(shuō)的記錄頭信息，可以去閱讀《MySQL是怎樣運(yùn)行的》一書，我和書中不同的是，書中講的Compact格式，字符集是ascii，我選用的是平時(shí)開發(fā)中用到的默認(rèn)dynamic格式，字符集是utf8mb4，字符集變化后所有的數(shù)據(jù)我在文中和圖中都有重新計(jì)算。大家平時(shí)或許沒關(guān)注過(guò)行格式，那么就是按照dynamic格式理解就可以，更貼近實(shí)際開發(fā)。
往 期 推 薦
1、致歉！抖音Semi Design承認(rèn)參考阿里Ant Design
2、對(duì)比7種分布式事務(wù)方案，還是偏愛阿里開源的Seata，真香！
點(diǎn)分享
點(diǎn)收藏
點(diǎn)點(diǎn)贊
點(diǎn)在看
