MySQL 8.0下带ORDER BY的JOIN/子查询慢、拆分查询更快的问题解析
问题根因拆解
- 索引层面不存在缺陷:你在user表创建的联合索引
user_1(group_id, id, deleted)完全可以支撑两种高效查询路径:一是按group_id批量过滤匹配的用户记录,二是沿user.id顺序逐行判断匹配性后提前终止,不需要额外调整索引结构。 - 慢查询的实际执行逻辑:优化器最终选择的路径是,先从user_group表取出所有
account_id=1对应的10个group_id,再遍历这10个group_id去user表的联合索引上拉取所有匹配的用户记录(共约10万条,和你统计的100110条读取量完全吻合)。由于联合索引的排序规则是先按group_id分组、再按id排序,跨不同group_id取出的用户记录天然没有全局id顺序,因此必须将这10万条记录写入临时空间做全局归并排序,最后才取limit 10的结果,整个过程没有任何提前终止的机制,最终耗时30ms。 - 为什么手写IN列表的查询性能跃升:当你把10个group_id硬编码到SQL的IN条件中时,优化器会重新评估成本,识别出“沿user.id的主键顺序逐行扫描、判断当前行的group_id是否在IN列表中、凑够10条结果就立刻停止扫描”的路径成本最低。由于10个group仅占全部用户的1%,平均扫描不到20行user记录就能凑够10条符合条件的结果,且因为是沿id顺序扫描,返回结果天然满足order by的排序要求,完全不需要临时排序,因此耗时仅1ms。
为什么MySQL 8.0.23优化器无法自动匹配最优路径
这是该版本优化器的典型成本估算偏差,并非SQL逻辑的问题:
- 处理带
order by + limit的关联查询时,优化器会评估“沿排序列顺序扫描、逐行关联判断匹配性”的路径,但会错误忽略limit带来的提前终止效应——它会默认认为这个路径需要扫描全量user表才能拿到完整结果,因此把这条路径的成本算得极高,不会优先选择。 - 对“先按关联键批量拉取所有匹配的user记录、再全局排序取limit”的路径,优化器会严重低估跨group排序的资源消耗,甚至完全忽略临时排序的开销,最终判定这条路径性价比更高。
- 对IN型子查询转半连接的场景,8.0.23版本默认不启用“沿排序列顺序扫描提前终止”的优化规则,这条规则是后续8.0.27版本才修复引入的,因此你写的IN子查询会和JOIN查询走完全一致的慢路径。
- 与PostgreSQL的性能差异本质:PG的优化器会自动识别带limit的关联查询的提前终止效应,不管哪种路径都会把limit截断带来的扫描行数下降纳入成本计算,因此能自动选出和手动拆分查询等价的快路径,不存在这个版本的能力盲区。
单条SQL的优化改写方案
不需要手动拆分两步查询,通过强制指定执行路径即可达到1ms的查询效率,两种可直接落地的写法:
- 写法1:用
STRAIGHT_JOIN强制连接顺序为“user表作为驱动表,沿主键id顺序扫描逐行关联user_group表”
执行逻辑和手写IN列表的快路径完全一致:沿user.id顺序扫描,每读到一行就用group_id等值查询user_group表,判断account_id是否匹配,凑够10条结果就停止扫描,无额外排序开销。select u.* from user u STRAIGHT_JOIN user_group g on u.group_id = g.id where g.account_id = 1 order by u.id limit 10; - 写法2:将IN子查询改写为EXISTS关联形式,触发优化器逐行判断、提前终止的执行逻辑
select u.* from user u where exists ( select 1 from user_group g where g.id = u.group_id and g.account_id = 1 ) order by u.id limit 10;
性能差异的核心根源
- 慢路径是全量匹配的批处理逻辑:必须把所有符合关联条件的10万条记录全部读取、排序完成,才能返回最前面的10条结果,不存在提前终止的可能。
- 快路径是流式扫描的提前终止逻辑:从id最小的记录开始逐行判断匹配性,只要攒够10条符合条件的结果就立刻停止扫描,完全不需要处理全量数据,扫描量是慢路径的万分之一级别,自然能达到1ms的响应速度。
内容的提问来源于stack exchange,提问作者Stephen
相关产品推荐
相关产品推荐

