database-designer

Database Designer - POWERFUL 层级 Skill

概述

这是一个全面的数据库设计 Skill，为现代数据库系统提供专家级的分析、优化和迁移能力。该 Skill 将理论原则与实用工具相结合，帮助架构师和开发者创建可扩展、高性能且易于维护的数据库 Schema。

核心能力

Schema 设计与分析

• 范式分析：自动检测范式级别（从 1NF 到 BCNF）
• 反范式策略：针对性能优化的智能建议
• 数据类型优化：识别不恰当的类型和大小问题
• 约束分析：缺失的外键、唯一约束和空值检查
• 命名规范校验：统一的表名和列名模式
• ERD 生成：从 DDL 自动生成 Mermaid 图表

索引优化

• 索引缺口分析：识别外键和查询模式上缺失的索引
• 复合索引策略：多列索引的最优列顺序
• 索引冗余检测：消除重叠和未使用的索引
• 性能影响建模：选择性估算和查询成本分析
• 索引类型选择：B-tree、哈希、部分索引、覆盖索引及特殊索引

迁移管理

• 零停机迁移：Expand-contract 模式实现
• Schema 演进：安全的列新增、删除和类型变更
• 数据迁移脚本：自动化的数据转换与校验
• 回滚策略：具备校验的完整逆向能力
• 执行规划：通过依赖解析得到有序的迁移步骤

工具工作流（请运行这些工具，不要手动分析 Schema）

所有路径均相对于本 Skill 文件夹；示例输入位于 assets/ 中。

1. 分析 Schema

python3 schema_analyzer.py --input schema.sql --generate-erd --output-format json -o analysis.json

接受 SQL DDL 或 JSON Schema（assets/sample_schema.sql / sample_schema.json）。输出包括范式发现、缺失约束、命名问题以及 Mermaid ERD——请向用户展示 ERD，并在优化之前修复已标记的问题。

2. 基于真实查询模式优化索引

python3 index_optimizer.py --schema assets/sample_schema.json --queries assets/sample_query_patterns.json --analyze-existing --format json -o indexes.json

请先将用户的高频查询写入查询模式 JSON（复制 assets/sample_query_patterns.json）。输出为按优先级排序的 CREATE INDEX 建议列表，以及冗余索引的删除建议。

3. 生成迁移

python3 migration_generator.py --current current_schema.json --target target_schema.json --zero-downtime --format sql -o migration.sql

--zero-downtime 会生成 expand-contract 计划；--validate-only 仅检查可行性，不生成 SQL。

4. 验证循环

对目标 Schema 重新运行第 1 步，确认第一轮发现的问题已全部消除；在交付迁移之前运行 migration_generator.py --validate-only。

数据库设计原则

→ 详见 references/database-design-reference.md

最佳实践

Schema 设计

1. 使用有意义的名称：清晰、统一的命名规范
2. 选择合适的数据类型：列大小适配，兼顾存储效率
3. 定义恰当的约束：外键、检查约束、唯一索引
4. 考虑未来增长：从一开始就为规模化做规划
5. 记录关系：清晰的外键关系和业务规则

性能优化

1. 有策略地建立索引：覆盖常见查询模式，同时避免过度索引
2. 监控查询性能：定期分析慢查询
3. 对大表分区：提升查询性能并简化维护
4. 使用合适的隔离级别：在一致性与性能之间取得平衡
5. 实现连接池：高效利用资源

安全注意事项

1. 最小权限原则：仅授予必要的最小权限
2. 加密敏感数据：包括静态数据和传输中的数据
3. 审计访问模式：监控并记录数据库访问
4. 校验输入：防止 SQL 注入攻击
5. 定期安全更新：保持数据库软件为最新版本

查询生成模式

带 JOIN 的 SELECT

-- INNER JOIN: only matching rows
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

-- LEFT JOIN: all left rows, NULLs for non-matches
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Self-join: hierarchical data (employees/managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

公共表表达式（CTE）

-- Recursive CTE for org chart
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;

窗口函数

-- ROW_NUMBER for pagination / dedup
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

-- RANK with gaps, DENSE_RANK without gaps
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;

-- LAG/LEAD for comparing adjacent rows
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

聚合模式

-- FILTER clause (PostgreSQL) for conditional aggregation
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  AVG(amount) FILTER (WHERE amount > 0) AS avg_positive
FROM accounts;

-- GROUPING SETS for multi-level rollups
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());

---

迁移模式

升级/回滚迁移脚本

每个迁移都必须有对应的可逆版本。文件名应使用时间戳前缀以保证执行顺序：

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

零停机迁移（扩展/收缩）

使用扩展-收缩模式，避免锁表或破坏正在运行的代码：

1. 扩展：添加新列/新表（可为空，并设置默认值）
2. 迁移数据：分批回填数据；应用层双写
3. 切换：应用改为从新列读取；停止写入旧列
4. 收缩：在后续迁移中删除旧列

数据回填策略

-- Batch update to avoid long-running locks
UPDATE users SET email_normalized = LOWER(email)
WHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL LIMIT 5000);
-- Repeat in a loop until 0 rows affected

回滚流程

• 在部署 up.sql 到生产环境之前，务必先在预发布环境中测试 down.sql
• 保持回滚窗口尽量短：如果已执行收缩步骤，回滚就需要编写新的正向迁移
• 对于不可逆的变更（例如删除含数据的列），应先进行逻辑备份

---

性能优化

索引策略

索引类型	适用场景	示例
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);
部分索引	仅覆盖部分行（减小索引体积）	CREATE INDEX idx_active ON users(email) WHERE active = true;
覆盖索引	仅索引扫描	CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at);

EXPLAIN 执行计划解读

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

需要关注的关键信号：

• 大表上出现 Seq Scan：可能缺少索引
• 行数估计值较高的 Nested Loop：考虑改用哈希连接或归并连接，或添加索引
• Buffers shared read 远高于 hit：工作集超出内存容量

N+1 查询检测

症状：应用对每一行单独发起一次查询（例如在循环中逐条获取关联记录）。

修复方法：

• 使用 JOIN 或子查询，一次往返获取全部数据
• 使用 ORM 的预加载（select_related / includes / with）
• 对 GraphQL 解析器使用 DataLoader 模式

连接池

工具	协议	最适合
PgBouncer	PostgreSQL	事务/语句级连接池，开销低
ProxySQL	MySQL	查询路由、读写分离
内置连接池（HikariCP、SQLAlchemy pool）	任意	应用层连接池

经验法则： 将连接池大小设置为 (2 * CPU 核心数) + 磁盘主轴数。对于云上 SSD，可从 2 * vCPU 数 开始，再逐步调优。

只读副本与查询路由

• 将所有 SELECT 查询路由到副本，写操作路由到主库
• 需考虑复制延迟（异步复制通常小于 1 秒，同步复制为 0）
• 在读取关键数据前，使用 pg_last_wal_replay_lsn() 检测延迟

---

多数据库决策矩阵

评估维度	PostgreSQL	MySQL	SQLite	SQL Server
最适合	复杂查询、JSONB、扩展	Web 应用、读多写少的工作负载	嵌入式、开发/测试、边缘端	企业级 .NET 技术栈
JSON 支持	优秀（JSONB + GIN）	良好（JSON 类型）	极少	良好（OPENJSON）
复制	流复制、逻辑复制	组复制、InnoDB 集群	不适用	Always On 可用性组
许可证	开源（PostgreSQL License）	开源（GPL）/ 商业	公有领域	商业
实际可承载规模上限	多 TB	多 TB	约 1 TB（单写入者）	多 TB

选型建议：

• PostgreSQL — 新项目的默认选择；扩展性和标准合规性最佳
• MySQL — 已有 MySQL 生态；简单的读多写少 Web 应用
• SQLite — 移动应用、命令行工具、单元测试数据库、IoT/边缘端
• SQL Server — 企业策略强制要求；与 .NET/Azure 深度集成

NoSQL 注意事项

数据库	模型	适用场景
MongoDB	文档型	模式灵活、快速原型开发、内容管理
Redis	键值/缓存	会话存储、限流、排行榜、发布/订阅
DynamoDB	宽列型	无服务器 AWS 应用，任意规模下延迟达到个位数毫秒

默认使用 SQL。只有在访问模式明确受益于 NoSQL 时，才考虑使用 NoSQL。

---

分片与复制

水平分区与垂直分区

• 垂直分区：将列拆分到不同的表中（例如，将 BLOB 列单独拆出）。可减少窄查询的 I/O。
• 水平分区（分片）：将行拆分到多个数据库/服务器中。当单个节点无法容纳数据集或承受吞吐量时需要使用。

分片策略

策略	工作方式	优点	缺点
哈希	shard = hash(key) % N	分布均匀	重新分片成本高
范围	按日期或 ID 范围分片	简单，适合时间序列	最新分片容易形成热点
地理位置	按用户所在地区分片	数据就近访问，满足合规要求	跨区域查询困难

复制模式

模式	一致性	延迟	使用场景
同步	强一致	写入延迟较高	金融交易
异步	最终一致	写入延迟低	读多写少的 Web 应用
半同步	至少一个副本确认	中等	兼顾安全性与速度

---

交叉引用

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