菜鸟笔记
提升您的技术认知

mysql为什么会选错索引?-ag真人游戏

首先创建一张表,并对字段a,b分别建立索引:

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;

然后往表中,插入十万行数据,值按整数递增:(1,1,1)、(2,2,2)、(3,3,3)…

delimiter ;;
create procedure insertdata()
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 insertdata();

接下来,我们执行一条sql:

mysql >explain select * from t where a between 10000 and 20000;

执行结果:

结果中的“key”字段就代表了查询中使用的索引。所以这条语句走了索引a,没什么问题。

我们再来执行如下操作:

但是这个时候session b的查询语句select * from t where a between 10000 and 20000就不会再选择索引a。

为了比较使用索引和不使用的查询性能对比,执行下面的语句:

set long_query_time=0;
select * from t where a between 10000 and 20000;
select * from t force(a) where a between 10000 and 20000;

下面是两种慢查询日志中的结果对比:

第一个查询查找了十万行,第二个查询走了索引,查找了一万行,速度明显比较快。

那为什么会选错索引呢?

选择索引是优化器的工作,优化器选择索引的目的,就是想要找到一个最优的执行方案,并用最小的代价去执行。

在数据库里面,扫描行数是影响执行代价的因素之一。扫描行数越少,意味着访问磁盘次数越少。但是扫描行数并不是唯一的评价标准,还会考虑临时表,是否排序等因素。

那扫描行数是如何判断的?
mysql在真正执行之前,只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。 一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。

我们可以用show index的方法看到不同索引的基数值,但是可以看到统计信息并不是太准确。 可以使用analyze table t来重新统计,但是也不一定准确。

那mysql是如何得到索引的基数呢?
答案是mysql会采取采样统计的方法,默认会选择n个数据页,统计这些页面上的不同值,得到平均值,再乘以总的页面数。

在mysql中,有两种存储索引统计的方式,可以通过设置innodb_stats_persisten来设置:

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

我们再来比较两个语句预估的查询行数,如下图:

图中的row字段就代表预估的查询行数。对于第一条语句,预估的查询行数是104620.第二条语句,预估的查询行数是37116。明显第二条语句的查询行数少,那为什么没有选择索引a呢?

这是因为,如果使用索引a,每次从索引a上拿到一个值,都要回表查询。而如果选择扫描十万行的语句,则不需要回表。因此优化器评估这两条语句时,觉得回表查询更耗费时间,所以没有使用索引。但是实际中,这种方式并不是最优的。

第一种解决办法是和第二条语句一样,采用force index强行选择一个索引。如果force index指定的索引在候选索引列表中,就直接选择这个索引,而不再去评估执行代价。但是这种方式不太优雅,而且改了索引名,语句也要改

第二种解决办法是考虑修改sql语句,引导mysql使用我们期望的索引

第三种解决办法是新建一个更合适的索引,删除掉误用的索引

网站地图