MySQL查询条件微小差异引发性能大幅波动的技术求助

嘿，这个问题我碰到过类似的情况——本质上是MySQL查询优化器在执行计划选择上的「临界点」问题，咱们一步步拆解：

核心原因：优化器切换了执行计划 #

MySQL的优化器会根据表的统计信息，估算每个索引能过滤掉的数据量，然后选择它认为成本最低的执行计划。当你把经度范围从-105至-103.597调整到-105至-103.596时，这个微小的变化刚好触发了优化器的执行计划切换：

• 快的场景：优化器大概率选择了gender索引（因为gender=1是等值条件，能快速过滤掉一半左右的数据），然后在过滤后的结果集里，依次匹配生日范围、经纬度范围。这种方式的回表次数少，IO开销低。
• 慢的场景：优化器误判了经度范围的过滤效率，转而选择了longitude索引。但经度范围扩大后，匹配的行数骤增，而longitude是单列索引，要满足其他条件（gender、生日、纬度）必须回表查询原数据，这就导致大量的随机IO，直接把耗时拉到了原来的10倍。

你可以用EXPLAIN命令分别执行两次查询，对比输出的type、key、rows字段，就能直观看到执行计划的差异。

次要原因：统计信息的采样偏差 #

MySQL 5.7的InnoDB会自动收集表的统计信息，但这些信息是采样估算的，不是精确值。当你的经度范围刚好跨过了采样数据里的某个区间临界点时，优化器估算的匹配行数会出现巨大偏差，进而做出错误的执行计划选择。

比如，采样数据可能显示longitude > -103.597的行数很少，但longitude > -103.596的行数突然暴增（实际可能并没有），优化器就会误以为用longitude索引更划算。

解决办法 #

1. 先确认执行计划 #

跑两次EXPLAIN对比执行计划：

EXPLAIN SELECT user_id FROM profiles WHERE gender = 1 AND birthday BETWEEN '1980-01-27' AND '1988-01-27' AND longitude BETWEEN -105 AND -103.597;
EXPLAIN SELECT user_id FROM profiles WHERE gender = 1 AND birthday BETWEEN '1980-01-27' AND '1988-01-27' AND longitude BETWEEN -105 AND -103.596;

重点看key字段（用了哪个索引）和rows字段（估算的匹配行数），验证是不是索引选择发生了变化。

2. 更新统计信息 #

手动更新表的统计信息，让优化器拿到更准确的数据：

ANALYZE TABLE profiles;

更新后再跑两次查询，看看性能是否恢复稳定。

3. 创建合适的联合索引 #

单列索引在多条件查询下的效率本来就有限，你可以创建一个覆盖联合索引，让查询完全在索引里完成，不需要回表：

CREATE INDEX idx_gender_birthday_lon_lat ON profiles(gender, birthday, longitude, latitude);

这个索引把等值条件gender放在最前面，接着是范围条件birthday，最后是经纬度范围。因为InnoDB的二级索引叶子节点会包含主键user_id，所以这个索引可以直接满足查询的所有条件（过滤+返回结果），彻底避免回表的IO开销，性能会非常稳定。

4. 临时强制索引（不推荐长期用） #

如果更新统计信息和创建联合索引暂时没法操作，可以用FORCE INDEX强制优化器使用gender索引：

SELECT user_id FROM profiles FORCE INDEX(gender) WHERE gender = 1 AND birthday BETWEEN '1980-01-27' AND '1988-01-27' AND longitude BETWEEN -105 AND -103.596;

但这种方式是硬编码，后续数据分布变化后可能失效，所以还是优先前面的方案。

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