MySQL 数据库索引数据结构的选择

MySQL高级 专栏收录该内容
9 篇文章 1 订阅

1. MySQL 数据库索引的数据结构选择

1.1 Hash索引

在这里插入图片描述
哈希索引就是采用一定的哈希算法,将键值通过hash(key)算法转化为数组的桶下标(比如John Smith转换为152,LisaSmith转化为001等),查找时不需要像B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。

从上面的图来看,B+树索引和哈希索引的明显区别是:

如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;当然了,这个前提是,键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,直到找到相应的数据;

不适合做范围查询,因为原先是有序的键值,经过哈希算法后,有可能变成不连续的了(比如原来John Smith和Lisa Smith原来是在一起的,但是经过hash算法后就变成了001 和152)

无法利用索引完成排序和分组

哈希索引也不支持多列联合索引的最左匹配规则

在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题

InnoDB索引是存放在硬盘上的,如果使用hash存储的话需要将所有的数据文件加载至内存,比较耗费内存空间。但是Memory存储引擎的索引是存在内存中的,因此可以使用Hash索引,而且默认使用的也是hash索引。

1.2 二叉搜索树 & 平衡二叉树

我们为user 表(用户信息表)建立了一个二叉查找树的索引。图中的圆为二叉查找树的节点,节点中存储了键(key)和数据(data)。键对应 user 表中的id(主键),数据对应 user表中的行数据。(数据库表中一行的记录,这里只有id、name字段,如果还有age、weight等字段也在data中)

在这里插入图片描述

查找 id=12 的用户信息,只需要 3 次即可找到匹配的数据。如果在表中一条条的查找的话,我们需要 6 次才能找到。二叉树可以提高我们的查询效率,但是如果上面的二叉查找树是这样的构造:
在这里插入图片描述

二叉树退化成了链表,相当于全表扫描。 原因其实是二叉查找树变得不平衡了。平衡二叉树:在满足二叉查找树特性的基础上,要求每个节点的左右子树的高度差不能超过 1

在这里插入图片描述

为了保证这棵树是一颗平衡树,在进行数据插入的时候,需要进行N多次的左旋和右旋操作,因此插入的性能比较低,但是查询的时候性能比较高,损失了插入性能满足了查询性能;

红黑树和AVL树类似,都是在进行插入和删除操作时通过特定操作保持二叉查找树的平衡,从而获得较高的查找性能。但红黑树不追求绝对平衡,而是相对平衡,保证每次插入最多只需要三次旋转就能达到平衡;

在这里插入图片描述

索引和数据都是存放在磁盘上的,索引不但能帮我们提升查询效率还能够减少IO次数,索引的数据结构有多种,选择哪种数据结构还要使用磁盘I/O次数评价索引结构的优劣,如何理解磁盘IO次数?

磁盘和内存之间是通过页进行数据交换的:

为了尽量减少I/O操作,计算机系统一般采取磁盘预读的方式,预读的长度一般为页(page)的整倍数。页是计算机管理存储器的逻辑块,硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块,每个存储块称为一页(在许多操作系统中,页的大小通常为4k),主存和磁盘以页为单位交换数据。

计算机系统是分页读取和存储的,一般一页为4KB,每次读取和存取的最小单元为一页,而磁盘预读时通常会读取页的整倍数。根据【局部性原理】①当一个数据被用到时,其附近的数据也通常会马上被使用。②程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高(不需要寻道时间,只需很少的旋转时间),所以即使只需要读取一个字节,磁盘也会读取一页的数据。

一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数 。

不管用什么二叉树,最终都会导致树的深度越来越深,树的深度越深IO次数就会增加,读取效率就会降低,为什么?

因为内存的易失性,一般情况下,我们都会选择将表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比,从磁盘中读取数据的速度会慢很多,所以,我们应当尽量减少从磁盘中读取数据的次数。

另外,从磁盘中读取数据时,都是按照磁盘块来读取的,并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中,那一次磁盘读取操作就会读取更多数据,那我们查找数据的时间也会大幅度降低。

如果我们用树这种数据结构作为索引的数据结构,那我们每查找一次数据就需要从磁盘中读取一个节点,也就是我们说的一个磁盘块 ,平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么?说明每个磁盘块仅仅存储一个键值和数据!那如果我们要存储海量的数据呢?

可以想象到二叉树的节点将会非常多,高度也会极其高,我们查找数据时也会进行很多次磁盘 IO,我们查找数据的效率将会极低!

如果使用二叉树这种数据结构,每一个磁盘块只会存储一个键值和数据,并不能填满一页上的所有内容,那多余的内容岂不是要浪费了?我们怎么才能把浪费的这部分内容利用起来呢?为了解决平衡二叉树的这个弊端,我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是B 树。

数据库系统巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页(16K)

1.3 B 树(多路平衡搜索树)

磁盘块存储的数据: ① 键值(主键id)② 数据(数据库表整行数据)③ 指向子节点的指针

在这里插入图片描述

图中的每个节点称为页,页就是我们上面说的磁盘块,在 MySQL 中数据读取的基本单位都是页,所以我们这里叫做页更符合 MySQL 中索引的底层数据结构。

从上图可以看出,B 树相对于平衡二叉树,每个节点存储了更多的键值(key)和数据(data),并且每个节点拥有更多的子节点,子节点的个数一般称为阶,上述图中的 B 树为 3 阶 B 树,高度也会j降低。因此B 树查找数据读取磁盘的次数将会减少,数据的查找效率也会比平衡二叉树高很多。

假如我们要查找 id=28 的用户信息,那么我们在上图 B 树中查找的流程如下:

  1. 先找到根节点页 1,判断 28 在键值 17 和 35 之间,根据页 1 中的指针 p2 找到页 3。(1次IO)

  2. 将 28 和页 3 中的键值相比较,28 在 26 和 30 之间,根据页 3 中的指针 p2 找到页 8。(1次IO)

  3. 将 28 和页 8 中的键值相比较,发现匹配的键值 28,键值 28 对应的用户信息为(28,bv)。(1次IO)

1.4 B + 树

在这里插入图片描述

根据上图我们来看下 B+ 树和 B 树有什么不同:

① B+ 树非叶子节点上是不存储数据的,仅存储键值,而 B 树节点中不仅存储键值,也会存储数据。

之所以这么做是因为在数据库中页的大小是固定的,InnoDB 中页的默认大小是 16KB。如果不存储数据,那么就会存储更多的键值,相应的树的阶数(节点的子节点树)就会更大,树就会更矮更胖,如此一来我们查找数据进行磁盘的 IO 次数又会再次减少,数据查询的效率也会更快。

另外,如果我们的 B+ 树一个节点可以存储 1000 个键值,那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。一般根节点是常驻内存的(第一次检索根节点不用读取磁盘),所以一般我们查找 10 亿数据,只需要 2 次磁盘 IO。

② B+ 树索引的所有数据均存储在叶子节点,而且数据是按照顺序排列的。

**B+ 树使得范围查找,排序查找,分组查找以及去重查找变得异常简单。**而 B 树因为数据分散在各个节点,要实现这一点是很不容易的。 B+ 树中各个页之间是通过双向链表连接的,叶子节点中的数据是通过单向链表连接的。

通过上图可以看到,在 InnoDB 中,数据页之间通过双向链表连接,叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中,B+ 树索引的叶子节点并不存储数据,而是存储数据的文件地址。

2. 聚簇索引和非聚簇索引

在 MySQL 中,B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。

**① 聚集索引(聚簇索引):**以主键作为键值而构建的 B+ 树索引, 称之为聚集索引。

② 非聚集索引(非聚簇索引):以主键以外的列值作为键值构建的 B+ 树索引, 称之为非聚集索引。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据,而是存储该列对应的主键,想要查找数据我们还需要根据主键再去聚集索引中进行查找,这个再根据聚集索引查找数据的过程,我们称为回表

2.1 利用聚集索引查找数据

在这里插入图片描述

现在假设我们要查找 id>=18 并且 id<40 的用户数据。对应的 sql 语句为:

select * from user where id>=18 and id <40

其中 id 为主键,具体的查找过程如下:

一般根节点都是常驻内存的,也就是说页 1 已经在内存中了,此时不需要到磁盘中读取数据,直接从内存中读取即可。

从内存中读取到页 1,要查找这个 id>=18 and id <40 或者范围值,我们首先需要找到 id=18 的键值。

从页 1 中我们可以找到键值 18,此时我们需要根据指针 p2,定位到页 3。

要从页 3 中查找数据,我们就需要拿着 p2 指针去磁盘中进行读取页 3。

从磁盘中读取页 3 后将页 3 放入内存中,然后进行查找,我们可以找到键值 18,然后再拿到页 3 中的指针 p1,定位到页 8。

同样的页 8 页不在内存中,我们需要再去磁盘中将页 8 读取到内存中。

将页 8 读取到内存中后。因为页中的数据是链表进行连接的,而且键值是按照顺序存放的,此时可以根据二分查找法定位到键值 18。

此时因为已经到数据页了,此时我们已经找到一条满足条件的数据了,就是键值 18 对应的数据。

因为是范围查找,而且此时所有的数据又都存在叶子节点,并且是有序排列的,那么我们就可以对页 8 中的键值依次进行遍历查找并匹配满足条件的数据。

我们可以一直找到键值为 22 的数据,然后页 8 中就没有数据了,此时我们需要拿着页 8 中的 p 指针去读取页 9 中的数据。

因为页 9 不在内存中,就又会加载页 9 到内存中,并通过和页 8 中一样的方式进行数据的查找,直到将页 12 加载到内存中,发现 41 大于 40,此时不满足条件。那么查找到此终止。

最终我们找到满足条件的所有数据,总共 12 条记录:

(18,kl), (19,kl), (22,hj), (24,io), (25,vg) , (29,jk), (31,jk) , (33,rt) , (34,ty) , (35,yu) , (37,rt) , (39,rt) 。

下面看下具体的查找流程图:

在这里插入图片描述

2.2 利用非聚集索引查找数据

在这里插入图片描述

在叶子节点中,不再存储所有的数据了,存储的是键值和主键。对于叶子节点中的 x-y,比如 1-1。左边的 1 表示的是索引的键值,右边的 1 表示的是主键值。

比如下面的数据库表:

select * from user where age=33
idnameage
1zs23
2ls7

查找的流程跟聚集索引一样,比如我们想要找到age=33的用户信息,需要找到键值33,进而可以知道键值 age=33时,存储的数据为47即主键为47,找到主键后我们需要再到聚集索引中查找具体对应的数据信息,此时又回到了聚集索引的查找流程。

在这里插入图片描述

在 MyISAM 中,聚集索引和非聚集索引的叶子节点都会存储数据的文件地址。

3. 主键索引和非主键索引

非主键索引的叶子节点存放的是主键的值,而主键索引的叶子节点存放的是整行数据,其中非主键索引也被称为二级索引,而主键索引也被称为聚簇索引。

如果查询语句是 select * from table where ID = 100,即主键查询的方式,则只需要搜索 ID 这棵 B+树。

如果查询语句是 select * from table where k = 1,即非主键的查询方式,则先搜索k索引树,得到ID=100,再到ID索引树搜索一次,这个过程也被称为回表。

因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降。

Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。

4. MySQl存储引擎

① InnoDB支持事务,MyISAM不支持

② InnoDB支持外键,而MyISAM不支持。

③ InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和 索引绑在一起的 ,必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。

MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说:InnoDB的B+树主键索引的叶子节点就是数据文件,辅助索引的叶子节点是主键的值;而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

④ InnoDB支持表、行(默认)级锁,而MyISAM支持表级锁

⑤ InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键),而Myisam可以没有

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

打赏
文章很值,打赏犒劳作者一下
<span style="font-size:14px;">一、课程简介</span><br /><p> <span style="font-size:14px;"><span style="color:#E56600;">『Java学习指南系列』</span>的第8篇教程 ,介绍MySQL数据库的安装使用,常见SQL语句,以及使用JDBC进行数据库开发的技术。</span> </p> <p> <span style="font-size:14px;"><br /></span> </p> <p> <span style="font-size:14px;">二、主要内容 </span> </p> <p> <span style="font-size:14px;">* MySQL的安装和使用</span> </p> <p> <span style="font-size:14px;">* 常用SQL语句,增删改查操作</span> </p> <p> <span style="font-size:14px;">* 高级SQL查询,多表关联查询</span> </p> <p> <span style="font-size:14px;">* 表的设计:主键,索引,内连接外连接,外键</span><span style="font-size:14px;"> </span> </p> <p> <span style="font-size:14px;">* MySQL配置管理: 用户授权,远程访问,备份还原</span> </p> <p> <span style="font-size:14px;">* JDBC连接: 使用JDBC连接数据库,执行增删改查操作</span> </p> <span style="font-size:14px;">* 基于JDBC数据库框架的设计(afsql框架),以此阐述数据库框架的设计原理</span><br /><p> <span style="font-size:14px;">* JDBC连接池,JDBC数据库开发的必备技术</span> </p> <p> <span style="font-size:14px;">* 数据库事务 Transaction 的支持 </span> </p> <p> <span style="font-size:14px;">* SQL的转义,数据库注入攻击的预防</span> </p> <p> <span style="font-size:14px;">* 数据库的动态访问:获取数据库和表的结构,根据表的结构自动生成POJO</span> </p> <p> <span style="font-size:14px;"><br /></span> </p> <p> <span style="font-size:14px;">三、课程体系</span> </p> <p> <span style="font-size:14px;color:#E56600;">〖Java学习指南</span><span style="font-size:14px;color:#E56600;">系列</span><span style="font-size:14px;"><span style="color:#E56600;">〗</span>:包含入门与进阶语法,Swing桌面开发,安卓开发,JavaFX开发,网络通信Socket,数据结构与算法等课程。</span> </p> <p> <span style="font-size:14px;color:#E56600;">〖网站开发</span><span style="font-size:14px;color:#E56600;">系列</span><span style="font-size:14px;"><span style="color:#E56600;">〗</span>:包含网页基础、网站入门、数据库、网站中级、FreeMarker、网站高级、项目应用、MyBatis、Redis等课程。</span> </p>
相关推荐
<p> <span style="font-size:14px;color:#E53333;">限时福利1:</span><span style="font-size:14px;">购课进答疑群专享柳峰(刘运强)老师答疑服务</span> </p> <p> <br /> </p> <p> <br /> </p> <p> <span style="font-size:14px;"></span> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>为什么需要掌握高性能的MySQL实战?</strong></span> </p> <p> <span><span style="font-size:14px;"><br /> </span></span> <span style="font-size:14px;">由于互联网产品用户量大、高并发请求场景多,因此对MySQL的性能、可用性、扩展性都提出了很高的要求。使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能,也是衡量一个程序员能力和薪资的标准之一。</span> </p> <p> <br /> </p> <p> <span style="font-size:14px;">为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了</span><span style="font-size:14px;">「高性能 MySQL 知识框架图」</span><span style="font-size:14px;">,帮你梳理学习重点,建议收藏!</span> </p> <p> <br /> </p> <p> <img alt="" src="https://img-bss.csdnimg.cn/202006031401338860.png" /> </p> <p> <br /> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>【课程设计】</strong></span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;">课程分为四大篇章,将为你建立完整的 MySQL 知识体系,同时将重点讲解 MySQL 底层运行原理、数据库的性能调优、高并发、海量业务处理、面试解析等。</span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;"></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>一、性能优化篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括经典 MySQL 问题剖析、索引底层原理和事务与锁机制。通过深入理解 MySQL索引结构 B+Tree ,学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引,从而彻底掌握索引的使用和优化技巧,能够避开很多实战中遇到的“坑”。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>二、MySQL 8.0新特性篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括窗口函数和通用表表达式。企业中的许多报表统计需求,如果不采用窗口函数,用普通的 SQL 语句是很难实现的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>三、高性能架构篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括主从复制和读写分离。在企业的生产环境中,很少采用单台MySQL节点的情况,因为一旦单个节点发生故障,整个系统都不可用,后果往往不堪设想,因此掌握高可用架构的实现是非常有必要的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>四、面试篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">程序员获得工作的第一步,就是高效的准备面试,面试篇主要从知识点回顾总结的角度出发,结合程序员面试高频MySQL问题精讲精练,帮助程序员吊打面试官,获得心仪的工作机会。</span> </p>
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页

打赏

小小茶花女

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值