MySQL知识点总结(一)——一条SQL的执行过程、索引底层数据结构、一级索引和二级索引、索引失效、索引覆盖、索引下推

本文主要是介绍MySQL知识点总结(一)——一条SQL的执行过程、索引底层数据结构、一级索引和二级索引、索引失效、索引覆盖、索引下推,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

MySQL知识点总结(一)——一条SQL的执行过程、索引底层数据结构、一级索引和二级索引、索引失效、索引覆盖、索引下推

  • 一条SQL的执行过程
  • 索引底层数据结构
    • 为什么不使用二叉树?
    • 为什么不使用红黑树?
    • 为什么不使用hash表?
    • 为什么不使用b-tree?
  • 一级索引和二级索引
  • 索引失效
  • 索引覆盖
  • 索引下推

一条SQL的执行过程

在这里插入图片描述

  • 客户端:用于向服务端发起sql查询或更新请求,MySQL自带的命令行客户端、MySQL的JDBC客户端等都是。
  • 连接器:用于接收客户端的连接,并进行身份认证、查询当前账号拥有的权限。
  • 查询缓存:MySQL服务端会将一条SQL的查询结果缓存缓存起来,下一次再执行相同的sql时,就可以直接从缓存中取。但是一旦对应的库表发生了更新,缓存将会被清空,因此只适用于更新频率不高的场景,MySQL8.0以上的版本已经将其去除。
  • 分析器:对SQL进行词法分析和语法发现,就是分析我们的这个SQL要干啥。
  • 优化器:对我们的SQL进行优化,选取使用的索引,生成执行计划。
  • 执行器:调用执行引擎的接口进行SQL查询或更新。

索引底层数据结构

MySQL索引的底层数据结构是B+树。

在这里插入图片描述

B+树是多路平衡树(B-tree)的一个变种,非叶子节点只存放主键和到下一级节点的指针,叶子节点存放主键和主键对应的数据行记录,叶子节点通过指针进行连接,形成一个双向链表,还有一个头指针和尾指针分别指向链表头节点和尾节点。在MySQL的b+tree中,一个索引页是16KB。

为什么不使用二叉树?

首先我们要明白一点,MySQL中的索引页是存储在磁盘中的,每次读取一个索引页,都是一次磁盘读取,会有磁盘寻址的开销,因此MySQL应该选取一种数据结构,可以让它尽量少的去读取磁盘,才适合作为存储索引的数据结构。

因为二叉树每个节点只有两个出路,树高较高,而B+树是多路平衡树,每个节点有多个出路,树高较矮,这意味着如果用二叉树作为索引的数据结构的话,磁盘寻址的次数会比使用B+树时多,性能不如B+树。

并且,在极端情况下,二叉树会退化成链表,比如id等于1、2、3、4、5、6、7的七条数据按顺序插入,最终二叉树的结果就变成了下图这个样子。

在这里插入图片描述

为什么不使用红黑树?

红黑树解决了二叉树极端情况退化成链表的问题,但是它没有解决树高较高的问题,因为红黑树也是一个二叉树的数据结构。

在这里插入图片描述

为什么不使用hash表?

hash表在插入和等值查询时非常快,可以做到O(1)的时间复杂度。但是hash表的原理是通过hash函数根据key算出一个hash值,然后通过hash值与hash表中的数组长度取模后,进行散列存储的,数据之间不存在顺序性,因此做索引范围查询时需要进行全表扫描,性能是比较低的。

在这里插入图片描述
而B+树是按顺序排好序的,并且索引页之间有双向指针,还有头指针和尾指针,范围查询非常方便。

为什么不使用b-tree?

B树是多路平衡树,分叉比二叉树和红黑树多,因此树高会比二叉树和红黑树矮。但是B树的非叶子节点也存放数据,而MySQL的索引页又固定是16KB,因此节点分叉较B+树少,树高比B+树高。此外,B树的叶子节点是没有双向链表连接的,因此范围查询的性能不如B+树。

在这里插入图片描述

一级索引和二级索引

一级索引也叫主键索引,是以主键作为索引键的索引,在B+树中通过主键进行排序。
在这里插入图片描述
二级索引是非主键索引,是以非主键的字段作为索引键进行排序,比如我们以上面的表为例,在age字段上建立一个二级索引,则效果如下图。

在这里插入图片描述

二级节点的叶子节点不存储行记录,而是存储索引建(age字段)和主键(id),当通过二级索引进行搜索时,会先从二级索引找到对应的主键,再通过主键在一级索引中进行查找,这个过程叫做回表。比如我们要通过二级索引查找age=60的这一条数据,则整个过程如下。

在这里插入图片描述

这个回表的过程是有性能开销的,如果MySQL判断走二级索引的代价比较大,不如全表扫描,就会放弃二级索引进行全表扫描。回表一般是因为我们建立二级索引时只包含一个索引键,没有包含要查询的其他字段,如果我们建立二级索引时,连同其他需要查询返回的字段一起建立一个二级联合索引,使得需要查询返回的字段在二级索引叶子节点中都有,MySQL就不会回表,这时候二级索引一般都会生效。

索引失效

索引失效是指由于SQL语句编写不规范(或其他原因)导致MySQL不走已经建立的索引进行查询,以下几种情况都会造成索引失效。

在这里插入图片描述

索引覆盖

索引覆盖是一种优化二级索引回表查询的手段,在建立索引时,原先的索引键连同最终需要查询返回的字段一起组成一个联合索引。这样,MySQL通过二级索引进行查询时,发现二级索引的叶子节点已经包含了所有需要查询返回的字段,就不会再回表查询,这样查询性能就会大大提高,原本由于大量回表而导致二级索引失效,通过这种优化手段,会使得MySQL会选择这个二级索引进行查询。

在这里插入图片描述

索引下推

在老版本的MySQL中,如果联合索引查询使用了范围查询,会使得联合索引中范围查询的字段的后续字段失效。比如我们有一张t_user表,有四个字段:“id(主键)、name、age、phone”。现在我们有一个sql:“select name, age, phone, where name like ‘黄%’ and age > 20;”。我们建立了一个联合索引(name,age),如果MySQL查询走了这个索引,那么MySQL5.6以前的版本是这样的:

在这里插入图片描述

新版本(5.6之后)的MySQL则通过索引下推进行优化,MySQL在通过二级索引中的name字段进行模糊匹配查询后,会利用二级索引中的第二个字段age进行条件判断来做进一步的筛选过滤,过滤掉不满足“age > 20”这个条件的id,这样可以减少回表的次数提升查询性能。

在这里插入图片描述

这篇关于MySQL知识点总结(一)——一条SQL的执行过程、索引底层数据结构、一级索引和二级索引、索引失效、索引覆盖、索引下推的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/655323

相关文章

Java覆盖第三方jar包中的某一个类的实现方法

《Java覆盖第三方jar包中的某一个类的实现方法》在我们日常的开发中,经常需要使用第三方的jar包,有时候我们会发现第三方的jar包中的某一个类有问题,或者我们需要定制化修改其中的逻辑,那么应该如何... 目录一、需求描述二、示例描述三、操作步骤四、验证结果五、实现原理一、需求描述需求描述如下:需要在

JavaScript中的reduce方法执行过程、使用场景及进阶用法

《JavaScript中的reduce方法执行过程、使用场景及进阶用法》:本文主要介绍JavaScript中的reduce方法执行过程、使用场景及进阶用法的相关资料,reduce是JavaScri... 目录1. 什么是reduce2. reduce语法2.1 语法2.2 参数说明3. reduce执行过程

Springboot中分析SQL性能的两种方式详解

《Springboot中分析SQL性能的两种方式详解》文章介绍了SQL性能分析的两种方式:MyBatis-Plus性能分析插件和p6spy框架,MyBatis-Plus插件配置简单,适用于开发和测试环... 目录SQL性能分析的两种方式:功能介绍实现方式:实现步骤:SQL性能分析的两种方式:功能介绍记录

使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

《使用sql-research-assistant进行SQL数据库研究的实战指南(代码实现演示)》本文介绍了sql-research-assistant工具,该工具基于LangChain框架,集... 目录技术背景介绍核心原理解析代码实现演示安装和配置项目集成LangSmith 配置(可选)启动服务应用场景

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包,它允许动态地构建和执行

Goland debug失效详细解决步骤(合集)

《Golanddebug失效详细解决步骤(合集)》今天用Goland开发时,打断点,以debug方式运行,发现程序并没有断住,程序跳过了断点,直接运行结束,网上搜寻了大量文章,最后得以解决,特此在这... 目录Bug:Goland debug失效详细解决步骤【合集】情况一:Go或Goland架构不对情况二:

SQL 中多表查询的常见连接方式详解

《SQL中多表查询的常见连接方式详解》本文介绍SQL中多表查询的常见连接方式,包括内连接(INNERJOIN)、左连接(LEFTJOIN)、右连接(RIGHTJOIN)、全外连接(FULLOUTER... 目录一、连接类型图表(ASCII 形式)二、前置代码(创建示例表)三、连接方式代码示例1. 内连接(I

redis群集简单部署过程

《redis群集简单部署过程》文章介绍了Redis,一个高性能的键值存储系统,其支持多种数据结构和命令,它还讨论了Redis的服务器端架构、数据存储和获取、协议和命令、高可用性方案、缓存机制以及监控和... 目录Redis介绍1. 基本概念2. 服务器端3. 存储和获取数据4. 协议和命令5. 高可用性6.

在MySQL执行UPDATE语句时遇到的错误1175的解决方案

《在MySQL执行UPDATE语句时遇到的错误1175的解决方案》MySQL安全更新模式(SafeUpdateMode)限制了UPDATE和DELETE操作,要求使用WHERE子句时必须基于主键或索引... mysql 中遇到的 Error Code: 1175 是由于启用了 安全更新模式(Safe Upd

Go语言中三种容器类型的数据结构详解

《Go语言中三种容器类型的数据结构详解》在Go语言中,有三种主要的容器类型用于存储和操作集合数据:本文主要介绍三者的使用与区别,感兴趣的小伙伴可以跟随小编一起学习一下... 目录基本概念1. 数组(Array)2. 切片(Slice)3. 映射(Map)对比总结注意事项基本概念在 Go 语言中,有三种主要