MySQL 索引底层原理：B+树、覆盖索引与最左前缀

2026年8月20日 数据库 suzhe

MySQL 索引几乎是所有后端面试和线上性能优化的第一考点，但绝大多数人只停留在「加个索引就快了」这一层。当查询突然走了全表扫描、当联合索引怎么建都不生效、当同样的 SQL 在测试库飞快而在生产库拖了三秒——这些问题的答案都藏在 B+树的结构里。这篇文章从存储引擎的页结构讲起，把 B+树、聚簇索引、回表、覆盖索引、最左前缀原则串成一条完整链路，并给出可直接照着跑的 EXPLAIN 排查流程。全文基于 MySQL 8.0 + InnoDB。

一、为什么 InnoDB 选择 B+树，而不是哈希或红黑树

索引的本质是用额外的存储空间换取查询时的比较次数。但磁盘（哪怕是 SSD）的随机 IO 代价远高于内存中的 CPU 比较，因此数据库索引的首要设计目标不是「减少比较次数」，而是减少磁盘 IO 次数。InnoDB 以 16KB 的页（Page）为最小 IO 单位，一次读盘就取回一整页，索引结构必须围绕这个约束设计。

1.1 三种候选结构的取舍

数据结构等值查询范围查询磁盘 IO 特性结论
哈希表O(1) 最快不支持键值散列，无序只适合内存表 / Memory 引擎
红黑树 / AVLO(log₂N)支持但低效树高极大，每层一次 IOIO 次数爆炸，不可用
B 树O(log_mN)支持非叶子节点也存数据，扇出小扇出不足，树更高
B+树O(log_mN)叶子链表，极优非叶子只存键+指针，扇出极大InnoDB 的选择
四种索引结构在磁盘场景下的对比

红黑树的问题在于它是二叉树：100 万行数据树高约 20 层，最坏情况需要 20 次磁盘 IO。而 B+树是多叉树，一个 16KB 页里能塞进上千个索引键，树高被压到 3 层左右。

1.2 三层 B+树到底能存多少行

假设主键为 BIGINT（8 字节）、InnoDB 页号指针为 6 字节，可以手算：

非叶子节点单条记录 = 主键 8B + 页指针 6B = 14B
单页可存指针数   = 16KB / 14B ≈ 16384 / 14 ≈ 1170

叶子节点存完整行，假设单行 1KB：
单页可存行数     = 16KB / 1KB = 16 行

树高 2 层（1 个根 + 叶子）: 1170 × 16      ≈ 1.8 万行
树高 3 层（根 + 中间 + 叶）: 1170 × 1170 × 16 ≈ 2189 万行
树高 4 层                : 1170³ × 16     ≈ 256 亿行

结论：三层 B+树即可支撑约两千万行。而根节点常驻 Buffer Pool，因此一次主键查询实际只需 1～2 次磁盘 IO。这也解释了为什么「单表两千万行」会成为一条经验性的分库分表红线——超过后树高变 4 层，每次查询多一次 IO。

1.3 B+树的两个关键结构特征

  • 只有叶子节点存数据：非叶子节点只存键和指针，扇出（fanout）最大化，树高最低。
  • 叶子节点构成双向链表：范围查询（BETWEEN、>、ORDER BY）定位到起点后顺序遍历链表即可，无需回到根节点重新查找，这是 B+树相对 B 树最大的优势。

二、聚簇索引与二级索引：InnoDB 的双层结构

很多人以为「索引是数据之外的一份附加结构」，这在 MyISAM 成立，在 InnoDB 并不成立。InnoDB 是索引组织表（Index Organized Table）：表数据本身就存放在主键索引的叶子节点里。

2.1 两类索引的叶子节点存了什么

索引类型别称叶子节点内容一张表的数量
聚簇索引主键索引 / Clustered Index完整行数据有且仅有 1 个
二级索引辅助索引 / Secondary Index索引列值 + 主键值可以多个
InnoDB 聚簇索引与二级索引的差异

如果建表时没有显式指定主键，InnoDB 会按以下顺序兜底：① 选择第一个所有列都 NOT NULL 的唯一索引；② 都没有则隐式生成一个 6 字节的 ROW_ID 作为聚簇索引键。务必显式定义主键——隐式 ROW_ID 是全库共享的自增计数器，高并发写入时会成为竞争点。

2.2 回表：二级索引的隐藏成本

由于二级索引叶子只存主键值，当查询需要索引列之外的字段时，必须拿主键再去聚簇索引查一遍完整行，这个动作叫回表（Bookmark Lookup）。

-- 假设 idx_email 是 email 列的二级索引
SELECT id, email      FROM users WHERE email = 'a@x.com';  -- 不回表：email、id 都在索引里
SELECT id, email, name FROM users WHERE email = 'a@x.com';  -- 需回表：name 不在索引中

-- 回表的实际路径：
-- 1) 在 idx_email 的 B+树中定位到 email='a@x.com' 的叶子记录，取出 id = 8823
-- 2) 拿 id=8823 再走一遍聚簇索引的 B+树，取出整行
-- => 两棵树各走一次，IO 次数翻倍

单条查询回表一次感知不明显，但当命中 5000 行时就是 5000 次随机 IO。这也是为什么优化器有时「宁愿全表扫描也不用索引」——当预估回表行数超过全表的约 20%～30%，顺序扫描反而更快。

2.3 主键选型：自增 BIGINT vs UUID

聚簇索引决定了物理存储顺序，因此主键是否单调递增直接影响写入性能：

  • 自增主键：新记录总是追加到最右侧叶子页，页填充率高，几乎不发生页分裂。
  • 随机 UUID：插入位置随机分布，频繁触发页分裂（Page Split），页填充率可能低至 50%，索引体积膨胀；且所有二级索引叶子都要冗余存储这个 36 字节的字符串。

如果业务确实需要 UUID（如多端离线生成、避免 ID 枚举），MySQL 8.0 提供了有序化方案：

-- 把 UUID v1 的时间低位与高位互换，使其近似单调递增，并压缩为 16 字节二进制
CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY,          -- 16B 而非 CHAR(36) 的 36B
  order_no VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

INSERT INTO orders (id, order_no, created_at)
VALUES (UUID_TO_BIN(UUID(), 1), 'NO202608200001', NOW());
--                          ^ swap_flag=1 是关键，开启时间位重排

-- 读出时还原为可读字符串
SELECT BIN_TO_UUID(id, 1) AS uuid, order_no FROM orders;

三、覆盖索引：用空间消灭回表

覆盖索引（Covering Index）不是一种索引类型，而是一种查询状态：当查询所需的全部列都能从索引里直接取到，无需回表，就称该索引「覆盖」了这个查询。这是索引优化中性价比最高的手段。

3.1 用 EXPLAIN 识别覆盖索引

CREATE TABLE user_login_log (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  login_at   DATETIME        NOT NULL,
  ip         VARCHAR(45)     NOT NULL,
  device     VARCHAR(64)     NOT NULL,
  KEY idx_user_time (user_id, login_at)
) ENGINE=InnoDB;

-- 查询 A：需要 ip，不在索引中 -> 回表
EXPLAIN SELECT ip FROM user_login_log
WHERE user_id = 1001 AND login_at > '2026-08-01';
-- Extra: Using where           （无 Using index，说明回表了）

-- 查询 B：只取索引内的列 -> 覆盖索引
EXPLAIN SELECT user_id, login_at FROM user_login_log
WHERE user_id = 1001 AND login_at > '2026-08-01';
-- Extra: Using index           ★ 覆盖索引命中

-- 查询 C：把 ip 加进索引，让 A 也变成覆盖
ALTER TABLE user_login_log ADD KEY idx_user_time_ip (user_id, login_at, ip);
-- 再执行查询 A，Extra 变为: Using index

记住这条判据：Extra 出现 Using index 表示覆盖索引命中（不回表）；出现 Using index condition 表示索引下推；而 Using filesort 或 Using temporary 才是真正需要警惕的信号。

3.2 覆盖索引优化深分页

LIMIT 1000000, 20 这类深分页之所以慢，是因为它先回表取了 100 万行再丢弃。用覆盖索引先拿主键、再 JOIN 回原表，可以把回表次数从 100 万降到 20：

-- 慢：回表 1000020 次
SELECT * FROM user_login_log
WHERE user_id = 1001 ORDER BY login_at LIMIT 1000000, 20;

-- 快：子查询走覆盖索引只扫索引，仅对最终 20 行回表
SELECT t.* FROM user_login_log t
INNER JOIN (
    SELECT id FROM user_login_log
    WHERE user_id = 1001
    ORDER BY login_at
    LIMIT 1000000, 20
) AS pk ON t.id = pk.id;

-- 更优（若前端可传上一页游标）：书签式分页，直接跳过偏移量
SELECT * FROM user_login_log
WHERE user_id = 1001 AND login_at > '2026-08-20 10:30:00'
ORDER BY login_at LIMIT 20;

覆盖索引的代价是体积增大、写入维护成本上升，因此只覆盖高频且明确的查询。

四、最左前缀原则：联合索引到底怎么建

联合索引 (a, b, c) 在 B+树里是按 a、再 b、再 c 的顺序依次排序的。只有当左侧列已经确定为具体值时，右侧列才是有序的——这就是最左前缀原则的全部由来。

4.1 一个联合索引等于几个索引

索引 (a, b, c) 可以支撑以下前缀组合的查询：(a)、(a, b)、(a, b, c)，但不能单独支撑 (b)、(c) 或 (b, c)。

4.2 索引失效场景速查表

场景示例（索引 idx(a,b,c)）是否走索引原因与对策
跳过最左列WHERE b = 2❌ 通常不走8.0.13+ 在 a 基数极低时可能用 Skip Scan
中间列断裂WHERE a = 1 AND c = 3⚠️ 仅用到 ac 可通过 ICP 过滤，但无法定位；补索引 (a, c)
范围后接等值WHERE a > 1 AND b = 2⚠️ 仅用到 a把范围列放最右：改索引为 (b, a)
列上使用函数WHERE DATE(a) = '2026-08-20'❌ 失效改写为区间；或建函数索引 ((DATE(a)))
隐式类型转换WHERE a = 123（a 为 varchar）❌ 失效参数加引号 a = '123'
前导通配 LIKEWHERE a LIKE '%abc'❌ 失效换全文索引 / 反转列 / ES
后置通配 LIKEWHERE a LIKE 'abc%'✅ 走范围扫描正常可用
OR 连非索引列WHERE a = 1 OR d = 2❌ 全表扫给 d 加索引后可走 index_merge
否定条件WHERE a != 1⚠️ 看选择性命中比例高时优化器直接全表扫
排序方向不一致ORDER BY a ASC, b DESC⚠️ 需 filesort8.0 支持降序索引 (a ASC, b DESC)
MySQL 8.0 常见索引失效场景与对策

4.3 列顺序的排列法则

建联合索引时，列顺序按以下优先级排列：

  1. 等值查询列在前，范围查询列在后——范围列之后的列会失去定位能力。
  2. 高选择性（区分度高）的列靠前——用 SELECT COUNT(DISTINCT col)/COUNT(*) 评估，越接近 1 越好。
  3. 排序与分组列紧跟等值列——可以顺带消除 Using filesort。
  4. 最后追加需要覆盖的列——让高频查询免于回表。

4.4 索引下推（ICP）：被误判为「失效」的优化

MySQL 5.6 引入索引条件下推（Index Condition Pushdown）后，那些「无法用于定位」的列并非完全无用——它们可以在存储引擎层就参与过滤，从而减少回表次数：

-- 索引 idx(a, b, c)，查询跳过了 b
EXPLAIN SELECT * FROM t WHERE a = 1 AND c = 3;
-- Extra: Using index condition
--   含义：a=1 用于 B+树定位；c=3 被下推到 InnoDB 层，
--         在读取索引记录时就地过滤，不满足的记录直接不回表。

-- 确认 ICP 是否开启（默认 on）
SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%';

-- 对照实验：临时关闭 ICP，观察 Extra 退化为 Using where
SET optimizer_switch = 'index_condition_pushdown=off';

五、实战：一条慢查询的完整优化过程

下面用一个订单统计场景走完「发现—诊断—优化—验证」四步。表结构与数据量：约 800 万行订单。

5.1 发现慢查询

-- 开启慢查询日志，阈值 1 秒，并记录未走索引的语句
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 也可直接从 performance_schema 找 TOP 慢语句（无需重启）
SELECT DIGEST_TEXT,
       COUNT_STAR                          AS calls,
       ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms,
       ROUND(SUM_ROWS_EXAMINED/COUNT_STAR) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'shop'
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 5;

5.2 诊断执行计划

-- 问题 SQL：查某商户近 30 天已完成订单的金额汇总
EXPLAIN ANALYZE
SELECT status, SUM(amount) AS total
FROM orders
WHERE merchant_id = 88
  AND created_at >= '2026-07-21'
  AND status = 'PAID'
GROUP BY status;

/* 输出关键行（原始状态，仅有 PRIMARY 主键）：
   type: ALL              <- 全表扫描
   key : NULL             <- 未命中任何索引
   rows: 7983512          <- 预估扫描近 800 万行
   Extra: Using where; Using temporary
   实际耗时: 4.12 s
*/

5.3 设计并建立索引

按 4.3 的法则拆解这条 SQL：merchant_id 与 status 是等值条件、created_at 是范围条件、amount 是需要覆盖的输出列。因此顺序为「等值 → 等值 → 范围 → 覆盖」：

-- 先评估选择性，确认 merchant_id 值得放最左
SELECT COUNT(DISTINCT merchant_id) / COUNT(*) AS sel_merchant,
       COUNT(DISTINCT status)      / COUNT(*) AS sel_status
FROM orders;
-- sel_merchant = 0.0004 ；sel_status = 0.0000005（仅 4 种状态，基数极低）

-- 建立联合覆盖索引：等值列在前，范围列居后，输出列收尾
ALTER TABLE orders
  ADD KEY idx_mch_status_time_amt (merchant_id, status, created_at, amount);

-- 8.0 在线 DDL，大表建议显式指定，避免锁表
ALTER TABLE orders
  ADD KEY idx_mch_status_time_amt (merchant_id, status, created_at, amount),
  ALGORITHM=INPLACE, LOCK=NONE;

5.4 验证优化效果

EXPLAIN ANALYZE
SELECT status, SUM(amount) AS total
FROM orders
WHERE merchant_id = 88 AND status = 'PAID'
  AND created_at >= '2026-07-21'
GROUP BY status;

/* 优化后：
   type : range
   key  : idx_mch_status_time_amt
   rows : 2143                       <- 从 798 万降到 2143
   Extra: Using where; Using index   <- ★ 覆盖索引，完全不回表
   实际耗时: 0.006 s                  <- 4.12s -> 6ms，约 680 倍
*/

-- 强制对照：不用索引跑一遍，量化收益
SELECT status, SUM(amount) FROM orders IGNORE INDEX (idx_mch_status_time_amt)
WHERE merchant_id = 88 AND status = 'PAID' AND created_at >= '2026-07-21'
GROUP BY status;

这套流程与 PostgreSQL 的调优思路高度相通，只是执行计划的读法不同——如果你的技术栈同时涉及 PG，可以对照阅读这篇 PostgreSQL 慢查询优化：执行计划解读与索引调优实战，其中 EXPLAIN (ANALYZE, BUFFERS) 的解读方式与本文的 EXPLAIN ANALYZE 可以互为参照；两种数据库在选型层面的差异也可参考 PostgreSQL 与 SQL Server 的对比分析。

六、索引设计检查清单

上线前过一遍这份清单：

  • ✅ 每张表都有显式自增主键，避免隐式 ROW_ID 与随机 UUID 直接作聚簇键。
  • ✅ 联合索引列顺序遵循「等值 → 排序 → 范围 → 覆盖」。
  • ✅ 高频查询用 EXPLAIN 确认出现 Using index，杜绝 type: ALL。
  • ✅ 已被其他索引前缀包含的冗余索引及时删除（如已有 (a,b) 就不需要单列 (a)）。
  • ✅ 单表索引数量控制在 5 个以内，每个索引都会拖慢 INSERT/UPDATE 并占用 Buffer Pool。
  • ✅ 长字符串列用前缀索引 KEY(col(20))，但注意前缀索引无法用于覆盖与排序。
  • ✅ 参数类型与列类型严格一致，避免隐式转换导致索引失效。
  • ✅ 大表加索引用 ALGORITHM=INPLACE, LOCK=NONE，并避开业务高峰。

查询已经优化到极限之后，下一层杠杆就是缓存层——把索引仍然扛不住的热点查询挡在数据库之前，具体策略可参考 Redis 缓存设计：穿透、击穿、雪崩与最佳实践。

七、小结

MySQL 索引优化不是靠背口诀，而是理解三件事：B+树的扇出决定了 IO 次数、聚簇索引的结构决定了回表成本、列的排序顺序决定了最左前缀能走多远。把这三点想清楚，再配合 EXPLAIN ANALYZE 做量化验证，绝大多数慢查询都能在十分钟内定位并解决。

建议在自己的库上做一次索引体检：用 performance_schema 找出 TOP 10 慢语句逐条跑 EXPLAIN，消灭所有 type: ALL 与 Using filesort。

版权声明：
作者：suzhe
链接：https://fsdata.site/2026/08/mysql-index-btree-optimization/
文章版权归作者所有，未经允许请勿转载。
上一篇 大模型 RAG 进阶：混合检索与重排序优化实战
下一篇 Spring Boot 3 升级踩坑实录：Jakarta、Security 6 与 7 大雷区
最新评论
暂无评论