|
你是否遇到过如此令人麻烦的问题,比如:由于前期规划不当,后期库表数据量猛增;或者由于“年久失修”而造成的表数据积累不断。等等诸如此类 大表 问题。
面对这些大表,是删也不能删,清理又困难。
即使你不嫌麻烦,一点一点的通过 delete 清理了数据,但是还会有令人头疼的 表碎片 问题。
面对 表ibd 文件的只增不减,也许想到了可以通过这些方法解决:
ALTER TABLE xxx engine=innodb;
OPTIMIZE TABLE xxx;
这两种方法虽然在清理表数据后可以释放空间,但是会造成锁表问题。
清理数据方法有很多,比如 [delete] [truncate] [删除分区] 等,其实最简单有效的方法就是 删除分区 。
有人会问:当初数据库中没有设计为分区表,谁也没想到这个表现在数据量这么大,这该怎么办?
再也不用抱怨你接手了一个烂摊子,也不用频繁接收表文件过大造成的文件系统告警了......
这将优雅的实现:
实现我们的大目标,那就需要借助专业的小工具。他就是
在线修改表结构,特点是修改过程中不会造成读写阻塞。
工作原理是创建要更改的表的空副本,根据需要对其进行修改,然后将原始表中的行复制到新表中。复制完成后,它会移走原始表并用新表替换。默认情况下,它还会删除原始表。
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
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;
表中存在数据 10000000
greatsql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.03 sec)
t1.ibd 文件大小有 468M
-rw-r----- 1 mysql mysql 468M May 5 06:07 t1.ibd
如果使用分区表,分区键需要包含在主键中,使用 pt-online-schema-change 可以 online 修改主键。
首先,使用 <strong>--dry-run</strong>
进行执行前测试
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.
然后,使用 <strong>--execute</strong>
执行
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 调整为分区表。
首先,使用 <strong>--dry-run</strong>
进行执行前测试
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.
然后,使用 <strong>--execute</strong>
执行
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 文件也不存在了,文件系统空间得到了释放。
合作电话:010-64087828
社区邮箱:greatsql@greatdb.com