子查询慢的主因是优化器未物化而反复执行，需通过EXPLAIN确认type是否为DEPENDENT SUBQUERY，再用MATERIALIZE提示、改写为JOIN或临时表等手段优化。
为什么子查询慢得离谱？先看执行计划里藏的真相
绝大多数 SQL 子查询性能差，不是语法写错了，而是优化器根本没按你想的方式执行。比如 WHERE id IN (SELECT user_id FROM logs WHERE status = 'error')，MySQL 5.7 之前会把子查询当成“依赖子查询”反复执行，外层每扫一行，内层就重跑一遍——10 万行主表？就是 10 万次全表扫描 logs。
真正要做的，是让数据库意识到：这个子查询结果集可以一次性算完、建临时索引、再做哈希连接。而触发这个行为的关键，是让子查询“独立”且“可物化”。
- 用
EXPLAIN看type列：如果出现ALL或DEPENDENT SUBQUERY，基本就是性能雷区 - 检查子查询是否含外部引用（如
WHERE t1.id = t2.ref_id），有则大概率无法物化 - 确认 MySQL 版本：8.0+ 默认启用
subquery_materialization_cost_based=ON，但老版本需手动加/*+ MATERIALIZE */提示（仅限支持 optimizer hints 的引擎）
IN 改成 EXISTS 后反而更慢？注意关联字段 NULL 和索引覆盖
EXISTS 不是银弹。当子查询里 SELECT * 且没走索引时，EXISTS 可能比 IN 更慢——因为它必须对每一行都做一次“是否存在”的判断，而 IN 在物化后可能走哈希查找。
关键在两点：关联字段不能为 NULL，且子查询的 WHERE 条件字段必须有索引。
-
IN遇到NULL值直接返回空结果（SQL 标准），但EXISTS不受NULL影响——如果业务逻辑依赖NULL过滤，强行替换会出错 - 子查询中
WHERE条件字段没索引？EXISTS就退化成嵌套循环 + 全表扫描，比原IN还糟 - 推荐写法：
EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id AND o.status = 'paid')——SELECT 1明确告诉优化器不用取数据，只判存在性
JOIN 比子查询快，但什么时候不该硬转？
把 WHERE id IN (SELECT ...) 改成 INNER JOIN 确实常能提速，前提是：子查询结果集不重复、且你不需要保留主表的 NULL 匹配语义。
一旦子查询返回多条匹配记录，JOIN 会放大主表行数，后续 COUNT、SUM 都可能翻倍，调试起来极隐蔽。
- 用
DISTINCT或GROUP BY去重？代价可能比原子查询还高——先聚合再连接，不如让优化器自己决定物化时机 - 需要“主表所有行 + 是否存在子记录”语义？用
LEFT JOIN ... ON ... IS NOT NULL替代EXISTS，但务必给ON字段加索引，否则NULL判断会拖垮性能 - 子查询带
LIMIT或ORDER BY？别转JOIN—— 这些子句在JOIN中无意义，且强制排序会阻断物化
MySQL 8.0+ 的 WITH 语句真能救命？小心 CTE 的物化陷阱
WITH 看似优雅，但默认是“非物化 CTE”，也就是每次被引用都重新执行。写成 WITH tmp AS (SELECT user_id FROM logs WHERE created_at > '2024-01-01') SELECT * FROM users WHERE id IN (SELECT user_id FROM tmp)，和原始子查询没本质区别。
必须显式加 MATERIALIZED 提示（MySQL 8.0.23+）才能强制物化：
WITH tmp AS /*+ MATERIALIZE */ (SELECT DISTINCT user_id FROM logs WHERE status = 'error') SELECT u.* FROM users u INNER JOIN tmp t ON u.id = t.user_id;
但要注意：物化 CTE 会把结果存进临时表，如果数据量大，可能触发 tmp_table_size 限制，降级成磁盘临时表，IO 开销陡增。
真正省事的做法，反而是先把中间结果写入带索引的临时表：CREATE TEMPORARY TABLE tmp_users AS SELECT DISTINCT user_id FROM logs WHERE ...; CREATE INDEX idx_uid ON tmp_users(user_id); —— 控制权完全在你手上，也方便 EXPLAIN 分析。
复杂点在于：物化时机、索引选择、NULL 处理，这些细节不调 EXPLAIN 看执行计划，光靠改写语法根本没法验证效果。
