优化器放弃索引mysql_MySQL选错索引的原因以及解决方案

最新推荐文章于 2023-06-02 18:01:01 发布

未眠-1031

最新推荐文章于 2023-06-02 18:01:01 发布

阅读量235

点赞数 1

文章标签：优化器放弃索引mysql

本文链接：https://blog.csdn.net/weixin_35395422/article/details/113141995

版权

本文分析了MySQL优化器在选择索引时可能出现错误的原因，包括采样统计导致的基数预估不准确和一致性视图影响。提出了解决方案，如使用ANALYZE TABLE重新统计信息、FORCE INDEX强制指定索引、优化SQL语句或调整索引设计。通过实例展示了如何引导优化器选择正确的索引。

摘要由CSDN通过智能技术生成

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

先看这样一个例子：

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

MySQL: 5.7.27, 隔离级别: RR

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；

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 的顺序执行，发现索引的选择如下：

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

set long_query_time=0;

--将慢查询日志打开，并将阙值设为 0. 在记录的日志中，可以发现 MySQL 并没有选择 a 所在的索引，同时花费了更长的时间。

这样看，MySQL 的优化器不一定每次都能选择合适的索引。想要理解出现该现象的原因，就要从优化器的选择逻辑说起。

优化器

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

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

扫描的行数：扫描的行数越少，就证明访问磁盘数据的次数越少，消耗的 CPU 资源就越少。

有没有涉及到临时表

排序

关于扫描行数的确定

计算索引的基数

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 来重新统计索引信息。

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

迁移数据库时，语法不支持

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

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

再看一个类似的例子：

先来看一下这句

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 就不一定了。