带JOIN和WHERE子句的COUNT查询执行极慢问题排查

你遇到的是非常典型的MySQL优化器执行路径选择错误+缺少联合索引导致的性能问题，所谓「组合查询耗时不超过单查询之和」的预期是不成立的——数据库执行查询时不会把两个单查询的结果做交集，而是会基于自身统计信息估算成本，选它认为成本最低的执行路径，一旦估算偏差，性能会出现数量级下跌。

先拆解三个查询的执行逻辑，就能明白差异在哪：

• 第一个仅做join的count查询：优化器选择offer_test表的city_id索引直接关联searchmod_location_city的主键id，count(*)不需要返回其他字段，全程走索引不需要回表，所以0.2秒就能跑完。
• 第二个仅做price过滤的count查询：优化器直接走price字段的单值索引，扫描满足price>500000的索引条目直接计数，同样不需要回表，0.06秒属于正常水平。
• 第三个组合查询慢的核心原因：你目前只建了price、city_id两个单值索引，没有能同时覆盖join条件和where过滤条件的联合索引，优化器在选择执行路径时出现了估算偏差：它误以为先通过city_id索引完成两表全量关联，再回表判断price条件的成本更低，实际上这个路径需要扫描offer_test全表的city_id索引条目，对每一条记录都回表到主键索引取price字段做判断，再加上join的嵌套循环开销，实际扫描行数、回表次数是前两个查询的几十上百倍，耗时直接飙升到20秒。

从你提供的执行计划也能验证这点：执行过程中没有用到覆盖索引，存在大量回表操作，估算的扫描行数和实际符合条件的行数偏差极大。

• 最彻底的解决方案：给offer_test表建适配查询逻辑的联合索引。针对你这个查询场景，推荐建idx_cityid_price(city_id, price)：这个索引属于覆盖索引，既可以直接用来匹配join关联的city_id条件，索引本身也存储了price字段值，做where过滤时不需要回表取数，整个count计算全程扫索引就能完成，耗时可以降到毫秒级。如果你的业务中更多查询是先按price过滤再做关联，也可以调整联合索引顺序为idx_price_cityid(price, city_id)，优先匹配高频过滤条件，多个联合索引可以根据实际业务查询频率搭配使用。
• 临时应急方案：如果暂时不方便加索引，可以用STRAIGHT_JOIN强制连接顺序，写法如下：

SELECT count(*) FROM offer_test
STRAIGHT_JOIN searchmod_location_city
    ON (offer_test.city_id = searchmod_location_city.id)
WHERE price > 500000

这个写法会强制offer_test作为驱动表，先走price索引过滤出符合条件的记录再做关联，能大幅降低扫描行数，但属于绕过优化器的临时手段，长期还是要靠合理的联合索引解决问题。

• 辅助手段：执行ANALYZE TABLE offer_test, searchmod_location_city;更新两张表的统计信息，部分场景下优化器选错路径是因为统计信息过时、对条件筛选率的估算不准，更新后可能选到正确的执行路径，但这个方案效果不稳定，不能替代索引优化。

最后提个通用优化思路：不要指望多个单值索引能解决多条件、多关联的查询性能问题，只要查询中同时涉及多个过滤条件、关联条件，优先考虑建顺序合理的联合索引，尽量让查询走覆盖索引，避免不必要的回表，就能解决90%以上的类似慢查询问题。

内容的提问来源于stack exchange，提问作者Sygol