Database design advisor

何时使用

在以下数据架构决策场景使用本技能：

• 新项目或重构需要做数据库选型（SQL vs NoSQL、PostgreSQL/MySQL/SQLite/SQL Server 之间）。
• 设计或评审 Schema：范式（1NF~BCNF）、反范式取舍、数据类型、约束（外键/唯一/非空）、命名规范、ERD 建模。
• 规划数据迁移，尤其是要求零停机的演进式变更。
• 优化索引与慢查询：索引缺口、复合索引列序、冗余索引、EXPLAIN 解读、N+1 问题。
• 设计分库分表（sharding）、复制、读写分离、连接池等扩展性方案。

不该用的边界（负边界）：

• 只是要代写/调试某条具体业务 SQL —— 用日常 SQL 助手类技能。
• 纯 ORM 框架用法、应用层代码细节 —— 属于后端开发范畴。
• 数据库集群的基础设施供给、部署运维 —— 属于 DevOps 范畴。
• 不替代生产变更的实际评审与备份流程，仅提供方法与脚本草案。

步骤

1. 澄清需求与约束：数据规模、读写比、一致性要求、延迟目标、团队既有技术栈、合规/地域要求。
2. 选型：先默认 SQL（首选 PostgreSQL），仅当访问模式明确受益时才上 NoSQL。对照「选型决策矩阵」给出推荐与理由。
3. Schema 设计：确定范式层级与必要的反范式，定义合适的数据类型与约束，统一命名，必要时用 Mermaid 生成 ERD。
4. 索引规划：按常见查询模式覆盖，避免过度索引；为外键补索引，确定复合索引列序，剔除冗余。
5. 迁移设计：每个迁移配套可逆的 up/down 脚本；需要零停机时采用 expand-contract 模式，数据分批回填。
6. 性能优化：用 EXPLAIN (ANALYZE, BUFFERS) 验证计划，排查 Seq Scan / N+1 / 内存不足等信号。
7. 扩展性方案（按需）：分区/分片策略、复制模式、读写分离、连接池规格。
8. 交付：建模方案 + 迁移脚本 + 索引/查询优化建议 + 选型与扩展性决策说明，并标注回滚与备份要求。

指令

Schema 设计最佳实践： 有意义且一致的命名；按存储效率选「恰好够用」的类型；定义外键/检查/唯一约束；预留增长空间；文档化关系与业务规则。

安全约束： 最小权限授予；敏感数据静态与传输加密；审计访问；校验输入防 SQL 注入；及时打安全补丁。

索引类型速查（PostgreSQL）：

类型	适用	示例
B-tree（默认）	等值、范围、ORDER BY	CREATE INDEX idx_users_email ON users(email);
GIN	全文检索、JSONB、数组	CREATE INDEX idx_docs_body ON docs USING gin(to_tsvector('english', body));
GiST	几何、范围类型、最近邻	CREATE INDEX idx_locations ON places USING gist(coords);
Partial（部分）	行子集，减小体积	CREATE INDEX idx_active ON users(email) WHERE active = true;
Covering（覆盖）	仅索引扫描	CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at);

EXPLAIN 关键信号： 大表 Seq Scan → 缺索引；高行估算的 Nested Loop → 改 hash/merge join 或加索引；Buffers shared read 远高于 hit → 工作集超内存。

连接池经验值： 池大小约 (2 * CPU 核数) + 磁盘主轴数；云 SSD 从 2 * vCPU 起步再调优。PgBouncer（PG，事务/语句级池化）、ProxySQL（MySQL，读写分离/路由）、应用内池（HikariCP / SQLAlchemy）。

选型决策矩阵（要点）： PostgreSQL —— 新项目默认，复杂查询/JSONB/扩展性最佳；MySQL —— 既有生态、读多的简单 Web 应用；SQLite —— 移动端/CLI/单测/边缘（约 1 TB 单写上限）；SQL Server —— 企业 .NET/Azure 栈。NoSQL：MongoDB（文档，Schema 灵活）、Redis（KV/缓存，会话/限流/排行榜/pub-sub）、DynamoDB（宽列，Serverless AWS、任意规模个位毫秒延迟）。原则：默认 SQL，访问模式明确受益时才用 NoSQL。

分片策略： Hash（shard = hash(key) % N，分布均匀但重分片昂贵）；Range（按日期/ID，简单且适合时序，但最新分片有热点）；Geographic（按地域，数据本地化/合规，但跨域查询难）。

复制模式： 同步（强一致，写延迟高，用于金融交易）；异步（最终一致，写延迟低，用于读多 Web）；半同步（至少一副本确认，安全与速度折中）。读写分离：SELECT 走副本、写走主库；注意复制延迟，关键读前用 pg_last_wal_replay_lsn() 检测滞后。

示例

零停机迁移（expand-contract 四步）：

1. Expand —— 新增列/表（可空、带默认值）。
2. Migrate —— 分批回填，应用层双写。
3. Transition —— 应用改读新列，停止写旧列。
4. Contract —— 后续迁移中删除旧列。

迁移文件命名（时间戳前缀，up/down 成对）：

migrations/
├── 20260101_000001_create_users.up.sql
├── 20260101_000001_create_users.down.sql
├── 20260115_000002_add_users_email_index.up.sql
└── 20260115_000002_add_users_email_index.down.sql

分批回填，避免长锁：

UPDATE users SET email_normalized = LOWER(email)
WHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL LIMIT 5000);
-- 循环执行直到影响行数为 0

查询模式样例：

-- 递归 CTE：组织架构层级
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e INNER JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY depth, name;

-- 窗口函数：分页/去重与相邻行对比
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

-- 条件聚合（PostgreSQL FILTER）
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active
FROM accounts;

注意事项

• 迁移必可逆：上线 up.sql 前先在预发环境验证 down.sql。
• 回滚窗口要短：一旦执行了 contract（删列），回滚需要新的「前向」迁移，而非简单回退。
• 不可逆变更先备份：删除含数据的列等操作前，先做逻辑备份。
• N+1 排查：症状是每行触发一次查询；修复用 JOIN/子查询一次取回、ORM 预加载（select_related/includes/with）、GraphQL 用 DataLoader。
• 不要过度索引：索引加速读但拖慢写并占空间，仅覆盖真实高频查询模式。
• 分区/分片是最后手段：单节点装不下或扛不住吞吐时才上；优先垂直拆分（拆列、隔离大字段）再考虑水平分片。
• 复制延迟：异步复制通常 <1s，关键一致性读需显式检测滞后或走主库。

互见

• sql-database-assistant —— 日常 SQL 编写、优化与调试。
• database-schema-designer —— ERD 建模、范式分析与 Schema 生成。
• migration-architect —— 跨数据库引擎或大规模 Schema 重构的迁移规划。
• senior-backend —— 应用层模式（连接池、ORM 最佳实践）。
• senior-devops —— 数据库集群与副本的基础设施供给。

---

采编自 alirezarezvani/claude-skills（MIT 许可证），已按中文「技能大典」规范适配重写。