数据库设计人员

Database Designer - POWERFUL 级技能

概述

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

核心能力

模式设计与分析

• 范式分析：自动检测范式级别（1NF 至 BCNF）

• 反范式策略：提供性能优化的智能建议

• 数据类型优化：识别不恰当的类型和尺寸问题

• 约束分析：检测缺失的外键、唯一约束和非空检查

• 命名规范验证：确保表名和列名模式的一致性

• ERD 生成：根据 DDL 自动创建 Mermaid 图表

索引优化

• 索引缺口分析：识别外键和查询模式中缺失的索引

• 复合索引策略：多列索引的最佳列顺序排列

• 索引冗余检测：消除重叠和未使用的索引

• 性能影响建模：选择性估算和查询成本分析

• 索引类型选择：B-tree、hash、部分索引、覆盖索引及专用索引

迁移管理

• 零停机迁移：实施“扩展-收缩”（expand-contract）模式

• 模式演进：安全的列添加、删除和类型变更

• 数据迁移脚本：自动化的数据转换与验证

• 回滚策略：具备验证能力的完整反转能力

• 执行计划：带有依赖解析的有序迁移步骤

工具工作流（请运行这些工具 —— 不要手动分析模式）

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

1. 分析模式

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

支持 SQL DDL 或 JSON 模式（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 将输出扩展-收缩计划；--validate-only 在不生成 SQL 的情况下检查可行性。

4. 验证循环

对 *目标* 模式重新运行步骤 1，并确认第一轮发现的问题已解决；在交付迁移脚本前运行 migration_generator.py --validate-only。

数据库设计原则

最佳实践

Sch

性能优化

安全考量

查询生成模式

带有 JOIN 的 SELECT

-- INNER JOIN: 仅返回匹配的行
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;
-- LEFT JOIN: 返回所有左表行，不匹配项显示为 NULL
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): 处理层级数据（如员工/经理）
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

公用表表达式 (CTEs)

-- 用于组织架构图的递归 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;

窗口函数

-- ROW_NUMBER 用于分页/去重
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;
-- RANK 有跳跃，DENSE_RANK 无跳跃
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;
-- LAG/LEAD 用于比较相邻行
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

聚合模式

-- FILTER 子句 (PostgreSQL) 用于条件聚合
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  AVG(amount) FILTER (WHERE amount > 0) AS avg_positive
FROM accounts;
-- GROUPING SETS 用于多级汇总
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());

---

迁移模式

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

零停机迁移 (扩展/收缩模式)

使用“扩展-收缩”模式以避免锁表或破坏运行中的代码：

1. 扩展 (Expand) — 添加新列/表（允许为空，带默认值）
2. 迁移数据 (Migrate data) — 分批回填数据；应用程序进行双写
3. 过渡 (Transition) — 应用程序从新列读取数据
列；停止写入旧列
4. 契约 (Contract) — 在后续迁移中删除旧列

数据回填策略

-- 分批更新以避免长时间锁表
UPDATE users SET email_normalized = LOWER(email)
WHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL LIMIT 5000);
-- 循环执行直到影响行数为 0

回滚流程

• 在将 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); |
| Partial (部分索引) | 行子集（减小索引体积） | CREATE INDEX idx_active ON users(email) WHERE active = true; |
| Covering (覆盖索引) | 仅索引扫描 (Index-only scans) | CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at); |

EXPLAIN 执行计划分析

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

关键关注信号：

• 大表上的 Seq Scan —— 缺失索引

• 预估行数较高的 Nested Loop —— 考虑使用 hash/merge join 或添加索引

• Buffers shared read 远高于 hit —— 工作集超过内存容量

N+1 查询检测

症状：应用程序每行记录发出一次查询（例如在循环中获取关联记录）。

解决方案：

• 使用 JOIN 或子查询在一次往返中获取数据

• ORM 预加载 (select_related / includes / with)

• GraphQL resolver 使用 DataLoader 模式

连接池

| 工具 | 协议 | 适用场景 |
|------|----------|----------|
| PgBouncer | PostgreSQL | 事务/语句级池化，低开销 |
| ProxySQL | MySQL | 查询路由、读写分离 |
| 内置池 (HikariCP, SQLAlchemy pool) | 通用 | 应用级池化 |

经验法则： 将池大小设置为 (2 * CPU 核心数) + 磁盘主轴数。对于云端 SSD，建议从 2 * vCPU 开始并进行调优。

读副本与查询路由

• 将所有 SELECT 查询路由至副本，写入操作路由至主库

• 考虑复制延迟（异步通常 <1s，同步为 0）

• 在读取关键数据前，使用 pg_last_wal_replay_lsn() 检测延迟

---

多数据库决策矩阵

| 维度 | PostgreSQL | MySQL | SQLite | SQL Server |
|----------|-----------|-------|--------|------------|
| 最佳适用 | 复杂查询, JSONB, 扩展插件 | Web 应用, 读密集型负载 | 嵌入式, 开发/测试, 边缘计算 | 企业级 .NET 技术栈 |
| JSON 支持 | 极佳 (JSONB + GIN) | 良好 (JSON 类型) | 极简 | 良好 (OPENJSON) |
| 复制机制 | 流复制, 逻辑复制 | 组复制, InnoDB cluster | 不适用 | Always On AG |
| 许可协议 | 开源 (PostgreSQL License) | 开源 (GPL) / 商业 | 公有领域 | 商业 |
| 实际最大容量 | 多 TB | 多 TB | ~1 TB (单写) | 多 TB |

选择建议：

• PostgreSQL —— 新项目的默认选择；具备最佳的可扩展性和标准兼容性

• MySQL —— 已有 MySQL 生态；简单的读密集型 Web 应用

• SQLite

• SQL Server — 企业政策强制要求；深度集成 .NET/Azure

NoSQL 考量

| 数据库 | 模型 | 适用场景 |
|----------|-------|----------|
| MongoDB | 文档 | 模式灵活、快速原型开发、内容管理 |
| Redis | 键值 / 缓存 | 会话存储、限流、排行榜、发布/订阅 |
| DynamoDB | 宽列 | AWS Serverless 应用、任何规模下均能实现个位数毫秒级延迟 |

> 默认使用 SQL。仅在访问模式能明显获益时才选择 NoSQL。

---

分片与复制

水平分区 vs 垂直分区

• 垂直分区 (Vertical partitioning)：将列拆分到不同表中（例如：将 BLOB 列分离）。减少窄查询的 I/O。

• 水平分区 (Horizontal partitioning/Sharding)：将行拆分到不同数据库/服务器。当单节点无法承载数据集或处理吞吐量时必须使用。

分片策略

| 策略 | 工作原理 | 优点 | 缺点 |
|----------|-------------|------|------|
| 哈希 (Hash) | shard = hash(key) % N | 分布均匀 | 重新分片成本高 |
| 范围 (Range) | 按日期或 ID 范围分片 | 简单，适用于时间序列 | 最新分片易出现热点 |
| 地理 (Geographic) | 按用户区域分片 | 数据本地化、合规性 | 跨区域查询困难 |

复制模式

| 模式 | 一致性 | 延迟 | 适用场景 |
|---------|------------|---------|----------|
| 同步 (Synchronous) | 强一致性 | 写入延迟较高 | 金融交易 |
| 异步 (Asynchronous) | 最终一致性 | 写入延迟低 | 读密集型 Web 应用 |
| 半同步 (Semi-synchronous) | 至少一个副本确认 | 中等 | 安全性与速度的平衡 |

---

交叉引用

• sql-database-assistant — 日常 SQL 工作的查询编写、优化和调试

• database-schema-designer — ERD 建模、规范化分析和模式生成

• migration-architect — 跨数据库引擎的大规模迁移规划或重大模式重构

• senior-backend — 应用层模式（连接池、ORM 最佳实践）

• senior-devops — 数据库集群和副本的基础设施部署