MySQL相似查询性能差异大，为何InnoDB选择不同索引？

这是MySQL基于成本的优化器（CBO）在决策时，根据查询条件的范围变化，估算出不同索引的执行成本后，选择了它认为更高效的索引——但实际执行效果和预估出现了偏差，具体原因可以从这几个角度拆解：

1. 优化器的成本估算逻辑 #

MySQL优化器会对比每个可用索引的预估扫描行数和索引类型成本，选择总成本最低的执行路径：

• 第一个查询（event_datez从2019-10-30开始）：优化器估算通过i_event_2索引（推测是event_datez相关的单字段或联合索引）进行范围扫描，仅需处理约297万行数据，再过滤出survey_id=158的结果，综合成本更低，因此选择了该索引。
• 第二个查询（event_datez提前一天，范围更大）：优化器判断i_event_2索引的范围扫描会覆盖更多数据，转而认为通过FK_g1lx0ea096nqioytyhtjng72t索引（survey_id的单字段索引）先定位所有survey_id=158的行（约1627万行），再过滤时间范围的成本更低——但实际执行时，扫描1600多万行的开销远大于扫描300万行，直接导致耗时暴增。

2. 统计信息的偏差 #

优化器的成本估算完全依赖InnoDB的表/索引统计信息，如果这些信息过时或不准确，就会引发决策失误：

• 可能survey_id=158的实际行数远多于优化器的预估，或者event_datez范围内的实际数据分布和统计信息不符，让优化器错误判断了两种索引的执行成本。
• 你可以尝试执行ANALYZE TABLE event;更新统计信息，看看优化器是否会调整索引选择逻辑。

3. 单字段索引的局限性 #

当前的两个索引都是单字段索引，无法同时高效过滤两个查询条件：

• i_event_2索引擅长快速定位时间范围，但需要额外过滤survey_id；
• survey_id索引擅长定位特定问卷的数据，但需要额外过滤时间范围。
  当时间范围扩大时，优化器认为单靠时间索引的过滤效果变差，就转向了survey_id索引，但实际效果并不理想。

如何解决？ #

最彻底的方案是创建联合索引，让两个查询条件都能被索引高效利用：

CREATE INDEX idx_survey_eventdate ON event(survey_id, event_datez);

这个索引可以同时匹配survey_id=158的等值条件和event_datez的范围条件，直接定位到目标数据，不需要额外过滤，无论时间范围多大，性能都能保持稳定。

如果需要临时解决问题，也可以用FORCE INDEX强制指定高效索引：

select count(*) from event FORCE INDEX(i_event_2) 
where survey_id = 158 and event_datez>'2019-10-29 00:00:00' and event_datez<'2019-11-28 23:59:59';

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