关于数据库索引 “最左原则” 的解析--反八股文系列(1)

村头法师
97 阅读7分钟

关于数据库索引 “最左原则” 的解析

标准答案

第一点：定义

数据库索引的最左原则，也叫最左前缀匹配原则，是指在使用联合索引时，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，那么查询条件要按照索引列从左到右的顺序依次包含，才能利用该索引加速查询。例如对于联合索引 (A, B, C)，查询条件中包含 A 列，或者包含 A 和 B 列，或者包含 A、B、C 列，都可以使用该索引。

为了更直观地理解，假设我们有一个联合索引（col1, col2, col3），以 MySQL 为例，在其 InnoDB 存储引擎下，B + 树索引结构如下示意图：

graph TD;
    A[根节点] --> B[col1=1]
    A --> C[col1=2]
    B --> D[col1=1, col2=1, col3=1]
    B --> E[col1=1, col2=2, col3=2]
    C --> F[col1=2, col2=1, col3=3]
    C --> G[col1=2, col2=2, col3=4]

从图中可以看出，索引项的排序是先按照 col1 进行排序，在 col1 值相同的情况下再按 col2 排序，最后在 col1 和 col2 值都相同的情况下按 col3 排序。这就决定了查询时只有从最左边的 col1 开始匹配，才能利用索引的有序性来快速定位数据。

第二点：原理

联合索引在底层是以 B + 树的形式存储的。B + 树中的索引项是按照索引定义里字段的顺序排序的，先按最左列排序，在最左列值相同的情况下再按次左列排序，以此类推。所以数据库查询时会优先使用最左列作为匹配依据，然后依次使用后续列，跳过最左侧字段会导致无法利用索引的有序性来加速查询。

例如，对于联合索引 (a, b, c)，如果查询条件是where b = '某个值'，由于 B + 树是先按 a 排序，在 a 相同的情况下 b 才是有序的，而此时跳过了 a，直接查询 b，数据库无法利用索引的有序性快速定位到满足条件的 b 值，只能进行全表扫描或者全索引扫描（如果有覆盖索引的情况）。只有当查询条件是where a = '某个值' and b = '某个值'时，数据库才能根据 a 的值在 B + 树中快速定位到相关节点，然后在这些节点中再根据 b 的值进一步筛选，从而利用索引加速查询。

例外情况

例外一：MySQL 8.0.13 版本后的 Skip Scan 特性

在 MySQL 8.0.13 版本引入了 Skip Scan Range Access Method，在一定条件下可以不遵守最左前缀原则。当联合索引的最左列区分度较低，值的数量较少时，查询优化器可以通过穷举最左列的所有可能值，来实现跳过最左列直接匹配后续列的索引。例如对于联合索引 (a, b, c)，若 a 字段只有少数几个值，此时查询where b =某个值，数据库可能会先穷举 a 的所有值，然后结合 b 的值来利用索引，而不是像以前一样完全无法使用索引。不过，如果字段区分度很高，这种方式会繁衍出太多 SQL 语句，反而降低性能，就不适用了。

例外二：数据库优化器的优化

当数据库中的数据量过小的时候，数据库优化器可能会认为全表扫描比使用索引更快，从而不遵循最左原则去使用索引，而是直接进行全表扫描。另外，某些数据库优化器可能会对查询条件进行重写优化，使得看似不满足最左原则的查询也能使用索引。例如一些查询优化器会自动调整查询条件中列的顺序，以匹配索引结构，但这并不是普遍适用的情况，不同数据库优化器的策略会有差异。

例外三：Oracle 数据库中的情况

在 Oracle 数据库中，对于复合主键（由多个列组成的主键）所对应的唯一索引，并不完全遵循最左原则。因为复合主键中的每一列都是唯一的，Oracle 的唯一索引可以在不同的列上创建，在这种情况下，Oracle 的唯一索引可以更有效地查询数据库中的数据，不需要严格按照最左原则来匹配。例如，如果一个复合主键由列 A 和列 B 组成，即使查询条件中先使用列 B，Oracle 的优化器也可能会根据数据分布等情况合理地利用索引来优化查询，而不像在 MySQL 中，不遵循最左原则基本无法利用联合索引。根据 Oracle 官方文档《Oracle Database SQL Language Reference》，其中对索引优化策略的描述为该情况提供了理论依据 。

例外四：OceanBase 数据库中的情况

OceanBase 作为分布式数据库，其索引机制也有一些与传统数据库不同的特性。在某些情况下，它对最左原则的遵循也存在一定的灵活性。OceanBase 会根据数据的分布、查询的热度以及系统负载等多种因素，通过自适应的查询优化策略来决定是否使用索引以及如何使用索引。例如，当数据在各个节点上分布较为均匀，且查询条件涉及到联合索引中中间位置的列，同时该列的数据选择性较高时，OceanBase 的查询优化器可能会采用一些特殊的算法，通过扫描部分索引节点以及结合其他辅助信息来优化查询，不一定完全依赖最左原则。OceanBase 官方文档《OceanBase 数据库 SQL 参考》中关于索引管理和查询优化的内容，能体现其索引使用的灵活性 。

官方知识点

目前暂无明确统一的官方文档对 “最左原则” 进行专门定义（MySQL 等数据库官方文档主要是围绕索引相关知识整体介绍，未单独就该原则设章节），但在 MySQL 官方文档中关于索引的部分，提及了索引的存储结构等相关内容，可帮助理解最左原则原理。如介绍 B + 树索引结构时，说明了其节点排序方式等，可作为理解最左原则底层原理的依据。相关内容可参考：MySQL 官方文档 InnoDB 索引类型介绍

Oracle 官方文档《Oracle Database SQL Language Reference》中关于索引的部分，阐述了复合主键索引的特性以及优化器的相关策略，解释了为何复合主键的唯一索引在一定程度上不遵循最左原则。

OceanBase 官方文档《OceanBase 数据库 SQL 参考》中关于索引管理和查询优化的部分，虽然没有直接针对最左原则的例外情况说明，但可以从其查询优化策略以及分布式索引的工作机制中推测出其对最左原则的灵活运用。

附录

avatar
文章
阅读
粉丝