B+ 树索引与索引设计

"给这个字段加个索引"是后端最常见的动作，但加错了比不加更糟：写放大、空间浪费、优化器选错索引。这篇文章先讲清 InnoDB 的 B+ 树到底长什么样，再讲联合索引的最左前缀、覆盖索引、索引下推，最后给出一套可执行的索引设计流程。面向已经会写 SQL、需要为业务表设计索引的开发者，基准版本 MySQL 8.0 + InnoDB。

一、为什么是 B+ 树

候选结构有哈希、二叉搜索树、跳表、B 树、B+ 树。InnoDB 选 B+ 树，原因是三者的交集：

• 磁盘 I/O 以页为单位，一个节点最好装下一整页。B+ 树的非叶子节点只存键和指针，16 KB 的页能放上千个键，三层的树可以覆盖上千万行。
• 范围查询需要有序遍历。B+ 树的叶子节点用双向链表相连，找到起点后顺序扫即可；B 树需要中序回溯。
• 树高低意味着 I/O 少。假设主键 BIGINT（8 字节）+ 指针（6 字节），一页约存 1170 个键；叶子页按每行 1 KB 算存 16 行，三层可存 1170 × 1170 × 16 ≈ 2190 万行。

哈希索引只支持等值查询，跳表适合内存，B 树非叶子也存数据导致扇出低。结论就落在 B+ 树上。

需要补充的是，B+ 树的高扇出不是靠"减少数据"实现的，而是靠只把键留在非叶子节点。B 树的每个节点都携带完整行数据，一页能放的键就少一个数量级，同样数据量下树高会多出一到两层。对一个十亿行的表来说，多一层意味着每次查询多一次随机 I/O，在机械盘上是 10 ms 量级的差距。另一个常被忽略的细节是页分裂：当插入的键不是递增时，目标页可能已经写满，InnoDB 会申请新页并把约一半数据搬过去，同时更新父节点的指针。这就是随机主键（UUID）写入代价高的根本原因——它让顺序写退化成随机写，还让页的填充率长期维持在 50% 左右，同样的数据占用近两倍空间。

二、聚簇索引与二级索引

InnoDB 的表就是一棵以主键组织的 B+ 树，称为聚簇索引（clustered index），叶子节点直接存放整行数据。如果没有主键，选第一个非空唯一索引；都没有则用隐藏的 DB_ROW_ID。

二级索引（secondary index）的叶子节点存的是索引列 + 主键值。因此通过二级索引查非索引列时，要先在二级索引找到主键，再回聚簇索引取整行，这个过程叫回表。一次二级索引查询可能是两次 B+ 树查找，这也是"少用 SELECT *"的底层原因。

CREATE DATABASE IF NOT EXISTS idx_demo DEFAULT CHARACTER SET utf8mb4;
USE idx_demo;

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NOT NULL,
  status      TINYINT         NOT NULL DEFAULT 0,
  amount      DECIMAL(12,2)   NOT NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  remark      VARCHAR(255)    NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY idx_user_created (user_id, created_at),
  KEY idx_status (status)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 造 50 万行
SET SESSION cte_max_recursion_depth = 10000000;

INSERT INTO orders (user_id, status, amount, created_at)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 500000
)
SELECT n % 20000, n % 5, ROUND(RAND() * 1000, 2),
       DATE_ADD('2026-01-01', INTERVAL (n % 300) DAY)
FROM seq;

三、联合索引与最左前缀

联合索引 (a, b, c) 的排序规则是：先按 a 排，a 相同按 b 排，b 相同按 c 排。B+ 树只能沿一个方向有序扫描，所以只能从最左列开始匹配。

-- 能走 idx_user_created
SELECT * FROM orders WHERE user_id = 100;
SELECT * FROM orders WHERE user_id = 100 AND created_at > '2026-05-01';
SELECT * FROM orders WHERE created_at > '2026-05-01' AND user_id = 100;  -- 优化器会重排

-- 不能走 idx_user_created
SELECT * FROM orders WHERE created_at > '2026-05-01';

范围查询会截断后续列：WHERE user_id = 100 AND created_at > '2026-05-01' 中，created_at 之后如果还有列就无法继续用于定位，但可以用于索引下推过滤。

索引下推（ICP）

MySQL 5.6 引入 Index Condition Pushdown。没有 ICP 时，存储引擎按索引找到记录就回表，再由 Server 层过滤；有 ICP 时，把能用于索引的条件也下推到存储引擎，在回表前先过滤，减少回表次数。

ALTER TABLE orders ADD KEY idx_user_status_amount (user_id, status, amount);

-- status 是范围后的列，但可被 ICP 用于过滤
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND status = 1 AND amount > 500;

EXPLAIN 的 Extra 列出现 Using index condition 即表示走了 ICP。

四、覆盖索引

如果查询需要的列全部在索引里，就不需要回表。Extra 显示 Using index。

-- 覆盖索引：只取 user_id 和 created_at
EXPLAIN SELECT user_id, created_at FROM orders
WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;

这条 SQL 还能避免 filesort，因为 (user_id, created_at) 天然按 user_id、created_at 有序，ORDER BY created_at 在 user_id 固定时可直接利用索引顺序。

SELECT * 会破坏覆盖索引，这是"禁止 SELECT *"最实在的理由。

五、索引设计流程

1. 按查询模式而不是按字段建索引

先收集高频 SQL，按 WHERE、ORDER BY、GROUP BY、JOIN 的列组合设计，而不是"每个看起来会查的字段都加一个"。

2. 等值列在前，范围列在后

联合索引 (a, b, c)，如果 a、b 是等值、c 是范围，能最大化利用。反过来 (a, c, b) 中 c 的范围会截断 b。

3. 区分度高的列靠前

区分度 = COUNT(DISTINCT col) / COUNT(*)。区分度低的列（性别、状态）单独建索引收益很小，放在联合索引里作为过滤补充更合适。

SELECT
  COUNT(DISTINCT status) / COUNT(*) AS status_sel,
  COUNT(DISTINCT user_id) / COUNT(*) AS user_sel
FROM orders;

4. 控制索引数量

每个二级索引都是一棵独立的 B+ 树，写入时要维护。经验值是单表索引不超过 5 个，写多读少的表更要克制。冗余索引（(a) 与 (a, b) 并存）应删掉前者。

-- 找出冗余索引：前缀被其他索引覆盖
SELECT * FROM sys.schema_redundant_indexes;
-- 找出从未使用的索引
SELECT * FROM sys.schema_unused_indexes;

5. 主键选择

• 用自增 BIGINT，避免页分裂和随机写。
• 不要用 UUID 做主键：随机插入导致页分裂、页利用率低，二级索引也因为要存 36 字节主键而膨胀。
• 如果必须用字符串主键，考虑 UUID_TO_BIN(uuid, 1) 转成 16 字节二进制并重排时间高位。

6. 索引与写放大的权衡

每个二级索引都对应一次额外的 B+ 树维护：插入时要定位并写入叶子页，更新索引列时要先删后插，页分裂时还要更新父节点。因此索引不是"越多查询越快"，而是在读与写之间做交换。经验做法是：先用慢日志找出 TOP 查询，为这些查询设计索引，再回头看 schema_unused_indexes 清理没人用的索引。不要为了"以后可能查"提前建索引，那部分成本从上线第一天就在付。

六、前缀索引与函数索引

长字符串列可以只索引前缀：

ALTER TABLE orders ADD KEY idx_remark_prefix (remark(20));

-- 选择前缀长度：让前缀区分度接近全列
SELECT
  COUNT(DISTINCT LEFT(remark, 10)) / COUNT(*) AS p10,
  COUNT(DISTINCT LEFT(remark, 20)) / COUNT(*) AS p20,
  COUNT(DISTINCT remark) / COUNT(*)          AS full_sel
FROM orders;

注意前缀索引不能用于覆盖索引，也无法支持 ORDER BY 全列排序。

8.0.13 起支持函数索引（实际上是隐藏的生成列 + 索引）：

ALTER TABLE orders
  ADD KEY idx_month ((DATE_FORMAT(created_at, '%Y-%m')));

EXPLAIN SELECT COUNT(*) FROM orders
WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-05';

5.7 没有函数索引，只能加生成列再建索引。

七、常见坑 / 最佳实践

1. 不要在低区分度列上单独建索引。status 只有 5 个值，全表 50 万行时每个值 10 万行，优化器大概率直接全表扫描，索引白占空间。要么放进联合索引，要么不建。
2. 联合索引顺序按"等值 → 范围 → 排序"排。把范围列放中间会让后面的等值列失效，是最常见的顺序错误。
3. ORDER BY 想用索引，排序列必须连续且方向一致。ORDER BY a ASC, b DESC 在 8.0 之前无法用索引（8.0 支持降序索引，但需显式 DESC 建索引）。
4. 索引列不要参与运算或函数。WHERE DATE(created_at) = '2026-05-01' 会失效，改成范围 created_at >= '2026-05-01' AND created_at < '2026-05-02'。
5. 上线前用 sys.schema_unused_indexes 和 schema_redundant_indexes 做一次体检，但注意统计周期要覆盖完整的业务周期（至少一个月），否则会误删低频但关键的索引。

八、小结

• InnoDB 只有 B+ 树一种主结构，聚簇索引叶子存整行，二级索引叶子存"索引列 + 主键"，因此有回表成本。
• 联合索引遵循最左前缀，范围列会截断后续列；ICP 能在回表前用上被截断的列。
• 覆盖索引消除回表，SELECT * 是覆盖索引的头号杀手。
• 索引设计从查询模式出发：等值在前、范围在后、高区分度优先、总数受控。
• 自增 BIGINT 主键是默认最优解，UUID 主键的代价远大于它的便利。