浅谈mysql查询优化机制，为什么mysql有时会走错索引或者不走索引？（索引统计）

东方青天

已于 2022-07-08 19:47:56 修改

阅读量1k

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 java

于 2022-07-07 19:51:41 首次发布

本文链接：https://blog.csdn.net/qq_31686135/article/details/125664856

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

相信很多程序猿在使用mysql的过程中会碰到一些让人摸不着的头脑的问题，甚至是‘灵异’事件，明明刚刚查询还很快，表数据也没增加多少，为什么查询突然变慢了，当然，导致查询变慢的原因有很多，今天我们主要介绍一下查询优化器在索引上容易出现的问题，你也可以理解为mysql的bug。

现在我们新建一张表，语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在这张表中，id是自增主键（这个很重要），a,b为普通索引，d没有索引。现在我们要往其中插入数据，为了方便后续操作，这里使用存储过程来实现批量插入。代码如下：

CREATE PROCEDURE `test`()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t (`a`,`b`,`d`) values(i,i,i);
    set i=i+1;
  end while;
end

我们先使用存储过程往表中插入数据。注意由于mysql默认事务自动提交，如果不设置取消自动提交或者显示开启事务，这里每一次插入都会单独开启一个事务，耗时极长，这里我们开启一个事务调用存储过程。

现在表中已经有10万条数据了，此时我们执行这样一条语句，结果会是怎样呢?

select * from t where a between 10000 and 20000;

你肯定会说，a有索引，肯定走a索引啊，是的至少这一句是这样的，下面是执行计划

在实际的业务场景中，有些表会经常的删除和插入数据，现在我们做如下操作，先开启事务A，并且开启一致性视图，如下：

然后开启视图B，并且使用delete删除表中的所有数据，重新调用存储过程插入数据。

此时表中数据任然是十万条，再次执行上面的查询，结果会是怎样呢？

看到这里是不是已经颠覆了你的认知，有索引竟然不走索引？从执行计划可以看出，优化器是预计走a索引的，但是实际上没有使用a索引，可能有些细心的同学们已经注意到了，两次执行计划的预计扫描行数差距甚大，第一次是10001行，与实际扫描行数没有出入，但是第二次的预计扫描行数却是99918，为什么会出现这种情况呢？可能有些同学更关心出现了这种情况怎么办，首先，如果你能够确定走索引a更好的话，可以使用force index 显示指定索引，要求优化器必须走指定的索引去查询，如:

当然这里的预计扫描行数还是不对。

说到这里就不得不介绍一下mysql的查询优化机制了，对于优化器来说，首先考虑是否需要额外的空间、排序，显然这里是没有这种情况的，这里影响优化器选择的是扫描行数，为什么第二次不走索引呢，因为优化器认为扫描行数太多还得回表不如直接在聚簇索引中去查。mysql的这种策略显然是没有问题的，问题在于为什么扫描行数不对，我们都知道，索引的性能一个很大的关键性指标就是索引的区分度，那么mysql是如何计算索引区分度的呢，答案是采样统计。

mysql查询引擎中以数据页为单位组织数据，mysql会取N个数据页，然后统计每个数据页中索引不同值的个数，计算出平均值然后乘N，得到统计基数，至于为什么要采样统计，很简单，性能。

mysql的参数innodb_stats_persistent可以控制N的值。

on：统计信息会持久化存储。 N 是 20，M 是 10。

off：统计信息只存储在内存中。N 是 8，M 是 16。

M的意思是当变更的行数超过1/M时，会重新进行索引统计。

不管是哪一种方式，采样统计都无法避免出现偏差，至于这里为什么会出现这么大的偏差，原因如下

第一：

表主键自增，第二次执行的时候实际上id已经是从100001开始了，当然光是这样还不足以让统计出错。

第二：上面我们第一个事务开启了一致性视图，由于一致性视图的存在导致mysql统计时误认为前面的十万条数据还在，这时候id列没有重复但是a,b重复了一半，这是导致统计出错的元凶。

知到了问题所在，解决起来也就简单了，重新统计索引即可。analyze table [表名]，要记得关闭第一个事务的一致性视图哦，否则再次统计还会是错的。