DELETE语句执行耗时19分钟是否过慢？如何优化执行计划？

耗时是否过慢？ #

19分钟确实属于过慢。针对130k行以内的stage_instance表，以及240k行以内的stage_binary表，正常情况下这类清理操作应该在几秒到数分钟内完成，19分钟的耗时明显超出合理范围，大概率是执行计划未有效利用索引，或者删除逻辑导致了全表扫描、锁表等问题。

优化执行计划的具体方案 #

1. 用LEFT JOIN替代NOT IN改写删除语句 #

NOT IN在部分数据库（如MySQL）的执行计划中，容易触发全表扫描，尤其是当子查询结果集较大时。改用LEFT JOIN能更高效地利用已有的索引：

DELETE si FROM stage_instance si
LEFT JOIN stage_binary sb ON si.binary_id = sb.id
WHERE sb.id IS NULL;

这种写法会让数据库优先利用stage_instance.binary_id的索引和stage_binary.id的主键索引进行关联匹配，快速定位到无对应关系的行。

2. 分批删除避免锁表与日志膨胀 #

一次性删除大量行可能导致长事务锁表，同时事务日志急剧增长，拖慢整体执行速度。可以按批次删除，比如每次删除1000行：

WHILE EXISTS (SELECT 1 FROM stage_instance si LEFT JOIN stage_binary sb ON si.binary_id = sb.id WHERE sb.id IS NULL) DO
    DELETE si FROM stage_instance si
    LEFT JOIN stage_binary sb ON si.binary_id = sb.id
    WHERE sb.id IS NULL LIMIT 1000;
END WHILE;

根据实际数据库性能，可调整LIMIT的数值（如500、2000），平衡单次操作的速度与总次数。

3. 用临时表预筛选待删除行 #

先将需要删除的stage_instance主键ID存入临时表，再基于临时表执行删除，能避免原表长时间被锁，且临时表的索引可以加速匹配：

-- 创建带主键索引的临时表
CREATE TEMPORARY TABLE temp_delete_ids (instance_id INT PRIMARY KEY);

-- 筛选出待删除的实例ID
INSERT INTO temp_delete_ids (instance_id)
SELECT si.id FROM stage_instance si
LEFT JOIN stage_binary sb ON si.binary_id = sb.id
WHERE sb.id IS NULL;

-- 执行删除
DELETE FROM stage_instance WHERE id IN (SELECT instance_id FROM temp_delete_ids);

-- 清理临时表
DROP TEMPORARY TABLE temp_delete_ids;

4. 验证索引有效性 #

虽然binary_id已建索引，仍需确认：

• stage_instance.binary_id与stage_binary.id的数据类型完全一致（比如都是INT），类型不匹配会导致索引失效；
• 用EXPLAIN分析原语句的执行计划，如果显示type: ALL（全表扫描），说明索引未被利用，此时必须改写语句。

5. 临时关闭二进制日志（生产环境谨慎使用） #

若不需要记录此次删除操作的二进制日志，可临时关闭以减少日志写入开销：

SET sql_log_bin = 0;
-- 执行删除语句
SET sql_log_bin = 1;

注意：生产环境使用前需确认不会影响数据备份与同步逻辑。

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