Skip to content

聚簇索引和非聚簇索引是什么？他们分别用在什么场景？ 回表是什么？非聚簇索引一定会回表吗？

聚簇索引和非聚簇索引是什么

  • 聚簇索引（Clustered Index）：
  • 索引和数据行存储在一起，表的数据按索引键排序。
  • 每张表只能有一个聚簇索引（通常是主键）。
  • 非聚簇索引（Non-Clustered Index）：
  • 索引和数据分开存储，索引叶子节点存键值和指向数据的指针。
  • 一张表可有多个非聚簇索引。

分别用在什么场景

  • 聚簇索引：
  • 主键查询、范围查询、排序场景。
  • 非聚簇索引：
  • 频繁查询的非主键列、多条件过滤。

回表是什么

  • 定义：
  • 回表是指非聚簇索引查询后，需再次访问聚簇索引（数据页）获取完整行数据的过程。

非聚簇索引一定会回表吗

  • 答：
  • 不一定。如果查询字段被非聚簇索引覆盖（覆盖索引），无需回表。

核心点

  • 聚簇索引高效但唯一，非聚簇索引灵活但可能回表。

1. 聚簇索引和非聚簇索引详解

(1) 聚簇索引

  • 结构：
  • 叶子节点存储整行数据（数据即索引）。
  • InnoDB 中，主键默认是聚簇索引。
  • 若无主键，选唯一非空索引，否则生成隐式 ROW_ID。
  • 特点：
  • 数据按键顺序存储，范围查询快。
  • 插入和更新可能重排数据页（页分裂）。
  • 存储示例：
  • 表 user(id, name, age)，id 是主键：
聚簇索引（B+树）：
叶子节点： [id=1, name=Alice, age=25] -> [id=2, name=Bob, age=30]

(2) 非聚簇索引

  • 结构：
  • 叶子节点存储索引键和主键值（InnoDB）或数据指针（MyISAM）。
  • 指向聚簇索引或数据行。
  • 特点：
  • 占用额外空间，维护成本高。
  • 适合多字段查询。
  • 存储示例：
  • 索引 idx_name 在 name 列：
非聚簇索引（B+树）：
叶子节点： [name=Alice, id=1] -> [name=Bob, id=2]

图示

聚簇索引:    [键+数据] -> [键+数据]
非聚簇索引:  [键+主键] -> 指向聚簇索引

2. 适用场景

(1) 聚簇索引场景

  • 主键查询：
  • 例：SELECT * FROM user WHERE id = 1。
  • 直接定位数据，无额外 IO。
  • 范围查询：
  • 例：SELECT * FROM user WHERE id BETWEEN 10 AND 20。
  • 数据顺序存储，扫描连续。
  • 排序：
  • 例：SELECT * FROM user ORDER BY id。
  • 已按键排序，无需额外操作。
  • 典型应用：
  • 订单表（order_id 主键）。
  • 用户表（user_id 主键）。

(2) 非聚簇索引场景

  • 非主键查询：
  • 例：SELECT name FROM user WHERE name = 'Alice'。
  • 索引 idx_name 加速定位。
  • 多条件过滤：
  • 例：SELECT * FROM user WHERE age = 25 AND city = 'NY'。
  • 复合索引 idx_age_city。
  • 频繁查询列：
  • 例：日志表按时间查询。
  • 典型应用：
  • 商品表（索引 category_id）。
  • 日志表（索引 create_time）。

3. 回表详解

(1) 回表定义

  • 过程：
  • 非聚簇索引找到主键值后，需访问聚簇索引获取完整行。
  • 示例：
CREATE INDEX idx_name ON user(name);
SELECT id, name, age FROM user WHERE name = 'Alice';
  • 流程：
    1. idx_name 查到 name=Alice 的 id=1。
    2. 用 id=1 访问聚簇索引，获取 age 等字段。
  • 代价：
  • 额外 IO，性能下降。

(2) 非聚簇索引一定会回表吗？

  • 答：
  • 不一定。如果查询字段全在非聚簇索引中（覆盖索引），无需回表。
  • 覆盖索引：
  • 例：
SELECT name FROM user WHERE name = 'Alice';

- `idx_name` 包含 `name`，直接返回。
  • 复合索引例：
CREATE INDEX idx_name_age ON user(name, age);
SELECT name, age FROM user WHERE name = 'Alice';

- `idx_name_age` 包含 `name` 和 `age`，无需回表。
  • 验证：
  • 用 EXPLAIN：
Extra: Using index
  • 表示覆盖索引，无回表。

(3) 回表优化

  • 加覆盖索引：
  • 例：CREATE INDEX idx_name_age ON user(name, age)。
  • 精简查询：
  • 例：SELECT name 替代 SELECT *。
  • 调整表结构：
  • 将常用字段放入索引。

4. 对比总结

特性 聚簇索引 非聚簇索引
存储 数据 + 索引 键 + 主键/指针
数量 1 个 多个
查询效率 直接访问数据 可能需回表
场景 主键、范围、排序 非主键、多条件
空间 无额外空间 占用额外空间

5. 延伸与面试角度

  • 与 InnoDB/MyISAM：
  • InnoDB：主键是聚簇，二级索引非聚簇。
  • MyISAM：全是非聚簇，数据分开。
  • 实际应用：
  • 电商：订单表用聚簇索引（order_id）。
  • 搜索：商品表用非聚簇索引（category_id）。
  • 面试点：
  • 问“区别”时，提存储和回表。
  • 问“优化”时，提覆盖索引。

总结

聚簇索引将数据和索引合并，适合主键和范围查询；非聚簇索引分开存储，适合非主键查询，可能需回表。覆盖索引可避免回表。面试时，可提示例或画 B+ 树，展示理解深度。