Innodb表压缩过程中遇到的坑（innodb_file_format）

来源：互联网发布：知乎小米软文编辑：程序博客网时间：2024/06/06 03:46

对于越来越多的数据，数据库的容量越来越大，压缩也就越来越常见了。在我的实际工作中进行过多次压缩工作，也遇到多次问题，在此和大家分享一下。

首先，我们先说说怎么使用innodb的压缩.

第一，mysql的版本需要大于5.5第二，设置innodb_file_format=barracuda第三，create table或者alter talble 增加 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;(默认的key_block_size=16)

其实很简单，根据经验，一般压缩比例可以达到30%-40%

然后，我们说说我在压缩过程中遇到的坑和发现的关联，当然有些比较二。

No1：

问题：使用脚本批量alter操作，只动态修改了实例的innodb_file_format=barracuda，然后alter所有数据库中的表。并没有修改配置文件中的设置。

结果：表中已有数据被压缩，但是在重启之后，由于innodb_file_format参数被重新修改成antelope，导致后续写入的数据没有被压缩（虽然表结构中有row_format=compressed，但是不会起作用），最终表体积仍然很大。

教训：实例和配置文件要同步修改。（这个错误最二，太低级 T_T，不解释了。）

No2:

问题：在innodb_file_format=antelope的情况下，建立压缩表（表结构中带有row_format=compressed），然后在设置innodb_file_format=barracuda。

结果：表结构中的row_format=compressed被忽略，后续写入表的数据并没有被压缩，最终导致表体积大。

教训：先修改innodb_file_format（session和global都需要修改），在create table或者alter table。

但是以上这点有个坑人的地方，在错误的顺序下，表是可以被成功建立了，只是会有warning，但是表结构中会有row_format=compressed，在后期排查的时候非常误导人！

+--------------------------+----------+| Variable_name            | Value    |+--------------------------+----------+| innodb_file_format       | Antelope || innodb_file_format_check | ON       || innodb_file_format_max   | Antelope |+--------------------------+----------+3 rows in set (0.00 sec)

test> create table test_1 (x int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected, 4 warnings (0.07 sec)

我们可以从warnings中看见，压缩设置被忽略了。但是最坑爹的一点是，如果我们show create table会有如下结果：

test_1 | CREATE TABLE `test_1` (  `x` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

在这种情况下，我们吸取教训，不能使用show create table看压缩状态，而是应该用show table status;

show table status like 'test_1'\G;*************************** 1. row ***************************           Name: test_1         Engine: InnoDB        Version: 10     Row_format: Compact           Rows: 0 Avg_row_length: 0    Data_length: 16384Max_data_length: 0   Index_length: 0      Data_free: 0 Auto_increment: NULL    Create_time: 2013-09-27 15:59:13    Update_time: NULL     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8        Comment:1 row in set (0.00 sec)

坑爹啊，不说了。正常应该这个样子

show table status like 'test_2'\G;*************************** 1. row ***************************           Name: test_2         Engine: InnoDB        Version: 10     Row_format: Compressed           Rows: 0 Avg_row_length: 0    Data_length: 8192Max_data_length: 0   Index_length: 0      Data_free: 0 Auto_increment: NULL    Create_time: 2013-09-27 16:09:51    Update_time: NULL     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8        Comment:1 row in set (0.00 sec)

No3:

发现和innodb_file_format相关的2个参数:

+--------------------------+-----------+| Variable_name            | Value     |+--------------------------+-----------+| innodb_file_format       | Barracuda || innodb_file_format_check | ON        || innodb_file_format_max   | Barracuda |+--------------------------+-----------+3 rows in set (0.00 sec)

官方的解释可以参考如下的链接：http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format

测试过程中发现，如果是innodb_file_format=barracuda而innodb_file_format_max=antelop，那么在建立压缩表的时候，max会自动变成barracuda。

localhost.test>show global variables like 'innodb_file_format%';+--------------------------+-----------+| Variable_name            | Value     |+--------------------------+-----------+| innodb_file_format       | Barracuda || innodb_file_format_check | ON        || innodb_file_format_max   | Antelope  |+--------------------------+-----------+3 rows in set (0.00 sec)localhost.test>create table test_4(x int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;Query OK, 0 rows affected (0.01 sec)localhost.test>show global variables like 'innodb_file_format%';+--------------------------+-----------+| Variable_name            | Value     |+--------------------------+-----------+| innodb_file_format       | Barracuda || innodb_file_format_check | ON        || innodb_file_format_max   | Barracuda |+--------------------------+-----------+3 rows in set (0.00 sec)

如果innodb_file_format_check这参数解释的，决定innodb是否会检查共享表空间中的表格式的tag，如果检查开启，那么当标记的表格式的tag高于innodb可以支撑的表格式，那么innodb会报错，并停止启动。如果支持，那么会将innodb_file_format_max的值改为这个tag的值。

这个坑用了几个小时。。查看mysql版本时发现xmapp里竟然是MariaDB,不过也符合上述mysql的三个条件。相关语句保存下来

0 0