聚簇索引与二级索引

InnoDB采用聚簇索引组织数据，二级索引通过主键关联聚簇索引。

聚簇索引

定义

聚簇索引将数据行按主键顺序存储在叶子节点，一张表只有一个聚簇索引。

结构示意

聚簇索引B+树：

            [根节点: 20|50]
           /       |        \
      [10|15]    [30|40]    [70|80]
       ↓         ↓          ↓
    叶子节点    叶子节点     叶子节点
   (id=10数据) (id=30数据)  (id=70数据)

叶子节点存储：完整数据行（按主键id排序）

特点

• 主键自动创建聚簇索引
• 数据按主键顺序物理存储
• 叶子节点存储完整数据行
• 一张表只能有一个聚簇索引

主键选择影响

-- ✅ 自增主键：数据顺序插入，页分裂少
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2)
);

-- ❌ 随机主键：数据随机插入，页分裂多
CREATE TABLE orders (
    uuid VARCHAR(36) PRIMARY KEY,  -- 随机UUID
    user_id INT,
    amount DECIMAL(10,2)
);

二级索引

定义

二级索引（辅助索引）叶子节点存储索引列值和主键值，非完整数据行。

结构示意

二级索引B+树（索引列: user_id）：

            [根节点: 100|200]
           /       |        \
      [50|80]    [150|180]  [250|300]
       ↓         ↓          ↓
    叶子节点    叶子节点     叶子节点
   (user_id=50  (user_id=150 (user_id=250
    + 主键id)    + 主键id)    + 主键id)

叶子节点存储：索引列值 + 主键值

特点

• 非聚簇存储，独立索引树
• 叶子节点存储主键值
• 查询完整数据需回表
• 可创建多个二级索引

创建二级索引

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    status TINYINT,
    INDEX idx_user(user_id),        -- 单列二级索引
    INDEX idx_user_status(user_id, status)  -- 联合二级索引
);

回表查询

概念

二级索引查询完整数据时，需先查二级索引获取主键值，再查聚簇索引获取完整数据行，称为回表。

查询过程

查询：SELECT * FROM orders WHERE user_id = 50;

1. 查二级索引idx_user：
   找到 user_id=50 的记录 → 获取主键 id=10
      ↓
2. 查聚簇索引：
   用主键 id=10 找到完整数据行
      ↓
3. 返回完整数据

总计：2次索引查找（二级索引 + 聚簇索引）

回表开销

-- ❌ 需回表
SELECT * FROM orders WHERE user_id = 1;
-- 扫描二级索引 + 回表扫描聚簇索引

-- ✅ 覆盖索引，无需回表
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);
SELECT user_id, status, amount FROM orders WHERE user_id = 1;
-- 只扫描二级索引，不回表

索引结构对比

主键查询效率

-- 主键查询（聚簇索引直接定位）
SELECT * FROM orders WHERE id = 100;
-- 1次索引查找，O(log n)

-- 二级索引查询（需回表）
SELECT * FROM orders WHERE user_id = 50;
-- 2次索引查找（二级索引 + 聚簇索引）

-- 覆盖索引查询（无需回表）
SELECT id, user_id FROM orders WHERE user_id = 50;
-- 1次索引查找

二级索引存储主键的好处

1. 减少索引维护开销
   -- 主键修改时只需修改聚簇索引
   -- 二级索引存储主键值，无需遍历修改

2. 覆盖索引优化
   -- 主键值在二级索引中，可直接返回

3. 节省空间
   -- 主键通常较小，减少索引大小

联合索引结构

CREATE INDEX idx_user_status ON orders(user_id, status);

二级索引叶子节点：
(user_id=50, status=1, 主键id=10)
(user_id=50, status=2, 主键id=11)
...

排序规则：
先按user_id排序，相同user_id按status排序，相同status按主键id排序

无主键表的处理

-- 无显式主键时，InnoDB自动选择：
CREATE TABLE t1 (
    user_id INT,
    name VARCHAR(50)
);

-- 1. 有唯一非空列 → 作为隐式主键
-- 2. 无唯一非空列 → 生成6字节隐藏主键DB_ROW_ID

-- 建议显式定义主键，避免隐藏列开销
CREATE TABLE t1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    name VARCHAR(50)
);

要点总结

• 聚簇索引一张表只有一个，叶子存储完整数据行
• 二级索引叶子存储索引值+主键值，可多个
• 二级索引查完整数据需回表，增加IO开销
• 覆盖索引可避免回表，提升查询效率
• 自增主键减少聚簇索引页分裂
• 建议显式定义主键，避免隐藏主键开销