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

本是古典何须时尚 2023-02-26 10:24 12阅读 0赞

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

首先，我们先说说怎么使用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，在后期排查的时候非常误导人！

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

+--------------------------+----------+
    | 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)

test> show warnings;  
\+---------+------+-----------------------------------------------------------------------+  
| Level | Code | Message |  
\+---------+------+-----------------------------------------------------------------------+  
| Warning | 1478 | InnoDB: KEY\_BLOCK\_SIZE requires innodb\_file\_format > Antelope. |  
| Warning | 1478 | InnoDB: ignoring KEY\_BLOCK\_SIZE=8. |  
| Warning | 1478 | InnoDB: ROW\_FORMAT=COMPRESSED requires innodb\_file\_format > Antelope. |  
| Warning | 1478 | InnoDB: assuming ROW\_FORMAT=COMPACT. |  
\+---------+------+-----------------------------------------------------------------------+  
4 rows in set (0.00 sec)

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

我们可以从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;

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

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: 16384
    Max_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)

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

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

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

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: 8192
    Max_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)

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

No3:

--------------------

发现和innodb\_file\_format相关的2个参数:

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

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

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

官方的解释可以参考如下的链接：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。

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

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)

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

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

[aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]: /images/20230209/c0284b7504004dd09b419e0e936cdd0d.png

发表评论取消回复

表情：

评论列表（有 0 条评论，12人围观）

还没有评论，来说两句吧...

相关阅读

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

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

本是古典何须时尚/ 2023年02月26日 10:24/ 0 赞/ 13 阅读

相关 sqoop使用过程中遇到的坑

一、mysql和hive互相当导入时遇到的注意：为了避免其他问题最好先将hive配置文件下的hive-site.xml复制到sqoop配置文件夹下并保持一致； 1、

旧城等待，/ 2023年02月21日 06:59/ 0 赞/ 35 阅读

相关 GitHub使用过程中遇到的坑（已解决）

GitHub使用过程中遇到的坑（已解决） 1、错误 ! \[rejected\] master -> master (non-fast-forward)

痛定思痛。/ 2023年02月14日 02:25/ 0 赞/ 11 阅读

相关升级webpack5过程中遇到的坑

将项目的weback从3升到了5，遇到了一系列的问题，这里记录两个： 1. extract-text-webpack-plugin兼容问题 2. webpack-dev-

忘是亡心i/ 2022年10月27日 15:49/ 0 赞/ 521 阅读

相关 swagger使用过程中遇到的坑

前言今天给两个项目添加swgger 都不顺利这里将，两个项目中遇到问题和解决方案贴出来。基本配置这里先贴出比较通用的swgger 的配置 1、添加swgg

不念不忘少年蓝@/ 2022年09月02日 05:17/ 0 赞/ 319 阅读

相关蓝牙模块调试过程中遇到的坑

HC05和HC06的AT指令是不一样的 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9

Myth丶恋晨/ 2022年08月29日 00:06/ 0 赞/ 213 阅读

相关 angular 学习过程中遇到的坑（整理中）

最近想学习下angular，然后该文章记录了我在学习中遇到的问题 1、参照书上的例子 hello.html <!DOCTYPE html> <html ng-ap

╰+攻爆jí腚メ/ 2022年07月15日 00:12/ 0 赞/ 261 阅读

相关安装ElasticSearch过程遇到的坑

采用默认安装时是可以成功安装的，但是只能在本机通过127.0.0.1:9200访问，修改: network.host: 为本机IP后，启动报错。 elasticsearch

绝地灬酷狼/ 2022年06月12日 02:42/ 0 赞/ 330 阅读

相关 flutter 安装过程遇到的坑

flutter doctor 需要jdk1.8 需要jdk1.8；如果装的是jdk9及以后的版本，flutter doctor会一直提示你需要升级android SDK

淡淡的烟草味﹌/ 2021年12月16日 14:43/ 0 赞/ 397 阅读

相关 git使用过程中遇到的一些坑

git SSL certificate problem: unable to get local issuer certificate 当git push远程仓库使用ht...

分手后的思念是犯贱/ 2021年06月10日 10:31/ 0 赞/ 679 阅读