索引保存额外的数据组织方式，使数据库更快找到候选行；它同时增加空间和写入维护成本。优化的目标是完成具体查询所需的工作量，而不是让所有 SQL 都出现 Index Scan。小表全扫、大范围聚合走顺序扫描，都可能比随机访问索引更便宜。
建一个能观察的样本
以下 PostgreSQL 实验只创建练习表。使用独立数据库，执行计划和耗时随机器、缓存与统计信息变化，不预设固定数字。
CREATE TABLE query_lab (
id bigint PRIMARY KEY,
owner_id integer NOT NULL,
state text NOT NULL,
created_at timestamptz NOT NULL
);
INSERT INTO query_lab
SELECT g, g % 1000, CASE WHEN g % 10 = 0 THEN 'open' ELSE 'done' END,
timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second'
FROM generate_series(1, 200000) AS g;
ANALYZE query_lab;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at FROM query_lab
WHERE owner_id = 42 ORDER BY created_at DESC, id DESC LIMIT 20;
CREATE INDEX query_lab_owner_time
ON query_lab(owner_id, created_at DESC, id DESC);
ANALYZE query_lab;
-- 再运行相同 EXPLAIN，对比扫描行数、排序节点和缓冲区访问。
复合索引先按 owner_id 缩小范围，再匹配排序，让数据库更有机会取得前 20 条就停止。是否这样执行仍由实际计划决定。仅为低基数 state 建一个普通索引，查询覆盖大部分行时可能没有收益；若固定业务只查很少的 open 记录，可以评估带 WHERE 条件的部分索引。
先分清估计与实测
EXPLAIN 展示估计计划；EXPLAIN ANALYZE 真正执行语句。后者用于 UPDATE、DELETE、带副作用的函数时会产生实际效果，不能随意在生产运行。cost 是规划器的相对成本单位，不是毫秒。
看计划时先找估计行数与实际行数差距，再看过滤掉多少行、循环次数、排序是否落盘和缓冲区读取。一条内层查询每次只花一点时间，乘上十万次 loops 后也可能成为主要瓶颈。统计信息过旧、列之间相关性、隐式类型转换、对索引列套函数，都可能导致计划偏离预期。
Index Only Scan 不保证完全不访问表，PostgreSQL 还需检查可见性信息。为了“覆盖所有查询”把很多大字段塞进索引，会推高写放大和缓存压力。先量化最重要的查询，再增加索引。
深分页需要改变读取方式
OFFSET 100000 LIMIT 20 仍需跳过很多候选行。若界面只需连续向后阅读，可把上一页最后一项作为游标：
SELECT id, created_at FROM query_lab
WHERE owner_id = 42
AND (created_at, id) < (timestamptz '2026-01-03 00:00:00+00', 172800)
ORDER BY created_at DESC, id DESC
LIMIT 20;
比较按元组字典序进行，两个排序项方向必须与条件一致。游标包含时间和唯一 ID，避免同一时间多条记录被遗漏。它让续查更便宜，但不会自动提供“阅读期间所有页面同一快照”，也不适合任意跳到第 5000 页。
练习与面试自查
分别尝试删去 owner 条件、改变排序方向、查询所有 owner。保留修改前后的计划，并解释复合索引为什么不一定适合每一种查询。补一个会显著影响写入的批量插入测量，才能评价索引的完整代价。
- 为什么“索引失效”不是足够精确的诊断？应查看哪些证据？
- 为什么数据库认为顺序扫描更便宜时，强行使用索引可能变慢？
- 索引能改善查询，为什么还可能加重锁等待和写入延迟？