技术干货 | 如何将大表在线改造为分区表并释放空间

技术干货 | 如何将大表在线改造为分区表并释放空间

出现麻烦

你是否遇到过如此令人麻烦的问题，比如：由于前期规划不当，后期库表数据量猛增；或者由于“年久失修”而造成的表数据积累不断。等等诸如此类 大表 问题。

面对这些大表，是删也不能删，清理又困难。

即使你不嫌麻烦，一点一点的通过 delete 清理了数据，但是还会有令人头疼的 表碎片 问题。

面对 表ibd 文件的只增不减，也许想到了可以通过这些方法解决：

• ALTER TABLE xxx engine=innodb;
• OPTIMIZE TABLE xxx;

这两种方法虽然在清理表数据后可以释放空间，但是会造成锁表问题。

如何优雅在线的清理数据并释放空间呢？

清理数据方法有很多，比如 [delete] [truncate] [删除分区] 等，其实最简单有效的方法就是 删除分区 。

有人会问：当初数据库中没有设计为分区表，谁也没想到这个表现在数据量这么大，这该怎么办？

再也不用抱怨你接手了一个烂摊子，也不用频繁接收表文件过大造成的文件系统告警了…

我们"在线"把大表改造成分区表不就好了！

这将优雅的实现：

• 在线 ——> 不影响业务
• 分区 ——> 便于管理
• 空间 ——> 删除分区直接释放

闪亮登场

实现我们的大目标，那就需要借助专业的小工具。他就是

percona-toolkit 之 pt-online-schema-change

概要

在线修改表结构，特点是修改过程中不会造成读写阻塞。

原理

工作原理是创建要更改的表的空副本，根据需要对其进行修改，然后将原始表中的行复制到新表中。复制完成后，它会移走原始表并用新表替换。默认情况下，它还会删除原始表。

用法

pt-online-schema-change [OPTIONS] DSN

选项

详见：https://greatsql.cn/docs/8.0.32-27/6-oper-guide/10-5-pt-development.html#pt-online-schema-change

最佳实践

1. 测试表

CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `data_value` varchar(255) DEFAULT NULL,
  `expired_date` int unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

2. 测试数据

表中存在数据 10000000

greatsql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.03 sec)

3. 查看表文件大小

t1.ibd 文件大小有 468M

-rw-r----- 1 mysql mysql 468M May  5 06:07 t1.ibd

4. 修改主键

如果使用分区表，分区键需要包含在主键中，使用 pt-online-schema-change 可以 online 修改主键。

首先，使用 --dry-run 进行执行前测试

pt-online-schema-change h=192.168.105.101,P=3306,u=root,p=root,D=test,t=t1 --alter "DROP PRIMARY KEY,ADD PRIMARY KEY (id, expired_date)" --recursion-method=none --no-check-replication-filters --no-check-unique-key-change --alter-foreign-keys-method auto --print --dry-run

执行结果：

Operation, tries, wait:
......
2025-05-05T06:59:52 Dropping new table...
DROP TABLE IF EXISTS `test`.`_t1_new`;
2025-05-05T06:59:52 Dropped new table OK.
Dry run complete.  `test`.`t1` was not altered.

然后，使用 --execute 执行

pt-online-schema-change h=192.168.105.101,P=3306,u=root,p=root,D=test,t=t1 --alter "DROP PRIMARY KEY,ADD PRIMARY KEY (id, expired_date)" --no-check-alter --recursion-method=none --no-check-replication-filters --no-check-unique-key-change --alter-foreign-keys-method auto --print --execute

执行结果：

Operation, tries, wait:
......
Copying `test`.`t1`:  49% 00:30 remain
Copying `test`.`t1`:  96% 00:02 remain
2025-05