技術(shù)干貨 | 如何將大表在線改造為分區(qū)表并釋放空間

出現(xiàn)麻煩

你是否遇到過如此令人麻煩的問題，比如：由于前期規(guī)劃不當(dāng)，后期庫表數(shù)據(jù)量猛增；或者由于“年久失修”而造成的表數(shù)據(jù)積累不斷。等等諸如此類大表問題。

面對這些大表，是刪也不能刪，清理又困難。

即使你不嫌麻煩，一點(diǎn)一點(diǎn)的通過 delete 清理了數(shù)據(jù)，但是還會(huì)有令人頭疼的 表碎片 問題。

面對表ibd 文件的只增不減，也許想到了可以通過這些方法解決：

ALTER TABLE xxx engine=innodb;
OPTIMIZE TABLE xxx;

這兩種方法雖然在清理表數(shù)據(jù)后可以釋放空間，但是會(huì)造成鎖表問題。

如何優(yōu)雅在線的清理數(shù)據(jù)并釋放空間呢？

清理數(shù)據(jù)方法有很多，比如 [delete] [truncate] [刪除分區(qū)] 等，其實(shí)最簡單有效的方法就是 刪除分區(qū) 。

有人會(huì)問：當(dāng)初數(shù)據(jù)庫中沒有設(shè)計(jì)為分區(qū)表，誰也沒想到這個(gè)表現(xiàn)在數(shù)據(jù)量這么大，這該怎么辦？

再也不用抱怨你接手了一個(gè)爛攤子，也不用頻繁接收表文件過大造成的文件系統(tǒng)告警了......

我們"在線"把大表改造成分區(qū)表不就好了！

這將優(yōu)雅的實(shí)現(xiàn)：

在線 ——> 不影響業(yè)務(wù)
分區(qū) ——> 便于管理
空間 ——> 刪除分區(qū)直接釋放

閃亮登場

實(shí)現(xiàn)我們的大目標(biāo)，那就需要借助專業(yè)的小工具。他就是

percona-toolkit 之 pt-online-schema-change

概要

在線修改表結(jié)構(gòu)，特點(diǎn)是修改過程中不會(huì)造成讀寫阻塞。

原理

工作原理是創(chuàng)建要更改的表的空副本，根據(jù)需要對其進(jìn)行修改，然后將原始表中的行復(fù)制到新表中。復(fù)制完成后，它會(huì)移走原始表并用新表替換。默認(rèn)情況下，它還會(huì)刪除原始表。

用法

pt-online-schema-change [OPTIONS] DSN

選項(xiàng)

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

最佳實(shí)踐

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. 測試數(shù)據(jù)

表中存在數(shù)據(jù) 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. 修改主鍵

如果使用分區(qū)表，分區(qū)鍵需要包含在主鍵中，使用 pt-online-schema-change 可以 online 修改主鍵。

首先，使用 --dry-run 進(jìn)行執(zhí)行前測試

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

執(zhí)行結(jié)果：

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 執(zhí)行

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

執(zhí)行結(jié)果：

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

查看表結(jié)構(gòu)

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)

此時(shí)可以看到，表 t1 的主鍵已經(jīng)修改為 (id,expired_date) 了。

5. 調(diào)整為分區(qū)表

使用 pt-online-schema-change 可以 online 調(diào)整為分區(qū)表。

首先，使用 --dry-run 進(jìn)行執(zhí)行前測試

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

執(zhí)行結(jié)果：

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 執(zhí)行

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

執(zhí)行結(jié)果：

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

查看表結(jié)構(gòu)

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)

此時(shí)可以看到，表 t1 已經(jīng)調(diào)整為分區(qū)表了。

6. 測試驗(yàn)證

查詢表分區(qū)及數(shù)據(jù)情況

-- 查詢數(shù)據(jù)
greatsql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.39 sec)

-- 查詢分區(qū)
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 文件已經(jīng)不存在了，每個(gè) ibd 文件即為分區(qū)文件。

刪除數(shù)據(jù)，釋放空間
如果需要?jiǎng)h除一些數(shù)據(jù)，直接刪除分區(qū)即可

-- 刪除 202501 分區(qū)數(shù)據(jù)
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)

數(shù)據(jù)刪除完成，看看文件系統(tǒng)所占空間是否釋放？

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

可以看到，分區(qū) p202501 所使用的 ibd 文件也不存在了，文件系統(tǒng)空間得到了釋放。

Enjoy GreatSQL ??

關(guān)于 GreatSQL

GreatSQL是適用于金融級應(yīng)用的國內(nèi)自主開源數(shù)據(jù)庫，具備高性能、高可靠、高易用性、高安全等多個(gè)核心特性，可以作為MySQL或Percona Server的可選替換，用于線上生產(chǎn)環(huán)境，且完全免費(fèi)并兼容MySQL或Percona Server。

相關(guān)鏈接： GreatSQL社區(qū) Gitee GitHub Bilibili

GreatSQL社區(qū)：

社區(qū)博客有獎(jiǎng)?wù)鞲逶斍椋?a target="_blank" rel="noopener nofollow">https://greatsql.cn/thread-100-1-1.html

技術(shù)交流群：

微信：掃碼添加GreatSQL社區(qū)助手微信好友，發(fā)送驗(yàn)證信息加群。

posted @ 2025-09-05 09:54 GreatSQL 閱讀(47) 評論(0) 收藏舉報(bào)

刷新頁面返回頂部

GreatSQL

技術(shù)干貨 | 如何將大表在線改造為分區(qū)表并釋放空間

技術(shù)干貨 | 如何將大表在線改造為分區(qū)表并釋放空間

出現(xiàn)麻煩

如何優(yōu)雅在線的清理數(shù)據(jù)并釋放空間呢？

我們"在線"把大表改造成分區(qū)表不就好了！

閃亮登場

percona-toolkit 之 pt-online-schema-change

概要

原理

用法

選項(xiàng)

最佳實(shí)踐

1. 測試表

2. 測試數(shù)據(jù)

3. 查看表文件大小

4. 修改主鍵

5. 調(diào)整為分區(qū)表

6. 測試驗(yàn)證

關(guān)于 GreatSQL

GreatSQL社區(qū)：

技術(shù)交流群：

公告