深入理解MySQL数据库核心技术之索引技术原理

1. MySQL数据库索引技术种类

1.1 B+树索引

• B+树是一种自平衡的多路搜索树，具有良好的层级平衡性和局部性。在MySQL的索引实现中，B+树的所有叶子节点构成一个有序链表，所有数据都在叶子节点上，并且叶子节点之间通过指针连接，方便范围查询。
• 每个内部节点（非叶子节点）存储索引键值及指向子节点的指针，而叶子节点除了存储索引键值外，还存储对应行记录的物理地址（在InnoDB中是聚簇索引则是行数据本身）。
• 当对索引列进行查询时，MySQL可以从根节点开始，沿着树形结构向下遍历，通过二分查找法快速定位到相应的叶子节点，从而得到所需的数据行。

1.2 聚簇索引与非聚簇索引

• 聚簇索引（Clustered Index）：在InnoDB存储引擎中，每张表都有一个聚簇索引，叶子节点直接包含行数据。主键默认是聚簇索引，如果没有显式定义主键，MySQL会选择一个唯一的非空索引代替，如果也没有这样的索引，则隐式创建一个row-id作为聚簇索引。
• 非聚簇索引（Secondary Index）：除了聚簇索引以外的其他索引称为非聚簇索引，它们的叶子节点存储的是主键值而非行数据。因此，当通过非聚簇索引查找行时，还需要根据叶子节点上的主键值回表到聚簇索引完成查找。

1.3 联合索引与最左前缀匹配原则

• 联合索引是对多个列创建的一个索引，其内部也是按照B+树结构组织，但键值是多个列的组合。在查询时，MySQL遵循最左前缀匹配原则，即查询时至少要从索引最左边的列开始，并依次向右匹配更多列，才能充分利用索引。
• 若查询条件没有包含索引最左边的列或者中间存在范围查询（>、<、BETWEEN等），则无法利用后续列的索引。

1.4 覆盖索引（Covering Index）

• 如果一个查询仅通过索引就能获取所需的所有列，无需回表查询行数据，那么该索引被称为覆盖索引。这种情况下，查询效率非常高，因为减少了磁盘I/O。

1.5 哈希索引

• MySQL中InnoDB引擎在特定条件下（如内存表或NDB集群存储引擎）也支持哈希索引，哈希索引适用于等值查询，且查找速度快，但不支持范围查询和排序。

2. B树（B-Tree）索引

2.1 数据结构特性：

• B树是一种自平衡的多路搜索树，每个节点可以有多个子节点，通常每个节点包含多个键值和对应的指针。
• B树索引的每个节点按升序存储键值，并且所有叶子节点在同一层，形成了一个高度平衡的树结构。
• 每个键值都会有两个或多个指针，指向键值大小在其左右两侧的子节点。若节点有n个键值，则会有n+1个子节点。

2.2 查找过程

• 当执行查询时，MySQL会从根节点开始，根据查询条件逐步向下遍历B树，由于B树的有序特性，每次比较都能排除一半以上的分支，直到找到与查询条件相匹配的叶子节点。
• 叶子节点存储了实际的数据记录或者指向数据记录的指针（在InnoDB存储引擎中，叶子节点存放的是行数据的物理地址，而在某些存储引擎中可能是键值本身）。

2.3 I/O效率

• B树索引的每个节点大小通常设计得足以容纳一个硬盘页，这样一次I/O操作就可以加载一个节点，进而减少磁盘I/O次数。相比于简单的线性扫描或二叉查找树，B树索引更适合磁盘存储环境，因为在大型数据库中，数据往往不能全部加载到内存，通过B树索引能够显著降低查找数据所需的磁盘I/O次数。

2.4 B+树索引

• MySQL中InnoDB存储引擎实际上使用的是B+树变种——B+树作为索引结构。B+树与B树的主要区别在于：
  • B+树的所有数据都存储在叶子节点上，而非叶子节点仅存储键值用于索引；
  • 叶子节点之间通过指针相连形成一个有序链表，有利于范围查询和全表扫描。