面试必备：MySQL 索引结构详解

为什么需要索引？

没有索引：全表扫描，10万条数据查一条，要扫 10万次。

有索引：二分查找，10万条数据查一条，最多 17 次。

无索引：  [1][2][3][4][5][6][7][8][9][10]...  → 查找 100000 次
            ↓   ↓   ↓   ↓   ↓   ↓   ↓   ↓
           找到目标要扫描全表

有索引：  [查找目标在第 50000 位]
            ↓ 二分
           [前半区|后半区]  → 只用查 17 次！

索引底层：数据结构演进

1. 哈希索引

键值对，O(1) 查找
┌────────┬────────┐
│  key   │ value  │
├────────┼────────┤
│ hash  │  0x123 │
│ code  │  0x456 │
│ name  │  0x789 │
└────────┴────────┘

缺点：

不支持范围查询 WHERE id > 10
不支持排序
哈希冲突多时性能退化

适用：等值查询，如 Redis

2. B-Tree（平衡多路查找树）

        [17 | 35]
       /    |     \
    [8]   [17]   [35|46]
   / | \  / \    / | \
 [1][4][8] [15][16][18][35][46][78]

特点：

每个节点有多个子节点（不像二叉树只有两个）
所有叶子节点在同一层
自平衡

3. B+Tree（MySQL 采用）

                    [17 | 35]
                   /    |     \
           ┌──────┘     │     └──────┐
           ▼            ▼            ▼
      [17|35]      [17|35]      [17|35]
         ↓            ↓            ↓
    ┌────┴────┐  ┌────┴────┐  ┌────┴────┐
    ↓         ↓  ↓         ↓  ↓         ↓
  [所有数据]  [所有数据]  [所有数据]  ← 叶子节点包含所有数据

B+Tree vs B-Tree：

特性	B-Tree	B+Tree
叶子节点	存放所有数据	只存放索引，叶子链表连接
非叶子节点	存放数据	只存放索引
搜索	最多 O(log n)	最多 O(log n)
范围查询	需要回表	叶子链表遍历即可
查询效率	不稳定（深度可能不同）	稳定（所有叶子深度相同）

MySQL InnoDB 的 B+Tree 结构

                        ┌─────────────────┐
                        │    根节点/索引    │
                        │   [主键索引页]    │
                        └────────┬─────────┘
                                 │
              ┌──────────────────┼──────────────────┐
              ▼                  ▼                  ▼
    ┌─────────────┐    ┌─────────────┐    ┌─────────────┐
    │  索引页 1   │    │  索引页 2   │    │  索引页 3   │
    │ [1][101][201]│    │ [51][151][251]│   │ [76][176][276]│
    └──────┬──────┘    └──────┬──────┘    └──────┬──────┘
           │                  │                  │
           ▼                  ▼                  ▼
   ┌───────────────┐  ┌───────────────┐  ┌───────────────┐
   │  叶子节点     │←→│  叶子节点     │←→│  叶子节点     │
   │ [1][name]...  │  │ [101][name]... │  │ [201][name]... │
   │ [2][name]...  │  │ [102][name]... │  │ [202][name]... │
   │ [3][name]...  │  │ [103][name]... │  │ [203][name]... │
   └───────────────┘  └───────────────┘  └───────────────┘

MySQL 存储：

16KB 为一页
B+Tree 深度一般 2-3 层（千万级数据）
3 层 B+Tree 可索引 20 亿条数据

聚簇索引 vs 非聚簇索引

这是 MySQL 面试的重中之重！

聚簇索引（Clustered Index）

定义：叶子节点存储完整的行数据。

InnoDB 表：
├─ 主键索引 → 叶子节点存整行数据（聚簇索引）
└─ 其他索引 → 叶子节点存主键值（非聚簇索引/辅助索引）

查询：
SELECT * FROM user WHERE id = 1
  → 直接走主键索引
  → 叶子节点就是数据，无需回表
  → 只需要一次查询！

非聚簇索引（辅助索引/二级索引）

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

查询：
SELECT * FROM user WHERE name = '张三'
  → 先走 name 索引
  → 叶子节点找到主键 id = 5
  → 再回到主键索引找完整数据（回表）
  → 需要两次查询！

两者对比

特性	聚簇索引	非聚簇索引
叶子节点	存储完整数据	存储索引+主键
数量	一个表只有一个	可以有多个
查找	直接获取数据	可能需要回表
插入	可能有页分裂	直接插入

回表查询

-- 先在 name 索引中查找
SELECT * FROM user WHERE name = '张三'

-- 执行过程：
-- 1. 在 name 索引树中查找，找到 name='张三' 对应的主键 id=5
-- 2. 根据 id=5 回主键索引查找完整行数据
-- 3. 返回结果

如何减少回表？

-- 只查索引列，不需要回表（覆盖索引）
SELECT name FROM user WHERE name = '张三'

-- 使用索引覆盖
SELECT name, age FROM user WHERE name = '张三' AND age = 18
-- 如果 (name, age) 是联合索引，可以一次查询

最左前缀原则（联合索引）

-- 创建联合索引
CREATE INDEX idx_name_age ON user(name, age)

-- 查询命中索引的情况：
✓ WHERE name = '张三'          -- 命中 name
✓ WHERE name = '张三' AND age = 18  -- 命中 name + age
✓ WHERE name IN ('张三', '李四')    -- 命中 name
✗ WHERE age = 18               -- 不命中（没有 name）
✗ WHERE age = 18 AND name = '张三'  -- 顺序错，但 MySQL 优化器会调整

索引失效的情况

-- 1. 左 LIKE 模糊匹配
SELECT * FROM user WHERE name LIKE '%张'  -- ✗ 索引失效
SELECT * FROM user WHERE name LIKE '张%'  -- ✓ 索引有效

-- 2. 使用函数/计算
SELECT * FROM user WHERE LEFT(name, 1) = '张'  -- ✗ 索引失效
SELECT * FROM user WHERE name = '张三'        -- ✓ 正常

-- 3. 类型转换
SELECT * FROM user WHERE name = 123  -- ✗ 索引失效（字符串列，参数是数字）

-- 4. OR 连接（OR 前后有非索引列）
SELECT * FROM user WHERE name = '张三' OR age = 18
-- ✗ name 有索引，age 没有，索引失效

-- 5. NOT / != / <>
SELECT * FROM user WHERE name != '张三'  -- ✗ 索引失效

-- 6. 隐式类型转换
ALTER TABLE user MODIFY phone VARCHAR(20);
SELECT * FROM user WHERE phone = 13800138000  -- ✗ 索引失效（字符串列用数字比较）

索引分类

类型	说明
主键索引	主键列，值唯一非空，聚簇索引
唯一索引	值唯一，可以有 NULL
普通索引	普通列
联合索引	多个列的组合索引
全文索引	文本内容搜索（MyISAM）

-- 主键索引
ALTER TABLE user ADD PRIMARY KEY(id);

-- 唯一索引
ALTER TABLE user ADD UNIQUE(email);

-- 普通索引
ALTER TABLE user ADD INDEX idx_name(name);

-- 联合索引
ALTER TABLE user ADD INDEX idx_name_age(name, age);

-- 全文索引（MyISAM）
ALTER TABLE article ADD FULLTEXT(title, content);

面试高频问题

Q1：为什么 MySQL 选 B+Tree 而不是 B-Tree？

范围查询友好：叶子节点链表连接，范围查询只需遍历链表
查询稳定：所有叶子节点深度相同，查询效率稳定
更适合磁盘：非叶子节点不存数据，可以放更多索引，降低树高

Q2：InnoDB 和 MyISAM 索引的区别？

特性	InnoDB	MyISAM
聚簇索引	支持（主键）	不支持
索引存储	叶子节点存数据	叶子节点存指针
数据存储	.ibd 文件	.MYD + .MYI
事务	支持	不支持

Q3：主键为什么建议用自增 ID？

插入时追加到末尾，避免页分裂
用 UUID 或字符串：插入位置不确定，可能导致大量页分裂

Q4：什么是页分裂？

InnoDB 以页为单位存储（16KB）。当插入数据导致页满时，需要分裂成两个页，影响性能。

页满了
┌────────────────────────┐
│ [1][2][3][4][5][6][7][8]│
└────────────────────────┘
        ↓ 插入 4.5
   需要分裂成两页
┌────────────┐ ┌────────────┐
│ [1][2][3][4]│ │[4.5][5][6]..│
└────────────┘ └────────────┘

自增 ID 追加到末尾，分裂概率低。

总结

索引结构：
├─ 哈希索引：O(1)，不支持范围
├─ B-Tree：多路平衡树
└─ B+Tree：B-Tree 变种，MySQL 采用

B+Tree 特点：
├─ 叶子节点链表连接
├─ 非叶子节点只存索引
└─ 深度一般 2-3 层

聚簇 vs 非聚簇：
├─ 聚簇：叶子节点存完整数据
└─ 非聚簇：叶子节点存主键，需回表

最左前缀原则：联合索引从左开始匹配

面试必备：MySQL 索引结构详解

面试必备：MySQL 索引结构详解

为什么需要索引？

索引底层：数据结构演进

1. 哈希索引

2. B-Tree（平衡多路查找树）

3. B+Tree（MySQL 采用）

MySQL InnoDB 的 B+Tree 结构

聚簇索引 vs 非聚簇索引

聚簇索引（Clustered Index）

非聚簇索引（辅助索引/二级索引）

两者对比

回表查询

如何减少回表？

最左前缀原则（联合索引）

索引失效的情况

索引分类

面试高频问题

Q1：为什么 MySQL 选 B+Tree 而不是 B-Tree？

Q2：InnoDB 和 MyISAM 索引的区别？

Q3：主键为什么建议用自增 ID？

Q4：什么是页分裂？

总结

评论区