in子查询在mysql中易慢，因5.6前无物化优化，每行外层都重执行子查询；5.7+仅对无关子查询物化，相关子查询仍逐行执行，导致n×m嵌套循环。
为什么 IN 子查询在 MySQL 里容易慢
因为 MySQL 5.6 之前对 IN (SELECT ...) 基本不做物化优化，每次外层行都要执行一遍子查询，变成嵌套循环（N × M）。即使子查询结果固定，也重复计算。5.7+ 虽支持物化，但前提是子查询不相关——一旦出现 WHERE t1.id = t2.parent_id 这类关联条件，物化就失效，照样回退成“对每行重跑子查询”。
- 典型慢现象：
EXPLAIN显示type=ALL或Extra列含Using where; Using join buffer - 真实场景：查“有订单的用户信息”，写成
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)，orders 表大时极慢 - 关键陷阱：你以为加了
orders.user_id索引就够了，但 MySQL 并不保证用它加速子查询执行——尤其当子查询被当成“依赖外部”的相关子查询时
把 IN 子查询改成 JOIN 的实操要点
这是最直接、兼容性最好、效果最稳的解法。本质是让优化器走 hash join 或 sort-merge，避免逐行触发子查询。
- 原写法：
SELECT u.* FROM users u WHERE u.id IN (SELECT o.user_id FROM orders o WHERE o.status = 'paid') - 改写后：
SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.status = 'paid' - 必须加
DISTINCT：否则一个用户多笔订单会重复返回用户行 - 索引要建在
orders(user_id, status)上（注意顺序！user_id在前才能用于 JOIN，status在后可过滤） - 如果只需要用户 ID，别用
*——SELECT DISTINCT u.id能显著减少临时表开销
EXISTS 比 IN 更适合“是否存在”类子查询
当子查询只关心“有没有匹配”，不关心具体值时，EXISTS 天然短路（找到第一条就停），且优化器更容易用上索引。
- 错误写法：
SELECT * FROM users u WHERE u.id IN (SELECT user_id FROM orders o WHERE o.user_id = u.id AND o.created_at > '2024-01-01') - 正确写法：
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.created_at > '2024-01-01') - 必须确保
orders(user_id, created_at)有联合索引，否则EXISTS仍可能全表扫 -
NOT EXISTS同理，但要注意 NULL 安全：若orders.user_id允许 NULL，NOT EXISTS可能漏数据，此时需额外IS NOT NULL判断
MySQL 8.0+ 可用 MATERIALIZED 提示强制物化，但慎用
仅当子查询确定无关、结果集小、且你明确控制执行计划时才考虑。它绕过优化器决策，但副作用明显。
- 语法：
SELECT * FROM users WHERE id IN (SELECT /*+ MATERIALIZE */ user_id FROM orders WHERE status = 'paid') - 生效前提：子查询不能引用外层表字段，否则提示被忽略
- 风险点：物化结果存临时表，若子查询结果大（比如百万级），反而拖慢整体；且该提示不被所有 MySQL 分支支持（如某些阿里云 RDS 版本会忽略）
- 验证是否生效：看
EXPLAIN FORMAT=TREE输出里是否有MATERIALIZE节点
实际调优时，优先改写为 JOIN 或 EXISTS，比折腾索引或提示更可靠。最容易被忽略的是：关联字段类型不一致（比如 users.id 是 BIGINT，orders.user_id 是 INT），会导致索引完全失效——连 JOIN 都救不了。
