为什么 MySQL 表中无数据，ibd 文件却很大？
为什么 MySQL 表中无数据，ibd 文件却很大？
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文约 800 字，预计阅读需要 3 分钟。
一、背景
在客户得生产环境中，数据表占用了大量得磁盘资源。主机上很多 ibd 文件都比较大，但是登录数据库查看，却发现表数据都是空的？
起初推测是这些表在做归档操作，最后没有做表碎片化整理导致的，但通过 binlog 分析得知，并没有发现大量 DELETE 操作。再通过开启并分析general log 日志发现，应用侧每天都会在凌晨时使用 insert into .. select * from xx 的 SQL 对前一天的数据大表进行备份。
由于数据库配置 max_binlog_cache_size[1] 参数限制，导致数据插入过程中达到 max_binlog_cache_size 最大值时出现事务回滚情况，但表空间没有得到释放。
以下是该问题现象的简单演示。
二、问题演示
通过 sysbench 工具创建 1 张 1000W 行的测试源表 sbtest1，并通过数据库命令查看表空间的占用大小。
mysql> select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (4.17 sec)
mysql> selectname,FILE_SIZE/1024/1024/1024as GB from information_schema.INNODB_TABLESPACES wherename='test/sbtest1';
+--------------+----------------+
| name | GB |
+--------------+----------------+
| test/sbtest1 | 2.269531250000 |
+--------------+----------------+
1 row in set (0.00 sec)
配置参数 max_binlog_cache_size 的大小。
mysql> set global max_binlog_cache_size=1*1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@max_binlog_cache_size;
+-------------------------+
| @@max_binlog_cache_size |
+-------------------------+
| 1073741824 |
+-------------------------+
1 row in set (0.00 sec)
手工模拟应用侧备份操作，将源表 sbtest1 数据备份到 t1 表中。
mysql> create table test.t1 like test.sbtest1;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test.t1 select * from test.sbtest1;
ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
查看 t1 表时，发现表中没有数据，但还是占用比较大的表空间。
mysql> select count(*) from test.t1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> selectname,FILE_SIZE/1024/1024/1024as GB from information_schema.INNODB_TABLESPACES wherename='test/t1';
+---------+----------------+
| name | GB |
+---------+----------------+
| test/t1 | 1.339843750000 |
+---------+----------------+
1 row in set (0.00 sec)
三、问题解决
临时解决方法
根据需要表数据大小，临时调大 max_binlog_cache_size 配置大小，让应用侧能正常完成数据备份。
长期解决方法
针对于以上场景的数据备份，应用侧每天将新数据写入以日命名的新表中，这样可以减少数据库的压力和对内存的消耗。
四、总结
在日常数据库的维护过程中，要注意合理配置及调整系统参数的配置，和减少一定量的大事务使用。需要我们制定一套数据库标准使用手册，并告知研发侧同学该如何更好地使用数据库。避免因为不当使用方式，给双方人员造成不必要的麻烦和财产损失。
五、max_binlog_cache_size 说明
https://dev.mysql.com
该参数用于控制事务 SQL 执行时需要使用的最大内存大小。在 32 位操作系统，该参数默认为 4G；64 位操作系统上，该参数默认为 16E。
在 MySQL 数据库实例未开启 GTID 时，建议配置的最大值不要超过 4G；在开启 GTID 后，不需要明确限制其最大值。
参考资料
[1]
max_binlog_cache_size: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_max_binlog_cache_size
本文关键字：#MySQL #binlog
腾讯云开发者
