根本原因是嵌套查询触发物化+全表扫描+长事务三重叠加：mysql 5.7默认物化派生表，若内层无索引或条件失效，则全表扫描并加临键锁；相关子查询重复执行加剧锁膨胀；锁持续至外层语句结束，导致远超实际需要的行或间隙被锁定。
嵌套查询在大规模导出时容易锁表，根本原因不是语法本身，而是物化行为 + 全表扫描 + 长事务三者叠加，让数据库不得不锁住远超实际需要的行或间隙。
为什么SELECT FROM (SELECT ...)会锁基表
MySQL 5.7+ 对派生表（即 SELECT * FROM (SELECT ...)）默认执行物化：把内层结果写入临时表。这个过程若内层没索引、没条件或条件失效，就会触发全表扫描——InnoDB 会对所有扫描到的行加临键锁（next-key lock），哪怕外层只取前10行。
- 典型错误写法：
SELECT * FROM (SELECT id, name FROM orders WHERE status = 'pending') t LIMIT 10，若status无索引，内层扫描全表并锁全部 pending 行 - 即使加了
ORDER BY id LIMIT 10，优化器也可能放弃索引排序，改走物化+文件排序，锁住所有参与排序的行 - 物化临时表期间，基表的行锁不会释放，直到整个外层语句结束——导出耗时越长，锁持有时间越久
IN/EXISTS 嵌套在导出场景中的隐式锁膨胀
导出常配合条件筛选，比如“导出每个用户最新订单”，写成 SELECT * FROM orders o1 WHERE o1.id = (SELECT id FROM orders o2 WHERE o2.user_id = o1.user_id ORDER BY created_at DESC LIMIT 1)。这种相关子查询在 MySQL 5.7 下无法下推 LIMIT，导致每行 o1 都重新全表扫描 o2。
- 10 万用户 → 子查询执行 10 万次 → 每次都可能锁住大量
orders行（尤其user_id无索引时） - 锁不是“一次性加完”，而是分散在多次扫描中，且持续到外层语句结束，极易与其他 DML 冲突
- PostgreSQL 虽对
EXISTS有 Anti Join 优化，但若递归 CTE 或子查询含未终止条件，仍会反复锁定同一行
怎么避免嵌套导出锁表：优先绕过物化
核心思路是不让数据库有机会物化中间结果，也不让它瞎扫。最稳妥的是把“逻辑嵌套”拆成应用层可控的两步。
- 先窄查主键：
SELECT id FROM orders WHERE status = 'pending' ORDER BY id LIMIT 10000（确保status和id有联合索引） - 再用这批
id批量拉详情：SELECT * FROM orders WHERE id IN (1,2,3,...)，注意控制IN列表长度（MySQL 默认max_allowed_packet限制实际可用约 65535 参数） - 如果必须用单条 SQL，MySQL 8.0+ 可尝试
WITH+ 条件下沉：WITH latest AS (SELECT user_id, MAX(id) AS max_id FROM orders GROUP BY user_id) SELECT o.* FROM orders o JOIN latest l ON o.id = l.max_id，前提是GROUP BY能走索引
导出时 ORDER BY + LIMIT 放在哪一层才安全
很多人想“导出最新1000条”，直接写 SELECT * FROM (SELECT * FROM logs ORDER BY created_at DESC) t LIMIT 1000，这等于告诉数据库：“先把整张表倒序排好，再取前1000”。只要 created_at 没索引，或索引选择性差，就必然全表扫描+全表锁。
- 安全做法：确保内层
ORDER BY字段有高效索引，且外层不破坏下推——MySQL 8.0+ 对含LIMIT的子查询会自动禁用物化，但前提是外层没FOR UPDATE或其他阻止下推的操作 - 更稳方案：用游标分页导出，比如
SELECT * FROM logs WHERE created_at ，每次用上一批最后的 <code>created_at值做下限 - 绝对不要在嵌套里写
ORDER BY ... LIMIT后还接JOIN或聚合，那基本等于主动申请锁全表
真正危险的从来不是“嵌套”这个词，而是你没看清执行计划里那个刺眼的 type: ALL，以及 rows 值比你要导的数据量大几个数量级——那说明锁已经悬在头顶了。
