YEN HARVEY
cd ../articles

后端/性能

我加了索引，为什么还是慢

6 min read
一本翻开的字典，按拼音排得整整齐齐；旁边一摞没有目录、胡乱堆着的散页。
一本翻开的字典，按拼音排得整整齐齐；旁边一摞没有目录、胡乱堆着的散页。

ACGHub 的数据慢慢涨起来之后，帖子列表第一个开始变慢。几千条的时候没感觉，到几万条，翻第一页都要等上一下。

我当时的反应特别「直觉」：哪个列查得慢，就给哪个列加索引呗。加完一测——几乎没变化。我盯着那条还是要跑大半秒的查询，第一次意识到：我对「索引」这东西的想象，可能从一开始就是错的。

ACGHub 用的是 PostgreSQL，下面的 EXPLAIN 都是 pg 的计划树，不是 MySQL 那套 type / key 表格。

慢查询长什么样

列表的查询很普通，就是按分类取最新的一页：

SELECT id, title, author_id, created_at
FROM posts
WHERE status = 'published' AND category_id = 12
ORDER BY created_at DESC
LIMIT 20;

数据少的时候它很快，数据一多就肉眼可见地拖。我学会的第一招是别瞎猜，先用 看它到底怎么跑的：

EXPLAIN ANALYZE SELECT ... ;

Limit
  ->  Sort
        Sort Key: created_at DESC
        ->  Seq Scan on posts  (rows=48213)
              Filter: (status = 'published' AND category_id = 12)

两个信号当时我还看不懂，但现在知道它们都在喊救命：

  • Seq Scan on posts：全表顺序扫，四万多行一行没漏地扫了一遍，没用上任何索引；
  • 上面那个 Sort 节点：扫完还不算完，为了 ORDER BY created_at 又重排了一遍。

四万行里挑出符合条件的，再整个排序，只为了取最前面的 20 条。慢得有道理。

我以为索引是个哈希表

WHERE 里有 category_id，那就给它来一个：

CREATE INDEX idx_category ON posts (category_id);

加之前我心里其实挺有底的。要问我「索引是个啥」，我会脱口而出：哈希表呗——category_id 当 key，值映射到行的位置，O(1) 命中，能不快吗？

可再 EXPLAIN，结果没变成我想的样子：

Limit
  ->  Sort
        Sort Key: created_at DESC
        ->  Index Scan using idx_category on posts  (rows=600)
              Index Cond: (category_id = 12)

Seq Scan 变成了 Index Scan，扫描行数从四万掉到几百——这一步是真有用。可那个 Sort 节点还在，查询整体还是没快多少。

我卡在这儿：索引明明用上了（计划里写着 Index Scan using idx_category），怎么还在排序？

更怪的是，我自己心里其实有另一半答案——直觉上我隐约知道，按 category 定位完，那一批行还得再按时间排一下。可这跟「哈希表 O(1)」是矛盾的：真哈希表哪有什么「定位完一段、段内再排序」？我嘴上说的模型，和我心里的直觉，根本对不上。

是这个 Sort 节点逼我把两边对质的。

哈希表的死穴：它给不了顺序

把这俩对质完，结论很干脆：PostgreSQL 默认建的索引根本不是哈希表，是 B 树（btree）。

为什么不默认用哈希？哈希表确实能 O(1) 点命中，但它有个死穴：一散列，就把顺序彻底打乱了。 而数据库时时刻刻在要顺序——

  • ORDER BY created_at（排序）；
  • WHERE created_at > '2023-01-01'、BETWEEN（范围）；
  • LIKE 'abc%'（前缀）；
  • 取最新、取最大（MIN / MAX）。

这些全建立在「数据是有序的」之上，哈希表一个都干不了。pg 其实真的提供哈希索引（CREATE INDEX ... USING hash），但它只认 =——范围、排序、前缀一概不管。这反而把哈希的死穴摆得明明白白：它拿顺序换了点查的速度。所以默认用的是 ：一份始终按索引列排好序的数据副本，叶子还串成有序链表，既能快速定位、又能顺着扫。

索引不是给值打的标记，是另一份排好序的数据。查询快不快，取决于它要的顺序，跟这份副本排好的顺序对不对得上。

换个类比就更直白了：索引像一本字典。 字典按拼音排好，所以你按拼音查飞快；可要让你按笔画查同一本字典呢？这份按拼音排的顺序一点忙都帮不上。哈希表更像是把字典每个字撕下来扔进一个大箱子——给你某个字你能瞬间摸到，但你永远别想「按顺序翻一页」。

看懂那个 Sort 节点

回到我那条查询。idx_category 这份副本是只按 category_id 排的，叶子摊开大概是这样：

category_idcreated_at（这一列是乱的！）
122023-03-01
122023-06-20
122023-01-09
122023-05-11
……

WHERE category_id = 12 靠它一步定位、缩到几百行——这步快了。可接下来 ORDER BY created_at DESC，pg 发现这几百行的 created_at 压根没排过，只能自己再排一遍，于是计划里多出那个 Sort 节点（量大时还会变成 Sort Method: external merge Disk，落到磁盘上排，更慢）。

所以单列索引只解决了一半：定位快了，排序还得现做。这正是我当时那半个对的直觉——只是我没把它和「哈希表」那半个错的模型对上账。

复合索引：让顺序对上

解法是让那份副本连排序也一起排好。建一个把两列放一起的 ：

CREATE INDEX idx_cat_created ON posts (category_id, created_at);

这份新副本先按 category_id 排，category_id 相同的再按 created_at 排。叶子摊开是这样：

category_idcreated_at（同一 category 内，有序！）
122023-01-09
122023-03-01
122023-05-11
122023-06-20（最新，倒扫从这取）
……

于是 WHERE category_id = 12 定位到这一段后，段内的 created_at 本来就排好了，pg 顺着倒扫（backward scan）20 条直接就是答案。那个烦人的 Sort 节点消失了：

Limit
  ->  Index Scan Backward using idx_cat_created on posts  (rows=20)
        Index Cond: (category_id = 12)

扫描行数从几百进一步掉到 20——它只读了真正要返回的那 20 行。这才是我一开始想要的效果。

顺带搞懂「回堆」和覆盖索引

还有个细节，是我搞懂 B 树之后才串起来的。

和 MySQL/InnoDB 不一样，PostgreSQL 没有聚簇索引——它不把表本身做成一棵按主键排的树。pg 的行就松散地堆在堆表（heap）里，索引的叶子存的是指向堆里物理位置的指针。所以走 Index Scan 定位到 20 条之后，还得拿着指针回堆里，把 title、author_id 这些列捞出来——这一步就是「回堆」。

如果某个高频查询要的列不多，可以把它们也带进索引，让查询要的列全在索引里，连回堆都省了——pg 会走 Index Only Scan，这就是 。pg 11 起可以用 INCLUDE 把额外列挂到叶子上（只为取值、不参与排序）：

-- 查询只要 id / title，用 INCLUDE 带进索引 → 免回堆
CREATE INDEX idx_cover ON posts (category_id, created_at) INCLUDE (id, title);

不过 pg 的 Index Only Scan 还得看可见性（visibility map）：表刚被大量更新、VACUUM 没跟上时，它仍可能退回去访问堆。而且列表通常要返回一堆字段，全塞进索引会很臃肿，所以这招更适合「字段很窄但调用极频繁」的查询，别无脑全覆盖。

索引不是免费的

最后这点得提，免得从一个极端滑到另一个——看见慢就疯狂加索引。

索引是「另一份排好序的副本」，那么每多一个索引，每次写入（insert / update）都得顺带去维护那份副本、保持它有序。读快了，写在变慢、磁盘在变大。

还有个常见的浪费：给区分度很低的列单独建索引。比如 status 只有「published / draft / deleted」三个值，给它单独建索引，一查就命中几万行，pg 的规划器（planner）多半懒得用它，直接 Seq Scan 更省事。低区分度的列，更适合跟在复合索引后面当过滤，而不是自己单飞。

说到底，索引是拿写入开销和空间去换读取速度，不是越多越好。

怎么确认它真的用上了

这一趟下来，我养成的最重要的习惯不是「会建索引」，而是建完一定回头看 EXPLAIN。pg 的计划树重点看这几样：

看什么想看到警惕
扫描方式Index Scan / Index Only ScanSeq Scan（全表扫）
有没有 Sort 节点没有（顺序由索引给）Sort，尤其 external merge Disk（又排了一遍）
实际行数（ANALYZE）接近你要的条数远大于结果（白扫一大堆）

回头看

把被打脸的地方补上，现在我对索引的心智模型是这样：

  • 索引不是哈希表、也不是给值打的标记，是另一份按指定列排好序的数据副本（btree）；
  • 默认不用哈希，正因为哈希给不了顺序——而排序、范围、前缀全靠顺序（pg 那个只认 = 的哈希索引就是反面教材）；
  • 一条查询快不快，取决于它要的定位 + 顺序，跟某份副本排好的顺序对不对得上；
  • 单列索引能帮你定位，但管不了和它无关的 ORDER BY——那会多出一个 Sort 节点；
  • 复合索引把定位和排序一起排好，顺序按「最左前缀」来；
  • 一切以 EXPLAIN 为准，索引也不是白来的。

这是 ACGHub 列表的第一次提速。有意思的是，过了几年我又在同一个列表上栽了一次跟头——那次单条查询都很快，却慢得更离谱。后来发现是另一种病：查询本身不慢，可我查了太多次（每页 20 条、每条又各查一遍作者和计数，把次数活活堆上去）。那就是 了，得用完全不同的招去治，不过那是另一篇的事。

索引这关我算是过了。它不快，往往不是它没用，是你要的顺序和它排的顺序没对上——而我一开始连它「排过序」都不知道，还当它是个哈希表呢。

// related

作者头像
yen@harvey:~$ exit 0