backend-db-performance

数据库优化技能

自由度：混合。 修复哪个查询／索引／N+1 问题 [高自由度]； 检查现有索引和运行 EXPLAIN ANALYZE [低自由度——严格照做]。

如何推理

1. 观察 — EXPLAIN ANALYZE / pg_stat_statements / 现有的 pg_indexes
2. 解读 — 顺序扫描与 N+1 问题、过量读取或缺少分页之间的区别
3. 分类 — 添加索引／预加载／缩小查询字段／分页／保持不变
4. 判定严重程度 — 写入路径超时的优先级高于 200ms 的列表页

示例

观察： /feed 的 p95 为 2.4s；Prisma 日志显示 81 次查询；pg_indexes 中没有 idx_posts_user_created。 解读： 先用 findMany 获取帖子，再逐行调用 user.findUnique——这是 N+1 问题；ORDER BY created_at 会导致顺序扫描。 分类： 使用 include: { author } 预加载 + 复合索引 (user_id, created_at DESC)。 验证： EXPLAIN ANALYZE → Index Scan；查询次数为 2；p95 < 200ms。没有添加重复索引。

报告前自我检查

• 先检查现有内容 — 在 CREATE INDEX 之前列出了 pg_indexes / 迁移记录
• EXPLAIN — 声称有效的方案有 ANALYZE 输出支撑，而非凭直觉判断
• 没有重复索引 — 已查询并确认拟议的索引名称不存在
• 负责人正确 — 架构一致性 → audit-db-schema；RLS 访问控制 → plan-rls-audit

系统性地识别并修复数据库性能问题。

适用场景

• 页面加载缓慢（数据库瓶颈）
• 查询超时错误
• N+1 查询
• 架构设计审查
• 索引优化
• 迁移规划

先检查现有内容 [低自由度——严格照做]

进行任何优化之前，请先验证当前状态：

1. 检查现有索引：

SELECT indexname, indexdef FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'your_table';

2. 检查现有迁移记录：

ls -la supabase/migrations/ | grep -i "index\|optim\|perf"

3. 检查索引是否已存在：

SELECT 1 FROM pg_indexes WHERE indexname = 'your_proposed_index';

4. 通过 Supabase 顾问工具检查当前问题：

• 使用 get_advisors MCP 工具检查性能／安全问题
• 不要重复修复已解决的问题

原因： 重复索引会浪费存储空间并拖慢写入。添加索引前务必先验证。

性能调查 [高自由度]

1. 找出慢查询

• Prisma：使用 new PrismaClient({ log: [{ emit: 'event', level: 'query' }] }) 和 db.$on('query', ...)；记录所有耗时超过 100 ms 的查询及其耗时。
• Postgres／Supabase：运行 CREATE EXTENSION IF NOT EXISTS pg_stat_statements，然后按 total_time DESC 排序，并查看 total_time / calls 和 rows / calls。

日志代码片段和 pg_stat_statements 查询：references/queries.md §识别慢查询。

2. 常见性能问题

问题	症状	解决方案
N+1 查询	大量小型查询	使用 include / 预加载
缺少索引	WHERE/JOIN 查询缓慢	为筛选列添加索引
全表扫描	大型表查询缓慢	添加索引，限制结果数量
过量读取	响应缓慢	仅选择所需字段
无分页	内存问题	添加游标／偏移量分页

修复 N+1 查询 [高自由度]

• 问题： 先调用 findMany，然后在循环中逐行调用 findUnique——共 1 + N 次查询。
• 修复： 预加载关联数据；使用嵌套 select 缩小查询范围，避免过量读取联接数据。
• Supabase：一次 .select('*, author:users(id, name, avatar)') 调用即可完成联接。

// Good - 2 queries total
const posts = await db.post.findMany({
  include: { author: { select: { id: true, name: true, avatar: true } } },
})

Prisma 错误／正确示例及 Supabase 对应写法：references/queries.md §修复 N+1 查询。

索引优化 [高自由度]

何时添加索引

在以下情况添加索引：

• 列用于 WHERE 子句（筛选）
• 列用于 JOIN 条件
• 列用于 ORDER BY 子句
• 列用于唯一约束

以下情况不要添加索引：

• 表很小（< 1000 行）
• 列的基数较低（唯一值较少）
• 很少查询该列
• 表有大量写入操作

索引类型

• 单列索引用于单个筛选条件；当查询为 WHERE a = ? ORDER BY b 时使用复合索引（列顺序很重要：先等值条件，再排序）。
• 约束使用唯一索引；热数据子集使用部分索引（WHERE published = true）；JSONB、数组和 to_tsvector 搜索使用 GIN。
• Prisma：@@index([userId, createdAt(sort: Desc)])、@@unique([...]) 与 SQL 对应。

CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);  -- composite, order matters
CREATE INDEX idx_posts_published ON posts(created_at) WHERE published = true;  -- partial

所有六种索引形式和 Prisma 模型：references/schema-and-indexes.md §索引类型、§Prisma 索引语法。

查询优化模式 [高自由度]

• 仅选择所需字段 — 使用 select: { id, name, email }，而不是查询整行。
• 分页 — 对于较小的页面，偏移量（skip/take）足够；对于较大或无限列表，使用游标（cursor: { id }、skip: 1）；从最后一行返回 nextCursor。
• 批处理 — 使用 createMany({ skipDuplicates })，而不是循环调用 create；使用 $transaction([...]) 执行相关写入。
• 计数 — 使用 db.post.count({ where })；在同一个 $transaction 中将其与分页查询配对。

const posts = await db.post.findMany({
  take: limit,
  skip: cursor ? 1 : 0,                        // skip the cursor row itself
  cursor: cursor ? { id: cursor } : undefined,
  orderBy: { createdAt: 'desc' },
})
const nextCursor = posts.length === limit ? posts[posts.length - 1].id : null

仅选择所需字段、两种分页方式、批处理和计数：references/queries.md §仅选择所需字段至§计数优化。

Schema 设计最佳实践 [高自由度]

规范化与反规范化

在以下情况下进行规范化：

• 数据经常变化
• 数据完整性至关重要
• 存储空间需要节省

在以下情况下进行反规范化：

• 读取性能至关重要

• 数据很少变化

• 复杂联接速度较慢

• 规范化：使用 post_stats(post_id PK → posts, view_count, like_count) 表；反规范化：将计数器作为 posts 的列。

• 数据类型 — ID 使用 UUID，有长度限制的字符串使用 VARCHAR(n)，金额使用 DECIMAL(10,2)，始终使用 TIMESTAMPTZ，固定集合使用 ENUM。

• 软删除 — 使用可空的 deletedAt，并添加 @@index([deletedAt])；每次读取都筛选 deletedAt: null。

两种结构的 SQL、数据类型列表和软删除模型：references/schema-and-indexes.md §规范化、§高效数据类型、§软删除。

Supabase 专属优化 [高自由度]

• RLS — 每行调用一次函数或子查询（get_user_team()）速度较慢；优先使用 user_id = auth.uid()，或者在规则需要时，使用针对成员资格表的 EXISTS 联接。
• Edge Functions — 将多查询聚合移入一次 supabase.rpc('get_dashboard_stats') 调用，并通过 Deno.serve 提供服务，而不是让客户端多次往返请求。

CREATE POLICY "fast_policy" ON posts FOR SELECT USING (user_id = auth.uid());

慢速／快速／联接策略和 Edge Function：references/schema-and-indexes.md §RLS 性能、§Edge Functions。

查询分析 [低自由度 — 严格按原样执行]

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 'abc123' ORDER BY created_at DESC LIMIT 20;

请关注：Seq Scan（大型表上的不良表现）、Index Scan（良好）、Nested Loop（检查是否存在 N+1）、较高的实际耗时。带注释的版本：references/queries.md §EXPLAIN ANALYZE。

关键指标

指标	目标	超出后的操作
查询耗时	< 100ms	添加索引、进行优化
扫描行数	< 返回行数的 10 倍	添加索引
内存用量	< 256MB	添加 LIMIT、使用分页
连接数	< 连接池大小	使用连接池

优化检查清单 [低自由度 — 不要跳过]

• 记录并监控查询
• 为筛选／联接列建立索引
• 不存在 N+1 查询（预加载）
• 所有列表端点均使用分页
• 仅选择所需字段
• 尽可能使用批处理操作
• 已配置连接池
• 已优化 RLS 策略
• 对慢速查询运行 EXPLAIN ANALYZE
• 使用了适当的数据类型