前言
在线核心业务都会有日志表,随着业务持续运行,日志表每天都在增大,最后超过阈值触发空间使用率告警。DBA 处理空间告警时,会先导出一份表大小信息,然后发给研发确认,哪些表是可以清理些数据的,让研发先清理。如果没有清理空间就需要提审批扩容。
如果有数据可以清理,又分为 删库、清空表、删除表中部分数据 三种情况。前两种可以直接使用 MySQL 命令处理,第三种通常需要研发写一个任务批量删除,切忌不能直接 DELETE FROM xxxx 一张大表或者一次删几千万的数据,这样会造成集群出现很大的延迟,而且会产生一个巨大的 Binlog 文件,以及更多的锁争用情况。
本篇文章将为介绍如何使用 pt-archiver 分批清理表中的数据,以及写批次任务的思路。
1. 环境准备
1.1 模拟造数
接下来,模拟删除一张日志表的场景,以下是表结构。
CREATE TABLE `order_operation_log` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`order_id` bigint(20) DEFAULT NULL COMMENT '订单号',
`order_num` varchar(16) DEFAULT NULL COMMENT '商品订单号',
`operation_before` varchar(500) COMMENT '操作前',
`operation_after` varchar(500) COMMENT '操作后',
`operator_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '操作人id',
`operator_name` varchar(30) NOT NULL DEFAULT '' COMMENT '操作人姓名',
`operation_remark` varchar(255) DEFAULT NULL COMMENT '操作备注',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单操作日志表'
为该表制造 1000w 行数据,如何造数可参考我之前的文章。
推荐阅读:MySQL 快速造数
mysql_random_data_load -h127.0.0.1 -u'root' -p'abc123' --max-threads=10 test order_operation_log 10000000
1.2 工具安装
本篇文章的主角 pt-archiver 包含在 Percona Toolkit 中,安装方法往期文章有介绍。
>> pt-archiver --version
pt-archiver 3.3.1
2. 删除数据
使用 pt-archiver 分批删除表中数据,生产环境推荐一次删除 2w 行,避免造成较大的主从延迟。
2.1 批次删除表
该命令表示删除 test 库下 order_operation_log 表全部数据,每次删除 1000 行。
pt-archiver --source h=127.0.0.1,P=3306,u=root,p=abc123,D=test,t=order_operation_log --where "id < 200000" --bulk-delete --limit 20000 --charset utf8 --progress 100000 --purge --commit-each
重要参数解释:
- –source:表示源实例信息,后面 h、P、u、p、D、t 分别表示主机地址、端口、用户、密码、数据库名、表名。
- –where:过滤条件,删除全表 1=1,删除部分数据可按需指定。
- –bulk-delete:指批量删除。
- –limit:每次批量的处理的行数。
- –commit-each:对于每批数据,只提交一次。
- –charset:连接数据库使用的字符集。
- –progress:进度打印,删除多少行打印一次进度。
- –purge:表示只删除数据。
- –sleep:处理一批数据后,等待几秒后再继续执行。
2.2 原理解析
开启 general_log 日志,可以更直观的看到 pt-archiver 执行过程。
# 测试使用,limit 指定为 10
pt-archiver --source h=127.0.0.1,P=3306,u=root,p=abc123,D=test,t=order_operation_log --where "id < 200000" --bulk-delete --limit 10 --charset utf8 --progress 100000 --purge --commit-each
# 设置会话 autocommi = 0 每个操作需要用户 commit 提交事务
set autocommit=0
# 进入目标表,查看表结构,这里会选择主键或者唯一键,作为分批处理的过滤条件。
USE `test`
SHOW CREATE TABLE `test`.`order_operation_log`
# 确认 id 字段为过滤条件,获取 id 最大值
SELECT MAX(`id`) FROM `test`.`order_operation_log`
# 该步骤的目的是,获取 id 主键删除范围的最大值和最小值
SELECT /*!40001 SQL_NO_CACHE */ `id`,`order_id`,`order_num`,`operation_before`,`operation_after`,`operator_id`,`operator_name`,`operation_remark`,`created_at`,`updated_at` FROM `test`.`order_operation_log` FORCE INDEX(`PRIMARY`) WHERE (id < 200000) AND (`id` < '4940000') ORDER BY `id` LIMIT 10
# 基于查询查到的主键最大值和最小值,进行删除
DELETE FROM `test`.`order_operation_log` WHERE (((`id` >= '109853'))) AND (((`id` <= '109862'))) AND (id < 200000) LIMIT 10
# 提交事务
commit
# 执行第二次,获取主键的最大值和最小值
SELECT /*!40001 SQL_NO_CACHE */ `id`,`order_id`,`order_num`,`operation_before`,`operation_after`,`operator_id`,`operator_name`,`operation_remark`,`created_at`,`updated_at` FROM `test`.`order_operation_log` FORCE INDEX(`PRIMARY`) WHERE (id < 200000) AND (`id` < '4940000') AND ((`id` >= '109862')) ORDER BY `id` LIMIT 10
# 执行删除
DELETE FROM `test`.`order_operation_log` WHERE (((`id` >= '109863'))) AND (((`id` <= '109872'))) AND (id < 200000) LIMIT 10
# 提交事务
commit
2.3 批处理思路
在业务代码中,如果有类似需求,也可以借鉴 pt-archiver 的实现方式。不过在获取最大值最小值时,可不必返回所有数据。
以下是模拟的过程,由于用于测试,我们使用的是 limit 10,一般生产可一批删除 2w 行。
-- 开启事务
begin;
-- 获取范围
SELECT /*!40001 SQL_NO_CACHE */ min(id), max(id) from (SELECT `id` FROM `test`.`order_operation_log` FORCE INDEX(`PRIMARY`) WHERE (id < 200000) AND (`id` < '4940000') ORDER BY `id` LIMIT 10) as tmp;
输出结果:
+---------+---------+
| min(id) | max(id) |
+---------+---------+
| 111103 | 111112 |
+---------+---------+
按照范围,执行删除:
-- 执行删除
DELETE FROM `test`.`order_operation_log` WHERE (((`id` >= '111103'))) AND (((`id` <= '111112'))) AND (id < 200000) LIMIT 10
-- 提交事务
commit;
得到结果:
Query OK, 10 rows affected (0.01 sec)
后记
MySQL 使用 DELETE 删除数据,并不会完成删除,而是打上删除标记,会出现碎片空间。如果要完全释放空间,需要重建表收缩空间碎片。
-- 低峰执行下方 SQL 即可收缩空间碎片,支持 online DDL
alter table table_name force, ALGORITHM=INPLACE, LOCK=NONE