背景
简单抽象下业务场景，有一张 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}
查一次就根据 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 开始，一次查询 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 进程被打满。
最后欢迎加入苏三的星球，你将获得：商城系统实战、秒杀系统实战、代码生成工具、系统设计、性能优化、技术选型、高频面试题、底层原理、Spring源码解读、工作经验分享、痛点问题等多个优质专栏。
还有1V1答疑、修改简历、职业规划、送书活动、技术交流。
目前星球已经更新了4400+篇优质内容，还在持续爆肝中..星球已经被官方推荐了3次，收到了小伙伴们的一致好评。戳我加入学习，已有1400+小伙伴加入学习。
我的技术专栏《程序员最常见的100个问题》，目前已经更新了80篇干货文章，里面收录了很多踩坑经历，对你的职业生涯或许有些帮助，最近收到的好评挺多的。
这个专栏总结了我10年工作中，遇到过的100个非常有代表性的技术问题，非常有参考和学习价值。
Java、Spring、分布式、高并发、数据库、海量数据、线上问题什么都有。
每篇文章从发现问题、分析问题、解决问题和问题总结等多个维度，深入浅出，分享了很多技术细节，定位和排查问题思路，解决问题技巧，以及实际工作经验。
你能从中学到很多有用知识，帮你少走很多弯路。
扫描下方二维码即可订阅：
原价199，现价只需23，即将涨价。
