MySQL面试必备一之索引

本文首发于公众号:Hunter后端

原文链接:MySQL面试必备一之索引

在面试过程中,会有一些关于 MySQL 索引相关的问题,以下总结了一些:

  1. MySQL 的数据存储使用的是什么索引结构
  2. B+ 树的结构是什么样子
  3. 什么是复合索引、聚簇索引、覆盖索引
  4. 什么是最左匹配原则
  5. 数据 B+ 树中是如何查询的
  6. 回表是什么操作
  7. B+ 树的查询有什么优势
  8. 索引下推是什么意思

对于上面这几个问题,看完这篇笔记你应该就会明白这些问题应该如何作答。

这篇笔记将从以下几个方面开始介绍:

  1. B+树
  2. 查询数据的过程
  3. 覆盖索引
  4. 联合索引
  5. MyISAM 的存储结构
  6. InnoDB 与 MyISAM 的区别
  7. B 树与 B+ 树

1、B+树

MySQL 的存储引擎包括 InnoDB、MyISAM、Memory 等,其中 InnoDB 是默认的表存储引擎,InnoDB 和 MyISAM 的数据都存储在 B+ 树这种结构中。

首先来了解下 B+ 树的结构。

1. B+ 树结构

与二叉树一样,B+ 树也是一种树结构,与之不同的点在于每一层并非只有左右两个子结点,而是可以有多个结点,而在 InnoDB 中,数据都是存储在叶子结点上,现在假设我们有一张 user 表,以 id 为主键,有 name、age 这两个字段,那么数据的存储示意图则如下:

在上图中,只展示了 B+ 树的两层,每一层有四个子结点,所有的数据都存储在叶子结点上。

在 InnoDB 中,B+ 树的高度通常为 2 到 4 层,假设每一层我们有 1000 个结点,那么如果树有 4 层,那么在第四层的叶子结点我们可以存储 1000 的三次方条数据,那么就可以存储 10 亿条数据。

2. 主键索引与聚簇索引

当我们创建一张表,并往里面添加数据,数据存储的格式就是上面这张图的形式,默认以主键作为索引,也就是 B+ 树的非叶子结点,所以又称其为主键索引。

对于这张表,除了 id 字段,还有 age 和 name 字段,这几个表字段是一起存放在叶子结点的,因此这种存储方式也称为聚簇索引。

3. 非聚簇索引与二级索引

在 InnoDB 中,除了主键索引外,我们还可以为表的某个或者某些字段创建索引,对于这些索引,我们称其为非聚簇索引或者二级索引。

二级索引的存储结构也是 B+ 树,不一样的点在于非叶子结点的值是创建了索引的字段值,叶子结点就不再是这条表数据了,而是这个索引字段所在的数据的主键值。

还是以前面的 user 表为例,我们在 name 字段上创建索引,那么 InnoDB 会额外创建一个 B+ 树,B+ 树的结构大致如下:

这里为了更直白的表示字段值的排序,用了英文名字来表示,如上就是一个二级索引的存储形式。

2、查询数据的过程

1. 根据主键查询

比如我们要查询 id = 80 的数据,sql 语句如下:

select id, name, age from user where id = 80;

这里是针对 id 字段进行查询,所以可以直接查询主键索引,根据上面主键索引的示意图,其查询步骤如下:

  1. 根据 id = 18 逐层找到 B+ 树的对应非叶子结点,比如这里就到了图里的最上层结点
  2. 根据 id = 18,判断 1 <18 < 35,所以查询进入的叶子结点将会进入最左侧往下
  3. 在最左侧的叶子结点,找到 id = 18 的叶子结点,然后这个节点对应的 id,name,age 字段获取然后返回

以上就是一次根据主键进行查询的过程

2. 根据二级索引查询

还是 user 表,在 name 字段上建立了一个二级索引,我们想要找到 name = “Hunter” 的 id,name,age 字段:

select id, name, age from user where name = "Hunter";

其查询过程如下:

  1. 根据 name=“Hunter” 查询二级索引的 B+ 树,也就是我们的第二张图,根据非叶子结点的值找到最左侧的数据
  2. 根据 “Hunter” 可以找到这条数据的主键 id = 3
  3. 根据 id = 3 去主键索引的 B+ 树里查询对应的字段(这里的查询操作就是根据主键查询数据了)

回表:上面的查询过程中,根据二级索引获取到的主键 id,到主键索引里查询对应的数据,这个过程就称为回表

3、覆盖索引

对上面二级索引查询的过程,我们有一个回表的操作,即根据二级索引获取到的主键 id 再去主键索引获取相应的字段数据,这部分的查询过程是会影响查询效率的。

那么针对这种回表的情况,我们可以在某些情况下使用覆盖索引来对其进行优化。

所谓覆盖索引,并非某种实际的索引结构,而应该算得上是一种思想或者优化手段。其主要思想为在二级索引中就可以拿到查询所需的全部字段,而不需要进行回表操作。

针对前面我们对 name 加了索引的情况,如果我们的 SQL 语句如下,那么即可使用覆盖索引,而不需要再到主键索引里回表查询:

select id, name from user where name = "Hunter";

在上面这个语句中,我们查询的是 id 和 name 两个字段,这两个字段在 name 字段的二级索引的查询中即可获取所需的字段值,那么则不需要进行回表操作,这个过程就相当于使用了覆盖索引。

而如果我们所需要的字段并不只是这两个字段,比如我们还要查询 age 字段,针对这种情况,如果要用覆盖索引的话,就需要引入下一节的内容,联合索引,或者叫复合索引。

4、联合索引

复合索引,或者叫联合索引,指的是针对多个字段创建的索引,常常适用于多个字段进行查询的场景。

1. 联合索引的结构

联合索引也是二级索引,不过它的非叶子结点的值是多个字段组合的。

还是以 user 表为例,我们在 age 和 name 上创建一个联合索引:

CREATE INDEX age_name_idx ON user (age, name);

那么,在 MySQL 中,这个二级索引的存储结构大致如下:

根据上面的这个结构,我们可以知道,字段的顺序是十分重要的,如果我们 SQL 语句的使用不当可能就会用不上联合索引。

2. 联合索引生效的情况

根据上图可以看到非叶子结点的值由两部分组成,分别是 age 和 name 的值,那么我们在进行查询的时候,也应该遵循这个顺序才可能使得索引生效。

1) 单个字段查询

如果我们要查询单个字段,比如 age,那么下面的条件都可以使得索引生效:

where age = 23;
where age > 25;

但是如果 where 后面的条件是针对 name 字段,那么下面的条件则不会使得这个联合索引生效:

where name = "Hunter";

2) 多个字段查询

如果是多个字段查询,那么则使用的时候一定要注意查询的顺序,下面的条件的是可以生效的:

where age = 23 and name = "Hunter";
where age = 24 and name like "Hun*";

而如果是 age 是一个范围查找,则不管 name 字段是什么条件,这个索引也可以生效,但仅仅是 age 字段会用到索引,name 字段的则不会用到索引,比如:

where age > 34 and name = "Hunter";

上面这个 SQL 语句,索引则只会对 age 字段生效进行范围查找,name 字段不用用到索引的精确匹配。

3) 最左匹配原则

基于联合索引的结构,如上图,最左匹配原则的概念其实就显而易见了,即联合索引只会从建立了索引的最左字段开始匹配,直到遇到范围查询则停止,就比如上面提到的这条:

where age > 34 and name = "Hunter";

它匹配到 age 就停止了,因为 age 是一个范围查询。

再来一种情况,如果我们的联合索引字段有三个,按照顺序为 age, name 和 field_3,下面的语句则会分别匹配到 age、name 和 field_3:

where age > 4 and name = "Hunter" and field_3 = 45;
where age = 34 and name like "Hun*" and field_3 = 45;
where age = 34 and name = "Hunter" and field_3 > 45;

下面这几种针对后面几个字段的查询联合索引都是不生效的:

where name = "Hunter" and field_3 = 45;
where field_3 = 45;

因为他们都没有从联合索引的最左字段字段开始查询。

3. 索引下推

索引下推是 MySQL 5.6 版本及以上引入的一个特性,主要用于减少回表的次数,从而实现提高查询性能的效果。

还是以前面 age 和 name 这个复合索引为例,SQL 如下:

where age > 30 and name like "Hun*";

如果没有索引下推,那么它的查询流程是根据 age > 30 这个条件,查询主键 id,逐个回表去主键索引里查询 name 字段的值是否满足 “Hun*” 这个条件。

而如果有索引下推这个优化,那么在二级索引里,查找出 age > 30 的值后,会直接根据复合索引中 name 的值来判断是否满足 “Hun*” 这个特性,满足的话就去回表查询,不满足则在当前复合索引里直接将这条数据过滤掉。

所谓的索引下推就是通过这个流程来减少回表的次数,以提高查询的性能。

5、MyISAM 的存储结构

MyISAM 的数据存储结构也是 B+ 树,但有一点不同,那就是 InnoDB 的叶子结点存储的是完整的一条数据,而 MyISAM 的叶子结点存储的数据的指针,通过指针指向底层存储的数据,其大概示意图如下:

同理,MyISAM 的二级索引的叶子结点也是直接指向存储的数据。

因此,MyISAM 在底层存储的表文件有三个,一个是 frm,是表的定义文件,一个 MYD,用于存储数据的文件,一个是 MYI,用于存储索引的文件。

相对来说,InnoDB 就只有两个,一个是 frm,一个是 IBD,用于存储索引和数据,因为 InnoDB 的叶子结点即存储了数据。

6、InnoDB 与 MyISAM 的区别

这里总结一下 InnoDB 与 MyISAM 的区别:

  1. InnoDB 是聚簇索引,MyISAM 是非聚簇索引
    1. 即 InnoDB 的主键索引是数据和索引放在一起的,而 MyISAM 是索引和数据分离的
  2. InnoDB 支持外键,MyISAM 不支持外键
  3. InnoDB 支持事务,MyISAM 不支持事务
  4. InnoDB 默认支持到行级锁,而 MyISAM 支持表级锁

7、B 树与 B+ 树

B 树和 B+ 树是很相似的树结构,都是每个结点都有多个子结点,不一样的在于 B 树的非叶子结点也存有数据,而 B+ 树只有叶子结点才有数据,非叶子结点都是索引数据,且 B+ 树的叶子结点之间也形成有序链表。

针对以上这个不同点,在 MySQL 中使用 B+ 树有如下优点:

  1. B+ 树在非叶子结点不包含数据,因此在内存页可以存放更多 key,从而在查询的时候可以减少 IO 次数
  2. B+ 树的叶子结点之间形成链表,遍历操作会更方便,而 B 树需要从根节点从上往下遍历
  3. B+ 树的数据都存放在叶子结点,而 B 树的非叶子结点也都有数据,所以查询的过程中 B+ 树总是需要访问到叶子结点,所以 B+ 树的查询效率会更稳定

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/505512.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

SVFormer: Semi-supervised Video Transformer for Action Recognition

标题&#xff1a;SVFormer&#xff1a;用于动作识别的半监督视频Transformer 原文链接&#xff1a;https://doi.org/10.48550/arXiv.2211.13222 源码链接&#xff1a;GitHub - ChenHsing/SVFormer 发表&#xff1a;CVPR 摘要 半监督动作识别是一项具有挑战性但至关重要的任…

2024年道路运输企业安全生产管理人员证模拟考试题库及道路运输企业安全生产管理人员理论考试试题

题库来源&#xff1a;安全生产模拟考试一点通公众号小程序 2024年道路运输企业安全生产管理人员证模拟考试题库及道路运输企业安全生产管理人员理论考试试题是由安全生产模拟考试一点通提供&#xff0c;道路运输企业安全生产管理人员证模拟考试题库是根据道路运输企业安全生产…

day58 动态规划part15

392. 判断子序列 简单 给定字符串 s 和 t &#xff0c;判断 s 是否为 t 的子序列。 字符串的一个子序列是原始字符串删除一些&#xff08;也可以不删除&#xff09;字符而不改变剩余字符相对位置形成的新字符串。&#xff08;例如&#xff0c;"ace"是"abcde&q…

通天星CMSV6 车载定位监控平台 任意文件上传漏洞复现(XVE-2023-23454)

0x01 产品简介 通天星CMSV6车载定位监控平台拥有以位置服务、无线3G/4G视频传输、云存储服务为核心的研发团队,专注于为定位、无线视频终端产品提供平台服务,通天星CMSV6产品覆盖车载录像机、单兵录像机、网络监控摄像机、行驶记录仪等产品的视频综合平台。 0x02 漏洞概述 …

汇编语言第四版-王爽第2章 寄存器

二进制左移四位&#xff0c;相当于四进制左移一位。 debug命令实操&#xff0c;win11不能启动&#xff0c;需要配置文件 Windows64位系统进入debug模式_window10系统64位怎么使用debugger-CSDN博客

DeepL Pro3.1 下载地址及安装教程

DeepL Pro是DeepL公司推出的专业翻译服务。DeepL是一家专注于机器翻译和自然语言处理技术的公司&#xff0c;其翻译引擎被认为在质量和准确性方面表现优秀.DeepL Pro提供了一系列高级功能和服务&#xff0c;以满足专业用户的翻译需求。其中包括&#xff1a; 高质量翻译&#xf…

vue3 视频播放功能整体复盘梳理

回顾工作中对视频的处理&#xff0c;让工作中处理的问题的经验固化成成果&#xff0c;不仅仅是完成任务&#xff0c;还能解答任务的知识点。 遇到的问题 1、如何隐藏下载按钮&#xff1f; video 标签中的controlslist属性是可以用来控制播放器上空间的显示&#xff0c;在原来默…

MySQL数据库高阶语句②

目录 一.子查询与多表查询 1.子查询 2.update子查询 3.多表查询 4.delete子查询 5.exists关键字也用于子查询 6.结果集 二.MySQL视图 1.定义 2.作用场景 3.视图与表的区别与联系 &#xff08;1&#xff09;区别 ①视图是已经编译好的sql语句。而表不是 ②视图没有…

unity 打包安卓错误汇集

Failed to find target with hash string "android-34’ in: D:Pr 他说找不到sdk34level的我用as打开后卸载又重装&#xff0c;最后解决了 我放到Plugins/Android/下面的Java代码没有被编译 这个不知道为什么。我故意把代码写的有问题&#xff0c;会报错那种&#xff…

linux自定义命令

文章目录 1、自定义命令介绍2、自定义命令步骤 (centos7)2.1 新建隐藏目录存放自定义命令脚本文件2.2 将新建的目录配置环境变量2.3 取别名的方式简化已有命令2.4 编写自定义命令脚本 1、自定义命令介绍 不管是linux系统还是windows系统都支持自定义命令&#xff0c;windows端…

MIPI CSI-2 Low Level Protocol解读

一、Low Level Protocol介绍 LLP 是一种面向字节的基于数据包的协议&#xff0c;支持使用短数据包和长数据包格式传输任意数据。为简单起见&#xff0c;本节中的所有示例均为单通道配置。 LLP特性&#xff1a; 传输任意数据&#xff08;与有效载荷无关&#xff09; 8 位字大…

代码随想录第二十五天 | 回溯算法P2 | ● 216● 17

216.组合总和III 找出所有相加之和为 n 的 k 个数的组合&#xff0c;且满足下列条件&#xff1a; 只使用数字1到9每个数字 最多使用一次 返回 所有可能的有效组合的列表 。该列表不能包含相同的组合两次&#xff0c;组合可以以任何顺序返回。 示例 1: 输入: k 3, n 7 输出…

解决AD使用交互式BOM插件时,插入make点导致显示异常的问题

记得上次写了一篇关于使用这个插件时出现这个问题的解决方法&#xff0c;具体可查看&#xff1a;AD使用交互式BOM插件时应该注意到的一个问题_ad的bom插件-CSDN博客 当时的解决办法就是删除后再运行脚本生成&#xff0c;这些天经过多次实验&#xff0c;发现是当时那个封装有问…

健身房预约管理系统(源码+文档)

健身房预约管理系统&#xff08;小程序、ios、安卓都可部署&#xff09; 文件包含内容程序简要说明含有功能&#xff1a;项目截图客户端首页我的预约登录教练预约时间我的注册页个人资料课程预约课程预约 管理端订单管理团课管理教练管理分类管理用户管理 文件包含内容 1、搭建…

vulnhub靶场之driftingblues-4

一.环境搭建 1.靶场描述 get flags difficulty: easy about vm: tested and exported from virtualbox. dhcp and nested vtx/amdv enabled. you can contact me by email for troubleshooting or questions. This works better with VirtualBox rather than VMware. 2.靶场…

Segger Embedded Studio IDE使用体验——默认的Section和Linker的设置

Segger Embedded Studio IDE使用体验之一——默认的Section和Linker的设置 一、简介二、操作2.1 编译后代码分析2.1.1 符号浏览器2.1.2 读取elf文件和map文件 2.2 调试2.2.1 查看变量2.2.2 设置供电 2.3 运行环境设置2.3.1 编译器2.3.2 汇编器2.3.3 包含其他文件2.3.4 .bss和.d…

【MATLAB源码-第24期】基于matlab的水声通信中海洋噪声的建模仿真,对比不同风速的影响。

操作环境&#xff1a; MATLAB 2022a 1、算法描述 水声通信&#xff1a; 水声通信是一种利用水中传播声波的方式进行信息传递的技术。它在水下环境中被广泛应用&#xff0c;特别是在海洋科学研究、海洋资源勘探、水下军事通信等领域。 1. **传输媒介**&#xff1a;水声通信利…

Postgresql导出数据和结构后再去另外一个Postgresql数据库中导入失败

参考教程&#xff1a; postgresql 在导入建表sql时 遇到错误 &#xff1a;https://blog.csdn.net/weixin_37706944/article/details/132321731 是因为原表定义了自增字段&#xff0c;解决办法&#xff1a; 解决方法&#xff1a; 执行如下sql后再新建表&#xff0c;就可以了 DR…

ngrok 内网穿透使用

title: ngrok 内网穿透使用 search: 2024-02-29 文章目录 背景Windows安装ngrok指令授权ngrok个人用户Authtoken穿透 http 或 https 服务ngrok的代理http指令ngrok获得静态域名指令ngrok的代理ssh指令 背景 这次寒假回家&#xff0c;很无奈&#xff0c;很多东西放在项目组服务…

[Windows]防火墙,出入站规则失效。

场景&#xff1a; 因为具体需要&#xff0c;在内网中&#xff0c;不想别人发现我们的nacos端口8848&#xff0c;因此我们设置了入站规则&#xff0c;特定的ip地址才能访问。但是实际测试中发现并不起作用。。。 经过一番排查得到一下结果。 为什么有些应用绕过了防火墙配置 有…