【实战篇】MySQL为什么有时候会选错索引？

场景分析 1

如下数据库表：

CREATE TABLE `t` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `a` (`a`),
 KEY `b` (`b`)
) ENGINE=InnoDB;

然后，我们往表 t 中插入 10 万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到 (100000,100000,100000)。
分析以下 sql：

select * from t where a between 10000 and 20000;

在这里插入图片描述
这条查询语句的执行也确实符合预期，key 这个字段值是’a’，表示优化器选择了索引 a。

接下来，我们进行如下操作：
在这里插入图片描述
这时候，session B 的查询语句 select * from t where a between 10000 and 20000 就不会再选择索引 a 了。我们可以通过慢查询日志（slow log）来查看一下具体的执行情况。

为了说明优化器选择的结果是否正确，增加了一个对照，即：使用 force index(a) 来让优化器强制使用索引 a。下面的三条 SQL 语句，就是这个实验过程。

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

第一句，是将慢查询日志的阈值设置为 0，表示这个线程接下来的语句都会被记录入慢查询日志中；
第二句，Q1 是 session B 原来的查询；
第三句，Q2 是加了 force index(a) 来和 session B 原来的查询语句执行情况对比。

可以看到，Q1 扫描了 10 万行，显然是走了全表扫描，执行时间是 40 毫秒。Q2 扫描了 10001 行，执行了 21 毫秒。也就是说，我们在没有使用 force index 的时候，MySQL 用错了索引，导致了更长的执行时间。

这个例子对应的是我们平常不断地删除历史数据和新增数据的场景。

优化器的逻辑

我们之前就提到过，选择索引是优化器的工作。

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

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

扫描行数如何判断

这个简单的查询语句并没有涉及到临时表和排序，所以 MySQL 选错索引肯定是在判断扫描行数的时候出问题了。

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

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

我们可以使用 show index 方法，看到一个索引的基数。如下图所示，就是表 t 的 show index 的结果。虽然这个表的每一行的三个字段值都是一样的，但是在统计信息中，这三个索引的基数值并不同，而且其实都不准确。
在这里插入图片描述

MySQL 是怎样得到索引的基数的呢？

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

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

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

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

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

这次的索引统计值（cardinality 列）虽然不够精确，但大体上还是差不多的，选错索引一定还有别的原因。

其实索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行。
在这里插入图片描述
其中，Q1 的结果还是符合预期的，rows 的值是 104620；但是 Q2 的 rows 值是 37116，偏差就大了。而我们之前用 explain 命令看到的 rows 是只有 10001 行，是这个偏差误导了优化器的判断。

到这里，可能你的第一个疑问不是为什么不准，而是优化器为什么放着扫描 37000 行的执行计划不用，却选择了扫描行数是 100000 的执行计划呢？

这是因为，如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。（回表）

使用普通索引需要把回表的代价算进去，在最开始执行 explain 的时候，也考虑了这个策略的代价，但最开始的选择是对的。也就是说，这个策略并没有问题。

所以冤有头债有主，MySQL 选错索引，这件事儿还得归咎到没能准确地判断出扫描行数。原因如下：

因为有一个事务 A 没有提交，事务 B 删除之前的 10W 行数据并不会直接删除（标记删除）。这样，索引 a 上面的数据其实就有两份。
而索引的统计选择了N个数据页，这部分数据页不受到前台事务的影响，所以整体统计值会变大，直接影响了索引选择的准确性；

这时候，可以使用 analyze table t 命令，重新统计索引信息。我们来看一下执行效果：
在这里插入图片描述

场景分析 2

select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

从条件上看，这个查询没有符合条件的记录，因此会返回空集合。 a、b 这两个索引的结构图如下所示：
在这里插入图片描述
如果使用索引 a 进行查询，那么就是扫描索引 a 的前 1000 个值，然后取到对应的 id，再到主键索引上去查出每一行，然后根据字段 b 来过滤。显然这样需要扫描 1000 行。

如果使用索引 b 进行查询，那么就是扫描索引 b 的最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描 50001 行。

我们来验证下，mysql 是如何选择的：

explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

在这里插入图片描述
可以看到，返回结果中 key 字段显示，这次优化器选择了索引 b，而 rows 字段显示需要扫描的行数是 50198。由此，我们可以得到以下结论：

扫描行数的估计值依然不准确；
这个例子里 MySQL 又选错了索引。

解决方法

采用 force index 强行选择一个索引

MySQL 会根据 词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行 。如果 force index 指定的索引在候选索引列表中，就 直接选择这个索引，不再评估其他索引的执行代价。

不过一般不使用 force index，一来这么写不优美，二来如果索引改了名字，这个语句也得改，显得很麻烦。而且如果以后迁移到别的数据库的话，这个语法还可能会不兼容。

而且因为选错索引的情况还是比较少出现的，所以开发的时候通常不会先写上 force index。而是等到线上出现问题的时候，你才会再去修改 SQL 语句、加上 force index。但是修改之后还要测试和发布，对于生产系统来说，这个过程不够敏捷，变更的及时性也不够。

修改语句，引导 MySQL 使用我们期望的索引

比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。
在这里插入图片描述
之前优化器选择使用索引 b，是因为它认为使用索引 b 可以避免排序（b 本身是索引，已经是有序的了，如果选择索引 b 的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。