【MySQL】 索引(上)

文章目录

  • 1. 索引的概念
  • 2. MySQL与磁盘 的交互基本单位
  • 3. 建立共识
  • 4. 现象与结论
    • 如何理解mysql中page概念
    • 为什么 要采用page的方案 进行交互 而不是用多少加载多少?
  • 5. 页目录
    • 为什么要引入 页目录概念
    • 单页情况
    • 多页情况
    • 使用B+树 构建索引
    • 为什么不用其他数据结构
    • 为什么采用B+树,而不采用B树?
  • 6. 聚簇索引 和 非聚簇索引

1. 索引的概念

索引:提高数据库的性能 只需执行 正确的 create index ,查询速度就可以提高 百上千倍
但查询速度的提高,是以 插入 、更新、删除 的速度为代价的,增加了大量的IO

索引的价值 ,在于提高一个海量数据的检索速度

索引是 在内存中 以特定的数据结构 组织的 一种结构


2. MySQL与磁盘 的交互基本单位

mysql 是一个应用层软件,在系统角度 是一个应用进程,在网络角度 属于一个应用层的服务

磁盘与操作系统之间 ,是按照4KB为单位进行IO交互

为了提高IO效率,所以MySQL的IO基本单位 是16KB
但按照常识来说,MySQL是应用层服务 是不可能访问硬件的
所以16KB是站在MySQL角度,向操作系统提出来的,操作系统内部存在文件缓冲区的
由MySQL向文件缓冲区发送16KB数据,再由文件缓冲区将数据以4*4KB的方式发送给磁盘


输入 show global status like ‘innodb_page_size’;
发现innodb 对应值为16384(按照字节为单位)
1KB大小 为1024 ,所以 16384 /1024 =16KB
即 MySQL 使用 innodb 引擎 按照 16KB 进行交互


3. 建立共识

1. mysql中的数据文件,都是以page为单位(16KB)保存在磁盘中
(msyql上创建数据库基本上都是 linux 上的文件,而linux上文件需要保存在磁盘中)


2. mysql的增删查改操作,都是需要进行计算的,找到对应的插入位置
而涉及到计算,就必须有CPU参与,为了便于CPU参与,一定要将数据移动到内存中

需要先由磁盘把数据 搬到操作系统内部,在搬到mysql自己的缓冲区 buffer pool内部,用mysql自己的代码对缓冲区进行增删查改
所以数据一定是磁盘中有,内存中也有


当操作完内存数据后,以特定的刷新策略,刷新到磁盘
(在buffer pool 缓冲区中 将16KB的数据进行修改,写入 文件缓冲区中,再把对应的数据刷到磁盘中)


3. mysql服务器在内存中运行时,在服务器内部,就申请了被称为 buffer pool 的大内存空间
来进行各种缓存,即很大的内存空间


4. 为了更高的效率,一定要尽可能减少 系统和磁盘的IO次数
(一次IO数据量越大,IO次数越少,效率越高)


4. 现象与结论

如何理解mysql中page概念

1 page 大小只有16KB ,而硬件大小为512字节,所以mysql内部一定需要会存在大量的page
就决定了mysql 必须要将多个同时存在的page 进行 管理

要管理所有的mysql内的page,就需要先描述,在组织
page 不仅仅是 一个内存块,其内部还有写入 管理信息
就可以将所有的page 用链表 的形式管理起来


为什么 要采用page的方案 进行交互 而不是用多少加载多少?

多加出来的东西 ,本质是一种预加载 (提前将数据放入内存中)
预加载机制 可以有效 利用 局部性原理 ,有效减少IO次数
(page大小为16KB,传过来的数据只有10KB,剩余6KB的空间就会被提前把即将用的数据占用)


局部性原理:
把一部分数据预先加载到缓冲区里,提高整机的效率
如CPU正在访问第100行代码,未来有很大概率访问101行,
所以一旦访问到第100行就把100行附近的数据全部load到内存中


5. 页目录

为什么要引入 页目录概念

创建一张表 user,内部包含 约束为主键的id、不为空的age、不为空的name


插入时,是按照 3 4 2 5 1 进行插入的


但在查找是发现是有序的


向一个具有主键的表中 乱序插入数据 发现数据会自动排序
是谁做的,为什么这么做?

不同的page,在MySQL中,都是16KB,使用prev和next 构成双向链表
因为有主键,所以MySQL会按照主键 对数据进行排序
若没有主键,则插入的顺序 就是 查询的顺序


若表很多,则可能存在多个page
而当查询某条记录时,会将整个page 加载到内存中,就可以有效的减少IO次数

page与page之间用链式结构连接起来,而每个page内部的数据 也是基于链表的
若查找一条记录,则为线性查找,效率就会很低
所以就引入了 页目录 的概念


假设有一本C语言基础的书,想要找到指针章节
可以有两者寻找方法:
1.从头逐页向后翻,直到找到目标内容
2.通过该书的目录,从而直接找到对应的目标内容
查找目录的方案,是可以顺序找的,因为目录肯定比书的页数少,所以可以提高定位


单页情况

针对单页page 引入目录
牺牲一部分保存数据的空间,用来存储目录


该目录只有两个字段
第一个字段为 指向起始位置的key值
第二个 指针字段 为 指向记录的起始位置

假设查找 id为4的记录,在之前需要 线性 遍历 4次
而现在 则可以通过 目录2[3] ,直接定位新的起始位置,提高了效率

所以是为了方便引入目录,才使其有序的


多页情况

单个page大小是固定的,随着数据不断增大,16KB不可能存下所有的数据,必定会有多个页来存储数据


虽然可以通过多个page 进行遍历, 再通过目录快速定位数据
但是也是有问题的,page内部的效率提升了,但page间依旧需要遍历


所以将其也引入页目录

page内部什么都不储存,只保存管理的子page
字段为 起始编号 ,再通过指针 指向对应的子page
(如:编号为1,则指向 起始编号为1的子page)


使用B+树 构建索引

该数据结构为 B+树
自顶向下查找,只需要加载部分目录到内存,即可完整整个算法过程,减少了IO次数


在MySQL内 对事务 统一使用 B+ 树的方式 进行管理
整个B+树 称为 MySQL innode db下的索引结构
当建表时,就是在该结构下,进行增删查改

而整个B+树 是被构建在mysql的buffer pool缓冲区中


1.叶子节点会保存数据,而路上节点没有,非叶子节点 ,不存数据,只要目录项
非叶子节点不存数据,就意味着可以存储更多的目录项,目录页可以更多的管理更多的叶子page
这棵树 一定是 一颗 矮胖型的树,就意味着 途径上的节点 减少,找到目标所需更少的page ,IO次数也变少了

2.叶子节点全部用链表联起来
叶子节点全部用链表联起来,而非叶子节点不会用链表连接,这是B+树的特点
比较希望进行范围查找
(如:查找 20 -30 区间内的数据,只需找到20 和30 ,以20为起点 ,30作为终点,因为是有序的,所以只需遍历即可)


为什么不用其他数据结构

1. 链表是 线性遍历的,效率上是不满足条件的

2. 二叉搜索树 是 瘦高 型的 一颗树,相比于 B+树的矮胖型
从叶子节点向下遍历时,会遇到很多节点 进行多次IO,就导致效率低下
而且还存在退化问题,可能退化成线性结构

3. AVL和红黑树 虽然近似平衡 ,但相比于 B+ ,树的整体同样过高,都是自顶向下寻找,层数越高 ,IO次数越多
所以还是选择 层数相对较低的 B+ 树 提高效率

4. 理论上hash 的搜索时间复杂度为O(1),理论上是可以充当索引的
但是hash 是没办法进行区间式的查找,会增加访问的成本


为什么采用B+树,而不采用B树?

B树 每一个节点 都对应一个配置,其配置内部 既包含 目录项 又包含数据
所以B树除了叶子节点包含数据外,路上节点也会包含数据,而且所有的叶子节点没有用链式结构连接起来


B+树 的 非叶子节点不存储 数据,数据全部都在叶子节点,并且所有的叶子节点全部都用链式结构连接起来

MySQL 认为 若在磁盘块中添加数据,就认为单个配置中 能够保存的目录项就会变少
(一个节点 既保存数据,又保存目录项, 其目录项肯定会比 只保存目录项的节点 少)

意味着 一个目录 所管理的子目录 变少了,所以 B树 相比较 B+树 会更高一些,IO的效率就会变低
( 拥有的目录项的数量是相同的,每个路上节点保存的目录项变少了, 经过的节点就会变多,整颗树就会变高)

B+树的叶子节点是链式连接的,所以只需找到对应范围的临界点位置 即可查询整个范围
而B树的叶子节点不是链式连接的,就意味进行范围查找时 每次都需要 重新遍历 B 树
所以对于范围查找,B树效率低下


6. 聚簇索引 和 非聚簇索引

将所有的数据全部放入 叶子节点 的存储引擎 为 innodb

除了 innodb的存储引擎 还有 MyISAM 的存储引擎

MyISAM 底层 采用 B+树 作为索引结果


叶子节点 存储的是 所对应的主键 记录的地址

通过B+树搜索 找到对应的数据,再根据数据对应的叶子节点 找到记录的地址

把 B+树 和 数据本身 分离的存储 方案 称为 非聚簇索引

MyISAM 特点为 将 索引 page 和 数据 page 分离 即叶子节点没有数据存在,只有对应的数据地址


在终端1中,输入 create database index_db 创建 index_db 库


创建 终端2, 使用 cd /var/lib/mysql ,进入该路径

当前路径下,存在 index_db目录


在终端1中的index_db库中,创建test1表,其中包含 约束为主键的 id、不为空的name,存储引擎为 innodb


在终端2中 发现index_db中 共有两个文件
test1.frm 为 表结构
test1.idb 为索引和数据结构


在终端1中的index_db库中,创建test2表,其中包含 约束为主键的 id、不为空的name,存储引擎为 myisam


在终端2中 发现index_db中 共有三个文件
test2.frm 为 表结构
test2.MYD 为 存储的数据
test2.MYI 为 索引结构

所以就可以验证 innodb 是聚簇索引 ,而myisam 为 非聚簇索引


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

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Classifier-Free Guidance

1.为什么需要分类引导 顾名思义,在原来扩散模型的基础上加上一个引导,让扩散模型朝着我们想要的方向去生成图像 从上图可以了解到生成下一张图像是有分类器参与的 无分类器就是这种形式要参与下一张图像的生成

SQL server数据库端口访问法

最近数据库连接&#xff0c;也是无意中发现了这个问题&#xff0c;数据库可根据端口来连接 网址:yii666.com< 我用的是sql2014测试的&#xff0c;在安装其他程序是默认安装了sql(sql的tcp/ip端口为xxx)&#xff0c;服务也不相同&#xff0c;但是由于比较不全&#xff0c;我…

ElementUI 自定义 Tree 树形控件背景

在 template 中 <div class"container"><el-tree :data"treeList" :props"defaultProps" accordion node-click"handleNodeClick" /> </div> 在 script 中 treeList: [{ id: "-1", label: "区域选…

oracle (8)Managing Tablespace Data File

目录 一、基础知识 1、表空间和数据文件 2、存储层次结构摘要 3、表空间的类型 4、表空间中的空间管理 5、临时表空间 6、Default Temporary TS 默认临时TS 二、常用实操 1、Creating Tablespaces创建表空间 2、Dictionary-Managed TS 字典管理的表空间 3、Locally …

uniapp 关于 video 组件的缩放比例问题

在 container 样式的 padding-bottom 设置比例值 9/16 比例值&#xff1a;56.25% 3/4 比例值&#xff1a;75% <view class"container"><video class"video-box" src"xxx.mp4" /> </view> .container {position: relative;wid…

与AI对话的艺术:如何优化Prompt以获得更好的响应反馈

前言 在当今数字化时代&#xff0c;人工智能系统已经成为我们生活的一部分。我们可以在智能助手、聊天机器人、搜索引擎等各种场合与AI进行对话。然而&#xff0c;要获得有益的回应&#xff0c;我们需要学会与AI进行有效的沟通&#xff0c;这就涉及到如何编写好的Prompt。 与…

设计模式之观察者模式

文章目录 一、介绍二、实现思路三、基本角色四、案例1. 不使用观察者模式2. 使用观察者模式 五、java中的观察者模式六、spring中的观察者模式七、优缺点 一、介绍 观察者模式(Observer Pattern)&#xff0c;又称监听器模式(Listener Pattern) 或 发布-订阅模式(Publish-Subsc…

OpenFeign的简单介绍和功能实操

前言 本文主要做一下OpenFeign的简单介绍和功能实操&#xff0c;实操主要是OpenFeign的超时和重试&#xff0c;在阅读本文章前&#xff0c;请完成《Nacos 注册中心介绍与实操》内的Nacos多模块生产消费者项目 什么是OpenFeign OpenFeign全名Spring Cloud OpenFeign&#xff…

树结构及其算法-二叉查找树

目录 树结构及其算法-二叉查找树 C代码 树结构及其算法-二叉查找树 二叉树在建立的过程中是根据“左子树 < 树根 < 右子树”的原则建立的&#xff0c;因此只需从树根出发比较键值即可&#xff0c;如果比树根大就往右&#xff0c;否则往左而下&#xff0c;直到相等就找…

基于OR-Tools的装箱问题模型求解(PythonAPI)

装箱问题 一、背包问题&#xff08;Knapsack problem&#xff09;1.1 0-1背包模型基于OR-Tools的0-1背包问题求解&#xff08;PythonAPI&#xff09;导入pywraplp库数据准备声明MIP求解器初始化决策变量初始化约束条件目标函数调用求解器打印结果 1.2 多重背包问题&#xff08;…

diffusers-Load pipelines,models,and schedulers

https://huggingface.co/docs/diffusers/using-diffusers/loadinghttps://huggingface.co/docs/diffusers/using-diffusers/loading 有一种简便的方法用于推理是至关重要的。扩散系统通常由多个组件组成&#xff0c;如parameterized model、tokenizers和schedulers&#xff0c…

SpringBoot整合自签名SSL证书,转变HTTPS安全访问(单向认证服务端)

前言 HTTP 具有相当优秀和方便的一面,然而 HTTP 并非只有好的一面&#xff0c;事物皆具两面性&#xff0c;它也是有不足之处的。例如&#xff1a; 通信使用明文&#xff08;不加密&#xff09;&#xff0c;内容可能会被窃听。不验证通信方的身份&#xff0c;因此有可能会遭遇…

多线程锁的升级原理是什么

在 Java 中&#xff0c;锁共有 4 种状态&#xff0c;级别从低到高依次为&#xff1a;无状态锁&#xff0c;偏向锁&#xff0c;轻量级锁和重量级锁状态&#xff0c;这几个状态会随着竞争情况逐渐升级。锁可以升级但不能降级。 多线程锁锁升级过程 如下图所示 多线程锁的升级过程…

Ha-NeRF源码解读 train_mask_grid_sample

目录 背景&#xff1a; &#xff08;1&#xff09;Ha_NeRF论文解读 &#xff08;2&#xff09;Ha_NeRF源码复现 &#xff08;3&#xff09;train_mask_grid_sample.py 运行 train_mask_grid_sample.py解读 1 NeRFSystem 模块 2 forward()详解 3 模型训练tranining_st…

windows和docker环境下springboot整合gdal3.x

链接: gdal官网地址 gdal gdal的一个用c语言编写的库&#xff0c;用于处理地理信息相关的数据包括转换&#xff0c;识别数据&#xff0c;格式化数据以及解析 同时提供第三方语言的SDK包括python&#xff0c;java上述需要编译后使用 java是需要使用jni接口调用实现方法在wind…

SAR 系统基本原理

目录 1.真实孔径雷达 2.合成孔径雷达 本文由CSDN点云侠原创&#xff0c;爬虫网站请自重。 1.真实孔径雷达 RADAR 中文名称雷达&#xff0c;是 Radio Detection And Ranging&#xff08;无线电探测与定位&#xff09;的缩写。雷达发射机产生足够的电磁能量&#xff0c;经过收发…

人工智能-多层感知机

隐藏层 该模型通过单个仿射变换将我们的输入直接映射到输出&#xff0c;然后进行softmax操作。 如果我们的标签通过仿射变换后确实与我们的输入数据相关&#xff0c;那么这种方法确实足够了。 但是&#xff0c;仿射变换中的线性是一个很强的假设。 线性模型可能会出错 例如&…

MySQL8.0安装

安装过程 一.官网下载离线tar包二.安装MySQL三.验证 一.官网下载离线tar包 点击此处下载 链接&#xff1a;https://pan.baidu.com/s/1_p2esJax95Ow39wfgM_paw 提取码&#xff1a;g415 –来自百度网盘超级会员V2的分享 二.安装MySQL 1.上传到/usr/local目录下 2.解压安装包…

Tigger绕过激活锁/屏幕锁隐藏工具,支持登入iCloud有消息通知,支持iOS12.0-14.8.1。

绕过激活锁工具Tigger可以用来帮助因为忘记自己的ID或者密码而导致iPhone/iPad无法激活的工具来绕过自己的iPhone/iPad。工具支持Windows和Mac。 工具支持的功能&#xff1a; 1.Hello界面两网/三网/无基带/乱码绕过&#xff0c;可以完美重启&#xff0c;支持iCloud登录、有消…

香港高端人才通行证计划入围高校/全球百强大学综合名单公布!

香港高端人才通行证计划入围高校/全球百强大学综合名单公布&#xff01; 香港高才通计划希望吸引世界各地具备丰富工作经验及高学历的人才到香港探索机遇&#xff0c;这些高端人才包括高收入人士和在世界顶尖大学毕业的学生。 此计划并不适用于阿富汗、古巴、老挝、朝鲜、尼泊尔…