DatabaseSchemaDesignerSkill database-schema-designer

数据库架构设计

概览

这项技能提供了全面指导，用于设计健壮、可扩展的数据库架构，适用于SQL和NoSQL数据库。无论是从头开始构建还是演进现有架构，此框架确保数据完整性、性能和可维护性。

何时使用这项技能：

• 设计新的数据库架构
• 重构或迁移现有架构
• 优化数据库性能
• 选择SQL和NoSQL方法
• 创建数据库迁移
• 建立索引策略
• 建模复杂关系
• 规划数据归档和分区

数据库设计哲学

核心原则

1. 建模领域，而不是UI

• 架构反映业务实体和关系
• 不要让UI需求驱动数据结构
• 分开表现关注点和数据模型

2. 优化读取或写入（不是两者都优化）

• OLTP（事务性）：规范化，优化写入
• OLAP（分析性）：非规范化，优化读取
• 根据访问模式选择

3. 从第一天起就计划扩展

• 索引策略
• 分区方法
• 缓存层
• 读取副本

4. 数据完整性优于性能

• 使用约束、外键、验证
• 性能问题可以稍后优化
• 数据损坏修复成本高

---

SQL数据库设计

规范化

数据库规范化减少了冗余，确保数据完整性。

第一范式（1NF）

规则：每个列包含原子（不可分割）值，没有重复组。

-- ❌ 违反1NF（一列中多个值）
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  product_ids VARCHAR(255)  -- '101,102,103' （不好！）
);

-- ✅ 遵循1NF
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

第二范式（2NF）

规则：必须在1NF + 所有非键列依赖于整个主键。

-- ❌ 违反2NF（customer_name仅依赖于customer_id，而不是完整键）
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  customer_id INT,
  customer_name VARCHAR(100),  -- 仅依赖于customer_id
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

-- ✅ 遵循2NF（客户数据在单独的表中）
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

第三范式（3NF）

规则：必须在2NF + 没有传递依赖（非键列仅依赖于主键）。

-- ❌ 违反3NF（country依赖于postal_code，而不是customer_id）
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  postal_code VARCHAR(10),
  country VARCHAR(50)  -- 依赖于postal_code，而不是id
);

-- ✅ 遵循3NF
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  postal_code VARCHAR(10),
  FOREIGN KEY (postal_code) REFERENCES postal_codes(code)
);

CREATE TABLE postal_codes (
  code VARCHAR(10) PRIMARY KEY,
  country VARCHAR(50)
);

反规范化（何时打破规则）

有时反规范化可以提高读取密集型应用的性能。

-- 为性能反规范化（缓存派生数据）
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total_amount DECIMAL(10, 2),  -- 从order_items计算
  item_count INT,               -- 从order_items计算
  created_at TIMESTAMP
);

-- 触发器或应用程序代码保持反规范化数据同步

何时反规范化：

• 读取密集型应用（报告，分析）
• 频繁连接的表导致性能问题
• 预计算的聚合（计数，总和，平均值）
• 缓存派生数据以避免复杂连接

---

数据类型

选择合适的数据类型以提高效率和准确性。

字符串类型

-- 固定长度（用于可预测的长度）
CHAR(10)      -- ISO日期：'2025-10-31'
CHAR(2)       -- 州代码：'CA'

-- 可变长度（用于可变长度）
VARCHAR(255)  -- 电子邮件，名称，短文本
TEXT          -- 长文本（文章，描述）

-- ✅ 好：适当的大小
email VARCHAR(255)
phone_number VARCHAR(20)
postal_code VARCHAR(10)

-- ❌ 不好：浪费或太小
email VARCHAR(500)       -- 太大
description VARCHAR(50)  -- 对于长文本来说太小

数值类型

-- 整数类型
TINYINT    -- -128到127（年龄，状态代码）
SMALLINT   -- -32,768到32,767（数量）
INT        -- -2.1B到2.1B（ID，计数）
BIGINT     -- 大数字（时间戳，大ID）

-- 小数类型
DECIMAL(10, 2)  -- 精确精度（金钱：$99,999,999.99）
FLOAT           -- 近似（科学计算）
DOUBLE          -- 更高的精度近似

-- ✅ 使用DECIMAL用于金钱
CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10, 2)  -- 精确精度
);

-- ❌ 不要使用FLOAT用于金钱
price FLOAT  -- 四舍五入错误！

日期/时间类型

DATE       -- 仅日期：2025-10-31
TIME       -- 仅时间：14:30:00
DATETIME   -- 日期+时间：2025-10-31 14:30:00
TIMESTAMP  -- Unix时间戳（自动转换时区）

-- ✅ 总是存储在UTC
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

布尔

-- PostgreSQL
is_active BOOLEAN DEFAULT TRUE

-- MySQL
is_active TINYINT(1) DEFAULT 1

---

索引策略

索引加速读取但减慢写入。策略性使用。

何时创建索引

-- ✅ 索引外键
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- ✅ 索引频繁查询的列
CREATE INDEX idx_users_email ON users(email);

-- ✅ 索引用于WHERE、ORDER BY、GROUP BY的列
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- ✅ 多列查询的复合索引
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

索引类型

B-Tree索引（默认）

-- 最适合等值和范围查询
CREATE INDEX idx_products_price ON products(price);

-- 受益的查询：
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 150;

哈希索引

-- 最适合仅精确匹配（不是范围）
CREATE INDEX idx_users_email USING HASH ON users(email);

-- 受益的查询：
SELECT * FROM users WHERE email = 'user@example.com';

全文索引

-- 最适合文本搜索
CREATE FULLTEXT INDEX idx_articles_content ON articles(title, content);

-- 受益的查询：
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('database design');

部分索引（PostgreSQL）

-- 仅索引特定行
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;

复合索引（列顺序重要）

-- ✅ 好：索引支持两个查询
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- 查询1：有效使用索引
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- 查询2：使用索引（customer_id仅）
SELECT * FROM orders WHERE customer_id = 123;

-- ❌ 查询3：不使用索引（status是第二列）
SELECT * FROM orders WHERE status = 'pending';

经验法则：将最具选择性的列放在第一位，或者最常单独查询的列。

---

约束

使用约束在数据库级别强制数据完整性。

主键

-- 自动递增整数
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL
);

-- UUID（更适合分布式系统）
CREATE TABLE users (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  email VARCHAR(255) UNIQUE NOT NULL
);

外键

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE      -- 删除客户时删除订单
    ON UPDATE CASCADE      -- 更新客户ID时更新订单
);

-- 替代方案：
ON DELETE RESTRICT   -- 如果引用则阻止删除
ON DELETE SET NULL   -- 父删除时设置为NULL
ON DELETE NO ACTION  -- 与RESTRICT相同

唯一约束

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  username VARCHAR(50) UNIQUE NOT NULL
);

-- 复合唯一约束
CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  UNIQUE (student_id, course_id)  -- 防止重复注册
);

检查约束

CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10, 2) CHECK (price >= 0),
  stock INT CHECK (stock >= 0),
  discount_percent INT CHECK (discount_percent BETWEEN 0 AND 100)
);

非空约束

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL,
  bio TEXT  -- 可空（可选）
);

---

常见架构模式

一对多（订单 → 订单项）

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

多对多（学生 ↔ 课程）

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
  id INT PRIMARY KEY,
  title VARCHAR(200) NOT NULL
);

-- 连接表（也称为联结表，链接表）
CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  grade VARCHAR(2),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

自引用（员工 → 经理）

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

多态关系（评论对帖子/照片）

-- 方法1：单独的外键与CHECK约束
CREATE TABLE comments (
  id INT PRIMARY KEY,
  content TEXT NOT NULL,
  post_id INT,
  photo_id INT,
  CHECK (
    (post_id IS NOT NULL AND photo_id IS NULL) OR
    (post_id IS NULL AND photo_id IS NOT NULL)
  ),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE
);

-- 方法2：commentable_type + commentable_id（Rails风格）
CREATE TABLE comments (
  id INT PRIMARY KEY,
  content TEXT NOT NULL,
  commentable_type VARCHAR(50) NOT NULL,  -- 'Post'或'Photo'
  commentable_id INT NOT NULL
);
-- 注意：不可能有外键约束（数据完整性较差）

---

NoSQL数据库设计

文档数据库（MongoDB）

何时使用：

• 需要模式灵活性
• 快速迭代
• 层次数据
• 读取密集型工作负载

嵌入与引用

嵌入（反规范化）

{
  "_id": "order_123",
  "customer": {
    "id": "cust_456",
    "name": "Jane Smith",
    "email": "jane@example.com"
  },
  "items": [
    { "product_id": "prod_789", "quantity": 2, "price": 29.99 },
    { "product_id": "prod_101", "quantity": 1, "price": 49.99 }
  ],
  "total": 109.97,
  "created_at": "2025-10-31T10:30:00Z"
}

何时嵌入：

• 数据经常一起访问
• 1：少数关系（少数项目）
• 子文档不需要独立存在

引用（规范化）

{
  "_id": "order_123",
  "customer_id": "cust_456",
  "item_ids": ["item_1", "item_2"],
  "total": 109.97,
  "created_at": "2025-10-31T10:30:00Z"
}

何时引用：

• 数据独立访问
• 1：多数关系（许多项目）
• 大型文档（接近16MB限制）
• 频繁更新的数据

MongoDB中的索引

// 创建索引
db.users.createIndex({ email: 1 }, { unique: true });

// 复合索引
db.orders.createIndex({ customer_id: 1, created_at: -1 });

// 文本索引用于搜索
db.articles.createIndex({ title: "text", content: "text" });

// 地理空间索引
db.stores.createIndex({ location: "2dsphere" });

---

数据库迁移

迁移最佳实践

1. 总是可逆的

-- 上迁移
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 下迁移
ALTER TABLE users DROP COLUMN phone;

2. 向后兼容

-- 好：添加可空列
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50);

-- 不好：添加必需列（破坏现有代码）
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) NOT NULL;

-- 更好：添加可空的，然后填充，然后必需
-- 迁移1：添加列
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50);

-- 迁移2：用默认值填充
UPDATE users SET middle_name = '' WHERE middle_name IS NULL;

-- 迁移3：必需
ALTER TABLE users MODIFY COLUMN middle_name VARCHAR(50) NOT NULL;

3. 数据迁移与架构更改分开

-- 迁移1：架构更改
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- 迁移2：数据迁移
UPDATE orders SET status = 'completed' WHERE completed_at IS NOT NULL;

4. 在生产副本上测试迁移

• 在包含生产数据快照的暂存环境中测试
• 测量迁移持续时间
• 计划停机时间（如果需要）

---

零停机迁移

添加列：

-- 第1步：添加可空列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 第2步：部署代码，写入新列
-- （应用程序现在同时写入旧列和新列）

-- 第3步：回填现有行
UPDATE users SET phone = old_phone WHERE phone IS NULL;

-- 第4步：使列必需（如果需要）
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL;

重命名列：

-- 第1步：添加新列
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- 第2步：复制数据
UPDATE users SET email_address = email;

-- 第3步：部署代码，从新列读取

-- 第4步：部署代码，写入新列

-- 第5步：删除旧列
ALTER TABLE users DROP COLUMN email;

---

性能优化

查询优化

使用EXPLAIN分析查询：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

查找：

• 类型：ALL（表扫描 - 不好），索引，ref，eq_ref
• 可能的键：可用的索引
• 键：实际使用的索引
• 行：估计扫描的行数

优化技术：

• 在WHERE、ORDER BY、GROUP BY列上添加索引
• 避免SELECT *（仅获取所需列）
• 使用LIMIT进行分页
• 为读取密集型查询反规范化

N+1查询问题

# ❌ 不好：N+1查询（1个查询用于订单 + N个查询用于客户）
orders = db.query("SELECT * FROM orders")
for order in orders:
    customer = db.query(f"SELECT * FROM customers WHERE id = {order.customer_id}")
    print(f"{customer.name} ordered {order.total}")

# ✅ 好：单个查询与JOIN
results = db.query("""
    SELECT orders.*, customers.name
    FROM orders
    JOIN customers ON orders.customer_id = customers.id
""")
for result in results:
    print(f"{result.name} ordered {result.total}")

---

与代理集成

后端系统架构师

• 在设计数据模型时使用这项技能
• 应用规范化和索引策略
• 计划可扩展性和性能

代码质量审查员

• 验证架构设计是否遵循最佳实践
• 检查缺失的索引和约束
• 审查迁移安全性

AI/ML工程师

• 使用反规范化模式进行分析
• 设计数据管道和聚合表

---

快速开始清单

设计新架构时：

• [ ] 确定实体和关系
• [ ] 根据需求选择SQL或NoSQL
• [ ] 规范化到3NF（SQL）或决定嵌入/引用（NoSQL）
• [ ] 定义主键（INT自增或UUID）
• [ ] 添加外键约束
• [ ] 选择适当的数据类型
• [ ] 添加唯一约束
• [ ] 计划索引策略（外键，WHERE列）
• [ ] 为必填字段添加NOT NULL约束
• [ ] 创建CHECK约束以进行验证
• [ ] 如有需要，计划软删除（deleted_at列）
• [ ] 添加时间戳（created_at，updated_at）
• [ ] 设计迁移脚本（上和下）
• [ ] 在暂存环境中测试迁移

---

技能版本：1.0.0 最后更新：2025-10-31 维护者：AI代理中心团队