提高M(jìn)ySQL中InnoDB表BLOB列的存儲(chǔ)效率的教程
更新時(shí)間：2015年05月08日 11:02:49 投稿：goldensun
這篇文章主要介紹了提高M(jìn)ySQL中InnoDB表BLOB列的存儲(chǔ)效率的教程,InnoDB的優(yōu)化在MySQL的優(yōu)化研究中也是一個(gè)非常熱門的課題,需要的朋友可以參考下
首先，介紹下關(guān)于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行記錄。因此理論上行記錄最大長度為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之類的長列（long column，也有可能是其他較長的列，不一定只有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長度的VARCHAR列類型；
10、當(dāng)InnoDB的文件格式（innodb_file_format）設(shè)置為Antelope，并且行格式為COMPACT 或 REDUNDANT 時(shí)，BLOB、TEXT或者長VARCHAR列只會(huì)將其前768字節(jié)存儲(chǔ)在聚集索頁中（最大768字節(jié)的作用是便于創(chuàng)建前綴索引/prefix index），其余更多的內(nèi)容存儲(chǔ)在額外的page里，哪怕只是多了一個(gè)字節(jié)。因此，所有列長度越短越好；
11、在off-page中存儲(chǔ)的BLOB、TEXT或者長VARCHAR列的page是獨(dú)享的，不能共享。因此強(qiáng)烈不建議在一個(gè)表中使用多個(gè)長列。
綜上，如果在實(shí)際業(yè)務(wù)中，確實(shí)需要在InnoDB表中存儲(chǔ)BLOB、TEXT、長VARCHAR列時(shí)，有下面幾點(diǎn)建議：
1、盡可能將所有數(shù)據(jù)序列化、壓縮之后，存儲(chǔ)在同一個(gè)列里，避免發(fā)生多次off-page；
2、實(shí)際最大存儲(chǔ)長度低于255的列，轉(zhuǎn)成VARCHAR或者CHAR類型（如果是變長數(shù)據(jù)二者沒區(qū)別，如果是定長數(shù)據(jù)，則使用CHAR類型）；
3、如果無法將所有列整合到一個(gè)列，可以退而求其次，根據(jù)每個(gè)列最大長度進(jìn)行排列組合后拆分成多個(gè)子表，盡量是的每個(gè)子表的總行長度小于8KB，減少發(fā)生off-page的頻率；
4、上述建議是在data page為默認(rèn)的16KB前提下，如果修改成8KB或者其他大小，請自行根據(jù)上述理論進(jìn)行測試，找到最合適的值；
5、字符型列長度小于255時(shí)，無論采用CHAR還是VARCHAR來存儲(chǔ)，或者把VARCHAR列長度定義為255，都不會(huì)導(dǎo)致實(shí)際表空間增大；
6、一般在游戲領(lǐng)域會(huì)用到比較多的BLOB列類型，游戲界同行可以關(guān)注下。
下面是測試驗(yàn)證過程，有耐心的同學(xué)可以慢慢看：
#
# 測試案例：InnoDB中長列存儲(chǔ)效率
# 測試場景描述：
# 在InnoDB表中存儲(chǔ)64KB的數(shù)據(jù)，對比各種不同存儲(chǔ)方式 # 每個(gè)表寫入5000行記錄，觀察最后表空間文件大小對比
#
#表0：所有數(shù)據(jù)存儲(chǔ)在一個(gè)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ù)寫入存儲(chǔ)過程：mysp_longcol_0_ins()
CREATE PROCEDURE `mysp_longcol_0_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_0(longcol) select repeat('a',65535);
set @i = @i + 1;
end while;
end;
#表1：將64KB字節(jié)平均存儲(chǔ)在9個(gè)列中
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ù)寫入存儲(chǔ)過程：mysp_longcol_1_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_1(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9) select
repeat('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ù)離散存儲(chǔ)在多個(gè)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ù)寫入存儲(chǔ)過程：mysp_longcol_1_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_2(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('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ù)離散存儲(chǔ)在多個(gè)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ù)寫入存儲(chǔ)過程：mysp_longcol_3_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_3(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('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ù)離散存儲(chǔ)在多個(gè)VARCHAR、BLOB列中，對比t_longcol_3中幾個(gè)列是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ù)寫入存儲(chǔ)過程：mysp_longcol_4_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_4(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('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ù)離散存儲(chǔ)在多個(gè)VARCHAR、BLOB列中，和t_longcol_4相比，變化在于前面的幾個(gè)列長度改成了255，但實(shí)際存儲(chǔ)長度還是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ù)寫入存儲(chǔ)過程：mysp_longcol_5_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_5(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('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條建議進(jìn)行分表，每個(gè)表所有列長度總和
#分表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ù)寫入存儲(chǔ)過程：mysp_longcol_51_ins()
CREATE PROCEDURE `mysp_longcol_51_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_51(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol15) select
repeat('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) select
repeat('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) select
repeat('a',16284),
repeat('a',20380);
insert into t_longcol_55(longcol20) select repeat('a',5977);
set @i = @i + 1;
end while;
end;
上述各個(gè)測試表都寫入5000行記錄后，再來對比下其表空間文件大小，以及重整表空間后的大小，觀察碎片率。詳細(xì)對比見下：
最后一種分表方式中，5個(gè)子表的表空間文件大小總和是 40960 + 40960 + 98304 + 286720 + 40960 = 507904 字節(jié)。
可以看到，這種方式的總大小和原始表大小差距最小，其他幾種存儲(chǔ)方式都比這個(gè)來的大。
相關(guān)文章
這篇文章主要給大家介紹了新手第一次把mysql裝進(jìn)docker中可能碰到的各種問題，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-06-06
Ubuntu Server下MySql數(shù)據(jù)庫備份腳本代碼
為了mysql數(shù)據(jù)庫的安全，我們需要定時(shí)備份mysql數(shù)據(jù)庫，這里提供下腳本代碼，需要的朋友可以參考下2013-06-06
INNOTOP是一個(gè)通過文本模式顯示MySQL和InnoDB的監(jiān)測工具。INNOTOP是用PERL語言寫成的，這使它能更加靈活的使用在各種操作平臺之上，它能詳細(xì)的的監(jiān)控出當(dāng)前MYSQL和INNODB運(yùn)行的狀態(tài)，以DBA根據(jù)結(jié)果，可以合理的優(yōu)化MYSQL，讓MYSQL更穩(wěn)定更高效的運(yùn)行。2014-08-08
這篇文章主要介紹了MySQL 外鍵(FOREIGN KEY)用法案例詳解,本篇文章通過簡要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08
這篇文章主要介紹了MysQL B-Tree 索引的相關(guān)資料，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2020-11-11
ubuntu下mysql?8.0.28 安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了ubuntu下mysql?8.0.28安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-04-04
這篇文章主要介紹了MySQL數(shù)據(jù)庫索引事務(wù)，索引是為了加速對表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散的存儲(chǔ)結(jié)；事物是屬于計(jì)算機(jī)中一個(gè)很廣泛的概念，一般是指要做的或所做的事情，下面我們就一起進(jìn)入文章了解具體內(nèi)容吧2022-12-12
MySQL server has gone away 問題的解決方法
MySQL server has gone away 問題解決方法，需要的朋友可以參考下。2010-06-06
MySQL入門(三) 數(shù)據(jù)庫表的查詢操作【重要】
本節(jié)比較重要，對數(shù)據(jù)表數(shù)據(jù)進(jìn)行查詢操作，其中可能大家不熟悉的就對于INNER JOIN(內(nèi)連接)、LEFT JOIN(左連接)、RIGHT JOIN(右連接)等一些復(fù)雜查詢。 通過本節(jié)的學(xué)習(xí)，可以讓你知道這些基本的復(fù)雜查詢是怎么實(shí)現(xiàn)的，,需要的朋友可以參考下2018-07-07
詳解MySQL數(shù)據(jù)庫優(yōu)化的八種方式(經(jīng)典必看)
關(guān)于數(shù)據(jù)庫優(yōu)化，網(wǎng)上有不少資料和方法，但是不少質(zhì)量參差不齊，有些總結(jié)的不夠到位，內(nèi)容冗雜。今天給大家分享一篇文章關(guān)于mysql數(shù)據(jù)庫優(yōu)化的八種方式，非常經(jīng)典，需要的的朋友參考下2017-03-03