优化MySQL InnoDB：独立表空间与BLOB存储策略

134 浏览量更新于2024-08-31 收藏 101KB PDF 举报

"提高MySQL中InnoDB表BLOB列存储效率的方法主要涉及表空间设置、数据页配置、行格式选择以及优化策略。" 在MySQL中，InnoDB存储引擎是广泛用于处理事务处理和数据安全性的引擎。对于含有大量BLOB（Binary Large Object）列的表，优化存储效率至关重要，因为BLOB类型通常用于存储大对象，如图片、文档等，占用空间较大。以下是一些关键点来提高BLOB列的存储效率： 1. **独立表空间**：启用`innodb_file_per_table`选项，让每个表都有自己的表空间文件，这有助于管理和维护，同时在需要的时候更容易进行备份或迁移。在MySQL 5.5及更高版本中，该选项可以在线动态修改并立即生效，对于旧版本，修改后需重启MySQL服务。 2. **数据页大小**：默认情况下，InnoDB的数据页大小为16KB。在MySQL 5.6之后，可以使用`innodb_page_size`选项调整页面大小，但不建议随意更改，除非对系统性能有深入理解，因为这可能影响到查询性能和磁盘I/O。 3. **数据页预留空间**：InnoDB在写入新数据时，会预留1/16的空间，以减小频繁创建新数据页的需求。这有助于保持数据的连续性，提高读写效率。 4. **行记录长度**：每个数据页至少存储两行记录，所以单行记录的最大长度理论上限是8KB，但实际应小于这个值，因为还需要留出空间存储InnoDB的内部结构。 5. **顺序写入**：为了最大化数据页的填充率，建议使用自增列作为主键，这样新纪录的写入能尽可能地保持顺序，从而提高数据页的利用率。 6. **数据页收缩**：当数据页的填充率低于1/2时，InnoDB会进行收缩操作，释放空闲空间，以避免不必要的碎片。 7. **行格式选择**：MySQL 5.6的InnoDB支持多种行格式，包括COMPACT、REDUNDANT、DYNAMIC和COMPRESSED。COMPACT格式比REDUNDANT更节省空间，但DYNAMIC和COMPRESSED格式虽然节省更多空间，但可能导致TPS（每秒事务处理量）显著下降。因此，不推荐使用COMPRESSED格式，除非对性能影响有充分的预期。 8. **BLOB列处理**：对于DYNAMIC或COMPRESSED行格式，BLOB等长列会被单独存储，以减少对主数据页的影响。这种分离存储方式可以避免长列对其他短列的影响，提高整体的存储效率。在设计数据库表时，结合以上策略，可以有效提高InnoDB存储BLOB列的效率。同时，定期进行表优化，如重建索引和分析表，也能帮助维持良好的存储状态。此外，根据实际需求选择合适的存储引擎，如TokuDB，其对大数据列有更好的支持，也是一个值得考虑的方案。

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

首先，介绍下关于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列类型，游戏界同行可以关注下。

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

# 测试案例：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',5535);

set @i = @i + 1;

end while;

end;

下载后可阅读完整内容，剩余4页未读，立即下载

weixin_38692184

粉丝: 8
资源: 933

优化MySQL InnoDB：独立表空间与BLOB存储策略

mysql中blob数据处理方式

优化InnoDB表BLOB,TEXT列的存储效率

优化MySQL InnoDB BLOB存储：提升效率策略

MySQL存储引擎比较：InnoDB、MyISAM与其他引擎

sqlite中blob大字段存储在mysql中用什么字段存储

mysql提高存储空间

mysql中 blob最大限制字数是多少,怎么提升

mysql blob 查询慢

mysql blob 主从

mysql blob 性能

最新资源