前言
案例取自极客时间《mysql45讲》
案例
//建表
CREATE TABLE `t` (
`id` int(11) AUTO_INCREMENT NOT NULL,
`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();
模拟执行器分析查询语句
explain select * from t where a between 20000 and 30000
//结果为选择key=a且 rows = 10001
// 即使用a索引，扫描了1万行数据
场景复现
//sessionA开启会话不提交
start transaction with consistent snapshot;
//sessionB删除数据且用存储过程重新插入
delete from t;
call idata();
//sessionB执行查询分析（sessionA并未提交）
explain select * from t where a between 20000 and 30000
//结果为选择key=null且 rows = 104157
奇了怪了，此时没用索引，进行了全表扫描
//sessionB强制使用索引试一下（sessionA并未提交）
explain select * from t force index(a) where a between 20000 and 30000
//结果为选择key=a 且 rows = 37116
虽然使用了索引，但是还是扫描了37116行，不妨结合之前的知识分析一下：
1.另一个事务未提交，需要保存之前的数据的数据版本，因此delete10万行数据实际是标记数据，这样每一行数据就有两个数据版本，旧的是delete之前的，新的是标记为delete的，索引a上的数据有两份
2.那还多出来的1万7呢，之前介绍过索引树的叶子节点存的是主键，select * 还要进行回表查询，这里将回表的扫描行数一并算上
为什么会选错索引
选择索引是优化器的工作，优化器要找到最优的执行方案并选择最小的代价去执行，扫描行数是影响执行代价之一（扫描越小，访问磁盘次数越少，消耗CPU资源越少）
mysql执行语句之前需要通过根据信息来统计记录数
这个统计信息就是索引的区分度，即索引上不同的值越多，区分度越高越好（show index t 的 cardinality字段查看），索引的区分度是利用采样统计得到的即取小部分统计信息再乘以整体。
除了使用统计信息，还会计算回表代价(主键不需要回表)
如果是统计信息不对那就修正
analyze table t
explain select * from t where a between 20000 and 30000
//sessionA提交
commit
另一种场景复现
explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
按理说这是个空集，利用索引a只扫描1000行，利用索引b要扫描50000行，这里优化器竟然选择了索引b！！
mysql又选错了索引
解决办法
//强制使用索引a，ok结果与预期相符
explain select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 10000)00000)
2.引导使用a索引
改为 order by a,b limit 1;
我们知道索引树上的数据是有序的，优化器使用b索引，一方面是认为索引b可以避免排序 ,order by a,b强制按照a,b排序意味着两个都需要排序，因此扫描行数成了影响决策的主要条件
3.删掉索引b
小结
解决mysql选错索引主要有两大方向
1.强制指定索引
2.干涉优化器选择（比如增大limit数量，增加order by ，写成子查询）
网友评论