關(guān)于InnoDB存儲引擎text和blob類型的優(yōu)化

我們在數(shù)據(jù)庫優(yōu)化的時候，看到一些表在設(shè)計上使用了text或者blob的字段，如果單表的存儲空間達(dá)到了近上百G或者大幾十G，這種情況再去改變和優(yōu)化就非常難了

一、簡介

為了清楚大字段對性能的影響，我們有必要知道innodb存儲引擎的處理方式：

1、一些知識點(diǎn) 

1.1 在InnoDB 1.0.x版本之前，InnoDB 存儲引擎提供了 Compact 和 Redundant（Redundant 格式是為兼容之前版本而保留的） 兩種格式來存放行記錄數(shù)據(jù)，compact 和 redundant 合稱為Antelope (羚羊)

對于blob，text，varchar(5120)這樣的大字段，innodb只會存放前768字節(jié)在數(shù)據(jù)頁中，而剩余的數(shù)據(jù)則會存儲在溢出段中（發(fā)生溢出情況的時候適用），最大768字節(jié)的作用是便于創(chuàng)建前綴索引/prefix index，其余更多的內(nèi)容存儲在額外的page里，哪怕只是多了一個字節(jié)。因此，所有列長度越短越好

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

• 擴(kuò)展存儲禁用了自適應(yīng)哈希，因為需要完整的比較列的整個長度，才能發(fā)現(xiàn)是不是正確的數(shù)據(jù)（哈希幫助InnoDB非?？焖俚恼业健安聹y的位置”，但是必須檢查“猜測的位置”是不是正確）。因為自適應(yīng)哈希是完全的內(nèi)存結(jié)構(gòu)，并且直接指向Buffer Pool中訪問“最”頻繁的頁面，但對于擴(kuò)展存儲空間卻無法使用Adaptive Hash

1.2 MySQL 5.1 中的 innodb_plugin 引入了新的文件格式：Barracuda (梭子魚)，該文件格式擁有新的兩種行格式：compressed和dynamic，兩種格式對blob字段采用完全溢出的方式，數(shù)據(jù)頁中只存放20字節(jié)，其余的都存放在溢出段中，因此，強(qiáng)烈不建議使用BLOB、TEXT、超過255長度的VARCHAR列類型；

1.3 innodb的page大小默認(rèn)為16kb，innodb存儲引擎表為索引組織表，樹底層的葉子節(jié)點(diǎn)為一雙向鏈表，因此每個頁中至少應(yīng)該有兩行記錄，這就決定了innodb在存儲一行數(shù)據(jù)的時候不能夠超過8k，但事實(shí)上應(yīng)該更小，因為還有一些InnoDB內(nèi)部數(shù)據(jù)結(jié)構(gòu)要存儲，5.6版本以后，新增選項 innodb_page_size 可以修改，在5.6以前的版本，只能修改源碼重新編譯，但并不推薦修改這個配置

1.4 InnoDB的data page在有新數(shù)據(jù)寫入時，會預(yù)留1/16的空間，預(yù)留出來的空間可用于后續(xù)的新紀(jì)錄寫入，減少頻繁的新增data page的開銷，受限于InnoDB存儲方式，數(shù)據(jù)如果是順序?qū)懭氲脑挘罾硐氲那闆r下，data page的填充率是15/16，但一般沒辦法保證完全的順序?qū)懭?，因此data page的填充率一般是1/2到15/16。因此每個InnoDB表都最好要有一個自增列作為主鍵，使得新紀(jì)錄寫入盡可能是順序的；當(dāng)data page填充率不足1/2時，InnoDB會進(jìn)行收縮，釋放空閑空間

1.5 COMPACT行格式相比REDUNDANT，大概能節(jié)省20%的存儲空間，COMPRESSED相比COMPACT大概能節(jié)省50%的存儲空間，但會導(dǎo)致TPS下降了90%。因此強(qiáng)烈不推薦使用COMPRESSED行格式

1.6 使用了blob數(shù)據(jù)類型，是不是一定就會存放在溢出段中？通常我們認(rèn)為blob這類的大對象的存儲會把數(shù)據(jù)存放在數(shù)據(jù)頁之外，其實(shí)不然，關(guān)鍵點(diǎn)還是要看一個page中到底能否存放兩行數(shù)據(jù)，blob可以完全存放在數(shù)據(jù)頁中(單行長度沒有超過8096字節(jié))，而varchar類型的也有可能存放在溢出頁中(單行長度超過8096字節(jié)，前768字節(jié)存放在數(shù)據(jù)頁中)

1.7 mysql在操作數(shù)據(jù)的時候，以page為單位，不管是更新，插入，刪除一行數(shù)據(jù)，都需要將那行數(shù)據(jù)所在的page讀到內(nèi)存中，然后在進(jìn)行操作，這樣就存在一個命中率的問題，如果一個page中能夠相對的存放足夠多的行，那么命中率就會相對高一些，性能就會有提升

1.8 在off-page中存儲的BLOB、TEXT或者長VARCHAR列的page是獨(dú)享的，不能共享。因此強(qiáng)烈不建議在一個表中使用多個長列

1.9 MySQL 5.6 中默認(rèn)還是 Compact 行格式，也是目前使用最多的一種 ROW FORMAT。用戶可以通過命令 SHOW TABLE STATUS LIKE'table_name' 來查看當(dāng)前表使用的行格式，其中 row_format 列表示當(dāng)前所使用的行記錄結(jié)構(gòu)類型

mysql>desc db_page;+-----------------+----------------+----------------+---------------+-------------------+-----------------+| Field           | Type           | Null           | Key           | Default           | Extra           |+-----------------+----------------+----------------+---------------+-------------------+-----------------+| id              | int(11)        | NO             | PRI           |                   | auto_increment  || title           | varchar(100)   | NO             |               |                   |                 || name            | varchar(100)   | YES            |               |                   |                 || content         | text           | YES            |               |                   |                 |+-----------------+----------------+----------------+---------------+-------------------+-----------------+mysql>show variables like "innodb_file_format";+-------------------------+-----------------+| Variable_name           | Value           |+-------------------------+-----------------+| innodb_file_format      | Barracuda       |+-------------------------+-----------------+mysql>show table status like "db_page" \G*************************** 1. row ***************************
           Name: db_page
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 3
    Create_time: 2017-03-07 13:30:19
    Update_time: 
     Check_time: 
      Collation: utf8_general_ci
       Checksum: 
 Create_options: 
        Comment: 
   Block_format: Original

在 msyql 5.7.9 及以后版本，默認(rèn)行格式由innodb_default_row_format變量決定，它的默認(rèn)值是DYNAMIC，也可以在 create table 的時候指定ROW_FORMAT=DYNAMIC。

注意，如果要修改現(xiàn)有表的行模式為compressed或dynamic，必須先將文件格式設(shè)置成Barracuda：set global innodb_file_format=Barracuda;，再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效，否則修改無效卻無提示

二、對TEXT/BLOB這類大字段類型的影響

2.1 compact

變長大字段類型包括blob,text,varchar，其中varchar列值長度大于某數(shù)N時也會存溢出頁，在latin1字符集下N值可以這樣計算：innodb的塊大小默認(rèn)為16kb，由于innodb存儲引擎表為索引組織表，樹底層的葉子節(jié)點(diǎn)為一雙向鏈表，因此每個頁中至少應(yīng)該有兩行記錄，這就決定了innodb在存儲一行數(shù)據(jù)的時候不能夠超過8k，減去其它列值所占字節(jié)數(shù)，約等于N。對于InnoDB，內(nèi)存是極為珍貴的，如果把768字節(jié)長度的blob都放在數(shù)據(jù)頁，雖然可以節(jié)省部分IO，但是能緩存行數(shù)就變少，也就是能緩存的索引值變少了，降低了索引效率

2.2 dynamic

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

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

另外，由于ROW_FORMAT=DYNAMIC 和 ROW_FORMAT=COMPRESSED 是從 ROW_FORMAT=COMPACT 變化來的，所以他們處理 CHAR類型存儲的方式和 COMPACT 一樣。

三. 對TEXT/BLOB型字段存取優(yōu)化

mysql的 io 以page為單位，因此不必要的數(shù)據(jù)（大字段）也會隨著需要操作的數(shù)據(jù)一同被讀取到內(nèi)存中來，這樣帶來的問題由于大字段會占用較大的內(nèi)存（相比其他小字段），使得內(nèi)存利用率較差，造成更多的隨機(jī)讀取。從上面的分析來看，我們已經(jīng)看到性能的瓶頸在于由于大字段存放在數(shù)據(jù)頁中，造成了內(nèi)存利用較差，帶來過多的隨機(jī)讀，那怎么來優(yōu)化掉這個大字段的影響

3.1 壓縮&合并

a、innodb提供了barracuda文件格式，將大字段完全存放在溢出段中，數(shù)據(jù)段中只存放20個字節(jié)，這樣就大大的減小了數(shù)據(jù)頁的空間占用，使得一個數(shù)據(jù)頁能夠存放更多的數(shù)據(jù)行，也就提高了內(nèi)存的命中率（對于本實(shí)例，大多數(shù)行的長度并沒有超過8k，所以優(yōu)化的幅度有限）；如果對溢出段的數(shù)據(jù)進(jìn)行壓縮，那么在空間使用上也會大大的降低，具體的的壓縮比率可以設(shè)置key_blok_size來實(shí)現(xiàn)。

b、可以把大字段用COMPRESS()壓縮后再存為BLOB，或者在發(fā)送到MySQL前在應(yīng)用程序中進(jìn)行壓縮

c、一張表有多個類blob字段，把它們組合起來如<TEXT><f_big_col1>long..</f_big_col1> <f_content>long..</f_content></TEXT>，再壓縮存儲

d、如果預(yù)期長度范圍varchar就滿足，就避免使用TEXT

3.2 拆分

將主表拆分為一對一的兩個關(guān)聯(lián)表，將大字段單獨(dú)放到另外一張表后，單行長度變的非常的小，page的行密度相比原來的表大很多，這樣就能夠緩存足夠多的行，buffer pool的命中率就會提高，應(yīng)用程序需要額外維護(hù)的是一張大字段的子表，還可以通過覆蓋索引來優(yōu)化，將索引和原表結(jié)構(gòu)分開，從訪問密度較小的數(shù)據(jù)頁改為訪問密度很大的索引頁，隨機(jī)io轉(zhuǎn)換為順序io

總結(jié)：還是讓單個page能夠存放足夠多的行，不斷的提示內(nèi)存的命中率，從數(shù)據(jù)庫底層存儲的原理出發(fā)，能夠更深刻的優(yōu)化數(shù)據(jù)庫

綜上，如果在實(shí)際業(yè)務(wù)中，確實(shí)需要在InnoDB表中存儲BLOB、TEXT、長VARCHAR列時，有下面幾點(diǎn)建議：

• 盡可能將所有數(shù)據(jù)序列化、壓縮之后，存儲在同一個列里，避免發(fā)生多次off-page

• 如果預(yù)期長度范圍varchar就滿足，就避免使用TEXT

• 如果無法將所有列整合到一個列，可以退而求其次，根據(jù)每個列最大長度進(jìn)行排列組合后拆分成多個子表，盡量是的每個子表的總行長度小于8KB，減少發(fā)生off-page的頻率