文章详情

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

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL回表查询与索引覆盖的区别是什么

2023-07-05 13:47

关注

这篇文章主要介绍“MySQL回表查询与索引覆盖的区别是什么”,在日常操作中,相信很多人在MySQL回表查询与索引覆盖的区别是什么问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”MySQL回表查询与索引覆盖的区别是什么”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!

回表查询

InnoDB索引分为两大类,一类是聚集索引(Clustered Index),一类是非聚集索引(Secondary Index)

聚集索引(聚簇索引):叶子节点中存的是整行数据,找到索引也就找到了数据,索引即数据,表中行的物理顺序与键值的逻辑(索引)顺序相同,一个表只能包含一个聚集索引。因为索引(目录)只能按照一种方法进行排序。

非聚集索引(普通索引、非聚簇索引、二级索引):非聚集索引的btree叶子节点中存储的是当行数据的PK(主键)。例如MYISAM通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,这也就是为什么索引不在key buffer命中时,速度慢的原因。

为什么非主键索引结构叶子结点存储的是主键值?

减少了出现行移动或者数据页分裂时二级索引的维护工作(当数据需要更新的时候,二级索引不需要修改,只需要修改聚簇索引,一个表只能有一个聚簇索引,其他的都是二级索引,这样只需要修改聚簇索引就可以了,不需要重新构建二级索引)

在使用非聚集索引时,为了取到具体数据,则需要通过PK回到聚集索引里去查询数据。着就叫回表查询。扫描了2次索引树。所以效率相对较低。

索引覆盖

索引覆盖就是解决回表查询的一种方案。见名知意,就是查询的所有列均被所使用的索引列覆盖(可以是单列索引也可以是联合索引,通常是联合索引,单列索引很难覆盖查询的所有列)。

因为索引中已经包含了要查询的字段的值,因此查询的时候直接返回索引中的字段值就可以了,不需要再到表中查询,避免了对主键索引的二次查询,也就提高了查询的效率。

id为聚集索引,name为非聚集索引:

select name, age from t where name = 'lcc';

就需要回表查询

索引覆盖:

在SQL中只查询name字段。这样name的索引就覆盖到了所有的查询列。

select name  from t where name = 'lcc';

将name的索引修改为联合索引(name, age ),之后还是执行select name, age from t where name = 'lcc'。这样也覆盖到了所有的查询列。
因为覆盖索引必须要存储索引的列值,而哈希索引、空间索引和全文索引等都不存储索引列值,从而只有使用B-Tree索引的数据可以做覆盖索引。

进行索引覆盖查询时,在explain(执行计划)的Extra列可以看到【Using Index】的信息。

索引覆盖的优点

哪些场景适合使用索引覆盖来优化SQL

哪些情况下不要建索引

索引下推

索引下推优化是 MySQL 5.6 引入的, 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数

建立联合索引:

KEY `username` (`name`,`age`) )

执行:

select * from user2 where name like 'j%' and age=99;

上面的查询sql符合索引的最左前缀原则,所以将会用到 username 索引

5中上面这个 SQL 的执行流程是这样的:

 5.6中上面这个 SQL 的执行流程是这样的:

到此,关于“MySQL回表查询与索引覆盖的区别是什么”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注编程网网站,小编会继续努力为大家带来更多实用的文章!

阅读原文内容投诉

免责声明:

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

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

软考中级精品资料免费领

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

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

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

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

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

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

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