您的位置:首页 > 数据库 > > 正文

mysql索引失效怎么办(MySQL选错索引的原因以及解决方案)

更多 时间:2022-04-03 12:54:24 类别:数据库 浏览量:208

mysql索引失效怎么办

MySQL选错索引的原因以及解决方案

MySQL 中,可以为某张表指定多个索引,但在语句具体执行时,选用哪个索引是由 MySQL 中执行器确定的。那么执行器选择索引的原则是什么,以及会不会出现选错索引的情况呢?

先看这样一个例子:

创建表 Y,设置两个普通索引, 创建一个存储过程用于插入数据。

MySQL: 5.7.27, 隔离级别: RR

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • CREATE TABLE `Y` (
  •  `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;
  • ?
  • 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 Y (`a`,`b`) values(i, i);
  •   set i=i+1;
  •  end while;
  • end;;
  • delimiter ;
  • call idata();
  • 查看如下事务:

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

    如果单独执行 Session B 中 select * from Y where a between 10000 and 20000;,毫无疑问会选择 a 这个索引。

    但如果安装 Session A,Session B 的顺序执行,发现索引的选择如下:

    mysql索引失效怎么办(MySQL选错索引的原因以及解决方案)

    可以发现,在 Session B 的场景下,执行器却没有选择 a 所在的索引,而是选择基于主键索引的全表扫描。

  • ?
  • 1
  • 2
  • set long_query_time=0;
  • --将慢查询日志打开,并将阙值设为 0. 在记录的日志中,可以发现 MySQL 并没有选择 a 所在的索引,同时花费了更长的时间。
  • 这样看,MySQL 的优化器不一定每次都能选择合适的索引。想要理解出现该现象的原因,就要从优化器的选择逻辑说起。

    优化器

    MySQL 中优化器的目的就是找到一个最优的执行方案,从而用最小的代价去执行语句。

    优化器在选择索引时,主要会考虑如下的因素:

    • 扫描的行数:扫描的行数越少,就证明访问磁盘数据的次数越少,消耗的 CPU 资源就越少。
    • 有没有涉及到临时表
    • 排序

    关于扫描行数的确定

    计算索引的基数

    MySQL 在执行语句前,其实并不能准确的计算出扫描的行数,而是通过数学统计信息来估算记录数。这个统计信息被称为索引的“区分度”,在索引上不同的值越多,区分度就越高。在一个索引上不同值的个数,称为“基数”。基数越大,索引的区分度越好。

    mysql索引失效怎么办(MySQL选错索引的原因以及解决方案)

    这里的 Cardinality 就是索引的基数,但基数并不是完全准确的。MySQL 是在获取基数时,实际上是采用采样统计的方式。

    计算时,会选择 N 个数据页,并统计这些页面上的不同值,得到一个平均值,然后乘以该索引的页面数,然后得到的就是索引的基数。

    在 MySQL 中,有两种存储索引的方式,可通过设置 innodb_stats_persistent 来切换:

    • on 时:表示统计信息会持久化存储,默认 N 为 20,M 为 10.
    • off 时,统计信息仅会存储在内存中,默认 N 为 8,M 为 16.

    由于表中数据是不断变化的,所以当更新的值超过 1/M 时,会自动触发索引统计。

    但需要注意的是,由于是采样统计,所以基数的值不是准确的

    预估扫描行数的错误

    之前看到,执行 Select * from Y where a between 10000 and 20000 预估的行数是 100015,这个是能理解的,因为走的是全表扫描。

    之后执行 select * from Y force index(a) where a between 10000 and 20000 预估的行数是 37116,这个就不能理解了,理想的情况下应该是 10001 行 (需要遍历到 20001)。

    而且更奇怪的是,虽然 37116 行的预估行数不太合理,但也远小于全表扫描的 100015,为什么优化器还是选择全表扫描呢?

    首先先看第二个问题,选择 100015 的原因是因为如果使用索引 a 的话,除了需要在 a 索引扫描外,还需要回表,主键索引上的查询代价,优化器也需要算进去,所以选择了全表扫描。

    这时再看第一个问题,为什么没有得到正确的行数。这个就和一致性视图有关了,首先 Session A 中,开启了一致性视图,并没有提交。之后的 Session 清空了 Y 表后,又重新创建了相同的数据,这时每行数据都有两个版本,旧版本是 delete 前的数据,新版本是标记为删除的数据。所以索引 a 上的数据其实有两份。也就造成了行数的预估错误。

    mysql 是通过标记删除的方法来删除记录的,并不是在索引和数据文件中真正的删除。而且由于一致性读的保证,不能删除 delete 的空间,再加上 insert 的空间。导致统计信息有误。

    选用错误索引的解决办法

    对于行数预估错误的情况, 可采用如下的方法:

    如果遇到 EXPLAIN 和预估的行数,数值相差较大时,可以通过analyze table 来重新统计索引信息。

    mysql索引失效怎么办(MySQL选错索引的原因以及解决方案)

    直接通过 force index 强制指定需要使用的索引,不让优化器进行判断。但使用 force 也可能带来一些问题:

    • 迁移数据库时,语法不支持
    • 不容易变更并且不太方便,因为选错索引的情况一般不会经常发生,在生产环境出现问题后,才需要改代码,但还需要重新进行上线测试,部署。

    优化 SQL 语句,引导优化器使用正确的索引

    再看一个类似的例子:

    mysql索引失效怎么办(MySQL选错索引的原因以及解决方案)

    先来看一下这句

  • ?
  • 1
  • SQL select * from Y where a between 1 and 1000 and b between5000 100000 order by b limit 1;
  • 在执行这句话时,可以选索引 a,也可以选索引 b. 我们知道,每个索引对应了一颗B+树。这里由于取得是 a 和 b 的交集,如果选用索引 a 的话,需要遍历 1 - 10001 行。选用索引 b 需要遍历 50000 - 100001 行。理论上来说,应该选择 a 作为索引,可以优化器又偏偏选择了 b 作为索引。

    这里选择 b 作为索引的原因,是因为优化器看到了后面的 order by 语句,由于要排序,而 B+ 树本身就是有序的,省去了排序的过程,所以选择了 b 作为索引。

    但从实际的执行时间来看,索引 a 执行时间更短,所以这里 MySQL 又选择了错误的索引。

    我们可以将上述语句中 order by b limit 改为 order by b,a limit 1 这时由于 a,b 索引都要排序,扫描的行数就成为执行器主要参考的条件,引导选择正确的索引。

    这样做的前提一定要保证执行的逻辑结果是一致的,比如在 limit 1 的情况下,order by b,a order by b 的结果一致,如果换成 limit 100 就不一定了。

    mysql索引失效怎么办(MySQL选错索引的原因以及解决方案)

    还有一种改发

  • ?
  • 1
  • select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;
  • 现在可以看到,优化器选择了合适的索引。原因在于 limit 100 让优化器认为,使用索引 b 的代价较高,进而选择索引 a. 其实就是通过 limit 100 诱导优化器做出选择。

    调整索引

    能否找到更优,更合适的索引,或者利用索引的原则,删除一些不必要的索引。

    总结

    现在我们知道,MySQL 在选择索引时,是会出现错误的情况的。优化器选择索引的原则主要有三个,扫描的行数,是否存在临时表,以及排序。行数的扫描,主要和基数有关,而基数的统计则是通过统计抽样决定的,进而预估的行数可能会是不准确的。

    在遇到扫描的行数不正确时,可以通过 analyze table 来重新统计表的信息,通过 force index 强制指定索引,或通过手动改变 sql 的语义,诱导优化器做出正确的选择。

    以上就是MySQL选错索引的原因以及解决方案的详细内容,更多关于MySQL 索引的资料请关注开心学习网其它相关文章!

    原文链接:https://www.cnblogs.com/michael9/p/12929775.html

    标签:mysql 索引
    您可能感兴趣