【迪B課堂】為騰訊云數(shù)據(jù)庫高級產(chǎn)品經(jīng)理迪B哥開設(shè)的面向數(shù)據(jù)庫開發(fā)者、數(shù)據(jù)庫運維人員、云端運維人員的系列培訓(xùn)課程，旨在幫助大家從入門到精通學(xué)習(xí)和使用數(shù)據(jù)庫。

成都創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比茂南網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式茂南網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋茂南地區(qū)。費用合理售后完善，十年實體公司更值得信賴。

本期為迪B課堂特刊【MySQL經(jīng)典案例解析系列】第二期。 搜索關(guān)注“騰訊云數(shù)據(jù)庫”官方微信，回復(fù)“迪B課堂”，即可查看歷史十期迪B課堂教程~

一、從常見的報錯說起

故事的開頭我們先來看一個常見的sql報錯信息：

相信對于這類報錯大家一定遇到過很多次，“數(shù)據(jù)大”也是生產(chǎn)過程中繞不開的一個話題。這里的數(shù)據(jù)“大”，遠不止存儲空間占用多，其中也包括了單個（表）字段存儲多、大，數(shù)據(jù)留存時間長，數(shù)據(jù)冗余多，冷熱數(shù)據(jù)不明顯導(dǎo)致的體量大，訪問峰值隨著熱點變化明顯，邏輯處理復(fù)雜導(dǎo)致數(shù)據(jù)存儲壓力放大等等?；氐竭@個報錯的問題上來，我們先來看一下這個表的結(jié)構(gòu)：

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

• 根據(jù)報錯的指引，把兩個大的varchar（22288）改成text、blob

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

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

二、深入探索

接著我們再來深入分析下關(guān)于限制大小“65535”的一些容易混淆的概念。

1. “65535”不是單個varchar(N)中N的最大限制，而是整個表非大字段類型的字段的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. 不同的字符集對字段可存儲的max會有影響，例如，UTF8字符需要3個字節(jié)存儲，對于VARCHAR（255）CHARACTER SET UTF8列，會占用255×3 =765的字節(jié)。故該表不能包含超過65,535/765=85這樣的列。GBK是雙字節(jié)的以此類推。

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

4. BLOB、TEXT、JSON列不同于varchar、char等字段，列長度信息獨立于行長存儲，可以達到65535字節(jié)真實存儲。

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

• InnoDB表，NULL和NOT NULL列存儲大小是一樣

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

  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)表通過在該行記錄一個位來標(biāo)識該行是否已被刪除。

• 動態(tài)表，delete_flag = 0，該標(biāo)記存儲在動態(tài)行首

6. 對于InnoDB表，NULL和NOT NULL列存儲大小是一樣

7. InnoDB允許單表最多1000個列

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

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

• innodb每個列的長度不能大于767 bytes；所有組成索引列的長度和不能大于3072 bytes

• myisam 每個列的長度不能大于1000 bytes，所有組成索引列的長度和不能大于1000 bytes

三、真正的故障

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

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

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

2. 某個字段插入的數(shù)據(jù)長度超過了改字段設(shè)置的max值

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

很快排除了第一個原因，因為首先業(yè)務(wù)的報錯不是在建立表的時候出現(xiàn)的，如果是表中非大字段之和65535，在建表的時候就會出錯，而業(yè)務(wù)是在寫入的時候才報錯的，而且通過庫表結(jié)構(gòu)也能發(fā)現(xiàn)大量的都是mediumblob類型字段，非大字段加起來遠小于65535。

接著根據(jù)業(yè)務(wù)提供的具體SQL，appversion、datadata、elt_stamp、id這幾個非大字段，也并沒有超過限制，mediumblob類型字段最大可存儲16M，業(yè)務(wù)的數(shù)據(jù)遠遠沒有達到這個量級。按照報錯的提示把 appversion、datadata、elt_stamp、id這幾個非大字段均改成blob類型，還是無法解決（根據(jù)之前的分析，必然不是問題的根源）。

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

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

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

1. 業(yè)務(wù)拆分表，大字段進行分表存儲

2. 通過解決Row_format的存儲方式解決問題

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

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

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

• 修改MySQL全局變量：
  SET GLOBAL innodb_file_format='Barracuda';

• 平滑變更原表的屬性：
  ROW_FORMAT=COMPRESSED

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

通過這個案例我們可以從中提煉出兩個值得深入研究一下的點：

1. 關(guān)于innodb_page_size

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

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

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

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

①讀寫場景（50%讀50%寫）

16K，對CPU壓力較小，平均在20%8K，CPU壓力為30%~40%，但select吞吐量要高于16K

②讀場景（100%讀）

16K和8K差別不明顯

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

2. 關(guān)于Row_format

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

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

①compact

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

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

②compressed或dynamic

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

dynamic行格式，列存儲是否放到off-page頁，主要取決于行大小，它會把行中最長的那一列放到off-page，直到數(shù)據(jù)頁能存放下兩行。TEXT/BLOB列 <=40 bytes 時總是存放于數(shù)據(jù)頁?？梢员苊鈉ompact那樣把太多的大列值放到 B-tree Node，因為dynamic格式認為，只要大列值有部分數(shù)據(jù)放在off-page，那把整個值放入都放入off-page更有效。

compressed 物理結(jié)構(gòu)上與dynamic類似，但是對表的數(shù)據(jù)行使用zlib算法進行了壓縮存儲。在long blob列類型比較多的情況下用，可以降低off-page的使用，減少存儲空間50%左右，但要求更高的CPU，buffer pool里面可能會同時存儲數(shù)據(jù)的壓縮版和非壓縮版，所以也多占用部分內(nèi)存。

五、DBbrain如何輕松處理

數(shù)據(jù)庫智能管家DBbrain對于用戶在數(shù)據(jù)庫中使用BLOB這類變長大字段類型也會根據(jù)具體場景給出如下優(yōu)先建議，幫助用戶更好的規(guī)避一些由于變長大字段帶來的業(yè)務(wù)問題。

1. 大字段在InnoDB里可能浪費大量空間。例如，若存儲字段值只是比行的要求多了一個字節(jié)，也會使用整個頁面來存儲剩下的字節(jié)，浪費了頁面的大部分空間。同樣的，如果有一個值只是稍微超過了32個頁的大小，實際上就需要使用96個頁面。

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

3. 一張表里有很多大字段，建議組合起來單獨存到一個列里面。讓所有的大字段共享一個擴展存儲空間，比每個字段用自己的頁要好。

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

5. 擴展存儲禁用了自適應(yīng)哈希，因為需要完整的比較列的整個長度，才能發(fā)現(xiàn)是不是正確的數(shù)據(jù)。

推薦

式數(shù)據(jù)庫TDSQL在銀行傳統(tǒng)核心系統(tǒng)中的應(yīng)用實踐

800人參賽只有1人全部答對，題目卻是現(xiàn)實中發(fā)生過的故障？

騰訊云國產(chǎn)數(shù)據(jù)庫TBase在保險行業(yè)的應(yīng)用實踐