MySQL 使用 pt-archiver 删除数据

前言

在线核心业务都会有日志表，随着业务持续运行，日志表每天都在增大，最后超过阈值触发空间使用率告警。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='订单操作日志表'
1
2
3
4
5
6
7
8
9
10
11
12
13

为该表制造 1000w 行数据，如何造数可参考我之前的文章。

推荐阅读：MySQL 快速造数

mysql_random_data_load -h127.0.0.1 -u'root' -p'abc123' --max-threads=10 test order_operation_log 10000000
1

1.2 工具安装

本篇文章的主角 pt-archiver 包含在 Percona Toolkit 中，安装方法往期文章有介绍。

推荐阅读：Percona Toolkit 工具集安装

>> pt-archiver --version
pt-archiver 3.3.1
1
2

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
1

重要参数解释：

• –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
1
2

# 设置会话 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

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;
1
2
3
4

输出结果：

+---------+---------+
| min(id) | max(id) |
+---------+---------+
|  111103 |  111112 |
+---------+---------+
1
2
3
4
5

按照范围，执行删除：

-- 执行删除
DELETE FROM `test`.`order_operation_log` WHERE (((`id` >= '111103'))) AND (((`id` <= '111112'))) AND (id < 200000) LIMIT 10
-- 提交事务
commit；
1
2
3
4

得到结果：

Query OK, 10 rows affected (0.01 sec)
1

后记

MySQL 使用 DELETE 删除数据，并不会完成删除，而是打上删除标记，会出现碎片空间。如果要完全释放空间，需要重建表收缩空间碎片。

-- 低峰执行下方 SQL 即可收缩空间碎片，支持 online DDL
alter table table_name force, ALGORITHM=INPLACE, LOCK=NONE	
1
2