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

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

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

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

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

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

优化器

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

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

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

关于扫描行数的确定

计算索引的基数

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,为什么优化器还是选择全表扫描呢?

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

扫一扫手机访问