zt@dev▊
Database · #mysql#index#b+tree#optimization

MySQL索引优化：从B+Tree到实战调优

2020.07.20 9 min 3.6k 字
// 目录 · contents

引言

索引是MySQL性能优化的核心武器。一个设计良好的索引可以将查询速度提升几个数量级，而一个不合理的索引则可能成为数据库性能的瓶颈。本文将从B+Tree的底层结构出发，逐步深入到各种索引优化技术的原理与实战。

B+Tree 数据结构

为什么选择B+Tree

MySQL的InnoDB存储引擎选择B+Tree作为索引的底层数据结构，核心原因在于：

  1. 磁盘IO友好：B+Tree的节点大小通常与磁盘页（16KB）对齐，减少随机IO
  2. 范围查询高效：叶子节点通过双向链表连接，支持高效的范围扫描
  3. 树高度低：3-4层即可支撑千万级数据，每次查询只需3-4次IO
graph TD
    A[Root Node<br/>P1|30|P2|60|P3] --> B[Internal Node<br/>P1|10|P2|20|P3]
    A --> C[Internal Node<br/>P1|40|P2|50|P3]
    A --> D[Internal Node<br/>P1|70|P2|80|P3]
    B --> E[Leaf Node<br/>1,5,8,10]
    B --> F[Leaf Node<br/>12,15,18,20]
    B --> G[Leaf Node<br/>22,25,28]
    C --> H[Leaf Node<br/>30,33,35]
    C --> I[Leaf Node<br/>40,43,45]
    C --> J[Leaf Node<br/>50,53,55]
    E <-.->|双向链表| F
    F <-.->|双向链表| G
    G <-.->|双向链表| H
    H <-.->|双向链表| I
    I <-.->|双向链表| J

B+Tree vs B-Tree vs Hash

特性 B+Tree B-Tree Hash
范围查询 高效（叶子链表） 需要中序遍历 不支持
等值查询 O(log n) O(log n) O(1)
排序 天然有序 需额外排序 不支持
磁盘IO 更少（非叶节点不存数据） 较多 取决于冲突率

InnoDB的B+Tree页结构

InnoDB每个B+Tree节点对应一个16KB的页（Page），页内部结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+------------------------------------------+
| File Header (38 bytes)                    |
+------------------------------------------+
| Page Header (56 bytes)                    |
+------------------------------------------+
| Infimum + Supremum Records               |
+------------------------------------------+
| User Records (实际数据行)                  |
|   Record1 -> Record2 -> Record3 -> ...   |
+------------------------------------------+
| Free Space                                |
+------------------------------------------+
| Page Directory (槽位，用于二分查找)         |
+------------------------------------------+
| File Trailer (8 bytes)                    |
+------------------------------------------+

聚簇索引与二级索引

聚簇索引（Clustered Index）

InnoDB的主键索引就是聚簇索引，数据行直接存储在B+Tree的叶子节点中。

graph TD
    subgraph 聚簇索引
        A[主键: 1, 5, 10, 20] --> B[叶子节点<br/>PK=1, name=Alice, age=25<br/>PK=5, name=Bob, age=30]
        A --> C[叶子节点<br/>PK=10, name=Carol, age=28<br/>PK=20, name=Dave, age=35]
    end
    subgraph 二级索引 idx_name
        D[索引键: Alice, Bob, Carol, Dave] --> E[叶子节点<br/>name=Alice, PK=1<br/>name=Bob, PK=5]
        D --> F[叶子节点<br/>name=Carol, PK=10<br/>name=Dave, PK=20]
    end
    E -.->|回表查询| B
    F -.->|回表查询| C

聚簇索引的选择规则：

  1. 如果有主键，以主键作为聚簇索引
  2. 如果没有主键，选择第一个非空唯一索引
  3. 如果都没有，InnoDB会创建一个隐藏的row_id作为聚簇索引

回表查询的代价

通过二级索引查询时，如果需要获取非索引列的数据，就需要回表——根据二级索引中存储的主键值，再到聚簇索引中查找完整的行记录。

1
2
3
4
5
6
7
8
-- 假设有索引 idx_name(name)
-- 这个查询需要回表
SELECT * FROM users WHERE name = 'Alice';

-- 执行过程：
-- 1. 在 idx_name 中找到 name='Alice' 对应的主键 PK=1
-- 2. 用 PK=1 到聚簇索引中查找完整行数据
-- 3. 返回结果

联合索引与最左前缀原则

联合索引的结构

联合索引(a, b, c)在B+Tree中的排序规则是：先按a排序，a相同时按b排序，b相同时按c排序。

1
2
-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, created_at);
graph LR
    subgraph "联合索引 (user_id, status, created_at)"
        A["(1, 'paid', '2025-01-01')"] --> B["(1, 'paid', '2025-01-15')"]
        B --> C["(1, 'shipped', '2025-01-10')"]
        C --> D["(2, 'paid', '2025-01-05')"]
        D --> E["(2, 'shipped', '2025-01-20')"]
        E --> F["(3, 'paid', '2025-01-03')"]
    end

最左前缀匹配规则

1
2
3
4
5
6
7
8
9
10
11
-- 能使用索引的查询
SELECT * FROM orders WHERE user_id = 1;                                    -- 使用 (user_id)
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';               -- 使用 (user_id, status)
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid' AND created_at > '2025-01-01'; -- 全部使用

-- 不能使用索引的查询
SELECT * FROM orders WHERE status = 'paid';                                -- 跳过了 user_id
SELECT * FROM orders WHERE status = 'paid' AND created_at > '2025-01-01'; -- 跳过了 user_id

-- 部分使用索引
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2025-01-01';     -- 只使用 (user_id)，跳过了 status

索引列顺序的选择原则

  1. 区分度高的列放前面：区分度 = COUNT(DISTINCT col) / COUNT(*)
  2. 等值查询的列放前面，范围查询的列放后面
  3. 频繁出现在WHERE条件中的列优先
1
2
3
4
5
6
-- 查看列的区分度
SELECT
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT created_at) / COUNT(*) AS created_at_selectivity
FROM orders;

覆盖索引

覆盖索引是指查询所需的所有列都包含在索引中，不需要回表查询。

1
2
3
4
5
6
7
-- 联合索引 idx_user_status_time(user_id, status, created_at)

-- 覆盖索引查询（不需要回表）
SELECT user_id, status, created_at FROM orders WHERE user_id = 1;

-- 非覆盖索引查询（需要回表获取 amount 列）
SELECT user_id, status, amount FROM orders WHERE user_id = 1;

在EXPLAIN输出中，如果Extra列显示Using index，说明使用了覆盖索引。

1
2
3
4
5
6
EXPLAIN SELECT user_id, status, created_at FROM orders WHERE user_id = 1;
-- +----+-------+------+---------------------+---------+------+-------+-------------+
-- | id | type  | key  | key_len             | ref     | rows | Extra              |
-- +----+-------+------+---------------------+---------+------+-------+-------------+
-- |  1 | ref   | idx_user_status_time | 4      | const   |   10 | Using index |
-- +----+-------+------+---------------------+---------+------+-------+-------------+

索引下推（ICP）

索引下推（Index Condition Pushdown）是MySQL 5.6引入的优化。在使用联合索引时，将部分WHERE条件下推到存储引擎层进行过滤，减少回表次数。

sequenceDiagram
    participant Client as 客户端
    participant Server as Server层
    participant Engine as 存储引擎层

    Note over Server, Engine: 不使用ICP
    Server->>Engine: 查询 user_id=1
    Engine->>Server: 返回所有 user_id=1 的记录（含回表）
    Server->>Server: 在Server层过滤 status='paid'
    Server->>Client: 返回结果

    Note over Server, Engine: 使用ICP
    Server->>Engine: 查询 user_id=1 AND status='paid'
    Engine->>Engine: 在索引中过滤 status='paid'
    Engine->>Engine: 只对满足条件的记录回表
    Engine->>Server: 返回过滤后的记录
    Server->>Client: 返回结果
1
2
3
4
5
-- 索引 idx_user_status(user_id, status)
-- 查询条件同时涉及 user_id 和 LIKE 模式匹配
SELECT * FROM orders WHERE user_id = 1 AND status LIKE '%pai%';

-- EXPLAIN 中 Extra 显示 Using index condition 表示使用了ICP

索引合并（Index Merge）

当查询涉及多个单列索引时，MySQL可能会使用索引合并优化：

1
2
3
4
5
6
7
8
9
-- 假设有两个单独的索引：idx_user(user_id) 和 idx_status(status)

-- Index Merge - Intersection（交集）
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

-- Index Merge - Union（并集）
SELECT * FROM orders WHERE user_id = 1 OR status = 'paid';

-- EXPLAIN 中 type 显示 index_merge

通常来说，使用联合索引比索引合并更高效。如果频繁出现Index Merge，应考虑创建联合索引。

EXPLAIN 执行计划分析

EXPLAIN 关键字段

1
2
3
4
EXPLAIN SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 10;
字段 含义 关键值
type 访问类型 system > const > eq_ref > ref > range > index > ALL
possible_keys 可能使用的索引 -
key 实际使用的索引 NULL表示全表扫描
key_len 使用的索引长度 越短越好
rows 预估扫描行数 越少越好
filtered 过滤比例 越接近100越好
Extra 额外信息 Using index / Using filesort / Using temporary

type 访问类型详解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- const: 通过主键或唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE id = 1;

-- eq_ref: 联表时对驱动表使用主键或唯一索引
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- ref: 使用非唯一索引等值查询
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- range: 范围查询
EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2025-01-01' AND '2025-03-01';

-- index: 全索引扫描
EXPLAIN SELECT user_id FROM orders;

-- ALL: 全表扫描（最差）
EXPLAIN SELECT * FROM orders WHERE amount > 100;

慢查询优化实战

案例一：分页查询优化

1
2
3
4
5
6
7
8
9
10
11
-- 慢查询：深分页
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 扫描 1000020 行，丢弃前 1000000 行

-- 优化方案1：使用延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

-- 优化方案2：使用游标分页（推荐）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

案例二：排序优化

1
2
3
4
5
6
7
8
9
-- 慢查询：Using filesort
SELECT * FROM orders WHERE user_id = 1 ORDER BY amount DESC;

-- 如果索引是 idx_user_status_time(user_id, status, created_at)
-- amount 不在索引中，需要 filesort

-- 优化：创建合适的索引
ALTER TABLE orders ADD INDEX idx_user_amount(user_id, amount);
-- 现在排序可以利用索引

案例三：索引失效的常见原因

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 1. 对索引列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2025;        -- 索引失效
SELECT * FROM users WHERE created_at >= '2025-01-01'
    AND created_at < '2026-01-01';                         -- 使用索引

-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;             -- phone是varchar，索引失效
SELECT * FROM users WHERE phone = '13800138000';           -- 使用索引

-- 3. LIKE以通配符开头
SELECT * FROM users WHERE name LIKE '%Alice%';             -- 索引失效
SELECT * FROM users WHERE name LIKE 'Alice%';              -- 使用索引

-- 4. OR条件中有非索引列
SELECT * FROM users WHERE name = 'Alice' OR age > 25;     -- 如果age无索引，全表扫描

-- 5. NOT IN / NOT EXISTS / != 的情况
SELECT * FROM users WHERE status != 'active';              -- 可能索引失效（取决于数据分布）

案例四：JOIN优化

1
2
3
4
5
6
7
8
9
-- 确保JOIN字段有索引，且类型一致
-- 小表驱动大表
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.vip_level = 3;

-- 如果 users 表 vip_level=3 的记录很少（小结果集）
-- MySQL会选择 users 作为驱动表，orders 作为被驱动表
-- 需要确保 orders.user_id 上有索引

索引设计最佳实践

flowchart TD
    A[分析查询模式] --> B{频繁查询?}
    B -->|是| C[确定WHERE/JOIN/ORDER BY列]
    B -->|否| D[不创建索引]
    C --> E[计算列区分度]
    E --> F{需要多列?}
    F -->|是| G[创建联合索引<br/>等值列在前 范围列在后]
    F -->|否| H[创建单列索引]
    G --> I{能覆盖查询?}
    H --> I
    I -->|是| J[使用覆盖索引]
    I -->|否| K[考虑加入SELECT列]
    K --> L{索引太宽?}
    L -->|是| M[保持当前索引]
    L -->|否| J
    J --> N[验证EXPLAIN]
    M --> N
    N --> O[监控慢查询日志]

索引设计原则总结：

  1. 控制索引数量：单表索引不超过5-6个，避免过度索引影响写入性能
  2. 优先使用联合索引：一个联合索引可以替代多个单列索引
  3. 关注索引长度：使用前缀索引减少索引大小（ALTER TABLE t ADD INDEX idx_name(name(10))）
  4. 定期清理无用索引：通过sys.schema_unused_indexes查看未使用的索引
  5. 避免冗余索引：(a, b)已包含(a)，无需再单独创建(a)索引
1
2
3
4
5
6
7
8
9
10
-- 查看表的索引使用情况（MySQL 8.0+ sys schema）
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_database';

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'your_database';

-- 慢查询日志配置
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录到慢查询日志
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

总结

MySQL索引优化是一个系统工程，核心要点包括：

  • 理解B+Tree结构是一切优化的基础，它决定了索引的能力和局限
  • 聚簇索引与二级索引的区别直接影响查询是否需要回表
  • 联合索引的最左前缀原则指导我们如何设计索引列的顺序
  • 覆盖索引是消除回表查询的利器
  • EXPLAIN是诊断查询性能的必备工具
  • 实际优化中，需要结合慢查询日志和业务场景综合考虑

索引不是银弹，过多的索引会增加写入开销和存储空间。好的索引设计需要在查询性能和写入性能之间找到平衡点。

踩坑记录

业务增长后订单表突破 3000 万行，用户订单列表接口开始超时。SQL 是 WHERE user_id = ? AND status = ? ORDER BY create_time DESC，user_id 和 status 都有独立索引，但 EXPLAIN 显示走的是 status 索引，扫描了 120 万行。原因是 status 枚举值只有 5 种，区分度极低，优化器选错了索引。

加了 (user_id, status, create_time) 联合索引后，rows_examined 从 120 万降到 1。顺序很关键：user_id 放第一位是因为它区分度最高，create_time 放最后支持排序，这样整个查询走覆盖索引，不需要回表。

还有一个坑是 ORDER BY 方向。联合索引是 (user_id, create_time ASC)，业务要 ORDER BY create_time DESC，MySQL 依然可以用这个索引反向扫描，但如果是 (user_id ASC, create_time DESC) 混合排序，索引就用不上了。这个细节坑了我一次。

实测结果

数据量：3,200 万行订单，测试用户有 8,000 条记录，8 核 16G MySQL 5.7

方案 平均耗时 rows_examined 是否回表
走 status 索引（优化器误选） 1,800ms 1,200,000 是
加联合索引 (user_id, status, create_time) 3ms 1 否（覆盖索引）

索引额外空间：约 2.1GB，对于这个查询频率完全值得。

我的看法

覆盖索引的价值被严重低估。很多人加了索引还是慢，根本原因是回表。设计索引时我会先问自己：这条 SQL 能不能只靠索引就拿到所有需要的字段？ 能的话就设计成覆盖索引，性能会有数量级的差距。

另外，不要迷信 FORCE INDEX。优化器选错索引应该通过优化索引设计来解决，而不是强制指定——一旦数据分布变化，强制索引可能反而变成拖累，而且代码里埋的 hint 很难被后人注意到。

作者 · authorzt
发布 · date2020-07-20
篇幅 · length3.6k 字 · 9 min
许可 · licenseCC BY-SA 4.0
$ echo "comments" · 评论
VS Code REST Client：最好用的 API 测试工具之一 Linux Commands Cheat Sheet/Linux常用命令速查手册