子查询可写在where、from、select三处：where后需单值或配合in/exists；from后为派生表，必须加别名；select中为标量子查询，每行执行一次，易引发n+1问题。
子查询写在哪？WHERE、FROM、SELECT 三处用法区别
WHERE 后的子查询最常见，比如查“工资高于平均值的员工”：SELECT name FROM emp WHERE salary > (SELECT AVG(salary) FROM emp)。它必须返回单值（或用 IN/EXISTS 接多行），否则直接报错 Subquery returns more than 1 row。
FROM 后的子查询（派生表）必须带别名，否则 mysql 会报 Every derived table must have its own alias：SELECT t.avg_sal FROM (SELECT AVG(salary) AS avg_sal FROM emp) AS t。注意：这个子查询在执行时会被物化（MySQL 5.7+ 可能优化为内联），但无法被外部条件下推，容易拖慢大表关联。
SELECT 列表里的子查询叫标量子查询，每行都执行一次，性能敏感：SELECT id, (SELECT count(*) FROM order WHERE user_id = u.id) AS order_cnt FROM user u。如果 user.id 没索引，或子查询没走索引，就会触发 N+1 查询问题。
IN vs EXISTS：查存在性时别硬套 IN
IN 在右操作数含 NULL 时结果恒为 UNKNOWN，整行被过滤掉——这不是 bug，是 SQL 三值逻辑决定的。例如：SELECT * FROM t1 WHERE id IN (SELECT id FROM t2)，只要 t2.id 里有一个 NULL，整个 IN 表达式失效。
EXISTS 不受 NULL 影响，语义更清晰：“只要子查询能返回一行，就为真”。且大多数引擎对 EXISTS 能做半连接（semi-join）优化，遇到第一条匹配就停止扫描；而 IN 可能先执行完子查询再哈希比对，内存占用更高。
- 如果子查询结果集小（NULL，
IN可读性略好 - 如果子查询涉及大表、或外层有
WHERE条件需下推，优先用EXISTS - 别写
NOT IN (subquery)：只要子查询返回任意NULL，整个结果为空——改用NOT EXISTS
相关子查询为什么慢？怎么识别和拆解
相关子查询指子查询里引用了外层表字段，比如：SELECT name, (SELECT MAX(score) FROM exam e WHERE e.student_id = s.id) FROM student s。它的执行逻辑是：对 s 的每一行，都重新执行一遍子查询。如果 student 有 10 万行，exam 有 50 万行，最坏就是 10 万 × 50 万次扫描。
判断是否相关子查询，只看子查询里有没有出现外层表的列名（如 s.id）。优化方向很明确：
- 给子查询的关联字段加索引：
exam(student_id, score)（联合索引，score放后面支持MAX()） - 改成
LEFT JOIN + GROUP BY：先聚合再关联，把 N 次查询变成 1 次扫描 - 确认是否真需要实时计算：有时用物化视图或冗余字段更稳
MySQL 8.0+ 的 WITH 子句不是语法糖，是优化突破口
WITH 定义的公用表表达式（CTE）在 MySQL 8.0+ 中默认不物化，也就是每次被引用都会重算——这点和 postgresql 不同。所以写成：WITH tmp AS (SELECT user_id, COUNT(<em>) c FROM log GROUP BY user_id) SELECT </em> FROM tmp WHERE c > 100 union SELECT * FROM tmp WHERE c ，<code>tmp 会被执行两次。
但加上 MATERIALIZED 提示（MySQL 8.0.23+）就能强制物化：WITH tmp AS (SELECT /<em>+ MATERIALIZE </em>/ user_id, COUNT(*) c FROM log GROUP BY user_id)。此时 tmp 只算一次，后续引用走临时表，对复杂子查询或多次引用场景提升明显。
- 不加提示时，CTE 和嵌套子查询性能基本一致，别以为用了
WITH就自动优化 - 递归 CTE（
WITH RECURSIVE）必须物化，无需手动加提示 - 如果只是想提升可读性，CTE 没问题；如果目标是减少重复计算，得盯紧执行计划里的
materialize标记
相关子查询的关联字段有没有索引，CTE 到底算几次，IN 里混进 NULL 会不会静默丢数据——这些地方不看执行计划、不测真实数据量，光靠语法对了没用。