【MySQL】深入解析索引实现原理

news/2024/5/26 20:05:15/文章来源:https://blog.csdn.net/weixin_46594796/article/details/136672404

文章目录

    • 1、索引介绍
    • 2、索引分类
      • 2.1、数据结构
        • Hash
        • B + Tree
      • 2.2、存储方式
        • 聚簇索引
        • 非聚簇索引
      • 2.3、功能特性
        • 主键索引
        • 唯一索引
        • 普通索引
      • 2.4、字段数量
        • 单列索引
        • 多列索引
    • 3、最佳实践
      • 3.1、索引覆盖
      • 3.2、回表操作
      • 3.3、最左匹配原则
      • 3.4、索引下推

1、索引介绍

对于MySQL数据库来说,有个非常重要的概念就是索引,索引的用途是:

加速我们对MySQL数据库的查询操作性能,减少磁盘IO操作。

注意这里说的是查询,而不是新增、修改、删除。索引不仅对新增、修改等操作没有性能提升,并且还会降低这类DML(非查询SQL)操作的性能,原因很简单,因为DML操作还需要我们对索引结构不断进行维护…所以索引并不是越多越好,还是有维护的成本的,所以很多情况是可以考虑不创建索引的,例如:

  • 表数据量少
  • 字段重复数据多
  • 频繁进行DML操作的表

如果把MySQL比喻成一本书籍,那么索引就如同这本书籍的目录,我们通过目录就能大概确定我们想要页码的范围,这样就大大缩短了我们查找的时间,索引就类似这个工作方式,通过B+Tree就能够知道所查询行记录所在页,大大缩短了查询的范围,减少了对磁盘IO次数,提高查询性能。例如:我们为 t_user 表的 age 字段建立了索引,此时如果执行如下SQL,就会通过索引查询,性能非常快:
在这里插入图片描述

2、索引分类

无论是网上文章还是书中,经常会提到一大堆的索引概念,例如:主键索引、聚簇索引、二级索引、辅助索引、唯一索引…大家看到这些繁琐的名词就会觉得头晕,其实他们之间的很多概念都是重复的,例如我们常说的主键索引其实就是聚簇索引,接下来我们来具体看看不同维度下索引的分类有哪些。

2.1、数据结构

Hash

在Buffer Pool缓冲中有个部分就叫做:自适应哈希索引,这个索引采用的就是Hash的数据结构,也就是KV存储。频繁访问使用的索引页在一定场景下会被InnoDB自动转换成自适应Hash索引,自适应哈希索引采用的就是Hash的数据结构,这个转换不需要人为手动操作,Hash数据结构的索引时间复杂度为O(1),性能相比于后续提到的B+Tree结构要更快,不过Hash数据结构索引存储数据是无序的,并且不支持范围查询,除了自适应哈希索引,我们也可以为表中的字段手动创建Hash索引

B + Tree

这是InnoDB存储引擎默认的索引数据结构,听名字就知道该数据结构是根据索引字段构造出一个树状结构。树的顶部就是根节点,树干部分我们称之为非叶子节点,最下方的部分就是叶子节点,B+Tree的每个节点本质都是一个页(16KB)。B+Tree的树状结构会按照索引字段值就行排序,也就是有序的,最下方叶子节点数据通过双向链表进行连接,方便相邻索引页横向检索查询。对于B+Tree数据结构,一般来说树层高3~4层,因为即使在数据量很大的情况,查询一个数据的磁盘 I/O 依然维持在 3-4次。

2.2、存储方式

聚簇索引

聚簇索引这个概念听着很唬人,其实我换个称呼你就知道它的真面目了,其实聚簇索引就是主键索引聚簇索引索引数据表数据在磁盘中的位置是一起的,也就是说可以通过聚集聚集索引字段值直接找到对应整行表记录。一张表中除了主键索引属于聚簇索引,其他所有的索引都属于非聚簇索引(后面会提到),聚簇索引还有其他称呼:

  • 聚簇索引
  • 聚集索引
  • 主键索引

接下来看看数据也中聚簇索引 B+Tree样貌(下图来自于小林coding),B+Tree中每个节点都是,每个都会对应一个页号,假如我们条件查询想要查主键索引字段值为1的数据(聚簇索引就是主键索引),就会从根节点(页38)开始,不断向下遍历,直至通过B+Tree找到叶子节点(页10),从而找到该页中记录的索引字段值为1的data,在定位记录所在哪一个页时,也是通过二分法快速定位到包含该记录的页。定位到该页后,页中记录会被页目录进行分组,在该页内通过页目录进行二分法快速定位记录所在的分组(槽号),最后在分组内进行遍历查找行记录。

这里需要注意:聚簇索引叶子节点的行记录是主键索引对应的整行记录(包含所有的字段值),其中最大、最小记录可以理解为数据页中行记录的边界。图中绿色部分为聚簇(主键)索引字段值,叶子节点的蓝色部分data为聚簇(主键)索引字段值对应的整行记录。
在这里插入图片描述

非聚簇索引

什么是非聚簇索引呢?只需要记住除了主键索引(属于聚簇索引),其他类型的索引都属于非聚簇索引非聚簇索引索引数据表数据在磁盘中的位置不是一起的(跟聚簇索引相反),用物理地址的方式维护两者的联系,非聚簇索引还有其他称呼:

  • 非聚簇索引
  • 非聚集索引
  • 辅助索引
  • 二级索引

接下来看看数据也中非聚簇索引 B+Tree样貌(下图来自于小林coding),跟上面提到的聚簇索引 B+Tree最大的区别就是叶子节点的行记录不再是索引对应的一整行内容,而是索引字段对应行记录的主键字段值

注意:图中绿色部分为非聚簇索引字段值,叶子节点的蓝色部分为非聚簇索引字段值对应行记录的主键id。

在这里插入图片描述

2.3、功能特性

主键索引

主键索引(PRIMARY KEY)是非空且唯一的,也就是说不允许重复,更不允许为空。
在这里插入图片描述

对于InnoDB存储引擎来说,任何一张表都会有主键索引,即使在表中时没有指定那个字段为主键索引,InnoDB也必须保证必须要有一个主键索引,当没有主键索引时它会按照以下规则选择:

如果没有指定主键索引,那就查找是否有非空唯一索引,认作主键索引
如果没有非空唯一索引,那 InnoDB 将自动生成一个隐式自增 id 列作为主键索引(row_id)

唯一索引

唯一索引(UNIQUE KEY)是不允许重复的,主键索引和唯一索引的区别在于不允许有空值
在这里插入图片描述

普通索引

普通索引(KEY)就是建立在普通字段上的索引,既不要求字段数据唯一,也不要求非空
在这里插入图片描述

2.4、字段数量

单列索引

单列索引顾名思义,就是通过一个列(字段)创建的索引:
在这里插入图片描述

多列索引

多列索引又叫联合索引,多个字段(字段数量 ≥ 2)联合组成的索引,实际开发过程中建议尽可能使用联合索引来代替单列索引,并且还要符合特定规则情况下才能保证索引是有效的,这个规则就是最左匹配原则,后续会提到:
在这里插入图片描述

3、最佳实践

3.1、索引覆盖

索引覆盖是比较理想的索引使用方式,具体解释就是:本次SQL查询所需要的字段数据都可以在当前索引的B+Tree叶子节点上找到,举个例子:加入有一张t_user表,表中有四个字段:id、name、age、weight,此时根据name、age字段建立了联合索引,联合索引本身就是非聚簇索引,也就是叶子节点中行记录不会包含主键id对应的整行记录,所以该联合索引构成的 B+Tree 叶子节点中行记录就大体包含了:name、age字段值,行记录对应的主键id。

此时如果查询SQL为下方语句,通过EXPLAIN查看本次查询SQL的执行计划可以看到Using Index,所需数据只需在当前索引即可全部获得,不须要再到表中获取其他字段数据,就说明本次的查询操作进行了索引覆盖
在这里插入图片描述

但是下方这个SQL的查询操作就无法采用索引覆盖,因为weight字段值在叶子节点中不存在:
在这里插入图片描述

所以当非聚簇索引的B+Tree中字段值无法满足本次查询请求,就需要去聚簇索引的B+Tree中进行进一步查找,因为聚簇索引的叶子节点行记录是整行的,这步操作有个很官方的称呼,叫做回表,所以说到这里大家也该知道为啥不建议使用select *,其中一个原因就是这类SQL绝大多数情况下都需要进行回表操作,接下来画图解释一下什么是回表操作。

3.2、回表操作

上面大致介绍过了,回表简单来说就是查询字段信息无法在非聚簇索引B+Tree的行记录中完全拿到,需要在查询一次聚簇索引B+Tree,拿到缺失的字段信息,所以只能先从非聚簇索引找到查询目标行所对应的主键id,然后根据主键id回到聚簇索引中查询整行记录,拿到想要的信息,如下图:
在这里插入图片描述

3.3、最左匹配原则

针对与多列索引(非聚簇索引)有个很重要的概念叫做最左匹配原则。简单来说就是我们的查询SQL如果想要命中联合索引,那么查询条件就要有一定的规则顺序,例如创建了一个 (a, b, c) 联合索引:

# 符合(由于优化器存在,a顺序不重要)
where a = 1where a = 1 and b = 2where a = 1 and b = 2 and c = 3# 不符合,索引失效
where b = 2where c = 3where b = 2 and c = 3

之所以需要匹配,原因就是B+Tree索引结构是有序的,根据a,b,c建立联合索引,InnoDB会从a字段开始排序,然后再是b,最后是c;所以单单通过b,c自然就无法进行排序查找。

MySQL的最左前缀原则,在匹配到范围查询时会停止匹配,比如>、<、between、like这类范围条件,并不会继续使用联合索引,例如:where a = 1 and b > 2 and c = 3,则会在每个节点依次命中ab,无法命中c。对于联合索引的建立有个技巧,就是把查询条件使用频次多的字段放到前面,这样尽可能的能够踩中最左匹配原则

3.4、索引下推

索引下推是InnoDB5.6版本诞生的优化特性,它出现就是为了减少回表次数,提高查询效率。上面讲最左匹配原则是提到过遇到范围查询条件时会停止规则匹配,例如:>、<、between、like,那么当我们对a,b建立联合索引,此时查询条件为:

select a,b,c fromwhere a > 10 and b = 100

此时只有 a 踩中了索引,假设此时查询a,b,c字段信息是需要回表,那么它并不会直接开始回表操作,它在继续回表前,先将b字段的判断条件进行一次筛选,如果不符合本次查询到行记录b字段值,那就没必要在进行回表了,这就是索引下推ICP,减少了回表次数,提高查询性能。

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

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

相关文章

【性能测试】Jmeter+InfluxDB+Grafana 搭建性能监控平台

一、背景 为什么要搭建性能监控平台&#xff1f; 在用 Jmeter 获取性能测试结果的时候&#xff0c;Jmeter自带的测试报告如下&#xff1a; 这个报告有几个很明显的缺点&#xff1a; 只能自己看&#xff0c;无法实时共享&#xff1b;报告信息的展示比较简陋单一&#xff0c;不…

linux安全--Nginx与Tomcat实现负载均衡

目录 1.实验拓扑原理图&#xff0c;前提实现全网互通 2.找到nginx的conf目录中的nginx.conf文件 3.实验效果 1.实验拓扑原理图&#xff0c;前提实现全网互通 搭建全网互通可以看https://blog.csdn.net/m0_74313947/article/details/136008513?spm1001.2014.3001.5501 搭建N…

尚硅谷SpringBoot3笔记 (二) Web开发

Servlet&#xff0c;SpringMVC视频推荐&#xff1a;53_尚硅谷_servlet3.0-简介&测试_哔哩哔哩_bilibili HttpServlet 是Java Servlet API 的一个抽象类&#xff0c;用于处理来自客户端的HTTP请求并生成HTTP响应。开发人员可以通过继承HttpServlet类并重写其中的doGet()、do…

从根到叶:深度理解哈希表

​​​​​​​ 一.哈希表的概念 关于查找元素时&#xff1a; 在顺序结构以及平衡树 中&#xff0c;元素关键码与其存储位置之间没有对应的关系&#xff0c;因此在 查找一个元素时&#xff0c;必须要经过关键 码的多次比较 。 顺序查找时间复杂度为 O(N) &#xff0c;平衡树中…

安卓上架华为踩坑合集

1.如果是离线打包&#xff0c;注意在manifest那里修改&#xff1a; android:debuggablefalse2…您的应用targetsdk版本低于30&#xff0c;不符合华为应用市场审核标准。 修改建议&#xff1a;请您将应用targetsdk等级升级到30或30以上。 因为之前我升到30被打回来过&#xff…

Unreal发布Android在刘海屏手机上不能全屏显示问题

Unreal 4.27发布Android在刘海屏手机上不能全屏显示问题 Android设置全屏刘海屏全屏设置4.27设置刘海屏在部分手机不能显示问题 Android设置全屏 AndroidManifest.xml文件配置 ...<activity android:name"com.epicgames.ue4.GameActivity" android:label"st…

2024计算机二级Python

1. 栈是先进先出&#xff0c;队是后进后出 2. 代码输出长度为5并不是\不占用位置&#xff0c;而是\与其后边的数字共同占用一个字符 3. 首先要弄清range函数此时表示的范围是前闭后开&#xff0c;不包含后面的数字&#xff0c;%函数表示的是余数&#xff0c;只有4是被整除的…

想要自己制作一款游戏,需要掌握哪些基本技能?

你是否曾经沉浸在游戏的世界中&#xff0c;感受到游戏带来的无限乐趣&#xff1f;你是否曾经梦想能够亲手制作一款属于自己的游戏&#xff0c;为玩家带来独特的体验&#xff1f;然而&#xff0c;要实现自己的游戏创作梦想&#xff0c;并不是一件轻松的事情。需要掌握各种技能和…

Java八股文(Maven)

Java八股文のMaven Maven Maven 什么是Maven&#xff1f; Maven是一个项目管理工具&#xff0c;用于构建、发布和管理Java项目。 它提供了一种标准化的项目结构、依赖管理和构建过程。 Maven的核心概念是什么&#xff1f; Maven的核心概念包括POM文件、依赖管理、仓库、生命周…

Paraverse白皮书发布,打造面向3D数字资产的去中心化运行与交易平台

随着信息技术的不断演进&#xff0c;我们正迎来以“元宇宙”和“Web3.0”为代表的“数字平行世界”。近日Paraverse平行云联合3D/XR产业和Web3.0领域的行业机构、专家发布了《Paraverse&#xff1a;面向3D数字资产的去中心化运行与交易平台》&#xff08;以下简称“白皮书”&am…

基于单片机的电子琴设计

基于单片机的电子琴设计 摘 要 读书、看电影、听音乐&#xff0c;都是最常见的丰富内心世界的良剂。听音乐&#xff0c;作为陶冶情操、提升境界最便捷的方式&#xff0c;正受到越来越多人们的欢迎。音乐可以很轻松的融入各种场合&#xff0c;给人们带来很轻松的氛围&#xff…

kakfa模拟仿真篇之spark-submit在linux运行 (更贴近真实场景)

源码在上篇 地址在这 &#xff1a;Kafka模拟器产生数据仿真-集成StructuredStreaming做到”毫秒“级实时响应StreamData落地到mysql-CSDN博客 这里分享一下一些新朋友不知道spark-submit 指令后 的参数怎么写 看这篇绝对包会 声明&#xff1a; 此项目是基于 maven 打包的说明…

ip广播智慧工地广播喊话号角 IP网络号角在塔吊中应用 通过寻呼话筒预案广播

ip广播智慧工地广播喊话号角 IP网络号角在塔吊中应用 通过寻呼话筒预案广播 SV-704XT是深圳锐科达电子有限公司的一款壁挂式网络有源号角&#xff0c;具有10/100M以太网接口&#xff0c;可将网络音源通过自带的功放和号角喇叭输出播放&#xff0c;可达到功率50W。SV-704XT内置有…

怎么避免电脑数据被拷贝?电脑如何禁用USB功能?

在无纸化办公的今天&#xff0c;很多重要数据都存放在电脑中。为了避免数据泄露&#xff0c;需要采用安全的方式保护电脑数据。那么&#xff0c;该如何避免电脑数据被拷贝呢&#xff1f;下面我们就来了解一下。 方法一&#xff1a;物理隔绝 物理隔绝是一种原始但有效的USB禁用…

KubeSphere 社区双周报|2024.02.29-03.14

KubeSphere 社区双周报主要整理展示新增的贡献者名单和证书、新增的讲师证书以及两周内提交过 commit 的贡献者&#xff0c;并对近期重要的 PR 进行解析&#xff0c;同时还包含了线上/线下活动和布道推广等一系列社区动态。 本次双周报涵盖时间为&#xff1a;2024.02.29-03.14…

【深度学习笔记】9_8 区域卷积神经网络(R-CNN)系列

注&#xff1a;本文为《动手学深度学习》开源内容&#xff0c;部分标注了个人理解&#xff0c;仅为个人学习记录&#xff0c;无抄袭搬运意图 9.8 区域卷积神经网络&#xff08;R-CNN&#xff09;系列 区域卷积神经网络&#xff08;region-based CNN或regions with CNN feature…

叶顺舟:手机SoC音频趋势洞察与端侧AI技术探讨 | 演讲嘉宾公布

后续将陆续揭秘更多演讲嘉宾&#xff01; 请持续关注&#xff01; 2024中国国际音频产业大会(GAS)将于2024年3.27 - 28日在上海张江科学会堂举办。大会将以“音无界&#xff0c;未来&#xff08;Audio&#xff0c; Future&#xff09;”为主题。大会由中国电子音响行业协会、上…

L1-5 猜帽子游戏

宝宝们在一起玩一个猜帽子游戏。每人头上被扣了一顶帽子&#xff0c;有的是黑色的&#xff0c;有的是黄色的。每个人可以看到别人头上的帽子&#xff0c;但是看不到自己的。游戏开始后&#xff0c;每个人可以猜自己头上的帽子是什么颜色&#xff0c;或者可以弃权不猜。如果没有…

导出pdf

pom依赖 <dependency><groupId>com.itextpdf</groupId><artifactId>itextpdf</artifactId><version>5.4.2</version></dependency><dependency><groupId>com.itextpdf</groupId><artifactId>itext-as…

PHP序列化基础知识储备

一、序列化与反序列化 1、概念 PHP中的序列化是指将复杂的数据类型转换为可存储或可传输的字符串&#xff0c;而反序列化则是将这些字符串重新转换回原来的数据类型。 序列化通常使用 serialize() 函数完成&#xff0c;它可以将数组、对象、字符串等复杂数据类型压缩到一个字…