DELETE嵌套查询未走索引时，InnoDB全表扫描并逐行加next-key锁，锁行数接近全表且覆盖所有间隙，逻辑等效锁表；根本原因是优化器退化为聚簇索引顺序遍历，而非真正升级表锁。
只要嵌套查询的 WHERE 条件没走索引，或者子查询触发了全表扫描，DELETE 就会锁住整张表——这不是配置问题，是 InnoDB 的锁升级机制在起作用。
为什么嵌套 DELETE 容易升级成表锁
MySQL 不允许在 DELETE 的子查询中直接引用目标表别名（如 DELETE FROM t WHERE id IN (SELECT id FROM t ...)），所以常见绕法是套一层派生表或用 EXISTS。但无论哪种写法，只要子查询没命中索引，就会全表扫描；InnoDB 边扫边加行锁 + 间隙锁，锁数量一过阈值，自动升级为表锁。
-
EXPLAIN SELECT *子查询部分，如果type是ALL或index，说明它在扫全表 - 别写
WHERE DATE(created_at) = '2024-01-01'——函数导致索引失效，扫全表 - 别传字符串给 INT 字段，比如
user_id = '123'，隐式转换跳过索引 - 子查询里用
GROUP BY或ORDER BY ... LIMIT却没索引支撑，也会强制物化+全表扫描
怎么让嵌套 DELETE 只锁该锁的行
核心是：子查询必须能走索引，并且结果集要小、可预测。不能依赖“看起来只删几行”就放松索引检查。
- 优先用
EXISTS替代IN：例如删“无订单用户”，写DELETE FROM users WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)，确保orders.user_id有索引 - 子查询里所有
WHERE和JOIN字段都得有索引，联合索引要覆盖最左前缀，比如ON a.id = b.a_id WHERE b.status = 'done'，就在b表建(a_id, status) - MySQL 8.0+ 可尝试
WITH下沉条件：把过滤提前到 CTE 内部，避免外层物化全量中间结果 - 绝对不要在子查询里写
SELECT *或未限定的ORDER BY——字段解析和排序都会扩大锁范围
最容易被忽略的隐形锁源
你确认子查询走了索引、也分批了，还是卡——大概率是其他会话在“偷偷锁着”。这类锁不报错，但会让所有优化失效。
- 监控脚本每秒跑
SELECT COUNT(*) FROM t，拿的是共享锁，而你的DELETE在等排他锁 - 长事务没提交，
SELECT * FROM information_schema.INNODB_TRX里trx_started时间很早的，就是元凶 - 应用层开了连接池但没正确 close，空闲连接仍持有 MDL 锁（
Waiting for table metadata lock） -
SHOW ENGINE INNODB STATUS\G里的LATEST DETECTED DEADLOCK段落，会明确写出哪个事务持有什么锁、在等什么锁
真正难处理的不是语法怎么写，而是你怎么知道子查询实际扫描了多少行、加了多少锁——EXPLAIN ANALYZE（PostgreSQL）或 EXPLAIN FORMAT=JSON（MySQL 8.0+）里 rows_examined 和 lock_type 才是关键证据，不是执行计划里那句 “Using index” 就万事大吉。
