头图

好的索引当然是要覆盖了!

如果你的查询中用到了索引,这是一个进步,如果能够更进一步,用到了覆盖索引,那么就更牛了!当我们设计一个索引的时候,如果能够从一个更加全面的角度去设计这个索引,不仅考虑到 where 中的条件,还能够考虑到整个 SQL,那么无疑这个索引的设计将是非常成功的。

当然不能为了覆盖而覆盖。

1. 什么是覆盖索引

要理解什么是覆盖索引,我们需要先来回顾一下 InnoDB 中索引树的数据结构。

假设我有如下数据:

id(主键)usernameageaddressgender
1ab99深圳
2bw95天津
3cx93深圳
4bc80上海
5bg85重庆
6ac98广州
7bw99海口
8ck90深圳
9cc92武汉
10af88北京

现在我给 username、age 以及 address 三个字段建立一个联合索引,那么这个联合索引的 B+Tree 可能是这个样子:

上面这个索引树是一个非聚集索引或者也可以说是一个二级索引,这种索引区别于我们之前文章跟大家聊的聚集索引(再聊 MySQL 聚簇索引),在聚集索引中,叶子结点就是这一行的数据,但是在二级索引中,叶子结点中保存的是主键值。

所以,当我们搜索的时候,如果使用的是二级索引,那么最终拿到的是主键值,有了主键值之后,我们还需要再去到聚簇索引中进行搜索,才能拿到完整的数据,这个过程我们也称之为回表。

很明显,如果进行了回表操作的话,那么执行效率显然就要下降一截,那么是否用到了二级索引就会回表呢?其实不然!如果是覆盖索引的话,就不需要回表。

那么什么是覆盖索引呢?

小伙伴们观察上面的索引树,大家发现在这个索引树中,离叶子结点最近的树枝上有 username、age 以及 address,而叶子结点上有 id,所以如果我想要查询的字段是 id、username、age 以及 address 中的任意一个或者任意几个的话,那么就不需要再去聚簇索引上查询了,当前这个 B+Tree 上直接就有现成的,直接返回即可,这个就是覆盖索引。

2. 实践

现在假设我有如下一张表:

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_prop_index` (`username`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

可以看到,这张表中有一个名为 user_prop_index 的索引,这个索引中一共存在三个字段,分别是 username、age 以及 address,现在我们来看如下 SQL 的执行计划:

explain select address from user where username='ab' and age=99\G

小伙伴们看到,Extra: Using index 就表示使用到了覆盖索引,因为我的查询 SQL 中最终想要的值,都在当前这棵索引树上。

更进一步,假设我要查询 id、address 以及 age 字段,如下:

explain select id,address,age from user where username='ab'\G

很明显,由于这三个字段都在索引树上,所以直接直接通过回表获取到。

但是,如果想直接 select *,那么由于这个索引树上没有 gender 字段,此时就必须要回表才能拿到 gender 字段的值,如下:

explain select * from user where username='ab'\G

可以看到,这个时候没有用到覆盖索引了。

3. 覆盖索引的优势

通过前面的介绍,覆盖索引的优势相信小伙伴们也能自己总结出来:

  1. 覆盖索引不需要回表,直接在 B+Tree 这颗索引树上就能读取到需要的数据,这极大的减少了数据库 IO 次数,在 IO 密集型应用中,这样的性能提升非常有效。
  2. 基于 B+Tree 中联合索引数据的排序规则,覆盖索引中,如果涉及到范围搜索,也是非常高效的(如果涉及到回表的话,效率就会降低很多)。

《Spring Boot+Vue全栈开发实战》作者

8.4k 声望
7.2k 粉丝
0 条评论
推荐阅读
Spring Boot 启动注解分析
@[toc]虽然我们在日常开发中,Spring Boot 使用非常多,算是目前 Java 开发领域一个标配了,但是小伙伴们仔细想想自己的面试经历,和 Spring Boot 相关的面试题都有哪些?个人感觉应该是比较少的,Spring Boot 本...

江南一点雨阅读 154

封面图
终于卷完了!Redis 打怪升级进阶成神之路(2023 最新版)!
是一种非关系型数据库服务,它能解决常规数据库的并发能力,比如传统的数据库的IO与性能的瓶颈,同样它是关系型数据库的一个补充,有着比较好的高效率与高性能。专注于key-value查询的redis、memcached、ttserver。

民工哥11阅读 989

封面图
算法可视化:一文弄懂 10 大排序算法
在本文中,我们将通过动图可视化加文字的形式,循序渐进全面介绍不同类型的算法及其用途(包括原理、优缺点及使用场景)并提供 Python 和 JavaScript 两种语言的示例代码。除此之外,每个算法都会附有一些技术说...

破晓L7阅读 945

封面图
硬卷完了!MongoDB 打怪升级进阶成神之路( 2023 最新版 )!
前面我们学习:MySQL 打怪升级进阶成神之路、Redis 打怪升级进阶成神之路,然后我们还在继续 NoSQL 的卷王之路。从第一篇文章开始,我们逐步详细介绍了 MogoDB 基础概念、安装和最基本的CURD操作、索引和聚合、工...

民工哥6阅读 553

封面图
「刷起来」Go必看的进阶面试题详解
逃逸分析是Go语言中的一项重要优化技术,可以帮助程序减少内存分配和垃圾回收的开销,从而提高程序的性能。下面是一道涉及逃逸分析的面试题及其详解。

王中阳Go4阅读 1.9k评论 1

封面图
架构设计-高性能篇
大家好,我是易安!今天我们谈一谈架构设计中的高性能架构涉及到的底层思想。本文分为缓存架构,单服务器高性能模型,集群下的高性能模型三个部分,内容很干,希望你仔细阅读。

架构狂人4阅读 795

Nginx常用配置及和基本功能讲解 | 京东云技术团队
作者:京东物流 殷世杰Nginx已经广泛应用于J-one和Jdos的环境部署上,本文对Nginx的常用的配置和基本功能进行讲解,适合Nginx入门学习。1 核心配置找到Nginx安装目录下的conf目录下nginx.conf文件,Nginx的基本功...

京东云开发者6阅读 606

封面图

《Spring Boot+Vue全栈开发实战》作者

8.4k 声望
7.2k 粉丝
宣传栏