✦我的博客

MySQL索引存储与回表机制详解

5 分钟阅读38 次浏览
MySQL索引B+树回表聚簇索引二级索引数据库性能覆盖索引

MySQL索引存储与回表机制详解

在MySQL的InnoDB存储引擎中，数据是按照B+树结构组织的。理解其底层存储原理和索引查询过程，对于SQL优化至关重要。

1. MySQL中数据是如何存储的？

InnoDB使用B+树作为索引结构，所有叶子节点存储的是【主键 | 完整的记录】，而非叶子节点则存储索引的值（用于导航）。但根据表上索引的不同情况，实际的存储形态会有差异：

  • 无任何索引：若表中没有定义任何索引，InnoDB会自动生成一个隐藏的主键 ROW_ID，并基于此创建聚簇B+树。叶子节点为 [ROW_ID | 完整记录]。此时执行 WHERE 查询只能进行全表扫描。
  • 只有主键索引：表本身就是一颗完整的B+树，数据行严格按照主键值升序排列。叶子节点里，主键旁就是完整的行数据，就像一本书的页码旁边直接印着正文。
  • 只有非主键索引：这种情况下实际上存在两颗B+树。主树仍是聚簇索引，按隐藏的 ROW_ID 排序，叶子节点存完整数据行。辅助树则是二级索引，单独建立B+树，其叶子节点只存 [索引列的值 | 隐藏的ROW_ID]。查询过程会发生回表：例如 SELECT * FROM user WHERE name = "ZHANGSAN"，先通过二级索引找到 name='ZHANGSAN' 对应的 ROW_ID，再拿着 ROW_ID 到主树中查找完整行。
  • 既有主键索引又有非主键索引：与上一条类似，同样有两颗B+树。聚簇索引的叶子节点按主键升序存放整行数据；每个二级索引也构成一棵B+树，叶子节点存放 [索引列的值 | 主键值]，并按索引列排序，相同索引列再按主键升序排列。

2. 回表

2.1 两个核心概念

聚簇索引：即主键索引，叶子节点直接包含整行数据，找到主键即获得完整记录。

二级索引：非主键索引，叶子节点只存放索引列的值和对应的主键值（如对 name 字段建的索引，叶子节点存储 name 和主键 id）。

2.2 回表完整流程

假设有表：

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  age INT,
  INDEX idx_name(name)
);

执行查询：

SELECT * FROM user WHERE name = '张三';

查询步骤：

  1. 走索引 idx_name：在这颗B+树上定位到 name = '张三' 的叶子节点。
  2. 获取主键：从该叶子节点中读取对应的主键值，假设为 id = 10。
  3. 回表查询：拿着主键 10 去聚簇索引的B+树中查找完整行（包含 id, name, age），将结果返回客户端。

关键点：二级索引中并没有 age 字段，因此必须回到聚簇索引中获取。

2.3 为什么会有回表？

当 SELECT 需要返回的列不完全包含在二级索引中时，就需要回表。如果查询所需的所有字段都能在二级索引的叶子节点中找到（例如只查询 id 和 name），则无需回表，这种情况叫做覆盖索引。

2.4 回表为什么影响性能？

  1. 随机 I/O：二级索引和聚簇索引是两颗独立的B+树，在磁盘上存储位置不同。回表操作意味着需要从有序的二级索引跳转到聚簇索引中进行随机读取。
  2. 一次查询变为两次查询：原本一次索引查找被拆分为查二级索引 + 查聚簇索引。

为什么会产生随机读写？

二级索引和聚簇索引虽然各自都是有序存储，但排序的键不同。二级索引按索引列（如 age）排序，聚簇索引按主键排序。当通过二级索引拿到一批主键时，主键的顺序往往是乱序的。

假设有一张表：

ID (主键)age (普通索引)
120
230
320
425
520

按照索引 age 排列，二级索引叶子节点的逻辑顺序为：

  • (20, 1)
  • (20, 3)
  • (20, 5)
  • (25, 4)
  • (30, 2)

当执行 WHERE age = 20 时，得到的主键 ID 是 (1, 3, 5)，它们在聚簇索引中也是顺序排列的，所以回表可能是顺序读取。但若范围查询 WHERE age BETWEEN 20 AND 30，得到的主键 ID 顺序变为 (1, 3, 5, 4, 2)，此时再拿着这些乱序的主键去聚簇索引中查找，就变成了随机 I/O。无论是 SSD 还是 HDD，随机读写都比顺序读写慢，这是慢查询中不易发现的性能瓶颈之一。

[!CAUTION] 如果索引列（如 name）包含大量重复值，数据库优化器可能会基于基数估算认为走索引不如全表扫描更高效，从而放弃使用索引，也就不存在回表问题了。

掌握索引的存储结构和回表机制，能够帮助我们更好地设计索引、编写高效的 SQL 查询，并利用覆盖索引来避免不必要的回表开销。

评论

加载评论中...