agency-database-optimizer

用途

该技能定位为数据库性能专家，目标是在高负载下构建性能良好、可平滑扩展、不会在凌晨三点出问题的数据库架构。它主要服务于 PostgreSQL，同时熟悉 MySQL、Supabase 和 PlanetScale 的使用模式。

主要能力

PostgreSQL 优化与高级特性
EXPLAIN ANALYZE 与查询计划解读
索引策略：B-tree、GiST、GIN、部分索引、复合索引
模式设计：规范化与反规范化的权衡
N+1 查询的检测与解决
连接池：PgBouncer、Supabase pooler
迁移策略与零停机部署
Supabase / PlanetScale 特定模式

使用方式

技能通过示例代码与规则指导数据库设计与优化实践。

模式设计示例

-- Good: Indexed foreign keys, appropriate constraints
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_created_at ON users(created_at DESC);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(500) NOT NULL,
    content TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index foreign key for joins
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Partial index for common query pattern
CREATE INDEX idx_posts_published 
ON posts(published_at DESC) 
WHERE status = 'published';

-- Composite index for filtering + sorting
CREATE INDEX idx_posts_status_created 
ON posts(status, created_at DESC);

使用 EXPLAIN 优化查询

-- ❌ Bad: N+1 query pattern
SELECT * FROM posts WHERE user_id = 123;
-- Then for each post:
SELECT * FROM comments WHERE post_id = ?;

-- ✅ Good: Single query with JOIN
EXPLAIN ANALYZE
SELECT 
    p.id, p.title, p.content,
    json_agg(json_build_object(
        'id', c.id,
        'content', c.content,
        'author', c.author
    )) as comments
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.user_id = 123
GROUP BY p.id;

-- Check the query plan:
-- Look for: Seq Scan (bad), Index Scan (good), Bitmap Heap Scan (okay)
-- Check: actual time vs planned time, rows vs estimated rows

防止 N+1 查询

// ❌ Bad: N+1 in application code
const users = await db.query("SELECT * FROM users LIMIT 10");
for (const user of users) {
  user.posts = await db.query(
    "SELECT * FROM posts WHERE user_id = $1", 
    [user.id]
  );
}

// ✅ Good: Single query with aggregation
const usersWithPosts = await db.query(`
  SELECT 
    u.id, u.email, u.name,
    COALESCE(
      json_agg(
        json_build_object('id', p.id, 'title', p.title)
      ) FILTER (WHERE p.id IS NOT NULL),
      '[]'
    ) as posts
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id
  LIMIT 10
`);

安全迁移

-- ✅ Good: Reversible migration with no locks
BEGIN;

-- Add column with default (PostgreSQL 11+ doesn't rewrite table)
ALTER TABLE posts 
ADD COLUMN view_count INTEGER NOT NULL DEFAULT 0;

-- Add index concurrently (doesn't lock table)
COMMIT;
CREATE INDEX CONCURRENTLY idx_posts_view_count 
ON posts(view_count DESC);

-- ❌ Bad: Locks table during migration
ALTER TABLE posts ADD COLUMN view_count INTEGER;
CREATE INDEX idx_posts_view_count ON posts(view_count);

连接池

// Supabase with connection pooling
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!,
  {
    db: {
      schema: 'public',
    },
    auth: {
      persistSession: false, // Server-side
    },
  }
);

// Use transaction pooler for serverless
const pooledUrl = process.env.DATABASE_URL?.replace(
  '5432',
  '6543' // Transaction mode port
);

关键规则

始终检查查询计划：部署查询前运行 EXPLAIN ANALYZE
为外键建立索引：每个外键都需要索引以支持连接
避免 SELECT *：只获取需要的列
使用连接池：不要为每个请求打开连接
迁移必须可逆：始终编写 DOWN 迁移
绝不在生产环境锁表：索引使用 CONCURRENTLY
防止 N+1 查询：使用 JOIN 或批量加载
监控慢查询：设置 pg_stat_statements 或 Supabase 日志

适用场景

适合需要设计可扩展数据库模式、优化慢查询、消除 N+1 问题、规划零停机迁移以及配置连接池的开发与运维工作。

限制

资料未提供该技能的具体安装方式、收费模式、平台支持范围或效果数据，这些信息暂未提供。