MySQL之索引(2)（B树、B+树、索引分类、聚集索引、二级索引、回表查询）

目录

1. 前言
2. B树与B+树
   • B树的特点
   • B+树的特点
   • B树与B+树的对比
3. 索引分类
   • 聚集索引
   • 二级索引
   • 唯一索引
   • 全文索引
4. 聚集索引与二级索引
   • 聚集索引的特点
   • 二级索引的特点
   • 聚集索引与二级索引的区别
5. 回表查询
   • 回表查询的定义
   • 回表查询的性能问题
   • 回表查询的优化
6. MySQL索引的使用场景与优化
   • 合适的索引使用场景
   • 索引的优化策略
   • 索引的注意事项
7. 总结

前言

在上一部分的 MySQL 索引系列中，我们探讨了 MySQL 索引的基本概念和原理。本篇文章将深入讲解 B树与 B+树在索引中的应用，探讨索引的分类，包括聚集索引与二级索引的区别与特性，分析回表查询的工作原理以及如何优化这些操作。我们还将结合具体的场景与案例，帮助大家理解如何在实际开发中高效使用索引。

B树与B+树

B树的特点

B树（Balanced Tree）是一种自平衡的树形数据结构，广泛应用于数据库索引中。它具有以下几个特点：

1. 多路平衡树：B树是一个多路平衡树，它的每个节点可以有多个子节点（而不是二叉树的两个子节点），并且每个节点的子节点数是有界的。
2. 节点包含多个元素：B树中的每个节点不仅保存指向子节点的指针，还保存键值对数据。键值按照大小有序排列。
3. 根节点特殊：根节点可以是一个叶子节点，也可以是一个非叶子节点。
4. 所有叶子节点的深度相同：树的所有叶子节点都在同一层级，保证了B树的平衡性。
5. 查找、插入、删除操作的时间复杂度是O(log N)：由于树是平衡的，查找、插入、删除等操作的时间复杂度可以控制在对数级别。

B+树的特点

B+树是B树的变体，常用于数据库和文件系统的索引结构。它相比于B树具有以下几点不同和优势：

1. 所有值存在叶子节点：与B树不同，B+树的所有数据都存储在叶子节点上，而非叶子节点仅用于索引。
2. 叶子节点链表化：B+树的叶子节点通过指针相互连接，形成一个链表。这样，B+树在区间查询时比B树更加高效，因为可以通过链表顺序遍历叶子节点。
3. 非叶子节点仅存储索引：非叶子节点只存储键值，用于引导查询，而不保存实际数据，节省了内存空间。
4. 更高的查询效率：由于所有数据都集中在叶子节点，B+树通常能够提供更高效的范围查询操作。

B树与B+树的对比

从对比表中可以看出，B+树相比B树更适合数据库索引，特别是当涉及到范围查询时，B+树的表现更加优秀。因此，MySQL InnoDB 存储引擎使用的是 B+ 树作为默认的索引结构。

索引分类

在MySQL中，索引可以分为以下几类：

聚集索引

聚集索引是数据表中一种特殊类型的索引，表中的数据存储顺序就是索引的顺序。换句话说，表中的数据行按聚集索引的顺序排列，并且每个表只能有一个聚集索引。

聚集索引的特点

1. 数据与索引合二为一：在聚集索引中，数据存储在叶子节点上，数据的物理存储顺序和索引的逻辑顺序一致。
2. 唯一性：一个表只能有一个聚集索引。
3. 效率高：聚集索引对于点查找和范围查询非常高效。
4. 性能影响：聚集索引的更新可能会导致大量的行数据搬移，因此对于频繁更新的表，聚集索引的性能可能会有所下降。

示例：聚集索引

假设我们有一个学生表：

sqlCopy Code
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

在上面的表中，student_id 列作为主键，它会自动成为聚集索引。因为聚集索引决定了数据的存储顺序，所有数据将按照 student_id 的顺序存储。

二级索引

二级索引（Secondary Index）是指在聚集索引之外创建的额外索引。二级索引的叶子节点不直接包含数据行，而是存储数据行的指针（即聚集索引的主键值）。因此，二级索引的查找需要先找到主键，再通过主键定位数据。

二级索引的特点

1. 通过主键间接定位数据：二级索引的叶子节点存储的是主键值，而不是数据行本身。
2. 索引不影响数据存储顺序：二级索引与数据存储顺序无关，它只是在现有数据表上创建的额外索引。
3. 查询效率较高：对于非主键的查询，二级索引能够显著提高查询速度。
4. 回表查询：当通过二级索引查询数据时，需要回表查询（即通过主键再次查找数据）。

示例：二级索引

假设我们在 students 表中添加一个二级索引：

sqlCopy Code
CREATE INDEX idx_name ON students (name);

此时，MySQL会为 name 字段创建一个二级索引。查询 name 字段时，会先通过二级索引查找 name 对应的主键 student_id，然后通过 student_id 定位数据行。

唯一索引

唯一索引是指索引列中的值必须是唯一的，这保证了数据的唯一性。唯一索引和普通索引的区别在于，唯一索引会强制约束索引列的值不重复。

示例：唯一索引

sqlCopy Code
CREATE UNIQUE INDEX idx_email ON users (email);

在上面的例子中，email 列的唯一索引确保了 users 表中的每个邮箱地址都是唯一的。

全文索引

全文索引用于进行文本数据的快速搜索，特别适用于大文本字段的查询（如 TEXT 或 VARCHAR 类型）。全文索引不仅能够加速全文检索，还能支持模糊查询。

示例：全文索引

sqlCopy Code
CREATE FULLTEXT INDEX idx_content ON articles (content);

在上面的例子中，content