提高MySQL中InnoDB表BLOB列的存儲效率的教程

2020-01-18 22:56:14

字體：大中小

供稿：網(wǎng)友

首先，介紹下關(guān)于InnoDB引擎存儲格式的幾個要點：
1、InnoDB可以選擇使用共享表空間或者是獨立表空間方式，建議使用獨立表空間，便于管理、維護。啟用 innodb_file_per_table 選項，5.5以后可以在線動態(tài)修改生效，并且執(zhí)行 ALTER TABLE xx ENGINE = InnoDB 將現(xiàn)有表轉(zhuǎn)成獨立表空間，早于5.5的版本，修改完這個選項后，需要重啟才能生效；
2、InnoDB的data page默認16KB，5.6版本以后，新增選項 innodb_page_size 可以修改，在5.6以前的版本，只能修改源碼重新編譯，但并不推薦修改這個配置，除非你非常清楚它有什么優(yōu)缺點；
3、InnoDB的data page在有新數(shù)據(jù)寫入時，會預(yù)留1/16的空間，預(yù)留出來的空間可用于后續(xù)的新紀錄寫入，減少頻繁的新增data page的開銷；
4、每個data page，至少需要存儲2行記錄。因此理論上行記錄最大長度為8KB，但事實上應(yīng)該更小，因為還有一些InnoDB內(nèi)部數(shù)據(jù)結(jié)構(gòu)要存儲；
5、受限于InnoDB存儲方式，如果數(shù)據(jù)是順序?qū)懭氲脑挘罾硐氲那闆r下，data page的填充率是15/16，但一般沒辦法保證完全的順序?qū)懭耄虼耍琩ata page的填充率一般是1/2到15/16。因此每個InnoDB表都最好要有一個自增列作為主鍵，使得新紀錄寫入盡可能是順序的；
6、當data page填充率不足1/2時，InnoDB會進行收縮，釋放空閑空間；
7、MySQL 5.6版本的InnoDB引擎當前支持COMPACT、REDUNDANT、DYNAMIC、COMPRESSED四種格式，默認是COMPACT格式，COMPRESSED用的很少且不推薦（見下一條），如果需要用到壓縮特性的話，可以直接考慮TokuDB引擎；
8、COMPACT行格式相比REDUNDANT，大概能節(jié)省20%的存儲空間，COMPRESSED相比COMPACT大概能節(jié)省50%的存儲空間，但會導致TPS下降了90%。因此強烈不推薦使用COMPRESSED行格式；
9、當行格式為DYNAMIC或COMPRESSED時，TEXT/BLOB之類的長列（long column，也有可能是其他較長的列，不一定只有TEXT/BLOB類型，看具體情況）會完全存儲在一個獨立的data page里，聚集索引頁中只使用20字節(jié)的指針指向新的page，這就是所謂的off-page，類似ORACLE的行遷移，磁盤空間浪費較嚴重，且I/O性能也較差。因此，強烈不建議使用BLOB、TEXT、超過255長度的VARCHAR列類型；
10、當InnoDB的文件格式（innodb_file_format）設(shè)置為Antelope，并且行格式為COMPACT 或 REDUNDANT 時，BLOB、TEXT或者長VARCHAR列只會將其前768字節(jié)存儲在聚集索頁中（最大768字節(jié)的作用是便于創(chuàng)建前綴索引/prefix index），其余更多的內(nèi)容存儲在額外的page里，哪怕只是多了一個字節(jié)。因此，所有列長度越短越好；
11、在off-page中存儲的BLOB、TEXT或者長VARCHAR列的page是獨享的，不能共享。因此強烈不建議在一個表中使用多個長列。

綜上，如果在實際業(yè)務(wù)中，確實需要在InnoDB表中存儲BLOB、TEXT、長VARCHAR列時，有下面幾點建議：
1、盡可能將所有數(shù)據(jù)序列化、壓縮之后，存儲在同一個列里，避免發(fā)生多次off-page；
2、實際最大存儲長度低于255的列，轉(zhuǎn)成VARCHAR或者CHAR類型（如果是變長數(shù)據(jù)二者沒區(qū)別，如果是定長數(shù)據(jù)，則使用CHAR類型）；
3、如果無法將所有列整合到一個列，可以退而求其次，根據(jù)每個列最大長度進行排列組合后拆分成多個子表，盡量是的每個子表的總行長度小于8KB，減少發(fā)生off-page的頻率；
4、上述建議是在data page為默認的16KB前提下，如果修改成8KB或者其他大小，請自行根據(jù)上述理論進行測試，找到最合適的值；
5、字符型列長度小于255時，無論采用CHAR還是VARCHAR來存儲，或者把VARCHAR列長度定義為255，都不會導致實際表空間增大；
6、一般在游戲領(lǐng)域會用到比較多的BLOB列類型，游戲界同行可以關(guān)注下。

下面是測試驗證過程，有耐心的同學可以慢慢看：

## 測試案例：InnoDB中長列存儲效率# 測試場景描述：# 在InnoDB表中存儲64KB的數(shù)據(jù)，對比各種不同存儲方式# 每個表寫入5000行記錄，觀察最后表空間文件大小對比##表0：所有數(shù)據(jù)存儲在一個BLOB列中CREATE TABLE `t_longcol_0` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol` blob NOT NULL COMMENT 'store all data in a blob column',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;#相應(yīng)的數(shù)據(jù)寫入存儲過程：mysp_longcol_0_ins()CREATE PROCEDURE `mysp_longcol_0_ins`( in cnt int )beginset @i = 1;while @i < cnt doinsert into t_longcol_0(longcol) select repeat('a',65535);set @i = @i + 1;end while;end;#表1：將64KB字節(jié)平均存儲在9個列中CREATE TABLE `t_longcol_1` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol1` blob NOT NULL COMMENT 'store all data in 9 blob columns',`longcol2` blob NOT NULL,`longcol3` blob NOT NULL,`longcol4` blob NOT NULL,`longcol5` blob NOT NULL,`longcol6` blob NOT NULL,`longcol7` blob NOT NULL,`longcol8` blob NOT NULL,`longcol9` blob NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#相應(yīng)的數(shù)據(jù)寫入存儲過程：mysp_longcol_1_ins()CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )beginset @i = 1;while @i < cnt doinsert into t_longcol_1(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9) selectrepeat('a',7500),repeat('a',7500),repeat('a',7500),repeat('a',7500),repeat('a',7500),repeat('a',7500),repeat('a',7500),repeat('a',7500),repeat('a',5535);set @i = @i + 1;end while;end;#表2：將64KB數(shù)據(jù)離散存儲在多個BLOB列中CREATE TABLE `t_longcol_2` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol1` blob NOT NULL COMMENT 'store 100 bytes data',`longcol2` blob NOT NULL COMMENT 'store 100 bytes data',`longcol3` blob NOT NULL COMMENT 'store 100 bytes data',`longcol4` blob NOT NULL COMMENT 'store 100 bytes data',`longcol5` blob NOT NULL COMMENT 'store 100 bytes data',`longcol6` blob NOT NULL COMMENT 'store 255 bytes data',`longcol7` blob NOT NULL COMMENT 'store 368 bytes data',`longcol8` blob NOT NULL COMMENT 'store 496 bytes data',`longcol9` blob NOT NULL COMMENT 'store 512 bytes data',`longcol10` blob NOT NULL COMMENT 'store 640 bytes data',`longcol11` blob NOT NULL COMMENT 'store 768 bytes data',`longcol12` blob NOT NULL COMMENT 'store 912 bytes data',`longcol13` blob NOT NULL COMMENT 'store 1024 bytes data',`longcol14` blob NOT NULL COMMENT 'store 2048 bytes data',`longcol15` blob NOT NULL COMMENT 'store 3082 bytes data',`longcol16` blob NOT NULL COMMENT 'store 4096 bytes data',`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',`longcol20` blob NOT NULL COMMENT 'store 5977 bytes data',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#相應(yīng)的數(shù)據(jù)寫入存儲過程：mysp_longcol_1_ins()CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )beginset @i = 1;while @i < cnt doinsert into t_longcol_2(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) selectrepeat('a',100),repeat('a',100),repeat('a',100),repeat('a',100),repeat('a',100),repeat('a',256),repeat('a',368),repeat('a',496),repeat('a',512),repeat('a',640),repeat('a',768),repeat('a',912),repeat('a',1024),repeat('a',2048),repeat('a',3082),repeat('a',4096),repeat('a',8192),repeat('a',16284),repeat('a',20380),repeat('a',5977);set @i = @i + 1;end while;end;#表3：將64KB數(shù)據(jù)離散存儲在多個CHAR、VARCHAR、BLOB列中CREATE TABLE `t_longcol_3` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol1` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol2` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol3` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol4` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol5` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#相應(yīng)的數(shù)據(jù)寫入存儲過程：mysp_longcol_3_ins()CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )beginset @i = 1;while @i < cnt doinsert into t_longcol_3(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) selectrepeat('a',100),repeat('a',100),repeat('a',100),repeat('a',100),repeat('a',100),repeat('a',256),repeat('a',368),repeat('a',496),repeat('a',512),repeat('a',640),repeat('a',768),repeat('a',912),repeat('a',1024),repeat('a',2048),repeat('a',3082),repeat('a',4096),repeat('a',8192),repeat('a',16284),repeat('a',20380),repeat('a',5977);set @i = @i + 1;end while;end;#表4：將64KB數(shù)據(jù)離散存儲在多個VARCHAR、BLOB列中，對比t_longcol_3中幾個列是CHAR的情況CREATE TABLE `t_longcol_4` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol1` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol2` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol3` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol4` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol5` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#相應(yīng)的數(shù)據(jù)寫入存儲過程：mysp_longcol_4_ins()CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )beginset @i = 1;while @i < cnt doinsert into t_longcol_4(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) selectrepeat('a',100),repeat('a',100),repeat('a',100),repeat('a',100),repeat('a',100),repeat('a',256),repeat('a',368),repeat('a',496),repeat('a',512),repeat('a',640),repeat('a',768),repeat('a',912),repeat('a',1024),repeat('a',2048),repeat('a',3082),repeat('a',4096),repeat('a',8192),repeat('a',16284),repeat('a',20380),repeat('a',5977);set @i = @i + 1;end while;end;#表5：將64KB數(shù)據(jù)離散存儲在多個VARCHAR、BLOB列中，和t_longcol_4相比，變化在于前面的幾個列長度改成了255，但實際存儲長度還是100字節(jié)CREATE TABLE `t_longcol_5` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#相應(yīng)的數(shù)據(jù)寫入存儲過程：mysp_longcol_5_ins()CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )beginset @i = 1;while @i < cnt doinsert into t_longcol_5(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) selectrepeat('a',100),repeat('a',100),repeat('a',100),repeat('a',100),repeat('a',100),repeat('a',256),repeat('a',368),repeat('a',496),repeat('a',512),repeat('a',640),repeat('a',768),repeat('a',912),repeat('a',1024),repeat('a',2048),repeat('a',3082),repeat('a',4096),repeat('a',8192),repeat('a',16284),repeat('a',20380),repeat('a',5977);set @i = @i + 1;end while;end;#從下面開始，參考第3條建議進行分表，每個表所有列長度總和#分表1，行最大長度 100 + 100 + 100 + 100 + 100 + 255 + 368 + 496 + 512 + 640 + 768 + 912 + 3082 = 7533 字節(jié)CREATE TABLE `t_longcol_51` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#分表2，行最大長度 1024 + 2048 + 4096 = 7168 字節(jié)CREATE TABLE `t_longcol_52` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#分表3，行最大長度 8192 字節(jié)CREATE TABLE `t_longcol_53` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#分表4，行最大長度 16284 + 20380 = 36664 字節(jié)CREATE TABLE `t_longcol_54` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#分表5，行最大長度 5977 + 4 = 5981 字節(jié)CREATE TABLE `t_longcol_55` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#相應(yīng)的數(shù)據(jù)寫入存儲過程：mysp_longcol_51_ins()CREATE PROCEDURE `mysp_longcol_51_ins`( in cnt int )beginset @i = 1;while @i < cnt doinsert into t_longcol_51(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,longcol11,longcol12,longcol15) selectrepeat('a',100),repeat('a',100),repeat('a',100),repeat('a',100),repeat('a',100),repeat('a',256),repeat('a',368),repeat('a',496),repeat('a',512),repeat('a',640),repeat('a',768),repeat('a',912),repeat('a',3082);insert into t_longcol_52(longcol13,longcol14,longcol16) selectrepeat('a',1024),repeat('a',2048),repeat('a',4096);insert into t_longcol_53(longcol17) select repeat('a',8192);insert into t_longcol_54(longcol18,longcol19) selectrepeat('a',16284),repeat('a',20380);insert into t_longcol_55(longcol20) select repeat('a',5977);set @i = @i + 1;end while;end;

上述各個測試表都寫入5000行記錄后，再來對比下其表空間文件大小，以及重整表空間后的大小，觀察碎片率。詳細對比見下：

201558110346220.png (1191×133)