【MySQL經典案例分析】關於資料行溢位由淺至深的探討

騰訊雲加社群發表於2018-12-27

本文由雲+社群發表

一、從常見的報錯說起

​ 故事的開頭我們先來看一個常見的sql報錯資訊:

img

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

img

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

  • 根據報錯的指引,把兩個大的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 row 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

三、真正的故障

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

img

按照提示和正常的思路,我們先第一反應認為業務存在如下的問題:

  • 設定的表結構中欄位超過了限制
  • 某個欄位插入的資料長度超過了改欄位設定的max值

​ 接著檢視了業務的庫表結構,如下:

img

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

​ 接著根據業務提供的具體SQL,appversion、datadata、elt_stamp、id這幾個非大欄位,也並沒有超過限制,mediumblob型別欄位最大可儲存16M,業務的資料遠遠沒有達到這個量級。按照報錯的提示把 appversion、datadata、elt_stamp、id這幾個非大欄位均改成blob型別,還是無法解決。(根據之前的分析,必然不是問題的根源)。

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

img

​ 查到這裡基本上排除了常見幾個問題,接著再看一下另一個引數的限制: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

四、繼續學習

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

1、關於innodb_page_size

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

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

img

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

img

​ 接著再來說一下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 bytes,不會發生行溢位(page overflow),內容都在資料頁(B-tree Node);如果列值長度 > 768位元組,那麼前768位元組依然在資料頁,而剩餘的則放在溢位頁(off-page),如下圖:

img

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

②compressed或dynamic

對blob採用完全行溢位,即聚集索引記錄(資料頁)只保留20位元組的指標,指向真實存放它的溢位段地址:

img

​ dynamic行格式,列儲存是否放到off-page頁,主要取決於行大小,它會把行中最長的那一列放到off-page,直到資料頁能存放下兩行。TEXT/BLOB列 <=40 bytes 時總是存放於資料頁。可以避免compact那樣把太多的大列值放到 B-tree Node,因為dynamic格式認為,只要大列值有部分資料放在off-page,那把整個值放入都放入off-page更有效。

​ 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前在應用程式中進行壓縮,可以獲得顯著的空間優勢和效能收益。

​ ⑤擴充套件儲存禁用了自適應雜湊,因為需要完整的比較列的整個長度,才能發現是不是正確的資料。

此文已由作者授權騰訊雲+社群釋出


相關文章