总阅读量:次

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

摘要: 原创出处 juejin.cn/post/6931890118538199048 「xlecho」欢迎转载，保留摘要，谢谢！

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

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

血一般的教训，请慎用insert into select。同事应用之后，导致公司损失了近10w元，最终被公司开除。

1 事情的起因

公司的交易量比较大，使用的数据库是mysql，每天的增量差不多在百万左右，公司并没有分库分表，所以想维持这个表的性能只能考虑做数据迁移。

同事李某接到了这个任务，于是他想出了这两个方案

  • 先通过程序查询出来，然后插入历史表，再删除原表
  • 使用insert into select让数据库IO来完成所有操作

第一个方案使用的时候发现一次性全部加载，系统直接就OOM了，但是分批次做就过多io和时间长，于是选用了第二种方案，测试的时候没有任何问题，开开心心上线，然后被开除。

2 到底发生了啥，我们复盘一下

先来看第一个方案，先看伪代码

// 1、查询对应需要迁移的数据
List<Object> list = selectData();

// 2、将数据插入历史表
insertData(list);

// 3、删除原表数据
deleteByIds(ids);

我们可以从这段代码中看到，OOM的原因很简单，我们直接将数据全部加载内存，内存不爆才怪。

再来看看第二个方案，到底发生了啥

为了维持表的性能，同时保留有效数据，经过商量定了一个量，保留10天的数据，差不多要在表里面保留1kw的数据。所以同事就做了一个时间筛选的操作，直接insert into select ... dateTime < (Ten days ago)，爽极了，直接就避免了要去分页查询数据，这样就不存在OOM啦。还简化了很多的代码操作，减少了网络问题。

为了测试，还特意建了1kw的数据来模拟，测试环境当然是没有问题啦，顺利通过。考虑到这个表是一个支付流水表，于是将这个任务做成定时任务，并且定在晚上8点执行。

晚上量也不是很大，自然是没有什么问题，但是第二天公司财务上班，开始对账，发现资金对不上，很多流水都没有入库。最终排查发现晚上8点之后，陆陆续续开始出现支付流水插入失败的问题，很多数据因此丢失。

最终定位到了是迁移任务引起的问题，刚开始还不明所以，白天没有问题，然后想到晚上出现这样的情况可能是晚上的任务出现了影响，最后停掉该任务的第二次上线，发现没有了这样的情况。

3 复盘

问题在哪里？

为什么停掉迁移的任务之后就好了呢？这个insert into select操作到底做了什么？我们来看看这个语句的explain。

我们不难从图中看出，这个查询语句直接走了全表扫描。这个时候，我们不难猜想到一点点问题。如果全表扫描，我们这个表这么大，是不是意味着迁移的时间会很长？假若我们这个迁移时间为一个小时，那是不是意味着就解释了我们白天没有出现这样问题的原因了。但是全表扫描是最根本的原因吗？

我们不妨试试，一边迁移，一边做些的操作，还原现场。最终还是会出现这样的问题。这个时候，我们可以调整一下，大胆假设，如果不全表扫描，是不是就不会出现这样的问题。当我们将条件修改之后，果然发现没有走了全表扫描了。最终再次还原现场，问题解决了

得出结论：全表扫描导致了这次事故的发生。

这样做就解决了发生的问题，但是做为陆陆续续开始失败这个就不好解释了。

原因

在默认的事务隔离级别下：insert into a select b的操作a表示直接锁表，b表是逐条加锁。这也就解释了为什么出现陆续的失败的原因。在逐条加锁的时候，流水表由于多数是复合记录，所以最终部分在扫描的时候被锁定，部分拿不到锁，最终导致超时或者直接失败，还有一些在这加锁的过成功成功了。

为什么测试没有问题？

在测试的时候充分的使用了正式环境的数据来测试，但是别忽视一个问题，那就是测试环境毕竟是测试环境，在测试的时候，数据量真实并不代表就是真实的业务场景。比方说，这个情况里面就少了一个迁移的时候，大量数据的插入这样的情况。最终导致线上bug

4 解决办法

既然我们避免全表扫描就可以解决，我们避免它就行了。想要避免全表扫描，对where后面的条件做索引，让我们的select查询都走索引即可

5 insert into还能用吗？

可以

6 总结

使用insert into select的时候请慎重，一定要做好索引

文章目录
  1. 1. 1 事情的起因
  2. 2. 2 到底发生了啥，我们复盘一下
    1. 2.0.0.1. 先来看第一个方案，先看伪代码
    2. 2.0.0.2. 再来看看第二个方案，到底发生了啥
  • 3. 3 复盘
    1. 3.0.0.1. 问题在哪里？
    2. 3.0.0.2. 原因
    3. 3.0.0.3. 为什么测试没有问题？
  • 4. 4 解决办法
  • 5. 5 insert into还能用吗？
  • 6. 6 总结