我的编程空间,编程开发者的网络收藏夹
学习永远不晚

Mysql索引覆盖的实现

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

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

Mysql索引覆盖的实现

1.什么是覆盖索引

通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但是mysql也可以从索引中直接获取数据,这样就不在需要读数据行了。 覆盖索引(covering index) 指一个查询语句的执行只需要从辅助索引中就可以得到查询记录,而不需要回表,去查询聚集索引中的记录。可以称之为实现了索引覆盖。 在mysql数据库中,如何看出一个sql是否实现了索引覆盖呢?

Mysql索引覆盖的实现

从执行计划看,Extra的信息为using index ,即用到了索引覆盖。

2.覆盖索引为什么快

innodb存储引擎底层实现包括B+树索引和哈希索引,innodb存储引擎默认的索引模型/结构是B+树,所以大部分时候我们使用的都是B+树索引,因为它良好的性能和特性更适合于构建高并发系统。根据索引的存储方式来划分,索引可以分为聚簇索引和非聚簇索引。聚簇索引的特点是叶子节点包含了完整的记录行,而非聚簇索引的叶子节点只有索引字段和主键ID。非聚簇索引中因为不含有完整的数据信息,查找完整的数据记录需要回表,所以一次查询操作实际上要做两次索引查询。而如果所有的索引查询都要经过两次才能查到,那么肯定会引起效率下降,毕竟能少查一次就少查一次。

覆盖索引就实现了从非聚簇索引中直接获取数据,所以效率会提升。

Mysql索引覆盖的实现

3.SQL优化场景

(1)无where条件

请看下面的sql

Mysql索引覆盖的实现

Mysql索引覆盖的实现

执行计划中,type为ALL,代表进行了全表扫描,扫描行数达到了26274308,所以执行时间为9.25秒,也是正常的。

那么如何优化?优化措施很简单,就是对查询列建立索引。如下,

alter table instance_space_history add index idx_org1(org1); 看添加索引后的执行计划

Mysql索引覆盖的实现

Possible_keys为null,说明没有where条件时优化器无法通过索引检索数据;

但是看extra的信息 Using index,即从索引中获取数据,减少了读取的数据块的数量 。

Mysql索引覆盖的实现

在看实际优化效果,扫描行数没变,但是使用了覆盖索引,查询时间从9.25秒缩短到5.67秒。 思考: 无where条件的查询,可以通过索引来实现索引覆盖查询。但前提条件是,查询返回的字段数足够少,更不用说select *之类的了。毕竟,建立key length过长的索引,始终不是一件好事情。

(2)where条件区分度低

使用区分度极低的字段作为where条件的查询SQL,对于dba或者研发人员优化一直是比较头疼的问题,这里介绍一种思路,就是通过索引覆盖来优化 。 t_material_image是一张8亿多数据的大表,where条件的material_type字段区分度很低,下面是没加任何索引的执行计划和查询时间(7.35秒)。

Mysql索引覆盖的实现

Mysql索引覆盖的实现

最容易想到的优化方式,就是给where条件的字段加索引,添加索引语句如下: alter table t_material_image add index idx_material_type (material_type);

再来看执行计划

Mysql索引覆盖的实现

通过执行计划和测试结果看,的确是有效果的,但是走索引后的查询效率依然不能满足我们期望。 然后试着给material_type,material_id添加联合索引。 alter table t_material_image add index idx_material_id_type (material_type,material_id);

Mysql索引覆盖的实现

Mysql索引覆盖的实现

从这个sql的执行计划看,出现Using index,实现了索引覆盖;再看执行时间,性能得到了巨大的提升,居然已经可以跑到0.85s左右了。

思考:

当where条件字段区分度低(过滤性差),且where条件与查询字段总数较少的情况下,使用索引覆盖优化,是个不错的选择。

(3)查询仅选择主键

对于Innodb的辅助索引,它的叶子节点存储的是索引值和指向主键索引的位置,然后需要通过主键在查询表的字段值,所以辅助索引存储了主键的值。如果查询所选择的列只有主键,应该考虑通过索引覆盖优化。 看下面的两个sql,字段 pin 和completion_time有联合索引,where条件差别只有comment_voucher_status = 0,但是执行时间差距巨大(第一个sql0.58s,第二个sql0.2s),为什么呢?是不是很困惑

Mysql索引覆盖的实现

Mysql索引覆盖的实现

Mysql索引覆盖的实现

Mysql索引覆盖的实现

我们来看执行计划,主要差别体现在extra,第一个sql用到Using index condition,而第二个sql用到Using index,因为pin和completion_time有联合索引,而且查询结果只选择了主键id,所以第二个sql覆盖了所有的where条件字段和查询结果选择字段,故实现了索引覆盖。 思考:

当查询字段只有主键时,更容易实现索引覆盖,因为索引只要覆盖where条件,就可以实现索引覆盖。

4.总结与建议

索引的核心作用: (1)通过索引检索仅需要数据 (2)从索引中直接获取查询结果
索引覆盖的条件: (1)Select查询的返回列包含在索引列中 (2)有where条件时,where条件中要包含索引列或复合索引的前导列 (3)查询结果的总字段长度可以接受

到此这篇关于Mysql索引覆盖的实现的文章就介绍到这了,更多相关Mysql索引覆盖内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

免责声明:

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

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

Mysql索引覆盖的实现

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

Mysql索引覆盖的实现

目录1.什么是覆盖索引2.覆盖索引为什么快3.SQL优化场景(1)无where条件(2)where条件区分度低(3)查询仅选择主键4.总结与建议1.什么是覆盖索引通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个
2023-03-03

Mysql索引覆盖如何实现

这篇“Mysql索引覆盖如何实现”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“Mysql索引覆盖如何实现”文章吧。1.什么是
2023-07-05

MySQL 覆盖索引的优点

一个通常的建议是为WHERE条件创建索引,但这其实是片面的。索引应当为全部查询设计,而不仅仅是WHERE条件。索引确实能有效地查找数据行,但MySQL也能够使用索引获取列数据,这样根本不需要去读取一行数据。毕竟,索引的叶子节点包含了索引对应
2022-05-30

MySQL 回表,覆盖索引,索引下推

目录回表覆盖索引索引下推无索引下推: 查看索引下推的状态有索引下推: 开启索引下推回表在研究mysql二级索引的时候,发现Mysql回表这个操作,往下研究了一下字面意思,找到索引,回到表中找数据解释一下就是:先通过索引扫描出数据所在
2022-07-11

mysql 14 覆盖索引+回表

覆盖索引概念:    MySQL可以利用索引返回select列表中的字段值(就是索引值)。而不必根据主键再次读取聚簇索引数据文件查到数据,也就是平时所说的不需要回表操作。覆盖索引其实是索引覆盖的意思,索引字段就已经囊括select查询的字段,即索引字段覆盖了需
mysql 14 覆盖索引+回表
2020-11-10

mysql覆盖索引如何创建

要创建一个覆盖索引,在创建索引时需要同时包含需要查询的列和查询的列。例如,假设我们有一个表包含列A、B、C,我们想要查询A和B列,那么我们可以创建一个包含A和B列的索引,这样就可以实现覆盖索引。具体创建方法如下:CREATE INDEX
mysql覆盖索引如何创建
2024-03-15

Mysql覆盖索引如何使用

在MySQL中,覆盖索引是指索引包含了查询需要的所有字段,这样MySQL可以直接使用索引来返回查询结果,而不需要再去访问数据表。这可以提高查询性能,减少IO操作。要使用覆盖索引,需要创建一个包含查询需要的所有字段的索引。然后在查询时,只选
Mysql覆盖索引如何使用
2024-04-09

mysql覆盖索引的优点是什么

这篇文章主要介绍了mysql覆盖索引的优点是什么,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。1、优点说明(1)索引项通常比记录小,因此MySQL访问数据少。(2)索引都是按
2023-06-15

mysql覆盖索引是什么意思

这篇文章主要介绍mysql覆盖索引是什么意思,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!1、查询语句中所需的列在索引中,这样查询结果就可以在索引的数据结构中找到。2、由于覆盖索引可以减少树木的搜索次数,显著提高查询
2023-06-25

MySQL中复合索引和覆盖索引的区别详解

目录前言准备复合索引覆盖索引总结前言准备我们先准备一张表和几个字段,方便介绍覆盖索引和复合索引。创建一个user表,表中有id、name、school、age字段。字段名字段类型idintnamevarcharschoolvarcha
MySQL中复合索引和覆盖索引的区别详解
2023-11-23

mysql覆盖索引高性能的示例分析

这篇文章主要介绍mysql覆盖索引高性能的示例分析,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!1、高性能的原因索引通常比记录要小,覆盖索引查询只需要读索引,而不需要读记录。索引都按照值的大小进行顺序存储,相比与随机
2023-06-15

编程热搜

目录