MySQL:5.7中delete长期处于preparing状态简析

实际这里是是一个delete in语句。

大概就是这样一个语句，处于preparing 状态，问delete为什么会处于这个状态？

对于preparing状态一般归结为优化阶段，并还没有进入实际的语句执行阶段。但是这里有所不同。并且我们知道一般delete执行慢一般状态会处于，

但是这里是preparing状态。那么为什么会处于这种状态呢？简单分析一下。

我们知道5.7的delete in实际上还是用的DEPENDENT SUBQUERY。这个类似exists的 *** 作，都会用外层查询驱动内层。稍微跟了一下5.7的delete in（不做详细研究），发现所有的query block并没有完全完成优化，实际上这种子查询会在调用Item_subselect::exec的时候通过

进行判断，如果unit->is_optimized()判断为false，则会调入unit->optimize(thd)，对subquery的query block进行优化，而优化结束后查询的状态会被设置为preparing。也就出现了这种现象，如下：

这种现象当前看起来是5.7（5.7以下没有测试）中DML语句带in/not in常见的，也可以归结为状态转换稍微考虑欠妥，可能开发大佬也没意识到这个状态已经变为DBA诊断问题的一个主要手段。

而到了8.0状态已经更加精细，有了一些细微的变化，而且8.0.21后支持DML 中的in使用semi join(not in还不行，但是状态是executing）优化。所以我们简单的将其归结为正在执行类似sending data状态即可。需要优化的是将这种delete改写为联合delete形式。

十万级别查询量 志强e5 cpu 单核100% 超3分钟才能跑完。

优化后10秒内可以跑完。

思路 通过临时表创建索引用 空间换时间避免频繁读取原表信息

mysql删除原则

not exist 比not in执行效率高 （线上项目保持正确性，没有尝试网上有人推荐使用 not exist 由于改动大没有尝试）

truncate 比 delete执行效率高

---