D1 与 SQLite 索引调优：把列表查询从 800ms 降到 8ms

线上博客文章数到两万之后，首页开始变慢：p95 从 40ms 涨到 800ms。D1 的慢查询日志指向了同一个语句。

先看语句长什么样

SELECT p.id, p.slug, p.title, p.summary, p.content, p.published_at
FROM posts p
WHERE p.status = 'published'
ORDER BY COALESCE(p.published_at, p.created_at) DESC, p.id DESC
LIMIT 10 OFFSET 0;

看起来人畜无害。但 ORDER BY 里带了个表达式 COALESCE(...)。

用 EXPLAIN QUERY PLAN 看真相

EXPLAIN QUERY PLAN
SELECT ... FROM posts p WHERE p.status = 'published'
ORDER BY COALESCE(p.published_at, p.created_at) DESC LIMIT 10;

SCAN posts AS p
USE TEMP B-TREE FOR ORDER BY

SCAN 意味着全表扫描，TEMP B-TREE 意味着把结果全排序一遍再取前 10 条。两万行数据，每次请求都要重新排一次。

第一刀：让索引能覆盖 ORDER BY

问题在于表达式无法直接使用索引。解决办法是把排序键固化成一个真实字段。

ALTER TABLE posts ADD COLUMN sort_at TEXT;
UPDATE posts SET sort_at = COALESCE(published_at, created_at);
CREATE INDEX idx_posts_list ON posts(status, sort_at DESC, id DESC);

然后在写入时维护它：

const sortAt = publishedAt ?? createdAt;
await db.prepare(
  `INSERT INTO posts (slug, title, status, published_at, created_at, sort_at)
   VALUES (?, ?, ?, ?, ?, ?)`,
).bind(slug, title, status, publishedAt, createdAt, sortAt).run();

再执行计划：

SEARCH posts AS p USING INDEX idx_posts_list (status=?)

全表扫描和临时排序都消失了，查询变成定位索引区间后顺序读 10 行。

第二刀：把大字段移出列表查询

content 是最大的列（Markdown 原文，平均 8KB）。列表页只需要摘要，却把两万行的正文都读了一遍。

改成分页只取轻量字段，摘要单独存：

SELECT id, slug, title, summary, published_at, sort_at
FROM posts
WHERE status = 'published'
ORDER BY sort_at DESC, id DESC
LIMIT 10 OFFSET ?;

如果确实需要自动截取摘要，就在写入时算好存进 summary，而不是在列表查询里现算。

第三刀：OFFSET 越翻越慢

LIMIT 10 OFFSET 20000 依然要先跳过两万行。深分页应该改成游标分页：

-- 第一页
SELECT ... FROM posts WHERE status = 'published'
ORDER BY sort_at DESC, id DESC LIMIT 10;

-- 下一页：带上上一页最后一行的 (sort_at, id)
SELECT ... FROM posts WHERE status = 'published'
  AND (sort_at < ? OR (sort_at = ? AND id < ?))
ORDER BY sort_at DESC, id DESC LIMIT 10;

条件里的 (sort_at, id) 正好匹配复合索引，代价恒定，和翻到第几页无关。

优化前后对比

几条可以复用的经验

1. 索引里不要放表达式。ORDER BY COALESCE(a, b) 一定走不了索引，把结果物化成字段
2. 看执行计划，不要猜。SCAN + TEMP B-TREE 就是明确的坏味道
3. 列表查询只取需要的列。大字段（正文、JSON）不要出现在分页 SQL 里
4. 深分页用游标。OFFSET 的成本是线性的，游标是常数
5. 复合索引的顺序很重要：等值条件在前（status），排序字段在后（sort_at DESC, id DESC）
6. 写多读少的表不要盲目加索引，每个索引都要在写入时付出代价

最后提醒一句：SQLite 没有「自动优化器帮你补索引」这种好事。两万行时还能靠内存扛，二十万行就是另一回事了——早点把执行计划看一遍。