mysql索引选错_MySQL为什么会选错索引？

场景

我们知道平时在我们写SQL语句中并没有指定要使用的索引，索引的选择是由MySQL决定的。具体又是由优化器来决定是用哪个索引(来达到最优的方案)。不过在频繁的删除数据和新增数据的场景下，优化器可能会选错索引(案例没有复现成功)。

优化器的逻辑

优化器在选择索引时主要参考：扫描行数、是否排序、是否启用临时表等

不准的扫描行数

MySQL在真正执行语句前并不能精确地知道满足条件的记录有多少条，而只能根据统计信息来估算这个记录数。这个统计信息就是索引的”区分度“。一个索引上不同的值越多，区分度就越高。一个索引上不同的值的个数，被称为”基数“(cardinality)。基数越高，区分度越好。

可以通过show index查看

MySQL获取这个基数采用的方式为采样统计，虽然将整张表取出来一行一行统计可以得到精确地结果，但是代价太高。

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

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

在MySQL中有两种索引统计的方式，可以通过innodb_stats_persistent的值来选择：设置为on时，表示统计信息会持久化存储。这时，默认的N是20，M是10；

设置为off时，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

由于是采样统计，无论N是8还是20，这个基数都是很容易不准的。

我们可以通过explain查看执行语句的扫描行数，然后这个rows是有可能不准的(可能的原因：虽然数据被删除了，但是由于有其他session开启了一致性读视图，原插入不能在之前的数据页操作，这时候MySQL去估计扫描行数时就变多了，因为这个范围的数据页变多了)。

可以是用analyze table t命令来进行重新统计索引信息。

另一个场景：1mysql> select * from t4 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

首先我们知道t4中并没有符合的数据，所以返回的是空。不过这里使用不同的索引是会对性能产生影响的，如果使用索引a，那么就是扫描索引a的前1000个值，然后取对应的id，再到主键索引上去查出每一行，然后根据字段b进行过滤。显然这样需要扫描1000行。

如果是用索引b，那么就是扫描索引b的最后50001个值，然后取对应的id，再到主键索引上去查出每一行，然后根据字段a进行过滤。显然这样需要扫描50001行。

那么如果让你选，你一定会选择使用索引b。我们来看看这条语句的执行计划：1mysql> explain select * from t4 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

我们可以看到返回的key代表MySQL选择了索引b，rows显示扫描行数是50128。强制是用索引a的话扫描行数是1000。索引a的扫描行数不准确。

MySQL选错了索引。

索引选择异常和处理

大部分情况下，MySQL都能选择正确索引，不过有时候也会出现上面的情况。我们可以使用以下的方式矫正：

force index

使用force index(index name) 强行选择一个索引。MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后评估每个索引的扫描行数。如果指定的索引出现在候选项中，就会直接使用该索引，不再评估其他索引的执行代价。

不过使用force index一来不够美观，而来如果索引改了名字，这个语句也需要改。如果数据库迁移了，可能还不支持这种语法。最主要的是因为无法做到变更的及时性，毕竟出现这种选错索引的情况很少见，一般开发都不会加上。都是等到线上服务器出现问题时才会去修改语句，然后测试、发布、对于生成环境这个过程不够敏捷。

修改SQL语句，引导数据库选择索引

优化器之所以选择索引b，是因为它认为使用索引b可以避免排序(b本身是索引，已经是有序的了，如果选择索引b的话，不需要再排序，只需要遍历)，所以即使索引b的扫描行数多，也判定为代价更小。

将语句修改为:1mysql> explain select * from t4 where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;

按照b,a排序，意味着两个索引都需要排序，这时候扫描行数成了影响决策的主要条件，于是优化器选择了只扫描1000行的索引a。

当然这样修改的方案不具有通用性，因为语句中刚好有limit 1，如果有满足条件的数据，order by b,a limit1 和order by b limit 1 都会返回b是中小的那一行。

还有一种不修改语义的方法：1mysql> select * from (select * from t4 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;

这里的加的limit100让优化器意识到使用索引b的代价是很大的(通过外围的limit1保证了逻辑正确)，其实是我们根据数据特征诱导了一下优化器，也不具有通用性。

新增或者删除索引

在有些场景下，我可以新建一个更合适的所有，来提供给优化器做选择，或者删除误用的索引。

思考题：为什么在实例1中，当开启一个事务拿到一致性读视图后，第二个事务将数据删除，重新插入会导致explain的扫描行数不对。

参考答案：

delete语句删除了所有的数据，然后再通过存储过程插入了10万数据，看上去是覆盖了原来的10万行。但是因为有一个事务开启了并没有提交(需要隔离级别是RR)，所以之前删插入的10万行数据不是能删除的。这样，之前的数据每一行数据都有两个版本，旧版本是删除之前的数据，新版本是标记为删除的数据。

这样索引a上的数据其实就有两份。

为什么主键不会这样呢？是因为主键是直接按照行数来估计的。而表的行数，优化器直接使用的是show table status的值。