in子查询在mysql 5.7及更早版本中易重复执行，导致n次扫描；mysql 8.0+支持semi-join优化但有条件限制；join改写可提升性能，但需确保关联字段有索引且执行计划显示type为ref/eq_ref。
IN子查询在MySQL 5.7及更早版本中容易触发重复执行
当写成 SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active')，MySQL 5.7 及之前版本常将其识别为“相关子查询”，导致外层每扫描一行 orders，就重新执行一次子查询——哪怕子查询本身不依赖外层字段。这会引发 N 次独立的 customers 表扫描或索引查找，IO 直接翻倍。EXPLAIN 中常见 dependent subquery 或 Using temporary。
- 即使子查询非相关，优化器也可能先物化结果到磁盘临时表（尤其结果集 > tmp_table_size），再做哈希匹配，引入额外随机IO
- 若子查询含
GROUP BY、LIMIT或ORDER BY，几乎必然走临时表+文件排序路径 - MySQL 8.0+ 默认开启
semijoin=on，但需满足条件：子查询不能有聚合、无DISTINCT、关联字段有索引，否则仍退化
JOIN能复用索引并启用高效连接算法
改写为 SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.status = 'active' 后，数据库明确知道这是两个集合的关联操作，可选择嵌套循环（带索引查找）、块嵌套循环或哈希连接（MySQL 8.0+）。
-
orders.customer_id和customers.id都必须有索引，否则type仍为ALL，JOIN 不比 IN 强 - 如果
customers是小表，MySQL 可能为其构建内存哈希表，然后单次扫描orders，对每行用customer_id做 O(1) 查找 - 执行计划中看到
type: ref或eq_ref，且key列非 NULL，才是索引真正生效
IN列表过长时，SQL解析和网络传输本身就在消耗IO
当应用拼出 WHERE id IN (1,2,3,...,100000) 这类语句，问题早已不在执行阶段：10 万个整数字符串需要 MySQL 解析器逐个 tokenize，消耗大量 CPU 和内存；超长 SQL 还可能触发 max_allowed_packet 截断或解析失败。
- 网络传输一个几十KB的SQL包，比发三条短命令（
CREATE TEMPORARY TABLE+INSERT+JOIN）更不可靠 - 临时表方案中，
INSERT可批量提交（如每 1000 行一批），而 IN 列表无法分批 - 数据库对长 IN 列表的统计信息估算不准，容易选错执行计划，比如跳过
eq_range_index_dive_limit（默认200）后直接放弃索引深度分析
NOT IN 的NULL陷阱会隐式放大IO
WHERE col NOT IN (SELECT nullable_col FROM t) 只要子查询返回任意 NULL，整个条件恒为 UNKNOWN，结果集为空——这不是逻辑错误，而是语义陷阱。为规避它，有人强行加 WHERE nullable_col IS NOT NULL，但这会让子查询失去索引覆盖能力，被迫全表扫描。
- 用
LEFT JOIN ... WHERE ... IS NULL替代，逻辑清晰且索引友好：SELECT o.* FROM orders o LEFT JOIN customers c ON o.customer_id = c.id AND c.status = 'active' WHERE c.id IS NULL - LEFT JOIN 的
ON条件中把过滤下推（c.status = 'active'），避免先 JOIN 再 WHERE 导致中间结果膨胀 - 注意：若
customers.id是主键，c.id IS NULL能准确表达“未匹配”，无需担心 NULL 干扰
实际改写时最容易被忽略的是：JOIN 改写后是否真的减少了数据读取量？重点看 EXPLAIN 的 rows 和 Extra 字段——如果仍是 Using join buffer 或 Using temporary，说明驱动表没选对，或者缺少关键索引，光换语法没用。
