文章目录
前言
在线核心业务都会有日志表，随着业务持续运行，日志表每天都在增大，最后超过阈值触发空间使用率告警。DBA 处理空间告警时，会先导出一份表大小信息，然后发给研发确认，哪些表是可以清理些数据的，让研发先清理。如果没有清理空间就需要提审批扩容。
如果有数据可以清理，又分为 删库、清空表、删除表中部分数据 三种情况。前两种可以直接使用 MySQL 命令处理，第三种通常需要研发写一个任务批量删除，切忌不能直接 DELETE FROM xxxx 一张大表或者一次删几千万的数据，这样会造成集群出现很大的延迟，而且会产生一个巨大的 Binlog 文件，以及更多的锁争用情况。
本篇文章将为介绍如何使用 pt-archiver 分批清理表中的数据，以及写批次任务的思路。
1. 环境准备
1.1 模拟造数
接下来，模拟删除一张日志表的场景，以下是表结构。
CREATETABLE`order_operation_log`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'ID',`order_id`bigint(20)DEFAULTNULLCOMMENT'订单号',`order_num`varchar(16)DEFAULTNULLCOMMENT'商品订单号',`operation_before`varchar(500)COMMENT'操作前',`operation_after`varchar(500)COMMENT'操作后',`operator_id`bigint(20)NOTNULLDEFAULT'0'COMMENT'操作人id',`operator_name`varchar(30)NOTNULLDEFAULT''COMMENT'操作人姓名',`operation_remark`varchar(255)DEFAULTNULLCOMMENT'操作备注',`created_at`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',`updated_at`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8 COMMENT='订单操作日志表'
为该表制造 1000w 行数据，如何造数可参考我之前的文章。
推荐阅读：MySQL 快速造数
mysql_random_data_load -h127.0.0.1 -u'root' -p'abc123' --max-threads=10test order_operation_log 10000000
1.2 工具安装
本篇文章的主角 pt-archiver 包含在 Percona Toolkit 中，安装方法往期文章有介绍。
推荐阅读：Percona Toolkit 工具集安装
>> pt-archiver --version
pt-archiver 3.3.1
2. 删除数据
使用 pt-archiver 分批删除表中数据，生产环境推荐一次删除 2w 行，避免造成较大的主从延迟。
2.1 批次删除表
该命令表示删除 test 库下 order_operation_log 表全部数据，每次删除 1000 行。
pt-archiver --sourceh=127.0.0.1,P=3306,u=root,p=abc123,D=test,t=order_operation_log --where"id < 200000" --bulk-delete --limit20000--charset utf8 --progress100000--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 --sourceh=127.0.0.1,P=3306,u=root,p=abc123,D=test,t=order_operation_log --where"id < 200000" --bulk-delete --limit10--charset utf8 --progress100000--purge --commit-each
# 设置会话 autocommi = 0 每个操作需要用户 commit 提交事务set autocommit=0# 进入目标表，查看表结构，这里会选择主键或者唯一键，作为分批处理的过滤条件。USE`test`SHOWCREATETABLE`test`.`order_operation_log`# 确认 id 字段为过滤条件，获取 id 最大值SELECTMAX(`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`FORCEINDEX(`PRIMARY`)WHERE(id <200000)AND(`id`<'4940000')ORDERBY`id`LIMIT10# 基于查询查到的主键最大值和最小值，进行删除DELETEFROM`test`.`order_operation_log`WHERE(((`id`>='109853')))AND(((`id`<='109862')))AND(id <200000)LIMIT10# 提交事务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`FORCEINDEX(`PRIMARY`)WHERE(id <200000)AND(`id`<'4940000')AND((`id`>='109862'))ORDERBY`id`LIMIT10# 执行删除DELETEFROM`test`.`order_operation_log`WHERE(((`id`>='109863')))AND(((`id`<='109872')))AND(id <200000)LIMIT10# 提交事务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`FORCEINDEX(`PRIMARY`)WHERE(id <200000)AND(`id`<'4940000')ORDERBY`id`LIMIT10)as tmp;
输出结果：
+---------+---------+|min(id)|max(id)|+---------+---------+|111103|111112|+---------+---------+
按照范围，执行删除：
-- 执行删除DELETEFROM`test`.`order_operation_log`WHERE(((`id`>='111103')))AND(((`id`<='111112')))AND(id <200000)LIMIT10-- 提交事务commit；
得到结果：
Query OK,10 rows affected (0.01 sec)
后记
MySQL 使用 DELETE 删除数据，并不会完成删除，而是打上删除标记，会出现碎片空间。如果要完全释放空间，需要重建表收缩空间碎片。
-- 低峰执行下方 SQL 即可收缩空间碎片，支持 online DDLaltertable table_name force,ALGORITHM=INPLACE,LOCK=NONE
版权归原作者 Bing@DBA 所有， 如有侵权，请联系我们删除。