首先，介紹下關(guān)于MySQL InnoDB引擎存儲(chǔ)格式的幾個(gè)要點(diǎn)：

1、InnoDB可以選擇使用共享表空間或者是獨(dú)立表空間方式，建議使用獨(dú)立表空間，便于管理、維護(hù)。啟用 innodb_file_per_table 選項(xiàng)，5.5以后可以在線動(dòng)態(tài)修改生效，并且執(zhí)行 ALTER TABLE xx ENGINE = InnoDB 將現(xiàn)有表轉(zhuǎn)成獨(dú)立表空間，早于5.5的版本，修改完這個(gè)選項(xiàng)后，需要重啟才能生效。

2、InnoDB的data page默認(rèn)16KB，5.6版本以后，新增選項(xiàng) innodb_page_size 可以修改，在5.6以前的版本，只能修改源碼重新編譯，但并不推薦修改這個(gè)配置，除非你非常清楚它有什么優(yōu)缺點(diǎn)。

3、InnoDB的data page在有新數(shù)據(jù)寫入時(shí)，會(huì)預(yù)留1/16的空間，預(yù)留出來的空間可用于后續(xù)的新紀(jì)錄寫入，減少頻繁的新增data page的開銷。

4、每個(gè)data page，至少需要存儲(chǔ)2行記錄。因此理論上行記錄最大長(zhǎng)度為8KB，但事實(shí)上應(yīng)該更小，因?yàn)檫€有一些InnoDB內(nèi)部數(shù)據(jù)結(jié)構(gòu)要存儲(chǔ)。

5、受限于InnoDB存儲(chǔ)方式，如果數(shù)據(jù)是順序?qū)懭氲脑?，最理想的情況下，data page的填充率是15/16，但一般沒辦法保證完全的順序?qū)懭?，因此，data page的填充率一般是1/2到15/16。因此每個(gè)InnoDB表都最好要有一個(gè)自增列作為主鍵，使得新紀(jì)錄寫入盡可能是順序的。

6、當(dāng)data page填充率不足1/2時(shí)，InnoDB會(huì)進(jìn)行收縮，釋放空閑空間。

7、MySQL 5.6版本的InnoDB引擎當(dāng)前支持COMPACT、REDUNDANT、DYNAMIC、COMPRESSED四種格式，默認(rèn)是COMPACT格式，COMPRESSED用的很少且不推薦（見下一條），如果需要用到壓縮特性的話，可以直接考慮TokuDB引擎。

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

9、當(dāng)行格式為DYNAMIC或COMPRESSED時(shí)，TEXT/BLOB之類的長(zhǎng)列（long column，也有可能是其他較長(zhǎng)的列，不一定只有TEXT/BLOB類型，看具體情況）會(huì)完全存儲(chǔ)在一個(gè)獨(dú)立的data page里，聚集索引頁中只使用20字節(jié)的指針指向新的page，這就是所謂的off-page，類似ORACLE的行遷移，磁盤空間浪費(fèi)較嚴(yán)重，且I/O性能也較差。因此，強(qiáng)烈不建議使用BLOB、TEXT、超過255長(zhǎng)度的VARCHAR列類型。

10、當(dāng)InnoDB的文件格式（innodb_file_format）設(shè)置為Antelope，并且行格式為COMPACT 或 REDUNDANT 時(shí)，BLOB、TEXT或者長(zhǎng)VARCHAR列只會(huì)將其前768字節(jié)存儲(chǔ)在聚集索頁中（最大768字節(jié)的作用是便于創(chuàng)建前綴索引/prefix index），其余更多的內(nèi)容存儲(chǔ)在額外的page里，哪怕只是多了一個(gè)字節(jié)。因此，所有列長(zhǎng)度越短越好。

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

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

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

2、實(shí)際最大存儲(chǔ)長(zhǎng)度低于255的列，轉(zhuǎn)成VARCHAR或者CHAR類型（如果是變長(zhǎng)數(shù)據(jù)二者沒區(qū)別，如果是定長(zhǎng)數(shù)據(jù)，則使用CHAR類型）。

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

4、上述建議是在data page為默認(rèn)的16KB前提下，如果修改成8KB或者其他大小，請(qǐng)自行根據(jù)上述理論進(jìn)行測(cè)試，找到最合適的值。

5、字符型列長(zhǎng)度小于255時(shí)，無論采用CHAR還是VARCHAR來存儲(chǔ)，或者把VARCHAR列長(zhǎng)度定義為255，都不會(huì)導(dǎo)致實(shí)際表空間增大。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請(qǐng)查看下面相關(guān)鏈接

最新評(píng)論