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

在日常功能开发过程在中，我们经常需要根据某些条件清理特定数据。某天，我需要在 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);

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

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