存储基础：数据模型、访问路径与 InnoDB 索引实现¶
0. 这一章要真正掌握什么¶
数据库基础不能停在“索引用 B+ 树”“加索引能加快查询”这种结论。真正需要理解的是更具体的执行过程：一张 InnoDB 表的数据究竟放在哪里；主键索引和二级索引的叶子节点分别存什么；按二级索引查 SELECT * 为什么会回表；覆盖索引为什么能少一次树查找；全表扫描在 InnoDB 中实际扫的是什么；随机主键为什么更容易引发页分裂和碎片；为什么主键太宽会让所有二级索引一起变大；有索引时优化器为什么仍可能选择扫描。
本章以 MySQL 8.4 / InnoDB 为主要实现对象，把“关系型数据模型 - 页面 - B+ 树 - 聚簇索引 - 二级索引 - 查询路径 - 写入代价 - Buffer Pool”连成一条执行链。事务隔离、MVCC、行锁、间隙锁会在数据库后续专题单独展开，但这里会讲清理解索引读写路径所需的 InnoDB 物理实现。
学完后，应当能对一条 SQL 从逻辑条件一直解释到 InnoDB 页级访问，而不是只背术语。
1. 先决定数据由谁负责：数据库、文件、对象存储¶
假设媒体服务生成一段视频，得到以下数据：
id 资产 ID
owner_id 所属用户
status PROCESSING / READY / FAILED
object_key 对象存储中的 key
size_bytes 文件大小
created_at 创建时间
video_bytes 180 MB 视频本体
如果把所有内容都追加进一个 txt 文件，最开始当然能工作。真正的问题是后续需要做的操作：按 id 查一条记录，按 owner_id 查用户资产，保证 object_key 不重复，多个服务实例并发写入，更新状态，删除记录，恢复异常中断后的数据。普通文件只提供路径、字节和偏移；这些查询结构、约束、并发修改、恢复能力都要由应用自己实现。
关系型数据库适合保存结构化业务状态，例如 id、owner_id、status、object_key、时间和大小。对象存储适合保存视频、图片、模型权重、导出包等大对象。对象存储中的 key 是对象命名空间里的标识符，用于定位对象；它不是“视频内容”，也不必等同于本地文件名。
典型拆分如下：
MySQL / InnoDB
id = 91231
owner_id = 42
status = READY
object_key = videos/42/91231.mp4
size_bytes = 188743680
对象存储
key = videos/42/91231.mp4
value = 180 MB 视频字节
数据库负责回答“这条资产是谁的、状态是什么、对象在哪里”；对象存储负责在给定 key 后返回大对象。业务接口通常先查询数据库元数据，再按 object_key 获取对象或生成受控访问地址。
大对象不是绝对不能存在数据库 BLOB 中，但把大量百 MB 级视频和高频查询元数据塞进同一普通业务表，会放大数据库页缓存、备份、复制、扫描和运维成本。应该从访问模式和系统成本解释，而不是说“数据库不能存二进制”。
2. 一张表先解决“什么是一条记录”和“什么状态不允许出现”¶
可以把媒体资产表定义成：
CREATE TABLE media_asset (
id BIGINT NOT NULL,
owner_id BIGINT NOT NULL,
object_key VARCHAR(512) NOT NULL,
status VARCHAR(32) NOT NULL,
size_bytes BIGINT NOT NULL,
created_at DATETIME(6) NOT NULL,
updated_at DATETIME(6) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_object_key (object_key),
KEY idx_owner_id (owner_id),
KEY idx_status (status)
) ENGINE=InnoDB;
这里有两类不同问题。
第一类是正确性。PRIMARY KEY(id) 规定每一行都必须有一个唯一、非空的正式身份；UNIQUE(object_key) 规定两个记录不能出现相同的对象 key；NOT NULL 规定字段不能处于“缺失”状态。这些是数据约束。
第二类是访问效率。idx_owner_id 和 idx_status 给查询增加新的访问路径。这些是索引。
唯一约束通常依靠唯一索引高效执行，但“约束”和“索引”不是一个概念：约束描述允许什么数据存在，索引描述怎样更快找到数据。
2.1 主键与 UNIQUE 的差别要回答到 MySQL 语义¶
MySQL 中一张表只能有一个 PRIMARY KEY；主键列必须 NOT NULL。UNIQUE 索引可以有多个。如果 UNIQUE 列允许 NULL，MySQL 允许出现多个 NULL，因为 NULL 不按普通相等值处理。
所以：
表达“id 是这一行的正式身份”。
表达“object_key 不是主身份，但业务要求非重复”。
“因为值唯一所以适合建索引”不是主因。更准确的因果是：数据库要高效判断“将要插入的键是否已经存在”，以及高效按这个候选键定位记录，因此主键/唯一约束通常由索引支撑。如果没有可检索结构，每次唯一性检查都可能退化成大范围扫描。
3. MySQL 与 InnoDB 分别在做什么¶
执行一条 SQL 时，可以先形成一个粗粒度分层：
例如：
优化器负责比较可用执行计划，例如扫描聚簇索引还是使用 idx_owner_id。真正按索引查找页、把页读入内存、在页中查记录，则由 InnoDB 执行。
因此，“SQL 有没有索引”与“这次查询有没有使用索引”是两件事。前者是表结构，后者是优化器对这次 SQL 的计划选择。
4. InnoDB 不是“表文件 + 独立索引文件”：表本身组织在聚簇索引里¶
理解 InnoDB 最关键的一句话是：每张 InnoDB 表都有一个聚簇索引，聚簇索引的叶子记录保存行数据。
如果显式定义 PRIMARY KEY，InnoDB 使用主键作为聚簇索引。如果没有主键，它会选择第一个所有列都 NOT NULL 的 UNIQUE 索引作为聚簇索引；如果仍没有，就创建隐藏的 GEN_CLUST_INDEX，使用一个单调递增的 6 字节 row ID。
所以在常见设计中：
不只是“有一个主键索引”。更具体地说，整张表的数据行都组织在以 id 为键的聚簇 B+ 树叶子页中。
可以把它抽象成：
[ root ]
/ \
[ internal ] [ internal ]
/ \ / \
[leaf] [leaf] [leaf] [leaf]
聚簇索引 leaf record:
(id, owner_id, object_key, status, size_bytes, created_at, updated_at, ...)
因此在 InnoDB 中，所谓“全表扫描”通常就是顺序扫描聚簇索引的叶子记录，而不是去扫描一个完全独立于主键索引的 heap 文件。
4.1 为什么主键设计会影响整张表¶
因为聚簇索引的键就是主键。主键过宽会直接扩大聚簇索引记录和内部节点键值；更重要的是，InnoDB 的每个二级索引记录都要保存对应行的主键值。所以一个 64 字节主键，不只让主键索引变大，也会让 idx_owner_id、idx_status 等所有二级索引一起变大。
这就是这里“为什么 InnoDB 主键通常建议短、稳定”的真正实现原因之一。
5. InnoDB 索引按“页”组织，B+ 树优化的是页访问次数¶
InnoDB 普通索引使用 B-tree 家族结构，工程教材通常按 B+ 树来解释其布局。MySQL 8.4 官方文档说明索引记录存储在 B-tree 的叶子页中，默认索引页大小为 16 KB；innodb_page_size 在实例初始化时决定页大小。
数据库不会为了查一个 key 每次只从磁盘读几十字节。页是 InnoDB 读写和缓存的重要单位。一个页中可以保存很多条索引记录，因此一个内部节点可以拥有很高的分支数，B+ 树会比较“矮”。
如果一个内部页能分出几百个子范围，三四层树就能覆盖非常大的记录规模。真正价值不在于“比较次数从 N 变成 log N”这一句，而在于：一次树下降只需要访问少量页，且内部节点只承担导航，可以让单页容纳更多键，从而提高分支因子、降低树高。
5.1 为什么不是二叉搜索树¶
如果每个节点只有两个孩子，即使保持平衡，在百万、千万级键数量下树高仍明显大于高分支 B+ 树。数据库索引访问常常受内存页命中和存储 I/O 影响，因此减少树层级和页访问非常重要。
5.2 为什么 B+ 树适合范围扫描¶
索引键有序。先通过树下降定位范围起点，例如：
找到 100000 附近的叶子页后，可以沿同层叶子页继续向后扫描，而不需要为区间里的每个键都重新从根节点查一次。InnoDB 同一层 B-tree index pages 通过前后页指针维持有序双向链关系，这使叶子范围遍历非常自然。
这也是 B+ 树相比哈希索引的重要优势：哈希更擅长精确等值定位，但天然不保存全局键顺序，范围查询、按索引顺序读取就不占优势。
6. 聚簇索引查找：主键命中后直接拿到整行¶
执行：
如果 id 是主键，InnoDB 大致执行：
1. 从聚簇 B+ 树 root page 开始；
2. 根据 id = 91231 决定进入哪个子页；
3. 继续下降到 leaf page；
4. 在叶子页找到主键为 91231 的 leaf record；
5. 这条 leaf record 本身就包含行数据，因此直接得到 SELECT * 所需字段。
这就是聚簇索引主键查询不需要“先索引定位，再去另一个数据文件取行”的原因。
如果相关根页、内部页、叶子页已经在 Buffer Pool 中，上述访问不需要每层都做磁盘 I/O；如果不在，才需要把对应页从持久存储加载进 Buffer Pool。树高只是访问页数量的一个因素，页是否已缓存同样重要。
7. 二级索引到底存什么：索引键 + 主键¶
假设：
在 InnoDB 中，二级索引叶子记录不会再复制整行。它至少包含二级索引列以及对应的主键列。可以把记录抽象成：
例如：
因此：
典型路径是：
第二步就是常说的回表。严格说，不是“回到表文件”，而是拿二级索引中保存的主键，再去聚簇索引查完整行。
如果 owner_id = 42 命中 200 行，可能需要对大量主键做聚簇索引读取。命中行越多，回表带来的随机页访问成本越值得考虑。
7.1 二级索引为什么要保存主键，而不是物理页地址¶
记录因页分裂、页合并、重组等原因发生物理位置变化时，如果二级索引保存固定物理地址，大量二级索引记录可能需要跟着改。保存稳定的聚簇键，可以让二级索引通过逻辑主键重新定位行。
同时，这也解释了“主键越宽，二级索引越大”：主键值被复制到每个二级索引记录里。
8. 覆盖索引：为什么有时查二级索引后不用回表¶
仍然有：
查询：
idx_owner_id 的叶子记录已经包含 owner_id 和主键 id，因此执行器可以直接从二级索引拿到结果，不必再访问聚簇索引。这就是覆盖索引的核心：某个索引已经包含该查询需要返回和过滤的全部列。
但如果查询是：
单列 idx_owner_id 无法覆盖 status、object_key、created_at 等所有列，仍需要回表。
所以“我把用户要返回的数据全都加进索引，避免回表”不是自动成立的优化。需要先看真实查询列。如果为了覆盖一个 SELECT * 把很多大列塞进联合索引，索引体积、Buffer Pool 占用、写放大都会迅速增加，可能得不偿失。正确做法通常是先收敛查询真正需要的列，再设计少量有价值的联合/覆盖索引。
联合索引的最左前缀等规则属于 DB-02 的更深索引专题，但“覆盖索引为什么能避免回表”在这里必须先理解。
9. 全表扫描、主键查找、二级索引查找、范围扫描的执行差异¶
把常见访问路径并排看最清楚。
9.1 全表扫描¶
如果 size_bytes 没有合适索引，InnoDB 需要扫描聚簇索引叶子记录并逐条判断条件。表有 N 行时，检查记录数大体随 N 线性增长。
9.2 主键等值查找¶
沿聚簇 B+ 树下降到目标叶子页，叶子记录就是整行。没有二次回表。
9.3 二级索引等值查找 + 回表¶
先查二级索引得到主键，再按主键查聚簇索引获得完整行。如果命中很多记录，回表次数和访问页分布会成为主要成本。
9.4 覆盖索引查找¶
如果二级索引已经覆盖所有需要字段，可以停在二级索引叶子页，不再访问聚簇索引。
9.5 范围扫描¶
先通过 B+ 树定位范围左边界，再沿有序叶子页继续扫描直到超过上界。这里 B+ 树的顺序性直接发挥作用。
10. “有索引”为什么仍可能全表扫描：核心是成本，不是规则口诀¶
假设表有 1,000,000 行，status 上存在二级索引：
执行：
FAILED 只命中约 0.2%。通过 idx_status 找到少量主键，再回表读取少量行，通常明显比扫描一百万行更有吸引力。
执行：
如果命中 80% 的表，二级索引方案意味着读取大量二级索引记录，再大量回表读取聚簇记录。此时直接顺序扫描聚簇索引可能更便宜。
因此优化器关心的是：
“区分度低的列不要建索引”也只是经验，不是绝对规则。例如 status 只有几个枚举值，但如果 FAILED 永远只占 0.1%，专门查询 FAILED 的后台任务仍可能从索引中获益。
11. EXPLAIN 要看什么，而不是只背 type¶
MySQL 中应使用：
基础阶段至少关注：
MySQL 8.0+ 还可以使用：
它会真正执行查询并提供实际执行时间和实际行数等信息，因此可以用来比较“优化器估计”和“真实执行”是否一致。生产环境使用时要注意这不是纯静态分析：查询真的会执行。
不要把 EXPLAIN 理解成“检查有没有索引”的命令。它回答的是“优化器计划怎样执行这条 SQL”。
12. INSERT 为什么会因为索引变多而变贵¶
假设一行新数据：
表中有：
一次 INSERT 至少要在三个 B+ 树逻辑结构中增加记录：
如果再加十个二级索引，每次 INSERT 就多出十份索引维护。索引不只占磁盘空间，还占 Buffer Pool，更增加页修改、日志、刷脏页等工作量。
因此“读查询可能变快”与“写路径一定变重”是索引设计最基本的权衡。
13. UPDATE 对哪些索引有影响：看被修改的键¶
执行：
首先通过主键聚簇索引找到这一行。因为 status 被 idx_status 索引，旧的二级索引键：
需要变成：
逻辑上等价于让旧索引项失效并增加新索引项。owner_id 没有变化，所以不需要因为业务键变化去重建 idx_owner_id 键。
如果改的是：
且 updated_at 没有索引，就不存在维护 updated_at 二级索引这一项。
所以“UPDATE 会更新所有索引”这个说法太粗。更准确的是：更新整行本身有存储引擎成本；当被索引列发生变化时，对应索引结构还要额外维护。事务版本、undo、redo 等会在后续专题详细展开。
14. DELETE 为什么也要维护索引¶
删除一行时，不只要让聚簇索引中的行失效，所有指向这行的二级索引项最终也必须被清理，否则二级索引会继续返回已经不存在的主键。
因此对一张二级索引很多的写密集表，INSERT、UPDATE、DELETE 都要承担额外索引维护成本。MySQL 官方优化文档也明确提醒：不必要的索引会增加 INSERT、UPDATE、DELETE 的成本。
15. 页满以后会发生什么：页分裂与主键局部性¶
B+ 树叶子页容量有限。MySQL 8.4 文档说明，新的记录插入聚簇索引时，InnoDB 会尝试保留一部分页空间给未来插入和更新。随着页逐渐填满，新记录如果应该插入某个已满页的中间位置，就可能触发页分裂：申请新页，把部分记录移动到新页，再更新父节点的导航关系。
页分裂不是错误，但会产生额外页分配、数据移动和父节点维护，并可能降低局部空间利用率。
15.1 递增主键为什么通常更友好¶
如果主键大致递增：
新记录主要追加到聚簇索引最右侧附近，插入位置具有较好的局部性。官方文档也说明，持续升序插入且只从末端删除时，可以避免随机插入造成的那类索引碎片。
如果主键是完全随机的 128-bit UUID：
每次新键可能落到不同叶子页。工作集更分散，更容易触碰大量页，也更容易造成页分裂和碎片。
但“自增主键绝对最好”也不成立。分布式 ID、写热点、数据生成方式、分库分表等都可能改变取舍。DB-01 先掌握单机 InnoDB 的局部性机制。
16. 为什么 B+ 树通常很矮：用数量级推演，不背“固定三层”¶
假设索引页默认 16 KB。为了建立数量级直觉，先做简化假设：一个内部节点的键 + 子页指针等平均占 32 B，则一个页理论上可以容纳数百个导航项。实际还要扣页头、目录、记录头、空闲空间等，因此不能直接拿 16 KB / 32 B 当真实容量。
即使每层平均只有 300 个分支：
所以 B+ 树能在非常大的 N 下保持较小高度。
如果需要估算“B+ 树一般几层能放多少数据”，正确做法不是背“3 层等于 2000 万”。应该先声明页大小、键大小、记录大小、分支因子假设，再估算。主键是 BIGINT 和主键是长字符串时，单页可容纳的索引项差异很大。
17. Buffer Pool：索引页和数据页为什么不一定每次都读磁盘¶
InnoDB Buffer Pool 是内存缓存区，用于缓存表和索引数据页。一次主键查找可能逻辑上经过 root、internal、leaf 三类页，但如果这些页都在 Buffer Pool 中，就不需要对每一层做一次磁盘读取。
常见热根页和高层内部页很容易长期处于内存中，因此真正造成明显 I/O 的往往是大量不同的叶子页访问。
这也解释了两个常见现象。
第一，同一条 SQL 第一次执行和随后多次执行的延迟可能不同，因为缓存热度变了。
第二，一个超大的冗余索引不仅浪费磁盘，也会竞争 Buffer Pool 空间，把真正热点的数据页和索引页挤出去。
Buffer Pool 采用基于 LRU 思路的页管理机制，并针对大扫描造成的缓存污染做了专门设计。DB-01 需要记住的是：查询成本最终是“访问哪些页，以及这些页是否已在内存”，不是抽象树高直接等于磁盘 I/O 次数。
18. 二级索引写入还有一个经典 InnoDB 优化：Change Buffer¶
当修改一个非唯一二级索引，而目标索引页当前不在 Buffer Pool 中时，如果每次都先把这个页从磁盘读进来再修改，会产生随机读。InnoDB 的 Change Buffer 可以暂存部分二级索引页变更，等该页之后被读入 Buffer Pool 时再 merge，从而减少这类随机 I/O。
Change Buffer 只服务二级索引，不服务聚簇索引；对唯一二级索引，插入缓冲也不能像普通非唯一二级索引那样延迟唯一性确认，因为新键写入前必须检查是否已经存在。还要注意版本差异：MySQL 8.4 的 innodb_change_buffering 默认值已经是 none，即默认不启用 change buffering；8.4 之前默认值曾是 all。因此这里应该把它解释成 InnoDB 提供的机制和适用条件，而不是说“所有 8.4 实例默认都会走 Change Buffer”。
这部分属于 InnoDB 写路径的进一步机制。如果只分析“为什么索引多会拖慢写入”，多个 B+ 树维护、页访问和页分裂已经能解释主要成本；继续分析二级索引随机写优化时，再引入 Change Buffer 及其版本配置。
19. 把一次二级索引查询完整推演出来¶
表结构：
CREATE TABLE media_asset (
id BIGINT NOT NULL,
owner_id BIGINT NOT NULL,
object_key VARCHAR(512) NOT NULL,
status VARCHAR(32) NOT NULL,
created_at DATETIME(6) NOT NULL,
PRIMARY KEY (id),
KEY idx_owner_id (owner_id)
) ENGINE=InnoDB;
执行：
假设命中 3 行：
执行过程可以描述为：
1. MySQL 优化器选择 idx_owner_id；
2. InnoDB 从 idx_owner_id 根页向下，定位 owner_id=42 的叶子范围；
3. 从二级索引叶子记录中读出 1001、1049、91231 三个主键；
4. 查询需要 object_key，而 idx_owner_id 中没有 object_key；
5. 分别使用三个 id 查 PRIMARY 聚簇索引；
6. 在聚簇叶子记录中读取 object_key；
7. 返回三条结果。
如果改成：
二级索引记录本来就包含主键 id，因此可以直接返回，不需要第 5 步的聚簇索引查找。
这个例子要做到能口头推演，因为“聚簇索引 / 二级索引 / 回表 / 覆盖索引”本质上是一件事的四个视角。
20. 把一次高选择性和低选择性查询放在一起比较¶
有索引：
数据：
对于：
idx_status 本身包含 status 和主键 id，不仅过滤比例高，而且可能直接覆盖查询。这个计划非常有吸引力。
换成：
命中 80 万行，并且每行都需要回表取完整字段。二级索引的优势迅速下降。扫描聚簇索引可以一次顺序处理大量完整记录，优化器可能选择扫描。
这比只背“status 区分度低所以不能建索引”准确得多，因为同一列在不同值、不同 SELECT 列表下，成本都可能不同。
21. 索引设计的基本决策顺序¶
面对一个真实查询，不应从“给 WHERE 字段建索引”开始，而应按下面的因果顺序思考。
先看访问模式：SQL 的过滤条件、排序条件、返回列、频率、命中行数和分页方式是什么。
再看数据分布：条件选择性如何，某些值是否极端偏斜。
再看当前物理结构：主键是什么，现有二级索引是什么，是否需要回表，主键宽度是多少。
再看写负载：INSERT QPS、哪些索引列频繁 UPDATE、索引数量和大小。
最后用 EXPLAIN、EXPLAIN ANALYZE 和真实压测验证。
索引不是“SQL 慢了就加”；它是在读路径上增加一份有序结构，并把维护成本转移到写路径和内存/磁盘占用上。
参考资料¶
-
MySQL 8.4 Reference Manual - Clustered and Secondary Indexes https://dev.mysql.com/doc/refman/8.4/en/innodb-index-types.html
-
MySQL 8.4 Reference Manual - The Physical Structure of an InnoDB Index https://dev.mysql.com/doc/refman/8.4/en/innodb-physical-structure.html
-
MySQL 8.4 Reference Manual - Optimizing InnoDB Queries https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-queries.html
-
MySQL 8.4 Reference Manual - Optimization and Indexes https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html
-
MySQL 8.4 Reference Manual - CREATE TABLE / PRIMARY KEY / UNIQUE https://dev.mysql.com/doc/refman/8.4/en/create-table.html
-
MySQL 8.4 Reference Manual - InnoDB Buffer Pool Optimization https://dev.mysql.com/doc/refman/8.4/en/innodb-buffer-pool-optimization.html
-
MySQL source documentation - InnoDB B-tree page previous/next links https://dev.mysql.com/doc/dev/mysql-server/latest/fil0types_8h.html
-
MySQL 8.4 Reference Manual - Change Buffer https://dev.mysql.com/doc/refman/8.4/en/innodb-change-buffer.html
-
MySQL 8.4 Reference Manual - EXPLAIN Statement https://dev.mysql.com/doc/refman/8.4/en/explain.html