MySQL 中长事务可能会导致哪些问题？

回答重点

1）长时间的锁竞争，阻塞资源：

• 长事务持有锁的时间较长，容易导致其他事务在尝试获取相同锁时发生阻塞，从而增加系统的等待时间和降低并发性能。
• 业务线程也会因为长时间的数据库请求等待而阻塞，部分业务的阻塞可能还会影响到别的服务，导致产生雪崩，最终使得服务全面崩盘，导致非常严重的线上事故。

2）死锁风险：

• 长事务更容易产生死锁，因为多个事务可能在互相等待对方释放锁，导致系统无法继续执行。

3）主从延迟：

• 主库需要长时间执行，然后传输给从库，从库又要重放好久，期间可能有很长一段时间数据是不同步的。

4）回滚导致时间浪费：

• 如果长事务执行很长一段时间，中间突发状况导致抛错，使得事务回滚了，之前做的执行都浪费了。

扩展知识

长事务的 SQL 如何处理？

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

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

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

当时由于数据量太大，这就会产生上面所说的长事务问题。如何解决呢？

答案就是拆！怎么拆呢？想必很多小伙伴会觉得很简单，按日期拆不就完事了？

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 一下，执行的非常快，1 分钟内就搞定了。

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