MYSQL06高级_为什么使用索引、优缺点、索引的设计、方案、聚簇索引、联合索引

文章目录

  • ①. 为什么使用索引
  • ②. 索引及其优缺点
  • ③. InnoDb - 索引的设计
  • ④. InnoDb中的索引方案
  • ⑤. 索引 - 聚簇索引
  • ⑥. 索引 - 二级索引

①. 为什么使用索引

  • ①. 索引是存储引擎用于快速找到数据记录的一种数据结构,就好比去图书馆找书,或者新华字典里找字,相当于一个目录,可以帮助我们快速的查找到数据所在的位置

  • ②. 在MySQL中也是同样的道理,进行数据查找时,首先看查询条件是否命中索引,符合则通过索引查找相关数据,如果不符合则需要全表扫描,即一条一条的查找记录,直到找到与条件符合的记录
    在这里插入图片描述

  • ③. 假如给数据使用二叉树这样的数据结构进行存储,如下图所示
    建立索引目的是为了减少磁盘I/O次数,加快查询效率
    在这里插入图片描述

②. 索引及其优缺点

  • ①. MySQL官方对索引的定义为:索引是帮助MySQL高效获取数据的数据结构

  • ②. 本质:索引是数据结构,可以理解为“排好序的快速查找数据结构

  • ③. 索引是在存储引擎中实现的,因此每种存储引擎的索引不一定完全相同,并且每种存储引擎不一定支持所有索引类型

  • ④. 优点

  1. 提高数据检索效率,降低数据库的IO成本
  2. 通过创建唯一索引,可以保证数据库表中每一行数据的唯一性
  3. 在实现数据的参考完整性方面,可加速表和表之间的连接。换句话说,对于有依赖关系的子表和父表联合查询时,可以提高查询效率
  4. 在使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间,降低了CPU的消耗
  • ⑤. 缺点
  1. 创建索引和维护索引要消耗时间,并且随着数据量的增加,所耗费的时间也会增加
  2. 索引需要占用磁盘空间,除了数据表占数据空间外,每一个索引还要占一定的物理空间,存储在磁盘上,如果有大量的索引,索引文件就可能比数据文件更快达到最大文件尺寸
  3. 虽然索引大大提高了查询速度,同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度

③. InnoDb - 索引的设计

  • ①. 首先,建一个表:
    这个新建的index_demo表有2个INT类型的列,1个CHAR类型的列,而且规定了c1位逐渐,这个表使用Compact行格式来实际存储记录,行格式之后会学习到。以下是简化了行格式的示意图:
    在这里插入图片描述
CREATE TABLE index_demo(
	c1 INT,
	c2 INT,
	c3 CHAR(1),
	PRIMARY KEY (c1)
) ROW_FORMAT = Compact;
  • ②. 我们只在示意图中展示记录这几个部分
  1. record_type:记录头信息的一项属性,表示记录的类型,0表示普通记录、2表示最小记录、3表示最大记录、1是目录项
  2. next_record:记录头信息的一项属性,表示下一条地址相对于本条记录的地址偏移量,我们用箭头来表明下一条记录是谁。可以理解为链表
  3. 各个列的值:这里只记录在index_demo表中的三个列,分别是c1,c2,c3
  4. 其他信息:除了上述3种信息以外的所有信息,包括其他隐藏列的值以及记录的额外信息

在这里插入图片描述在这里插入图片描述

  • ③. 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值
  1. 假设:每一个数据页最多能存放三条记录,实际上一个数据页非常大,可以存放好多记录。向表中插入3条记录,填充数据页
INSERT INTO index_demo VALUES(1,4,'u'),(3,9,'d'),(5,3,'y');
  1. 那么这些记录已经按照主键值的大小串联成一个单向链表了
    在这里插入图片描述3. 那这时候我们插入一条主键为4的记录,这个数据页已经显示不下了,只能新建一个数据页,而且因为4 < 5,所以这条记录应该保存在页10中,把主键为5的记录移动到下一个页中,这个过程叫页分裂
    在这里插入图片描述
  • ④. 给所有的页建立一个目录项 - 由于数据页的编号可能是不连续的,所以插入了多条记录后,可能会出现以下的情况
    在这里插入图片描述
  • ⑤. 因为这些数据页在物理存储上是不连续的,所以如果想从这么多页中根据主键值快速定位某些记录所在的位置,我们需要给他们做一个目录,每一个页对应一个目录项,每个目录项包括下边两个部分:
  1. 比如:查找主键值为20的记录,具体查找过程分两步:
  2. 先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中因为12 < 20 <209),它对应的页 - 是页9
  3. 再根据前边说的在页中查找记录的方式去页9中定位具体的记录
    在这里插入图片描述
  • ⑥. 针对数据页做的简易目录就搞定了。这个目录有一个别名,称为索引

④. InnoDb中的索引方案

  • ①. 迭代1次:目录项纪录的页 - 我们把前边使用到的目录项放到数据页中的样子就是这样
    在这里插入图片描述
  • ②. 从图中可以看出来,我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调目录项记录和普通的用户记录的不同点:
  1. 目录项记录的record_type值是1,而普通用户记录的record_type值是0
  2. 目录项记录只有主键值和页的编号两个列,而普通的用户记录的列是用户自己定义的,可能包含很多列 ,另外还有InnoDB自己添加的隐藏列
  3. 了解:记录头信息里还有一个叫min_rec_mask的属性,只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1 ,其他别的记录的min_rec_mask值都是0
  • ③. 相同点:两者用的是一样的数据页,都会为主键值生成 Page Directory 页目录,从而在按照主键值进行查找时可以使用二分法来加快查询速度
  1. 现在以查找主键为 20 的记录为例,根据某个主键值去查找记录的步骤就可以大致拆分成下边两步:
  2. 先到存储目录项记录 的页,也就是页30中通过 二分法 快速定位到对应目录项,因为12 < 20 <209 ,所以定位到对应的记录所在的页就是页9
  3. 再到存储用户记录的页9中根据二分法 快速定位到主键值为20的用户记录
  • ④. 迭代2次:多个目录项纪录的页
    在这里插入图片描述在这里插入图片描述
  • ⑤. 迭代3次:目录项记录页的目录页
    如图,我们生成了一个存储更高级目录项的页33 ,这个页中的两条记录分别代表页30和页32,如果用户记录的主键值在 [1, 320) 之间,则到页30中查找更详细的目录项记录,如果主键值不小于320的话,就到页32中查找更详细的目录项记录
    在这里插入图片描述
  • ⑥. 我们可以用下边这个图来描述它 - 这个数据结构,它的名称是B+树

在这里插入图片描述

  • ⑦. 一个B+树的节点其实可以分成好多层,规定最下边的那层,也就是存放我们用户记录的那层为第0层,之后依次往上加。之前我们做了一个非常极端的假设:存放用户记录的页 最多存放3条记录 ,存放目录项记录的页最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的,假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录 ,所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录 ,那么
  1. 如果B+树只有1层,也就是只有1个用于存放用户记录的节点,最多能存放100条记录
  2. 如果B+树有2层,最多能存放1000×100=10,0000条记录
  3. 如果B+树有3层,最多能存放1000×1000×100=1,0000,0000条记录
  4. 如果B+树有4层,最多能存放 1000×1000×1000×100=1000,0000,0000条记录。相当多的记录
  5. 你的表里能存放100000000000条记录吗?所以一般情况下,我们用到的B+树都不会超过4层 ,那我们通过主键值去查找某条记录最多只需要做4个页面内的查找查找3个目录项页和一个用户记录页,又因为在每个页面内有所谓的Page Directory页目录,所以在页面内也可以通过二分法实现快速定位记录

⑤. 索引 - 聚簇索引

  • ①. 使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
  1. 页内的记录是按照主键的大小顺序排成一个单向链表
  2. 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
  3. 存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
  • ②. B+树的叶子节点存储的是完整的用户记录
    完整的用户记录,就是指这个记录中存储了所有列的值包括隐藏列

  • ③. 们把具有这两种特性的B+树称为聚簇索引,所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建,InnoDB存储引擎会自动的为我们创建聚簇索引

  • ④. 聚簇索引优点

  1. 数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快
  2. 聚簇索引对于主键的排序查找和范围查找速度非常快
  3. 按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省了大量的io操作
  • ⑤. 聚簇索引缺点
  1. 插入速度严重依赖插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能,因此对于InnoDB表,我们一般都会定义一个自增的ID列为主键
  2. 更新主键的代价很高,因为将会导致被更新的行移动。因此对于InnoDB表,我们一般定义主键为不可更新
  • ⑥. 限制
  1. 对于MySQL数据库,目前只有InnoDB数据引擎支持聚簇索引,而MyISAM并不支持聚簇索引
  2. 由于数据物理存储排序方式只能由有一种,所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键
  3. 如果没有定义主键,InnoDB会选择非空的唯一索引代替。如果没有这样的索引,InnoDB会隐式的定义一个主键来作为聚簇索引
  4. 为了充分利用聚簇索引的聚簇的特性,索引InnoDB表的主键列尽量选择有序的顺序id,而不建议用无序的id,比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长
  • ⑦. 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式(所有的记录都存储在了叶子节点),也就是所谓的索引即数据,数据即索引

⑥. 索引 - 二级索引

  • ①. 二级索引也被称为辅助索引、非聚簇索引,上面介绍的聚簇索引只能在搜索条件是主键时才能发挥作用,因为B+树的数据都是按照主键进行排序的
    在实际开发过程中,我们经常使用别的列作为搜索条件,如果使用该列为搜索条件的频率非常高时,我们就可以考虑使用此列创建一个二级索引,依次来提升搜索的速度
    在这里插入图片描述
  • ②. 使用记录c2列的大小进行记录和页的排序,这包括三个方面的含义
  1. 页内的记录是按照c2列的大小顺序排成一个单向链表
  2. 各个存放用户记录的页也是根据页中用户记录的c2列的大小顺序排成一个双向链表
  3. 存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的c2列的大小顺序排成一个双向链表
  • ③. B+树的叶子节点存储的并不是完整的用户记录,而只是c2列+主键这两个列的值

  • ④. 目录项记录中不再是主键+页号的搭配,而是c2列+页号的

  • ⑤. 通过二级索引搜索,需要先从二级索引的B+树找到符合条件的主键id,然后再去聚簇索引的B+树进行搜索。这里就会有同学想问了,为什么不在二级索引的B+树中存储完整的用户记录呢?
    如果把完整的用户记录放到叶子节点是可以不用回表,但是太占用地方了,相当于每创建一个索引,就将用户记录都拷贝一份,非常浪费存储空间

  • ⑥. 聚簇索引与非聚簇索引的原理不同,在使用上也有一些区别

  1. 聚簇索引的叶子节点存储的就是我们的数据记录,非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响表的物理存储数据
  2. 一个表只能由一个聚簇索引,因为只能有一种排序存储方式,但可以有多个非聚簇索引
  3. 使用聚簇索引的时候,数据的查询效率高,但如果对数据进行插入,删除,更新等操作,效率会比非聚簇索引低。这是因为修改非聚簇索引,只需要操作当前的B+树,而如果修改聚簇索引,不仅要操作当前B+树,所有非聚簇索引的叶子节点的id都需要修改
  • ⑦. 联合索引也是二级索引的一种,如果我们使用了多个列创建索引,例如先按照c2排序,c2相同按照c3排序,那么这就是一个联合索引。
    联合索引相较于二级索引,在每一个节点上存储的数据更多了,这里就画一个草图,不再赘述了

在这里插入图片描述

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

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

相关文章

Vue2 第十七节 Vue中的Ajax

1.Vue脚手架配置代理 2.vue-resource 一.Vue脚手架配置代理 1.1 使用Ajax库 -- axios ① 安装 : npm i axios ② 引入: import axios from axios ③ 使用示例 1.2 解决开发环境Ajax跨域问题 跨域&#xff1a;违背了同源策略&#xff0c;同源策略规定协议名&#xff0…

ApplicationContent 和BeanFactory的关系

选中这个类&#xff0c; ctrl alt u 从springboot的启动类说起 我们知道这个启动类返回值呢是哟个configurableApplicationContent 类型 我们查看她的类图 从图中我们可以看出&#xff0c;configurableApplicationContent是见解的继承了BeanFactory接口&#xff0c;扩展了他…

2023爱分析·信创云市场厂商评估报告:中国电子云

01 研究范围定义 信创2.0时代开启&#xff0c;信创进程正在从局部到全面、从细分到所有领域延展。在这个过程中&#xff0c;传统的系统集成,也在逐步向信创化、数字化及智能化转变。随着信创产业的发展&#xff0c;企业需要更多的技术支持和服务&#xff0c;而传统的系统集成已…

C语言 函数指针详解

一、函数指针 1.1、概念 函数指针&#xff1a;首先它是一个指针&#xff0c;一个指向函数的指针&#xff0c;在内存空间中存放的是函数的地址&#xff1b; 示例&#xff1a; int Add(int x&#xff0c;int y) {return xy;} int main() {printf("%p\n",&Add);…

uniapp引入inconfont自定义导航栏

app,h5端引入 uniapp本身的全局设置中有个iconfontsrc属性 所以只需要 1.iconfont将需要的icon添加至项目 2.下载到本地解压后,将其中的ttf文件,放在static静态目录下 3.在page.json中对全局文件进行配置tabBar(导航图标) “iconfontSrc”: “static/font/iconfont.ttf”, …

SQLAlchemy快速使用

一、介绍 sqlalchemy是一个基于python实现的orm框架&#xff0c;跟web框架无关&#xff0c;独立的。 同步orm框架&#xff1a;django的orm&#xff08;3.0以后支持异步&#xff09;、sqlalchemy&#xff08;大而重&#xff09;、peewee&#xff08;小而轻、同步和异步&#xf…

Netty 粘包半包

什么是 TCP 粘包半包&#xff1f; 假设客户端分别发送了两个数据包 D1 和 D2 给服务端&#xff0c;由于服务端一次读取到的字节 数是不确定的&#xff0c;故可能存在以下 4 种情况。 &#xff08;1&#xff09;服务端分两次读取到了两个独立的数据包&#xff0c;分别…

MyBatis查询数据库之一(概念+创建项目+基础交互)

目录 1.MyBatis是什么&#xff1f; 2.为什么学习MyBatis&#xff1f; 3. 怎么学 MyBatis 4.第⼀个MyBatis查询 4.1 添加MyBatis框架支持 4.1.1老项目添加MyBatis 4.1.2 新项目添加MyBatis 4.2 配置连接字符串和MyBatis 4.2.1 配置连接字符串 4.2.2 配置 MyBatis 中的…

python爬虫之scrapy框架介绍

一、Scrapy框架简介 Scrapy 是一个开源的 Python 库和框架&#xff0c;用于从网站上提取数据。它为自从网站爬取数据而设计&#xff0c;也可以用于数据挖掘和信息处理。Scrapy 可以从互联网上自动爬取数据&#xff0c;并将其存储在本地或在 Internet 上进行处理。Scrapy 的目标…

谈谈基因编辑技术

目录 1.基因编辑的概念 2.基因编辑技术的发展 3.基因编辑技术给人类带来的福利 1.基因编辑的概念 基因编辑是指通过人为干预&#xff0c;对生物个体的基因组进行特定的修改。这种技术使得科学家能够直接改变生物体的遗传信息&#xff0c;包括添加、删除或修改特定基因的DNA序…

为什么你一定要学会写作?

不管是写文章还是做短视频&#xff0c;也就是现在的自媒体&#xff0c;都有一个很大的好处&#xff1a; 写作是把自己的同一段时间重复销售很多很多次的极少选择之一&#xff0c;也是相对来看最容易成为普通人起步的台阶之一。 你写一篇文章&#xff0c;可能花了半天时间&…

uniapp点击图片放大预览

阐述 有些时候我们在用uniapp显示图片时&#xff0c;有的不宜全部显示到屏幕上&#xff0c;uniapp提供了一个非常好用的api。 实现方式如下&#xff1a; <template><view class"content"><image class"logo" src"/static/images/a.…

移动开发最佳实践:为 Android 和 iOS 构建成功应用的策略

您可以将本文作为指南&#xff0c;确保您的应用程序符合可行的最重要标准。请注意&#xff0c;这份清单远非详尽无遗&#xff1b;您可以加以利用&#xff0c;并添加一些自己的见解。 了解您的目标受众 要制作一个成功的应用程序&#xff0c;你需要了解你是为谁制作的。从创建…

Scala编程语言入门教程

Scala教程 方便个人学习和查阅 学习目标 Scala介绍 简介 Scala创始人Martin Odersky马丁奥德斯基 再回到我们的scala语言&#xff0c;在Scala官网https://www.scala-lang.org/介绍了其六大特征。 Java和scala可以混编 类型推测(自动推测类型) 并发和分布式&#xff08;Ac…

OLED透明屏采购指南:如何选择适合您需求的高品质产品

引言&#xff1a;OLED透明屏作为一种创新的显示技术&#xff0c;正在迅速发展并广泛应用于各个领域。 然而&#xff0c;面对市场上琳琅满目的OLED透明屏产品&#xff0c;如何选择适合自己需求的高品质产品成为了采购者们的重要任务。 对此&#xff0c;尼伽将为您提供一份OLED…

mybatis-plus的update方法,到底会不会更新null值?看这一篇就够了

普通的设置值。需要传入一个Wrapper对象 默认情况下是不会更新null值的&#xff0c;也就是只更新设置值的字段 LambdaUpdateWrapper<OrderChildRoom> orderChildRoomUpdateWrapper new LambdaUpdateWrapper<>();orderChildRoomUpdateWrapper.set(OrderChildRoo…

基于SqlSugar的开发框架循序渐进介绍- 基于MongoDB的数据库操作整合

SqlSugar的开发框架本身主要是基于常规关系型数据库设计的框架&#xff0c;支持多种数据库类型的接入&#xff0c;如SqlServer、MySQL、Oracle、PostgreSQL、SQLite等数据库&#xff0c;非关系型数据库的MongoDB数据库也可以作为扩展整合到开发框架里面&#xff0c;通过基类的继…

SPI通信协议

简介 SPI有主、从两种模式&#xff0c;通常由一个主模块和一个或多个从模块组成&#xff08;SPI不支持多主机&#xff09;&#xff0c;主 模块选择一个从模块进行同步通信&#xff0c;从而完成数据的交换。提供时钟的为主设备&#xff08;Master&#xff09;&#xff0c;接 收时…

AlamLinux 9 显示最小化按钮

AlamLinux 9 显示最小化按钮 默认情况下&#xff0c;AlamLinux 9 是没有最小化按钮的&#xff0c;在操作上会影响我们的工作效率。 想加上最小化按钮也非常简单&#xff0c;1行命令就能解决。 gsettings set org.gnome.desktop.wm.preferences button-layout ":minimize…

<dependency> idea中为什么这个变黄色

在IDE中&#xff0c;当你的代码出现黄色高亮时&#xff0c;通常表示存在警告或建议的提示。对于Maven的<dependency>标签来说&#xff0c;黄色高亮可能有以下几种原因&#xff1a; 依赖项未找到&#xff1a;黄色高亮可能表示IDE无法找到指定的依赖项。这可能是由于配置错…