IN 改成 EXISTS 后反而更慢？注意关联字段 NULL 和索引覆盖

很多人把EXISTS当作优化子查询的万能钥匙，但这其实是个误区。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）写起来确实优雅，但有个容易踩的坑：在MySQL中，CTE默认是“非物化”的。也就是说，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)，其执行方式和原始的子查询可能并无本质区别。

在MySQL 8.0.23及以上版本，必须显式添加MATERIALIZED提示才能强制物化：

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的限制，导致临时表被写入磁盘，带来巨大的I/O开销。

话说回来，有时候最直接、最可控的做法，反而是手动创建带索引的临时表：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仔细查看执行计划，光靠改变语法写法，是很难验证优化效果的。