mysql 为什么会选错索引

前言

在写 sql 语句时,如果你没有指定使用哪个索引,那么索引是由mysql来决定的。
在一次开发中,我遇到了一个问题,sql 语句并没有执行我新建立的索引,而是使用了以前建立的索引,导致查询的效率很低。
那为什么mysql会为我们的sql选错索引呢?

我建立一个表测试一下:

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;

分别在 a, b 字段上建立索引。

使用存储过程插入数据:

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();
开始分析
explain select * from t where a between 10000 and 20000;

在这里插入图片描述
可以看到起使用 a 字段的索引.
但是在平常的不断的删除或者新增数据的场景,mysql 就可能选错索引。

优化器逻辑

在之前的mysql 的基础架构中提到 mysql 的索引是由优化器来进行选择的。
而优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。

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

扫描行数是怎么判断
mysql 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数。

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

SHOW INDEX FROM t;

在这里插入图片描述

mysql 是怎样得到索引的基数

采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。
而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。

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

show variables like 'innodb_stats_persistent';

在这里插入图片描述

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

索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断,执行这个语句本身要扫描多少行。

在这里插入图片描述
可以上面的查询 扫描了 10001 行。

如果统计信息不对,我们可以使用

ANALYZE TABLE t;

重新统计。

但是优化器不只看扫描行数,看下这个 sql

explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

在这里插入图片描述
但是这条语句明显先去查询 索引 a 的效率会更高。

索引选择异常和处理

  1. 使用 force index ,但是这样代码写起来不优雅
  2. 修改 sql 语句
explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;

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

或者

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;
  1. 删除误用的索引,新建合适的索引
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值