MySQL数据库批量删除行的查询优化方案咨询

替换NOT IN为JOIN提升性能 #

NOT IN在子查询返回大量数据时，MySQL的执行效率会显著下降，甚至可能出现隐式的全表扫描。改用LEFT JOIN来匹配需删除的数据，能大幅优化执行逻辑：

DELETE s
FROM student_score s
LEFT JOIN temp t ON s.id = t.MaxID
WHERE s.lesson_id IS NOT NULL
  AND t.MaxID IS NULL
ORDER BY s.id
LIMIT 1000;

给关键字段添加索引 #

索引能直接减少数据库扫描的数据量，建议添加以下索引：

• 给student_score表创建联合索引：CREATE INDEX idx_lesson_id_id ON student_score(lesson_id, id);
  这个索引可以快速过滤出lesson_id IS NOT NULL的行，同时利用id快速关联临时表。
• 给temp表的MaxID字段创建索引：CREATE INDEX idx_temp_maxid ON temp(MaxID);
  让JOIN操作时能快速定位匹配的ID，避免全表扫描临时表。

去掉非必要的ORDER BY #

如果业务上不强制要求按id顺序删除，直接去掉ORDER BY id子句，能省去排序的额外开销，进一步提升删除速度：

DELETE s
FROM student_score s
LEFT JOIN temp t ON s.id = t.MaxID
WHERE s.lesson_id IS NOT NULL
  AND t.MaxID IS NULL
LIMIT 1000;

调整批量删除的批次大小 #

LIMIT 1000不是固定最优值，可以根据服务器资源情况调整：

• 若服务器IO、CPU充足，可尝试调大批次（比如2000、5000），减少循环执行的次数；
• 若资源紧张，调小批次避免锁表时间过长影响其他业务。

极端场景：重建表替代逐批删除 #

如果待删除数据占比超过60%（比如50万行里有30万+要删），逐批删除的效率会很低，不如直接重建表：

1. 创建临时表存储需要保留的数据：

CREATE TABLE student_score_temp AS
SELECT * FROM student_score
WHERE lesson_id IS NOT NULL
  AND id IN (SELECT MaxID FROM temp);

2. 替换原表：

RENAME TABLE student_score TO student_score_old;
RENAME TABLE student_score_temp TO student_score;

3. 给新表重建原有的索引、约束，最后按需删除旧表。

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