文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL选错索引的原因是什么

2023-07-05 14:04

关注

本篇内容介绍了“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) DOinsert 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;

执行结果:

MySQL选错索引的原因是什么

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

我们再来执行如下操作:

MySQL选错索引的原因是什么

但是这个时候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选错索引的原因是什么

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

那为什么会选错索引呢?

2.优化器的逻辑

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

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

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

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

MySQL选错索引的原因是什么

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

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

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

MySQL选错索引的原因是什么

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

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

3.解决办法

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

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

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

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

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯