10 | MySQL为什么有时候会选错索引

优化器的逻辑

找到最优的执行方案，考虑的因素包括但不限于：扫描的行数、是否使用临时表、是否排序

索引的区分度

索引的区分度即是说一个索引中不同值的个数，又称之为“基数”，基数越大区分度越好

通过“采样统计”获取索引的区分度

InnoDB 默认选择N个页，统计每个页上的不同值，求平均数，然后乘以页面数，得到索引的基数。 索引的更新导致变更的行数超过 1/M 时，从新采样。 innodb_stats_persistent 设置为 on 时：N 为 20，M 为 10 innodb_stats_persistent 设置为 off 时：N 为 8，M 为 16

优化器有时会选择扫描行数更多的那个方案

如果使用普通索引预估扫描行数 3 万，不使用普通索引直接全表扫描 10 万行，由于使用普通索引还需要回表，这部分时间也考虑进去的话，优化器会认为全表扫描会更快

主动修正统计信息

analyze table table_name

索引选择异常的处理方案

• 使用 force index(index_name) 强制使用指定的索引
• 新建一个更合适的索引给优化器使用或者删掉误用的索引

问题讨论

通过 session A 的配合，让 session B 删除数据后又重新插入了一遍数据，然后就发现 explain 结果中，rows 字段从 10001 变成 37000 多。而如果没有 session A 的配合，只是单独执行 delete from t 、call idata()、explain 这三句话，会看到 rows 字段其实还是 10000 左右。你可以自己验证一下这个结果。这是什么原因呢？

答案 delete 语句删掉了所有的数据，然后再通过 call idata() 插入了 10 万行数据，看上去是覆盖了原来的 10 万行。但是，session A 开启了事务并没有提交，所以之前插入的 10 万行数据是不能删除的。这样，之前的数据每一行数据都有两个版本，旧版本是 delete 之前的数据，新版本是标记为 deleted 的数据。这样，索引 a 上的数据其实就有两份。然后你会说，不对啊，主键上的数据也不能删，那没有使用 force index 的语句，使用 explain 命令看到的扫描行数为什么还是 100000 左右？（潜台词，如果这个也翻倍，也许优化器还会认为选字段 a 作为索引更合适）是的，不过这个是主键，主键是直接按照表的行数来估计的。而表的行数，优化器直接用的是 show table status 的值。