数据库基础体系 · 第 5/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

数据库索引原理：B+Tree、联合索引、覆盖索引与写放大

数据库索引的核心作用，是把“对大量数据逐行检查”转换为“沿着有序结构定位少量候选记录”。但索引并不是免费的加速器：它需要额外存储空间，需要在插入、删除和更新时维护，还会受到事务可见性、数据分布、查询条件和执行计划的共同影响。

理解索引，至少需要回答四个问题：

1. B+Tree 如何把一次查找从近似全表扫描降低为树上的少量页面访问？
2. 联合索引为什么遵循“最左匹配”，以及等值、范围条件如何共同决定可用的索引前缀？
3. 覆盖索引为什么可以减少回表，但为什么“索引包含了查询列”仍不一定等于“完全不访问表”？
4. 写放大从哪里产生，为什么增加一个索引可能同时影响写延迟、空间、缓存和复制链路？

下文以 PostgreSQL 当前稳定版本的 B-tree 语义和 MySQL 8.4、InnoDB 的常见实现为主要边界。两者都支持 B-tree 索引，但表组织方式不同，因此“回表”和“覆盖”的具体含义不能混为一谈。

---

一、索引首先解决的是“减少需要检查的数据”

假设表中有一亿行，查询为：

SELECT *
FROM orders
WHERE user_id = 42;

没有索引时，数据库通常只能检查大量甚至全部数据页，逐行判断 user_id = 42 是否成立。这个过程的主要成本包括：

• 读取表数据页；
• 解码或访问行；
• 执行谓词判断；
• 处理事务可见性；
• 找到匹配行后构造结果。

如果有：

CREATE INDEX orders_user_id_idx ON orders (user_id);

数据库可以先在索引中定位 user_id = 42 的位置，再取得对应的数据行。索引并不直接改变 SQL 的逻辑结果，它只是提供了一个更便宜的物理访问路径。

这里有一个重要边界：索引能否被使用，取决于使用索引的总代价是否低于其他路径。即使存在索引，优化器也可能选择全表扫描。例如：

• 目标值占表中绝大多数行；
• 表很小，扫描表的成本低；
• 统计信息认为索引选择性很差；
• 需要返回大量列，逐条回表比顺序扫描更慢；
• 查询条件对索引列做了无法有效利用索引顺序的表达式变换。

因此，“建了索引”与“查询一定走索引”是两个不同命题。

---

二、B+Tree 的结构与查找过程

2.1 从有序数组到多路平衡树

如果把一列值排成有序数组，二分查找可以在约 O(log₂N) 次比较内定位目标。但数据库不能简单把一亿个键放进一个连续数组，因为：

• 数据页按块存储，不能为了插入一个值频繁移动整个数组；
• 磁盘或 SSD 访问以页为单位；
• 索引需要持续支持插入、删除和更新；
• 树节点必须适应固定大小的数据页。

B+Tree 是一种多路平衡搜索树。与二叉搜索树不同，一个节点可以包含很多分隔键和很多子指针。设内部节点最多有 m 个子指针，则其树高近似为：

$$h \approx \log_m N$$

其中：

• N 是索引中叶子项的大致数量；
• m 是一个内部页面能容纳的子指针数量；
• h 是从根到叶子的层数。

由于一个数据库索引页通常能容纳许多键，m 往往远大于 2，所以树高通常很低。一次查找主要需要访问：

1. 根页面；
2. 若干内部页面；
3. 一个叶子页面；
4. 如果需要完整行，再访问表数据页面。

缓存命中的页面可能不需要物理 I/O，但仍会产生内存访问和锁/闩相关成本。

2.2 B+Tree 的典型特征

B+Tree 通常具有以下结构特征：

• 内部节点保存用于导航的分隔键和子节点指针；
• 叶子节点保存实际索引条目；
• 所有叶子处于相同深度，因此树是平衡的；
• 叶子节点通常按键顺序连接，便于范围扫描；
• 插入导致节点过满时进行分裂；
• 删除导致节点过稀时可能合并或重新分配。

“B+Tree”是数据结构名称；具体数据库产品的 B-tree 实现可能有额外优化。PostgreSQL 的常规 btree 索引和 InnoDB 的索引都属于这一类有序平衡树，但它们保存的数据内容、并发维护方式和表组织方式并不完全相同。

2.3 等值查找的逐步过程

假设索引中有如下叶子顺序：

[10, 15, 21, 30, 42, 50, 63, 77]

查询：

WHERE user_id = 42

查找过程可以抽象为：

1. 从根节点读取分隔键；
2. 判断 42 落在哪个子树范围；
3. 进入对应内部节点；
4. 重复比较，直到叶子节点；
5. 在叶子节点中定位 42；
6. 根据索引条目取得对应记录位置；
7. 对记录执行剩余过滤条件和事务可见性判断。

如果索引列不唯一，叶子中可能有多个相同键值，它们对应多条记录。数据库需要继续扫描该键值对应的连续索引范围。

2.4 范围查找为什么适合 B+Tree

查询：

WHERE user_id >= 42
  AND user_id < 50

不是分别查找每一个值，而是：

1. 沿树定位第一个不小于 42 的叶子项；
2. 在叶子链上顺序读取后续项；
3. 直到遇到 50 或范围结束。

因此 B+Tree 同时适合：

• 精确匹配；
• 前缀范围；
• 排序；
• 分页中的有序游标定位。

例如：

SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

如果存在合适的联合索引，数据库可能直接按索引顺序获得前 20 条，而不必先收集所有匹配行再排序。

但这不是无条件成立的。排序方向、空值排序规则、过滤条件和优化器代价都会影响最终计划。

---

三、从索引项到表行：PostgreSQL 与 InnoDB 的差异

“索引找到键”之后，数据库还需要取得查询所需的数据。这里最容易混淆的是 PostgreSQL 和 InnoDB 的数据组织方式。

3.1 PostgreSQL：索引通常指向堆表中的行位置

PostgreSQL 的普通表数据存储在 heap 中。B-tree 索引项通常包含：

• 索引键；
• 指向表中行版本位置的 TID，也就是页号和页内位置。

因此一个典型过程是：

索引扫描
  -> 找到索引键
  -> 得到 TID
  -> 访问 heap 页面
  -> 检查行版本是否对当前事务可见
  -> 读取所需列

这个从索引访问 heap 数据页的步骤通常称为回表，在 PostgreSQL 的执行计划中常见为 Index Scan。

PostgreSQL 还支持 Index Only Scan。它的前提不只是“查询列都在索引里”，还包括数据库能够确认相应 heap 页面上的行对当前快照可见。PostgreSQL 使用 visibility map 记录页面级可见性信息；如果页面没有被标记为 all-visible，执行器仍可能访问 heap 检查 MVCC 信息。

因此：

列被索引覆盖
≠
一定完全不访问 heap

在频繁更新、刚插入、尚未充分 vacuum 的表上，visibility map 覆盖率可能较低，索引只读扫描仍需要访问 heap。

3.2 InnoDB：聚簇主键与二级索引

InnoDB 的主键索引是聚簇索引。表记录本身按主键组织在主键 B+Tree 的叶子节点中。

假设：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

二级索引：

CREATE INDEX orders_user_id_idx ON orders (user_id);

其二级索引叶子项通常保存：

(user_id, 主键 id)

查询 user_id = 42 时，过程是：

二级索引
  -> 找到 user_id = 42 和对应主键 id
  -> 再到主键聚簇索引查找完整行

这也是 InnoDB 中二级索引回表的典型含义。

如果查询只需要：

SELECT id, user_id
FROM orders
WHERE user_id = 42;

二级索引叶子中已经有 user_id 和主键 id，可能直接完成查询，这通常称为覆盖索引访问。

但如果查询：

SELECT amount
FROM orders
WHERE user_id = 42;

二级索引没有 amount，就需要根据主键回到聚簇索引读取完整记录。

3.3 主键长度会影响 InnoDB 二级索引

因为 InnoDB 二级索引叶子通常携带主键值，所以主键越宽，所有二级索引都可能变大。例如：

• BIGINT 主键占用较少且长度固定；
• 较长的字符串主键会增加每个二级索引项的大小；
• 二级索引变大后，能容纳的条目减少，树可能更高，缓存命中率也可能下降。

这不是说字符串主键一定错误，而是主键设计会通过二级索引传播其存储和访问成本。

---

四、联合索引：键是按字典序排列的

联合索引，也称复合索引，是多个列共同组成的一个索引键。例如：

CREATE INDEX orders_user_created_idx
ON orders (user_id, created_at);

它不是两个独立索引的简单叠加，而是按如下字典序排列：

(user_id, created_at)

示例数据：

user_id	created_at
1	2025-01-01 09:00
1	2025-01-02 10:00
1	2025-01-03 11:00
2	2025-01-01 08:00
2	2025-01-04 12:00
3	2025-01-02 13:00

排序优先级是：

1. 先比较 user_id；
2. 只有 user_id 相同，才比较 created_at。

这就是“最左匹配”背后的数据结构原因。

4.1 为什么 (user_id, created_at) 能支持三类查询

对于索引：

(user_id, created_at)

以下查询通常都能有效利用索引前缀：

WHERE user_id = 1

索引可以定位 user_id = 1 的连续区间。

WHERE user_id = 1
  AND created_at = '2025-01-02 10:00:00'

索引可以进一步定位该区间中的具体键。

WHERE user_id = 1
  AND created_at >= '2025-01-02'
  AND created_at <  '2025-01-04'

索引先定位 user_id = 1，再在该用户的局部有序范围中查找时间区间。

形式化地说，对于键：

$$K=(k_1,k_2,\ldots,k_n)$$

如果查询对前若干列给出了可用于导航的条件，数据库可以利用一个连续的索引区间进行查找。等值条件把前缀固定下来，范围条件则把搜索空间切成一个区间。

4.2 为什么单独查询第二列通常不能充分利用该索引

查询：

WHERE created_at >= '2025-01-02'

并没有固定 user_id。由于不同用户的 created_at 值交错分布，索引的整体顺序并不是按 created_at 全局有序：

(1, 2025-01-01)
(1, 2025-01-02)
(2, 2025-01-01)
(2, 2025-01-04)
(3, 2025-01-02)

满足时间条件的项分散在多个区域，无法像单列 created_at 索引那样直接定位一个连续的全局区间。

优化器仍可能选择扫描整个联合索引，因为联合索引可能比表更小，或者可以作为其他访问路径的一部分，但这不等于第二列获得了与单列索引相同的定位能力。

4.3 等值列、范围列与后续列

考虑索引：

(a, b, c)

查询：

WHERE a = 10
  AND b >= 100
  AND c = 5

典型的逻辑是：

1. a = 10 固定第一个索引维度；
2. b >= 100 在 a = 10 的子范围中形成范围扫描；
3. c = 5 通常不能再把 B+Tree 的扫描边界精确缩小为一个连续区间，因为 b 已经是范围；
4. 但 c = 5 仍可能作为索引扫描期间的过滤条件，减少回表。

因此需要区分：

• 索引定位条件：用于确定 B+Tree 要扫描的起止边界；
• 索引过滤条件：虽然在索引层判断，但不一定进一步缩小扫描边界；
• 表过滤条件：必须回表后才能判断。

“范围列后面的列不能使用”是过度简化。更准确的说法是：范围列之后的列通常难以继续形成传统 B+Tree 的连续扫描边界，但仍可能用于索引条件下推、索引过滤或覆盖读取，具体取决于引擎和执行计划。

4.4 联合索引顺序不是简单的“高选择性优先”

常见建议是把选择性高的列放前面，但它不是普遍定律。索引顺序需要同时考虑：

• 查询是否经常对某列做等值匹配；
• 是否需要支持范围查询；
• 是否需要支持 ORDER BY；
• 是否需要覆盖查询列；
• 数据分布与相关性；
• 写入时索引维护成本；
• 是否存在多个查询模式。

例如：

WHERE tenant_id = ?
  AND status = ?
  ORDER BY created_at DESC
LIMIT 20

可能更适合：

(tenant_id, status, created_at DESC)

即使 tenant_id 的全局选择性并不高，因为多租户系统中它可能是所有查询都必须使用的分区前缀。

反例是只根据单列基数选择：

(status, tenant_id, created_at)

如果 status 只有 pending、paid、cancelled 三种值，而查询几乎总是先限定租户，那么把低基数 status 放在最左侧可能导致扫描范围过大，也无法自然支持按租户的局部排序。

---

五、联合索引与排序、分页

索引不仅能过滤，还可能消除排序。

索引：

CREATE INDEX orders_user_created_id_idx
ON orders (user_id, created_at DESC, id DESC);

查询：

SELECT id, created_at, amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

其逻辑过程可以是：

1. 定位 user_id = 42 的索引区间；
2. 从 created_at 降序的一端开始扫描；
3. 使用 id 作为相同时间戳下的稳定次序；
4. 取到 20 条后停止；
5. 如果 amount 不在索引中，再对这些行回表。

这里的 id 是重要的稳定排序列。只有按 created_at 排序时，如果很多行时间相同，分页可能遇到顺序不稳定或重复/遗漏问题。

5.1 OFFSET 分页的成本

SELECT id, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

即使有索引，数据库通常也需要跳过前 100000 个符合条件的索引项，才能返回后面的 20 个。

更适合大页码的方式是基于游标的分页：

SELECT id, created_at
FROM orders
WHERE user_id = 42
  AND (created_at, id) < ('2025-01-03 11:00:00', 9000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

这里的边界值是上一页最后一条记录。它把“跳过很多行”转换为“从某个索引位置继续扫描”。

实际使用时应注意：

• 元组比较要求排序方向和条件逻辑一致；
• created_at 与 id 的组合应能形成稳定顺序；
• 如果允许空值，需要明确 NULL 排序规则；
• 数据在分页期间发生插入或删除时，结果仍取决于事务隔离级别和业务对一致性的要求。

---

六、覆盖索引：减少回表，不是绕过所有检查

6.1 覆盖索引的定义

如果一个查询所需的列都可以从索引项中获得，就称该索引覆盖了这个查询。

例如查询：

SELECT user_id, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

索引：

(user_id, created_at DESC)

至少包含了：

• 过滤列 user_id；
• 排序列 created_at；
• 投影列 user_id、created_at。

因此执行器可能不需要访问完整表行。

覆盖索引的收益来自两点：

1. 索引项通常比完整表行更小，扫描的数据量更少；
2. 不需要为每个匹配索引项再次访问表数据页，随机访问减少。

6.2 PostgreSQL 的 INCLUDE

PostgreSQL 支持在 B-tree 索引中使用 INCLUDE 添加非排序键列：

CREATE INDEX orders_user_created_cover_idx
ON orders (user_id, created_at DESC)
INCLUDE (amount);

这里：

• user_id 和 created_at 是键列，参与 B-tree 排序和搜索；
• amount 是包含列，不参与排序，也不能用来构造普通索引边界；
• amount 的存在目的是让某些查询能够从索引中直接获得结果列。

查询：

SELECT created_at, amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

可能使用 Index Only Scan。但是否真的完全不访问 heap，还要看 visibility map 和事务可见性。

INCLUDE 会增加索引项大小，因此不应把大量宽列无条件加入索引。过大的索引会增加存储、缓存和写入成本，甚至使页分裂更频繁。

6.3 InnoDB 中的覆盖索引

InnoDB 没有与 PostgreSQL INCLUDE 完全对应的通用语法。二级索引本身包含：

• 显式定义的二级索引列；
• 聚簇主键列。

因此以下查询通常可以由二级索引覆盖：

SELECT id, user_id
FROM orders
WHERE user_id = 42;

因为二级索引叶子项中有 user_id 和主键 id。

如果希望覆盖 amount，通常需要把它作为索引列：

CREATE INDEX orders_user_created_amount_idx
ON orders (user_id, created_at, amount);

但 amount 作为普通索引键的一部分会影响索引排序和索引大小。它可以帮助覆盖查询，却不一定参与搜索边界。

6.4 覆盖索引的反例

以下索引：

(user_id, created_at)

并不能覆盖：

SELECT amount
FROM orders
WHERE user_id = 42;

因为 amount 不在索引项中。

即使查询只选择索引中的列，也可能存在其他原因导致不能获得理想收益：

• PostgreSQL 的 heap 页面不可通过 visibility map 直接确认可见；
• 查询包含索引无法直接计算的表达式；
• 需要检查表级或行级安全策略；
• 索引项本身不能提供某些 MVCC 所需信息；
• 优化器估算使用索引的成本更高。

所以覆盖索引的准确含义是：索引提供了查询所需的数据列，允许执行器有机会避免回表，而不是一种无条件的执行保证。

---

七、MVCC 如何影响索引扫描

索引只保存用于定位和取值的信息，事务可见性通常还需要结合行版本元数据判断。

7.1 PostgreSQL 的可见性路径

PostgreSQL 的更新通常不是原地覆盖旧版本，而是产生新的行版本，并让索引和 heap 共同参与可见性判断。一个索引项可能指向：

• 当前事务可见的行版本；
• 对当前快照不可见的旧版本；
• 已被删除但仍因其他事务快照而暂时保留的版本。

因此 PostgreSQL 的典型索引扫描不是：

索引命中 = 结果必然有效

而是：

索引找到候选 TID
  -> 访问 heap
  -> 根据快照判断版本可见性
  -> 判断剩余谓词
  -> 返回结果

Index Only Scan 依赖 visibility map 来减少 heap 访问，但如果页面不是 all-visible，仍要回到 heap 验证。

这也解释了为什么更新频繁的表上，索引只读扫描的收益可能低于预期。

7.2 InnoDB 的一致性读

InnoDB 使用 MVCC 的 undo 信息构造符合事务读视图的旧版本。二级索引定位到记录后，执行器仍可能需要根据聚簇记录和事务信息判断可见性。

因此“二级索引覆盖了查询列”主要减少的是取得列值所需的聚簇索引访问，并不意味着事务一致性检查从逻辑上消失。

隔离级别、锁定读和普通一致性读也会改变行为：

SELECT ...
FROM orders
WHERE user_id = 42;

与：

SELECT ...
FROM orders
WHERE user_id = 42
FOR UPDATE;

不是同一种访问语义。后者需要锁定符合条件的记录，并可能需要访问聚簇记录完成锁定和版本相关处理。不能把普通覆盖读取的成本直接套用到锁定读。

---

八、索引条件、表达式与可搜索性

B+Tree 依赖的是索引键的有序性。查询条件只有在能够映射为索引键上的连续区间时，才能充分发挥树的导航能力。

8.1 对列做函数变换

索引：

CREATE INDEX users_email_idx ON users (email);

查询：

WHERE lower(email) = 'alice@example.com'

普通 email 索引按原始 email 排序，而查询比较的是 lower(email) 的结果，两者不是同一个有序键。

在 PostgreSQL 中，可以创建表达式索引：

CREATE INDEX users_lower_email_idx
ON users (lower(email));

在 MySQL 8.4 中，可以使用与版本语义匹配的函数索引或生成列方案；实际部署前应确认函数是否允许建立索引、表达式是否确定性以及优化器是否能匹配该表达式。不要把 PostgreSQL 的表达式索引语法直接复制到 MySQL。

8.2 前缀匹配与后缀匹配

对于字符串索引：

WHERE email LIKE 'alice%'

前缀 alice 对应一个连续的排序范围，通常有机会利用 B-tree。

而：

WHERE email LIKE '%@example.com'

匹配的是后缀，符合条件的值可能分散在整个索引中，普通 B-tree 无法直接从后缀定位起点。

这不是“LIKE 一律不能走索引”，而是要看通配符是否破坏了左侧有序前缀。

8.3 隐式类型转换和排序规则

以下因素也可能破坏预期的索引使用：

• 查询参数类型与列类型不一致，导致隐式转换；
• 字符串列的排序规则与比较语义不匹配；
• 对索引列进行了算术、类型转换或非确定性函数；
• 使用了不适合 B-tree 的相等或范围语义。

诊断时不能只看 SQL 文本中的“有无索引”，而要查看执行计划中实际的访问条件。

---

九、从执行计划验证索引是否真的有用

9.1 PostgreSQL 示例

创建测试表：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    amount NUMERIC(12, 2) NOT NULL
);

CREATE INDEX orders_user_created_idx
ON orders (user_id, created_at DESC);

查询计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

重点观察：

• 是否出现 Index Scan 或 Index Only Scan；
• Index Cond 是否包含 user_id = 42；
• 是否出现额外的 Filter；
• 是否有 Sort 节点；
• actual rows 与估算行数是否差距很大；
• Buffers 中 heap 读取是否很多；
• 计划是否实际执行了大量回表。

如果出现：

Index Scan using orders_user_created_idx ...
  Index Cond: (user_id = 42)

说明索引至少被用于定位用户范围。

如果出现：

Filter: ...
Rows Removed by Filter: ...

说明还有条件没有作为索引边界处理，可能在索引层或 heap 层过滤。

如果出现 Index Only Scan，还应观察 heap fetches。heap fetches 较高，说明索引覆盖并没有完全消除 heap 访问。

EXPLAIN ANALYZE 会真正执行语句。对于 SELECT 通常风险较低，但对包含修改的语句必须谨慎；生产诊断应先确认语句是否有副作用，并结合采样和只读环境验证。

9.2 MySQL 示例

EXPLAIN ANALYZE
SELECT id, created_at, amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

可关注：

• type 是否为 const、ref、range 等；
• key 是否选择了预期索引；
• key_len 使用了多少索引前缀；
• rows 估算扫描行数；
• filtered 估算过滤比例；
• 是否出现 Using filesort；
• 是否出现 Using index。

Using index 通常表示查询可以从索引读取所需列，即覆盖索引访问；它不等同于“没有任何内部成本”，也不等同于 PostgreSQL 的 Index Only Scan 语义。

EXPLAIN ANALYZE 会执行语句并返回实际运行信息。对于写语句、锁定读或可能扫描大量数据的语句，应在隔离环境或只读副本中谨慎使用。

9.3 估算错误比“是否走索引”更值得关注

假设优化器估计：

rows = 100

但实际为：

actual rows = 5,000,000

那么它可能错误地选择：

• 先走低效索引再大量回表；
• 选择不合适的 Join 顺序；
• 使用不适合当前结果规模的嵌套循环；
• 误判排序或聚合成本。

索引诊断应与统计信息和基数估计结合。索引本身只是候选访问路径，优化器需要估计每条路径将访问多少行、多少页以及后续操作的成本。

---

十、写放大：一次逻辑写入为何产生多次物理维护

10.1 定义写放大

写放大可以泛指：

$$\text{写放大} = \frac{\text{实际产生的存储、日志或复制写入量}} {\text{业务逻辑写入量}}$$

分子和分母必须先明确口径。不同场景可能分别统计：

• 数据文件写入字节；
• WAL 或 redo 日志字节；
• 数据页修改次数；
• 存储设备实际写入量；
• 复制链路传输量；
• 后台 compaction 或 checkpoint 产生的写入。

因此“写放大是 3 倍”如果没有说明统计口径，结论并不完整。

10.2 插入一行时发生了什么

假设表有三个索引：

主键索引
(user_id, created_at) 联合索引
(status) 单列索引

插入一行：

INSERT INTO orders
(id, user_id, created_at, amount, status)
VALUES
(1001, 42, CURRENT_TIMESTAMP, 99.00, 'paid');

逻辑上只有一行数据，但数据库可能需要：

1. 写入表数据或聚簇主键叶子；
2. 向主键索引插入键；
3. 向联合索引插入键；
4. 向状态索引插入键；
5. 修改多个索引页的页面内容；
6. 如果页面空间不足，执行页面分裂；
7. 写入 WAL 或 redo 以保证崩溃恢复；
8. 更新相关统计或后台维护元数据；
9. 将修改传播到副本或变更日志系统。

页面分裂尤其重要。若插入键值集中在索引右端，可能形成右侧热点；若键值随机，插入会分散到更多页面，可能增加随机写和缓存失效率。

10.3 更新为什么可能比插入更贵

更新索引列：

UPDATE orders
SET user_id = 43
WHERE id = 1001;

通常不能简单地在 B+Tree 中“把原键改成新键而不留痕迹”。逻辑上更接近：

删除旧索引键 (42, ...)
插入新索引键 (43, ...)

如果一次更新修改了多个索引列，就可能需要维护多个索引。

更新非索引列也不一定没有索引成本：

• PostgreSQL 的 MVCC 更新可能产生新的 heap 行版本；
• 新旧版本的存活和清理需要 vacuum；
• 某些情况下索引可能需要新增指向新版本的条目，或受 HOT 更新条件影响；
• InnoDB 需要维护聚簇记录、undo、redo，并根据索引列是否变化处理二级索引。

因此“这个字段不在索引里，所以更新它完全不影响索引”并不总是准确。它可能不需要修改索引键，但仍会影响表版本、日志、清理和缓存。

10.4 索引数量与写成本的关系

如果一张表有 k 个需要维护的索引，一次插入通常至少要影响这些索引结构。可以用一个粗略模型表示：

$$C_{\text{write}} \approx C_{\text{table}} + \sum_{i=1}^{k} C_{\text{index},i} + C_{\text{log}} + C_{\text{split}} + C_{\text{visibility/cleanup}}$$

其中：

• C_table 是表或聚簇索引的写成本；
• C_index,i 是第 i 个索引的维护成本；
• C_log 是日志和恢复相关成本；
• C_split 是页分裂等结构调整成本；
• C_visibility/cleanup 是 MVCC 和后台清理成本。

这个公式不是产品内部精确计费模型，但能说明一个事实：增加索引通常降低某些读成本，同时增加所有相关写路径的成本。

10.5 写放大的生产表现

索引过多或索引过宽，可能表现为：

• 插入和更新延迟上升；
• WAL/redo 增长更快；
• 复制延迟上升；
• 索引文件占用大量空间；
• buffer pool 或 shared buffers 中有效数据比例下降；
• 页面分裂和碎片增加；
• 备份、恢复和迁移时间增长；
• 热点索引页产生更强竞争。

这些表现不一定都由索引导致。需要结合写入量、日志量、页面分裂、锁等待、缓存命中率和执行计划共同判断。

---

十一、索引设计的完整算例

考虑一个多租户订单表：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    amount DECIMAL(12, 2) NOT NULL
);

有三类查询：

-- Q1：查询某租户最近的订单
SELECT id, created_at, amount
FROM orders
WHERE tenant_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Q2：查询某租户某状态的最近订单
SELECT id, created_at, amount
FROM orders
WHERE tenant_id = 7
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Q3：后台按状态查所有租户订单
SELECT id, tenant_id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 100;

11.1 方案 A：三个单列索引

(tenant_id)
(status)
(created_at)

它们各自可能支持部分过滤，但：

• Q1 需要按 created_at 排序，可能额外排序；
• Q2 可能需要先按租户和状态过滤，再排序；
• Q3 也可能需要大量候选行和排序；
• 查询优化器有时可使用索引合并，但索引合并不是联合索引的等价替代，通常会产生额外集合合并或回表成本。

11.2 方案 B：联合索引 (tenant_id, status, created_at, id)

CREATE INDEX orders_tenant_status_created_id_idx
ON orders (tenant_id, status, created_at DESC, id DESC);

对 Q2，它具有良好的连续范围：

tenant_id = 7
status = 'paid'
created_at 按降序连续扫描
id 用于稳定排序

但对 Q1，status 没有条件。虽然 tenant_id = 7 能使用最左前缀，但同一租户下不同状态的记录会交错，无法直接按全局 created_at 顺序得到 Q1 的结果，可能需要额外排序或扫描更大范围。

11.3 方案 C：分别为 Q1、Q2 建立针对性索引

CREATE INDEX orders_tenant_created_id_idx
ON orders (tenant_id, created_at DESC, id DESC);

CREATE INDEX orders_tenant_status_created_id_idx
ON orders (tenant_id, status, created_at DESC, id DESC);

这可以分别优化 Q1 和 Q2，但付出：

• 多一个索引的存储；
• 插入和相关更新需要维护两个索引；
• 缓存空间被更多索引占用；
• 备份和恢复成本增加。

如果查询还需要 amount，在 PostgreSQL 中可考虑：

CREATE INDEX orders_tenant_created_cover_idx
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount);

在 InnoDB 中则需要把 amount 放入索引定义，例如：

CREATE INDEX orders_tenant_created_amount_idx
ON orders (tenant_id, created_at DESC, id DESC, amount);

但 MySQL 中这会使 amount 成为索引键的一部分，索引更宽；而且排序方向的具体利用仍应通过 EXPLAIN 验证。

11.4 Q3 为什么需要不同的前缀

Q3 没有限定 tenant_id，其主要访问模式是：

按 status 定位
按 created_at 排序

如果 Q3 是高频且结果集较小，可以考虑：

(status, created_at, id)

这说明联合索引不存在一个对所有查询都最佳的列顺序。列顺序描述的是某种查询工作负载下的物理访问路径，而不是列本身的永久属性。

---

十二、部分索引与条件索引的边界

PostgreSQL 支持部分索引，例如：

CREATE INDEX orders_pending_idx
ON orders (tenant_id, created_at DESC)
WHERE status = 'pending';

它只索引满足谓词的行，适合“某个状态占比小、查询频繁且条件稳定”的场景。

查询：

SELECT id, created_at
FROM orders
WHERE tenant_id = 7
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

如果优化器能证明查询条件蕴含索引谓词，就可能使用该部分索引。

但必须注意：

• 查询条件需要与部分索引谓词在逻辑上匹配；
• 参数化查询、表达式形式和类型转换可能影响优化器证明；
• 状态分布变化后，索引收益可能下降；
• 部分索引不适用于需要查询其他状态的路径。

MySQL 8.4 的索引能力和语法不能直接套用 PostgreSQL 的部分索引定义。若需要类似效果，可能要使用生成列、分区或其他数据建模方式，并单独验证优化器行为。

---

十三、常见误解与失败表现

13.1 “索引列越多越好”

错误。增加列可能带来覆盖收益，但也会：

• 增大索引项；
• 降低每页容纳的条目数；
• 增加树高或页面访问量；
• 增加写入和日志成本；
• 让缓存中更多空间被低频索引占用。

应区分“用于搜索和排序的键列”与“仅为覆盖而加入的列”，并根据真实查询负载取舍。

13.2 “低基数列不能建索引”

错误。低基数列单独作为过滤条件时，确实可能不划算；但它放在合适的联合索引中仍可能有价值。

例如：

WHERE tenant_id = ?
  AND deleted = false
  ORDER BY created_at DESC

deleted 只有两个值，却可能作为联合索引的一部分帮助缩小租户范围或支持特定查询。最终仍需看数据分布和计划。

13.3 “只要命中索引，就比全表扫描快”

错误。索引扫描通常包含：

索引访问 + 回表 + 可见性检查 + 随机页面访问

如果命中行比例很高，顺序扫描可能更便宜。优化器选择全表扫描不必然说明索引失效。

13.4 “联合索引可以替代所有单列索引”

不一定。索引 (a, b) 能较好支持以 a 开头的查询，但不能自然替代只按 b 查找的索引。

同时，现有联合索引是否已经覆盖某个单列索引，还要考虑：

• 查询是否使用该列作为最左前缀；
• 是否需要该列的独立排序；
• 索引项是否更宽；
• 优化器对扫描成本的估算。

13.5 “删除索引只会影响读性能”

错误。删除索引可能降低写放大和空间占用，也可能使某些唯一性约束、排序路径或并发访问路径改变。生产环境删除前应：

1. 检查索引是否承担唯一约束或约束实现；
2. 分析一段足够长的真实查询计划；
3. 观察是否存在低频但关键的后台任务；
4. 使用产品提供的并发或低阻塞索引操作方式；
5. 保留恢复方案，并在删除后验证慢查询、错误率和写入延迟。

13.6 “执行计划显示用了索引，所以性能没有问题”

错误。需要继续看：

• 实际扫描行数；
• 实际返回行数；
• 回表次数；
• 估算误差；
• 是否发生排序；
• 是否有大量 Rows Removed by Filter；
• 是否存在锁等待或 I/O 等待；
• 是否只是扫描了整个索引而非有效定位。

索引扫描一个大索引的全部内容，可能并不比扫描表更好。

---

十四、生产诊断的因果路径

遇到“有索引但查询仍慢”时，可以按以下因果链分析，而不是立即新增索引。

14.1 先确认查询语义和边界

确认：

• 实际执行的 SQL 是否包含隐式类型转换；
• 参数值是否与测试值分布相同；
• 是否存在排序、聚合、Join 或分页；
• 是否是普通读还是锁定读；
• 事务隔离级别和数据新旧程度是否不同。

同一条 SQL 在不同参数值下可能选择不同计划，这属于参数敏感和数据分布问题，而不是单纯的索引缺失。

14.2 再看实际计划

PostgreSQL：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...

MySQL：

EXPLAIN ANALYZE
SELECT ...;

重点将“估算行数—实际行数—访问页数—回表数量”串起来。如果估算错误，优先检查统计信息、数据相关性和谓词表达式；如果估算正确但回表过多，再考虑覆盖或重新设计访问路径。

14.3 区分 CPU、I/O 与锁等待

索引只解决部分数据定位成本。慢查询还可能等待：

• 行锁或表锁；
• buffer pool 或 shared buffers 中的数据页；
• WAL/redo 刷盘；
• 磁盘 I/O；
• Join 的内存和临时空间；
• 网络发送大量结果。

例如一个查询计划很短，但执行时间大多消耗在锁等待，那么继续增加索引通常不能解决根因。索引、MVCC 和锁是相互影响的：索引决定扫描和加锁范围，MVCC 决定可见性和版本处理，锁决定并发等待。

---

十五、如何在索引收益与写放大之间取舍

索引设计本质上是一个成本平衡问题：

$$\text{总成本} = \text{读成本} + \text{写成本} + \text{存储成本} + \text{维护与恢复成本}$$

一个只被偶尔执行的复杂查询，不一定值得让每次写入都维护一个很宽的索引。反过来，如果某个索引支持核心接口的高频点查、稳定排序或关键唯一性约束，其维护成本通常是合理的。

可以用以下问题检验一个索引是否有明确目的：

1. 它支持哪些具体查询？
2. 查询使用的是索引的哪一段前缀？
3. 它是否同时提供排序或覆盖能力？
4. 预计扫描多少索引项和多少表行？
5. 表的插入、更新和删除频率是多少？
6. 被索引列是否经常变化？
7. 它是否与已有索引高度重叠？
8. 该索引变宽后，对缓存、日志、复制和备份有什么影响？
9. 删除或不创建它时，哪条真实业务路径会退化？
10. 这个结论是否通过实际执行计划和线上指标验证？

最终，B+Tree 提供的是有序定位能力；联合索引决定这种有序性如何分层；覆盖索引减少的是取得查询列时的额外访问；写放大则揭示了所有这些读优化的维护代价。只有把数据结构、MVCC、执行计划和写入路径放在同一条因果链上，才能判断一个索引究竟是在解决问题，还是只是在增加系统负担。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：数据库 Schema 设计：数据类型、主外键、约束、NULL 与演进
• 下一篇：SQL 执行计划与查询优化：基数估计、Join 算法和慢查询诊断
• 延伸：MVCC、锁与死锁：可见性、锁粒度、等待图和线上诊断

官方资料

• PostgreSQL SQL Language
• MySQL Reference Manual

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。