主题
数据库索引：从少读哪些数据，理解一条查询为什么快
“建了索引却没走”和“走了索引还是慢”，是两种问题。前者可能是优化器认为顺序读更便宜，后者可能只是把全表扫描换成了全索引扫描。判断索引是否有用，要看它减少了多少候选记录、多少表页访问，以及排序等后续工作。
本文先解释 B+ 树和两种常见存储布局，再用 PostgreSQL 18.6 的实际执行计划观察联合索引、覆盖、表达式和跳跃扫描。MySQL 部分固定讨论 8.4 的 InnoDB，索引下推等行为依据官方文档，未在本次环境运行。完整建表、造数、查询和运行脚本都在文末。
一棵树为什么能少读很多页
数据库按页组织存储。每查一个键都扫描所有数据页，数据量增加后成本很快升高。B+ 树把有序键分层：内部节点保存分隔键和子页指针，叶节点保存有序条目；先沿分隔键定位叶页，再沿相邻叶页继续扫描范围。一个内部页能容纳很多分支，树通常不需要很深。高度、键宽、填充率和页大小都影响访问成本，不能背一个“千万行固定三层”的数字。InnoDB 官方文档把这种结构称为 B-tree；本文用 B+ 树模型解释其叶页存放记录、内部页导航的特征。[1][2]
正在绘制图示…
查看图源代码
flowchart TB
R["根页：分隔键 40"] --> I1["内部页：10 / 25"]
R --> I2["内部页：60 / 80"]
I1 --> L1["叶页：1…9"]
I1 --> L2["叶页：10…24"]
I1 --> L3["叶页：25…39"]
I2 --> L4["叶页：40…59"]
I2 --> L5["叶页：60…79"]
I2 --> L6["叶页：80…"]
L1 -. "相邻叶页" .-> L2
L2 -.-> L3
L3 -.-> L4
L4 -.-> L5
L5 -.-> L6
查 key = 27，沿树找到含 27 的叶页。查 key BETWEEN 27 AND 58，定位起点后继续走范围。范围包含全表大部分条目时，再精巧的定位也省不掉读取结果本身的成本。
索引维护也有代价。插入会修改目标页，必要时拆页；更新索引列要维护相关条目；索引占磁盘和缓存，还增加日志、备份与复制的负担。选索引的目标是给重要访问路径减负，不是让每列都有一棵树。[3][1:1]
同样叫“主键”，InnoDB 和 PostgreSQL 存的东西不同
| 关系 | MySQL 8.4 InnoDB | PostgreSQL 18 常规堆表 |
|---|---|---|
| 数据行在哪里 | 聚簇索引叶页中 | 独立堆表页中 |
| 主键索引 | 有显式主键时，以它组织聚簇索引 | 通常是独立的唯一 B-tree 索引，指向堆中元组 |
| 普通二级索引条目 | 索引键加聚簇键值，借此找整行 | 索引键加堆元组位置等索引信息 |
| 查索引后还缺列 | 通过聚簇键再找聚簇索引，俗称回表 | 去堆表取数据，并检查可见性 |
| 覆盖了所有引用列 | 有机会避免取完整行，但 MVCC 等条件仍可能要求访问聚簇记录 | 有机会 Index Only Scan，但仍依赖 visibility map，可能出现 Heap Fetches |
InnoDB 没有显式主键时，会选合适的全非空唯一索引，否则创建隐藏的聚簇索引；不是“没有主键就没有聚簇索引”。聚簇键越宽，每个二级索引里带的定位信息通常越大，这是选择短主键的一个实际理由。[4][5]
PostgreSQL 的 CLUSTER 是按某索引重排堆表的一次操作，不会把表永久变成 InnoDB 那种由主键持续维护的聚簇组织。后续写入不会自动保持那次物理顺序。[6]
正在绘制图示…
查看图源代码
flowchart LR
A["InnoDB 二级索引<br/>tenant_id → 主键 id"] --> B["聚簇索引<br/>id → 完整行"]
C["PostgreSQL B-tree<br/>tenant_id → 堆位置"] --> D["堆表<br/>数据版本与可见性"]
C --> E["列已覆盖且页 all-visible？"]
E -->|是| F["直接返回索引中数据"]
E -->|否| D
联合索引是按多个键依次排序
对 (tenant_id, created_at) 建索引，先把同一租户的条目放在一起，再按创建时间排。于是 tenant_id = 42 AND created_at >= 99000 能定位租户 42 的局部范围。只有 created_at >= 99000 时，满足条件的条目分散在不同租户区域，难以用一个连续区间圈住。[7][8]
这就是“最左前缀”有用的部分：它解释为什么前导等值条件通常有利于缩小搜索范围。它不能推出“没写第一列就绝不会用索引”，也不能推出“范围条件后面的列完全没用”。后面的列仍可能在索引中筛选，或者通过具体优化减少扫描。
查询条件，索引为 (a,b,c) | 通常怎样理解 | 还需要检查 |
|---|---|---|
a = ? AND b = ? | 定位一个很窄的前缀范围 | 选择率、是否还要回表 |
a = ? AND b >= ? AND c = ? | a 与 b 确定主范围，c 可能继续过滤 | c 是否减少扫描、只减少回表，或参与 skip scan |
b = ? | 普通前缀定位不再直接适用 | 全索引扫描、skip scan 或全表扫描的成本 |
a = ? ORDER BY b,c | 索引顺序可能省排序 | 排序方向、NULL、表达式、最终计划 |
| 三个单列索引代替联合索引 | 某些计划可组合索引 | 不能因此保证与一个联合索引等价，也未必保留排序 |
PostgreSQL 18 明确支持 B-tree skip scan：前导列不同值少时，可以对这些前导值反复定位后面的条件。MySQL 8.4 也有 skip scan，但适用查询形态和限制不同，应读各自文档，而非把一条口诀当作 SQL 标准。[8:1][9]
交互实验：联合索引边界与过滤
联合索引：哪些条目能跳过，哪些还得检查？
把整数键 (a,b) 按字典序排好：先比 a，a 相同才比 b。修改数据或条件，观察一个连续候选区间如何定位、怎样过滤，再与全表检查核对结果。
这是内存有序数组上的机制模拟，用二分查找展示边界定位；不是 B+ 树页访问或真实 MySQL 执行计划，不报告毫秒和加速倍数。
01
选一个问题 · 条件怎样圈住候选？
五个示例只修改查询条件，保留你编辑的数据。可继续微调下面的条件。
02
输入 · 数据和两列条件
此按钮会替换数据，并重置查询。
03
过程 · 先定位边界，再逐条检查
步骤 0 / 15
先按 a 升序排列，同一 a 内再按 b 升序排列。输入行号只是教学标识。定位边界后，逐条检查候选。
先定位候选区间，再逐条核对条件。
已比较 0 次 · 已检查 0 行
保留 0 行 · 过滤 0 行
展开完整边界、计数与全部条目
全部条件：a = 2 AND b >= 20
先固定 a，再用 b 的条件定位组内边界；重复的 (a,b) 会全部保留。
用于构造区间的列：a、b。区间内仍需筛选的列：无额外条件。本模型在扫描时复核全部条件。
键范围： (2, 20) ≤ (a,b)； (a,b) ≤ (2, +∞)。 ±∞ 是定位边界的标记，不是数据值。
候选位置 [起点, 终点)[?, ?)终点位置不包含在区间内
二分定位的键比较次数0仅统计已经发生的边界比较
已检查的候选条目0不与定位比较相加估算耗时
已过滤 / 已保留0 / 0每个候选仍复核全部条件
- 位置 0 · 行 #9(1, 10)边界待定位
- 位置 1 · 行 #2(1, 20)边界待定位
- 位置 2 · 行 #5(1, 30)边界待定位
- 位置 3 · 行 #4(2, 10)边界待定位
- 位置 4 · 行 #6(2, 20)边界待定位
- 位置 5 · 行 #8(2, 20)边界待定位
- 位置 6 · 行 #1(2, 30)边界待定位
- 位置 7 · 行 #11(2, 40)边界待定位
- 位置 8 · 行 #3(3, 10)边界待定位
- 位置 9 · 行 #10(3, 20)边界待定位
- 位置 10 · 行 #7(3, 30)边界待定位
- 位置 11 · 行 #12(4, 20)边界待定位
试一试：后面的列还有用吗？
用默认数据选“a 范围＋b 等值”。a ≥ 2 圈住 9 个候选，b = 20 再过滤掉 5 个，保留 4 行。换成“a 等值＋b 范围”，定位后只检查 4 个候选。选择“查重复键”，两条 (2,20) 都应返回。
示例按钮只改条件，保留你编辑的数据；要复现这些数字，先恢复默认数据与条件。试着把 ≥ 换成 >，观察边界等值条目如何被排除。
适用边界与真实数据库的区别
只处理两列非空整数、AND 连接、等值和单段范围；不解析任意 SQL，不处理 NULL、字符串排序规则、隐式转换、OR、IN、多区间、降序索引或 skip scan。同键按输入行号稳定排列，只为便于比较。
采用固定的“等值前缀＋首个范围”策略；即使闭区间上下限相同，也按范围处理，不做优化器化简。没有 a 条件时扫描整个索引，是此模型的策略，不能据此断言真实数据库不能用索引。
索引在每次输入变化后重新排序，排序与构建代价不计入查找步骤。这里不模拟回表、覆盖、索引条件下推、MVCC、页 I/O、写入维护或优化器选路，不能替代 EXPLAIN 与实测。
不操作也能读懂：索引 (a,b) 先聚拢相同 a，再排列 b；a=2 且 b≥20 可以形成局部连续区间。只有 b≥20 时，命中分散在各个 a 分组中。
机制依据：MySQL 8.4 联合索引、多列索引的范围定位。二分步骤为教学模型自行实现。
一次真实实验：先看计划形状，暂时不比较毫秒
测试表有 100,000 行，100 个租户，每行含 120 字符的 note。created_at 在这里是递增整数，仅为观察索引排序，实际业务通常用时间类型。没有目标联合索引时：
sql
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT amount FROM index_orders
WHERE tenant_id = 42 AND created_at >= 99000;实际计划中的关键行如下，省略了耗时、缓冲和显示包装：
text
Seq Scan on index_orders ... (actual rows=10.00 loops=1)
Filter: ((created_at >= 99000) AND (tenant_id = 42))
Rows Removed by Filter: 99990数据库读了这份表的候选行，再过滤掉 99,990 行。随后增加索引并对静态测试数据执行 VACUUM (ANALYZE)：
sql
CREATE INDEX idx_tenant_created
ON index_orders(tenant_id, created_at) INCLUDE(amount);
VACUUM (ANALYZE) index_orders;同一查询的实际关键输出变成：
text
Index Only Scan using idx_tenant_created on index_orders ... (actual rows=10.00 loops=1)
Index Cond: ((tenant_id = 42) AND (created_at >= 99000))
Heap Fetches: 0
Index Searches: 1tenant_id 和 created_at 是查找键，amount 是随条目携带的返回值。INCLUDE 中的列不参与这个索引的键顺序，也不参与唯一性判断。此次静态数据的可见性信息允许零次取堆；刚更新过的表即便出现 Index Only Scan，也可能有非零 Heap Fetches，所以“覆盖”不能直接等同“永远不回表”。[10][11]
把返回列换成未覆盖的 note，实际计划采用 Bitmap Index Scan 加 Bitmap Heap Scan，并访问 10 个堆块。Bitmap 路径先收集候选位置，再按堆块访问，有机会减少乱序读；它不是“索引失效”。[12]
一个特意保留的反例：表达式查询仍然可能“走索引”
sql
SELECT amount FROM index_orders WHERE tenant_id + 0 = 42;在尚未建立表达式索引时，本次 PostgreSQL 仍选择了 idx_tenant_created：
text
Index Only Scan using idx_tenant_created on index_orders ... (actual rows=1000.00 loops=1)
Filter: ((tenant_id + 0) = 42)
Rows Removed by Filter: 99000
Heap Fetches: 0它利用索引较窄、能提供返回列的优点，但没有把这个表达式变成有效的定位条件，依然检查了 100,000 个索引条目。光看到“Index”就说优化成功，会漏掉这里的大量扫描。
建立 ON index_orders((tenant_id+0)) 的表达式索引之后，计划的 Index Cond 才出现 ((tenant_id + 0) = 42)，通过 Bitmap Index Scan 找出约 1,000 行。生产中这个 +0 应先删掉；这里保留它是为了隔离表达式索引的作用。真正有价值的场景是按 lower(email) 搜索等无法简单删除的表达式；创建表达式索引还会增加插入及相关更新的计算成本。[13]
没有第一列条件，也不代表用不了联合索引
sql
SELECT amount FROM index_orders WHERE created_at = 99942;本次结果：
text
Index Only Scan using idx_tenant_created on index_orders ... (actual rows=1.00 loops=1)
Index Cond: (created_at = 99942)
Heap Fetches: 0
Index Searches: 101结合 PostgreSQL 18 的 skip scan 文档，可以把这一计划解释为针对缺失前导条件进行多次索引定位，而不是普通单次前缀搜索。100 个租户的分布使这条路径有吸引力。把租户数、命中比例或数据宽度换掉，优化器可能做出不同选择；这里没有宣称所有“只查第二列”的查询都快。[8:2]
最后，SELECT note ... WHERE tenant_id >= 0 实际匹配全部 100,000 行，优化器选了 Seq Scan。索引在，谓词也能用于 B-tree，但遍历索引再取全表数据没有明显好处。顺序扫描在这种情形下是合理选项。
覆盖索引和索引下推分别省哪一步
覆盖索引解决“查询所需的数据是否都已在索引里”。索引条件下推（ICP）解决“必须读取完整行之前，能否先在存储引擎用索引里的列排除一些候选”。MySQL 8.4 InnoDB 的 ICP 用于适合的二级索引访问；读取聚簇索引时完整记录已经在手，不能套用同样的节省逻辑。[14]
例如联合索引为 (zipcode, lastname)，条件为 zipcode='10000' AND lastname LIKE '%smith%'。邮编用于定位一个范围，lastname 的前导通配符通常不能继续形成普通 B-tree 前缀范围，但索引条目里有姓氏，存储引擎可先判断包含关系，仅为符合者取完整行。这里是官方机制的说明性场景，未在本次环境执行。
| 计划中的线索 | 通常意味着什么 | 不能据此推出什么 |
|---|---|---|
MySQL Using index | 可以从索引满足所需列，常用于覆盖访问 | 总扫描量一定小 |
MySQL Using index condition | 使用 ICP 在索引层测试条件 | 与覆盖索引同义 |
PostgreSQL Index Cond | 可交给相应索引访问路径的条件 | 每个条件都同样缩小连续扫描区间 |
PostgreSQL Filter | 在该节点对候选结果继续过滤 | 一定没有使用任何索引 |
PostgreSQL Heap Fetches | Index Only Scan 中仍访问堆的次数 | 只有零才叫使用索引 |
MySQL 的 Extra 文本和 PostgreSQL 的节点不是统一接口；要按对应引擎的执行计划文档读。[15][16]
少背“失效清单”，多问四个具体问题
- 条件与索引表达式、类型和排序规则匹配吗？
lower(name)不自动等于普通name索引；隐式类型转换可能改变可用访问路径。MySQL 对字符串列与数字比较的规则有明确限制，不能把任何转换都笼统判成“失效”。[13:1][17] - 选择率合适吗？选择率可理解为符合条件的比例，基数常指不同值数量。一个状态只有三个取值，并不等于索引必然无用：其中一个值只占万分之一时，查它仍可能很有价值。统计信息过旧或列间相关性未建模，会让估计偏离实际。[18]
- 扫描后还要做什么？大量回表、连接、排序、聚合、网络传输，都可能比定位本身昂贵。
SELECT *容易破坏原本可覆盖的查询，宽索引则把成本转移到写入与存储。 - 数据库是否支持更合适的索引？PostgreSQL 可为固定子集建部分索引，但查询条件必须让规划器识别出它落在该子集内；全文检索或任意子串搜索也不该总强求普通 B-tree。[19]
IS NULL、OR、不等于、函数、前导通配符，都不能脱离引擎、索引类型和数据分布，单独宣判“索引失效”。即使某写法不能做窄范围定位，优化器也可能选扫描整个覆盖索引。应该描述丢掉的是哪一种能力。
EXPLAIN 要从估计读到实际
普通 EXPLAIN 展示计划，不执行查询主体；PostgreSQL EXPLAIN ANALYZE 会真的执行。对 UPDATE、DELETE 使用前必须准备隔离环境或明确的回滚策略；回滚也不能抹掉所有外部副作用，不能在不熟悉的生产语句上试。MySQL EXPLAIN ANALYZE 同样会执行其支持的语句，格式及支持范围应查 8.4 文档。[16:1][20]
| 观察项 | PostgreSQL 常见字段 | MySQL 8.4 常见字段或 TREE 信息 | 读到异常后查什么 |
|---|---|---|---|
| 访问方式 | Seq/Index/Bitmap Scan | type、key、迭代器节点 | 是否在做全量扫描 |
| 估计行数 | rows | rows、filtered | 统计、参数分布、相关性 |
| 实际工作量 | actual rows、loops | ANALYZE 的 rows、loops | 循环次数是否放大；多次执行时行数通常为每次平均 |
| 后置过滤 | Filter、Rows Removed | attached condition 等 | 是否能更早排除 |
| 数据访问 | BUFFERS、Heap Fetches | 结合 Performance Schema 等观察 | 读了多少页，是否有不必要回表 |
| 排序和临时工作 | Sort、磁盘/内存信息 | filesort、temporary 等 | ORDER BY、聚合和内存是否合适 |
成本单位不是毫秒。节点时间通常含子节点工作，也不能把父子耗时简单相加。本篇实验用 TIMING OFF 避免逐节点计时开销，只把行数、扫描形状和取堆次数当作主要证据，没有拿单次毫秒值宣称“快几百倍”。数据是均匀人工分布、临时表、单用户模式；它不能代表真实多会话、持久表、冷缓存或生产硬件。[16:2]
真正值得保留的索引，应有明确受益查询、可接受的写入代价，以及能复测的计划证据。否则每加一个索引，只是多了一份需要一直维护的数据。
参考资料
完整复现材料
实际环境：PostgreSQL 18.6，Linux/amd64，官方源码 SHA-256 校验后本地构建，single-user 离线模式。没有启动网络服务，没有测试 MySQL。single-user 无法提供真实并发锁行为，限制见官方说明。测试使用临时表，会话退出即清理。
需要 Python 3 与 PostgreSQL 18.6 的 postgres/initdb。将二进制路径改为自己的位置；必须是新建、可丢弃、没有其他进程占用的数据目录：
sh
export POSTGRES=/path/to/postgresql-18.6/bin/postgres
export PGDATA="$(mktemp -d)/data"
/path/to/postgresql-18.6/bin/initdb -D "$PGDATA" -A trust --no-locale -E UTF8
python3 run_single.py indexes.sql计划选择、估计行数和计时可能随统计采样、硬件或版本变化。此脚本不强制关闭顺序扫描，保留优化器自己的选择。适配脚本仅适用于本篇固定输入，不是通用 SQL 解析器。
indexes.sql
代码块 · 36 行 · 点击收起 / 展开
sql
\set ON_ERROR_STOP on
CREATE TEMP TABLE index_orders(
id integer PRIMARY KEY,
tenant_id integer NOT NULL,
created_at integer NOT NULL,
amount integer NOT NULL,
note text NOT NULL
);
INSERT INTO index_orders
SELECT g,g%100,g,g%1000,repeat('x',120) FROM generate_series(1,100000) g;
ANALYZE index_orders;
\echo no-index
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT amount FROM index_orders WHERE tenant_id=42 AND created_at>=99000;
CREATE INDEX idx_tenant_created ON index_orders(tenant_id,created_at) INCLUDE(amount);
VACUUM (ANALYZE) index_orders;
\echo covering
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT amount FROM index_orders WHERE tenant_id=42 AND created_at>=99000;
\echo noncovering
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT note FROM index_orders WHERE tenant_id=42 AND created_at>=99000;
\echo function-without-expression-index
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT amount FROM index_orders WHERE tenant_id+0=42;
CREATE INDEX idx_tenant_expr ON index_orders((tenant_id+0));
ANALYZE index_orders;
\echo expression-index
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT amount FROM index_orders WHERE tenant_id+0=42;
\echo left-prefix-absent
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT amount FROM index_orders WHERE created_at=99942;
\echo all-rows
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT note FROM index_orders WHERE tenant_id>=0;run_single.py
代码块 · 42 行 · 点击收起 / 展开
python
"""Run the deliberately simple fixtures shipped with these articles.
Input: ordinary SQL and optional psql \\set/\\pset/\\echo directives.
This is a fixture adapter, not a general SQL parser. No fixture may contain
semicolons inside quoted values, comments, function bodies, or identifiers.
"""
import os
from pathlib import Path
import re
import subprocess
import sys
sql = Path(sys.argv[1]).read_text()
lines = []
for line in sql.splitlines():
if line.startswith('\\echo '):
label = line[len('\\echo '):].replace("'", "''")
lines.append("SELECT '" + label + "';")
elif line.startswith('\\'):
continue
else:
lines.append(line)
commands = []
for part in '\n'.join(lines).split(';'):
part = part.strip()
if not part:
continue
# COPY gives legible values; it does not change the SELECT's semantics.
if re.match(r'(?i)^(SELECT|WITH)\b', part):
part = "COPY (" + part + ") TO STDOUT WITH (DELIMITER '|', NULL 'NULL')"
commands.append(part + ';\n\n')
run = subprocess.run(
[os.environ.get('POSTGRES', 'postgres'), '--single', '-j',
'-D', os.environ['PGDATA'], 'postgres'],
input=''.join(commands), text=True, capture_output=True)
print(run.stdout, end='')
print(run.stderr, end='', file=sys.stderr)
if run.returncode:
sys.exit(run.returncode)
# The stand-alone backend may recover from a SQL error and exit zero.
# An ERROR is still a failed fixture; expected failures are checked outside.
if re.search(r'\b(ERROR|FATAL|PANIC):', run.stderr):
sys.exit(1)