深入理解MySQL數(shù)據(jù)行溢出

本文主要給大家簡(jiǎn)單講講深入理解MySQL數(shù)據(jù)行溢出，相關(guān)專(zhuān)業(yè)術(shù)語(yǔ)大家可以上網(wǎng)查查或者找一些相關(guān)書(shū)籍補(bǔ)充一下，這里就不涉獵了，我們就直奔主題吧，希望深入理解MySQL數(shù)據(jù)行溢出這篇文章可以給大家?guī)?lái)一些實(shí)際幫助。                                                            

一、從常見(jiàn)的報(bào)錯(cuò)說(shuō)起

故事的開(kāi)頭我們先來(lái)看一個(gè)常見(jiàn)的sql報(bào)錯(cuò)信息：

相信對(duì)于這類(lèi)報(bào)錯(cuò)大家一定遇到過(guò)很多次了，特別對(duì)于OMG這種已內(nèi)容生產(chǎn)為主要工作核心的BG，在內(nèi)容線(xiàn)的存儲(chǔ)中，數(shù)據(jù)大一定是個(gè)繞不開(kāi)的話(huà)題。這里的數(shù)據(jù)“大”，遠(yuǎn)不止存儲(chǔ)空間占用多，其中也包括了單個(gè)（表）字段存儲(chǔ)多、大，數(shù)據(jù)留存時(shí)間長(zhǎng)，數(shù)據(jù)冗余多，冷熱數(shù)據(jù)不明顯導(dǎo)致的體量大，訪(fǎng)問(wèn)峰值隨著熱點(diǎn)變化明顯，邏輯處理復(fù)雜導(dǎo)致數(shù)據(jù)存儲(chǔ)壓力放大等等?；氐竭@個(gè)報(bào)錯(cuò)的問(wèn)題上來(lái)，我們先來(lái)看一下這個(gè)表的結(jié)構(gòu)：

看到這里，我相信大家會(huì)有不同的處理方式了，這里就不對(duì)各種處理方式的優(yōu)劣做比較了，僅僅敘述使用頻率較高的兩種處理方式。

• 根據(jù)報(bào)錯(cuò)的指引，把兩個(gè)大的varchar（22288）改成text、blob

• 根據(jù)業(yè)務(wù)特點(diǎn)，縮小varchar的存儲(chǔ)長(zhǎng)度,或者按照規(guī)則拆分成多個(gè)小的vachar和char

這兩種的處理方式也各有優(yōu)缺點(diǎn)，把字段改成text或者blob，不僅增大了數(shù)據(jù)存儲(chǔ)的容量，對(duì)這個(gè)字段的索引頁(yè)只能采用前綴或者全文索引了，如果業(yè)務(wù)側(cè)存儲(chǔ)的是json格式的數(shù)據(jù)，5.7支持json數(shù)據(jù)類(lèi)型是個(gè)不錯(cuò)的選擇，可以針對(duì)單個(gè)子類(lèi)進(jìn)行查詢(xún)和輸出。同樣如果縮小和拆分的話(huà)就比較依賴(lài)業(yè)務(wù)的場(chǎng)景和邏輯需求了，業(yè)務(wù)使用的邏輯上需要修改，工程量也需要評(píng)估。

二、深入探索

接著我們?cè)賮?lái)深入分析下關(guān)于限制大小“65535”的一些容易混淆的概念。

1、“65535”不是單個(gè)varchar(N)中N的最大限制，而是整個(gè)表非大字段類(lèi)型的字段的bytes總合。

---------------------------------------------------------------------------------------------

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

---------------------------------------------------------------------------------------------

2、不同的字符集對(duì)字段可存儲(chǔ)的max會(huì)有影響，例如，UTF8字符需要3個(gè)字節(jié)存儲(chǔ)，對(duì)于VARCHAR（255）CHARACTER SET UTF8列，會(huì)占用255×3 =765的字節(jié)。故該表不能包含超過(guò)65,535/765=85這樣的列。GBK是雙字節(jié)的以此類(lèi)推。

3、可變長(zhǎng)度列在評(píng)估字段大小時(shí)還要考慮存儲(chǔ)列實(shí)際長(zhǎng)度的字節(jié)數(shù)。例如，VARCHAR（255）CHARACTER SET UTF8列需要額外的兩個(gè)字節(jié)來(lái)存儲(chǔ)值長(zhǎng)度信息，所以該列需要多達(dá)767個(gè)字節(jié)存儲(chǔ)，其實(shí)最大可以存儲(chǔ)65533字節(jié)，剩余兩個(gè)字節(jié)存儲(chǔ)長(zhǎng)度信息。

4、BLOB、TEXT、JSON列不同于varchar、char等字段，列長(zhǎng)度信息獨(dú)立于行長(zhǎng)存儲(chǔ)，可以達(dá)到65535字節(jié)真實(shí)存儲(chǔ)

5、定義NULL列會(huì)降低允許的最大列數(shù)。

• InnoDB表，NULL和NOT NULL列存儲(chǔ)大小是一樣

• MyISAM表，NULL列需要額外的空間記錄其值是否為NULL。每個(gè)NULL需要一個(gè)額外的位（四舍五入到最接近的字節(jié)）。最大行長(zhǎng)度計(jì)算如下：

row length = 1  + (sum of column lengths)   + (number of NULL columns + delete_flag + 7)/8 + (number of variable-length columns)

• 靜態(tài)表，delete_flag = 1，靜態(tài)表通過(guò)在該行記錄一個(gè)位來(lái)標(biāo)識(shí)該行是否已被刪除。

• 動(dòng)態(tài)表，delete_flag = 0，該標(biāo)記存儲(chǔ)在動(dòng)態(tài)行首，動(dòng)態(tài)表具體可以根據(jù)

6、對(duì)于InnoDB表，NULL和NOT NULL列存儲(chǔ)大小是一樣

7、InnoDB允許單表最多1000個(gè)列

8、varchar主鍵只支持不超過(guò)767個(gè)字節(jié)或者768/2=384個(gè)雙字節(jié) 或者767/3=255個(gè)三字節(jié)的字段 而GBK是雙字節(jié)的，UTF8是三字節(jié)的

9、不用的引擎對(duì)索引的限制有區(qū)別

• innodb每個(gè)列的長(zhǎng)度不能大于767 bytes；所有組成索引列的長(zhǎng)度和不能大于3072 bytes

• myisam 每個(gè)列的長(zhǎng)度不能大于1000 bytes，所有組成索引列的長(zhǎng)度和不能大于1000 bytes

三、真正的故障

下面來(lái)說(shuō)下今天遇到的業(yè)務(wù)故障，線(xiàn)上業(yè)出現(xiàn)了大量的如下報(bào)錯(cuò)，導(dǎo)致程序無(wú)法寫(xiě)入數(shù)據(jù)：

按照提示和正常的思路，我們先第一反應(yīng)認(rèn)為業(yè)務(wù)存在如下的問(wèn)題：

• 設(shè)置的表結(jié)構(gòu)中字段超過(guò)了限制

• 某個(gè)字段插入的數(shù)據(jù)長(zhǎng)度超過(guò)了改字段設(shè)置的max值

接著查看了業(yè)務(wù)的庫(kù)表結(jié)構(gòu)，如下：

很快排除了第一個(gè)原因，因?yàn)槭紫葮I(yè)務(wù)的報(bào)錯(cuò)不是在建立表的時(shí)候出現(xiàn)的，如果是表中非大字段之和65535，在建表的時(shí)候就會(huì)出錯(cuò)，而業(yè)務(wù)是在寫(xiě)入的時(shí)候才報(bào)錯(cuò)的，而且通過(guò)庫(kù)表結(jié)構(gòu)也能發(fā)現(xiàn)大量的都是mediumblob類(lèi)型字段，非大字段加起來(lái)遠(yuǎn)小于65535。

接著根據(jù)業(yè)務(wù)提供的具體SQL，appversion、datadata、elt_stamp、id這幾個(gè)非大字段，也并沒(méi)有超過(guò)限制，mediumblob類(lèi)型字段最大可存儲(chǔ)16M，業(yè)務(wù)的數(shù)據(jù)遠(yuǎn)遠(yuǎn)沒(méi)有達(dá)到這個(gè)量級(jí)。按照?qǐng)?bào)錯(cuò)的提示把 appversion、datadata、elt_stamp、id這幾個(gè)非大字段均改成blob類(lèi)型，還是無(wú)法解決。（根據(jù)之前的分析，必然不是問(wèn)題的根源）。

冷靜下來(lái)后，發(fā)現(xiàn)其實(shí)還有個(gè)細(xì)節(jié)被忽略掉了，業(yè)務(wù)的失敗率不是100%，說(shuō)明還是有成功的請(qǐng)求，通過(guò)對(duì)比成功和失敗的sql，發(fā)現(xiàn)果然數(shù)據(jù)量差異的還是mediumblob類(lèi)型字段。那么現(xiàn)在第一個(gè)想到的就是，max_allowed_packet這個(gè)參數(shù)，是不是調(diào)小了，是的單個(gè)請(qǐng)求超過(guò)大小被拒絕了，查了下配置的值（如下圖），配置的大小1G，sql的數(shù)據(jù)長(zhǎng)度遠(yuǎn)沒(méi)有這么大，這個(gè)原因也排除了。

查到這里基本上排除了常見(jiàn)幾個(gè)問(wèn)題，接著再看一下另一個(gè)參數(shù)的限制：innodb_page_size，這個(gè)的默認(rèn)值是16K，每個(gè)page兩行數(shù)據(jù)，所以每行最大8k數(shù)據(jù)。

查看了下數(shù)據(jù)表Row_format是Compact，那么我們可以推斷問(wèn)題的原因應(yīng)該就是innodb默認(rèn)的approach存儲(chǔ)格式會(huì)把每個(gè)blob字段的前864個(gè)字節(jié)存儲(chǔ)在page里，所以blob超過(guò)一定數(shù)量的話(huà)，單行大小就會(huì)超過(guò)8k，所以就報(bào)錯(cuò)了。通過(guò)對(duì)比業(yè)務(wù)寫(xiě)成功和失敗的SQL也應(yīng)征了這個(gè)推論，那么現(xiàn)在要怎么解決這個(gè)問(wèn)題？

• 業(yè)務(wù)拆分表，大字段進(jìn)行分表存儲(chǔ)

• 通過(guò)解決Row_format的存儲(chǔ)方式解決問(wèn)題

  由于業(yè)務(wù)單表的存儲(chǔ)條數(shù)并不大，而且業(yè)務(wù)邏輯不適合拆分，所以我們要在Row_format上來(lái)解決這個(gè)問(wèn)題。

Barracuda文件格式下?lián)碛袃煞N新的行記錄格式Compressed和Dynamic兩種，新的兩種格式對(duì)于存放BLOB的數(shù)據(jù)采用了完全的行溢出的方式，在數(shù)據(jù)頁(yè)中只存放20個(gè)字節(jié)的指針，實(shí)際的數(shù)據(jù)都存放在BLOB Page中。Compressed行記錄格式的另一個(gè)功能就是存儲(chǔ)在其中的數(shù)據(jù)會(huì)以zlib的算法進(jìn)行壓縮。

相關(guān)的變更操作就相對(duì)簡(jiǎn)單了：

1、 修改MySQL全局變量：

SET GLOBAL innodb_file_format='Barracuda';

2、平滑變更原表的屬性：

ROW_FORMAT=COMPRESSED

四、繼續(xù)學(xué)習(xí)

通過(guò)這個(gè)案例我們可以從中提煉出兩個(gè)值得深入研究一下的點(diǎn)：

1、關(guān)于innodb_page_size

從MySQL5.6開(kāi)始，innodb_page_size可以設(shè)置Innodb數(shù)據(jù)頁(yè)為8K,4K，默認(rèn)為16K。這個(gè)參數(shù)在一開(kāi)始初始化時(shí)就要加入my.cnf里，如果已經(jīng)創(chuàng)建了表，再修改，啟動(dòng)MySQL會(huì)報(bào)錯(cuò)。

那么在5.6的版本之前要修改這個(gè)值，怎么辦？那只能是在源碼上做點(diǎn)文章了，然后重新rebuild一下MySQL。

UNIV_PAGE_SIZE是數(shù)據(jù)頁(yè)大小，默認(rèn)的是16K，該值是可以設(shè)置必須為2的次方。對(duì)于該值可以設(shè)置成4k、8k、16k、32K、64K。同時(shí)更改了UNIV_PAGE_SIZE后需要更改UNIV_PAGE_SIZE_SHIFT 該值是2的多少次方為UNIV_PAGE_SIZE，所以設(shè)置數(shù)據(jù)頁(yè)分別情況如下：

接著再來(lái)說(shuō)一下innodb_page_size設(shè)置成不同值的對(duì)于mysql性能上的影響，測(cè)試的表含有1億條記錄，文件大小30G。

①讀寫(xiě)場(chǎng)景（50%讀50%寫(xiě)）

16K，對(duì)CPU壓力較小，平均在20%

8K，CPU壓力為30%~40%，但select吞吐量要高于16K

②讀場(chǎng)景（100%讀）

16K和8K差別不明顯

InnoDB Buffer Pool管理頁(yè)面本身也有代價(jià)，Page數(shù)越多，那么相同大小下，管理鏈表就越長(zhǎng)。因此當(dāng)我們的數(shù)據(jù)行本身就比較長(zhǎng)（大塊插入），更大的頁(yè)面更有利于提升速度，因?yàn)橐粋€(gè)頁(yè)面可以放入更多的行，每個(gè)IO寫(xiě)的大小更大，可以更少的IOPS寫(xiě)更多的數(shù)據(jù)。 當(dāng)行長(zhǎng)超過(guò)8K的時(shí)候，如果是16K的頁(yè)面，就會(huì)強(qiáng)制轉(zhuǎn)換一些字符串類(lèi)型為T(mén)EXT，把字符串主體轉(zhuǎn)移到擴(kuò)展頁(yè)中，會(huì)導(dǎo)致讀取列需要多一個(gè)IO，更大的頁(yè)面也就支持了更大的行長(zhǎng)，64K頁(yè)面可以支持近似32K的行長(zhǎng)而不用使用擴(kuò)展頁(yè)。 但是如果是短小行長(zhǎng)的隨機(jī)讀取和寫(xiě)入，則不適合使用這么大的頁(yè)面，這會(huì)導(dǎo)致IO效率下降，大IO只能讀取到小部分。

2、關(guān)于Row_format

Innodb存儲(chǔ)引擎保存記錄，是以行的形式存放的。在InnoDB 1.0.x版本之前，InnoDB 存儲(chǔ)引擎提供了 Compact 和 Redundant 兩種格式來(lái)存放行記錄數(shù)據(jù)。MySQL 5.1 中的innodb_plugin 引入了新的文件格式：Barracuda，該文件格式擁有新的兩種行格式：compressed和dynamic。并且把 compact 和 redundant 合稱(chēng)為Antelope?？梢酝ㄟ^(guò)命令SHOW TABLE STATUS LIKE 'table_name'；來(lái)查看當(dāng)前表使用的行格式，其中 row_format 列表示當(dāng)前所使用的行記錄結(jié)構(gòu)類(lèi)型。

MySQL 5.6 版本中，默認(rèn) Compact ，msyql 5.7.9 及以后版本，默認(rèn)行格式由innodb_default_row_format變量決定，默認(rèn)值是DYNAMIC，也可以在 create table 的時(shí)候指定ROW_FORMAT=DYNAMIC（通過(guò)這個(gè)可動(dòng)態(tài)調(diào)整表的存儲(chǔ)格式）。如果要修改現(xiàn)有表的行模式為compressed或dynamic，必須先將文件格式設(shè)置成Barracuda（set global innodb_file_format=Barracuda;）。再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效，否則修改無(wú)效卻無(wú)提示。

①compact

如果blob列值長(zhǎng)度 <= 768 bytes，不會(huì)發(fā)生行溢出(page overflow)，內(nèi)容都在數(shù)據(jù)頁(yè)(B-tree Node)；如果列值長(zhǎng)度 > 768字節(jié)，那么前768字節(jié)依然在數(shù)據(jù)頁(yè)，而剩余的則放在溢出頁(yè)(off-page)，如下圖：

上面講的blob或變長(zhǎng)大字段類(lèi)型包括blob、text、varchar，其中varchar列值長(zhǎng)度大于某數(shù)N時(shí)也會(huì)存溢出頁(yè)，在latin1字符集下N值可以這樣計(jì)算：innodb的塊大小默認(rèn)為16kb，由于innodb存儲(chǔ)引擎表為索引組織表，樹(shù)底層的葉子節(jié)點(diǎn)為一雙向鏈表，因此每個(gè)頁(yè)中至少應(yīng)該有兩行記錄，這就決定了innodb在存儲(chǔ)一行數(shù)據(jù)的時(shí)候不能夠超過(guò)8k，減去其它列值所占字節(jié)數(shù)，約等于N。

②compressed或dynamic

對(duì)blob采用完全行溢出，即聚集索引記錄（數(shù)據(jù)頁(yè)）只保留20字節(jié)的指針，指向真實(shí)存放它的溢出段地址：

dynamic行格式，列存儲(chǔ)是否放到off-page頁(yè)，主要取決于行大小，它會(huì)把行中最長(zhǎng)的那一列放到off-page，直到數(shù)據(jù)頁(yè)能存放下兩行。TEXT/BLOB列 <=40 bytes 時(shí)總是存放于數(shù)據(jù)頁(yè)。可以避免compact那樣把太多的大列值放到 B-tree Node，因?yàn)閐ynamic格式認(rèn)為，只要大列值有部分?jǐn)?shù)據(jù)放在off-page，那把整個(gè)值放入都放入off-page更有效。

compressed 物理結(jié)構(gòu)上與dynamic類(lèi)似，但是對(duì)表的數(shù)據(jù)行使用zlib算法進(jìn)行了壓縮存儲(chǔ)。在long blob列類(lèi)型比較多的情況下用，可以降低off-page的使用，減少存儲(chǔ)空間（50%左右，可參見(jiàn)之前“【數(shù)據(jù)庫(kù)評(píng)測(cè)報(bào)告】第三期：innodb、tokudb壓縮性能”報(bào)告中的測(cè)試結(jié)果），但要求更高的CPU，buffer pool里面可能會(huì)同時(shí)存儲(chǔ)數(shù)據(jù)的壓縮版和非壓縮版，所以也多占用部分內(nèi)存。

最后參考了《高性能MySQL》，給出一些使用BLOB這類(lèi)變長(zhǎng)大字段類(lèi)型的建議：

①大字段在InnoDB里可能浪費(fèi)大量空間。例如，若存儲(chǔ)字段值只是比行的要求多了一個(gè)字節(jié)，也會(huì)使用整個(gè)頁(yè)面來(lái)存儲(chǔ)剩下的字節(jié)，浪費(fèi)了頁(yè)面的大部分空間。同樣的，如果有一個(gè)值只是稍微超過(guò)了32個(gè)頁(yè)的大小，實(shí)際上就需要使用96個(gè)頁(yè)面。

②太長(zhǎng)的值可能使得在查詢(xún)中作為WHERE條件不能使用索引，因而執(zhí)行很慢。在應(yīng)用WHERE條件之前，MySQL需要把所有的列讀出來(lái)，所以可能導(dǎo)致MySQL要求InnoDB讀取很多擴(kuò)展存儲(chǔ)，然后檢查WHERE條件，丟棄所有不需要的數(shù)據(jù)。

③一張表里有很多大字段，最好組合起來(lái)單獨(dú)存到一個(gè)列里面。讓所有的大字段共享一個(gè)擴(kuò)展存儲(chǔ)空間，比每個(gè)字段用自己的頁(yè)要好。

④把大字段用COMPRESS()壓縮后再存為BLOB，或者在發(fā)送到MySQL前在應(yīng)用程序中進(jìn)行壓縮，可以獲得顯著的空間優(yōu)勢(shì)和性能收益。

⑤擴(kuò)展存儲(chǔ)禁用了自適應(yīng)哈希，因?yàn)樾枰暾谋容^列的整個(gè)長(zhǎng)度，才能發(fā)現(xiàn)是不是正確的數(shù)據(jù)。

深入理解MySQL數(shù)據(jù)行溢出就先給大家講到這里，對(duì)于其它相關(guān)問(wèn)題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會(huì)捕捉一些行業(yè)新聞及專(zhuān)業(yè)知識(shí)分享給大家的。