技术分享 | MySQL 安全 delete 巨大量数据行

作者：王向

爱可生 DBA 团队成员，负责公司 DMP 产品的运维和客户 MySQL 问题的处理。擅长数据库故障处理。对数据库技术和 python 有着浓厚的兴趣。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

前言

随着业务量的增长，存储在 MySQL 中的数据日益剧增，如果业务量不讲武德，搞偷袭，趁我没反应过来把很多表，很快，都打到了千万级别，亿级别。如果大意，没有闪，这就导致跟其 Join 的表的 SQL 变得很慢，对应用接口的 response time 也变长了，影响了用户体验。

一般常见增长量巨大的表都是一些记录、日志类型数据，只需要保留 2 到 3 月。此时需要对表做数据清理实现瘦身。那么这么大的数据如何进行删除，而不影响数据库的正常使用呢？

如何进行删除？都有哪些方案？

根据前辈多年的删表经验来说( • ̀ω•́ )✧，删除大量数据时一定要分批缓慢删除，否则很容易阻塞整个表，还有可能因为产生的 binlog 过大让从库原地 GG。

delete * from where create_time <= ? limit ?;

确定删除方案后，我们就可以使用 pt-archiver 进行删除，对没错这个家伙不只可以用个归档，删除数据也是行家。

下面这介绍两种方案，比较有局限性，但对业务可以停的的场景有用：

1.mysqldump 备份出来需要的数据，然后 drop table，导入

2.mysqldump 备份出来需要的数据，然后 truncate table，导入

明显都会造成表一段时间的不可用。同时还会引起 IO 飙升的风险

如果这张大表仍然还有被高频的访问，你敢直接 drop table&truncate 那基本上就是茅坑里点灯，找死！具体有哪些风险，等下篇文章进行解读！o(╥﹏╥)o

使用 pt-archiver 进行分批缓慢删除

参数介绍

主要介绍删除历史数据能用到的，

pt-archiver --help 
--progress 每多少行打印进度信息
--limit  限制select返回的行数
--sleep  指定select语句休眠时间
--txn-size 指定多少行提交一次事务
--bulk-delete 用单个DELETE语句批量删除每个行块。该语句删除块的第一行和最后一行之间的每一行，隐含--commit-each
--dry-run 打印查询，不做任何操作后退出

删除数据

把大象装进冰箱一共分三步：

1.打印查询

2.打开会话保持功能 screen（防止窗口意外断开造成程序中断；笔者曾经因为忘记打开会话保持在机器面前守了半天；因为 10 分钟没操作堡垒机会断线(ಥ﹏ಥ)）

3.执行删除

# 打印查询
$ pt-archiver --source h=10.186.65.19,P=3306,u=root,p='123',D=sbtest,t=sbtest1 --purge --charset=utf8mb4 --where "id <= 400000" --progress=200  --limit=200 --sleep=1 --txn-size=200  --statistics  --dry-run
# 解释：删除sbtest库，sbtest1表数据，字符集为utf8mb4，删除条件是 id <= 400000，每次取出200行进行处理，每处理200行则进行一次提交，每完成一次处理sleep 1s

SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE (id <= 400000) AND (`id` < '23132073') ORDER BY `id` LIMIT 200
SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE (id <= 400000) AND (`id` < '23132073') AND ((`id` >= ?)) ORDER BY `id` LIMIT 200
DELETE FROM `sbtest`.`sbtest1` WHERE (`id` = ?)

# 打开会话保持功能
screen -S archiver


# 执行删除
$ pt-archiver --source h=10.186.65.19,P=3306,u=root,p='123',D=sbtest,t=sbtest1 --purge --charset=utf8mb4 --where "id <= 400000" --progress=200  --limit=200 --sleep=1 --txn-size=200  --statistics

......
2021-02-16T17:52:24    2115  398200
2021-02-16T17:52:25    2116  398400
2021-02-16T17:52:26    2117  398600
2021-02-16T17:52:27    2118  398800
2021-02-16T17:52:28    2119  399000
2021-02-16T17:52:29    2120  399200
2021-02-16T17:52:30    2121  399400
2021-02-16T17:52:31    2123  399600
2021-02-16T17:52:32    2124  399800
2021-02-16T17:52:33    2125  400000
2021-02-16T17:52:33    2125  400000
Started at 2021-02-16T17:17:08, ended at 2021-02-16T17:52:34
Source: A=utf8mb4,D=sbtest,P=3306,h=10.186.65.19,p=...,t=sbtest1,u=root
SELECT 400000
INSERT 0
DELETE 400000
Action        Count       Time        Pct
sleep          2000  2003.1843      94.22
deleting     400000    88.6074       4.17
select         2001     2.9120       0.14
commit         2001     1.4004       0.07
other             0    30.0424       1.41

在删除数据后的处理：

MySQL 的机制下 delete 后磁盘不会立即释放，在业务空闲时间进行分析表以便真正从磁盘上移除数据解除空间占用（极端情况可能需要重启释放），非必做（一般可不做）；视场景而定。这里不做过多讲解。

如需要可以研究一下 optimize table：https://dev.mysql.com/doc/ref...