Mysql为什么有时候会选错索引

案例

正常情况

有一个表t ( id, a , b )，id是主键索引，a是Normal索引。

正常情况下，针对a进行查询，可以走索引a

并且查询的数量和预估扫描行数是差不多的，都是10001行

奇怪的现象

随着时间的变化，后面可能就会发生下面的情况

根据explain计划，我们发现数据还是那么多，但是不走a索引了，并且优化器知道有a索引，但是最终还是走了全表扫描。

优化器的逻辑

先了解一下优化器
选择索引是优化器的工作，而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少（扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断）

下面只讨论扫描行数带来的影响
那么优化器是怎么去估算需要扫描多少行？

我们可以通过命令看到有一个Cardinality（基数），选择索引需要扫描的行数就是通过它来判断的，它代表一个索引上不同的值的个数，值越大说明区分度越高，那么越有可能走这个索引

优化器的选择一定对么？

上面看到针对下面这个sql，优化器觉得全表扫描更合适，但实际上真的是速度最快的么？

select * from t where a between 10000 and 20000;

我们实际执行一下：

不接受优化器的建议，强行走a索引执行一下：

重点看3个指标：Query_time（执行耗时）、Rows_sent（返回行数）、Rows_examined（扫描/行数）
我们发现强行走索引a其实更快，实际扫描行数也少。那么为什么优化器不走索引a呢？

我们再执行一个命令（更新表的统计信息）：

ANALYZE TABLE t;

然后我们再看一下执行计划：

explain select * from t where a between 10000 and 20000;

发现优化器竟然又选择了索引a，说明是因为统计信息不准确，没有及时更新导致优化器进行了错误的选择。
接着实际执行一下：

select * from t where a between 10000 and 20000;

我们发现实际的扫描行数和预估的扫描行数对上了，并且也确实走了索引a，耗时也降下来了
最后我们再看一下索引的统计信息

统计信息确实和上面不一样了，更新了。但是这里有个问题是：虽然统计信息不一样了（能确保确实更新索引统计信息的sql起作用了）但统计信息和一开始也差不多，为什么Cardinality值差不多的情况下，优化器做出了不一样的选择？
因为实际上表数据经历了大量的删除、新增操作，Cardinality不会更新一些还未提交的事务数据，所以看似基数差不多，实际上基数不一样。优化器对主键的判断是基于实际表行数来判断的，所以主键的判断是准的，不准的是其他索引的统计信息。

总结

本文讨论了Mysql（InnoDB）在索引统计信息不准确或更新不及时的情况下，优化器基于统计信息进行粗估的执行计划，可能会选错索引。
我们一般应对的方法如下：

1. 更新索引统计信息
2. 修改SQL语句强制走固定索引
3. 新增索引（比如上面新增一个索引a,b）
4. 删除索引（假设优化器选择了索引b，确保该索引没有其他作用的前提下，那么删掉索引b，可能就会走索引a了）