MySQL資料行溢出的深入理解

這篇文章帶給大家的內容是關於MySQL資料行溢出的深入理解，有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。

一、從常見的報錯說起

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

#相信對於這類報錯大家一定遇到過很多次了，特別對於OMG這種已內容生產為主要工作核心的BG，在內容線的存儲中，數據大一定是個繞不開的話題。這裡的數據“大”，遠不止存儲空間佔用多，其中也包括了單個（表）字段存儲多、大，數據留存時間長，數據冗餘多，冷熱數據不明顯導致的體量大，訪問峰值隨著熱點變化明顯，邏輯處理複雜導致資料儲存壓力放大等等。回到這個報錯的問題上來，我們先來看一下這個表的架構：

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

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

• #根據業務特點，縮小varchar的儲存長度,或依照規則拆分成多個小的vachar和char

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

二、深入探索

接著我們再來深入分析下關於限制大小「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 #size.--# -------------------------------------------------- ------------------------------------------

2、不同的字元集對字段可儲存的max會有影響，例如，UTF8字元需要3個位元組存儲，對於VARCHAR（255）CHARACTER SET UTF8列，會佔用255×3 =765的位元組。故表格不能包含超過65,535/765=85這樣的欄位。 GBK是雙位元組的以此類推。

3、可變長度列在評估欄位大小時還要考慮儲存列實際長度的位元組數。例如，VARCHAR（255）CHARACTER SET UTF8列需要額外的兩個字節來存儲值長度信息，所以該列需要多達767個字節存儲，其實最大可以存儲65533字節，剩餘兩個字節存儲長度資訊.

4、BLOB、TEXT、JSON列不同於varchar、char等字段，列長度資訊獨立於行長存儲，可以達到65535字節真實存儲

5、定義NULL列會降低允許的最大列數。

InnoDB表，NULL和NOT NULL列儲存大小是一樣
MyISAM表，NULL列需要額外的空間記錄其值是否為NULL。每個NULL需要一個額外的位元（四捨五入到最接近的位元組）。最大行長度計算如下：
row length = 1   (sum of column lengths)   (number of NULL columns delete_flag 7)/8 (number of variable-length columns)

#靜態表，delete_flag = 1，靜態表透過在該行記錄一個位元來標識該行是否已被刪除。
動態表，delete_flag = 0，該標記儲存在動態行首，動態表具體可以根據
6、對於InnoDB表， NULL和NOT NULL列儲存大小是一樣

7、InnoDB允許單表最多1000個欄位

8、varchar主鍵只支援不超過767個位元組或768/2=384個雙位元組或767/3=255個三位元組的欄位而GBK是雙位元組的，UTF8是三位元組的

9、不用的引擎對索引的限制有差別

innodb每個列的長度不能大於767 bytes；所有組成索引列的長度和不能大於3072 bytes
myisam 每個列的長度不能大於1000 bytes，所有組成索引列的長度和不能大於1000 bytes

三、真正的故障

下面來說下今天遇到的業務故障，線上業出現了大量的如下報錯，導致程式無法寫入資料：

依照提示與正常的思路，我們先第一反應認為業務存在如下的問題：

• 設定的表結構中欄位超過了限制

• 某個欄位插入的資料長度超過了改字段設定的max值

##接著查看了業務的庫表結構，如下：

很快很快就排除了第一個原因，因為首先業務的報錯不是在建立表格的時候出現的，如果是表中非大字段之和65535，在建表的時候就會出錯，而業務是在寫入的時候才報錯的，而且透過庫表結構也能發現大量的都是mediumblob類型字段，非大字段加起來遠小於65535。

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

冷靜下來後，發現其實還有個細節被忽略掉了，業務的失敗率不是100%，說明還是有成功的請求，透過對比成功和失敗的sql，發現果然數據量差異的還是mediumblob類型字段。那現在第一個想到的就是，max_allowed_pa​​cket這個參數，是不是調小了，是的單一請求超過大小被拒絕了，查了下配置的值（如下圖），配置的大小1G，sql的資料長度遠沒有這麼大，這個原因也排除了。

查到這裡基本上排除了常見幾個問題，接著再看一下另一個參數的限制：

innodb_page_size，這個的預設值是16K，每個page兩行數據，所以每行最大8k數據。

查看了下資料表Row_format是Compact，那麼我們可以推斷問題的原因應該就是

innodb預設的approach儲存格式會把每個blob欄位的前864個字節儲存在page裡，所以blob超過一定數量的話，單行大小就會超過8k，所以就報錯了。透過比較業務寫成功和失敗的SQL也應徵了這個推論，那麼現在要怎麼解決這個問題？

• 業務分割表，大字段進行分錶儲存

• #透過解決Row_format的儲存方式解決問題
  由於業務單表的儲存條數並不大，而且業務邏輯不適合拆分，所以我們要在Row_format上來解決這個問題。

Barracuda檔案格式下擁有兩種新的行記錄格式Compressed和Dynamic兩種，新的兩種格式對於存放BLOB的資料採用了完全的行溢出的方式，在資料頁中只存放20個位元組的指針，實際的資料都存放在BLOB Page中。 Compressed行記錄格式的另一個功能就是儲存在其中的資料會以zlib的演算法進行壓縮。

相關的變更運算相對簡單了：

1、 修改MySQL全域變數：

##SET GLOBAL innodb_file_format='Barracuda';

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

ROW_FORMAT=COMPRESSED

ROW_FORMAT=COMPRESSED

四、繼續學習

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

1、關於innodb_page_size

#從MySQL5.6開始，innodb_page_size可以設定Innodb資料頁為8K,4K，預設為16K。這個參數在一開始初始化時就要加入my.cnf裡，如果已經建立了表，再修改，啟動MySQL會報錯。

那麼在5.6的版本之前要修改這個值，怎麼辦？那隻能是在原始碼上做點文章了，然後重新rebuild一下MySQL。

######UNIV_PAGE_SIZE是資料頁大小，預設的是16K，該值是可以設定必須為2的次方。對於該值可以設定成4k、8k、16k、32K、64K。同時更改了UNIV_PAGE_SIZE後需要更改UNIV_PAGE_SIZE_SHIFT 該值是2的多少次方為UNIV_PAGE_SIZE，所以設定資料頁分別情況如下：###

接著再來談談innodb_page_size設定成不同值的對於mysql效能上的影響，測試的表格含有1億筆記錄，檔案大小30G。

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

16K，對CPU壓力較小，平均在20%

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

②讀場景（100%讀）

16K和8K差異不明顯

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

2、關於Row_format

Innodb儲存引擎保存記錄，是以行的形式存放的。在InnoDB 1.0.x版本之前，InnoDB 儲存引擎提供了 Compact 和 Redundant 兩種格式來存放行記錄資料。 MySQL 5.1 中的innodb_plugin 引入了新的檔案格式：Barracuda，該檔案格式擁有新的兩種行格式：compressed和dynamic。並且把 compact 和 redundant 合稱為Antelope。可以透過命令SHOW TABLE STATUS LIKE 'table_name'；來查看目前表使用的行格式，其中 row_format 欄位表示目前所使用的行記錄結構類型。

MySQL 5.6 版本中，預設Compact ，msyql 5.7.9 及以後版本，預設行格式由innodb_default_row_format變數決定，預設值是DYNAMIC，也可以在create table 的時候指定ROW_FORMAT=DYNAMIC（透過這個可動態調整表的儲存格式）。如果要修改現有資料表的行模式為compressed或dynamic，必須先將檔案格式設定成Barracuda（set global innodb_file_format=Barracuda;）。再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效，否則修改無效卻無提示。

①compact

如果blob列值長度 > 768字節，那麼前768字節依然在資料頁，而剩餘的則放在溢出頁(off-page)，如下圖：

上面講的blob或變長大欄位類型包括blob、text、varchar，其中varchar列值長度大於某數N時也會存溢位頁，在latin1字元集下N值可以這樣計算：innodb的區塊大小預設為16kb，由於innodb儲存引擎表為索引組織表，樹底層的葉子節點為一雙向鍊錶，因此每個頁至少應該有兩行記錄，這就決定了innodb在儲存一行資料的時候不能夠超過8k，減去其它列值所佔位元組數，約等於N。

②compressed或dynamic

對blob採用完全行溢出，即聚集索引記錄（資料頁）只保留20位元組的指針，指向真實存放它的溢出段位址：

dynamic行格式，列儲存是否放到off-page頁，主要取決於行大小，它會把行中最長的那一列放到off-page，直到資料頁能存放下兩行。 TEXT/BLOB欄位

compressed 物理結構上與dynamic類似，但是對錶的資料行使用zlib演算法進行了壓縮儲存。在long blob列類型比較多的情況下用，可以降低off-page的使用，減少存儲空間（50%左右，可參見之前“【數據庫評測報告】第三期：innodb、tokudb壓縮性能”報告中的測試結果），但要求更高的CPU，buffer pool裡面可能會同時儲存資料的壓縮版和非壓縮版，所以也佔用部分記憶體。

最後參考了《高效能MySQL》，給一些使用BLOB這類變長大欄位類型的建議：

①大字段在InnoDB里可能浪费大量空间。例如，若存储字段值只是比行的要求多了一个字节，也会使用整个页面来存储剩下的字节，浪费了页面的大部分空间。同样的，如果有一个值只是稍微超过了32个页的大小，实际上就需要使用96个页面。

②太长的值可能使得在查询中作为WHERE条件不能使用索引，因而执行很慢。在应用WHERE条件之前，MySQL需要把所有的列读出来，所以可能导致MySQL要求InnoDB读取很多扩展存储，然后检查WHERE条件，丢弃所有不需要的数据。

③一张表里有很多大字段，最好组合起来单独存到一个列里面。让所有的大字段共享一个扩展存储空间，比每个字段用自己的页要好。

④把大字段用COMPRESS()压缩后再存为BLOB，或者在发送到MySQL前在应用程序中进行压缩，可以获得显著的空间优势和性能收益。

⑤扩展存储禁用了自适应哈希，因为需要完整的比较列的整个长度，才能发现是不是正确的数据。

以上是MySQL資料行溢出的深入理解的詳細內容。更多資訊請關注PHP中文網其他相關文章！