记一次MySQL大表空间收缩

最近收到 MySQL 数据库磁盘空间使用率过高的报警,发现其中的一个表空间占用竟然高达三百多 G。删除了一半多的数据后,可是表文件大小还是没变,当时就懵了。
请教 DBA 才知道删除大量数据后存在数据空洞,MySQL 不会自动收缩表空间,需要手工操作。

产生原因

  1. InnoDB 里的数据都是用 B+树的结构组织的,每当删除了一条记录后,InnoDB 引擎只会把这个记录标记为删除,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大。

  2. 记录的复用只限定符合范围条件的数据。比如说删除一条 ID 为 400 的数据,当执行插入操作 ID 为 400 前后的数据时,MySQL 可能会复用这个位置。但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片。

  3. 如果是随机插入数据也会造成数据空洞。

解决方案

  1. 对数据导出后进行收缩,然后导入数据。
  2. 重建表

目前没有导出数据的条件,采用重建表的方法,可以采用alter table A engine=InnoDB 命令。
MySQL 5.6 版本开始引入了 Online DDL,允许在表上执行 DDL 的操作(比如创建索引)的同时不阻塞并发的 DML 操作 和 查询(select)操作。

  • Algorithm=Inplace :为了避免表拷贝导致的实例性能问题(空间、I/O 问题),建议在 DDL 中包含该选项。如果 DDL 操作不支持 Algorithm=Inplace 方式,DDL 操作会立刻返回错误。

  • Lock=None :为了在 DDL 操作过程中不影响业务的 DML 操作,建议在 DDL 中包含该选项。如果 DDL 操作不支持 Lock=None (允许并行 DML 操作)选项,DDL 操作会立刻返回错误。

所有的 DDL 操作均建议在业务低峰期进行,避免对业务产生影响。经过测试,删除表的数据后,对应的磁盘空间也正常释放了。