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

ops/2024/9/23 10:23:06/

在这里插入图片描述

文章目录

  • 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.ppmy.cn/ops/29767.html

相关文章

软件开发标准流程与软件工程基本理论

软件开发标准流程与软件工程基本理论 一、需求分析 软件开发需要&#xff0c;自用户提出开始&#xff0c;商业合作确定&#xff08;规范化&#xff1a;软件开发项目合同&#xff09;&#xff0c;进入软件工程开始阶段&#xff1a;需求分析。 软件项目Team负责需求分析开发人员…

2024年北京餐饮供应链展览会|北京餐饮食材展

高联采高校后勤餐饮博览会 暨第25届北京高校后勤餐饮联合招标采购大会 同期举办&#xff1a;中国北京餐饮供应链博览会 主 题&#xff1a; 因为FOOD校园GOOD / 同创高校大舞台共享精彩高联采 时 间&#xff1a;2024年9月21日-22日 地 点&#xff1a;中国国际展览中心&…

Java中的集合类有哪些常见的排序算法?

在Java中&#xff0c;集合类&#xff08;如ArrayList、LinkedList、HashSet等&#xff09;通常会使用内部排序算法对元素进行排序。这些算法通常基于Java的内置排序算法&#xff0c;如快速排序、归并排序等。 以下是一些常见的Java集合类排序算法&#xff1a; 1. **快速排序&…

远程为ubuntu安装teamviwer(无UI界面) - 简书

远程为ubuntu安装teamviwer&#xff08;无UI界面&#xff09; - 简书 远程为ubuntu安装teamviwer&#xff08;无UI界面&#xff09; - 简书

详解Go语言Map

Map的声明 使用make()函数定义Map map_name : make(map[KeyType]valueType,initialCapacity)KeyType是键的类型&#xff0c;ValueType是值的类型&#xff0c;initialCapacity是可选参数&#xff0c;用于指定map的初始容量。 使用map关键字定义Map m : map[string]int {&quo…

【C++STL详解(五)】--------list的介绍与使用

目录 前言 一、list的介绍 二、list的使用 Ⅰ.默认成员函数 1、构造函数 2、赋值重载 3、析构函数 Ⅱ、容量 1.size() Ⅲ、迭代器与遍历 1.beginend (正向迭代器) 2.rbeginrend (反向迭代器) 3.front 4.back Ⅳ、增删查改 1.push_front 2.pop_front 3.push_b…

【强训笔记】day7

NO.1 思路&#xff1a;双指针模拟&#xff0c;begin表示最长数字字符串最后一个字符&#xff0c;而len表示数字字符串的长度&#xff0c;i用来遍历&#xff0c;如果为数字&#xff0c;那么定义j变量继续遍历&#xff0c;直到不为数字&#xff0c;i-j如果大于len&#xff0c;就…

salesforce vscode 获取profile metadata所有配置内容

1.更新cli&#xff1a;sfdx update 2.安装read插件&#xff1a;sfdx plugins:install sfdx-plugin-source-read 3.通过vscode☁️&#xff08;org browser&#xff09;或者package.xml拉取profile&#xff0c;此时获取的简档没有所有配置内容 <?xml version"1.0&qu…