MySQL 5.7及之前IN子查询删除会触发物化临时表和嵌套循环,导致性能崩塌;应改用带别名的JOIN写法、避免NOT IN、大列表删除需建临时表,且修改后必须验证EXPLAIN。

IN子查询删除触发嵌套循环 + 物化临时表
MySQL 5.7 及之前版本对 DELETE ... WHERE id IN (SELECT ...) 这类语句默认走物化(materialization)策略:先完整执行子查询,把全部结果写入内部临时表;再对外层每行主键做一次“是否存在匹配”的逐行扫描。10 万行 × 每次查临时表 = 实际执行 10 万次查找,且临时表一旦超 tmp_table_size(默认 16MB)就落磁盘,Created_tmp_disk_tables 指标飙升。
EXPLAIN DELETE 看不到执行计划,但等价 SELECT 会暴露 type=DEPENDENT SUBQUERY 或 Extra=Using temporary —— 这就是性能崩塌的信号。
用JOIN重写DELETE必须加别名且显式指定驱动表
直接把 IN 换成 JOIN 不行,MySQL 的 DELETE 语法不支持无别名的多表删除写法,也不自动决定哪张表是驱动表。必须写成:
DELETE u FROM users u INNER JOIN orders o ON u.id = o.user_id AND o.status = 'paid';
关键点:
- 必须给被删表起别名(如
u),并在DELETE后明确写出DELETE u - 关联条件(如
o.status = 'paid')要写在ON子句里,不是WHERE,否则无法触发索引下推 - 如果原逻辑是“删没订单的用户”,得用
LEFT JOIN ... WHERE o.user_id IS NULL,不能错用INNER JOIN
大列表删除时IN后面跟5000个ID反而比子查询还慢
当应用层传了 5000 个 ID 拼成 WHERE id IN (1,2,3,...,5000),优化器会因 eq_range_index_dive_limit(默认 200)阈值放弃精确索引统计,转而用粗略估算,常导致误判为“全表扫描更便宜”,key 显示为空、type=ALL。
更糟的是隐式类型转换:比如 id 是 BIGINT,却传字符串 '1','2',触发全字段转换,索引彻底失效。
此时正确做法是:
- 建临时表:
CREATE TEMPORARY TABLE tmp_del_ids (id BIGINT PRIMARY KEY) - 批量插入:
INSERT INTO tmp_del_ids VALUES (1),(2),...,(5000)(别单条插) - 用
JOIN删除:DELETE u FROM users u JOIN tmp_del_ids t ON u.id = t.id
NOT IN删除极易误删,NULL会让整行消失
DELETE FROM users WHERE id NOT IN (SELECT user_id FROM orders) 是高危操作:只要子查询返回任意一个 NULL,整个 NOT IN 表达式恒为 UNKNOWN,结果集为空 —— 你以为删的是“没下单用户”,实际一行都不删,或者删错范围。
安全替代方案只有两个:
- 用
NOT EXISTS:DELETE u FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id),它对NULL不敏感 - 显式排除
NULL:DELETE FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL)
改完必须立刻跑等价 SELECT + EXPLAIN,确认 key 列非空、没出现 DEPENDENT SUBQUERY,否则上线等于事故。


















