MySQL索引选择异常的原因揭秘

场景引入

我们都明白，MySQL里一张表能够支持多个索引。不过在编写SQL语句的时候，并没有主动指定要使用哪个索引，而是由MySQL来进行判定。然而有时候，MySQL会选错索引，致使执行速度变得极为缓慢。

举个例子，假设有一张表包含(id,a,b)三个字段，并且分别建立了索引。然后向这张表中插入10万行记录，取值是依次递增的，也就是数据从(1,1,1)一直到(100000,100000,100000)。

插入的过程借助一个存储过程来实现：

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

接下来分析一条SQL语句：

select * from t where a between 10000 and 20000;

很显然，这条语句本应能用上索引a。对这条语句进行EXPLAIN，查看执行状况：

（此处保留图片占位符）

接着，在表t上进行如下操作：

（此处保留图片占位符）

如上，session A开启了一个事务，随后session B删除所有数据，又调用存储过程插入数据并开展查询。

可是，这条查询语句并没有选择索引a。使用如下三条语句来进行实验：

set long_query_time=0;
select * from t where a between 10000 and 20000; 
select * from t force index(a) where a between 10000 and 20000;/*起到对照作用*/

慢查询日志如下：

（此处保留图片占位符）

能够发现，session B的查询语句走的是全表扫描，也就是MySQL用错了索引。

这个场景其实很常见，对应平常不断进行数据删除和新增的场景。所以，本文要把为什么会选错索引讲清楚。

优化器的逻辑

在MySQL中，选择索引是由MySQL的优化器来完成的。优化器选择索引的目的是找到一个最优的执行方案，用最小的代价来执行语句。

在数据库中，判断执行代价有诸多标准。直观来讲就是扫描行数，扫描行数越少，意味着访问磁盘的次数越少，消耗的CPU资源也就越少。除此之外，优化器还会综合考虑是否使用临时表、是否需要排序等因素。

在前面的例子中，没有涉及临时表和排序，那么就是在扫描行数的判断上出现了偏差。所以，我们需要知晓MySQL是怎样判断扫描行数的。

MySQL在执行语句之前，并不能精准知道满足条件的记录有多少条，只能依据统计信息来进行估算。这个统计信息指的是索引的区分度。一个索引上不同的值（我们称之为基数）越多，索引的区分度就越好。

可以运用show index方法来查看索引的基数。查看例子中表t的基数，结果如下：

（此处保留图片占位符）

能够发现，尽管三个字段插入的数据是相同的，但MySQL统计的基数却各不相同，而且都不准确。

由于逐行统计代价过高，MySQL在统计基数时采用的是采样统计的办法：采样统计时，InnoDB默认会选取N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，算出这个索引的基数。

数据表会持续更新，所以索引统计信息也会不断变化。当变更的数据行数超过总行数的1/M时，会自动触发重新进行一次索引统计。在MySQL中，索引统计有两种存储方式，可以通过设置参数innodb_stats_persistent的值来进行选择：

• 设为on，表示统计信息持久化存储，此时默认N=20,M=10；

• 设为off，表示统计信息只存在内存中，此时默认N=8,M=16。

虽然基数统计并非完全准确，但从show index的结果来看，大体还是接近的，所以选错索引还有其他缘由。

除了进行基数统计，优化器还会判断执行语句本身要扫描多少行。对于例子中的语句，优化器预估的扫描行数为：

（此处保留图片占位符）

从rows字段可以看出，没用上a索引的预计扫描行数是104620，而强制使用a索引的预计扫描行数是37116。

这时，你可能会产生疑问，既然用上索引a的扫描行数少，且该语句可以用索引a，为什么优化器不使用呢？

这是因为，如果使用索引a，在select *的时候，需要先从索引a获取id，再回到主键索引找出整行数据，优化器会计算这个代价；如果不使用索引a，是直接在主键索引上扫描并获取数据。在这个例子中，优化器认为直接扫描主键索引更快，尽管这个判断是错误的。

所以，选错索引的本质原因还是没有准确判断出扫描行数。

既然是统计信息有误，那就需要进行修正。可以使用analyze table t来重新统计索引信息：

（此处保留图片占位符）

能够发现这次判断是正确的。所以，当发现explain的结果和实际情况差距较大时，可以先使用analyze来重新统计。

基于相同的表t，来看另一个语句：

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

从条件来看，这个查询返回的是空。那么在索引的选择上，该语句会怎样选择呢？

按照我们自己的分析：

• 要是使用索引a，会先扫描索引a的前1000个值，取到对应的id后进行回表，然后依据字段b进行过滤，这样需要扫描1000行。

• 要是使用索引b，会先扫描索引b的最后50001个值，取到对应的id后进行回表，然后依据字段b进行过滤，这样需要扫描50001行。

对该语句进行EXPLAIN的结果：

（此处保留图片占位符）

可以看到，优化器选择的是索引b，预估扫描行数为50198，也就是MySQL又选错了索引。

索引选择异常和处理

碰到上面例子中选错索引的情况，处理办法主要有三种：

（1）采用force index强制选择索引

在不强制选择索引时，MySQL会根据词法解析结果分析出可能使用的索引，然后依次判断每个索引需要扫描多少行。而强制选择后，MySQL会直接选择这个索引。

比如对于例子2，假设使用force index强制选择索引a：

（此处保留图片占位符）

可以看到，使用合理的索引，速度快了很多。

不过强制选择也有缺点：

• 如果索引改名，该语句也得修改；

• 如果以后迁移到其他数据库，该语法不一定兼容；

• 变更不及时，往往是等出现选错索引的问题时才会去强制选择。

（2）修改语句

比如在例子2中，把order by b limit 1改为order by b,a limit 1，语义逻辑不变。但之前优化器使用索引b是因为觉得使用索引b可以避免排序，而修改后使用两个索引都需要排序，扫描行数就成了影响决策的主要条件，此时优化器会选择扫描行数较少的a。

这种方法的缺点就是不通用，需要根据不同语句做不同修改。

（3）新建更合适的索引

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