为何MySQL会选择使用基数为1的type索引？

问题背景 #

创建categories表并插入100万条type=1的数据后，type索引的基数为1，但执行查询时MySQL仍选择该索引，导致查询耗时（0.80秒）远高于全表扫描（0.23秒）。

核心原因分析 #

1. 成本模型的IO估算偏差
   InnoDB的二级索引仅存储主键值，使用type索引时，优化器需要先扫描索引获取所有100万条主键，再逐个执行回表查询获取完整行数据。这种操作会产生大量随机IO，而全表扫描是顺序IO——顺序读取数据页的效率远高于随机读取，优化器的成本模型在这种极端场景下，没有准确评估两种IO模式的实际性能差异，误判了索引扫描的成本。

2. 统计信息与实际执行的脱节
   虽然SHOW INDEXES显示基数为1，优化器也知道该索引会返回全表数据，但它的成本计算逻辑中，对“回表阶段的开销”估算不足。默认情况下，优化器假设索引扫描的平均成本低于全表扫描，但在所有数据都集中在一个索引节点的极端场景下，这个假设不成立。

3. 版本特定的优化器逻辑
   MySQL 8.0.35的优化器在处理这种100%均匀分布的索引时，未触发“跳过低效索引”的判断逻辑，依然遵循了“存在匹配索引则优先考虑”的基础规则，忽略了该索引实际上毫无筛选价值。

解决办法 #

• 强制指定访问路径：在查询中使用FORCE INDEX(PRIMARY)或IGNORE INDEX(type)，直接让优化器采用全表扫描：

  SELECT * FROM categories FORCE INDEX(PRIMARY) WHERE type = 1;
  

• 删除无效索引：由于type字段的所有值完全相同，这个索引既不能提升查询效率，还会增加插入/更新时的索引维护成本，直接删除是最优选择：

  DROP INDEX type ON categories;
  

• 调整优化器参数（不推荐全局修改）：如果必须保留索引，可以临时调整optimizer_switch中的参数，比如关闭index_merge，但这种方法可能影响其他查询的优化逻辑，仅适合临时测试。

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