【MySQL】10｜MySQL为什么有时会选错索引？

我们都知道在MySQL中一张表可以支持多个索引。但是我们在写sql的时候，并没有主动指定使用哪个索引，也就是说，使用哪个索引是由MySQL来确定的。

有时我们可能会遇到这种情况，明明一个语句可以执行的很快，却由于MySQL选错了索引，而导致执行速度变得很慢。

优化器的逻辑

在第一篇文章中，我们知道选择索引是优化器的工作。

优化器选择索引的目的，是要找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

1 扫描行数

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息估算记录数。

这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。一个索引上不同的值的个数，我们称之为“基数”。也就是说，这个基数越大，索引的区分度就越好。

我们可以是用show index的方法，看到一个索引的基数。

2 怎么得到索引基数

MySQL 采用采样统计的方法。因为虽然把整张表一行一行拿出来统计，结果比较精确，但是代价也比较高。

采样统计的时候，InnoDB 默认会选择N个数据页，统计这些页面上不同值，得到一个平均值，然后乘以这个索引的页面数M，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重做一次索引统计。

在MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

• 设置为 on 的时候，表示统计信息会持续化存储。这时，默认的N是20，M是10。
• 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

由于是采样统计，所以结果都是不准确的。

优化器除了根据索引基数来判断，还会考虑到二级索引需要回表查询，这就增加了一次查询。

可以使用 analyze table t 命令重新统计索引信息。

3 索引选择异常和处理

大多数时候，优化器都能找到正确的索引，但遇到MySQL选错索引，导致执行很慢时，我们该怎么办呢？

1. 采用force index强行指定一个索引，但不建议在程序中写死
2. 修改语句，引导MySQL使用我们期望的索引
3. 在有些场景下，可以新建一个更合适的索引，来提供给优化器选择，或者删掉误用的索引