【MySQL | 第十篇】重新认识MySQL索引匹配过程

在这里插入图片描述

文章目录

  • 10.重新认识MySQL索引匹配过程
    • 10.1匹配规则
    • 10.2举例:联合索引遇到范围查询(>、<、between、like)
      • 10.2.1例子一:>
      • 10.2.2例子二:>=
      • 10.2.3例子三:between
      • 10.2.4例子四:like

10.重新认识MySQL索引匹配过程

​ MySQL 的索引匹配过程是数据库查询优化中的关键部分。

10.1匹配规则

经过分析总结,MySQL 索引匹配的一般规则一共有:五种

  • 等值匹配、最左匹配原则、列前缀匹配、匹配范围值、索引覆盖
  1. 等值匹配:

    如果查询条件是等值匹配,即 = 操作符,MySQL 可以高效地使用索引来查找匹配的行。这种情况下,MySQL 会直接根据索引中的值找到匹配的记录。

  2. 最左匹配原则:

    MySQL 的组合索引使用最左匹配原则,即只有当查询条件匹配索引的最左侧列时,索引才会被使用。例如,对于 (a, b, c) 的组合索引,查询条件只包含 aa, ba, b, c 才会使用索引。

    • mysql会一直向右匹配直到遇到范围查询(>、<)就停止匹配(注意:对于 >=、<=、BETWEEN、like 前缀匹配这四种范围查询,并不会停止匹配
    • 比如a = 1 and b = 2 and c > 3 and d = 4
      • 如果建立(a,b,c,d)顺序的索引,d是用不到索引的
      • 如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整(建立索引时他们三个的顺序)
  3. 列前缀匹配:

    可以匹配某一列值的开头部分:

    使用索引: explain select * from staffs where name like 'j%'  ;索引失效: explain select * from staffs where name like '%j%'
  4. 匹配范围值:

    可以查找某一范围的值:explain select * from staffs where name >‘Mary’ ;

  5. 索引覆盖:

    如果查询的列都包含在索引中,并且查询不需要访问表数据,即可通过索引得到所有需要的信息,这种情况称为索引覆盖。索引覆盖可以提高查询性能,因为不需要访问表中的实际数据行。

    例子:

    explain select name,age,pos from staffs where name=‘张三’ and age=23 and pos =‘dev’;

10.2举例:联合索引遇到范围查询(>、<、between、like)

​ 参考链接:联合索引遇到范围查询(>、<、between、like)的情况

  • 错误结论:联合索引的最左匹配原则会一直向右匹配直到遇到范围查询(>、<、between、like) 就会停止匹配。
  • 正确结论联合索引的最左匹配原则,在遇到范围查询(如 >、<)的时候,就会停止匹配,也就是范围查询的字段可以用到联合索引,但是在范围查询字段后面的字段无法用到联合索引。但是,对于 >=、<=、BETWEEN、like 前缀匹配这四种范围查询,并不会停止匹配

10.2.1例子一:>

Q1: select * from t_table where a > 1 and b = 2;

联合索引(a, b)哪一个字段用到了联合索引的 B+Tree?

  1. 由于联合索引(二级索引)是先按照 a 字段的值排序的,所以符合 a > 1 条件的二级索引记录肯定是相邻的,于是在进行索引扫描的时候,可以定位到符合 a > 1 条件的第一条记录
  2. 然后沿着记录所在的链表向后扫描,直到某条记录不符合 a > 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。
  3. 但是在符合 a > 1 条件的二级索引记录的范围里,b 字段的值是无序的
  4. 因此,我们不能根据查询条件 b = 2 来进一步减少需要扫描的记录数量(b 字段无法利用联合索引进行索引查询的意思)。
  5. 因此,Q1 这条查询语句只有 a 字段用到了联合索引进行索引查询,而 b 字段并没有使用到联合索引

10.2.2例子二:>=

Q2: select * from t_table where a >= 2 and b = 7,
联合索引(a, b)哪一个字段用到了联合索引的 B+Tree?

  1. Q2 和 Q1 的查询语句很像,唯一的区别就是 a 字段的查询条件「大于等于」。

  2. 虽然在符合 a>= 1 条件的二级索引记录的范围里,b 字段的值是「无序」的,但是对于符合 a = 1 的二级索引记录的范围里,b 字段的值是「有序」的(因为对于联合索引,是先按照 a 字段的值排序,然后在 a 字段的值相同的情况下,再按照 b 字段的值进行排序)。

    image-20240502155314217

  3. 所以,Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询

10.2.3例子三:between

Q3: SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2,
联合索引(a, b)哪一个字段用到了联合索引的 B+Tree?

  1. Q3 查询条件中 a BETWEEN 2 AND 8 的意思是查询 a 字段的值在 2 和 8 之间的记录。

  2. 不同的数据库对 BETWEEN … AND 处理方式是有差异的。在MySQL 中,BETWEEN 包含了 value1 和 value2 边界值,类似于 >= and =<

  3. 虽然 a 字段使用了 BETWEEN 进行范围查询,但是联合索引的最左匹配原则并没有在遇到 a 字段的范围查询( BETWEEN)后就停止匹配了,b 字段还是可以用到了联合索引的。

    img

10.2.4例子四:like

Q4: SELECT * FROM t_user WHERE name like ‘j%’ and age = 22
联合索引(name, age)哪一个字段用到了联合索引的 B+Tree?

  1. 由于联合索引(二级索引)是先按照 name 字段的值排序的,所以前缀为 ‘j’ 的 name 字段的二级索引记录都是相邻的, 于是在进行索引扫描的时候,可以定位到符合前缀为 ‘j’ 的 name 字段的第一条记录,然后沿着记录所在的链表向后扫描,直到某条记录的 name 前缀不为 ‘j’ 为止。
  2. 所以 a 字段可以在联合索引的 B+Tree 中进行索引查询,形成的扫描区间是[‘j’,‘k’)。注意, j 是闭区间。如下图:

img

  1. 虽然在符合前缀为 ‘j’ 的 name 字段的二级索引记录的范围里,age 字段的值是「无序」的,但是对于符合 name = j 的二级索引记录的范围里,age字段的值是「有序」的(因为对于联合索引,是先按照 name 字段的值排序,然后在 name 字段的值相同的情况下,再按照 age 字段的值进行排序)。

  2. 于是,在确定需要扫描的二级索引的范围时,当二级索引记录的 name 字段值为 ‘j’ 时,可以通过 age = 22 条件减少需要扫描的二级索引记录范围(age 字段可以利用联合索引进行索引查询的意思)。

  3. 也就是说,从符合 name = ‘j’ and age = 22 条件的第一条记录时开始扫描,而不需要从第一个 name 为 j 的记录开始扫描 。如下图的右边

    img

所以,Q4 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询

在这里插入图片描述

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

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

相关文章

一对一WebRTC视频通话系列(一)—— 创建页面并显示摄像头画面

本系列博客主要记录WebRtc实现过程中的一些重点&#xff0c;代码全部进行了注释&#xff0c;便于理解WebRTC整体实现。 一、创建html页面 简单添加input、button、video控件的布局。 <html><head><title>WebRTC demo</title></head><h1>…

机器学习之基于Tensorflow(LSTM)进行多变量时间序列预测股价

欢迎大家点赞、收藏、关注、评论啦 &#xff0c;由于篇幅有限&#xff0c;只展示了部分核心代码。 文章目录 一项目简介 二、功能三、系统四. 总结 一项目简介 项目简介&#xff1a;机器学习之基于TensorFlow&#xff08;LSTM&#xff09;进行多变量时间序列预测股价 一、项目…

HTML5本地存储账号密码

<!DOCTYPE html> <html lang"zh-CN"> <head><meta charset"UTF-8"><meta name"viewport" content"widthdevice-width, initial-scale1.0"><title>HTML5本地存储账号密码</title> </head…

ROS快速实现helloworld

ROS中涉及的编程语言以C和Python为主&#xff0c;ROS中的大多数程序两者都可以实现&#xff0c;在本系列教程中&#xff0c;每一个案例也都会分别使用C和Python两种方案演示&#xff0c;大家可以根据自身情况选择合适的实现方案。 ROS中的程序即便使用不同的编程语言&#xff…

leetcode51.N皇后(困难)-回溯法

思路 都知道n皇后问题是回溯算法解决的经典问题&#xff0c;但是用回溯解决多了组合、切割、子集、排列问题之后&#xff0c;遇到这种二维矩阵还会有点不知所措。 首先来看一下皇后们的约束条件&#xff1a; 不能同行不能同列不能同斜线 确定完约束条件&#xff0c;来看看究…

Uniapp好看登录注册页面

个人介绍 hello hello~ &#xff0c;这里是 code袁~&#x1f496;&#x1f496; &#xff0c;欢迎大家点赞&#x1f973;&#x1f973;关注&#x1f4a5;&#x1f4a5;收藏&#x1f339;&#x1f339;&#x1f339; &#x1f981;作者简介&#xff1a;一名喜欢分享和记录学习的…

Kafka(十二)Streams

目录 Streams1 什么式是流式处理2 流式处理的相关概念2.1 拓扑2.2 时间2.2.1 输入时间2.2.2 输出时间 2.3 状态2.4 流和表2.5 时间窗口2.5.1 测试时间窗口 2.6 处理保证 3 流式处理设计模式3.1 单事件处理3.2 使用本地状态3.3 多阶段处理和重分区3.4 使用外部查找&#xff1a;流…

日本宇宙航空研究“Int-Ball2”自由飞行相机机器人采用的Epson IMU

IMU有助于飞行的稳定控制和电池充电的自动对接- 精工爱普生公司&#xff08;TSE:6724&#xff0c;“Epson”&#xff09;很高兴地宣布&#xff0c;日本宇宙航空研究开发机构&#xff08;JAXA&#xff09;选择了爱普生M-G370系列的惯性测量单元&#xff08;IMU&#xff09;&…

附录6-4 黑马优购项目-分类和购物车

目录 1 分类 1.1 接口 1.2 窗口限制 1.3 选中状态样式判断 1.4 点击左侧时右侧会到顶点 1.5 源码 2 购物车 2.1 store 2.2 tabBar徽标 2.3 滑动删除 2.4 结算 2.4.1 结算前登录 2.4.2 结算功能 2.5 触发组件事件 2.6 源码 1 分类 分类最上部是…

JAVA面试专题-微服务篇

Spring cloud Spring Cloud 5大组件有哪些 注册中心/配置中心&#xff1a;nacos 负载均衡&#xff1a;Ribbon 服务远程调用&#xff1a;Feign 服务保护&#xff1a;sentinel 服务网关&#xff1a;Gateway 微服务注册和发现 nacos和eureka的区别 负载均衡 微服务向Ribbon发送…

unity制作app(3)--gps定位

1.unity中定位Unity之GPS定位&#xff08;高德解析&#xff09;_unity gps定位-CSDN博客 代码需要稍微修改一下&#xff0c;先把脚本绑到一个button上试一试&#xff01; 2.先去高德地图认证&#xff08;app定位&#xff09; 创建应用和 Key-Web服务 API | 高德地图API (ama…

Golang | Leetcode Golang题解之第64题最小路径和

题目&#xff1a; 题解&#xff1a; func minPathSum(grid [][]int) int {if len(grid) 0 || len(grid[0]) 0 {return 0}rows, columns : len(grid), len(grid[0])dp : make([][]int, rows)for i : 0; i < len(dp); i {dp[i] make([]int, columns)}dp[0][0] grid[0][0]…

场景文本检测识别学习 day06(Vi-Transformer论文精读、MAE论文阅读)

Vi-Transformer论文精读 在NLP领域&#xff0c;基于注意力的Transformer模型使用的非常广泛&#xff0c;但是在计算机视觉领域&#xff0c;注意力更多是和CNN一起使用&#xff0c;或者是单纯将CNN的卷积替换成注意力&#xff0c;但是整体的CNN 架构没有发生改变VIT说明&#x…

【知识学习/复习】损失函数篇,包含理解应用与分类:回归、分类、排序、生成等任务

损失函数总结 一、损失函数理解二、不同任务的损失函数的应用1.图像分类2.目标检测3.语义分割4.自然语言处理&#xff08;NLP&#xff09;5.图神经网络&#xff08;GNN&#xff09;6.生成式网络 三、损失函数1. 回归任务损失函数常见损失函数IoU系列损失函数1. IoU损失函数&…

计算机网络chapter2——应用层

文章目录 第2章 应用层章节引出—— 2.1应用层协议原理2.1.1 网络应用程序体系结构&#xff08;1&#xff09;客户-服务器体系结构&#xff08;2&#xff09;对等(P2P)体系结构2.1.2 进程通信1.客户和服务器进程2.进程与计算机网络之间的接口3. 进程寻址 2.1.3 可供应用程序使用…

【Cpp】类和对象

标题&#xff1a;【Cpp】类和对象 水墨不写bug 正文开始&#xff1a; &#xff08;一&#xff09;面向过程与面向对象 面向过程和面向对象是两种不同的编程思想。 面向过程指的是将程序分解成多个步骤&#xff0c;每个步骤都是一个独立的函数&#xff0c;通过函数之间的调用实…

# notepad++ 编辑器英文版,如何打开自动换行

notepad 编辑器英文版&#xff0c;如何打开自动换行 在Notepad中&#xff0c;如果你想要开启自动换行功能&#xff0c;可以按照以下步骤操作&#xff1a; 1、打开 Notepad 编辑器。 1.1. 依次点击菜单栏中的【视图】&#xff0c;英文版对应【View】。1.2. 在【视图】下拉菜单…

自动化机器学习流水线:基于Spring Boot与AI机器学习技术的融合探索

&#x1f9d1; 作者简介&#xff1a;阿里巴巴嵌入式技术专家&#xff0c;深耕嵌入式人工智能领域&#xff0c;具备多年的嵌入式硬件产品研发管理经验。 &#x1f4d2; 博客介绍&#xff1a;分享嵌入式开发领域的相关知识、经验、思考和感悟&#xff0c;欢迎关注。提供嵌入式方向…

51单片机两个中断及中断嵌套

文章目录 前言一、中断嵌套是什么&#xff1f;二、两个同级别中断2.1 中断运行关系2.2 测试程序 三、两个不同级别中断实现中断嵌套3.1 中断运行关系3.2 测试程序 总结 前言 提示&#xff1a;这里可以添加本文要记录的大概内容&#xff1a; 课程需要&#xff1a; 提示&#x…

结构分析的有限元法及matlab实现(徐荣桥)|【PDF教材+配套案例Matlab源码】

专栏导读 作者简介&#xff1a;工学博士&#xff0c;高级工程师&#xff0c;专注于工业软件算法研究本文已收录于专栏&#xff1a;《有限元编程从入门到精通》本专栏旨在提供 1.以案例的形式讲解各类有限元问题的程序实现&#xff0c;并提供所有案例完整源码&#xff1b;2.单元…