首页 > 数据库 > MySQL > 正文

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

2024-07-24 13:07:10
字体：大 中 小
来源：转载
供稿：网友

这篇文章主要介绍了提高MySQL中InnoDB表BLOB列的存储效率的教程,InnoDB的优化在MySQL的优化研究中也是一个非常热门的课题,需要的朋友可以参考下

首先，介绍下关于InnoDB引擎存储格式的几个要点：

1、InnoDB可以选择使用共享表空间或者是独立表空间方式，建议使用独立表空间，便于管理、维护。启用 innodb_file_per_table 选项，5.5以后可以在线动态修改生效，并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间，早于5.5的版本，修改完这个选项后，需要重启才能生效;

2、InnoDB的data page默认16KB，5.6版本以后，新增选项 innodb_page_size 可以修改，在5.6以前的版本，只能修改源码重新编译，但并不推荐修改这个配置，除非你非常清楚它有什么优缺点;

3、InnoDB的data page在有新数据写入时，会预留1/16的空间，预留出来的空间可用于后续的新纪录写入，减少频繁的新增data page的开销;

4、每个data page，至少需要存储2行记录。因此理论上行记录最大长度为8KB，但事实上应该更小，因为还有一些InnoDB内部数据结构要存储;

5、受限于InnoDB存储方式，如果数据是顺序写入的话，最理想的情况下，data page的填充率是15/16，但一般没办法保证完全的顺序写入，因此，data 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，大概能节省20%的存储空间，COMPRESSED相比COMPACT大概能节省50%的存储空间，但会导致TPS下降了90%。因此强烈不推荐使用COMPRESSED行格式;

9、当行格式为DYNAMIC或COMPRESSED时，TEXT/BLOB之类的长列(long column，也有可能是其他较长的列，不一定只有TEXT/BLOB类型，看具体情况)会完全存储在一个独立的data page里，聚集索引页中只使用20字节的指针指向新的page，这就是所谓的off-page，类似ORACLE的行迁移，磁盘空间浪费较严重，且I/O性能也较差。因此，强烈不建议使用BLOB、TEXT、超过255长度的VARCHAR列类型;

10、当InnoDB的文件格式(innodb_file_format)设置为Antelope，并且行格式为COMPACT 或 REDUNDANT 时，BLOB、TEXT或者长VARCHAR列只会将其前768字节存储在聚集索页中(最大768字节的作用是便于创建前缀索引/prefix index)，其余更多的内容存储在额外的page里，哪怕只是多了一个字节。因此，所有列长度越短越好;

11、在off-page中存储的BLOB、TEXT或者长VARCHAR列的page是独享的，不能共享。因此强烈不建议在一个表中使用多个长列。

综上，如果在实际业务中，确实需要在InnoDB表中存储BLOB、TEXT、长VARCHAR列时，有下面几点建议：

1、尽可能将所有数据序列化、压缩之后，存储在同一个列里，避免发生多次off-page;

2、实际最大存储长度低于255的列，转成VARCHAR或者CHAR类型(如果是变长数据二者没区别，如果是定长数据，则使用CHAR类型);

3、如果无法将所有列整合到一个列，可以退而求其次，根据每个列最大长度进行排列组合后拆分成多个子表，尽量是的每个子表的总行长度小于8KB，减少发生off-page的频率;

4、上述建议是在data page为默认的16KB前提下，如果修改成8KB或者其他大小，请自行根据上述理论进行测试，找到最合适的值;

5、字符型列长度小于255时，无论采用CHAR还是VARCHAR来存储，或者把VARCHAR列长度定义为255，都不会导致实际表空间增大;

6、一般在游戏领域会用到比较多的BLOB列类型，游戏界同行可以关注下。

下面是测试验证过程，有耐心的同学可以慢慢看：

  1. # 
  2. # 测试案例：InnoDB中长列存储效率 
  3. # 测试场景描述： 
  4. # 在InnoDB表中存储64KB的数据，对比各种不同存储方式 # 每个表写入5000行记录，观察最后表空间文件大小对比 
  5. # 
  6.  
  7. #表0：所有数据存储在一个BLOB列中 
  8. CREATE TABLE `t_longcol_0` ( 
  9. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  10. `longcol` blob NOT NULL COMMENT 'store all data in a blob column', 
  11. PRIMARY KEY (`id`) 
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; 
  13.  
  14. #相应的数据写入存储过程：mysp_longcol_0_ins() 
  15. CREATE PROCEDURE `mysp_longcol_0_ins`( in cnt int ) 
  16. begin 
  17. set @i = 1; 
  18. while @i < cnt do 
  19. insert into t_longcol_0(longcol) select repeat('a',65535); 
  20. set @i = @i + 1; 
  21. end while; 
  22. end; 
  23.  
  24. #表1：将64KB字节平均存储在9个列中 
  25. CREATE TABLE `t_longcol_1` ( 
  26. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  27. `longcol1` blob NOT NULL COMMENT 'store all data in 9 blob columns', 
  28. `longcol2` blob NOT NULL, 
  29. `longcol3` blob NOT NULL, 
  30. `longcol4` blob NOT NULL, 
  31. `longcol5` blob NOT NULL, 
  32. `longcol6` blob NOT NULL, 
  33. `longcol7` blob NOT NULL, 
  34. `longcol8` blob NOT NULL, 
  35. `longcol9` blob NOT NULL, 
  36. PRIMARY KEY (`id`) 
  37. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  38.  
  39. #相应的数据写入存储过程：mysp_longcol_1_ins() 
  40. CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int ) 
  41. begin 
  42. set @i = 1; 
  43. while @i < cnt do 
  44. insert into t_longcol_1(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9) select 
  45. repeat('a',7500), 
  46. repeat('a',7500), 
  47. repeat('a',7500), 
  48. repeat('a',7500), 
  49. repeat('a',7500), 
  50. repeat('a',7500), 
  51. repeat('a',7500), 
  52. repeat('a',7500), 
  53. repeat('a',5535); 
  54. set @i = @i + 1; 
  55. end while; 
  56. end; 
  57.  
  58. #表2：将64KB数据离散存储在多个BLOB列中 
  59. CREATE TABLE `t_longcol_2` ( 
  60. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  61. `longcol1` blob NOT NULL COMMENT 'store 100 bytes data', 
  62. `longcol2` blob NOT NULL COMMENT 'store 100 bytes data', 
  63. `longcol3` blob NOT NULL COMMENT 'store 100 bytes data', 
  64. `longcol4` blob NOT NULL COMMENT 'store 100 bytes data', 
  65. `longcol5` blob NOT NULL COMMENT 'store 100 bytes data', 
  66. `longcol6` blob NOT NULL COMMENT 'store 255 bytes data', 
  67. `longcol7` blob NOT NULL COMMENT 'store 368 bytes data', 
  68. `longcol8` blob NOT NULL COMMENT 'store 496 bytes data', 
  69. `longcol9` blob NOT NULL COMMENT 'store 512 bytes data', 
  70. `longcol10` blob NOT NULL COMMENT 'store 640 bytes data', 
  71. `longcol11` blob NOT NULL COMMENT 'store 768 bytes data', 
  72. `longcol12` blob NOT NULL COMMENT 'store 912 bytes data', 
  73. `longcol13` blob NOT NULL COMMENT 'store 1024 bytes data', 
  74. `longcol14` blob NOT NULL COMMENT 'store 2048 bytes data', 
  75. `longcol15` blob NOT NULL COMMENT 'store 3082 bytes data', 
  76. `longcol16` blob NOT NULL COMMENT 'store 4096 bytes data', 
  77. `longcol17` blob NOT NULL COMMENT 'store 8192 bytes data', 
  78. `longcol18` blob NOT NULL COMMENT 'store 16284 bytes data', 
  79. `longcol19` blob NOT NULL COMMENT 'store 20380 bytes data', 
  80. `longcol20` blob NOT NULL COMMENT 'store 5977 bytes data', 
  81. PRIMARY KEY (`id`) 
  82. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  83.  
  84. #相应的数据写入存储过程：mysp_longcol_1_ins() 
  85. CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int ) 
  86. begin 
  87. set @i = 1; 
  88. while @i < cnt do 
  89. insert into t_longcol_2(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10, 
  90. longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select 
  91. repeat('a',100), 
  92. repeat('a',100), 
  93. repeat('a',100), 
  94. repeat('a',100), 
  95. repeat('a',100), 
  96. repeat('a',256), 
  97. repeat('a',368), 
  98. repeat('a',496), 
  99. repeat('a',512), 
  100. repeat('a',640), 
  101. repeat('a',768), 
  102. repeat('a',912), 
  103. repeat('a',1024), 
  104. repeat('a',2048), 
  105. repeat('a',3082), 
  106. repeat('a',4096), 
  107. repeat('a',8192), 
  108. repeat('a',16284), 
  109. repeat('a',20380), 
  110. repeat('a',5977); 
  111. set @i = @i + 1; 
  112. end while; 
  113. end; 
  114.  
  115. #表3：将64KB数据离散存储在多个CHAR、VARCHAR、BLOB列中 
  116. CREATE TABLE `t_longcol_3` ( 
  117. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  118. `longcol1` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  119. `longcol2` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  120. `longcol3` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  121. `longcol4` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  122. `longcol5` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  123. `longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data', 
  124. `longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data', 
  125. `longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data', 
  126. `longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data', 
  127. `longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data', 
  128. `longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data', 
  129. `longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data', 
  130. `longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data', 
  131. `longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data', 
  132. `longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data', 
  133. `longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data', 
  134. `longcol17` blob NOT NULL COMMENT 'store 8192 bytes data', 
  135. `longcol18` blob NOT NULL COMMENT 'store 16284 bytes data', 
  136. `longcol19` blob NOT NULL COMMENT 'store 20380 bytes data', 
  137. `longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data', 
  138. PRIMARY KEY (`id`) 
  139. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  140.  
  141. #相应的数据写入存储过程：mysp_longcol_3_ins() 
  142. CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int ) 
  143. begin 
  144. set @i = 1; 
  145. while @i < cnt do 
  146. insert into t_longcol_3(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10, 
  147. longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select 
  148. repeat('a',100), 
  149. repeat('a',100), 
  150. repeat('a',100), 
  151. repeat('a',100), 
  152. repeat('a',100), 
  153. repeat('a',256), 
  154. repeat('a',368), 
  155. repeat('a',496), 
  156. repeat('a',512), 
  157. repeat('a',640), 
  158. repeat('a',768), 
  159. repeat('a',912), 
  160. repeat('a',1024), 
  161. repeat('a',2048), 
  162. repeat('a',3082), 
  163. repeat('a',4096), 
  164. repeat('a',8192), 
  165. repeat('a',16284), 
  166. repeat('a',20380), 
  167. repeat('a',5977); 
  168. set @i = @i + 1; 
  169. end while; 
  170. end; 
  171.  
  172. #表4：将64KB数据离散存储在多个VARCHAR、BLOB列中，对比t_longcol_3中几个列是CHAR的情况 
  173. CREATE TABLE `t_longcol_4` ( 
  174. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  175. `longcol1` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  176. `longcol2` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  177. `longcol3` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  178. `longcol4` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  179. `longcol5` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  180. `longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data', 
  181. `longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data', 
  182. `longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data', 
  183. `longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data', 
  184. `longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data', 
  185. `longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data', 
  186. `longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data', 
  187. `longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data', 
  188. `longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data', 
  189. `longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data', 
  190. `longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data', 
  191. `longcol17` blob NOT NULL COMMENT 'store 8192 bytes data', 
  192. `longcol18` blob NOT NULL COMMENT 'store 16284 bytes data', 
  193. `longcol19` blob NOT NULL COMMENT 'store 20380 bytes data', 
  194. `longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data', 
  195. PRIMARY KEY (`id`) 
  196. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  197.  
  198. #相应的数据写入存储过程：mysp_longcol_4_ins() 
  199. CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int ) 
  200. begin 
  201. set @i = 1; 
  202. while @i < cnt do 
  203. insert into t_longcol_4(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10, 
  204. longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select 
  205. repeat('a',100), 
  206. repeat('a',100), 
  207. repeat('a',100), 
  208. repeat('a',100), 
  209. repeat('a',100), 
  210. repeat('a',256), 
  211. repeat('a',368), 
  212. repeat('a',496), 
  213. repeat('a',512), 
  214. repeat('a',640), 
  215. repeat('a',768), 
  216. repeat('a',912), 
  217. repeat('a',1024), 
  218. repeat('a',2048), 
  219. repeat('a',3082), 
  220. repeat('a',4096), 
  221. repeat('a',8192), 
  222. repeat('a',16284), 
  223. repeat('a',20380), 
  224. repeat('a',5977); 
  225. set @i = @i + 1; 
  226. end while; 
  227. end; 
  228.  
  229. #表5：将64KB数据离散存储在多个VARCHAR、BLOB列中，和t_longcol_4相比，变化在于前面的几个列长度改成了255，但实际存储长度还是100字节 
  230. CREATE TABLE `t_longcol_5` ( 
  231. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  232. `longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  233. `longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  234. `longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  235. `longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  236. `longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  237. `longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data', 
  238. `longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data', 
  239. `longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data', 
  240. `longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data', 
  241. `longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data', 
  242. `longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data', 
  243. `longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data', 
  244. `longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data', 
  245. `longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data', 
  246. `longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data', 
  247. `longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data', 
  248. `longcol17` blob NOT NULL COMMENT 'store 8192 bytes data', 
  249. `longcol18` blob NOT NULL COMMENT 'store 16284 bytes data', 
  250. `longcol19` blob NOT NULL COMMENT 'store 20380 bytes data', 
  251. `longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data', 
  252. PRIMARY KEY (`id`) 
  253. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  254.  
  255. #相应的数据写入存储过程：mysp_longcol_5_ins() 
  256. CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int ) 
  257. begin 
  258. set @i = 1; 
  259. while @i < cnt do 
  260. insert into t_longcol_5(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10, 
  261. longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select 
  262. repeat('a',100), 
  263. repeat('a',100), 
  264. repeat('a',100), 
  265. repeat('a',100), 
  266. repeat('a',100), 
  267. repeat('a',256), 
  268. repeat('a',368), 
  269. repeat('a',496), 
  270. repeat('a',512), 
  271. repeat('a',640), 
  272. repeat('a',768), 
  273. repeat('a',912), 
  274. repeat('a',1024), 
  275. repeat('a',2048), 
  276. repeat('a',3082), 
  277. repeat('a',4096), 
  278. repeat('a',8192), 
  279. repeat('a',16284), 
  280. repeat('a',20380), 
  281. repeat('a',5977); 
  282. set @i = @i + 1; 
  283. end while; 
  284. end; 
  285.  
  286. #从下面开始，参考第3条建议进行分表，每个表所有列长度总和 
  287. #分表1，行最大长度 100 + 100 + 100 + 100 + 100 + 255 + 368 + 496 + 512 + 640 + 768 + 912 + 3082 = 7533 字节 
  288. CREATE TABLE `t_longcol_51` ( 
  289. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  290. `longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  291. `longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  292. `longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  293. `longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  294. `longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data', 
  295. `longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data', 
  296. `longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data', 
  297. `longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data', 
  298. `longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data', 
  299. `longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data', 
  300. `longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data', 
  301. `longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data', 
  302. `longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data', 
  303. PRIMARY KEY (`id`) 
  304. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  305.  
  306. #分表2，行最大长度 1024 + 2048 + 4096 = 7168 字节 
  307. CREATE TABLE `t_longcol_52` ( 
  308. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  309. `longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data', 
  310. `longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data', 
  311. `longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data', 
  312. PRIMARY KEY (`id`) 
  313. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  314.  
  315. #分表3，行最大长度 8192 字节 
  316. CREATE TABLE `t_longcol_53` ( 
  317. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  318. `longcol17` blob NOT NULL COMMENT 'store 8192 bytes data', 
  319. PRIMARY KEY (`id`) 
  320. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  321.  
  322. #分表4，行最大长度 16284 + 20380 = 36664 字节 
  323. CREATE TABLE `t_longcol_54` ( 
  324. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  325. `longcol18` blob NOT NULL COMMENT 'store 16284 bytes data', 
  326. `longcol19` blob NOT NULL COMMENT 'store 20380 bytes data', 
  327. PRIMARY KEY (`id`) 
  328. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  329.  
  330. #分表5，行最大长度 5977 + 4 = 5981 字节 
  331. CREATE TABLE `t_longcol_55` ( 
  332. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  333. `longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data', 
  334. PRIMARY KEY (`id`) 
  335. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  336.  
  337. #相应的数据写入存储过程：mysp_longcol_51_ins() 
  338. CREATE PROCEDURE `mysp_longcol_51_ins`( in cnt int ) 
  339. begin 
  340. set @i = 1; 
  341. while @i < cnt do 
  342. insert into t_longcol_51(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10, 
  343. longcol11,longcol12,longcol15) select 
  344. repeat('a',100), 
  345. repeat('a',100), 
  346. repeat('a',100), 
  347. repeat('a',100), 
  348. repeat('a',100), 
  349. repeat('a',256), 
  350. repeat('a',368), 
  351. repeat('a',496), 
  352. repeat('a',512), 
  353. repeat('a',640), 
  354. repeat('a',768), 
  355. repeat('a',912), 
  356. repeat('a',3082); 
  357.  
  358. insert into t_longcol_52(longcol13,longcol14,longcol16) select 
  359. repeat('a',1024), 
  360. repeat('a',2048), 
  361. repeat('a',4096); 
  362.  
  363. insert into t_longcol_53(longcol17) select repeat('a',8192); 
  364.  
  365. insert into t_longcol_54(longcol18,longcol19) select 
  366. repeat('a',16284), 
  367. repeat('a',20380); 
  368.  
  369. insert into t_longcol_55(longcol20) select repeat('a',5977); 
  370.  
  371. set @i = @i + 1; 
  372. end while; 
  373. end; 


上述各个测试表都写入5000行记录后，再来对比下其表空间文件大小，以及重整表空间后的大小，观察碎片率。详细对比见下：

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

最后一种分表方式中，5个子表的表空间文件大小总和是 40960 + 40960 + 98304 + 286720 + 40960 = 507904 字节。

可以看到，这种方式的总大小和原始表大小差距最小，其他几种存储方式都比这个来的大。

上一篇：使用sysbench来测试MySQL性能的详细教程

下一篇：探究MySQL中索引和提交频率对InnoDB表写入速度的影响

发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
学习交流
更多

硬盘分区如何设置准确的分区空间

硬盘分区如何设置准确的分区空间...
热门图片
更多
猜你喜欢的新闻
猜你喜欢的关注

新闻热点

最牛同桌！我考了696分我同桌考了703分
2024-06-26 22:28:41
650分！高二女生考入北大：遗憾不能上高三
2024-06-26 22:26:16
男生估分600只考了397 妈妈：高考虽重要，但不代表所有
2024-06-26 22:23:01
唐尚珺回应是否会直播带货：有人出100万想和他合作！
2024-06-25 19:29:23
名校抢人名场面：清华、北大太拼了！
2024-06-25 19:22:14
男生高考语文满分！网友：第一次听说
2024-06-25 19:19:15

疑难解答

图片精选

网友关注

关于本站 - 网上投稿 - 商务合作 - 隐私政策 - 网站地图
Copyright © 2008 - 2024 VEVB.COM. All Rights Reserved.武林网 版权所有