MySQL / 系统课时

理解聚簇索引与二级索引

理解聚簇索引与二级索引

分析 InnoDB 查询成本时,关键不是只看“有没有索引”,而是看一次索引访问最终要取到什么数据。本篇用一张表说明聚簇索引叶子保存整行、二级索引叶子保存二级键与主键值,以及二级索引查询何时需要再回到聚簇索引取行。

学习目标

  • 区分 InnoDB 聚簇索引与二级索引叶子记录保存的内容
  • 解释二级索引如何通过主键定位完整行
  • 判断覆盖索引与回表访问在成本上的差异

从一张表看两类索引

下面创建一张带主键和普通二级索引的表。PRIMARY KEY (id) 定义聚簇索引,idx_email (email) 是二级索引。

sql
CREATE TABLE customer (
  id BIGINT NOT NULL,
  email VARCHAR(128) NOT NULL,
  display_name VARCHAR(64) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_email (email)
) ENGINE = InnoDB;

INSERT INTO customer (id, email, display_name, created_at) VALUES
  (101, 'a@example.com', 'Alice', '2026-09-01 10:00:00'),
  (205, 'b@example.com', 'Bob',   '2026-09-01 10:05:00');

预期结果

text
Query OK, 0 rows affected
Query OK, 2 rows affected

在 InnoDB 中,聚簇索引的叶子记录就是表数据记录,因此按主键 id=205 找到叶子后即可取得 email、display_name、created_at 等列。二级索引 idx_email 的叶子记录则至少包含二级键 email 和对应的主键值 id,它不会再存一份完整行。

聚簇索引与二级索引的叶子记录和回表路径

为什么二级索引叶子保存主键

InnoDB 用主键作为聚簇索引键,因此二级索引若要定位整行,需要一个稳定的“行地址”。它保存的就是主键值,而不是物理页偏移。这样记录因页分裂、合并或移动而改变物理位置时,二级索引不必依赖易变化的地址。

这也解释了一个常见成本:如果按 email 找到 b@example.com,二级索引叶子先得到主键 205;查询还需要 display_name 时,再以 205 查聚簇索引。这第二次 B+Tree 定位通常称为回表。

用查询验证访问路径

先查询二级索引自身已经具备的 id 与 email。因为二级索引叶子同时保存这两项,优化器可以把它作为覆盖索引使用。

sql
EXPLAIN
SELECT id, email
FROM customer
WHERE email = 'b@example.com';

预期结果

text
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customer | NULL       | ref  | idx_email     | idx_email | <VALUE> | const | <VALUE> | <VALUE> | Using index |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+

Extra 中的 Using index 表示该查询所需列可直接由索引记录提供。key_len、rows、filtered 等估算值会随版本、字符集和统计信息变化,因此用 <VALUE> 表示。

再把 display_name 加入查询。它不在 idx_email 叶子记录中,执行时通常需要先走二级索引,再依据主键访问聚簇索引记录。

sql
EXPLAIN
SELECT id, email, display_name
FROM customer
WHERE email = 'b@example.com';

预期结果

text
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customer | NULL       | ref  | idx_email     | idx_email | <VALUE> | const | <VALUE> | <VALUE> | NULL  |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

EXPLAIN 不会直接写出“回表一次”,但当使用二级索引且所需列无法被该索引覆盖时,可以从索引结构推导出还需要读取聚簇索引记录。真正的物理 I/O 次数还受缓冲池命中、页布局和一次返回多少行影响。

主键宽度会影响所有二级索引

二级索引叶子要携带主键,因此主键越宽,每个二级索引条目通常也越大。表上二级索引越多,这个放大效应越明显:索引占用更多页,同样内存能缓存的条目减少,扫描更多二级索引项时也会搬运更多字节。

因此设计 InnoDB 主键时,不只要考虑主键查询本身,还要考虑它作为“二级索引定位符”的全局成本。能满足业务唯一性和生成需求的前提下,较短、稳定的主键通常更有利于索引体积与缓存效率。

什么时候回表成本最明显

单次等值查询只返回一两行时,回表通常不是首要问题;真正需要警惕的是二级索引先命中大量记录,而查询又需要读取索引外的列,此时可能发生大量聚簇索引访问。优化时应结合选择性、返回行数和实际列需求判断,而不是机械地给所有查询追加覆盖列。

覆盖索引能省掉回表,但也会扩大二级索引、增加写入维护成本。正确的判断顺序是:先确认查询会通过哪个索引筛出多少行,再判断这些行是否值得通过增加索引列来减少回表。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

理解聚簇索引与二级索引

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通