MySQL深入-为什么会选错索引

相关文章：

MySQL高级-索引使用 (opens new window)

在使用索引的过程，会由于MySQL选错了索引，导致一条本来执行速度很快的SQL语句却需要很长的时间。

首先我们创建一个简单的表，并利用存储过程往其中插入10万条记录。

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；

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();

接下来我们执行一条查询语句：

select * from t where a BETWEEN 10000 and 20000;

我们使用 explain 命令来分析一下：

可以看到key为a，说明优化器在索引选择的时选择了索引a。

下面我们使用两个事务在进行如下操作：

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

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

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 用错了索引，导致了更长的执行时间。

优化器的逻辑

选择索引是MySQL Server层的优化器负责选择索引的工作。而优化器选择索引的目的是找到一个最优的执行方案，并用最小的代价去执行语句。

在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。

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

对于上述我们的实验操作，问题所在就是判断扫描行数的时候出现问题了。

扫描行数如何判断

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

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

通过show index的方法，我们查看t的索引情况。可以看到t表的三个索引的基数都不相同，而且都不精确。

如何得到索引的基数

索引基数的计算是通过采样统计的方式。

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

如果计算索引的基数把整张表的数据一行一行取出来，虽然是精确的结果，但是太耗时间。

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

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

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

索引选择代价问题

在show index from t的结果中我们可以发现虽然索引的基数不够精确，但是大体相差不大。但是为什么最终优化器选择了去扫描全表呢？

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

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

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

下面我们来看如下查询语句：

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只需要扫描1000行，而索引b则要扫描50001行，从行数的角度我们觉得可能选择索引a要快一些。

下面我们使用explain来分析一下：

可以看到最终优化器选择是索引b，而且扫描的行数也不50001行。

优化器选择索引，不仅考虑扫描行数，也考虑是否建临时表以及是否排序。

这里优化器选择索引b的原因：

• 因为有 order by b，优化器认为走索引 b 可以避免排序。
• 又有 limit 1，优化器认为只要找到了 1 条满足条件的记录，索引 b 的遍历就可以提前终止，虽然可能要遍历 50001 条记录，但是优化器认为这是值得冒险的事，所以决定了走索引 b；

索引选择异常和处理

在上诉我们的实验中，会因为优化器选择了错误的索引导致sql语句执行速度很慢。那么我们如何去处理索引选择异常的情况呢？

force index

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

相当于我们强制了优化器去选择我们指定的索引。

缺点：

• SQL语句书写不够优雅
• 如果索引名字更换了，对应SQL语句也需要更换

修改sql语句

我们可以考虑sql语句，引导Mysql使用我们期望的索引。

在上诉例子中，显然把“order by b limit 1” 改成 “order by b,a limit 1”，就可以让优化器走索引a。

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

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

建立更适合的索引

有时候需要根据业务需求去删除一些不必要的索引。

小结

优化器选错索引的原因：

• 扫描行数（索引的基数）
• 排序
• 回表查询

选错索引处理的方法：

• force index
• 修改sql语句
• 建立更适合的索引

参考

MySQL 实战 45 讲-极客时间 (opens new window)