文章详情

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

请输入下面的图形验证码

提交验证

短信预约提醒成功

详解MySQL覆盖索引、索引下推

2023-09-10 10:40

关注

目录

1.覆盖索引

1.1.概述

1.2.聚集索引、非聚集索引

1.3.回表查询

1.4.覆盖索引

2.索引下推


1.1.概述

覆盖索引,是为了避免“回表查询”,从而降低查询耗时的一种使用索引的方法,所以要聊覆盖索引首先我们要知道什么是"回表查询,“回表查询”是因为MySQL的索引结构决定的,是因为非聚集索引要找聚集索引拿数据而出现的现象,所以我们又要先了解MySQL中的聚集索引和非聚集索引。

文章的脉络就是先聊聚集索引、非聚集索引是怎么带来了“回表查询”的问题,然后怎么用用覆盖索引解决这个问题。

1.2.聚集索引、非聚集索引

关于索引基础、B树、B+树等相关的详细内容可以看博主之前的两篇文章:

数据结构(8)树形结构——B树、B+树(含完整建树过程)_b+树构造过程__BugMan的博客-CSDN博客

详解MySQL索引__BugMan的博客-CSDN博客

众所周知mysql的索引有两种:

聚集索引:

聚集索引的叶子节点上挂着的是一条分支上的所有索引对应的数据。

 MySQL会默认为每张表维护一个聚集索引,如果表有主键,那么这个聚集索引就是主键索引,如果没有主键,MySQL仍然会为每个表维护一个隐藏的聚集索引。在innodb引擎中,这个隐藏的聚集索引是一个6字节长的ROWID,每个数据行都会有一个隐藏的ROWID。

非聚集索引:

非聚集索引叶子结点上挂的是索引值和对应值指向的聚集索引的值,也就是说真正的数据一定是要找聚集索引拿的。

1.3.回表查询

上面聊完非聚集索引一定会找聚集索引拿数据后,其实“回表查询”问题就已经引出来了,所谓的“回表查询”,指的是当我们使用非聚集索引查询时我们是无法直接得到数据,而需要通过叶子结点上的聚集索引的值去聚集索引的B+树里查找数据的过程。

“回表查询”带来的问题一目了然,索引是存在磁盘上的,查完一棵B+树后,还要去查第二棵B+树进行遍历匹配,查第二棵B+树就带来了额外的IO耗时,磁盘IO本来就是磁盘旋转+磁头上下摆动的机械动作,这个动作在计算机的运行体系里是个极度慢的动作,极度的耗时。

1.4.覆盖索引

覆盖索引,指的是select具体字段代替select *,将复合索引建立在要查询的具体字段上,这样在非聚集索引上,也就是第一颗B+树上就能拿到数据,从而不用“回表查询”。

做个实验:

我建了一个sys_user表,里面随即插入了100万条数据:

然后在username上建立一个索引:

create index index_username on sys_user(username);

未使用覆盖索引查询其中一条数据:

explain select * from sys_user where username='test_852107';

使用覆盖索引查询其中一条数据:

explain select username from sys_user where username='test_852107';

 

索引下推,是自MySQL5.6版本引入的一个新特性,目的也是减少“回表查询”,从而提升整体的查询效率。

假设表中有三个字段name、age、sex,

SQL写成这样是没问题的,完美满足左前缀原则:

select * from tuser where name ='zou' and age=10 and sex=1;

但如果SQL写成这样,就会出问题,索引断开了,sex这个索引值是没有用上的:

select * from tuser where name ='zou' and sex=1;

在5.6之前索引断了就不会往下走了,匹配到的所有name='zou'的数据,挨个“回表”,无疑这将会造成很多无意义的额外IO开销:

而索引下推的意思是,索引断裂、走不下去后,不会立即回表,还会向下推一步再继续比较其它索引字段,从而减少无意义的额外IO。以上表为例,就是会匹配到name='zou'后发现索引断掉了也不会立即回表,而是继续向下比对sex是不是等于1:

 

来源地址:https://blog.csdn.net/Joker_ZJN/article/details/130631391

阅读原文内容投诉

免责声明:

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

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

软考中级精品资料免费领

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

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

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

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

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

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

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