MySQL索引详解

本文最后更新于：3 年前

引言

随着数据量的持续增长，如何在海量记录中迅速找到目标数据，成为数据库性能优化的关键。索引正是为了解决这一问题而生，它借助专门的数据结构（如 B+ 树），帮助数据库在最短时间内定位所需行。MySQL 提供了多种类型的索引以应对不同场景需求：从最常见的主键索引、唯一索引，到专门面向文本搜索的全文索引，以及地理空间场景所需的空间索引。本文将围绕 MySQL 中的索引概念、实现原理和应用技巧展开探讨，帮助读者在开发和运维中高效利用索引来提升查询性能、维护数据一致性。

定义

索引是一种数据结构，它为数据库表中的一列或多列数据建立映射关系，使得数据库能够快速定位到数据行，而无需遍历整个表。可以把索引看作是书籍的目录，通过目录能迅速找到对应的章节内容。

创建命令

主键索引

  • 主键索引在创建表时通常直接定义。
  • 主键索引的特点是：唯一性 + 不允许为空。

创建示例：

1
ALTER TABLE table_name ADD PRIMARY KEY (id);

唯一索引

  • 唯一索引用于确保列值的唯一性，但允许 NULL 值。
  • 一个表可以有多个唯一索引。

创建示例：

1
2
3
ALTER TABLE table_name ADD UNIQUE (email);

CREATE UNIQUE INDEX idx_email ON table_name (email);

普通索引

  • 普通索引没有唯一性约束，主要用于提高查询效率。

创建示例：

1
2
3
ALTER TABLE table_name ADD INDEX idx_name (name);

CREATE INDEX idx_name ON table_name (name);

复合索引

  • 复合索引在单个索引中包含多个列，用于多列组合查询。
  • 遵循最左前缀原则，即必须从索引的最左列开始使用。

创建示例：

1
2
3
ALTER TABLE table_name ADD INDEX idx_name_age (name, age);

CREATE INDEX idx_name_age ON table_name (name, age);

全文索引

  • 用于对文本字段进行全文搜索。
  • 支持 MyISAM 和 InnoDB 存储引擎。

创建示例：

1
2
3
ALTER TABLE table_name ADD FULLTEXT (content);

CREATE FULLTEXT INDEX idx_content ON table_name (content);

前缀索引

  • 用于减少大文本字段的索引大小。
  • 只索引字段的前 N 个字符。

创建示例：

1
CREATE INDEX idx_email_prefix ON table_name (email(10));

基本原理

数据结构

MySQL 中最常用的索引实现结构是 B+ 树，其特点是所有数据都存储在叶子节点上，并且叶子节点之间通过链表相连，方便进行范围查询。除此之外，还有哈希索引（主要用于 Memory 存储引擎）以及空间索引（用于 GIS 数据）等。

存储方式

对于 InnoDB 引擎，主键索引采用聚簇索引，即数据行本身存储在索引叶子节点；而其他辅助索引（非聚簇索引）存储的是主键值的引用，这种设计便于通过主键 “回表” 获取完整数据。

作用

  • 提升查询效率：

    • 快速定位数据：索引可以大幅减少数据库需要扫描的行数，避免全表扫描，从而降低 I/O 操作，提高查询速度。

    • 支持范围查询和排序：B+ 树的结构非常适合范围查询和有序数据的查找，可用于加速 BETWEEN、>、< 等条件查询以及 ORDER BY 排序。

    • 覆盖查询：当查询字段全部包含在索引中时（即覆盖索引），可以避免回表操作，进一步提高查询性能。

  • 数据完整性与约束：

  • 唯一性验证：唯一索引可以保证数据的唯一性，防止重复数据插入。

  • 加速关联查询：在多表关联时，索引能够大幅降低数据匹配的计算量。

优缺点

优点

  • 大幅提升查询效率：对于数据量较大的表，使用合适的索引可以显著减少查询时间。
  • 降低 I/O 负担：索引使得数据库能够直接定位到数据位置，减少磁盘读写次数。
  • 优化排序和分组：合适的索引可以使排序、分组操作在索引层面完成，节省计算资源。
  • 支持数据完整性约束：唯一索引等有助于保证数据质量。

缺点

  • 增加写操作成本：插入、更新、删除操作时，除了对数据表进行修改，还需要维护相应的索引结构，这会带来额外的 CPU 和磁盘开销。
  • 占用额外存储空间：索引需要额外的磁盘空间保存数据结构和辅助信息。
  • 设计不当可能影响性能：索引过多或不合理的索引顺序会导致查询优化器选择不合适的执行计划，反而降低性能。
  • 维护复杂性：随着数据变化，索引可能会产生碎片，需要定期整理或重建索引。

场景

适合使用索引的场景

  • 大数据量的表：当数据量较大时，全表扫描的代价很高，使用索引能大幅提高查询效率。
  • 外键关联字段：对于频繁与外键关联的表字段，建立索引能大幅降低数据匹配的计算量。
  • 频繁的查询操作：对于经常出现在 WHERE 子句、JOIN 条件、ORDER BY 或 GROUP BY 中的字段，应考虑建立索引。
  • 需要保证数据唯一性：主键和唯一索引不仅加速查询，同时确保数据的唯一性，防止重复插入。
  • 范围查询与排序：例如查询某个时间段的数据或对结果进行排序时，合适的 B+ 树索引能够显著提高效率。
  • 覆盖索引场景：如果查询中涉及的列全部包含在某个索引中，则可以避免回表，达到更快的查询响应。

谨慎或避免使用索引的场景

  • 频繁的写操作：插入、更新、删除操作需要同时维护索引，过多或不合理的索引会降低写入性能。
  • 小数据量的表：如果表的数据量很少，全表扫描的开销可能远低于维护索引的额外成本，此时使用索引收益不明显。
  • 不适宜的查询条件：
    • 使用 LIKE '%keyword%' 的模糊匹配，前置通配符会导致索引失效。
    • 在查询中对索引字段进行函数操作或类型转换，也可能导致无法利用索引。
  • 动态变化的数据：数据频繁变动的场景下，索引的统计信息可能不够准确，导致查询优化器选择不佳的执行计划，此时需要额外关注索引维护。
  • 数据重复且分布均匀的字段：索引选择性是指索引列中不重复值的比例（公式：选择性 = 不同值的数量 / 总行数）。当索引选择性较低时，大量行具有相同的值，使用索引可能会返回大量数据行，这会导致查询性能不如全表扫描。

分类

主键索引（Primary Key Index）

  • 定义：表中唯一标识每一行数据的索引，不能为空且必须唯一。
  • 特点：InnoDB 中采用聚簇索引，数据行直接存储在主键索引中，查询效率高。

唯一索引（Unique Index）

  • 定义：除了能加速查询，还能保证字段值的唯一性。
  • 特点：与主键索引类似，但允许存在空值（NULL）的情况（具体实现上可能有所不同）。

普通索引（Non-Unique 或 Index）

  • 定义：主要用于加速查询，不对数据的唯一性做限制。
  • 特点：适用于大多数查询场景，创建和维护成本相对较低。

复合索引（Composite Index）

  • 定义：在同一索引中包含多个列。
  • 特点：遵循 “最左前缀原则”，即查询条件中必须包含索引最左边的列才能有效利用复合索引。

全文索引（Fulltext Index）

  • 定义：针对文本数据的全文搜索建立的索引。
  • 特点：适用于需要对大段文本进行匹配搜索的场景（例如搜索文章内容），但其灵活性和性能在某些场景下不及专门的搜索引擎，如 Elasticsearch。

空间索引（Spatial Index）

  • 定义：专门针对地理信息系统（GIS）数据设计的索引。
  • 特点：支持二维或多维数据的快速查询，使用场景较为特殊。

前缀索引

  • 定义：对文本字段的部分字符建立索引。
  • 特点：可以减少索引体积，但可能会影响索引的选择性和精确度。

索引与 B+ 树

B+ 树的结构与核心特点

基本结构

B+ 树是一种多路平衡搜索树，由内部节点（非叶子节点）和叶子节点构成。

  • 内部节点：仅存储键值和指向子节点的指针，不存储实际数据。这些节点起到导航作用，帮助快速定位到正确的叶子节点。
  • 叶子节点：存储所有的数据记录或数据记录的指针，是实际存储数据的位置。

有序性与链表结构

  • 顺序存储：所有叶子节点通过双向链表连接，保证了数据的顺序性，这使得范围查询、顺序遍历和排序操作非常高效。
  • 平衡性：B+ 树是一棵平衡树，所有叶子节点都处在同一深度，因此查询时从根到叶子的路径长度固定，降低了磁盘 I/O 次数。

节点容量与磁盘适应性

  • 多路分支：每个节点可以存储多个键值（通常上百个），从而使树的高度很低，即使面对海量数据，也只需访问少量节点即可定位目标数据。
  • 数据页存储：B+ 树的节点通常与磁盘页（如 InnoDB 中默认的 16KB 页面）相匹配，充分利用磁盘 I/O 的块读取特性，提高存取效率。

索引对 B+ 树的利用

聚簇索引（Clustered Index）

数据与索引共存：InnoDB 的主键索引采用聚簇索引实现，即数据记录本身存储在 B+ 树的叶子节点中。这意味着：

  • 根据主键查询时，直接在叶子节点上即可找到完整数据，无需额外的 “回表” 操作。
  • 聚簇索引保证了数据的物理顺序与逻辑顺序一致，这对于范围查询和排序都非常有利。

辅助索引（Secondary Index）

  • 存储方式：InnoDB 中除主键外的其他索引（辅助索引）也采用 B+ 树结构，但它们的叶子节点并不存储实际数据，而是存储相应的索引键和主键值。
  • 工作流程：在执行查询时，如果查询条件使用了辅助索引，则首先通过 B+ 树快速定位到匹配的主键值，再通过聚簇索引找到完整记录，这一过程称为 “回表” 操作。
  • 设计原则：为减少回表次数，常见的优化方式是建立覆盖索引（即查询所需字段均包含在辅助索引中），从而直接在辅助索引中返回结果。

数据更新与维护

  • 插入与分裂：当向 B+ 树中插入数据时，如果目标叶子节点已满，会触发节点分裂，进而调整父节点结构以保持平衡。这一过程虽然会带来额外的维护开销，但由于树的高度较低，整体更新代价一般可控。
  • 缓冲机制：InnoDB 采用 Buffer Pool 缓存数据页，频繁访问的 B+ 树节点（特别是根节点和较高层的内部节点）往往会被缓存在内存中，从而降低磁盘 I/O，进一步提升查询性能。

范围查询与顺序扫描

  • 高效区间查找：由于 B+ 树叶子节点之间按键值顺序相连，当执行范围查询（例如 BETWEEN、>、< 等）时，只需定位到第一个匹配节点，随后沿链表顺序扫描即可，极大地提高了查询效率。
  • 排序优化：如果查询要求返回有序数据，B+ 树天然的顺序存储特点能够避免额外的排序操作，直接利用索引顺序返回结果。

图示及说明

B+树结构

B+ 树索引的多级结构

  • 根节点（Level 2）

    • 通常在 B+ 树的最上层（图中为 Page 1）。

    • 根节点中只存储索引键（如 1、28、66）和指向下一层节点的指针（如指向 Page 2、Page 3、Page 4）。

    • 在 InnoDB 中，这一层的节点往往会被加载在内存里（Buffer Pool）进行缓存，加快定位子节点的速度。

  • 内部节点（Level 1）

    • 这些页面（图中 Page 2、Page 3、Page 4）承接根节点的下探，也只存储索引键和指向更底层页面的指针。

    • 例如 Page 2 中的键值是 1、10、20，它会分别指向 Page 5、Page 6、Page 7 这三个更底层页面。

    • 该层的目的仍然是 “导航”，帮助快速锁定目标页所在的叶子节点，而不存储实际行数据。

  • 叶子节点（Level 0）

  • 这里才是真正存储数据的地方（InnoDB 的聚簇索引模式），在图中就是 Page 5、Page 6、Page 7、… Page 13。

  • 例如图中 Page 12 叶子节点里存储了 id=78, name=A1, age=20 这样的行数据。

  • 所有记录都存放在这一层，这也是 B+ 树和 B 树在概念上的一个主要区别：B+ 树把所有数据都集中在最底层的叶子节点。

叶子节点间的双向链表

从图中可以看出，每个叶子节点与相邻节点之间通过双向链表指针（紫色双向箭头）相互连接。这种设计的好处在于：

  1. 范围查询：可以只用一次索引查找定位到区间起点，然后依次顺序或逆序遍历叶子节点，即可获得完整区间数据；无需反复返回到上层节点。
  2. 顺序存取：在做排序、扫描等操作时，利用已排序的叶子节点链表可以极大地减少排序或临时表的开销。

索引键与数据存储的对应关系

  1. 聚簇索引（主键索引）
    • InnoDB 将表的主键作为聚簇索引的索引键，图中展示的示例是按 id 字段构建的 B+ 树。
    • 在叶子节点中，索引键和行数据直接保存在同一数据页中（如 Page 12 存了 id=78, name=A1, age=20）。
    • 当进行基于主键的等值或范围查询时，可以直接根据 B+ 树导航到对应叶子节点，快速拿到完整行。
  2. 辅助索引（Secondary Index）
    • 虽然图里主要展示的是主键索引的结构，但在 InnoDB 中，对非主键字段建立的索引也还是 B+ 树。
    • 辅助索引的叶子节点并不存储整行数据，而是存储 “索引键 + 主键值”。然后通过这个主键值再去回表查询真正的数据行。
    • 这就是为什么主键在 InnoDB 中极为重要，因为所有辅助索引都依赖它来定位完整数据。

数据页 (Page) 与 InnoDB 存储

  1. InnoDB 的数据页
    • InnoDB 默认的页面大小是 16KB（也可配置为 8KB 或 4KB 等），所以图中的每一个 Page X 可以看成 16KB 大小的 “数据块”。
    • B+ 树的节点结构恰巧和 “页” 一一对应，保证在一次磁盘 I/O 读取一个数据页时，可以得到同一个节点的所有索引信息。
  2. 内部节点与叶子节点的页面区别
    • 内部节点页中主要是 “（键值，指针）” 的对，用来指导查找方向。
    • 叶子节点页中同样有 “（键值，指针）” ，不过它的指针指向的是实际行记录（聚簇索引的情况下，直接把整行存在同一个数据页；辅助索引则指向主键）。
    • 由于 B+ 树的高扇出特性，当数据量很大时，树高依然很低，一般只需 3 ~ 4 层就能容纳大量行。

关键细节

  • Level 2（根节点） 中每个键值对应的下级页面：

    • 键值区间 [1 ~ 28) => Page 2
    • 键值区间 [28 ~ 66) => Page 3
    • 键值区间 [66 ~ +∞) => Page 4

    在实际实现中，可能还会有区间的右边界等定义，但思路类似。

  • Level 1（内部节点） 示例：

    • Page 2 拥有键值 1、10、20，对应下级页面分别是 Page 5、Page 6、Page 7。
    • 当要查找 id = 15 的记录时，会先比对根节点键值，发现应从 Page 2 继续查找，再在 Page 2 中比对 10 和 20，得知应去 Page 6 找最终记录。

  • Level 0（叶子节点） 中的行数据：

    • 图中显示的 Page 12、Page 13 等存放了真实的 (id, name, age) 信息，彼此之间通过双向链表衔接。
    • 叶子节点中同一个页面里可能包含多行数据，具体行数取决于单行大小和 16KB 页的利用率。

  • 单向链表指针（绿色箭头）与双向链表指针（紫色箭头）

    • 一些指针是自上而下的层级指向。
    • 一些指针是叶子节点之间的横向连接，既可正向又可反向遍历。

总结

  1. B+ 树层级：从根节点、内部节点到叶子节点，每一层各司其职，能在大规模数据情况下依旧保持相对较低的树高。
  2. 聚簇索引融合数据：InnoDB 将数据行本身保存在叶子节点（主键索引），这使得基于主键的查询和范围扫描非常高效。
  3. 双向链表适合范围扫描：叶子节点间的双向链表有效提升了区间查询、排序、分组等操作的性能。
  4. 高度与 I/O：B+ 树的高扇出特性使得访问一个节点就能拿到大量索引信息，树高变低，磁盘 I/O 次数也随之减少。
  5. 回表机制：对辅助索引的查询，最终还是需要通过主键到聚簇索引中取回完整行数据，这里也体现了主键索引在 InnoDB 中的中心地位。

对比 B 树

特性 B树 B+树
节点内容 每个节点同时存储键值和数据记录。 只有叶子节点存储数据记录，非叶子节点只存储索引键和指针。
叶子节点链表 叶子节点之间没有指针连接。 叶子节点通过双向链表连接，方便顺序遍历和范围查询。
查找结束位置 查询可以在任何一个节点结束（可能是非叶子节点）。 查询必须到达叶子节点才能结束，所有数据存储在叶子节点中。
树的层级 树的层级可能较高，因为节点存储数据占用更多空间。 树的层级更低，因为节点仅存索引键，容量更大。
范围查询 范围查询较为复杂，需要遍历多个子树。 范围查询高效，从叶子节点链表顺序遍历即可。

为什么 B+ 树更适合作为 MySQL 的索引结构？

  1. 磁盘读写效率更高
    • B+ 树的非叶子节点只存索引键和指针，单个节点可以容纳更多索引值，树的高度更低。
    • 查询时需要从根节点到叶子节点，树高较低意味着磁盘 I/O 次数更少，性能更高。
  2. 范围查询更高效
    • B+ 树的叶子节点通过双向链表连接，范围查询只需定位到范围的起点，然后通过链表顺序扫描即可完成，效率非常高。
    • B 树没有链表结构，范围查询需要回到上层节点，复杂度更高。
  3. 数据顺序性更强
    • 所有数据都存储在叶子节点，并按键值顺序排列，这种顺序存储方便支持 ORDER BY、GROUP BY 等操作，而无需额外排序。
  4. 占用空间更少
    • B+ 树非叶子节点不存实际数据，因此整体存储空间更小，可以容纳更多索引，减少磁盘访问次数。
  5. 一致的查询路径
    • 在 B+ 树中，所有查询都会到达叶子节点，而 B 树可能在不同层结束。
    • 一致的路径方便优化查询逻辑，并简化范围查询的实现。

综上，B+ 树通过降低树高、优化范围查询、支持顺序存储等特性，大大提高了 MySQL 的查询效率，相比于 B 树是个更优的选择。

其他索引

MyISAM 索引

MyISAM 索引通常与 InnoDB 索引进行对比，如下：

  • 存储结构与数据存放：
    • MyISAM： 数据和索引分别存储在不同的文件中；索引结构一般采用 B-Tree（在本质上也可以认为是 B+ 树结构，只是在官方手册或多数资料中常直接称为 B-Tree 索引）。
    • InnoDB： 数据和索引存储在同一表空间中，且采用聚集索引（主键索引）存储数据。
  • 事务支持与锁机制：
    • MyISAM： 不支持事务，使用表级锁，适合读多写少的场景。
    • InnoDB： 支持事务、行级锁和外键约束，适用于高并发、事务性要求较高的应用。
  • 全文索引支持：
    • MyISAM： 长期支持全文索引。
    • InnoDB： 从 MySQL 5.6 开始也支持全文索引，但在实现细节和性能上有所不同。

哈希索引

基本特性

哈希索引是基于哈希表实现的索引结构，查找等值匹配时速度极快。

使用限制

  • 仅支持等值查询，不支持范围查询（如 BETWEEN、<、> 等）。
  • 无法用于排序操作，因为哈希表内部没有顺序性。

适用场景

主要用于 Memory 存储引擎中，对于仅依赖精确匹配的场景非常适合。

空间索引

基本特性

空间索引主要用于存储和查询地理空间数据（GIS 数据），支持数据类型如 POINT、LINESTRING、POLYGON 等。

索引结构

常用 R-Tree 算法来实现，对空间数据进行高效的范围搜索和邻近搜索。

支持情况

  • MyISAM 一直支持空间索引。
  • InnoDB 从较新的版本（如 MySQL 8.0）开始也支持空间索引。

前缀索引

基本特性

前缀索引是指只对字符类（如 VARCHAR、TEXT）字段的前若干字符建立索引，而不是整个字段，有助于减少索引占用的存储空间和提高索引构建效率。

使用限制

  • 匹配能力不如全值索引精确，查询时可能需要回表验证。
  • 在设计时需要平衡前缀长度，既要尽可能覆盖区分性，又要控制索引大小。

全文索引

基本特性

全文索引用于对文本内容进行自然语言全文搜索，支持复杂的关键字匹配、布尔搜索等。

支持情况

  • MyISAM 一直支持全文索引，适合需要搜索大段文本数据的应用（如博客、论坛等）。
  • InnoDB 从 MySQL 5.6 起也开始支持全文索引，但其实现和性能特性与 MyISAM 有所不同。

使用注意

全文索引更适合非结构化或半结构化的文本搜索，对于结构化数据推荐使用常规 B-Tree 索引。

索引统计信息

基本特性

MySQL 内部会对每个索引维护统计信息，如基数、分布情况等，这些信息帮助查询优化器选择最优执行计划。

更新与维护

  • 数据变动较大时，统计信息可能需要重新计算；可以通过 ANALYZE TABLE table_name 命令手动更新或通过 SHOW INDEX FROM table_name 查看或评估某个索引的基数。
  • 一般情况下无需手动干预，优化器会自动利用统计信息进行判断。

注意事项

统计信息不准确可能导致查询优化器选择非最优方案，因此在大批量数据更新后适时更新统计信息是个好习惯。

索引合并

基本特性

当查询条件中涉及多个不同的索引列时，MySQL 可能采用索引合并（Index Merge）策略，即同时利用多个索引进行过滤，再将结果合并后返回。

合并方式

  • 索引交集： 同时满足多个索引条件的记录。
  • 索引并集： 满足任一索引条件的记录，再通过额外过滤得到最终结果。

使用注意

  • 索引合并可以在某些情况下提高查询效率，但也可能引入额外的合并开销。
  • 优化时需要关注 EXPLAIN 输出，评估是否需要调整索引设计以避免依赖过多的索引合并操作。

总结

索引既是 MySQL 数据库中提升查询速度的利器，也是一把双刃剑：在查询场景中，合理设计索引能成倍地减少磁盘扫描量，大大加速等值、范围及排序等各类操作；在写入场景中，索引维护会增加额外开销，不当的索引布局反而拖慢插入和更新的性能。掌握索引原理与应用场景，将帮助数据库开发者和运维人员在面对各种复杂查询需求时，找到性能与存储之间的最佳平衡点，为系统提供更卓越的数据处理能力。

MySQL
MySQL MySQL索引

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！