慢 SQL 打爆监控！亿级数据表的删除问题-51CTO.COM

背景

简单抽象下业务场景，有一张 MySQL 表用来存储用户的操作日志，需要依赖这个日志来做一些业务逻辑的判断，并且每个用户可以存在多条日志，所以可想而知，随着时间的推移，这张表肯定是会越来越大的，必须要做治理。

秉持着最简原则，我们暂时不考虑分库分表，数据能删则删，因为表中数据其实并不是每一条都有用，梳理了下业务之后，我们最终的治理方向就是：

Job 每个月定时执行一次来删除数据
保留近 3 个月的数据，之前的数据可以删除
删除并不是无脑删除，每条日志有一个对应的类型 type（取值固定，假设是 a、b、c、d、e 吧），当 type = c 的时候该条日志不能删除（忽略这个奇怪的逻辑，纯属业务需求）

我们可以抽象出这样一张表就命名为 log 吧，它有如下字段：

type 没有索引并且也不适合做索引。

删除数据的条件：

以上就是背景，应该比较清楚了

首先大表删除的基本方针一定是批量删除，即分批查，分批删。

最基本的方案就是把 datachange_lasttime 和 type 的要求都放在 SQL 中，直接通过 SQL 找到我们要删的数据：

select id from log
where 
    datachange_lasttime <= '2023-06-17 00:00:00' 
    and type != 'c'
limit #{limit}

查一次就根据 id 批量删除一次，每次查 limit 条，停止条件就是查不出来数据了

早期方案在数据量级几千万的时候还是没问题的，因为我们这个删除只需要离线运行，所以用定时 job 跑就可以，对业务基本没啥影响。

但随着表越来越大，上亿之后，这条 SQL 直接卡住，慢查询告警猛增，已经没有办法正常完成删除了。

type 由于没有索引放在 SQL 中是巨大瓶颈，必须得去掉！datachange_lasttime 也可以从 SQL 中拿出来，查出来之后在内存中再做 type 和 datachange_lasttime 的筛选（也就是在 Java 代码中写这个逻辑），然后再根据 id 批量删除。

查询 SQL 如下：

select id from log
from t_user_pop_log
order by id
limit #{offset}, #{limit}

分页查询图方便我直接用的 MyBatis PageHelper，但是很快我就为此付出代价，就是总是有脏数据没删干净，我们举个例子分析下：

假设表中总数据 300 条
第一次查询：select * from log limit 0,100; 查出了 100 条数据，但是经过我们 type != c 的过滤后，最终只删除了 50 条数据，那么表中还剩余 250 条数据
第二次查询，表中有 250 条数据，select * from log limit 101,200; 查出了 100 条数据，但是经过我们 type != c 的过滤后，最终只删除了 60 条数据，那么表中还剩余 190 条数据
第三次查询，表中有 190 条数据，select * from log limit 201,300; 这次查询就出问题了，因为表中只有 190 条数据了，offset = 201 是查不出来数据的，所以这就导致总有一部分数据是没有经过处理的

想到的解决方案是一直查第一页（也就是 offset = 0），直到第一页没数据，那就停止查询

但是很明显这个停止查询的条件存在问题，如果恰好这一页的所有数据全都是 “type=c”，也就是这一页的数据都是不能删的数据，那么循环就会卡在这一页，因为这一页的数据永远不会发生变化

我们看失败方案，其实可以发现失败的最根本原因是 MyBatis Pagehelper 的 offset 的计算不对，考虑我们自己做分页，不用 MyBatis Pagehelper，这样就改成如下方式来分批查询：

select *
from t_user_pop_log
where id >= #{startId}
order by id
limit #{limit}

这条 SQL 中只涉及主键 id，速度是非常快的：

上述方案很容易想到一个点，那就是 startId 可以不需要每次都从 1 开始。

每个月删除一次，那其实除了第一次，后续的删除只需要删除一个月的数据，只有第一次删除是需要扫描三个月前的所有数据。举个例子：

5.1 执行第一次删除，保留近三个月即 2.1 之后的数据，2.1 之前的数据要全部扫描并删除
6.1 执行第二次删除，保留近三个月即 3.1 之后的数据，2.1 之前的数据已经被删除了，所以这次删除其实只需要删除从 2.1 开始到 3.1 这一个月的数据就可以了

那么 startId 的初始取值逻辑就是：

首次删除：startId = 1
非首次删除：startId = datachange_lastime >=【当前时间 - 3 个月 - 1 个月】的最小 id（还可以给这个时间加一点容错空间，多扫描几天的数据也无妨，比如 15 天，startId = datachange_lastime >=【当前时间 - 3 个月 - 1 个月 - 15 天】的最小 id）

以上，在首次删除的时候，扫描的数据量非常大，可以考虑加一点 sleep，防止 DB 进程被打满。