【MySQL实战45讲10】MySQL为什么有时候会选错索引

Posted on In Views:

前言

我们在写SQL语句的时候，并没有指定选择哪个索引，也就是说，使用哪个索引是MySQL来确定的。

我们可能会遇到一些情况，一条本来可以执行得很快的语句，却由于MySQL选错了索引，而导致执行速度变得很慢。

看看下面这个例子，先建立一个简单的表，表里有a、b两个字段，并分别建上索引:

1
2
3
4
5
6
7
8
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)。

用存储过程来插入数据，如下：

1
2
3
4
5
6
7
8
9
10
11
12
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语句：

1
mysql> select * from t where a between 10000 and 20000;

意料之内的，通过explain命令查看这条语句的执行情况可以看到，使用的是索引a。

使用explain命令查看语句执行情况

但是，换一种方式，在已经准备好的包含了10万行数据的表上，再做如下操作。

Session A Session B
start transaction with consistent snapshot;
delete from t;
call idata();
explain select * from t where a between 10000 and 20000;
commit;

sessionA就是开启了一个事务。随后，session B把数据都删除后，又调用了idata这个存储过程，插入了10万行数据。

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

为了说明优化器选择的结果是否正确，我增加了一个对照，即：使用 force index(a) 来让优化器强制使用索引 a

下面的三条SQL语句，就是这个实验过程。

1
2
3
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 原来的查询语句执行情况对比。

下图就是slow log的结果

slow log结果

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

优化器的逻辑

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

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

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

那么，问题就是：扫描行数是怎么判断的？

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

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

MySQL是通过采样统计来得到索引的基数的，虽然把整张表一行行统计，可以得到精确的结果，但是代价太高了，索引选择了采样统计。

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

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

了解了MySQL是如何得到基数之后，再来看看优化器评估的，这两个语句的扫描行数是多少。

意外的explain结果

其中，Q1 的结果还是符合预期的，rows 的值是 104620；但是 Q2 的 rows 值是 37116，偏差就大了。而之前用 explain 命令看到的 rows 是只有 10001 行，是这个偏差误导了优化器的判断。

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

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

而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。

优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。

analyze table t 命令，可以用来重新统计索引信息，修正统计信息，这之后rows就变为了10001行了。

如果只是索引统计不准确，通过analyze命令可以解决很多问题，但是优化器不止是看扫描行数。

依然是基于这个表t，看看另外一个语句;

1
mysql> 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 行。

显然，如果使用索引a，执行速度明显会快很多，下面看看到底是不是这样的。

执行explain命令

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

使用explain命令查看语句执行情况2

可以看到，返回结果中 key 字段显示，这次优化器选择了索引 b，而 rows 字段显示需要扫描的行数是 50198。

从这个结果中，你可以得到两个结论：

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

索引选择异常和处理

一种方法是，像我们第一个例子一样，采用 force index 强行选择一个索引。

第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。

在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

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