必须改写嵌套子查询，因其在千万级数据上会反复执行（外层扫10万行则子查询执行10万次），仅加索引无效；in(select...)在含相关条件时退化为dependent subquery，易触发临时表膨胀甚至oom，应改用join或exists并确保索引精准匹配。
嵌套子查询在千万级数据上慢，不是因为逻辑复杂，而是数据库真在反复执行它——外层扫10万行，子查询就跑10万次。必须改写，不能只靠加索引。
为什么 IN (SELECT ...) 在大表里会崩
MySQL 和 PostgreSQL 对 IN (SELECT) 的处理策略不同，但共同点是：一旦子查询里引用了外层字段（比如 WHERE o.user_id = u.id），就成了“相关子查询”，优化器无法物化，只能逐行触发执行。
- 用
EXPLAIN看执行计划，如果出现DEPENDENT SUBQUERY或Correlated Nested Loop，就是正在重复执行 -
IN子查询返回超 10 万行时，MySQL 5.7/8.0 容易触发内存临时表膨胀，直接 OOM - 即使
user_id有索引，若驱动表顺序不对（比如内表被全扫），索引也白建
用 JOIN 替代 IN/EXISTS 的实操要点
把子查询“拉平”成显式连接，是最快见效的改法，但要注意语义等价和去重问题。
- 原写法：
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active') - 改成：
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'active' - 如果
orders和users是一对多关系，JOIN可能导致重复行，此时该用EXISTS或加DISTINCT -
EXISTS更适合存在性判断，且对NULL安全，但要确保关联条件写全：WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 'active')
索引必须配得准，否则 JOIN 也白搭
JOIN 能提速的前提，是连接字段和过滤条件都命中索引。光建单列索引往往不够。
- 对上面例子，
users表需要联合索引：CREATE INDEX idx_status_id ON users(status, id)—— 把过滤字段放前面，关联键放后面 -
orders表的user_id字段必须有索引，类型要和users.id严格一致（比如都是BIGINT，不能一边是INT一边是VARCHAR） - 执行
EXPLAIN后，检查type列：应为ref或eq_ref，而不是ALL或index - 避免在
JOIN条件里用函数或隐式转换，比如ON CAST(o.user_id AS CHAR) = u.id会强制全表扫描
什么时候该用手动物化临时表
当子查询结果稳定、复用率高（比如每天只变一次的“VIP 用户列表”），依赖优化器自动物化不可靠，不如自己控制。
- MySQL 写法：
CREATE TEMPORARY TABLE tmp_vip_users AS SELECT id FROM users WHERE status = 'vip' - 然后主查询走
JOIN tmp_vip_users，比每次都算子查询快得多 - PostgreSQL 可用
CREATE UNLOGGED TABLE加速写入，再加索引 - 注意：临时表生命周期绑定 session，别在长连接里忘了清理；线上批量任务建议用带前缀的普通表 + 定时清理
最常被忽略的点：改写 JOIN 后没验证结果是否等价——特别是 NULL 值、空集、一对多关系下的行数变化。上线前务必用小数据集比对结果集差异。
