MySQL in查询，exists，join对比

-- 1 IN子查询SELECT * FROM table WHERE id IN (SELECT tid FROM detail WHERE code 1);-- 2EXISTSSELECT * FROM table WHERE EXISTS (SELECT 1 FROM detail WHERE code 1 AND tid table.id);-- 3拆分-- 步骤1查出常量列表SELECT tid FROM detail WHERE code 1; -- 结果(1,2,3,4,5,6,7)-- 步骤2常量IN查询SELECT * FROM table WHERE id IN (1,2,3,4,5,6,7);-- 4JOIN写法SELECT table.*FROM tableINNER JOIN detail ON table.id detail.tidWHERE detail.code 1;① IN子查询原生写法先查子查询把结果集堆到一个临时表里然后再拿外层表去匹配。缺陷是临时表可能没索引匹配起来全表扫。而且MySQL优化器是个黑盒版本不同执行计划天差地别线上容易踩坑。② 常量IN先查出ID列表再塞到第二条SQL的IN里。这时候ID列表被当成常量数组MySQL直接走索引范围扫描range执行计划100%可控。代价是多一次网络请求但换来了绝对的稳定。③ EXISTS外层每扫一行就执行一次子查询去判断存不存在。它吃索引要求子查询表关联字段必须有索引同时要求外层结果集本身很小比如已经过滤到只剩几十条否则外层全表扫是灾难。④ JOIN把两张表关联起来可以返回两边的字段。但要注意一对多关系会产生重复数据可能需要加DISTINCT去重。性能取决于驱动表选择和关联字段索引可以用STRAIGHT_JOIN强制指定驱动顺序。最终选型一句话· 列表小1000条→ 用你的常量IN最稳· 需要查右表字段 → 用JOIN· 外层已过滤到很少数据且子查询是大表 → 用EXISTS· 原生子查询IN → 能不用就不用优化器不靠谱缺陷① 原生子查询IN的缺陷·物化临时表无索引5.6以前子查询结果集堆到临时表里外层每扫一行都要全表扫这个临时表数据量一大直接炸。· 优化器抽风即使5.6后有半连接优化但只要子查询里带GROUP BY、DISTINCT、UNION、聚合函数优化器立马退化为DEPENDENT SUBQUERY外层每行执行一次子查询复杂度O(n²)。·NULL值陷阱子查询结果里如果混进NULL整个IN条件返回UNKNOWN结果集直接变空业务上极难排查。· 执行计划版本波动MySQL 5.5、5.6、5.7、8.0对子查询优化策略都不一样升级MySQL版本可能导致SQL突然变慢。② 常量IN拆成两次查询的缺陷·SQL长度受限ID列表太长超过max_allowed_packet默认4MB直接报错超过eq_range_index_dive_limit默认200优化器会放弃索引走全表扫描。· 网络IO翻倍原来是1条SQL变2条网络延迟翻倍高并发下RT会上升。· 数据一致性风险两次查询之间子查询数据可能发生变化比如ID被删除导致两次结果逻辑不一致除非在事务内或业务允许脏读。· 列表去重负担如果子查询结果有重复ID应用层要自己去重不然IN里重复值会额外消耗索引匹配次数。· 分页不友好如果外层要分页LIMIT必须先查所有ID再分页可能查了一万个ID最后只取10条浪费严重。③ EXISTS的缺陷·外层必须全表扫描EXISTS是外层驱动内层如果外层没有其他过滤条件如WHERE status1它会全扫外层整个表每条都去子查询判断一次外层表大就必死。· 子查询索引要求严苛子查询关联字段必须建索引否则每行执行一次全表扫内层表直接灾难。· 依赖子查询陷阱如果写成了SELECT * FROM table WHERE EXISTS (SELECT tid FROM detail WHERE table.id detail.tid AND code 1)EXPLAIN里看到DEPENDENT SUBQUERY就意味着外层每行执行一次没法提前终止。每次子查询的SQL文本都一样但里面table.id的值每次都不同所以没办法提前把子查询结果算好复用必须一行一行地代入、执行。DEPENDENT——子查询依赖外层的当前行值无法独立执行。如果外层table有10万条数据子查询detail每次都能走索引0.1毫秒查到那10万次就是10秒。但如果把table先过滤到10条比如加个WHERE table.status1那子查询只执行10次0.01秒就完事。执行次数完全被外层行数绑架。外层扫多少行它就执行多少次。优化器没有任何办法“提前终止”或“批量合并”——因为每行的table.id都不一样优化器没法预判。④ JOIN的缺陷· 一对多产生重复数据如果右表有多条匹配左表数据会被重复返回业务必须用DISTINCT或GROUP BY去重增加排序和临时表开销。· 驱动表选错全盘皆输优化器选错驱动表该小表驱动大表时选反了直接导致全表扫大表性能暴跌。虽然可以用STRAIGHT_JOIN强制但需要人工分析执行计划。· 返回字段宽导致内存爆炸JOIN会把两表字段都缓存到join_buffer如果字段多、数据量大内存占用飙升。· 锁范围扩大关联查询在RR隔离级别下可能同时锁两个表的索引间隙死锁概率成倍增加。总结没有银弹。常量IN输在列表长度和两次查询的一致性原生IN子查询输在优化器黑盒EXISTS输在外层全表扫和索引依赖JOIN输在数据膨胀和驱动表选择。选哪个取决于数据量级、索引设计和业务容忍度底线是绝对不用原生IN子查询其他三种按场景选但必须先用EXPLAIN看执行计划