MySQL选错索引的原因是什么

本篇内容介绍了“MySQL选错索引的原因是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1.引例

首先创建一张表，并对字段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;

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

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

那为什么会选错索引呢？

2.优化器的逻辑

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

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

那扫描行数是如何判断的？
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上拿到一个值，都要回表查询。而如果选择扫描十万行的语句，则不需要回表。因此优化器评估这两条语句时，觉得回表查询更耗费时间，所以没有使用索引。但是实际中，这种方式并不是最优的。

3.解决办法

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

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

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

“MySQL选错索引的原因是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！