MySQL错误选择索引：如何配置使其自动选用最优联合索引？

问题背景 #

先明确场景细节：

• 表字段：Id(int)、DateTime(datetime(6))、CompanyId(int，外键)、IsExcluded(tinyint(1))
• 两个BTREE索引：
  1. 单字段索引：CompanyId
  2. 联合索引：CompanyId,DateTime,IsExcluded

执行以下查询时，MySQL默认选择单字段索引，耗时2.3秒；强制使用联合索引仅需0.015秒。但缩小DateTime查询范围（减少一天）后，它又会自动选用联合索引：

select IsExcluded,DateTime,CompanyId FROM table where 
IsExcluded = 0 and 
DateTime >= '2022-06-02' and 
DateTime < '2022-09-22' and 
CompanyId = 1;

核心疑问：明明联合索引效率更高，MySQL为何选错？有没有办法不用修改查询语句，让它自动选用最优的联合索引？

解决办法 #

1. 更新表的统计信息 #

MySQL优化器完全依赖表的统计信息判断索引成本，若统计信息过时，必然会做出错误选择。执行以下命令重新收集统计数据：

ANALYZE TABLE your_table_name;

更新后，优化器能准确识别联合索引的覆盖查询优势（无需回表取数据），大概率会自动切换到联合索引。

2. 调整优化器成本参数 #

若更新统计信息无效，可通过调整参数引导优化器倾向于选择联合索引：

• 调大eq_range_index_dive_limit：默认值为200，当范围查询的预估行数超过该值时，优化器会用模糊统计而非精确计算。若你的DateTime范围刚好卡在阈值附近，将其调至1000可让优化器精确评估联合索引的价值：

  SET GLOBAL eq_range_index_dive_limit = 1000;
  

  （全局参数需重启生效，也可用SET SESSION仅修改当前连接）
• 微调optimizer_costs参数：比如降低key_range_cost，降低范围索引的成本权重，让优化器更愿意选择带范围条件的联合索引。

3. 删除冗余的单字段索引 #

单字段索引CompanyId是联合索引的前缀，联合索引本身已支持仅查询CompanyId的场景（BTREE索引前缀可单独使用）。删除这个冗余索引后，MySQL只能选择联合索引，还能减少日常索引维护的开销：

DROP INDEX idx_companyid ON your_table_name;

（删除前确认无其他查询依赖该单字段索引，即便有，用联合索引也能正常执行，性能差异可忽略）

4. 将单字段索引设为不可见 #

不想删除索引的话，可将单字段索引标记为不可见，优化器默认不会选择它，但需要时仍可手动指定：

ALTER TABLE your_table_name ALTER INDEX idx_companyid INVISIBLE;

这样日常查询会自动使用联合索引，若后续有特殊场景需用到单字段索引，只需在查询中添加FORCE INDEX(idx_companyid)即可。

为何会出现索引选择异常？ #

本质是优化器的成本估算偏差：

• 它可能认为单字段索引CompanyId的选择性高（比如CompanyId=1的行数少），但忽略了查询后需要回表获取DateTime和IsExcluded的开销；而联合索引是覆盖索引，无需回表，实际开销远低于单字段索引。
• 当DateTime范围缩小，优化器预估回表行数减少，重新计算成本后发现联合索引更划算，因此自动切换。

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