相关子查询、IN子查询、标量子查询易触发重复全表扫描，根本原因是数据库执行器被迫为每行外层数据重执行内层扫描；解决路径是优先用JOIN、EXISTS或窗口函数替代，并确保关联字段有合适索引。
相关子查询、IN子查询、标量子查询这三类写法最容易触发重复全表扫描，根本原因不是“SQL写错了”，而是数据库执行器被迫为每行外层数据重新执行一次内层扫描。解决路径很明确：能用JOIN就别用子查询；不能用JOIN时，优先用EXISTS或窗口函数替代。
相关子查询为什么必然导致全表扫描
当子查询里出现外层表的字段（比如WHERE u.department = u2.department），数据库无法提前算出结果，只能对每一行外层记录都重跑一遍子查询。哪怕外层只返回10行，内层也可能被扫10次。
- MySQL 5.7及更早版本几乎不优化这类查询，
EXPLAIN中会看到大量DEPENDENT SUBQUERY - 即使内层表有索引，若关联字段没走索引（如
u2.department无索引），每次都是全表扫描 - PostgreSQL虽支持
LATERAL，但执行计划仍可能选嵌套循环+顺序扫描，尤其当统计信息不准时
IN (SELECT ...)在什么情况下会退化成全表扫描
不是所有IN都慢，但以下情况基本等于主动放弃索引：
- 子查询含
GROUP BY、DISTINCT或聚合函数——MySQL 5.7+半连接优化直接失效 - 子查询结果集太大（比如上万行），优化器认为走索引还不如全扫
-
IN右侧字段没索引，或外层WHERE条件没驱动到索引列（如user_id IN (SELECT id FROM users WHERE region = 'CN')，region索引无效） -
EXPLAIN显示type: ALL或Extra: Using where; Using join buffer
改用EXISTS通常更稳：EXISTS (SELECT 1 FROM users u2 WHERE u2.id = orders.user_id AND u2.region = 'CN')，优化器更容易利用users(id)索引做反向查找。
多个标量子查询怎么避免反复扫同一张表
像(SELECT COUNT(*) FROM orders WHERE user_id = u.id)这种写法，用户表每多一行，订单表就被扫一次。10万用户=10万次扫描，I/O爆炸。
- 首选窗口函数：
COUNT(o.id) OVER(PARTITION BY u.id)+LEFT JOIN，整张订单表只扫一遍 - 必须用
GROUP BY时，确保orders(user_id)有索引，且聚合结果通过JOIN关联回主表 - 如果聚合条件复杂（如只统计
status = 'paid'的订单），先用CTE预过滤：WITH paid_orders AS (SELECT user_id, COUNT(*) AS cnt FROM orders WHERE status = 'paid' GROUP BY user_id)，再JOIN - 注意：窗口函数依赖内存缓冲，
PARTITION BY字段基数太高（如毫秒级时间戳）可能溢出到磁盘，比标量子查询还慢
索引设计如何配合子查询避免全表扫描
光有索引不够，得让索引“被用上”。关键看子查询里哪些字段参与了等值/范围过滤和关联。
- 对
IN或EXISTS中的子表，至少要建单列索引在关联字段上（如users(id)） - 如果子查询带额外过滤（如
WHERE region = 'CN'），建联合索引：INDEX(region, id)，满足最左前缀 - 避免在子查询
WHERE里对索引列用函数：YEAR(created_at) = 2023→ 改成created_at BETWEEN '2023-01-01' AND '2023-12-31' - 覆盖索引能减少回表，但子查询作为派生表时（
FROM (SELECT ...)），MySQL 8.0+默认物化为InnoDB临时表，无索引——所以务必只选必要字段，别SELECT *
真正容易被忽略的是：子查询是否真的需要实时计算。很多场景下，把中间结果缓存到临时表或物化视图里，比硬扛优化器更可靠。
