MySQL B+ 树索引与优化

💡 一句话概述

InnoDB 用 B+ 树存索引：非叶子节点只存键不存数据，所以树又矮又胖（3 层约能撑 2000 万行，树高就是磁盘 I/O 次数）；叶子节点存数据并用双向链表串起来，范围查询顺着链表扫即可。围绕这棵树派生出聚簇索引/二级索引、回表、覆盖索引、最左前缀、索引下推等一整套面试高频知识点。

---

🔑 核心概念

1. B+ 树 — 多路平衡搜索树，非叶子节点只存键 + 页指针，叶子节点存数据且互相用双向链表连接；树高 ≈ 磁盘 I/O 次数，所以要"矮胖"。
2. 聚簇索引（主键索引） — 叶子节点存**整行数据**，"索引即数据"，一张 InnoDB 表只有一棵。
3. 二级索引（辅助索引） — 叶子节点只存**索引列的值 + 主键值**，按二级索引查非索引列时需要**回表**。
4. 覆盖索引 — 要查的列全部包含在二级索引里，不用回表，EXPLAIN 的 Extra 显示 Using index。
5. 最左前缀原则 — 联合索引 (a,b,c) 按 a→b→c 顺序排序，等价于建了 (a)、(a,b)、(a,b,c) 三个索引，查询必须从最左列开始连续命中。

---

📝 详解

为什么是 B+ 树，而不是 B 树 / 红黑树 / 哈希表 / 跳表

先说大白话：数据库的数据在磁盘上，而磁盘 I/O 比内存访问慢几个数量级。内存里读一个值大约百纳秒级，机械磁盘随机 I/O 是毫秒级，即使是 SSD 也要几十到几百微秒。所以索引设计的核心目标只有一个——用最少的磁盘 I/O 次数找到数据。

一次磁盘 I/O 读多少？InnoDB 以**页（page）为单位读，默认 innodb_page_size = 16KB。也就是说，不管你只取 1 行还是 100 行，一次 I/O 都是搬 16KB 进来。那么问题就转化为：**怎样让一次 16KB 的 I/O 承载尽可能多的"寻路信息"？

这就是各数据结构的高下之分：

结构	问题
红黑树/AVL 树	二叉，每个节点只有 2 个分叉。1000 万数据树高约 23 层（log₂10⁷ ≈ 23.2），最坏要 20 多次磁盘 I/O，而且每个节点很小，16KB 的页严重浪费
B 树	多叉，矮了很多；但**每个节点（包括非叶子节点）都存完整数据**，数据一大，单页能放的键就少，分叉数少 → 树又变高；且叶子节点之间没有链表，范围查询要不断"回到上层再下来"
哈希表	等值查询 O(1) 确实快（InnoDB 的自适应哈希索引 AHI 就是它），但**哈希值无序**：不支持范围查询（WHERE id > 100）、不支持排序、不支持最左前缀匹配
跳表	本质是"链表 + 多级稀疏索引"，范围查询友好、实现简单，这正是 **Redis ZSet 选它**的原因。但跳表是**内存场景**的最优解：Redis 数据全在内存，不怕树高；而跳表每层节点只指向少量后继，一次磁盘 I/O 读进来的 16KB 无法被充分利用，磁盘场景下不如 B+ 树
B+ 树	多叉 + 非叶子节点只存键不存数据 + 叶子节点双向链表，三个特性完美命中"减少磁盘 I/O + 支持范围查询"两个需求 ✅

B+ 树长什么样

                        ┌──────────────────────────────┐
        非叶子节点       │   [20]        [50]           │   ← 只存键 + 页指针
        （只存键）       └──┬──────────┬──────────┬─────┘     不存数据行！
                 ┌─────────┘          │          └─────────┐
                 ▼                    ▼                    ▼
          ┌────────────┐      ┌────────────┐      ┌────────────┐
          │ [5]  [12]  │      │ [25] [40]  │      │ [66] [80]  │
          └─┬───┬───┬──┘      └─┬───┬───┬──┘      └─┬───┬───┬──┘
            │   │   │           │   │   │           │   │   │
   ═════════▼═══▼═══▼═══════════▼═══▼═══▼═══════════▼═══▼═══▼═════════
   叶子节点 ┌──────┐ ⇄ ┌──────┐ ⇄ ┌──────┐ ⇄ ┌──────┐ ⇄ ┌──────┐ ⇄ ...
  （存数据） │ 1~4  │   │ 5~11 │   │12~19 │   │20~24 │   │25~39 │
            │ 数据 │   │ 数据 │   │ 数据 │   │ 数据 │   │ 数据 │
            └──────┘   └──────┘   └──────┘   └──────┘   └──────┘
   ══════════════════════════════════════════════════════════════════
              叶子层是一条按主键有序的双向链表

两个关键设计：

1. 非叶子节点只存键，不存数据 → 一个 16KB 的页能塞下极多的键和指针 → 分叉极多 → 树极矮。
2. 叶子节点用双向链表串起来，且按主键有序 → 范围查询（WHERE id BETWEEN 20 AND 40）先定位到 20 所在叶子页，然后**顺着链表往右扫**就行，不用回到根节点重新 descent；ORDER BY id 也天然免排序。

三层 B+ 树能存多少行？（估算）

这是面试经典题，推导过程要会说。以 InnoDB 默认页大小 16KB、主键 BIGINT（8 字节）、页指针 6 字节为例：

非叶子节点每个分叉占用 = 主键 8B + 页指针 6B = 14B
一个 16KB 的页能放的分叉数 ≈ 16 × 1024 / 14 ≈ 1170 个

三层树：
  第 1 层（根）      ：1 个页           → 1170 个分叉
  第 2 层            ：1170 个页        → 1170 × 1170 个分叉
  第 3 层（叶子层）  ：1170 × 1170 个页
  假设每行数据 1KB，一个叶子页放 16 行：

  总行数 ≈ 1170 × 1170 × 16 ≈ 2190 万行

结论：2000 万行以内的表，按主键查一行，最多 3 次磁盘 I/O。而且根节点页几乎永远在 Buffer Pool（内存）里，实际往往只有 1~2 次真实磁盘读。注意这些数字都是**估算**——每行 1KB、页利用率 100% 都是理想化假设，实际能存多少取决于行宽和页的填充率，但数量级是对的。这也解释了"单表 2000 万行"这个流传甚广的经验值：超过之后树可能变 4 层，I/O 多一次，性能出现台阶式下降。

聚簇索引 vs 二级索引：两棵树

InnoDB 一张表实际上有**多棵 B+ 树**：

	聚簇索引（主键索引）	二级索引（辅助索引）
叶子节点存什么	整行数据	索引列的值 + 主键值
数量	一张表只有一棵	建几个索引就有几棵
按什么排序	主键	索引列（相同时再按主键）
查到索引列之外的字段	直接就是整行，无需额外操作	需要**回表**：拿主键去聚簇索引再查一次

大白话：聚簇索引是"数据本身按主键组织成的树"，二级索引是"一本目录，目录页上写着'去主键 X 那里找'"。

为什么二级索引叶子不直接存行数据、只存主键？两个原因：① 省空间——每棵二级索引都复制一份整行的话，磁盘和 Buffer Pool 都撑不住；② 行数据更新（或行迁移）时，只需改聚簇索引一处，不用同步改所有二级索引。

没有主键怎么办？InnoDB 会选一个非空唯一索引当聚簇索引；都没有，就偷偷生成一个 6 字节的隐藏列 ROW_ID 作为聚簇键（用户不可见）。

回表：一次查询走两棵树

CREATE TABLE user (
    id      BIGINT       NOT NULL AUTO_INCREMENT,
    name    VARCHAR(64)  NOT NULL,
    phone   VARCHAR(20)  NOT NULL,
    age     INT          NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name (name)          -- 二级索引
) ENGINE = InnoDB;

-- 查询：按 name 查整行
SELECT * FROM user WHERE name = '张三';

执行过程分两步，走两棵树：

 二级索引树 idx_name                     聚簇索引树（主键）
┌─────────────────────┐              ┌─────────────────────┐
│      [李]   [王]     │              │      [100]  [500]   │
└───┬──────┬──────┬───┘              └───┬──────┬──────┬───┘
    ▼      ▼      ▼                      ▼      ▼      ▼
┌───────┐┌───────┐┌───────┐         ┌───────┐┌───────┐┌───────┐
│张 │赵 ││李 │…  ││王 │…  │         │id=1   ││id=100 ││id=500 │
│(100,  ││       ││       │         │整行数据││整行数据││整行数据│
│ 123)  ││       ││       │         └───────┘└───────┘└───────┘
└───────┘└───────┘└───────┘               ▲
   │                                      │
   │ ① 在 idx_name 树查到                 │ ② 回表：拿着主键 id=100
   │   ('张三', id=100)                   │   去聚簇索引树查整行
   └──────────────────────────────────────┘

这个"②"就是**回表**——多走一棵树，多几次 I/O。回表次数取决于命中行数：WHERE name = '张三' 命中 1 行就回表 1 次；WHERE name LIKE '张%' 命中 1000 行就要回表 1000 次，这时优化器甚至可能觉得"还不如直接全表扫"。

覆盖索引：不回表的捷径

如果查询的列**全部**包含在某个二级索引里，就不需要回表了——这叫**覆盖索引**（索引"覆盖"了查询所需的所有列）：

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

-- 只查 name 和 age，两列都在 idx_name_age 里
EXPLAIN SELECT name, age FROM user WHERE name = '张三';

+----+------+-------------+-------+------+---------------+
| id | type | key         | ref   | rows | Extra         |
+----+------+-------------+-------+------+---------------+
|  1 | ref  | idx_name_age| const |    1 | Using index   |  ← 覆盖索引！
+----+------+-------------+-------+------+---------------+

Extra = Using index 就是覆盖索引的标志：只扫二级索引这一棵树就拿到了全部结果，零回表。联合索引 (name, age) 的叶子节点存的是 (name, age, id)（索引列 + 主键），所以 SELECT id, name, age 也能被覆盖。

这也是"禁止 SELECT *"的最硬核理由：一旦 SELECT *，任何二级索引都无法覆盖整行，必然回表（详见常见陷阱四）。

最左前缀原则

联合索引 (a, b, c) 的排序规则是：先按 a 排，a 相同再按 b 排，b 也相同再按 c 排。就像字典按"部首→笔画"多级排序——只知道笔画数、不知道部首，字典帮不了你。

所以 (a,b,c) 相当于免费建了三个索引：

(a, b, c)  ≡  (a)  +  (a, b)  +  (a, b, c)

但它**不包含** (b)、(c)、(b,c) 的能力。哪些 WHERE 能用上索引，逐项判断：

WHERE 条件	能否用 idx(a,b,c)	用到几列	说明
a = 1	✅	a	走 (a)
a = 1 AND b = 2	✅	a,b	走 (a,b)
a = 1 AND b = 2 AND c = 3	✅	a,b,c	完整命中
b = 2	❌	—	缺最左列 a，b 在全树范围是无序的，用不上
c = 3	❌	—	同上
b = 2 AND c = 3	❌	—	缺 a，整段用不上
a = 1 AND c = 3	⚠️	只有 a	b 断了，c 无法在索引中定位；c=3 只能靠 ICP 在引擎层过滤（见下节）
a > 1 AND b = 2	⚠️	只有 a	范围之后全失效：a 是范围时，b 在 a 的每段内有序、跨段整体无序
a = 1 AND b > 2 AND c = 3	⚠️	a,b	同理，b 用了范围，c 用不上
a = 1 ORDER BY b	✅	a + b 排序	b 在 a=1 段内有序，免 filesort
a = 1 ORDER BY c	❌	—	c 在 (a=1) 段内无序，需要 filesort

"范围之后全失效"再解释一句：索引里数据按 (a,b,c) 排列，当 a > 1 时命中的是多个 a 值，每个 a 值内部 b 各自有序，但拼起来整体无序，所以 b 的条件无法用于索引定位。注意失效的是"继续用索引定位"，条件本身仍可通过 ICP 在索引层过滤（不是白写）。

联合索引怎么设计

1. 等值查询的列放前面，范围查询的列放后面。(status, create_time) 优于 (create_time, status)——WHERE status = 1 AND create_time > '2024-01-01' 时前者两列都能用于定位，后者 create_time 一用范围，status 就废了。
2. 区分度高的列放前面。区分度 = COUNT(DISTINCT col) / COUNT(*)，越接近 1 越好。name（几乎人人不同）放前面，gender（就俩值）放前面等于没放——扫一半数据。
3. 尽量覆盖高频查询。把高频 SELECT 的列纳入联合索引，做成覆盖索引，直接消灭回表。比如订单列表页高频查 (user_id, status, create_time) 且只展示金额，可以建 (user_id, status, create_time, amount)。
4. 顺序可以微调验证：用 EXPLAIN 对比 key_len（实际用到的索引字节数），越大说明用上的列越多。

索引下推 ICP（Index Condition Pushdown，MySQL 5.6+）

看这个查询，idx(a,b,c)，条件是 a = 1 AND c = 3：按最左前缀，c 断了，索引只能定位到 a = 1 的所有记录。**没有 ICP（5.6 之前）**的流程是：存储引擎把所有 a=1 的记录**逐条回表**取整行，交给 Server 层，Server 层再用 c = 3 过滤——假设 a=1 有 1000 行、其中 c=3 只有 10 行，就白白回表了 990 次。

ICP 的思路：c 的值其实**就在二级索引里**（联合索引叶子存了 a、b、c、主键），何必回表后再过滤？把 c = 3 这个条件**下推到存储引擎层**，在遍历索引时先筛掉不满足的行，只对通过的行回表：

无 ICP：  索引定位 a=1 ──→ 回表 1000 次 ──→ Server 层过滤 c=3 ──→ 剩 10 行
有 ICP：  索引定位 a=1 ──→ 引擎层先筛 c=3 ──→ 只回表 10 次 ✅

-- 确认 ICP 开着（默认开）
SHOW VARIABLES LIKE 'optimizer_switch';   -- index_condition_pushdown=on

EXPLAIN SELECT * FROM user WHERE name LIKE '张%' AND age = 20;
-- 假设有 idx_name_age(name, age)

Extra: Using index condition    ← ICP 生效的标志

注意 ICP 的适用范围：只用于二级索引上的 range/ref/eq_ref/ref_or_null 访问，且下推的条件必须是**索引里包含的列**。Extra 里 Using index condition（ICP）和 Using index（覆盖索引）是两码事，别混。

索引失效的常见场景

#	场景	反例 SQL	说明 / 改法
1	对索引列做函数或运算	WHERE YEAR(create_time) = 2024 WHERE id + 1 = 10	索引存的是原始值，不是函数结果。改成 WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'、WHERE id = 9。原则：索引列必须"干净"地出现在比较符一侧
2	隐式类型转换	列 phone VARCHAR(20)，却写 WHERE phone = 13800000000	字符串和数字比较时，MySQL 会把**字符串转成数字**，等价于 WHERE CAST(phone AS DOUBLE) = 138...——对索引列套了函数，失效。必须传 '13800000000'。反过来，数字列传字符串（WHERE age = '20'）不受影响
3	前导模糊 LIKE	WHERE name LIKE '%张' 或 '%张%'	B+ 树按前缀有序，% 开头无法定位起点。LIKE '张%' ✅ 可以走索引
4	OR 连接了非索引列	WHERE name = '张三' OR age = 20（age 无索引）	一半条件要全表扫，整体只能全表扫。若 name、age 都有索引，优化器可能用 index_merge 分别查再合并
5	NOT IN / != / NOT LIKE（部分场景）	WHERE status != 1	不是绝对失效：若排除后剩余行占比很小，仍可能走索引；占比大时优化器主动放弃（见 #7）
6	联合索引不满足最左前缀	索引 (a,b,c)，WHERE b = 2	见上节
7	优化器认为全表更快	小表；或 WHERE gender = '男' 命中 50% 的行	走索引 = 扫二级索引树 + 大量回表（随机 I/O），全表扫 = 顺序读聚簇索引。命中率过高或表很小时，全表扫反而快，优化器基于成本模型主动选 ALL。这不是 bug，是正确决策

还有一个字符集层面的坑：两表 JOIN 时，若关联列的**字符集或排序规则（collation）不一致**（如 utf8 vs utf8mb4），也会触发隐式转换导致其中一边索引失效。

EXPLAIN 怎么看

EXPLAIN 是索引优化的第一工具，重点盯四列：type、key、rows、Extra。

type：访问类型，性能从好到坏

system > const > eq_ref > ref > range > index > ALL

type	含义	典型场景
system	表只有一行（系统表）	基本见不到
const	主键/唯一索引等值查，最多一行	WHERE id = 1
eq_ref	JOIN 时被驱动表用主键/唯一索引等值匹配，每次只一行	JOIN ... ON a.id = b.uid（b.uid 唯一）
ref	普通（非唯一）索引等值查，可能多行	WHERE name = '张三'（name 有普通索引）
range	索引范围扫描	WHERE id > 100、BETWEEN、IN、LIKE 'abc%'
index	全索引扫描：把整棵二级索引树扫一遍（比 ALL 好在索引树更小，且可能覆盖）	覆盖索引但不带 WHERE 过滤条件
ALL	全表扫描：扫聚簇索引整棵树	索引失效或没有可用索引

经验底线：线上查询至少要做到 range，核心链路争取 ref 及以上；见到 index 和 ALL 就要警惕。

其他关键列

列	看什么
key	实际用了哪个索引；为 NULL = 没走索引
key_len	实际使用的索引字节数，判断联合索引用到了第几列（varchar 列 = 定义长度×字符集字节数 + 2 长度字节 + 1 NULL 标记字节）
rows	**估算**要扫描的行数，越小越好；注意是统计信息估算值，不精确
filtered	估算扫描行中满足 WHERE 条件的百分比

Extra：附加信息，面试最爱问

Extra	含义	好坏
Using index	覆盖索引，不回表	✅ 最好
Using index condition	ICP 索引下推，引擎层先过滤再回表	✅ 好
Using where	Server 层还要用 WHERE 再过滤（引擎返回的数据不完全满足条件）	⚠️ 中性，常见
Using filesort	无法利用索引顺序，需要额外排序（内存或磁盘临时文件）	❌ 尽量消灭：让 ORDER BY 列走索引
Using temporary	用了临时表（常见于 GROUP BY / DISTINCT / UNION）	❌ 尽量消灭：让 GROUP BY 列走索引

Using where 和 Using index 经常同时出现，不冲突：前者说"Server 层还过滤了一次"，后者说"数据全部来自索引没回表"。

自增主键 vs UUID

InnoDB 数据按主键顺序组织在聚簇索引里，所以**主键的取值顺序直接决定插入性能**：

自增主键（顺序插入）：新行永远追加在最后一页的末尾
┌────────┐ ┌────────┐ ┌────────┐
│ 1 ~ 100│→│101~200 │→│201~299 │→ [300 追加在这里，页写满就开新页]
└────────┘ └────────┘ └────────┘
✅ 顺序 I/O，页分裂极少，插入快

UUID（随机插入）：新行随机落在整棵树的任意页
┌────────┐ ┌────────┐ ┌────────┐
│ 1a3f.. │⇄│ 8c02.. │⇄│ f07b.. │  插入 "9e11.." → 可能插进第 1 页中间
└────────┘ └────────┘ └────────┘
❌ 页频繁写满 → 页分裂；随机 I/O；缓存命中率低

	自增 BIGINT	UUID
插入模式	顺序追加，几乎不分裂	随机插入，频繁**页分裂**
I/O 模式	顺序写，缓存友好	随机读写
空间	8 字节	字符串形式 36 字节（BINARY(16) 也要 16 字节）
连带伤害	—	所有二级索引的叶子都存主键值，主键越肥，每棵二级索引都跟着肥
适用	单机/单库写入 ✅	需要全局唯一且可预生成 ID 的场景，慎用做主键；折中方案：有序 UUID（如 UUIDv7）或雪花 ID（趋势递增的 64 位整数，兼具全局唯一与顺序性）

补充两个相关知识点：

• 页分裂：某页插入新行时空间不够，InnoDB 会申请新页，把原页部分记录挪过去，并修改上层节点指针——一次分裂涉及多个页的读写，代价高。除了随机主键，DELETE 留下大量碎片、INSERT 时无序键值都会诱发分裂。
• 页合并：与分裂相反，当相邻页因删除变得很空（低于阈值，约一半）时，InnoDB 会把两页记录合并成一页，回收空间。OPTIMIZE TABLE 可以主动重建表消除碎片。
• 顺带一提：聚簇索引叶子页之间在物理上是**双向链表**（页内有 Page Directory 加速页内二分查找），这也是范围扫描高效的物理基础。

---

💻 代码示例

建表与索引：

-- 建表：自增 BIGINT 主键
CREATE TABLE `order` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `user_id`     BIGINT UNSIGNED NOT NULL,
    `status`      TINYINT         NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已取消',
    `amount`      DECIMAL(10,2)   NOT NULL,
    `create_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    -- 联合索引：等值列 user_id/status 在前，范围列 create_time 在后
    KEY `idx_user_status_time` (`user_id`, `status`, `create_time`),
    -- 覆盖索引：把高频查询要展示的 amount 也放进去，列表页零回表
    KEY `idx_user_cover` (`user_id`, `status`, `create_time`, `amount`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 后续补建索引（MySQL 5.6+ 大多支持 Online DDL，不长时间锁表）
CREATE INDEX idx_create_time ON `order` (`create_time`);

-- 验证执行计划
EXPLAIN SELECT id, amount
FROM `order`
WHERE user_id = 1001 AND status = 1 AND create_time >= '2024-01-01';
-- 期望：type=range, key=idx_user_cover, Extra=Using where; Using index（覆盖，不回表）

Go 侧配合（database/sql）：

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "strings"

    _ "github.com/go-sql-driver/mysql"
)

type Order struct {
    ID     int64
    Amount float64
}

// QueryUserOrders 演示覆盖索引查询：只 SELECT 需要的列（id, amount 都在
// idx_user_cover 里），Extra 显示 Using index，零回表。
// 反面教材：SELECT * —— 任何二级索引都覆盖不了整行，必然回表。
func QueryUserOrders(ctx context.Context, db *sql.DB, userID int64) ([]Order, error) {
    const q = `SELECT id, amount FROM ` + "`order`" + `
               WHERE user_id = ? AND status = 1 AND create_time >= '2024-01-01'`
    rows, err := db.QueryContext(ctx, q, userID) // 永远用占位符，防注入
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var orders []Order
    for rows.Next() {
        var o Order
        if err := rows.Scan(&o.ID, &o.Amount); err != nil {
            return nil, err
        }
        orders = append(orders, o)
    }
    return orders, rows.Err()
}

// BatchInsert 演示自增主键的顺序插入：
// 不指定 id，由 AUTO_INCREMENT 分配 → 新行永远追加在聚簇索引最右页，
// 顺序 I/O、几乎不触发页分裂。同时用多值 INSERT 合并事务，减少刷盘次数。
// 若换成客户端生成的随机 UUID 做主键，这里会变成随机插入，页分裂频繁。
func BatchInsert(ctx context.Context, db *sql.DB, items [][2]interface{}) error {
    if len(items) == 0 {
        return nil
    }
    const cols = "(user_id, status, amount)"
    placeholders := make([]string, 0, len(items))
    args := make([]interface{}, 0, len(items)*3)
    for _, it := range items {
        placeholders = append(placeholders, "(?, 0, ?)")
        args = append(args, it[0], it[1])
    }
    q := "INSERT INTO `order` " + cols + " VALUES " + strings.Join(placeholders, ",")

    _, err := db.ExecContext(ctx, q, args...)
    if err != nil {
        return fmt.Errorf("batch insert: %w", err)
    }
    return nil
}

func main() {
    db, err := sql.Open("mysql", "user:pass@tcp(127.0.0.1:3306)/shop?parseTime=true")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    ctx := context.Background()
    if err := BatchInsert(ctx, db, [][2]interface{}{{1001, 99.9}, {1002, 45.5}}); err != nil {
        log.Fatal(err)
    }
    orders, err := QueryUserOrders(ctx, db, 1001)
    if err != nil {
        log.Fatal(err)
    }
    log.Printf("got %d orders", len(orders))
}

---

⚠️ 常见陷阱

陷阱一：给低区分度列建索引，基本没用

gender、status（只有两三个值）、is_deleted 这类列，等值一查就命中全表 30%~50% 的行。走索引 = 扫二级索引树 + 海量回表（随机 I/O），比全表顺序扫还慢，优化器多半直接放弃这个索引（type=ALL），索引白建，还占空间、拖慢写入。规避：建索引前先看区分度 COUNT(DISTINCT col)/COUNT(*)，太低就别单独建；确有过滤需求时把它作为联合索引的**非首列**（如 (user_id, status)），或依赖 ICP 过滤。

陷阱二：索引不是越多越好

每个索引都是一棵独立的 B+ 树：① 占磁盘和 Buffer Pool；② 每次 INSERT/UPDATE/DELETE 都要维护所有相关索引树，写放大明显（极端情况下更新一个字段要动好几棵树）；③ 索引太多会让优化器选路成本变高、甚至选错索引。规避：定期用 sys.schema_unused_indexes 找出从未被使用的索引删掉；能用联合索引/覆盖索引合并的就合并；单表索引数量控制在个位数。

陷阱三：函数包裹索引列、前导模糊，索引悄悄失效还不自知

WHERE YEAR(create_time) = 2024、WHERE LEFT(name, 2) = '张'、WHERE phone = 13800000000（varchar 列传数字触发隐式转换）、LIKE '%张'——这些语句**都能正常返回结果**，只是慢，功能测试根本发现不了，上了量才炸。规避：核心 SQL 上线前必须过一遍 EXPLAIN，确认 type 不是 ALL、key 不为 NULL；范围条件改写成 >= / < 的裸列比较；应用层参数类型与列类型严格对齐（Go 里 phone 就用 string 传）。

陷阱四：SELECT * 让覆盖索引失效，必然回表

辛苦设计的覆盖索引 (user_id, status, create_time, amount)，一句 SELECT * 就全废了——* 包含所有列，二级索引里没有的列只能回表取，Extra 从 Using index 变成回表 + Using where，QPS 高时 I/O 直接翻倍。规避：查询永远显式列出需要的字段；代码 review 时把 SELECT * 当红线；ORM（如 GORM）默认生成 SELECT *，高频查询要用 Select("id", "amount") 显式指定。

---

🏋️ 练习题

练习 1：为什么 InnoDB 选 B+ 树而不是 B 树？红黑树和哈希表又输在哪？

提示：从"磁盘 I/O 次数 = 树高"和"范围查询"两个角度回答。

答案

① 对比 B 树：B+ 树非叶子节点只存键和页指针、不存数据，一个 16KB 页能放约 1170 个分叉（主键 8B + 指针 6B），树极矮——3 层约撑 2000 万行（估算），查一行最多 3 次 I/O；B 树非叶子节点也存数据，单页分叉少，同样数据量树更高。另外 B+ 树叶子节点用双向链表按序串起来，范围查询定位到起点后顺链表扫即可；B 树叶子无链表，范围查询要反复回到上层。 ② 红黑树：二叉树，1000 万数据高约 23 层，意味着最坏 20 多次磁盘 I/O，且小节点无法填满 16KB 的页，完全不适合磁盘。 ③ 哈希表：等值查 O(1)，但哈希值无序——不支持范围查询、排序、最左前缀。InnoDB 内部有自适应哈希索引（AHI）作为热点页的加速缓存，但不能替代 B+ 树。

练习 2：表上有联合索引 idx(a,b,c)，下面每条 SQL 能用到索引的哪些列？

-- ① WHERE a = 1 AND c = 3
-- ② WHERE a > 1 AND b = 2
-- ③ WHERE b = 2 AND c = 3
-- ④ WHERE a = 1 AND b = 2 ORDER BY c

答案

① 只用到 a 定位：b 断了，c 无法走索引树定位；但 MySQL 5.6+ 若开启 ICP，c = 3 会下推到存储引擎层，在遍历索引时先过滤再回表（Extra: Using index condition），减少回表次数。 ② 只用到 a：a 是范围条件，"范围之后全失效"，b=2 不能用于索引定位（同样可能靠 ICP 过滤）。 ③ 完全用不上：缺最左列 a，b、c 在全索引范围内无序，只能全表扫或全索引扫。 ④ a、b 用于定位，c 用于免排序：在 (a=1, b=2) 段内 c 天然有序，ORDER BY c 直接顺索引读，Extra 无 filesort——这是最理想的一条。

练习 3：为什么不建议用 UUID 做 InnoDB 主键？如果业务上必须全局唯一 ID 怎么办？

答案

三个原因：① 页分裂：聚簇索引按主键顺序组织，UUID 随机，新行随机插入导致页频繁写满、分裂（挪数据 + 改上层指针 + 随机 I/O），而自增主键永远追加在最右页，几乎不分裂；② 空间放大：UUID 字符串 36 字节 vs BIGINT 8 字节，且**每棵二级索引的叶子都要存一份主键值**，索引越多放大越狠，还挤占 Buffer Pool；③ 随机插入让页缓存/Buffer Pool 命中率下降。 替代方案：雪花算法（Snowflake）ID——64 位、趋势递增、全局唯一，兼具自增的顺序性和分布式唯一性，是主流选择；或用有序的 UUIDv7（前缀是时间戳）；实在要用 UUID，可存 BINARY(16) 省一半空间，但页分裂问题仍在。

---

🔗 相关链接

• MySQL 官方文档：InnoDB Index Types — 聚簇索引与二级索引的官方定义
• MySQL 官方文档：Optimizing Queries with EXPLAIN — EXPLAIN 各列与 type 等级的权威说明
• MySQL 官方文档：Index Condition Pushdown — ICP 的适用条件与开关
• Redis 为什么用跳表实现 ZSet — 对比理解内存场景与磁盘场景的结构选型
• High Performance MySQL（高性能 MySQL） — 索引与查询优化的系统性读物，Schema 与索引设计章节值得精读