MySQL 优化实战：为何 DELETE + IN 子查询性能不佳，而 JOIN 却能高效利用索引？

问题背景：一次看似简单的数据清理

在日常功能开发过程在中，我们经常需要根据某些条件清理特定数据。某天，我需要在 tbl_doa_activityspecial 表中删除与另一组条件匹配的记录。直觉上，我写下了这样的 SQL：

DELETE FROM tbl_doa_activityspecial 
WHERE ActSetId IN (SELECT DISTINCT ActSetId FROM ...);

　　

这个查询逻辑清晰，但执行时却发现性能极差。使用 EXPLAIN 分析执行计划后，发现了一个令人困惑的现象：MySQL 优化器没有使用 tbl_doa_activityspecial_ActSetId_IDX 这个明显应该使用的索引，而是显示 possible_keys: null。

探寻根源：MySQL 优化器的"保守策略"

经过深入分析，我发现这个问题背后有几个关键原因：

1. 子查询物化导致的性能陷阱

当 MySQL 遇到 IN (子查询) 结构时，它可能会选择将子查询的结果物化（Materialize）到一个临时表中，然后再执行主查询。这个过程包括：

• 执行子查询并将结果写入临时表
• 可能对临时表进行去重（如使用 DISTINCT 时）
• 最后执行基于临时表的查询

物化过程破坏了索引使用的连续性，优化器难以将外部查询的条件与子查询的结果高效关联。

2. DELETE 操作的特殊性

与 SELECT 查询不同，DELETE 操作有以下特点：

• 风险更高：数据删除是不可逆操作
• 锁定要求：需要获取行锁，可能影响并发性能
• 日志记录：需要生成回滚日志用于事务处理

因此，MySQL 优化器在处理 DELETE 语句时会更加"保守"，倾向于选择更可靠而非最高效的执行计划。

3. 统计信息的影响

如果表的统计信息不是最新的，优化器可能错误地估计使用索引与全表扫描的成本，从而做出非最优决策。

解决方案：JOIN 重写的力量

将查询重写为 JOIN 形式后，问题迎刃而解：

DELETE t 
FROM tbl_doa_activityspecial t
JOIN (SELECT DISTINCT ActSetId FROM ...) s ON t.ActSetId = s.ActSetId;

使用 EXPLAIN 分析新查询，确认已经正确使用了 tbl_doa_activityspecial_ActSetId_IDX 索引。

为什么 JOIN 更有效？

1. 明确的连接关系：优化器能够清晰识别两个数据集之间的关联条件
2. 避免不必要的物化：减少了创建临时表的开销
3. 更好的成本估算：优化器可以更准确地评估不同执行计划的成本
4. 直接的索引利用：连接条件直接指向索引字段，使索引使用更加直接

深度解析：MySQL 优化器的工作机制

查询重写优化

MySQL 优化器会对查询进行重写，但不同的原始写法会导致不同的重写结果：

• IN 子查询可能被重写为 EXISTS 或物化形式
• JOIN 语法则提供了更直接的连接语义

成本估算差异

优化器基于成本估算选择执行计划，主要考虑：

• IO 成本：读取数据的开销
• CPU 成本：处理数据的开销
• 内存使用：临时表、排序等的内存需求

对于 IN 子查询，优化器可能高估使用索引的成本或低估全表扫描的成本。

其他解决方案对比

方案一：使用 EXISTS 子查询

DELETE FROM tbl_doa_activityspecial t
WHERE EXISTS (
    SELECT 1 FROM ... s 
    WHERE s.ActSetId = t.ActSetId
);

方案二：强制使用索引

 

DELETE FROM tbl_doa_activityspecial FORCE INDEX (tbl_doa_activityspecial_ActSetId_IDX)
WHERE ActSetId IN (SELECT ActSetId FROM ...);

方案三：使用派生表连接

DELETE t
FROM tbl_doa_activityspecial t
INNER JOIN (
    SELECT DISTINCT ActSetId FROM ...
) s USING (ActSetId);

实践建议与最佳实践

1、始终先使用 SELECT 测试

-- 先检查会影响到多少行
SELECT COUNT(*) FROM tbl_doa_activityspecial 
WHERE ActSetId IN (SELECT ActSetId FROM ...);

2、大批量删除分批次进行，因为大批量的删除可能会导致锁升级　

-- 每次删除1000条记录，避免长事务
DELETE FROM tbl_doa_activityspecial 
WHERE ActSetId IN (...)
LIMIT 1000;

3、在低峰期执行大规模删除操作，因为你很难确定删除期间会发生什么

通过这次优化经历，我得到了几个重要启示：

1. 不要盲目相信直觉：看似逻辑等价的查询，实际性能可能差异巨大
2. EXPLAIN 是关键工具：任何时候都要使用 EXPLAIN 验证执行计划
3. 了解优化器的工作机制：理解优化器的决策过程有助于写出更高效的 SQL
4. JOIN 通常优于子查询：在大多数情况下，JOIN 语法能提供更好的性能

这个案例再次证明了深入了解数据库内部工作机制的重要性。作为开发者，我们不仅要写出功能正确的 SQL，更要关注其性能特征，特别是在涉及到大规模数据时。

记住：最好的查询不是看起来最优雅的，而是执行最高效的。