图解Mysql索引原理

概述

是什么

  • 索引像是一本书的目录列表,能根据目录快速的找到具体的书本内容,也就是加快了数据库的查询速度
  • 索引本质是一个数据结构
  • 索引是在存储引擎层,而不是服务器层实现的,所以,并没有统一的索引标准,不同存储引擎的索引的工作方式不同,也不是所有的存储引擎都支持所有类型的索引,即使多个存储引擎支持同一种类型的索引,其底层实现也可能不同————《高性能mysql》

优劣势

优点:

  1. 提高数据检索的效率,降低了数据库的IO成本
  2. 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
  3. 被索引的列会自动进行排序,包括【单列索引】和【组合索引】,只是组合索引的排序要复杂一些

缺点

  1. 索引会占用磁盘空间
  2. 索引虽然提高了查询的效率,但是会影响增删改的效率,因为每次增删改数据时,数据库要同时更新维护索引的结构

数据结构

索引是存储引擎层面实现的,所以不同的存储引擎使用的索引数据结构也不同,底层结构主要是B+树和哈希两种

hash索引

基于哈希表实现的,对选中的索引列计算出一个哈希码,在哈希表存储的是哈希码以及指向每个数据行的指针(在mysql中,只有memory存储引擎支持哈希索引,且是memory的默认索引方式)
优点: 查找的速度非常快(只需存储对应的哈希值,所以索引的结构十分紧凑)
缺点:

  • 不能避免读取行。哈希表中只包含哈希值和行指针,而不存储字段值
  • 无法用于排序。哈希表的索引数据并不是按照索引列匹配查找的
  • 不支持部分索引列匹配查找。因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
  • 只支持等值比较查询,包括=,IN(),<=>,也不支持范围查找
  • 存在哈希冲突。当出现哈希冲突时,必须遍历链表中所有的行指针,逐行进行比较,直到找到所有符合条件的行。同时,当哈希冲突很多的时候,一些索引维护操作的代价也会很高。例如,如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引,那么当从表中删除一行时,存储引擎需要遍历对应哈希值的链表中的每一行,找到并删除对应行的引用,冲突越多,代价越大

B+树

默认的索引底层数据结构是B+树,B+树是一颗多叉平衡搜索树,如图:

  1. B+树的节点中存储着多个元素,每个节点内有多个分叉
  2. 叶子结点包含了所有的索引项
  3. 只有叶子结点存储数据,非叶子结点只存储索引键
  4. 叶子结点使用双向指针连接,形成了一个双向有序链表,支持范围查询
  5. 在查找数据的时候,由于数据都存放在最底层的叶子节点上,所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系

对比B树

B树也是一个平衡多叉树,结构如图:

  1. B树的节点中存储着多个元素,每个内节点有多个分叉
  2. 所有节点中的元素包含键值和数据,如果data存储的是行记录,行的大小随着列数的增多,所占空间会变大。这时,一个页中可存储的数据量就会变少,树相应就会变高,磁盘IO次数就会变大
  3. 父节点当中的元素不会出现在子节点中
  4. 叶节点之间没有指针连接,不支持范围查询

Mysql索引

MyISAM索引(非聚簇索引)

使用B+树作为索引结构,叶节点的data域存放的是数据记录的地址(主键索引和辅助索引存储的都是数据记录的地址),也叫做“非聚簇索引”,如图

  1. 主键索引非必需,若存在则主键索引必须唯一
  2. 辅助索引的结构和主键索引结构一致,可以重复,会存在多个符合条件的数据,所以即使是等值查询,也需要按照范围查询的方式在辅助索引树中检索数据
  3. 检索过程:首先按照B+树搜索算法搜索索引,如果指定的key存在,则取出其data域的值,然后以data域的值为地址,去读取相应的表数据记录

InnoDB索引

主键索引(聚簇索引)

叶子节点的data域存储的是完整的数据记录,key就是数据表的主键,也叫做“聚簇索引,如图:

  1. 检索过程:首先按照B+树搜索算法搜索索引,如果指定的key存在,则取出其data域的值即为表数据
  2. InnoDB要求必须有主键,且唯一;如果没有显示指定,mysql系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,mysql会自动为InnoDB表生成一个隐含字段作为主键,类型为long
  3. 尽量在InnoDB上采用自增字段做表的主键;因为InnoDB数据文件本身是一颗B+树,非单调的主键会造成在插入记录时数据文件为了维持B+树的特性而频繁的分裂调整,十分低效,如果表使用自增主键,那么每次插入新的记录,记录会顺序添加到当前索引节点的后续未知,当一页写满,就会自动开辟一个新的页
  4. 不推荐用uuid做主键;uuid无序,插入操作会频繁做分裂调整,而且字段更长占用的空间更大,空间一大,一页存储的索引数据就减少,就需要占用更多页,查询时的磁盘io次数会增加,影响效率

辅助索引

辅助索引的叶子结点的data域存储的是相应记录主键的值,也就是InnoDB的所有辅助索引都引用主键作为data域,当主键索引行移动或数据页分裂时,减少了辅助索引的维护工作,如图所示:

  • 检索过程:首先按照B+树搜索算法搜索索引,如果指定的key存在,则取出其data域的值即主键id,然后用主键id去主键索引树查询,找到对应的数据。这个过程中去主键索引树查询的过程叫做“回表”

联合索引和最左匹配原则

  • 联合索引是用表中的多个字段组成一个索引,比如创建一个联合索引idx_abc(a,b,c),那么该索引的每个键都包含这三个字段,且是按a,b,c依次排列
  • 联合索引的存储方式:最底层的叶子节点按照第一列a列从左到右递增排列,但是b列和c列是无序的,b列只有在a列值相等的情况下小范围内递增有序,而c列只能在a,b两列相等的情况下小范围内递增有序
  • 联合索引的检索方式:比如查询条件为where a=1 and b=28 and c=3,那么B+树会先比较a列来确定下一步应该搜索的方向,往左还是往右;如果a列相同再比较b列;但是如果查询条件没有a列,B+树就不知道第一步应该从哪个节点查起,所以这也是最左前缀匹配原则的原因
  • 最左前缀匹配原则:使用组合索引查询时,mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配
  • 用联合索引id_abc查询要符合最左匹配原则,相当于创建了(a)、(a,b)(a,b,c)三个索引
  • 联合索引的创建原则:在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面,频繁使用代表索引利用率高,区分度高代表筛选粒度大,这些都是在索引创建的需要考虑到的优化场景,也可以在常需要作为查询返回的字段上增加到联合索引中,如果在联合索引上增加一个字段而就能用到覆盖索引,那就可以加上

覆盖索引

覆盖索引并不是一种索引结构,而是一种sql优化手段。这源于辅助索引和主键索引的关键,如果只用覆盖索引那么必然要去主键索引那回表查询到需要的字段,但是如果在辅助索引树上能查询到所需的字段呢,就不需要再去主键索引上查询了呀,减少了回表就减少了磁盘io,就提升了查询速度呀

辅助索引树上有两块数据,一个是索引key,一个是data域,data域固定是主键id没法变,前面讲的联合索引表明索引key可以是多个字段组合的,那么就可以合理使用联合索引实现覆盖索引,减少回表次数,提升查询效率

⚠️使用这种手段必须是频繁查询的字段,不然没提升速度反而增加了索引结点的占用空间导致效率下降

总结

希望这些内容可以帮助你更好的理解mysql的索引,对sql优化能有更好的想法💡

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

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

相关文章

【分享】两种方法禁止修改Word文档

对于比较重要的Word文件&#xff0c;不想被随意编辑修改&#xff0c;可以试试以下两个方法&#xff0c;不清楚的小伙伴&#xff0c;一起来看看吧&#xff01; 方法1&#xff1a;设置“只读方式” 我们可以给Word文档设置以“只读方式”打开&#xff0c;这样就算编辑修改了文档…

[数据集][目标检测]焊接处缺陷检测数据集VOC+YOLO格式3400张8类别

数据集格式&#xff1a;Pascal VOC格式YOLO格式(不包含分割路径的txt文件&#xff0c;仅仅包含jpg图片以及对应的VOC格式xml文件和yolo格式txt文件) 图片数量(jpg文件个数)&#xff1a;3400 标注数量(xml文件个数)&#xff1a;3400 标注数量(txt文件个数)&#xff1a;3400 标注…

用AI工具设计赛博朋克壁纸

用AI工具设计赛博朋克壁纸 今天我要向大家分享的项目十分直观且具有高流量&#xff0c;我坚信这会对大家有所帮助。我相信大家在壁纸创作者的作品中一定看过科幻和赛博朋克的元素&#xff0c;这些深具特色的未来科技主题的壁纸影响了现代设计的方向。 设计师们巧妙地运用了几…

鸿蒙HarmonyOS实战—如何使用Video组件播放视频

1.视频播放 鸿蒙系统中&#xff0c;关于视频播放&#xff0c;可以使用上层视频组件Video。 参数如下 src 支持file:///data/storage路径前缀的字符串&#xff0c;用于读取应用沙箱路径内的资源。需要保证目录包路径下的文件有可读权限。 说明&#xff1a;视频支持的格式是&am…

在Three.js中实现模型点击高亮:整合EffectComposer与OutlinePass的终极指南

效果【后期实现鼠标点击选中轮廓后给出一个弹窗显示相应的模型信息】 标签指示线参考我的上一篇文章 引言 Three.js不仅让WebGL的3D图形编程变得简单易懂&#xff0c;还通过其强大的扩展库支持丰富的后期处理效果&#xff0c;为3D场景增添无限魅力。本篇文章将引导您深入了…

实习面试题(答案自敲)、

1、为什么要重写equals方法&#xff0c;为什么重写了equals方法后&#xff0c;就必须重写hashcode方法&#xff0c;为什么要有hashcode方法&#xff0c;你能介绍一下hashcode方法吗&#xff1f; equals方法默认是比较内存地址&#xff1b;为了实现内容比较&#xff0c;我们需要…

使用from…import语句导入模块

自学python如何成为大佬(目录):https://blog.csdn.net/weixin_67859959/article/details/139049996?spm1001.2014.3001.5501 在使用import语句导入模块时&#xff0c;每执行一条import语句都会创建一个新的命名空间&#xff08;namespace&#xff09;&#xff0c;并且在该命名…

词法分析器的设计与实现--编译原理操作步骤,1、你的算法工作流程图; 2、你的函数流程图;3,具体代码

实验原理&#xff1a; 词法分析是编译程序进行编译时第一个要进行的任务&#xff0c;主要是对源程序进行编译预处理之后&#xff0c;对整个源程序进行分解&#xff0c;分解成一个个单词&#xff0c;这些单词有且只有五类&#xff0c;分别时标识符、关键字&#xff08;保留字&a…

CentOS7配置国内清华源并安装docker-ce以及配置docker加速

说明 由于国内访问国外的网站包括docker网站&#xff0c;由于种种的原因经常打不开&#xff0c;或无法访问&#xff0c;所以替换成国内的软件源和国内镜像就是非常必要的了&#xff0c;这里整理了我安装配置的基本的步骤。 国内的软件源有很多&#xff0c;这里选择清华源作为…

终于来啦!Stable Diffusion 3将在6月12日正式开源

6月3日晚&#xff0c;著名开源大模型平台Stability AI的联合首席执行官Christian Laforte&#xff0c;在AMD的产品发布会上宣布&#xff0c;文生图模型 Stable Diffusion 3将于6月12日在Hugging Face开源权重。 本次开源的是Stable Diffusion 3的Medium模型&#xff0c;有20亿…

Python中数字比较与获取较大值的深入解析

目录 一、引言 二、Python数字类型概述 三、数字比较操作符 四、获取较大值的逻辑与实现 五、高级话题&#xff1a;使用内置函数和库 六、性能分析与优化 七、案例分析 八、总结与展望 一、引言 在编程世界中&#xff0c;数字的比较和获取较大值是基础且常见的操作。P…

Python中上下文管理器解析

文章目录 基本原理上下文管理器的工作原理自定义上下文管理器enter和exitcontextlib 模块 异常处理 Python中的上下文管理器&#xff08;Context Manager&#xff09;是一种用于管理资源的机制&#xff0c;特别是在文件操作、数据库连接和锁定等场景中非常有用。上下文管理器通…

SpringBoot中MyBatisPlus的使用

MyBatis Plus 是 MyBatis 的增强工具&#xff0c;提供了许多强大的功能&#xff0c;简化了 MyBatis 的使用。下面是在 Spring Boot 中使用 MyBatis Plus 的步骤&#xff1a; 添加依赖&#xff1a;在 Maven 或 Gradle 的配置文件中添加 MyBatis Plus 的依赖。 配置数据源&#…

Linux【安全 02】OpenSSH漏洞修复(离线升级最新版本流程)网盘分享3个安装包+26个离线依赖

OpenSSH离线升级最新版本流程 1. 漏洞信息2. 环境说明3.依赖安装3.1 在线安装3.2 离线安装 4.备份卸载4.1 备份4.2 卸载旧版本 5.安装5.1 zlib5.2 ssl5.3 openssh5.3.1 安装5.3.2 配置 6.脚本整理7.文件资源 本文仅针对CentOS7.8版本&#xff0c;其他版本未测试&#xff0c;安装…

Excel单元格格式无法修改的原因与解决方法

Excel单元格格式无法更改可能由多种原因造成。以下是一些可能的原因及相应的解决方法&#xff1a; 单元格或工作表被保护&#xff1a; 如果单元格或工作表被设置为只读或保护状态&#xff0c;您将无法更改其中的格式。解决方法&#xff1a;取消单元格或工作表的保护。在Excel中…

Hadoop伪分布式安装教程

Hadoop伪分布式安装教程 一、安装背景1.1 软件列表1.2 系统软件列表 二、安装Hadoop2.1 安装 Java 环境2.1.1 前期准备2.1.2 文件传输2.1.3 解压文件2.1.4 配置 jdk 的环境变量2.1.5 输入 java、javac、java -version 命令检验 jdk 是否安装成功 2.2 Hadoop 下载地址[hadoop](h…

一些关于深度聚类以及部分对比学习的论文阅读笔记

目录 资料SwAV问题方法方法的创新点为什么有效有什么可以借鉴的地方聚类Multi-crop 代码 PCL代码 Feature Alignment and Uniformity for Test Time Adaptation代码 SimSiam 资料 深度聚类算法研究综述(很赞&#xff0c;从聚类方法和深度学习方法两个方面进行了总结&#xff0…

基于聚类和回归分析方法探究蓝莓产量影响因素与预测模型研究附录

&#x1f31f;欢迎来到 我的博客 —— 探索技术的无限可能&#xff01; &#x1f31f;博客的简介&#xff08;文章目录&#xff09; 目录 背景数据说明数据来源思考 附录数据预处理导入包以及数据读取数据预览数据处理 相关性分析聚类分析数据处理确定聚类数建立k均值聚类模型 …

《面试笔记》——MySQL终结篇30

三大范式&#xff1f; 第一范式&#xff1a;字段具有原子性&#xff0c;不可再分&#xff08;字段单一职责&#xff09; 第二范式&#xff1a;满足第一范式&#xff0c;每行应该被唯一区分&#xff0c;加一列存放每行的唯一标识符&#xff0c;称为主键&#xff08;都要依赖主…

【Linux】深入解析动静态库:原理、制作、使用与动态链接机制

文章目录 前言&#xff1a;1. 什么是动静态库2. 动静态库的制作和使用3. 动态库的查找问题4. 理解动态库的加载4.1. 站在系统的角度理解4.2. 编址、可执行程序4.3. 动态库动态链接和加载问题 总结&#xff1a; 前言&#xff1a; 在软件开发中&#xff0c;动静态库是两种重要的…