PostgreSQL 索引设计与慢查询优化 | 拾音

从一条慢查询开始

文章列表页需要"已发布 + 按发布时间倒序 + 分页"：

SELECT id, title, summary, published_at
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 9 OFFSET 0;

数据量到 20 万行时，这条查询耗时 480 ms。EXPLAIN ANALYZE 显示走了顺序扫描并对全表排序。

复合索引的列顺序

建立索引：

CREATE INDEX idx_articles_status_published
ON articles (status, published_at DESC);

列顺序的原则是：等值条件在前，排序字段在后。status 是等值过滤，published_at 用于排序，因此可以同时利用过滤与有序扫描，省掉 Sort 节点。

优化后耗时降到 2.3 ms，扫描行数从 20 万降到 9。

标签筛选的陷阱

按标签筛选时很容易写成：

SELECT a.* FROM articles a
JOIN article_tags at ON at.article_id = a.id
WHERE at.tag_id = $1 AND a.status = 'published'
ORDER BY a.published_at DESC LIMIT 9;

article_tags 的主键是 (article_id, tag_id)，按 tag_id 查询无法使用主键前缀，因此需要单独的索引：

CREATE INDEX idx_article_tags_tag ON article_tags (tag_id);

分页：OFFSET 的代价

OFFSET 10000 仍需要扫描并丢弃前一万行，深分页会越来越慢。博客场景更适合游标分页：

SELECT id, title, published_at FROM articles
WHERE status = 'published' AND (published_at, id) < ($1, $2)
ORDER BY published_at DESC, id DESC LIMIT 9;

由于前台只展示"上一页/下一页"，游标分页完全够用，并且耗时稳定在 3 ms 以内。

索引不是越多越好

每个索引都会增加写入成本。我最终保留了 4 个索引：

索引	用途
(status, published_at)	文章列表
(slug) 唯一	详情页
(article_id, status)	评论查询
(tag_id)	标签筛选

小结

索引设计要围绕真实查询，而不是围绕表结构。先用 EXPLAIN ANALYZE 找到瓶颈，再按"等值在前、排序在后"建复合索引，最后用游标分页替代深 OFFSET。