logo
简体中文

导航菜单

MySQL 面试精讲

聚簇索引 vs 非聚簇索引

🟡 中等

题目描述

解释聚簇索引和非聚簇索引的区别。什么是回表？如何避免回表？

示例场景

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    KEY idx_age (age)
);

-- 查询 1：回表
SELECT * FROM users WHERE age = 18;

-- 查询 2：不回表
SELECT id FROM users WHERE age = 18;

提示

  • 聚簇索引：数据和索引在一起
  • 非聚簇索引：索引和数据分离
  • 回表：从辅助索引获取主键，再查聚簇索引

解法

参考答案 (3 个标签)
聚簇索引 非聚簇索引 回表

聚簇索引（Clustered Index）

-- InnoDB 的聚簇索引
-- 主键索引就是聚簇索引

CREATE TABLE users (
    id INT PRIMARY KEY,      -- 聚簇索引
    name VARCHAR(50),
    age INT
);

-- B+树叶子节点存储：
-- | id (主键) | name | age | 其他列 |

特点：

  1. 数据和索引存储在一起
  2. 一张表只能有一个聚簇索引
  3. 通常是主键（如果没有主键，选择唯一非空索引）
  4. 数据按主键顺序存储

非聚簇索引（Secondary Index）

-- 辅助索引
CREATE INDEX idx_age ON users(age);

-- B+树叶子节点存储：
-- | age (索引列) | id (主键) |

特点：

  1. 索引和数据分离
  2. 叶子节点存储索引列和主键
  3. 一张表可以有多个非聚簇索引
  4. 需要回表才能获取完整数据

回表过程

-- 查询：SELECT * FROM users WHERE age = 18;

-- 步骤：
-- 1. 在 idx_age（非聚簇索引）中查找 age=18
--    找到：| age=18 | id=5 |
--    找到：| age=18 | id=10 |
--    找到：| age=18 | id=15 |

-- 2. 根据主键 id，在聚簇索引中查找完整数据
--    id=5 → | id=5 | name="Alice" | age=18 | ... |
--    id=10 → | id=10 | name="Bob" | age=18 | ... |
--    id=15 → | id=15 | name="Charlie" | age=18 | ... |

-- 总共：1 + 3 = 4 次索引查找

图示：

非聚簇索引 idx_age：
┌─────────────────┐
│ age=18, id=5    │──┐
│ age=18, id=10   │──┼───┐
│ age=18, id=15   │──┼───┼───┐
│ age=19, id=20   │   │   │   │
└─────────────────┘   │   │   │
                      │   │   │
                      ▼   ▼   ▼
              聚簇索引（主键索引）：
              ┌──────────────────────┐
              │ id=5, name="Alice"   │
              │ id=10, name="Bob"    │
              │ id=15, name="Charlie"│
              │ id=20, name="David"  │
              └──────────────────────┘

覆盖索引（避免回表）

-- ❌ 回表
SELECT * FROM users WHERE age = 18;

-- ✅ 不回表（覆盖索引）
SELECT id FROM users WHERE age = 18;

-- ✅ 不回表（联合索引）
CREATE INDEX idx_age_name ON users(age, name);
SELECT id, name FROM users WHERE age = 18;

原理：

-- 联合索引 idx_age_name：
-- | age | name | id |

-- 查询 SELECT id, name FROM users WHERE age = 18;
-- 索引已包含 id 和 name，无需回表

扩展：InnoDB vs MyISAM

索引实现对比

特性InnoDBMyISAM
聚簇索引✓✗
非聚簇索引✓✓
数据存储主键顺序数据文件独立

MyISAM 的索引结构

-- MyISAM 所有索引都是非聚簇索引
-- 所有索引的叶子节点都指向数据的物理地址

主键索引：
| id | 数据文件偏移量 |

辅助索引：
| age | 数据文件偏移量 |

-- 不需要回表，直接通过偏移量访问数据
-- 但无法支持范围查询的高效性

InnoDB vs MyISAM

特性InnoDBMyISAM
事务✓✗
外键✓✗
锁粒度行锁表锁
崩溃恢复✓✗
聚簇索引✓✗
全文索引5.6+✓

索引优化技巧

1. 使用覆盖索引

-- ❌ 回表
SELECT count(*) FROM users WHERE age > 18;

-- ✅ 覆盖索引（优化）
CREATE INDEX idx_age ON users(age);

-- ✅ 联合索引
CREATE INDEX idx_age_id ON users(age, id);
SELECT count(*) FROM users WHERE age > 18;

2. 避免 SELECT *

-- ❌ 回表
SELECT * FROM users WHERE age = 18;

-- ✅ 只查询需要的列
SELECT id, name FROM users WHERE age = 18;

-- ✅ 创建联合索引
CREATE INDEX idx_age_name_id ON users(age, name, id);

3. 主键设计

-- ❌ 使用过长的主键（辅助索引变大）
CREATE TABLE users (
    id VARCHAR(36) PRIMARY KEY,  -- UUID
    name VARCHAR(50),
    age INT
);

-- ✓ 使用自增主键
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- ✓ 使用雪花算法（分布式）
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

4. 延迟关联

-- ❌ 先回表再排序
SELECT * FROM users WHERE age > 18 ORDER BY name LIMIT 1000, 10;

-- ✓ 延迟关联（先获取 ID）
SELECT u.* FROM users u
JOIN (
    SELECT id FROM users WHERE age > 18 ORDER BY name LIMIT 1000, 10
) AS t ON u.id = t.id;
上一章节 为什么使用 B+树
下一章节 最左前缀原则

搜索