#
# 测试案例:InnoDB中长列存储效率
# 测试场景描述:
# 在InnoDB表中存储64KB的数据,对比各种不同存储方式 # 每个表写入5000行记录,观察最后表空间文件大小对比
#
#表0:所有数据存储在一个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;
#相应的数据写入存储过程: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字节平均存储在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;
#相应的数据写入存储过程: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数据离散存储在多个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;
#相应的数据写入存储过程: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数据离散存储在多个
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;
#相应的数据写入存储过程: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数据离散存储在多个
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;
#相应的数据写入存储过程: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数据离散存储在多个
VARCHAR
、BLOB列中,和t_longcol_4相比,变化在于前面的几个列长度改成了255,但实际存储长度还是100字节
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;
#相应的数据写入存储过程: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条建议进行分表,每个表所有列长度总和
#分表1,行最大长度 100 + 100 + 100 + 100 + 100 + 255 + 368 + 496 + 512 + 640 + 768 + 912 + 3082 = 7533 字节
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 字节
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 字节
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 字节
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 字节
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;
#相应的数据写入存储过程: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
;