MySQL 索引最左匹配原则详解

news/2024/9/30 12:33:00 标签: mysql, 数据库

MySQL 索引最左匹配原则详解

在使用 MySQL 数据库进行查询优化时,索引是一项至关重要的工具。理解索引的最左匹配原则及其底层实现原因,对于编写高效的 SQL 查询至关重要。本文将深入讲解 MySQL 索引最左匹配原则,为什么不满足最左匹配原则会导致索引失效,以及背后的底层原因。

什么是 MySQL 索引最左匹配原则?

MySQL 的索引最左匹配原则指的是:在联合索引中,查询条件必须从索引的最左边的列开始,且顺序连续,才能有效使用索引。联合索引是由多列字段组成的索引,最左匹配原则决定了 MySQL 使用索引的方式。

例如,如果有一个联合索引 (A, B, C),那么 MySQL 只有在查询中使用 A(A, B)(A, B, C) 作为条件时,才能有效利用这个索引。这就是“最左匹配”的含义。以下是最左匹配原则的几种情况:

  1. 使用 A:索引有效。
  2. 使用 AB:索引有效。
  3. 只使用 BC:索引失效。
  4. 使用 BC,但不使用 A:索引失效。
  5. 使用 AC:索引部分失效 A 走索引 C 不走索引。

为什么不满足最左匹配原则索引会失效?

要理解为什么不满足最左匹配原则时索引会失效,我们需要了解 MySQL 索引的底层数据结构和查找过程。

1. 索引的数据结构 —— B+树

MySQL 的 InnoDB 存储引擎大多数情况下使用 B+树 作为索引的数据结构。B+树是一种平衡树结构,适合于范围查找和有序数据的存储。每个节点按顺序存储键值,并且叶子节点之间使用链表连接,以便于范围查询。

当我们创建一个联合索引 (A, B, C) 时,MySQL 会以 A 作为最外层的键值,然后根据 A 的值进行进一步排序,依次对 BC 进行排序。这意味着在 B+树中,数据的组织顺序是 (A -> B -> C)

2. 查找过程

  • 当我们按照 A 查询时,MySQL 能在 B+树中从根节点开始,快速定位到对应的 A 值,然后基于 A 的值进一步向下查找相关的 BC
  • 当查询条件包含 (A, B) 时,MySQL 仍然可以使用联合索引从 A 查找到 B,并继续查找 C
  • 但是如果直接跳过 A,如只用 BC 作为查询条件,MySQL 无法通过联合索引定位,因为在 B+树结构中,没有单独针对 BC 的直接路径。也就是说,B+树的索引是从左到右逐层组织的,跳过最左列会导致无法利用索引结构进行查找。

3. 顺序和连续性的重要性

最左匹配原则的另一个核心是 顺序的连续性。在 (A, B, C) 的联合索引中,必须按照从 A 开始的顺序使用。例如,如果只使用 (A, C) 而不包含 B,则只有索引 A 生效。

这是因为 MySQL 无法跳过 B 直接找到 C,B+树中的节点存储顺序需要依次按照 (A -> B -> C) 进行匹配。因此,查询条件必须保持与索引顺序一致且连续,才能有效利用索引。

索引失效的底层原因

总结来看,索引失效的底层原因主要归结为以下几点:

  1. B+树的结构限制:B+树的索引组织是自上而下、从左到右的。在联合索引中,每一层(列)依赖于上一层的值才能定位到下一层的节点,因此跳过最左列会导致 MySQL 无法利用索引路径。

  2. 查询路径的建立:MySQL 在查询过程中会根据索引结构建立一条从根节点到目标叶子节点的路径。这条路径的建立是基于联合索引的最左列开始的,一旦查询条件不满足最左匹配,MySQL 就无法构建这条路径,只能进行全表扫描或其他类型的扫描。

  3. 排序和存储方式:联合索引的列是按顺序依次排序和存储的。查询条件中包含的列必须保持与索引中列的顺序一致,才能利用索引中的排序信息。否则,MySQL 会放弃使用索引,因为它无法有效利用已有的排序来加速查找。

如何避免索引失效?

  1. 遵循最左匹配原则:尽量按照联合索引的顺序来设计查询条件,确保查询从最左侧的列开始。

  2. 适当设计索引:如果某些查询经常只使用联合索引中的中间列,可以考虑创建单独的索引,以避免这种查询导致索引失效。

  3. 避免跳过列:在使用联合索引时,不要跳过其中的某些列。例如,如果 (A, B, C) 是联合索引,那么在查询中包含 AB,即使不需要 C,也能确保索引有效。

结论

MySQL 索引最左匹配原则是联合索引的一个重要特性,其根本原因在于 B+树的数据结构和查询路径的构建方式。理解最左匹配原则和索引失效的底层原因,可以帮助我们在设计数据库和编写查询时更好地优化性能,从而避免不必要的全表扫描。

有效使用索引能够大大提高查询效率,但需要确保查询条件与索引的设计一致,特别是在使用联合索引时,遵循最左匹配原则是关键。


http://www.niftyadmin.cn/n/5685108.html

相关文章

【以图搜图代码实现】--犬类以图搜图示例

1.背景及目标 随着互联网技术的发展,图像数据呈指数级增长。图像搜索技术已经成为人们日常生活和工作中不可或缺的一部分,尤其是在电子商务、社交媒体、在线教育等领域。传统的基于文本的搜索引擎虽然已经非常成熟,但在面对大量无标签或标签…

GEE数据集:1996 年到 2020 年全球红树林观测数据集(JAXA)(更新)

目录 简介 数据集说明 数据集 代码 代码链接 结果 引用 许可 网址推荐 0代码在线构建地图应用 机器学习 简介 全球红树林观测 这项研究使用了日本宇宙航空研究开发机构(JAXA)提供的 L 波段合成孔径雷达(SAR)全球mask…

buff叠满!软考报名越晚,批次越晚?考试越难?

近日,各地软考办都发布了2024年下半年软考批次安排。 报考了软考中级-系统集成项目管理工程师(简称“集成”)的广东考生炸锅了,我会被分到11月11日(周一)的第四批次、第五批次考试吗? 软考批次是…

CSS 中的@media print 是干什么用的?

media print { ... } 是CSS中的一个媒体查询,它专门用于定义当内容被打印到纸张上时应该应用的样式规则。在这个查询块内,你可以设置各种样式,以确保打印输出的内容看起来整洁、专业,并且只包含必要的信息。 在你给出的例子中&am…

Spring - @Import注解

文章目录 基本用法源码分析ConfigurationClassPostProcessorConfigurationClass SourceClassgetImportsprocessImports处理 ImportSelectorImportSelector 接口DeferredImportSelector 处理 ImportBeanDefinitionRegistrarImportBeanDefinitionRegistrar 接口 处理Configuratio…

《动手学深度学习》笔记2.5——神经网络从基础→使用GPU (CUDA-单卡-多卡-张量操作)

目录 0. 前言 原书正文 1. 计算设备 (CPU和GPU) 补充:torch版本cuda报错的解决方案 2. 张量与GPU 3. 存储在GPU上 4. 复制(多卡操作) 5. 旁注 (CPU和GPU之间挪数据) 6. 神经网络与GPU 小结 0. 前言 课程全部代码(pytorc…

《ToDesk 云电脑、易腾云、青椒云移动端体验实测:让手机秒变超级电脑》

前言 科技发展到如今2024年,可以说每一年都在发生翻天覆地的变化。云电脑这个市场近年来迅速发展,无需购买和维护额外的硬件就可以体验到电脑端顶配的性能和体验,并且移动端也可以带来非凡体验。我们在外出办公随身没有携带电脑情况下&#x…

【C++——文件操作】

写入 #include<iostream> #include<fstream> //ofstream所需头文件 using namespace std;int main() {//一打开文件:string str R"(C:\Users\admin\Desktop\新建文件夹\test.txt)";//也可以用C风格字符串//打开文件&#xff0c;如果不存在就创建一…