技术干货 | 怎样将大表在线改造为分区表并开释空间

技术干货 | 怎样将大表在线改造为分区表并开释空间

出现贫困

你是否遇到过如此令人贫困的问题，比如：由于前期规划不妥，后期库表数据量猛增；或者由于“年久失修”而造成的表数据积累不断。等等诸如此类 大表 问题。
面对这些大表，是删也不能删，清算又困难。
纵然你不嫌贫困，一点一点的通过 delete 清算了数据，但是还会有令人头疼的 表碎片 问题。
面对 表ibd 文件的只增不减，也许想到了可以通过这些方法解决：

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

这两种方法虽然在清算表数据后可以开释空间，但是会造成锁表问题。
怎样优雅在线的清算数据并开释空间呢？

清算数据方法有很多，比如 [delete] [truncate] [删除分区] 等，其实最简单有效的方法就是 删除分区 。
有人会问：当初数据库中没有设计为分区表，谁也没想到这个体如今数据量这么大，这该怎么办？
再也不用抱怨你接手了一个烂摊子，也不用频繁接收表文件过大造成的文件系统告警了......
我们"在线"把大表改造成分区表不就好了！

这将优雅的实现：

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

闪亮登场

实现我们的大目标，那就必要借助专业的小工具。他就是
percona-toolkit 之 pt-online-schema-change

概要

在线修改表布局，特点是修改过程中不会造成读写阻塞。
原理

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

1. 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. 测试表

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

复制代码

2. 测试数据

表中存在数据 10000000

1. greatsql> SELECT COUNT(*) FROM t1;
2. +----------+
3. | COUNT(*) |
4. +----------+
5. | 10000000 |
6. +----------+
7. 1 row in set (3.03 sec)

复制代码

3. 检察表文件大小

t1.ibd 文件大小有 468M

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

复制代码

4. 修改主键

如果使用分区表，分区键必要包含在主键中，使用 pt-online-schema-change 可以 online 修改主键。
首先，使用 --dry-run 举行实行前测试

1. 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

复制代码

实行结果：

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

复制代码

然后，使用 --execute 实行

1. 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

复制代码

实行结果：

1. Operation, tries, wait:
2. ......
3. Copying `test`.`t1`:  49% 00:30 remain
4. Copying `test`.`t1`:  96% 00:02 remain
5. 2025-05-05T07:06:55 Copied rows OK.
6. 2025-05-05T07:06:55 Analyzing new table...
7. 2025-05-05T07:06:55 Swapping tables...
8. RENAME TABLE `test`.`t1` TO `test`.`_t1_old`, `test`.`_t1_new` TO `test`.`t1`
9. 2025-05-05T07:06:55 Swapped original and new tables OK.
10. 2025-05-05T07:06:55 Dropping old table...
11. DROP TABLE IF EXISTS `test`.`_t1_old`
12. 2025-05-05T07:06:55 Dropped old table `test`.`_t1_old` OK.
13. 2025-05-05T07:06:55 Dropping triggers...
14. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_del`
15. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_upd`
16. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_ins`
17. 2025-05-05T07:06:55 Dropped triggers OK.
18. Successfully altered `test`.`t1`.

复制代码

检察表布局

1. greatsql> SHOW CREATE TABLE t1\G;
2. *************************** 1. row ***************************
3.        Table: t1
4. Create Table: CREATE TABLE `t1` (
5.   `id` int NOT NULL AUTO_INCREMENT,
6.   `data_value` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
7.   `expired_date` int unsigned NOT NULL,
8.   PRIMARY KEY (`id`,`expired_date`)
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
10. 1 row in set (0.00 sec)

复制代码

此时可以看到，表 t1 的主键已经修改为 (id,expired_date) 了。
5. 调解为分区表

使用 pt-online-schema-change 可以 online 调解为分区表。
首先，使用 --dry-run 举行实行前测试

1. pt-online-schema-change h=192.168.105.101,P=3306,u=root,p=root,D=test,t=t1 \
2. --alter "
3.   PARTITION BY RANGE (expired_date) (
4.     PARTITION p202501 VALUES LESS THAN (1738339200),  -- 2025-02-01 00:00:00
5.     PARTITION p202502 VALUES LESS THAN (1740758400),  -- 2025-03-01 00:00:00
6.     PARTITION p202503 VALUES LESS THAN (1743436800),  -- 2025-04-01 00:00:00
7.     PARTITION p202504 VALUES LESS THAN (1746028800),  -- 2025-05-01 00:00:00
8.     PARTITION p202505 VALUES LESS THAN (1748707200),  -- 2025-06-01 00:00:00
9.     PARTITION p_max VALUES LESS THAN MAXVALUE
10.   )" \
11. --recursion-method=none --no-check-replication-filters --no-check-unique-key-change --alter-foreign-keys-method auto --print --dry-run

复制代码

实行结果：

1. Operation, tries, wait:
2. ......
3. 2025-05-05T07:18:17 Dropping new table...
4. DROP TABLE IF EXISTS `test`.`_t1_new`;
5. 2025-05-05T07:18:17 Dropped new table OK.
6. Dry run complete.  `test`.`t1` was not altered.

复制代码

然后，使用 --execute 实行

1. pt-online-schema-change h=192.168.105.101,P=3306,u=root,p=root,D=test,t=t1 \
2. --alter "
3.   PARTITION BY RANGE (expired_date) (
4.     PARTITION p202501 VALUES LESS THAN (1738339200),  -- 2025-02-01 00:00:00
5.     PARTITION p202502 VALUES LESS THAN (1740758400),  -- 2025-03-01 00:00:00
6.     PARTITION p202503 VALUES LESS THAN (1743436800),  -- 2025-04-01 00:00:00
7.     PARTITION p202504 VALUES LESS THAN (1746028800),  -- 2025-05-01 00:00:00
8.     PARTITION p202505 VALUES LESS THAN (1748707200),  -- 2025-06-01 00:00:00
9.     PARTITION p_max VALUES LESS THAN MAXVALUE
10.   )" \
11. --recursion-method=none --no-check-replication-filters --no-check-unique-key-change --alter-foreign-keys-method auto --print --execute

复制代码

实行结果：

1. Operation, tries, wait:
2. ......
3. Operation, tries, wait:
4. ......
5. Copying `test`.`t1`:  24% 01:30 remain
6. Copying `test`.`t1`:  59% 00:40 remain
7. Copying `test`.`t1`:  97% 00:02 remain
8. 2025-05-05T07:22:02 Copied rows OK.
9. 2025-05-05T07:22:02 Analyzing new table...
10. 2025-05-05T07:22:02 Swapping tables...
11. RENAME TABLE `test`.`t1` TO `test`.`_t1_old`, `test`.`_t1_new` TO `test`.`t1`
12. 2025-05-05T07:22:02 Swapped original and new tables OK.
13. 2025-05-05T07:22:02 Dropping old table...
14. DROP TABLE IF EXISTS `test`.`_t1_old`
15. 2025-05-05T07:22:02 Dropped old table `test`.`_t1_old` OK.
16. 2025-05-05T07:22:02 Dropping triggers...
17. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_del`
18. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_upd`
19. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_ins`
20. 2025-05-05T07:22:02 Dropped triggers OK.
21. Successfully altered `test`.`t1`.

复制代码

检察表布局

1. greatsql> SHOW CREATE TABLE t1\G
2. *************************** 1. row ***************************
3.        Table: t1
4. Create Table: CREATE TABLE `t1` (
5.   `id` int NOT NULL AUTO_INCREMENT,
6.   `data_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
7.   `expired_date` int unsigned NOT NULL,
8.   PRIMARY KEY (`id`,`expired_date`)
9. ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
10. /*!50100 PARTITION BY RANGE (`expired_date`)
11. (PARTITION p202501 VALUES LESS THAN (1738339200) ENGINE = InnoDB,
12. PARTITION p202502 VALUES LESS THAN (1740758400) ENGINE = InnoDB,
13. PARTITION p202503 VALUES LESS THAN (1743436800) ENGINE = InnoDB,
14. PARTITION p202504 VALUES LESS THAN (1746028800) ENGINE = InnoDB,
15. PARTITION p202505 VALUES LESS THAN (1748707200) ENGINE = InnoDB,
16. PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
17. 1 row in set (0.00 sec)

复制代码

此时可以看到，表 t1 已经调解为分区表了。
6. 测试验证

• 查询表分区及数据情况
  

1. -- 查询数据
2. greatsql> SELECT COUNT(*) FROM t1;
3. +----------+
4. | COUNT(*) |
5. +----------+
6. | 10000000 |
7. +----------+
8. 1 row in set (3.39 sec)
10. -- 查询分区
11. greatsql> SELECT table_schema,table_name,partition_name,partition_expression,partition_description FROM information_schema.partitions WHERE table_name = 't1' order by partition_name asc;
12. +--------------+------------+----------------+----------------------+-----------------------+
13. | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
14. +--------------+------------+----------------+----------------------+-----------------------+
15. | test         | t1         | p202501        | `expired_date`       | 1738339200            |
16. | test         | t1         | p202502        | `expired_date`       | 1740758400            |
17. | test         | t1         | p202503        | `expired_date`       | 1743436800            |
18. | test         | t1         | p202504        | `expired_date`       | 1746028800            |
19. | test         | t1         | p202505        | `expired_date`       | 1748707200            |
20. | test         | t1         | p_max          | `expired_date`       | MAXVALUE              |
21. +--------------+------------+----------------+----------------------+-----------------------+
22. 6 rows in set (0.01 sec)

复制代码

• 查询表文件情况
  

1. total 509M
2. -rw-r----- 1 mysql mysql 116M May  5 07:22 t1#p#p202501.ibd
3. -rw-r----- 1 mysql mysql 104M May  5 07:22 t1#p#p202502.ibd
4. -rw-r----- 1 mysql mysql 116M May  5 07:22 t1#p#p202503.ibd
5. -rw-r----- 1 mysql mysql 112M May  5 07:22 t1#p#p202504.ibd
6. -rw-r----- 1 mysql mysql  60M May  5 07:22 t1#p#p202505.ibd
7. -rw-r----- 1 mysql mysql 112K May  5 07:22 t1#p#p_max.ibd

复制代码

可以看到，t1.ibd 文件已经不存在了，每个 ibd 文件即为 分区文件。

• 删除数据，开释空间
  如果必要删除一些数据，直接删除分区即可
  

1. -- 删除 202501 分区数据
2. greatsql> ALTER TABLE t1 DROP PARTITION p202501;
3. Query OK, 0 rows affected (0.07 sec)
4. Records: 0  Duplicates: 0  Warnings: 0
6. greatsql> SELECT COUNT(*) FROM t1;
7. +----------+
8. | COUNT(*) |
9. +----------+
10. |  7727745 |
11. +----------+
12. 1 row in set (2.53 sec)

复制代码

数据删除完成，看看文件系统所占空间是否开释？

1. total 393M
2. -rw-r----- 1 mysql mysql 104M May  5 07:42 t1#p#p202502.ibd
3. -rw-r----- 1 mysql mysql 116M May  5 07:22 t1#p#p202503.ibd
4. -rw-r----- 1 mysql mysql 112M May  5 07:22 t1#p#p202504.ibd
5. -rw-r----- 1 mysql mysql  60M May  5 07:22 t1#p#p202505.ibd
6. -rw-r----- 1 mysql mysql 112K May  5 07:22 t1#p#p_max.ibd

复制代码

可以看到，分区 p202501 所使用的 ibd 文件也不存在了，文件系统空间得到了开释。
Enjoy GreatSQL 😃
关于 GreatSQL

GreatSQL是实用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产情况，且完全免费并兼容MySQL或Percona Server。
相关链接：   GreatSQL社区        Gitee        GitHub        Bilibili
GreatSQL社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

技术交换群：