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

2025-09-05 152

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

出现麻烦

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

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

即使你不嫌麻烦，一点一点的通过 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

修改主键

如果使用分区表，分区键需要包含在主键中，使用 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-05T07:06:55 Copied rows OK.
2025-05-05T07:06:55 Analyzing new table...
2025-05-05T07:06:55 Swapping tables...
RENAME TABLE `test`.`t1` TO `test`.`_t1_old`, `test`.`_t1_new` TO `test`.`t1`
2025-05-05T07:06:55 Swapped original and new tables OK.
2025-05-05T07:06:55 Dropping old table...
DROP TABLE IF EXISTS `test`.`_t1_old`
2025-05-05T07:06:55 Dropped old table `test`.`_t1_old` OK.
2025-05-05T07:06:55 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_ins`
2025-05-05T07:06:55 Dropped triggers OK.
Successfully altered `test`.`t1`.

查看表结构

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

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

调整为分区表

使用 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 "
  PARTITION BY RANGE (expired_date) (
    PARTITION p202501 VALUES LESS THAN (1738339200),  -- 2025-02-01 00:00:00
    PARTITION p202502 VALUES LESS THAN (1740758400),  -- 2025-03-01 00:00:00
    PARTITION p202503 VALUES LESS THAN (1743436800),  -- 2025-04-01 00:00:00
    PARTITION p202504 VALUES LESS THAN (1746028800),  -- 2025-05-01 00:00:00
    PARTITION p202505 VALUES LESS THAN (1748707200),  -- 2025-06-01 00:00:00
    PARTITION p_max VALUES LESS THAN MAXVALUE
  )" \
--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-05T07:18:17 Dropping new table...
DROP TABLE IF EXISTS `test`.`_t1_new`;
2025-05-05T07:18:17 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 "
  PARTITION BY RANGE (expired_date) (
    PARTITION p202501 VALUES LESS THAN (1738339200),  -- 2025-02-01 00:00:00
    PARTITION p202502 VALUES LESS THAN (1740758400),  -- 2025-03-01 00:00:00
    PARTITION p202503 VALUES LESS THAN (1743436800),  -- 2025-04-01 00:00:00
    PARTITION p202504 VALUES LESS THAN (1746028800),  -- 2025-05-01 00:00:00
    PARTITION p202505 VALUES LESS THAN (1748707200),  -- 2025-06-01 00:00:00
    PARTITION p_max VALUES LESS THAN MAXVALUE
  )" \
--recursion-method=none --no-check-replication-filters --no-check-unique-key-change --alter-foreign-keys-method auto --print --execute

执行结果：

Operation, tries, wait:
......
Operation, tries, wait:
......
Copying `test`.`t1`:  24% 01:30 remain
Copying `test`.`t1`:  59% 00:40 remain
Copying `test`.`t1`:  97% 00:02 remain
2025-05-05T07:22:02 Copied rows OK.
2025-05-05T07:22:02 Analyzing new table...
2025-05-05T07:22:02 Swapping tables...
RENAME TABLE `test`.`t1` TO `test`.`_t1_old`, `test`.`_t1_new` TO `test`.`t1`
2025-05-05T07:22:02 Swapped original and new tables OK.
2025-05-05T07:22:02 Dropping old table...
DROP TABLE IF EXISTS `test`.`_t1_old`
2025-05-05T07:22:02 Dropped old table `test`.`_t1_old` OK.
2025-05-05T07:22:02 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_ins`
2025-05-05T07:22:02 Dropped triggers OK.
Successfully altered `test`.`t1`.

查看表结构

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

此时可以看到，表 t1 已经调整为分区表了。

测试验证

查询表分区及数据情况

-- 查询数据
greatsql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.39 sec)

-- 查询分区
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;
+--------------+------------+----------------+----------------------+-----------------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+--------------+------------+----------------+----------------------+-----------------------+
| test         | t1         | p202501        | `expired_date`       | 1738339200            |
| test         | t1         | p202502        | `expired_date`       | 1740758400            |
| test         | t1         | p202503        | `expired_date`       | 1743436800            |
| test         | t1         | p202504        | `expired_date`       | 1746028800            |
| test         | t1         | p202505        | `expired_date`       | 1748707200            |
| test         | t1         | p_max          | `expired_date`       | MAXVALUE              |
+--------------+------------+----------------+----------------------+-----------------------+
6 rows in set (0.01 sec)

查询表文件情况

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

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

删除数据，释放空间如果需要删除一些数据，直接删除分区即可

-- 删除 202501 分区数据
greatsql> ALTER TABLE t1 DROP PARTITION p202501;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

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

total 393M
-rw-r----- 1 mysql mysql 104M May  5 07:42 t1#p#p202502.ibd
-rw-r----- 1 mysql mysql 116M May  5 07:22 t1#p#p202503.ibd
-rw-r----- 1 mysql mysql 112M May  5 07:22 t1#p#p202504.ibd
-rw-r----- 1 mysql mysql  60M May  5 07:22 t1#p#p202505.ibd
-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-54-1-1.html

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

（对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~）

技术交流群：

微信&QQ群：

QQ群：533341697

微信群：添加GreatSQL社区助手（微信号：wanlidbc ）好友，待社区助手拉您进群。

微信关注我们

原文链接：https://my.oschina.net/GreatSQL/blog/18690717

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

TDS数据治理深度实践：从标准化到智能化的演进之路

TDS数据治理深度实践：从标准化到智能化的演进之路 TDS（Turing Data Studio）是专注于数据开发和治理的平台。其架构涵盖了从基础设施到用户功能的各个层次，包括数据开发、数仓管理、监控运维和资源管理等模块，支持高效的任务调度、资源管理和数据血缘分析。上一代大数据产品在实际运行中仍存在以下关键问题： 1. 开发阶段风险控制不足：数据开发任务经过修改调试后，会直接用于处理线上数据，存在误操作直接影响线上数据的隐患。 2. 产出阶段质量保障滞后：数据产出质量。需要等数据完全产出后，通过人工校验或下游反馈发现，无法在数据产出过程中实时拦截空值、异常值等问题。数据产出时效性。任务执行达到重试失败上限或触发延迟产出报警后，数据RD才能收到通知，难以有效维持数据时效性。 3. 运维阶段处理低效：日志分析有时需要值班同学帮助查看定位原因排查问题，依赖人工经验，处理问题效率较低。数据产出异常时，由于涉及的类型和层级较多，难以快速准确识别下游受损范围，并导致未及时回溯数据。为了解决上述问题，TDS建设了体系化的数据治理方案。流程标准化（规范开发）→ 质量可控化（主动防控）→...

2025-09-04

137

编者按： AI 真的在"思考"吗？当模型面对数学推理、代码生成或复杂决策时，它是如何一步步推演出答案的？如果你曾困惑于大模型在关键任务中表现不稳定、缺乏可解释性，甚至生成结果难以验证，那么你并不孤单。这些痛点不仅影响研发效率，更直接制约了AI在高风险场景中的落地可靠性。本文系统梳理了测试时计算（test-time compute）的三大实现路径：N 选 1 采样、多数投票及相关方法、思维链（Chain-of-Thought）自我推理，到融合搜索算法与世界模型的结构化推理系统，还深入探讨了验证器设计、奖励机制、隐空间推理与智能体行为优化等关键挑战。作者 | Davis Treybig 编译 | 岳扬当前大语言模型（LLM）最有趣的研究趋势之一，是推理模型的兴起 ------ 这类模型在给出答案前会花费时间进行思考。这种技术通常被称为「测试时计算」（test-time compute），即在推理阶段进行深度推理。其实在模型推理过程中应用搜索或深度推理的思路早已存在（例如 AlphaZero[1]，以及 Transformer 诞生之前就尝试用类似方法解决旅行商问题的论文[2]），但...

2025-09-05

114

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。