B+ 树与索引结构

为什么用 B+ 树

索引为什么最后落到 B+ 树，不是为了概念好看，而是它更贴近磁盘页和范围查询的实际需求：

  • 二叉树：深度太深，每次查询需要多次磁盘IO
  • 哈希表：范围查询无法用（只能精确查询），不支持排序
  • B+树：一个节点可以存多个key，减少树深度；所有data都在叶子节点，范围查询和排序都高效

常见业务表在索引设计还算正常的前提下，树高一般不会太夸张，所以查找成本通常比很多人想得低。这里更该关注的是回表、扫描范围和是否命中合适索引。

叶子节点有序结构

B+ 树的叶子节点天然有序，所以范围查询和按索引字段排序时更容易顺着扫下去：

1
[10-20-30-40-50-60-70-80-90]  <- 叶子节点按大小排序

执行SELECT * FROM user WHERE age BETWEEN 20 AND 60时：

  1. 定位到20这个节点
  2. 沿着链表顺序扫到60
  3. 无需回到根节点再查其他范围

索引设计

联合索引顺序

同一张表的多个字段建联合索引时，顺序很关键：

1
2
3
4
5
-- 方案A：(status, create_time)
SELECT * FROM order WHERE status = 1 AND create_time > '2025-01-01'

-- 方案B：(create_time, status)  
SELECT * FROM order WHERE status = 1 AND create_time > '2025-01-01'

这个例子里更合理的是把等值条件放前面，把范围条件放后面：

  • status 是等值条件，通常适合作为前导条件之一
  • create_time 是范围条件，放后面
  • 优化器更容易把扫描范围收住

选择标准：

  1. 优先考虑等值匹配和选择性：不要死背模板，要结合真实 where 条件看谁更适合做前导列
  2. 等值条件优先于范围条件
  3. 排序字段优先（如果要求排序）

前缀索引

对很长的字符串字段，建前缀索引而不是全字段索引：

1
2
3
4
5
-- 不推荐：存储entire email
CREATE INDEX idx_email ON user(email);

-- 推荐：只存前10个字符
CREATE INDEX idx_email ON user(email(10));

前缀索引能省空间，但排序、分组和覆盖能力都会受影响，所以别只看索引建出来了没有。

覆盖索引

覆盖索引的价值不在概念，而在于能少一次回表：

1
2
3
4
5
6
7
-- 需要回表
CREATE INDEX idx_status ON order(status);
SELECT id, status, amount FROM order WHERE status = 1;

-- 覆盖索引，无需回表
CREATE INDEX idx_status_amount ON order(status, amount);
SELECT id, status, amount FROM order WHERE status = 1;

EXPLAIN 执行计划

重点看哪些字段

1
2
3
4
5
6
7
EXPLAIN SELECT * FROM order WHERE user_id = 123 AND status = 1;

+----+-------------+-------+------+----------------------+-----+
| id | select_type | table | type | key                  | rows|
+----+-------------+-------+------+----------------------+-----+
|  1 | SIMPLE      | order | ref  | idx_user_status      | 50  |
+----+-------------+-------+------+----------------------+-----+

type字段最关键（从快到慢）：

  • const：查找主键或唯一索引，返回最多1行
  • ref：普通索引，返回多行
  • range：索引范围扫描（BETWEEN、<、>）
  • index：全索引扫描（比全表扫描少）
  • ALL：全表扫描，最慢

type 不是越靠前越神，重点是别掉到 ALL，以及它是不是符合你对这条 SQL 的预期。

key字段：显示用了哪个索引。NULL表示没用索引。

rows字段：表示优化器估算要扫多少行。这个数字不要求绝对准，但如果明显偏大，通常说明索引或统计信息有问题。

常见索引失效场景

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 1. 前导字段没用条件
CREATE INDEX idx ON user(status, create_time);
SELECT * FROM user WHERE create_time > '2025-01-01';  -- 不走索引

-- 2. 使用函数或表达式
SELECT * FROM order WHERE YEAR(create_time) = 2025;  -- 不走索引
SELECT * FROM order WHERE amount * 2 > 1000;  -- 不走索引

-- 3. 类型不匹配发生隐式转换
CREATE INDEX idx ON user(phone);
SELECT * FROM user WHERE phone = 13812345678;  -- phone是varchar，这里是数字，转换后不走索引

-- 4. LIKE以通配符开头
SELECT * FROM user WHERE name LIKE '%张三';  -- 不走索引
SELECT * FROM user WHERE name LIKE '张三%';  -- 走索引

-- 5. OR条件中有一个字段没索引
SELECT * FROM user WHERE user_id = 1 OR name = '张三';  -- 如果name没索引，整个条件不走索引

性能排查

慢查询日志

慢查询日志最适合做第一轮抓手，先把慢 SQL 捞出来，再决定是改索引还是改查询模型：

1
2
3
4
5
6
-- 设置慢查询阈值为1秒
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 慢查询日志地址
SHOW VARIABLES LIKE 'slow_query_log_file';

不要一上来就猜。先看慢日志里到底是哪几条 SQL 稳定超时，再配合 EXPLAIN 看执行计划。

常见瓶颈

1. 多表JOIN太复杂

1
2
3
4
5
6
-- 不好：5个表JOIN，很难优化
SELECT * FROM order o
  JOIN user u ON o.user_id = u.id
  JOIN product p ON o.product_id = p.id
  JOIN category c ON p.category_id = c.id
  JOIN shop s ON p.shop_id = s.id;

如果关联数据量可控，有时拆成主查询加补充查询会更稳：

1
2
3
4
Order order = getOrder(id);  // 1次
User user = getUser(order.getUserId());  // 1次
Product product = getProduct(order.getProductId());  // 1次
// 再由应用层组装。是不是更快，要看数据量和访问模式，不是绝对结论。

2. 大表全字段查询

1
2
3
4
5
-- 不好
SELECT * FROM user WHERE age > 18;  -- 可能有100列数据

-- 好
SELECT id, name, age FROM user WHERE age > 18;  -- 只要3列

3. IN列表太大

1
2
3
4
5
6
-- 不好
SELECT * FROM user WHERE id IN (1, 2, 3, ..., 100000);

-- 好：用JOIN或EXISTS替代
SELECT u.* FROM user u
  JOIN (SELECT id FROM ids_list) ids ON u.id = ids.id;

索引维护

分析表统计信息

统计信息过期时，优化器选择的执行计划可能变差：

1
2
-- 更新表的统计信息
ANALYZE TABLE order;

重建索引

长期UPDATE和DELETE后，索引可能碎片化：

1
2
-- 重建索引
OPTIMIZE TABLE order;

这类动作放到线上前要先确认锁表影响，不要把一次维护操作做成新的故障源。

总结

问题 排查顺序
查询慢 1. 先抓慢日志 2. 再看 EXPLAIN 3. 最后决定改索引还是拆查询
插入慢 1. 先确认是不是索引太多 2. 再看批量写入方式和刷盘策略
磁盘占用多 1. 先看表和索引大小 2. 再决定归档、分区还是清理历史数据