聚簇索引和二级索引有什么区别？什么是回表和覆盖索引？

详细解析#

两种索引存的是什么#

B+ 树本身的结构见 InnoDB 为什么用 B+ 树。

聚簇索引怎么选：有主键就用主键；没有就用第一个所有列都是 NOT NULL 的唯一索引；都没有，InnoDB 用一个隐藏的 6 字节行 ID 生成聚簇索引。

二级索引为什么存主键，而不是行的物理地址：页分裂、页合并时，数据行会移动位置。存主键的话，行移动了二级索引也不用改；代价是每次回表都要再查一次聚簇索引。

回表的过程和代价#

以 user 表为例，idx_name_age 是 (name, age) 上的联合索引：

SELECT * FROM user WHERE name = 'Tom';

1. 在 idx_name_age 中找到第一条 name = 'Tom' 的记录，拿到主键 id = 7
2. 拿 id = 7 到聚簇索引里从根节点往下查，取出整行        ← 回表
3. 沿 idx_name_age 的叶子节点继续往后读，下一条还是 'Tom' 就重复第 2 步

每条匹配的记录都要单独回表一次，而这些主键在聚簇索引里往往是分散的，回表就成了大量随机 I/O。匹配的行越多，回表越贵，所以当优化器估算要回表的行很多时，会直接选择全表扫描。

覆盖索引和索引下推#

覆盖索引：查询用到的列都能在二级索引里拿到，就不用回表。二级索引里天然带着主键，所以 SELECT id, name, age FROM user WHERE name = 'Tom' 也是覆盖索引。常见做法是把高频查询要用的少量列加进联合索引，用索引变大换回表次数减少。

索引下推（Index Condition Pushdown，ICP，MySQL 5.6 引入）：

SELECT * FROM user WHERE name LIKE 'T%' AND age = 20;

name LIKE 'T%' 是范围条件，后面的 age 不能再用来在索引中定位（原因见 最左前缀原则）。

• 没有 ICP：存储引擎按 name 每找到一条记录就回表，把整行交给 Server 层，由 Server 层判断 age = 20
• 有 ICP：Server 层把 age = 20 下推给存储引擎，引擎直接用二级索引里的 age 值过滤，不满足的记录不回表

ICP 在 InnoDB 中只用于二级索引，目的是减少回表，EXPLAIN 的 Extra 显示 Using index condition。

主键为什么推荐自增#

• 自增主键：新记录总是追加到聚簇索引最右边的页，写满了就开一个新页，页基本是填满的
• 随机主键（如 UUID）：插入位置随机，目标页满了就要页分裂：申请新页，把一部分记录搬过去。MySQL 官方文档的说法是，顺序插入时页大约 15/16 满，随机插入时只有 1/2 到 15/16 满。同样的数据占用更多页，缓冲池能缓存的有效数据也更少
• 主键长度：每个二级索引都要存主键值，CHAR(36) 的 UUID 比 8 字节的 BIGINT 大得多，所有二级索引都会跟着变大

一定要用 UUID 时，MySQL 8.0 可以用 UUID_TO_BIN(UUID(), 1) 把它转成大致按时间递增的 16 字节二进制再存。

代码示例#

CREATE TABLE user (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL,
  city VARCHAR(50) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_name_age (name, age)
) ENGINE = InnoDB;

几条查询的 EXPLAIN 结果（示意，只保留关键列）：

面试官可能追问#

易错点#

• Using index 表示覆盖索引，Using index condition 表示索引下推，两者不是一回事
• 覆盖索引不只看 WHERE，SELECT、ORDER BY 用到的列也都要在索引里
• InnoDB 中"主键索引"和"聚簇索引"通常指同一个东西，一张表只有一个
• 主键不是越随机越好，也不是越长越好：它影响页分裂，也影响所有二级索引的大小