show index from table;
设置为 on 的时候,表示统计信息会持久化存储。持久化统计信息功能通过将统计信息存储到磁盘并使其在服务器重新启动期间保持不变来提高执行计划的稳定性,以便优化器更有可能每次为给定查询做出一致的选择。持久化更新统计信息时候索引页的取样页数,默认值是20
设置为 off 的时候,表示信息只存在内存当中。非持久化更新统计信息时候索引页的取样页数,默认值是8
CREATE TABLE `t` (
`id` int(11) 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 t (`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 t where a between 10000 and 20000;
commit;
(操作事务后 使用 强制使用索引 explain 情况 示意图)
Q:为什么操作完事务之后,在查询就不走索引了呢?
A:MySQL 没有执行该语句,没有走索引a,而是走了主键索引,进行全表扫描。原因是因为,MySQL优化器判断使用 ALL 全表扫描 优化 于 索引 a 。
判断流程是这样的:优化器估算 使用 索引 a 时,预计扫描行数为 三万多行,再加上 索引 a 是 二级索引,每个记录都需要回表检查是否满足条件,整体消耗高于使用全表扫描。这里出现的核心问题就是在于优化器估算错误预计扫描的行数(实际一万,估算三万)。
Q:为什么强制使用了索引为什么扫描行数是37116?
A:这里会出现这个情况,就是因为 mvcc 的原因。session A 开启了事务,但是并没有进行提交,所以之前的10万行数据是不能删除的。这样的话,之前的数据每一行都会有两个版本,旧版本是delete之前的数据,新版本是标记为删除后的数据。这样的话 索引 a 上就有两份索引数据。
说白一点:
这里看到的删除,只是标记为删除,因为另一个事务没有提交,数据仍然在数据页中,后插入的数据需要找到新的空位插入进去,这样查询时会扫描删除的数据 + 后插入的数据,同时算上回表扫描主键索引,扫描行数差不多是3万7千多行数据。
tips:
如果之前上的事务还没有进行提交,你即使使用 analyze table 也是没有办法进行重新优化索引信息的。
end
如果笔记有什么错误的地方或者哪里有问题的话,麻烦各位指点,给我留言就好。
最后,求关注。每天进步一点点,欢迎关注我的公众号「白砂」。
如果我的文章对你有所帮助,还请帮忙点赞、在看、转发一下,非常感谢!
点个在看你最好看