背景

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

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

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

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

id（主键）type（无索引）datachange_lasttime（时间，有索引）

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

删除数据的条件：

datachange_lasttime <= 当前时间 - 3 个月type != c

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

早期方案

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

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

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

查一次就根据 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}1.2.3.4.

分页查询图方便我直接用的 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}1.2.3.4.5.

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

startId 从 1 开始，一次查询 limit 条，根据 id 升序查对查询出来的记录做 type != c & datachange_lasttime <= 当前时间 - 3 个月的筛选，从而筛选出需要删除的 id根据筛选出来的 id 进行批量删除更新下一次查询的 startId = 本次查询结果中最大的 id + 1停止条件：如果本次查询结果的第一条记录的 datachange_lasttime > 当前时间 - 3 个月，后面的数据就不需要删除了

上述方案很容易想到一个点，那就是 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 进程被打满。