聚簇索引回表与覆盖索引

一句话InnoDB 的表本身就是一棵以主键为 key 的 ​B 树聚簇索引​。普通索引的叶子节点存的是主键值查数据时需要​回表​。覆盖索引让查询不回表是性能优化的核心手段。聚簇索引Clustered Index什么是聚簇索引InnoDB 的表 一棵以主键为 key 的 B 树。这棵树就是聚簇索引。B 树的 key 主键 id B 树的叶子节点 整行数据id, name, age, ... 所以 SELECT * FROM user WHERE id 100; → 从 B 树根节点开始3 层就到叶子节点直接拿到整行数据 ✅主键索引的叶子节点就是数据本身。找到了 key 就找到了整行数据。聚簇索引的特性① 一个表只能有一个聚簇索引 因为数据在磁盘上只能按一种顺序物理排列 ② 主键就是聚簇索引 CREATE TABLE user (id BIGINT PRIMARY KEY, ...) → id 就是聚簇索引 ③ 没有主键怎么办 MySQL 优先级 有主键 → 主键做聚簇索引 ✅ 没主键 → 第一个 NOT NULL 唯一索引 → 聚簇索引 都没有 → 隐藏的 ROW_ID → 聚簇索引为什么不推荐 UUID 做主键自增 IDbigint 新数据 → key 递增 → 追加到 B 树最右边 → 不发生页分裂 → 性能好 ✅ UUID字符串 新数据 → key 随机 → 插入到 B 树中间 → 页分裂频繁 → 性能差 ❌ 分布式场景替代方案雪花算法 - 64 位 long8 字节和 bigint 一样 - 按时间递增追加到 B 树末尾 - 全局唯一非聚簇索引普通索引普通索引的叶子节点存什么CREATEINDEXidx_ageONuser(age);idx_age 这棵 B 树 内部节点存 age路标 叶子节点存 age 主键 id不是整行数据 所以 SELECT * FROM user WHERE age 25; ① 走 idx_age → 找到叶子节点 → 拿到主键 id100 ② 拿 id100 去聚簇索引再查一次 → 拿到整行数据 ③ 第②步就叫回表回表Back to Table什么是回表普通索引不存整行数据只存主键 id。 查到主键 id 后还需要再去聚簇索引查一次才能拿到整行数据。 这第二次查询就叫回表。 回表的代价多一次 B 树查找多几次磁盘 I/O怎么避免回表用覆盖索引。-- 假设有一个联合索引idx_age_name(age, name)-- ❌ 会回表SELECT*FROMuserWHEREage25;-- 因为 * 包含了 age、name 之外的其他字段-- 普通索引的叶子节点没有这些字段 → 必须回表-- ✅ 不回表覆盖索引SELECTage,nameFROMuserWHEREage25;-- age 和 name 都在 idx_age_name 的叶子节点里 → 直接返回 ✅覆盖索引定义查询的字段全部包含在索引的叶子节点中不需要回表。 覆盖索引 索引覆盖了查询所需的全部字段 例子 表user(id, name, age, address) 索引idx_age_name(age, name) SELECT age, name FROM user WHERE age 25 → age 和 name 都在 idx_age_name 的索引树里 → MySQL 只走索引树不回表 ✅Oracle/达梦 vs MySQL 索引差异MySQL InnoDB 聚簇索引叶子节点 → 存整行数据 普通索引叶子节点 → 存主键 id 回表用主键 id 再去聚簇索引查一次 Oracle/达梦 主键索引叶子节点 → 存 ROWID物理地址 普通索引叶子节点 → 存 ROWID物理地址 回表用 ROWID 直接定位到数据行一次寻址 所以 Oracle/达梦也需要回表但 ROWID 是物理地址 定位数据比 MySQL 的主键 id 二次查询更快一步。 面试回答模板InnoDB 的表本身就是一棵以主键为 key 的 B 树聚簇索引。 一个表只能有一个聚簇索引因为数据只能按一种顺序物理排列。 普通索引的叶子节点存的是主键值查数据时需要先走普通索引 拿到主键 id再去聚簇索引查整行数据——这叫回表。 避免回表的方法是覆盖索引把查询中用到的字段都包含在索引里。 这样索引树本身就包含了所有需要的数据不用回表。 这也是为什么建联合索引时除了条件字段还要考虑 select 的字段。参考来源《MySQL 技术内幕InnoDB 存储引擎》MySQL 官方文档Clustered and Secondary Indexes