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

可能会出现一种情况，一条本来可以执行的很快的语句，却由于MySQL选错了索引，而导致执行速度变的很慢。

先建一个表：

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;

给这个表插入10W行记录，取值按整数递增，即:(1,1,1),(2,2,2),(3,3,3)，直到（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，如下图是执行情况：

作者的结果：

 

再做如下操作：

session A开启了一个事务，随后session B把数据都删了，然后又调了一遍存储过程，插入了10W行数据。

这时候，session B的查询语句select * from t where between 10000 and 20000就不会再选择索引a了。可以通过慢查询日志（slow log）来查看一下具体的执行情况。

为了说明更清楚，这儿增加了一个对账，即：使用force index(a)来让优化器强制使用索引a。如下三条语句：

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是强制使用了索引，和session B原来的查询语句做对比。

不知道出于什么原因，没有复现，以下是作者的慢查询日志：

Query_time:执行时间              

Rows_examined:扫描行数

可能出现改情况的场景：平常不断地删除历史数据和新增数据的场景。

优化器的逻辑

选择索引是优化器的工作。

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

当然扫描行数不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。只是这儿的语句比较简单，没有涉及到别的，只有扫描行数。

扫描行数是怎么判断的？

不能精确的知道满足这个条件的记录有多少行，只能根据统计信息来估算记录数。

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

通过show index from tablename;语句可以看到一个索引的基数，如下图就是表t的基数：

作者的结果：

 

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

因为把整张表拿出来一行行统计代价太大，所以MySQL“采样统计”的方法。

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

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

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

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

不管怎么设置，因为是采样统计，所以基数都是很容易不准确的。

 

但是，从上面的图可以看出虽然不准确，但是大概差不多。选错索引肯定还有别的原因。

索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行。

rows是预计扫描行数。

Q1因为选错了索引，rows的值104620结果基本符合预期；但是Q2的37116，偏差就大了，而图1中用explain命令看到的rows是只有10001行，是这个偏差误导了优化器的判断。

为什么放着扫描37000行的执行计划不用，却选择了扫描行数是100000的执行计划呢？

因为如果使用索引a，每次从索引a上拿到一个值都要回表，这个代价也算进去了；而如果选择扫描10万行，是直接在主键上扫描，没有额外的代价；优化器会估算这两个选择的代价，从结果来看，优化器认为直接扫描主键索引更快。当然从实际执行来看，这个选择不是最优的。

所以归结原因，选错索引，还是因为没能准确判断出扫描行数。

 

既然是统计信息不对，那就修正，可以通过analyze table t命令，来重新统计索引信息，如下：

这就对了。

所以实践中，如果发现explain的结果预估的rows值跟实际情况差距比较大，可以采用这个方法来处理。

 

这个语句：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个值，其他与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描50001行。

但是执行结果：

使用的是索引b。从这个结果得到两个结论：

1.扫描行数的估计值依然不准确；

2.这个例子里MySQL又选错索引了。

 

索引选择和异常处理

大多数能选对，但是碰到这种选错的时候应该怎么办呢？

一种方法是，采用force index强行选择一个索引。force index起到了“矫正”的作用。但是这样不够灵活，如果索引要改或者发生数据库迁移的话，代码也得改。而且线上出现问题，这样改也不够敏捷。

第二种方法是，可以考虑修改语句，应道MySQL使用我们期望的索引。比如这个例子，可以把“order by b limit 1”改成“order by b,a limit 1”,语义逻辑是相同的。

执行效果（扫描行数变成了999）：

之前优化器认为使用索引b可以避免排序（b本身是索引，已经是有序的了），所以计算扫描行数多，也判断为代价更小。

现在改成order by b,a这种写法，以为这两个字段都要排序，这样扫描行数就成了主要影响条件了，所以选择索引a.

也可以这样：

通过limit 1000告诉优化器，使用索引b的代价是很高的。

第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删除无用的索引。

 

上一篇：09 | 普通索引和唯一索引，应该怎么选择？

下一篇：11 | 怎么给字符串字段加索引？