半月前我被人拉去看一条订单查询的慢SQL单看执行计划其实不算难看索引确实命中了过滤条件也走了可整体就是磨蹭了大几百毫秒。翻完字段才知道真正吃掉时间的不是锁、不是排序而是每条满足条件的记录都得到堆表里重新“取”一次。这个问题正是覆盖索引能解决的。很多同学聊起覆盖索引第一反应是“把所有查询字段塞进一个复合索引”这话对了一半在 PostgreSQL 里还容易踩坑。PostgreSQL 从 11 开始提供了INCLUDE子句专门用来把“不需要参与索引键匹配、但查询要返回”的列放进索引里。今天就把我平时用覆盖索引的思路、建法、验证手段和坑一次性说清楚适合正在做报表查询优化、订单列表加速或者纯粹想把EXPLAIN看得更明白的朋友。1. 什么是覆盖索引为什么它能稳吃“Index Only Scan”1.1 一个索引扫描为什么还会慢先说一个我反复见到的现场。某业务表结构大概是这样的订单号、用户ID、金额、状态、创建时间。开发同学给user_id建了普通B树索引查询也走索引了可一旦某个用户订单很多查询时间就明显上涨。原因是索引扫描分两步第一步沿着B树索引找到符合user_id 某值的叶子页取出每行对应的TID行指针即页号和行号。第二步拿着TID回到表堆heap里按物理位置读取整行数据再从这行里把order_no、amount、status等字段挑出来返回。第二步就是“回表”。如果某个用户有 3000 个订单查询就要随机访问 3000 个堆页。哪怕内存足够、页都在缓存里CPU 和内存总线也得来回倒腾几千次慢是必然的。1.2 覆盖索引解决了什么覆盖索引的思路非常直接如果索引本身已经把查询需要的列都保存了一份数据库就没必要再回到堆表取数据可以直接从索引页返回结果。这时执行计划会从Index Scan变成Index Only Scan也就是“索引只读扫描”。注意这个词“Only”指的不是“只扫了一点索引”而是完全不用访问表堆。索引页里存了什么B树索引页里除了索引键值还能通过INCLUDE存额外列的副本。这些额外列不会参与索引的排序、查找和去重但它们确实被物理保存在索引元组里。查询需要它们时直接从索引元组里取。听起来很美好但也有一个隐藏前提为了确定索引里的行是否对当前事务可见数据库需要看表页的可见性位图visibility map。如果表页还没有被VACUUM标记为“全页可见”那么即使有了覆盖索引数据库也得去堆表核对一下该行的可见性。这个细节我们在后面的Heap Fetches部分细讲。1.3 和 MySQL 里“硬塞复合索引”的思路差异MySQL 的老经验是查询要哪些列就把这些列全塞进二级索引里形成覆盖索引。这套思路在 MySQL 没问题但在 PostgreSQL 里直接搬容易出事。PostgreSQL 的复合索引要求你明确区分“键列”和“附带列”。键列决定索引的排序和搜索附带列INCLUDE只负责“扛数据”。比如CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC) INCLUDE (order_no, amount, status);这里user_id、created_at是真正的索引键支持WHERE user_id ?条件和ORDER BY created_at DESC的排序order_no、amount、status则是“搭车的乘客”纯粹为了省略回表。为什么 PostgreSQL 要拆成这两类因为如果你把三个返回字段都当键列那么索引的键比较逻辑就会更复杂插入和更新时需要比较的字段更多索引页膨胀得也快。而作为INCLUDE列它们不参与键比较插入时只负责“带上副本”开销小得多同时仍然享受覆盖效果。这也给了我一个很重要的思路覆盖索引不是复合索引的平替它是复合索引的补充手段。能用键列解决的过滤排序仍然要放键列返回列才考虑往INCLUDE里放。2. 覆盖索引的创建姿势与验证方法2.1 核心语法INCLUDE 子句到底怎么写语法本身不难难的是“哪些列放键、哪些列放 INCLUDE”。拿一个订单中心表的简化场景举例CREATE TABLE t_order_center ( id BIGSERIAL PRIMARY KEY, user_id INT NOT NULL, order_no VARCHAR(32), amount NUMERIC(12,2), status SMALLINT, created_at TIMESTAMPTZ, channel VARCHAR(20), remark TEXT );有一条高频查询是SELECT order_no, amount, status FROM t_order_center WHERE user_id 10086 ORDER BY created_at DESC LIMIT 10;优化前只建一个user_id普通索引执行计划大概率是Index ScanLIMIT 10只是减少了最终返回的行数但为了拿到order_no、amount、status每一行都免不了回表。优化做法CREATE INDEX idx_cover_user_created ON t_order_center (user_id, created_at DESC) INCLUDE (order_no, amount, status);几个细节created_at DESC这种写法是允许的B树索引天然支持逆序扫描如果不显式写 DESC执行计划也能通过倒序扫描达到效果但不建议依赖。LIMIT 10对顺序很重要。因为索引已经按created_at DESC排序数据库只需要找到user_id 10086的第一个叶子元组向后读 10 条即可整个过程不需要额外排序。如果查询里还有status过滤条件比如AND status 1那最好把status挪到键列里否则它只是一个返回列不能用来快速过滤。2.2 怎么用 EXPLAIN 验证有没有真正命中覆盖建好索引后先别急着上生产老老实实跑一遍验证EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT order_no, amount, status FROM t_order_center WHERE user_id 10086 ORDER BY created_at DESC LIMIT 10;如果索引真正生效你会看到类似这样的输出Limit (cost0.43..1.14 rows10 width42) (actual time0.018..0.031 rows10 loops1) - Index Only Scan using idx_cover_user_created on t_order_center (cost0.43..8.46 rows75 width42) (actual time0.014..0.024 rows10 loops1) Index Cond: (user_id 10086) Heap Fetches: 0 Buffers: shared hit4判断标准有三个节点名称必须包含Index Only Scan不能是Index Scan。Heap Fetches如果为 0说明确认完全没回表。Buffers的输出很小说明内存里只用了少量索引页。如果你看到Index Scan不要急着骂数据库99% 是查询里用了没有放进索引的列。比如查询里多了channel而channel不在索引键也不在INCLUDE里PostgreSQL 就只能回表补齐它。2.3 别忽略 BUFFERS判断代价的关键指标EXPLAIN (ANALYZE)已经很好用但做性能对比时我强烈建议加上BUFFERS。这个参数会告诉你每个节点消耗了多少缓存页和磁盘页。回表和全索引扫描的最大区别并不总是“耗时”而在于缓存命中率。如果所有堆表页都恰好留在内存里回表可能也就几十毫秒。可一旦数据量超过内存、冷页需要从磁盘读取回表的代价会成倍放大。Buffers: shared hit4意味着这次查询只打了 4 个索引页且全在共享缓冲区里。如果换成普通索引扫描 75 行可能要额外读 75 个堆页即使全命中缓存也变成了“读取 75 个页 vs 读取 4 个页”的差距。等数据量再翻十倍用BUFFERS观察会更直观覆盖索引的页访问数是与返回行数挂钩回表方案的页访问数与扫描行数挂钩。3. 什么场景适合覆盖索引什么场景不要碰3.1 高频固定列查询最适合的第一战场覆盖索引最常见的受益场景是“同一个查询反复出现、返回列固定、每次命中行数还不少”。判断标准很简单去pg_stat_statements里捞 Top SQL找那种calls很高、mean_time还能接受但total_time占比较大的查询。比如订单列表、用户消息列表、对账明细查询它们的特点是查询条件相对固定集中在user_id、status、created_at这类字段。返回的列基本固定而且只是一行记录里的一小部分字段不是SELECT *。单个用户的数据量会随着时间增长命中行数可能从 10 涨到几千。这种场景上覆盖索引收益非常直接。命中 3000 行时普通索引回表 3000 次覆盖索引只需要顺序读取索引里对应的元组。回表是随机小 IO索引连续读是顺序 IO二者差距悬殊。3.2 聚合/计数查询也可以吃到红利除了单行明细聚合查询同样值得考虑。比如运营后台要算“每个用户已支付订单的金额总和”SELECT user_id, SUM(amount) FROM t_order_center WHERE status 2 GROUP BY user_id;如果只建status普通索引数据库找到所有status 2的行指针后还要回表取amount再分组求和。建一个部分索引加上覆盖效果能让聚合计算直接在索引层完成CREATE INDEX idx_orders_paid_user_amount ON t_order_center (user_id, amount) WHERE status 2;这里的status 2已经写死在部分索引里索引里只存“已支付”的订单amount作为键列排第二可以直接形成分组的关键信息。如果不想把amount当键列也可以放到INCLUDE里。两种做法都可行区别在于放键列可以支持更多的排序和范围条件放INCLUDE能减少索引维护成本。我的经验是优先把过滤、分组、排序字段放在键列其余需要返回的列放INCLUDE。3.3 怎么决定哪些列放 INCLUDE这里我有一套自己的判断顺序供参考过滤列WHERE等值条件和范围条件优先放键列因为它们需要参与 B-tree 定位。排序列ORDER BY对应列放键列而且顺序和方向要匹配查询写法。分组列GROUP BY对应列考虑放键列因为分组本质上依赖相同键值相邻排列。返回列SELECT列表里的其他字段放INCLUDE。永远不要放索引的列更新极频繁的列、超长文本列、JSONB 列。还用前面的例子说明user_id过滤 → 键列created_at排序 → 键列order_no、amount、status只是返回 →INCLUDE列。这样做出的索引既小又能精准满足查询。对于INCLUDE列的数量我也给出一个经验性建议覆盖索引的总宽度最好控制在 40 字节以内最多也别超过 100 字节。一旦超过索引页里能存放的元组数量骤降索引体积会膨胀缓存命中率反而下降覆盖效果会被抵消。3.4 覆盖索引的危险边缘有些场景我强烈不建议上覆盖索引写多读少的表每笔 INSERT/UPDATE 都要维护额外的索引页副本写入放大很明显。如果一张表每秒几千次 UPDATE再加一个覆盖索引IO 开销会让你得不偿失。表字段特别宽查询几乎返回全部列比如SELECT *覆盖索引需要把整行几乎所有字段塞进索引和复制一张表没什么区别。这时不如优化应用层返回字段或者考虑物化视图。冷查询一天只跑三四次的统计任务犯不着为它维护一个长期索引。索引本身也是存储和写入成本。4. 踩坑记录覆盖索引不是普通索引的替身4.1 INCLUDE 列不能参与过滤排序别误导这是新手最容易踩的坑。很多人看到INCLUDE (status)就以为WHERE status 1能用到这个索引看到INCLUDE (created_at)就以为能直接用它排序。事实是INCLUDE列只是“顺路保存”不会进入 B-tree 的键值比较逻辑。你想在WHERE里过滤status、在ORDER BY里按created_at排序就必须把这两个字段放在索引键列中而不是INCLUDE。我之前见过一个案例开发同学把created_at放进了INCLUDE查询语句里又写了ORDER BY created_at DESC结果执行计划出现了显式的Sort节点所有数据先在内存排序再返回。改成把created_at提到键列后Sort节点消失查询直接从索引顺序读取耗时才真正降下来。4.2 把大字段塞进 INCLUDE 等于自爆覆盖索引很忌讳大字段。常见的是把remark、description、content这类TEXT字段塞进INCLUDE想着“这条查询需要返回这个列”。问题在于索引页和堆页一样也是有固定容量的默认 8KB 一页。一个TEXT字段如果平均几百字节一个 8KB 页只能存几十个索引元组。原本一个覆盖索引页能存放上千个元组现在几十个就满了。查询扫描 3000 行意味着要连续读几十上百个索引页比回表还慢。正确的做法是把这类大字段排除在覆盖索引之外。如果业务确实非常需要考虑返回时延迟关联或者把大字段拆分到另一张低频访问的表里。实在要放先算算avg(pg_column_size(remark))估算新增索引体积是否可控。SELECT avg(pg_column_size(remark)) FROM t_order_center;如果你估算出来覆盖索引新增体积可能超过原表体积的 20%~30%就要反复斟酌别盲目上。4.3 频繁更新字段会破坏 HOT 更新PostgreSQL 有一个优化叫 HOTHeap-Only Tuple简单说如果一个表行更新后所有索引键都没有发生变化数据库可以在同一个堆页内部完成版本更新不需要维护任何索引。但如果你把某个字段放到了INCLUDE里而这个字段经常被 UPDATE比如订单状态status从 1 变成 2、又变成 3那问题就来了即使INCLUDE列不参与索引键比较索引元组里仍然保存了它的副本。更新后这个索引元组指向的新行版本与旧版本不一致数据库为了保持索引与表行一致必须更新对应索引元组HOT 更新路径就走不通了。结果就是一次简单的状态更新除了更新表堆还要额外更新覆盖索引。如果订单状态变更频率很高这个额外开销会直接压到写入链路上。我的建议是把“状态”这类会频繁变化的字段从覆盖索引里拿出来。如果真的既要状态覆盖、又要高频更新那就接受写入成本上升或者考虑用部分索引只覆盖一部分状态比如只覆盖“已支付”状态的订单。4.4 主键、唯一约束别指望用 INCLUDE 补复合主键的列全部是键列PostgreSQL 的PRIMARY KEY不会让你加INCLUDE子句。如果你建了复合主键(user_id, created_at)又想在这之外覆盖返回字段不能直接在主键上做文章必须另建一个普通索引。唯一索引倒是可以配合INCLUDE但要注意语义INCLUDE列不参与唯一性判断。CREATE UNIQUE INDEX idx_uq_user_created ON t_order_center (user_id, created_at) INCLUDE (order_no);这个索引只保证(user_id, created_at)组合唯一不保证order_no唯一。如果你误解成“组合唯一且 order_no 也唯一”后续业务就会在不经意间插入重复order_no。唯一性校验远比索引优化可怕别在这里省事。5. 实战记录某订单列表查询从 15ms 降到 1.8ms5.1 模拟订单表与慢查询重新现场为了写这节我在本地重新搭了一套数据模型就按上文的t_order_center表结构来灌了大约 500 万行模拟订单数据。灌数据时顺便用random()分布用户ID模拟单用户几千条订单的情况。高频查询模拟为运营后台的“某用户最近订单流水”SELECT order_no, amount, status FROM t_order_center WHERE user_id 8848 ORDER BY created_at DESC LIMIT 20;先只建user_id普通索引跑 5 次取平均值。结果相当凑合执行时间在 13ms~17ms 之间波动。执行计划里看到了标准的Index Scan using idx_user_id on t_order_center Index Cond: (user_id 8848) Sort (actual time... rows...)这里有两点值得注意计划里有Sort节点。普通(user_id)索引只能保证user_id有序不能保证created_at有序所以排序必须另做。虽然 LIMIT 20但排序前需要把该用户所有匹配行都收集起来然后整体排序再取前 20。Sort的代价与订单总数强相关。5.2 优化前后执行计划对比接着建覆盖索引CREATE INDEX idx_cover_user_created ON t_order_center (user_id, created_at DESC) INCLUDE (order_no, amount, status);再跑同一个查询这次执行计划变成了Limit (actual time0.011..0.020 rows20 loops1) - Index Only Scan using idx_cover_user_created on t_order_center (actual time0.009..0.015 rows20 loops1) Index Cond: (user_id 8848) Heap Fetches: 0 Buffers: shared hit3放大看细节节点从Index Scan变成Index Only ScanHeap Fetches: 0证明全部数据来自索引页。Sort节点消失。索引键里已经有created_at DESC数据库可以直接倒序读取不需要额外排序。Buffers: shared hit3意味着这次查询只用了 3 个索引页全部内存命中。我分别跑了多次优化前平均约 15ms优化后平均约 1.8ms。数据量更大的场景下差距会更夸张。5.3 验证写入侧开销与长期稳定性覆盖索引不是白送的写入侧确实有额外成本。我在同一张表上做过对比只有主键和user_id普通索引时批量插入 10 万行耗时约 2.3 秒加上覆盖索引后同样插入 10 万行耗时约 2.9 秒涨幅大概 26%。这个开销在插入密集型场景里不能无视。如果你做的是流水账类型业务每秒插入几千条再加一两个覆盖索引写入链路的 CPU 和 IO 都会增加。我的习惯是先评估这张表的“读写比”如果读请求占总请求的比例明显超过 80%覆盖索引的收益通常大于代价如果读写接近五五开必须压测后再上。另外还有一个容易忽略的稳定性问题覆盖索引长期有效的前提是可见性位图要尽量完整。如果一个表经常被 UPDATE或者一直没有跑VACUUM即使查询写的是Index Only Scan你还是会在执行计划里看到不小的Heap Fetches数值。我见过一张更新频繁的表覆盖索引建完后Heap Fetches长期居高不下性能并没有想象中那么好。排查方法很简单每次EXPLAIN (ANALYZE, BUFFERS)时顺手看Heap Fetches一旦数值持续增长VACUUM (VERBOSE) t_order_center;再跑一次看看是否回落。日常维护里给这种表设置合理的autovacuum参数比事后手动VACUUM更有用。在真实环境里做覆盖索引前我还要提醒你三件事第一先把慢查询从pg_stat_statements里捞出来确认“值得优化”的是哪些。不要看着统计页觉得某个查询不太对就顺手加一个覆盖索引。我见过不少团队为了优化一个每天只跑三五次的报表造了一个巨大的覆盖索引结果主表的写入量上去了收益却微乎其微。第二凡是覆盖索引的效果一律用EXPLAIN (ANALYZE, BUFFERS)验证不要只看“有没有 Index Only Scan”这几个字。有些场景里虽然显示的是 Index Only Scan但Heap Fetches很高说明可见性位图没跟上实际性能依然不理想。第三也是我最想强调的覆盖索引是个很锋利的工具但它不是万能钥匙。适合它的场景永远是“高频读、固定返回列、命中行数多、写入压力可控”这四者的交集。我自己的习惯是能少建一个索引就少建一个能用部分索引减小体积就更倾向于部分索引。数据库优化的终点不是堆索引而是让每一条慢查询都有明确的目标和执行计划。