MySQL DELETE子查询被标记为DEPENDENT SUBQUERY的原因分析

这是个挺有意思的问题——明明是完全相同的过滤逻辑，换个语句类型就触发了完全不同的执行计划，我来帮你拆解背后的原因：

核心差异：DEPENDENT SUBQUERY vs SUBQUERY #

先明确两个执行计划类型的本质区别：

• SUBQUERY：子查询仅执行一次，结果会被缓存下来供外部查询复用，这也是你用SELECT时速度飞快的核心原因。
• DEPENDENT SUBQUERY：子查询会跟随外部查询的每一行重复执行一次——相当于每检查一行schema_a.table_a的数据，就跑一遍子查询，自然会慢到离谱。

为什么DELETE会触发DEPENDENT SUBQUERY？ #

数据库优化器对**读操作（SELECT）和写操作（DELETE）**的优化策略完全不同，主要有这几个关键因素：

1. 优化优先级的差异 #

SELECT的核心目标是快速返回结果，优化器会尽可能选择最高效的执行路径——比如自动把你的NOT IN子查询转换成独立执行的计划：先算出schema_b.table_b的所有id，再用这个列表去过滤table_a的数据。

但DELETE是写操作，优化器的优先级立刻变成了数据一致性、锁安全、事务兼容性，它会倾向于选择更保守的执行路径。比如像MySQL这类数据库的优化器会认为，逐行执行子查询能避免因为子查询结果在执行过程中变化（比如并发修改schema_b.table_b）导致的删除逻辑不一致，哪怕你的场景里根本没有这种关联关系。

2. 子查询转换的支持差异 #

很多数据库的优化器对SELECT语句的子查询转换支持更完善，能轻松把NOT IN子查询转换成半连接或者临时表查询。但对于DELETE语句，优化器可能无法完成这种转换——因为写操作涉及到行锁、触发器、外键约束等额外逻辑，优化器不敢贸然把子查询转为独立执行，怕引发意外的数据问题。

3. 锁机制的影响 #

DELETE需要对要删除的行加排他锁，如果先执行子查询得到所有要删除的行，可能需要一次性锁定大量数据，这会大幅增加锁冲突的概率。优化器可能会选择逐行检查的方式，减少锁的持有时间和范围，代价就是执行速度变慢。

怎么让DELETE也快速执行？ #

既然知道了原因，我们可以通过重写语句来绕过这个优化器的限制：

方法1：用LEFT JOIN替代NOT IN #

把NOT IN逻辑转换成左连接，这样优化器能更容易生成高效的执行计划：

DELETE t1
FROM schema_a.table_a t1
LEFT JOIN schema_b.table_b t2 
  ON t1.column_b = t2.id
WHERE t1.column_a = 'VALUE_A' 
  AND t2.id IS NULL;

方法2：先把要删除的ID存入临时表 #

先通过SELECT把符合条件的id查出来存到临时表，再基于临时表执行DELETE：

-- 创建临时表存储要删除的ID
CREATE TEMPORARY TABLE temp_delete_ids AS
SELECT id 
FROM schema_a.table_a 
WHERE column_a = 'VALUE_A' 
  AND column_b NOT IN (SELECT id FROM schema_b.table_b);

-- 执行删除
DELETE FROM schema_a.table_a 
WHERE id IN (SELECT id FROM temp_delete_ids);

这两种方式都能让子查询只执行一次，避免逐行触发的低效问题。

内容的提问来源于stack exchange，提问作者Saket Mehta