文章目录
- 10|MySQL为什么有时候会选错索引?
- 优化器的逻辑
- 索引选择异常和处理
10|MySQL为什么有时候会选错索引?
在MySQL中,一张表是支持多个索引的,但是,写sql语句的时候,并没有主动指定使用哪一个索引,也就是说,索引的使用,是由MySQL来指定的
那么可能会有这种情况,本来一条可以执行的很快的语句,但是由于MySQL选错了索引,导致执行速度变的很慢
例:
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), (3, 3, 3)…(100000, 100000, 10000)
这里采用存储过程:
delimiter ;;
create procedure idate()
begin
declare i int;
set i = 1;
while (i <= 100000) do
insert into t value(i, i, i);
set i = i + 1;
end while;
end;;
delimiter;
call idate();
那么当我们查询的时候:
select * from t where a between 10000 and 20000
你肯定会觉得显而易见,a上有索引,因此使用a的索引进行查询,事实也的确如此,可以使用explain命令查看这条语句执行的情况
那么,再执行如下操作:
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; |
session A的操作是开启一个事务,随后sessionB把数据都删除,又调用存储过程,插入了10万行数据
这时候,select B的查询语句select * from t where a between 10000 and 20000就不会选择索引a了,很奇怪,对吧?
优化器的逻辑
选择索引是优化器的工作,而优化器选择索引的目的是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库中,扫描行数是影响执行的因素之一,扫描的行数越少,意味着IO次数越少,消耗的cpu资源越少
当然,扫描行数并不是唯一的标准,优化器还会结合是否使用临时表,是否排序等因素进行综合判断
上面的例子显然没有涉及到临时表,所以MySQL肯定是判断错扫描行数了
那么,问题在于,优化器是如何判断扫描行数的?
MySQL在真正执行语句之前,并不能精确的知道这个条件的记录有多少条,而只能根据统计信息来估算行数,这个统计信息就是索引的区分度
,我们称之为基数
,也就是说,基数越大,索引的区分度越好
可以使用show index看到一个索引的基数,但是MySQL是如何得到索引的基数呢?
MySQL是采样统计的方法,为什么要采样统计呢?因为把整章表取出来一行行统计,虽然可以得到精确的结果,但是代价太大了,所以只能选择采样统计。
采样统计的时候,InnoDB默认会选择N个数据页,统计这些页上的不同值,得到一个平均值,然后乘这个索引的页面数,就得到了索引的基数
而数据表是持续更新的,统计索引信息页不会固定不变,所以变更的数据行数超过1/ M的时候,就会自动触发重新做一次索引统计
在MySQL中,有两种存储索引统计方式,可以设置参数innodb_stats_persisiten的值来选择
- 为on的时候,表示统计信息会持久化存储,这时,默认的N是20,M是10
- 为off的时候,表示统计信息只存储在内存中,这时,默认的N是8,M是16
由于是采样统计,不管N是20还是8,这个基数都是很容易不准了
但,这还不是全部,选错索引还有别的原因
其实索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断,执行这个语句本身要扫描多少行
如果没有指明使用索引a,那么会明显减少需要扫描的行数,但是为啥不使用a呢?因为优化器还会把回到主键上查出整行数据算进去,也就是说,优化器任何回表的代价更大
那么有了答案,只需要使用命令:analyze命令就可以了
再看另一个例子:
select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
从条件上看,这个查询没有符合的,因此会返回空集
在执行之前,如果让你来选择索引,你会怎么选择?
如果是索引a,那么就是扫描索引a的前1000个值,然后取到对应的id,再回表,然后根据字段b来过滤,显然要扫描1000行
如果使用索引b进行查询,那么就是扫描索引b的最后50001的值,与上面的执行过程相同,也是需要回到主键索引上取值判断,所以需要扫描50001行,也就是说,使用a更好一些
但是如果你实际执行代码,会发现,优化器估算的行数失效了,并且又选错了索引
索引选择异常和处理
解决办法是:采用force index,强行指定一个索引,MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项,然后在候选列表中依次判断每个索引需要扫描多少行。如果force index指定的索引在候选索引列表中,那么就直接使用这个所i你,不再评估其他索引的代价
方法二就是:修改语句,引导MySQL使用我们期望的索引,比如说上面的sql语句的order by b limit 1
改成order by b, a limit 1
,逻辑是相同的
那么之前优化器选择索引b,因为b可以避免排序,(b本身是索引,已经是有序的了,如果选择b则不再需要排序,只需要遍历即可)。所以即使扫描行数多,也会被判定为代价小
现在的写法是按照b, a排序就意味着两个索引都需要排序,因此,扫描行数成了影响决策的主要条件,于是优化器就选择了索引a
方法三:新建一个更适合的索引,来提供给优化器,或者删除掉误用的索引