第十讲笔记：MySQL 为什么有时会选错索引

一般情况下我们写 SQL 语句不会去选择索引。

我们先建个简单表，并往里面添加 100000 行数据，例如：（1，1，1）。。。（100000，100000，100000）

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

当想查看一条 SQL 语句的执行情况时，可以选择 expain 命令。比如：

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

可见，key 为 a ，表示使用的是 a 这个索引。可有时却会使用错。

rows 这个字段表示预计扫描的行数

优化器的逻辑

优化器的目的是找到一个最优的解决方案，并用最小的代价去执行语句。

其中，扫描行数是影响执行代价的因素之一。

扫描行数是怎么判断的呢？

MySQL 在真正执行语句之前不会知道扫多少行，只能根据统计信息来估算记录数。

这个就是索引的区分度。一个索引上不同的值越多，这个索引的区分度就越高。一个索引上不同值的个数，我们称为“基数”（cardinality）。基数越大，索引的区分度越好。

可以使用 show index 的方法来，看到索引的基数。但这个数往往不是准确的。

那 MySQL 怎么得到的这个基数呢？

如果把整张表拿出来统计，开销太大了。于是使用“采样统计”方法。

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

而表上的数据会进行更新的，所以，当变更的数据行数超过 1/M 的时候，会自动触发一次索引统计。

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

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

不论是哪一种，这个基数很容易不准。

其他的判断因素

当扫描器认为，在普通索引上要扫 37000 行，主键索引要扫 100000 行，也有可能去扫主键。

因为，扫描器认为，每次从普通索引上拿一个值，就要去主键索引上查出整行数据，这个代价优化器也要算进去。直接在主键索引上扫，没有额外开销。

我们可以用 analyze table t 来重新统计索引信息。这样会好一点。

索引选择异常和处理

第一种方式，直接在 SQL 语句中加入 force index(indexName) 强行选择索引。但是不推荐，原因如下：

• 这么写不好看，而且索引改了名字，这个语句也得改
• 变更的及时性。一般来说，选错索引情况比较少，在开发的时候不会先写上 force index。而是等到上线出现问题了才会修改 SQL 语句。

第二种方式，可以修改 SQL 语句，引导 MySQL 使用我们期望的索引。

比如我们可以把 order by b limit 1 改成 order by b,a limit 1 ，语义是相同的。

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

现在 order byb,a 这种写法，要求按照 b,a 排序，就意味着使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描 1000 行的索引 a。

当然，这种修改并不是通用的优化手段，只是刚好在这个语句里面有 limit 1，因此如果有满足条件的记录， order byb limit 1和 order byb,a limit 1 都会返回 b 是最小的那一行，逻辑上一致，才可以这么做。

如果你觉得修改语义这件事儿不太好，这里还有一种改法。

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

在这个例子里，我们用 limit 100 让优化器意识到，使用b索引代价是很高的。其实是我们根据数据特征诱导了一下优化器，也不具备通用性。

第三种方式是，可以单独给场景建一个更适合的索引，给优化器选择。

不过，在这个例子中，我没有找到通过新增索引来改变优化器行为的方法。这种情况其实比较少，尤其是经过 DBA 索引优化过的库，再碰到这个 bug，找到一个更合适的索引一般比较难。

如果我说还有一个方法是删掉索引 b，你可能会觉得好笑。但实际上我碰到过两次这样的例子， 最终是 DBA 跟业务开发沟通后，发现这个优化器错误选择的索引其实根本没有必要存在，于是就删掉了这个索引，优化器也就重新选择到了正确的索引。

——引用自大佬原话