阅读全文

⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 yes的练级攻略 「是Yes呀」欢迎转载，保留摘要，谢谢！

• 长事务
• 拆 SQL
• 有时候要转换思路
• 最后

---

🙂🙂🙂关注**微信公众号：【芋道源码】**有福利：

1. RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表
2. RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址
3. 您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。
4. 新的源码解析文章实时收到通知。每周更新一篇左右。
5. 认真的源码交流微信群。

---

今天来讲讲关于大表删除的问题。

比如，你现在需要删除一张一共有 5 亿数据的表里面的 2021 年数据，假设这张表叫 yes。

我相信你脑子在 1s 内肯定会蹦出这条 SQL :

delete from yes where create_date > "2020-12-31" and create_date < "2022-01-01";

如果直接执行这条 SQL 会发生什么问题呢？

长事务

我们需要关注到一个前提：这张表有 5 亿的数据，所以它是一张超大表，因此这个 where 条件可能涉及非常多的数据，所以我们可以从离线数仓或者备库查下数据量，然后我们发现这条 SQL 会删除 3 亿左右的数据。

那么一次性 delete 完的方案是不行的，因为这会涉及到长事务的问题。

长事务涉及到加锁，只会在事务执行完毕后才会释放锁，由于长事务锁了很多数据，如果期间有频繁的 DML 想要操作这些数据，那么就会造成阻塞。

连接都阻塞住了，业务线程自然就阻塞了，也就是说你的服务线程都在等待数据库的响应，然后可能还会影响到别的服务，可能产生雪崩，于是就 GG 了。

长事务可能会造成主从延迟，你想想主库执行了好久，才执行完给从库，从库又要重放好久，期间可能有很长一段时间数据是不同步的。

还有一种情况，业务都有个特殊停机窗口，你觉得你可以为所欲为，然后开始执行长事务了，然后执行了 5 小时之后，不知道啥情况抛错了，事务回滚了，于是浪费了 5 个小时，还得重新开始。

综上，我们需要避免长事务的发生。

那面对可能发生长事务的 SQL 我们怎么拆呢？

拆 SQL

我们就以上面这条 SQL 为例：

delete from yes where create_date > "2020-12-31" and create_date < "2022-01-01";

看到这条 SQL，如果要拆分，想必很多小伙伴会觉得很简单，按日期拆不就完事了？

delete from yes where create_date > "2020-12-31" and create_date < "2021-02-01";
delete from yes where create_date >= "2021-02-01" and create_date < "2021-03-01";

......

这当然可以，恭喜你，你已经拆分成功了，没错就这么简单。

但是，如果 create_date 没有索引怎么办？

没索引的话，上面这就全表扫描了啊？

影响不大，没有索引我们就给他创造索引条件，这个条件就是主键。

我们直接一个 select min(id)... 和 select max(id).... 得到这张表的主键最小值和最大值，假设答案是 233333333 和 666666666。

然后我们就可以开始操作了：

delete from yes where (id >= 233333333 and id < 233433333) and create_date > "2020-12-31" and create_date < "2022-01-01";
delete from yes where (id >= 233433333 and id <233533333) and create_date > "2020-12-31" and create_date < "2022-01-01";

......

delete from yes where (id >= 666566666 and id <=666666666) and create_date > "2020-12-31" and create_date < "2022-01-01";

当然你也可以再精确些，通过日期筛选来得到 maxId，这影响不大（不满足条件的 SQL 执行很快，不会耗费很多时间）。

这样一来 SQL 就满足了分批的操作，且用得上索引。

如果哪条语句执行出错，只会回滚小部分数据，我们重新排查下就好了，影响不大。

而且拆分 SQL 之后还可以并行提高执行效率。

当然，并行可能有锁竞争的情况，导致个别语句等待超时。不过影响不大，只要机器状态好，执行得快，因为锁竞争导致的等待并不一定会超时，如果个别 SQL 超时的话，重新执行就好了。

有时候要转换思路

关于大表删除有时候要转换思路，把删除转成插入。

假设还是有一张 5 亿的数据表，此时你需要删除里面 4.8 亿的数据，那这时候就不要想着删除了，要想着插入。

道理很简单，删除 4.8 亿的数据，不如把要的 2000W 插入到新表中，我们后面业务直接用新表就好了。

这两个数据量对比，时间效率差异不言而喻了吧？

具体操作也简单：

1. 创建一张新表，名为 yes_temp；
2. 将 yes 表的 2000W 数据 select into 到 yes_temp 中；
3. 将 yes 表 rename 成 yes_233；
4. 将 yes_temp 表 rename 成 yes。

狸猫换太子，大功告成啦！

之前有个记录表我们就是这样操作的，就 select into 近一个月的数据到新表中，以前老数据就不管了，然后 rename 一下，执行得非常快。

本来预估 2 小时的 SQL 操作，1 分钟就搞定了。

这种类似的操作是有工具的，比如 pt-online-schema-change 等，不过我没用过，有兴趣的小伙伴可以自己去看看，道理是一样的，多了几个触发器，这里不多赘述了。

最后

咱们开发还是得多学一些数据库的操作和原理，因为好多数据库的操作都需要亲力亲为，小公司没 DBA 的话就不说了，大公司的话咱也不知道 DBA 到底会关心到哪个程度，还是得靠自己靠谱。