mysql 为什么会选错索引
学习检测
-
mysql如何判断一个查询的扫描行数?流程?
-
索引基数如何计算? 通过哪个参数可以设置索引统计的存储方式?
-
可以重新统计索引信息的命令是什么?
-
扫描采样记录的存储方式?
-
如何定位索引选择异常这样的问题?
-
索引选择异常的问题可以有哪几种处理方式?
答案总结
-
通过采样统计的方式,统计可能扫描的行数
-
取N个数据页,找出数据中不同的值的平均值,乘以数据页数,就为要扫描行的约数 M为数据行的变化数
-
analyze table tbl_name;
-
扫描采样的结果在5.7以后默认持久化存储,默认值是N 10 M20,存在内存中 N–8 M–16
-
查看执行计划
-
重新索引采样
诱导优化器使用最优结果
强制使用最优索引
案例
建表
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;
****插入10万数据,取值按整数递增(1,1,1),(2,2,2)...****
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();
执行sql语句
mysql> select * from t where a between 10000 and 20000;
查看执行计划
从图中刚看出,用到了这条”a”的索引
修改数据
这里,session A 的操作你已经很熟悉了,它就是开启了一个事务。随后,session B 把数据都删除后,又调用了 idata 这个存储过程,插入了 10 万行数据。
这时候,session B 的查询语句 select * from t where a between 10000 and 20000 就不会再选择索引 a 了。我们可以通过慢查询日志(slow log)来查看一下具体的执行情况。
set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
-
第一句,是将慢查询日志的法制设置为0,标识这个线程接下来的语句都会被记录到慢查询日志
-
第二句,Q1是sessionB原来的查询
-
第三句,Q2是加了force index(a)来和sessionB原来的查询语句执行情况做对比
可以看到,Q1 扫描了 10 万行,显然是走了全表扫描,执行时间是 40 毫秒。Q2 扫描了 10001 行,执行了 21 毫秒。也就是说,我们在没有使用 force index 的时候,MySQL 用错了索引,导致了更长的执行时间。
这个例子对应的是我们平常不断地删除历史数据和新增数据的场景。这时,MySQL 竟然会选错索引,是不是有点奇怪呢?今天,我们就从这个奇怪的结果说起吧。
优化器的逻辑
而优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。
扫描行数的计算
MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数。
这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。
采样统计
为什么要采样统计呢?
因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择“采样统计”。
-
采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。
-
而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数**超过 1/M**的时候,会自动触发重新做一次索引统计。
采样统计方式
参数 innodb_stats_persisten
innodb_stats_persistent(
-
非持久化统计信息开关),MySQL 5.7中默认为打开,持久化存储统计信息
-
该选项设置为ON时候,统计信息会持久化存储到磁盘中,而不是存在在内存中
-
相反,如果是非持久化存储的(存在内存中),相应的统计信息会随着服务器的关闭而丢失。
innodb_stats_persistent_sample_pages
-
持久化更新统计信息时候索引页的取样页数)
-
默认是20个page,如果设置的过高,那么在更新统计信息的时候,会增加ANALYZE TABLE的执行时间。
innodb_stats_transient_sample_pages
-
临时性更新统计信息时候索引页的取样页数
-
默认值是8,innodb_stats_persistent设置为disable的情况下innodb_stats_transient_sample_pages才生效,
也就是非持久化存储过索引统计信息的时候,innodb_stats_transient_sample_pages为更新统计信息的采样页数
innodb_stats_persistent
设置为on的时候,表示统计信息会被持久化存储,这时候默认值N(N个数据也)是20,M(表中数据行变化数)是10
设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。
其实索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断,执行这个语句本身要扫描多少行。接下来,我们再一起看看优化器预估的,这两个语句的扫描行数是多少。
rows 这个字段表示的是预计扫描行数。
其中,Q1 的结果还是符合预期的,rows 的值是 104620;但是 Q2 的 rows 值是 37116,偏差就大了。
而图 1 中我们用 explain 命令看到的 rows 是只有 10001 行,是这个偏差误导了优化器的判断。到这里,可能你的第一个疑问不是为什么不准,而是优化器为什么放着扫描 37000 行的执行计划不用,却选择了扫描行数是 100000 的执行计划呢?
这是因为,如果使用索引 a,每次从索引 a 上拿到一个值,都要回到主键索引上查出整行数据,这个代价优化器也要算进去的。而如果选择扫描 10 万行,是直接在主键索引上扫描的,没有额外的代价。
修改统计信息
既然是统计信息不对,那就修正。analyze table t 命令,可以用来重新统计索引信息。我们来看一下执行效果。
analyze table t
修改使用索引方法
采用 force index 强行选择一个索引
select * from t force index(a) where a between 10000 and 20000;
修改语句,导致mysql期望使用我们的索引