【MySQL】深入了解索引背后的内部结构

目录

索引的认识:

作用:

索引的使用:

索引底层的数据结构:

哈希表

AVL树

红黑树

B树:

B+树:

B+树搜索:


索引的认识:

索引是数据库中的一个数据结构,用于加速查询操作。

作用:

  • 数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍内容和书籍目录的关系。
  • 索引所起的作用类似书籍目录,可用于快速定位、检索数据。
  • 索引对于提高数据库的性能有很大的帮助

比如一本字典,如果一一的查找,这个效率将会很低下。

但如果给它加上标签的话,就一下可以找到你想搜索的东西,所有它大大提高了我们的查询速度。

索引可以提高查询速度,但可能会拖慢增删改的速度,后续对数据进行增删改的操作,都是要同步索引的。但在实际开发中,查询的频率要远远高于增删改的操作,所以这是利大于弊


索引的使用:

//查看索引
show index from 表名;

//创建索引
//对于非主键、非唯一约束、非外键的字段,可以创建普通索引
create index 索引名 on 表名(字段名);

//删除索引

drop index 索引名 on 表名;

 操作:

注意:

        索引的创建也是一个危险操作!!!
针对空表或者数据量比较小的表中创建索引没有任何问题,但如果表中数据很大,此时创建索引将会引起大量的CPU/硬盘IO的消耗,可能会把MySQL直接搞挂;

        解法方法:

1.预测哪个索引可能会频繁使用,根据建议,提前给它创建好
2.引入新的数据库服务器,提取创建好索引,将旧的数据库服务器数据慢慢导入到新服务器中(常规做法);

索引底层的数据结构:

索引一定是引入了一些额外的数据结构,加快了查询速度!

引入索引的目的,就是通过其他的数据结构,来加快查询的速度,以便减小表的遍历!


那么哪些数据结构可以加快查询速度?

        1.顺序表: 随机访问,并且插入删除效率低下 不适合加快查询速度

        2.链表:从头依次遍历,更不能加快查询速度

        3.哈希表 

哈希表

    众所周知,哈希表查询速度是最快的,构造出合适的哈希函数,可以达到O(1)查询速度,即使在极端情况下,将所有值通过哈希函数映射到一个同哈希桶上,达到O(N),这种情况一般只存在于理论上,现实中几乎不可能出现。最坏情况下,设置哈希桶上的每个链表长度为M,O(M)也是近视O(1)。      

    虽然哈希表的查询速度特别快,但是它只能查找特定的某个值(key),并不是一连串的范围,但数据库中一般要我们查找的情况下是一系列的范围数据,所以并不适合数据库查询;

        4.树

二叉搜索树的数据 中序遍历是连续范围的数据 是有序的,可以进行范围查询,如果是一个比较平衡的二叉树搜索树 遍历速度O(logN)  最坏情况下变成一个链表,就会变成O(N)速度

AVL树

是一颗严格的二叉搜索树,左右子树高度不能超过1 所以遍历速度O(logN)  但是当你非常严格的情况下,每次进行增删改的操作,从而触发旋转操作,每次旋转,都会有开销。

红黑树

而红黑树并没有AVL树那么严格,触发旋转的概率很小,虽然没有AVL树平衡,但是查询速度也没差多少

红黑树里面的数据 中序遍历是连续范围的数据 是有序的,可以进行范围查询,但由于它是二叉类型的,如果数据量特别大,这会让树的高度变得非常高,树的高度每加一层,比较次数就会增加一次,由于数据都是保存在硬盘中,就会多要一次硬盘IO操作了,它查询的效率就会变得慢,所以并不适合大规模的数据


 因此就引入了B树,它是一个N叉搜索树,同样数量的数据,需要的节点变少了,树的高度大大降低了,从而减小了遍历的次数。

B树:

以上是B树的大概形状

1.每个节点上的key是有序的,比较的时候可以直接用二分查找

2.B树会控制每个节点上的key的数量,如果key太多,就会分裂更多的叶子节点出来

3.多个数据,都是放在一块连续的存储空间上,比较的时候,使用一次IO就可以遍历完整个节点 因此B树更适合对应这种数据量的范围查找,但数据库索引的最终形态是B+树,B树的升级版


B+树:

B+树也是N叉树 对比B树 B+树做了进一步优化

1.B+树每个父节点的元素都会在子节点的最大值出现

2. B树的每个节点都包含键值(Key)和相应的值(Value)(包括叶子节点和非叶子节点)。B+树非叶子节点只存储键值(Key)也就是ID,叶子节点存储所有的数据,并且每个叶子节点是以链表结构存储起来的,B+树可以通过简单的顺序访问叶子节点来高效地执行范围查询。

3.进行每次查询操作,都会落到最终的叶子节点上,每次经历的硬盘IO次数都是稳定的(稳定做一件事在计算机中很重要)

4. B+树的非叶子节点都存储的数据比较小,所有可以存储在内存中,进一步减小硬盘IO的次数

特性B树B+树
数据存储位置数据存储在所有节点(包括内部节点)数据只存储在叶子节点
内部节点存储键和值仅存储键(不存储数据)
叶子节点链接无链接叶子节点通过链表连接
查询效率适合单点查询更适合范围查询
范围查询性能较差非常高效(通过叶子节点链表)
树的高度相对较高较低
内存/磁盘利用内存和磁盘利用相对较低更高效,能容纳更多节点


Mysql中支持多种存储引擎,其中InnoDB最常用(也是面试做常考查的内容),不同的存储引擎使用的索引也是不同的 

B+树搜索:

下面来介绍B+树在有无索引的情况下如何检索:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

在这张表中,id为主键,所有默认会创建索引,name和age列则没创建索引

1)遍历有主键索引的列 

SELECT * FROM employees WHERE id = 5;

MySQL 会利用 B+ 树索引,直接从根节点开始查找,快速定位到 id = 5 的叶子节点,查询的时间复杂度为 O(log N)。 

2)遍历无索引的列 

SELECT * FROM EMPLOYEES WHERE NAME = 'zhangsan' ;

 MySQL 只能通过全表扫描来查找数据,效率较低,尤其在表的数据量较大时。

3) 遍历有索引的列却不是主键索引

create index index_id on employees(age) ;

 此时为age列创建索引

select * from employees where age = 20 ;

此时根据关于age的B+树找到对应叶子节点,但此时非主键索引的B+树的叶子节点存储的都是主键Id,因此找到Id之后,再在id主键索引的B+树中遍历 找到对应的叶子节点,此时叶子节点才正在存储我们想要找到的数据;

因此需要遍历俩次B+树,第一次找到主键Id,再在主键Id的B+树找到对应的值;

总结: 

  • 没有索引的情况下,MySQL 只能通过全表扫描来查找数据,效率较低,尤其在表的数据量较大时。
  • 使用 B+ 树索引的情况下,MySQL 可以通过 B+ 树的查找机制(O(log N) 的复杂度)高效地定位记录,从而大大提升查询性能。B+ 树支持点查询和范围查询,尤其对于大数据量的表,具有非常重要的优化作用。

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

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

相关文章

【MySQL】--- 数据类型

Welcome to 9ilks Code World (๑•́ ₃ •̀๑) 个人主页: 9ilk (๑•́ ₃ •̀๑) 文章专栏: MySQL 🏠 数据类型分类 MySQL是一套整体的对外数据存取方案,既然要存取数据,而数据有不同的类型,因此MySQL也存在不同的数据类型,有不同的用…

电商店铺数据集成到金蝶云星辰V2的实践经验分享

电商店铺数据集成到金蝶云星辰V2的技术案例分享 在电商业务快速发展的背景下,如何高效地将聚水潭平台上的电商店铺数据集成到金蝶云星辰V2系统中,成为了许多企业面临的重要挑战。本文将详细探讨一个实际运行的解决方案——“电商店铺->金蝶客户”&am…

在VBA中结合正则表达式和查找功能给文档添加交叉连接

在VBA中搜索文本有两种方式可用,一种是利用Range.Find对象(更常见的形式可能是Selection.Find,Selection是Range的子类,Selection.Find其实就是特殊的Range.Find),另一种方法是利用正则表达式,但…

大腾智能CAD:国产云原生三维设计新选择

在快速发展的工业设计领域,CAD软件已成为不可或缺的核心工具。它通过强大的建模、分析、优化等功能,不仅显著提升了设计效率与精度,还促进了设计思维的创新与拓展,为产品从概念构想到实体制造的全过程提供了强有力的技术支持。然而…

实现Python将csv数据导入到Neo4j

目录 一、获取数据集 1.1 获取数据集 1.2 以“记事本”方式打开文件 1.3 另存为“UTF-8”格式文件 1.4 选择“是” 二、 打开Neo4j并运行 2.1 创建新的Neo4j数据库 2.2 分别设置数据库名和密码 ​编辑 2.3 启动Neo4j数据库 2.4 打开Neo4j数据库 2.5 运行查看该数据库…

MySQL知识汇总(二):select

select语句 -- select语句 select 字段 from 表 -- 查询全部信息 select * from 表 SELECT * FROM student2 -- 查询指定字段 select name from 表 SELECT name FROM student2 -- 起别名 给查询结果用 AS 起个其他的名字,可以是字段也可以是表 SELECT name AS 名字 …

Restaurants WebAPI(二)——DTO/CQRS

文章目录 项目地址一、DTO1.1 创建Restaurant的Dto1.2 修改之前未使用Dto的接口1.2.1 修改GetRestaurantByIdUseCase1.2.2 修改IGetRestaurantByIdUseCase接口1.2.3 再次请求接口1.3 显示Dish List1.3.1创建DishDto1.3.2 在RestaurantDto里添加DishDto1.3.3 使用Include添加Dis…

c++--------c++概念

定义与起源 C是一种高级编程语言,它是C语言的扩展。C由Bjarne Stroustrup在20世纪80年代初开发,最初被称为“C with Classes”。其设计目的是在保持C语言高效性的同时,增加面向对象编程(OOP)的特性。例如,…

面向对象设计过程的理解和实践

在软件开发的世界里,面向对象设计(Object-Oriented Design,简称OOD)是一项至关重要的技术。它不仅帮助我们更好地将现实世界的问题转化为软件系统中的对象,还确保这些对象之间能够高效地协同工作,共同实现系…

游泳溺水识别数据集,对9984张原始图片进行YOLO,COCO JSON, VOC XML 格式的标注,平均识别率在91.7%以上

游泳溺水识别数据集: 对9984张原始图片进行YOLO,COCO JSON, VOC XML 格式的标注,平均识别率在91.7%以上 ,可识别泳池或者水库中是否有人溺水。 数据集分割 训练组98% 9818图片 有效集%…

Llama3.370B超越GPT-4o和Claude3.5 Sonnet

AI领域日新月异,最近AI 领域发生了太多事情,本文就语言大模型Llama 3.3 70B、GPT-4o 和 Claude 3.5 Sonnet进行对比。 12月7日,Meta今年的最终AI模型将要来了。Meta12月6日发布了Llama 3.3,拥有700亿个参数,但其性能与…

linux内核网络分层概述

在开发应用时,我们使用 socket 实现网络数据的收发。以tcp为例,server端通过 socket, bind, listen来创建服务端,然后通过 accept接收客户端连接;客户端通过 socket和 connect系统调用来创建客户端。用于数据收发的系统调用包括 s…

【全栈实战】基于 Vue3 + Wot Design Uni 动手封装组件

😊你好,我是小航,一个正在变秃、变强的文艺倾年。 😊好久没有更新有关前端实战教程了,本文主要讲解【全栈实战】基于 Vue3 Wot Design Uni 动手封装组件! 😊这个教程你将会学到技术正确的选型、…

Ajax中的axios

既然提到Ajax,那就先来说一说什么是Ajax吧 关于Ajax Ajax的定义 Asynchronous JavaScript And XML:异步的JavaScript和XML。 反正就是一句话总结: 使用XML HttpRequest 对象与服务器进行通讯。 AJAX 是一种在无需重新加载整个网页的情况下&…

60.基于SSM的个人网站的设计与实现(项目 + 论文)

项目介绍 本站是一个B/S模式系统,个人网站是在MySQL中建立数据表保存信息,运用SSMVue框架和Java语言编写。并按照软件设计开发流程进行设计实现充分保证系统的稳定性。系统具有界面清晰、操作简单,功能齐全的特点,使得基于SSM的网…

聊聊Flink:Flink的状态管理

一、Flink的状态是什么? 我们知道,Flink的一个算子可能会有多个子任务,每个子任务可能分布在不同的实例(即slot)上,我们可以把Flink的状态理解为某个算子的子任务在其当前实例上的一个变量,该变…

Idea 将多个module显示在同一个project

Idea 将多个maven项目显示在同一个project下 1、选择菜单 File-》New -》Module from Existing Sources -> 2、在弹出的界面选中对应的Module的pom.xml,然后点击OK按钮就行了 (弹出框上面也提示了Eclipse 项目选中.project文件;Maven 项目选中pom.xml; ) 最终显…

文件解析漏洞中间件(iis和Apache)

IIS解析漏洞 IIS6.X #环境 Windows Server 2003 在iis6.x中&#xff0c;.asp文件夹中的任意文件都会被当做asp文件去执行 在默认网站里创建一个a.asp文件夹并创建一个1.jpg写进我们的asp代码 <%now()%> #asp一句话 <%eval request("h")%> 单独创建一…

gitee别人仓库再上传自己仓库

一、新建一个自己的Git仓库 如果没有注册账号的朋友&#xff0c;可以先去注册一个Gitee的账号&#xff0c;用于管理自己的代码特别好用&#xff01;&#xff01;&#xff01; 接下来就是在gitee上新建一个自己的仓库&#xff0c;如下图所示 二、右建 Git Bush Here删除.git文件…

STM32F407 | Embedded IDE01 - vscode搭建Embedded IDE开发环境(支持JLINK、STLINK、DAPLINK)

导言 Embedded IDE官网:https://em-ide.com/docs/intro 我猜肯定有部分人使用SI Keil开发STM32项目&#xff0c;也有vscode Keil开发STM32程序。SI或vscode编写代码&#xff0c;然后切换Keil编译、下载、调试程序。有一段时间&#xff0c;我也是这么干的。但是&#xff0c;程…