数据库优化——深入理解Mysql索引底层数据结构与算法

【MySQL系列2】深入分析MySQL索引的存储结构和优化方案,看完这篇再也不怕面试官问索引了 由于细节内容实在太多了,为了不影响文章的观赏性,只截出了一部分知识点大致的介绍一下,每个小节点里面都有更细化的内容!小编准备了一份Java进阶学习路线图(Xmind)以及来年金三银四必备的一份《Java面试必备指南》《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!外链图片转存中…(img-GNs40sc0-1713401827666)] 阅读详情

前言:

最近学习了数据库这块的知识,看了一些视频,现在把学到的一些知识点整理出来,也为自己做个笔记。

索引是什么?

工作中,在数据库查询中,可能有些人觉得只要加了索引查询速度就很快,其实并不是这样子的。当然索引到底是什么东西呢?

索引:索引是帮助MySQL高效获取数据的排好序的数据结构,该数据结构是存储在文件中的。

上面的是一个对索引的一个解释,我们可以看出索引其实是一个数据结构。效果就是高效率获取结果。当然,这也是我们正确使用这个数据结构的结果。

索引的数据结构

索引的常用数据结构有

  • 二叉树
  • 红黑树
  • hash
  • BTree

索引查找数据的原理简述

通过索引文件加载出排好序的数据结构,比如获取一个id = 10,的那条数据,没有索引的情况下,是从id=1 遍历到id = 10这里需要查询10次。而有了索引真实通过数据结构去寻找,比如二叉树,这样就大大的减少了查找次数,找到id = 10这条索引里面存储了文件指针。文件指针指向的是id = 1000的这条记录的数据的位置。这样就可以提高很多倍的效率,当然不同的数据结构会有不同的效率。下文会继续分析。

数据库优化——深入理解Mysql索引底层数据结构与算法

这个就是一个二叉树查询的过程和步骤。查询第10条数据时,只需要查询5次就可以。

二叉树数据结构

二叉树的数据结构其实是不适合作索引的,虽然有一定程度的减少了查询的次数。但是效率并不是很高。

二叉树的查找数据的时间复杂度是O(log2N),查找时间适合深度有关系的,深度越高查找时间越长。但是二叉树如果要是已主键为索引,然后主键是自增的话,那么就会发生链表的情况,这样是没有减少查询次数,如果数据量大的,查询时间会很长,和没有使用索引几乎无差别的。

数据库优化——深入理解Mysql索引底层数据结构与算法

这是一个自增主键的二叉树数据结构。如果我查询id = 6的数据,那么我依然需要查询6次。如果说这是二叉树数据结构的缺点的话,那么红黑树刚刚好完善了二叉树的缺点。

红黑树数据结构

红黑树它平衡了二叉树,它具有一个自动平衡的功能,使得自增id不会成为链表状态。这样可以使查询时间减少。完美的避开了二叉树的致命缺陷。下面我们看一下红黑树的数据结构。

数据库优化——深入理解Mysql索引底层数据结构与算法

它不像二叉树那样,呈现链表状态,他能一定程度的减少查询次数,缩短查询时间。这个数据结构如果是内存中的话,那么运行效率是非常高,但是这个数据结构中在索引中,却不是很适合做索引的数据结构,这是为什么呢?

因为索引是存储在文件中的。刻在磁盘上面,数据量增加后,文件大小就会增加。如果是千万级的数据量那么文件会很大。而数据是没有办法一次性加载到内存中的。所以加载到内存中需要多次加载数据,每一次都不能确保数据在相同的磁道上,所以在存储数据的时候,需要尽量存在相同的磁道上面。磁盘读取数据时,是顺序读取的,这样是不会产生寻道时间,只有磁盘旋转。而红黑树没有顺序分布在磁盘中,所以查询的时候回出现不断地寻道。如果数据量大的情况下,会产生更多的寻道。这样在数据库查询中,会大大降低查询速度。

注意: 数据结构是内存中的逻辑结构而不是存储时候的物理结构,在磁盘上面数据是数组、杂乱的。这里需要说一下磁盘的存取原理。数据存储在磁盘的磁道上。然后磁盘是不停地转动的,如果数据在同一个磁道上面那么查询数据会非常快。如果要是在不同的磁道上面,那么需要磁头的移动,做寻道操作,而磁头的移动时间是非常高的。

HASH·散列数据结构

简单说一下用hash做索引,索引中是存着文件指针的。如果将一个主键的hash值作为文件指针,那么想象一下,比如我查询id = 6的数据 那么直接将6 做一次hash 得到hash值,这时候就已经得到文件地址了。直接一次文件IO操作,就能得到数据。这个速度是非常的块。但是索引没有用hash的原因是什么呢?

hash在查询一条数据情况下确实非常快。但是如果你查询 id>6 的数据hash就很无力了。因为没有办法查,其实就是走不了索引。

MySQL在InnoDB搜索引擎中有hash数据结构的索引。为什么会留着他,小编猜应该是在查询单条数据时候,hash的速度非常快吧,所以如果需要查询单条数据的时候,在数据库优化的时候不妨创建一个hash的索引试试,可能会有意想不到的结果。

B·Tree数据结构

  • B-Tree的数据结构是每个节点可以存储多个数据的。
  • B-Tree的数据结构的特点:
  • 度(Degree)-节点的数据存储个数
  • 所有的叶子节点的深度都是相同的。
  • 叶子节点的指针为空
  • 节点中的数据从左到右是递增排列的

数据库优化——深入理解Mysql索引底层数据结构与算法

这就是B-Tree的数据结构,你会发现这个是每个节点有多条数据,然后就是同样是10条数据,B-Tree的数据结构深度只有3 。每个节点中的数据都是一起更新到磁盘中的。所以这样会打打减短了深度。寻道的次数也就少了。这里有人会问,为什么不将度的值设置的很大,让他只有一层不就好了。这样所有的索引数据都是在内存中被计算那么速度应该会很快的哈。这里涉及到磁盘的操作。

从磁盘中读取数据:CPU查找数据,将查找的数据放在内存中。供CPU再次调度使用。如果内存中没有CPU需要的数据就回去磁盘读取数据,放进内存中。而磁盘和内存的数据交互有一个单位叫页。一页的大小是4k,磁盘和内存产生一次交互是以页为单位的,也就是4k的整数倍,但是这个大小是有上限的。

所以如果将所有索引放在一个节点上面,有可能数据量超过了磁盘和内存交互数据的最大值。这样这样我们就没有办法一次性拿出所有的数据放进内存了。所以这样会导致数据混乱。

这里度的值,MySQL是自动根据机器硬件做优化的。计量的将每页的大小增加到磁盘和内存数据交互的最大值。这样会将数据库的查询速度增加到最大。

下面我们观察B·Tree的数据结构 ,如果需求是我需要查询大于6的记录,那么查询起来好像也是有点复杂。最起码我需要截取0006右边的数据结构在将他排序然后查询,这样效率好像还是不是很高。MySQL就退出了B+Tree的数据结构。下面我们讲讲B+Tree的数据结构,看看它对于MySQL来说有哪些优点。

B+Tree数据结构

B+Tree 从名字上面看起来好像和B·Tree好像差不多。是的,B+Tree 只是B·Tree的变种。也就是在B·Tree的基础上新加了一些功能。

非叶子节点不存储数据,值存储key(也就是定义的索引字段) 非叶子节点之所以之存储key,是为了使非叶子节点的度更大,每次获取的数据更多。以此来增加查询速度。

叶子节点存在指针

顺序访问指针,提高区间访问的性能。

B+Tree在B·Tree的特性上面新加了这些特性。下面我们看看B+Tree的数据结构到底是什么样子的吧。

数据库优化——深入理解Mysql索引底层数据结构与算法

我们从上图可以看出,B+Tree数据结构在子叶节点拥有所有数据,我给数据结构添加了10个数字。下面也是顺序排列的10个数字。那么非子叶节点是干什么用的呢?假设我需要查抄大于等于7的记录那么我可以直接查到0007然后指向0009在指向0007就可以了。线面是有顺序指针的,有7,8指向9,10这样就可以直接去查找后面的数据了。不需要跨层级去查询。这样速度回提高很多。

B+Tree索引的性能分析

获取数据时进行了预读

预读:磁盘一般会顺序读取一定长度的数据(在B·Tree中见到的页的倍数)放入内存中。

局部性原理

局部性引用:当一条数据 被用到时,其附近的数据也通常马上会被使用

B+Tree 节点的大小设置为一页,每次新建节点时,直接申请一个页的空间,这样,每个节点的数据,物理上面也存储在一个页里,保证预读的顺利进行。读取数据是,获取一个节点的数据就只需要一次I/O。

B+Tree的度一般会超过100,这样保证深度不会太高(一般会在3~5h),这样也就保证了I/O的次数在3 - 5次之间。

那么我们看一下B+Tree是如何查询数据的。

第一次I/O:

数据库优化——深入理解Mysql索引底层数据结构与算法

第二次I/O:

数据库优化——深入理解Mysql索引底层数据结构与算法

第三次I/O:

数据库优化——深入理解Mysql索引底层数据结构与算法

这样我们就查出来了指定的数据,很快。那么我们如果需要查询大于等于8的时候,上面已经说到,B+Tree子叶节点有一个顺序指针,直接指向后面得位置,所以以8右边的数据查询很快。

MyISAM搜索引擎

MyISAM搜索引擎是非聚集的,它的索引文件和数据文件是分离的

数据库优化——深入理解Mysql索引底层数据结构与算法

数据库优化——深入理解Mysql索引底层数据结构与算法

MyISAM 是通过索引绑定的文件指针来查找到相应的数据的。

InnoDB搜索引擎

InnoDB索引实现了聚合

数据文件和索引文件是同一个文件

表数据文件本身B+Tree的索引结构文件

聚集索引叶子节点包含了完整的数据记录

数据库优化——深入理解Mysql索引底层数据结构与算法

数据库优化——深入理解Mysql索引底层数据结构与算法

为什么InnoDB必须有主键,为什么不用UUID,并且最好是整型自增的 ?

回答:因为InnoDB搜索引擎 是聚合的,表的数据中必然需要一个B+Tree的索引结构,而主键索引的叶子节点时表的数据,所以InnoDB 必须需要主键,否则无法聚合。

说到这里,可能会有很多小伙伴开始质疑,我在创建表的时候有的时候也忘记了创建主键呀,但是为什么一样能创建成功呢?为什么不报错。 那是因为为了用户的体验,MySQL在创建表的时候会判断,如果没有主键,那么就会从你创建表的字段中默认选择一个作为主键,如果没有可作为主键的字段,那么就会自动生成一个整型的 字段作为主键。(这里是看不到生成的字段的。)

InnoDB为什么必须要用自增ID呢?而不用UUID。 这里应该有些公司还是在使用UUID作为主键吧。我上家公司也是使用的UUID作为主键,但是这种主键有什么弊端呢 ?自增主键有什么优势呢?

UUID 是32长度的字符串,因为主键对于业务是无用的数据,如果数据量大的话,会占用很多无用的空间

由于UUID的占用的空间大,分配相同的空间导致度的个数减少,每次预读的内容减少,就有可能导致多进行I/O操作。这样会影响查询时间。

由于UUID是字符串,排序的时候,是使用ASCII排序的,比整型排序慢。

前面讲过,B+Tree在插入数据的时候,会直接开辟出一定量的内存,比如开辟了一页的内存,然后插入数据的时候,会将这一页的数据插入到磁盘里,这样就会在同一磁道上面,在查询的时候同一节点的数据不需要寻道,然后,思考一下,如果用UUID的话,如何保证,按照顺序排列,并且又能插入在同一磁道上面? 显然是不能的。所以这里需要自增的主键,这样才能保证每次插入的时候就能保证同一节点的数据是顺序的并且在同一磁道上面呢,这样在查询的时候才会不会寻道。查询速度才快。所以要用自增主键,而不用UUID。

为什么非主键索引结构叶子节点存储的是主键的值,而不是整条数据?待会我们回答这两个问题。

回答:主键索引默认就会存在,非主键索引是自己添加的,如果要是非主键索引也保存整条数据的话,会导致后面一些问题。

空间问题,大家都知道数据库的大部分空间都是数据所占用了。如果非主键索引多了,并且数据上升,那么空间占用是和非主键索引的个数成正比的。

数据冗余,两个索引,同一张表,数据存量分,显得数据非常冗余。.

数据一致性问题。在每次添加数据的时候,还要考虑到数据一致性的问题。每次修改都需要检查两边或更多遍数据是否一致,并且添加数据的时候,也需要添加两边。这样影响到数据添加的效率。

联合索引结构

联合索引的底层的数据结构是什么样子?

数据库优化——深入理解Mysql索引底层数据结构与算法

联合索引是将多个索引字段分成三部分,就是在插入索引的时候会根据最左前缀原理进行排序。

索引最左前缀原理

最左前缀原理是用在联合索引时才会产生。

是用来给联合索引做排序用的。是一种排序原则。

比如:user_id,user_name,create_time 这三个属性作为索引,那么排序的时候是先排序第一个,如果第一排除出来了。就不继续往后排序了。如果第一个相等,那么就继续往后排序。这样以此类推。

其实和sql的 select语句的排序是一个原理 比如

select user_id,user_name,create_time from sys_user order by user_id,user_name,create_time; 

那么排序的顺序原理就是利用的最左前缀原理。

最后福利

有人要问了,看的什么视频?

私信我“Java”就能免费获取视频的领取方式了,还有更多Java资料,包括近一年面试的经历,我都整理成了PDF,也花了我挺多时间,希望大家帮我转发评论下!谢谢大家了!

深入理解MySQL索引底层数据结构与算法 目录 一 理解索引的特性 二 索引的各种存储结构及其优缺点 (一)二叉树 (二)红黑树 (三)Hash (四)B-Tree (五) B+Tree(MySQL索引的真正存储结构) 三. 联合索引底层存储结构 一 理解索引的特性 索引是帮助MySQL高效获取数据的排好序的数据结构 索引存储在文件里 二 索引的各种存储结构及其优缺点 在开始讲这一小节之前,我们先来看一... 阅读详情

相关推荐

性能调优专题(1)之深入理解Mysql索引底层数据结构与算法

一、前言 在这里我们主要是介绍一下几个知识点: 索引数据结构、红黑树、Hash、B+树详解; 索引是怎么支撑千万级表的快速查找; 面试常问B+树索引面试题 联合索引底层结构又是怎么样的; 二、索引的本质 索引是帮助Mysql高效获取数据的排序好的数据结构。这种数据结构MySQL中最常用的就是B+树(B+Tree)。 就好比给你一本书和一篇文章标题,如果没有目录,让你找此标题对应的文章,可能需要从第一页翻到最后一页;如果有目录大纲,你可能只需要在目录页寻找此标题,...

jokeMqc的博客 1349

mysql数据结构_数据库优化——深入理解Mysql索引底层数据结构与算法

前言:最近学习了数据库这块的知识,看了一些视频,现在把学到的一些知识点整理出来,也为自己做个笔记。索引是什么?工作中,在数据库查询中,可能有些人觉得只要加了索引查询速度就很快,其实并不是这样子的。当然索引到底是什么东西呢?索引:索引是帮助MySQL高效获取数据的排好序的数据结构,该数据结构是存储在文件中的。上面的是一个对索引的一个解释,我们可以看出索引其实是一个数据结构。效果就是高效率获取结果。当...

weixin_39772651的博客 294

STM32H745xI双核芯片SMPS/LDO配置误区:从复位失败到精准排查的实战复盘

本文详细分析了STM32H745xI双核芯片在SMPS/LDO配置中的常见误区,特别是复位失败问题的排查过程。通过对比参考板与故障板的波形数据,揭示了电感选型不当导致的电源稳定性问题,并提供了硬件设计检查清单和调试技巧,帮助开发者避免类似陷阱。

weixin_42531588的博客 265

数据结构关于叶子的计算

公式:所有结点的度数之和+1=结点总数   一个结点的子数个数称为该结点的度   

Terry的IT世界 1169

BIM模型导入SuperMap

目录 一、超图 BIM插件安装 1.1 插件下载 1.2 安装 二、BIM模型导出 2.1 Revit插件导出 2.2 SuperMap导入工具 三、SuperMap BIM数据处理与服务发布 四、SuperMap三维数据处理中关于模型高程修改方法 一、超图 BIM插件安装 1.1 插件下载 下载链接:http://support.supermap.com.cn/DownloadCenter/DownloadPage.aspx?tt=ProductAAS&id=134 1..

爱晒太阳的风 3600

STM32 FATFS文件系统按行读取SD卡数据

STM32 SD数据实现按行读取

qq_32216815的博客 7831

Mysql索引底层结构解析

mysql

qq_41956309的博客 896

嵌入式Linux Qt/QML开发实战:从Buildroot配置到应用部署

本文详细介绍了嵌入式Linux Qt/QML开发全流程,从Buildroot系统配置、交叉编译工具链搭建到应用部署与调试。以RK3506开发板为例,提供实战指南,帮助开发者高效完成嵌入式应用开发,解决常见环境配置与性能优化问题。

m2n3o4p5的博客 231

不同存储结构的文件磁盘io操作次数_数据库优化——深入理解Mysql索引底层数据结构与算法...

前言:最近学习了数据库这块的知识,看了一些视频,现在把学到的一些知识点整理出来,也为自己做个笔记。索引是什么?工作中,在数据库查询中,可能有些人觉得只要加了索引查询速度就很快,其实并不是这样子的。当然索引到底是什么东西呢?索引:索引是帮助MySQL高效获取数据的排好序的数据结构,该数据结构是存储在文件中的。上面的是一个对索引的一个解释,我们可以看出索引其实是一个数据结构。效果就是高效率获取结果。当...

weixin_39930671的博客 473

f2fs学习笔记 - 3. F2FS文件系统布局

目录1.前言2.F2FS布局说明2.1. super block(block 0x0 ~ block 0x1ff)2.2 CP(block 0x200 ~ block 0x5ff)cp page1nat journalsit journaldata summarycurrent hot node summarycurrent warm node summarycurrent cold node summarycp page22nd checkpoint2.3 SIT(block 0x600 ~ block

HZero 4647

深入解析Matlab phantom函数:从基础模体到自定义头部仿真

本文深入解析了Matlab中的phantom函数,它是医学图像处理算法测试的核心工具。文章详细介绍了如何使用内置的Shepp-Logan及其改良版头部仿真模体,并重点讲解了通过自定义参数矩阵来构建专属模体的方法。通过从生成模体到模拟投影(正弦图)再到图像重建的完整实战流程,展示了phantom函数在CT等算法开发与验证中的关键作用。

m3n5b7v8c9x的博客 953

JAVA学习笔记

学习记录。

Mr_Wang_YF的博客 257

影刀初级考试操作题2

题型E主要考察了Excel的操作,总体上难度不大,流程也很少。有些细节地方比如是追加还是覆盖,多试几次就没问题了。

weisili2012的博客 1万+

深入理解Mysql索引底层数据结构与算法

索引 索引是帮助MySQL高效获取数据的排好序的数据结构(容易忽略的点:排好序)(形象点就是教科书的目录) 索引存储在文件里(也就是说有IO操作) 索引结构: 这里说说在几种数据结构中,mysql为什么选择hash,B+Tree 二叉树 红黑树 hash BTree 首先,如果数据没有索引,那么我们读取数据是这样的 这里有一篇很好的硬盘存取原理文章,看完更容易理解:http...

caijunsen的博客 1万+

【即插即用模块】Transformer篇 | MICCAI 2025 | ReLULA:Q/K双ReLU线性注意力,既轻又准!

本文介绍了一种基于线性注意力的多实例学习模块(Lin-MIL),适用于高分辨率视觉任务。该模块通过转置计算顺序和使用ReLU激活,将计算复杂度从O(n²d)降至O(nd²),同时提升特征判别性。核心创新包括Q/K双ReLU激活、可学习温度系数和数值稳定归一化。实验表明,该模块在轻量化视觉任务中优于传统线性注意力和Mamba架构。代码实现了模块化矩阵乘法,支持即插即用替换ViT自注意力层。该模块特别适合计算病理学等需要处理大量图像补丁的任务。

主要更新底层视觉(去噪、超分等)相关的科研内容,形式为【论文精读】+【论文复现】 140

VC中sqlite的使用

在VC中对sqlite的使用,实现数据库大批数据的快速插入,读取数据库中的数据在ListCtrl中显示

深入理解MySQL索引:底层数据结构与算法详解

在数据库操作中,磁盘I/O是一个关键的性能瓶颈。B+Tree通过减少磁盘访问来优化这一点,特别是在处理大量数据的查询时数据库索引常常需要处理范围查询(例如,查找某个区间内的所有记录)。B+Tree的结构使得这类查询非常高效由于叶子节点之间是相互连接的,B+树非常适合快速顺序访问数据,这在数据库中是一个常见需求B+Tree的内部节点不存储数据,这使得每个节点可以存储更多的键。这种结构也更加缓存友好,因为它允许更多的节点保留在内存中,减少了磁盘访问。

likunxing的博客 1814

lwip-2.0.3

lwip是瑞典计算机科学院(SICS)的Adam Dunkels 开发的一个小型开源的TCP/IP协议栈。实现的重点是在保持TCP协议主要功能的基础上减少对RAM 的占用。

性能调优——14 深入理解MySQL索引底层数据结构与算法

索引数据结构详解、千万级数据表如何用B+树索引快速查找、聚集索引&聚簇索引&稀疏索引到底是什么?为什么DBA总推荐使用自增主键做索引?联合索引底层数据结构又是怎样的?Mysql最左前缀优化原则是怎么回事?索引失效怎么避免?

念头通达 1078

pscad IEEE标准系统搭建说明

pscad IEEE标准系统搭建说明,包含典型的IEEE 9节点、14节点、30节点、39节点和118节点系统。内含标准测试系统的原始数据和潮流结果。

复分析 阿尔福斯(Ahlfors) 第三版答案 英文

答案分为十章,有部分习题没有答案,全英文复分析阿尔福斯(Ahlfors) 第三版答案

《Mysql基础》-- 深入理解MySql索引底层数据结构与算法

深入理解MySql索引底层数据结构与算法 2021-05-16 10:59:06 一、索引本质 1、索引的本质 select * from t where col2 = 89 我们插入到数据表中的数据,有可能分布存储在磁盘不同的空间中,而我们的数据库查询的时候每次都要进行磁盘IO。当我们不加索引的时候,此时查询就是全表扫描,即一行一行的比对数据,多次进行磁盘IO,直到查询到我们所需的数据。 而我们所能想到的优化,自然是减少磁盘IO。而减少磁盘IO,我们自然是要减少数据比对的次数,.

lmj3732018的博客 9672

Mysql性能调优-高级篇(一)-深入理解Mysql索引底层数据结构与算法

深入理解 MySQL 索引的底层数据结构与算法,不仅是数据库性能优化的核心能力,更是从开发者向架构师、从程序员向高级工程师进阶的关键一步

weixin_41846452的博客 882

Mysql(一):深入理解Mysql索引底层数据结构与算法

众所众知,MySql的查询效率以及查询方式,基本上和索引息息相关,所以,我们一定要对MySql的索引有一个具体到数据底层上的认知。这一次也是借着整理的机会,和大家一起重新一下MySql的索引底层。各位看官可以各取所需,希望能够对大家有所帮助。一句话总结:索引是帮助MySQL高效获取数据的的。

qq_43602877的博客 1466
上一篇: Java后端技术栈,到底如何深入学习?
下一篇: java框架之Spring 核心框架体系结构
Javaesandyou
博客等级 码龄5年 742粉丝 563原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值