简单的网站更新 关键词优化 关键词互联,如何做网,机械设备做公司网站,网站 wap 插件文章目录 前言1. 环境准备1.1 模拟造数1.2 工具安装 2. 删除数据2.1 批次删除表2.2 原理解析2.3 批处理思路 后记 前言
在线核心业务都会有日志表#xff0c;随着业务持续运行#xff0c;日志表每天都在增大#xff0c;最后超过阈值触发空间使用率告警。DBA 处理空间告警时… 文章目录 前言1. 环境准备1.1 模拟造数1.2 工具安装 2. 删除数据2.1 批次删除表2.2 原理解析2.3 批处理思路 后记 前言
在线核心业务都会有日志表随着业务持续运行日志表每天都在增大最后超过阈值触发空间使用率告警。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)
) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENT订单操作日志表为该表制造 1000w 行数据如何造数可参考我之前的文章。 推荐阅读MySQL 快速造数 mysql_random_data_load -h127.0.0.1 -uroot -pabc123 --max-threads10 test order_operation_log 100000001.2 工具安装
本篇文章的主角 pt-archiver 包含在 Percona Toolkit 中安装方法往期文章有介绍。 推荐阅读Percona Toolkit 工具集安装 pt-archiver --version
pt-archiver 3.3.12. 删除数据
使用 pt-archiver 分批删除表中数据生产环境推荐一次删除 2w 行避免造成较大的主从延迟。
2.1 批次删除表
该命令表示删除 test 库下 order_operation_log 表全部数据每次删除 1000 行。
pt-archiver --source h127.0.0.1,P3306,uroot,pabc123,Dtest,torder_operation_log --where id 200000 --bulk-delete --limit 20000 --charset utf8 --progress 100000 --purge --commit-each重要参数解释
–source表示源实例信息后面 h、P、u、p、D、t 分别表示主机地址、端口、用户、密码、数据库名、表名。–where过滤条件删除全表 11删除部分数据可按需指定。–bulk-delete指批量删除。–limit每次批量的处理的行数。–commit-each对于每批数据只提交一次。–charset连接数据库使用的字符集。–progress进度打印删除多少行打印一次进度。–purge表示只删除数据。–sleep处理一批数据后等待几秒后再继续执行。
2.2 原理解析
开启 general_log 日志可以更直观的看到 pt-archiver 执行过程。
# 测试使用limit 指定为 10
pt-archiver --source h127.0.0.1,P3306,uroot,pabc123,Dtest,torder_operation_log --where id 200000 --bulk-delete --limit 10 --charset utf8 --progress 100000 --purge --commit-each# 设置会话 autocommi 0 每个操作需要用户 commit 提交事务
set autocommit0# 进入目标表查看表结构这里会选择主键或者唯一键作为分批处理的过滤条件。
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# 提交事务
commit2.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, ALGORITHMINPLACE, LOCKNONE
