名称: 数据库设计描述: 设计可扩展、规范化的数据库模式，包括关系、索引、约束和迁移策略，适用于关系型和NoSQL数据库。使用场景包括设计数据库模式、规划表关系和外国键、创建索引优化查询性能、定义约束和验证规则、设计可扩展的数据模型、规划数据库迁移策略、选择SQL或NoSQL、实现分片策略、优化查询性能或建立数据完整性规则。

数据库设计 - 模式、索引和查询优化

何时使用此技能

设计新的数据库模式和模型
规划表关系和外国键约束
创建索引以优化查询性能
定义数据库约束和验证规则
设计可扩展性和未来增长
规划数据库迁移策略
选择SQL或NoSQL数据库
实现数据库分片或分区
规范化数据以减少冗余
优化慢速数据库查询
设计审计跟踪和软删除
规划备份和灾难恢复策略

何时使用此技能

设计数据库模式，选择SQL/NoSQL，优化查询，规划迁移，或解决数据建模问题。
当处理相关任务或功能时
在需要此专业知识的开发过程中

使用时机: 设计数据库模式，选择SQL/NoSQL，优化查询，规划迁移，或解决数据建模问题。

核心原则

先规范化，必要时反规范化 - 从正确规范化开始
策略性索引 - 平衡查询速度与写入开销
为规模设计 - 从一开始考虑增长模式
选择合适的工具 - SQL与NoSQL取决于使用场景
迁移是永恒的 - 仔细规划模式变更

模式设计基础

1. 规范化（减少冗余）

-- ❌ 反规范化 - 数据重复
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  customer_email VARCHAR(100),
  customer_address TEXT,  -- 每个订单都重复！
  product_name VARCHAR(200),
  product_price DECIMAL(10,2),  -- 重复产品数据
  quantity INT
);

-- ✅ 规范化 - 分离关注点
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  address TEXT
);

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT DEFAULT 0
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  created_at TIMESTAMP DEFAULT NOW(),
  status VARCHAR(20) DEFAULT 'pending'
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INT NOT NULL REFERENCES products(id),
  quantity INT NOT NULL CHECK (quantity > 0),
  price_at_purchase DECIMAL(10,2) NOT NULL  -- 历史快照
);

2. 主键和外国键

-- ✅ 自增整数（传统）
CREATE TABLE users (
  id SERIAL PRIMARY KEY,  -- PostgreSQL
  -- id INT AUTO_INCREMENT PRIMARY KEY, -- MySQL
  email VARCHAR(255) UNIQUE NOT NULL
);

-- ✅ UUID（分布式系统，无冲突）
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  title VARCHAR(200) NOT NULL
);

-- ✅ 复合主键（连接表）
CREATE TABLE user_roles (
  user_id INT NOT NULL REFERENCES users(id),
  role_id INT NOT NULL REFERENCES roles(id),
  granted_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (user_id, role_id)
);

-- ✅ 外国键与级联选项
CREATE TABLE comments (
  id INT PRIMARY KEY,
  post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,  -- 帖子删除时删除评论
  user_id INT NOT NULL REFERENCES users(id) ON DELETE SET NULL,  -- 保留评论，置空用户
  content TEXT NOT NULL
);

3. 性能索引

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

-- ✅ 复合索引（多列查询）
CREATE INDEX idx_orders_customer_date 
ON orders(customer_id, created_at DESC);

-- ✅ 部分索引（条件性）
CREATE INDEX idx_active_users 
ON users(email) WHERE status = 'active';

-- ✅ 全文搜索索引
CREATE INDEX idx_posts_search 
ON posts USING GIN(to_tsvector('english', title || ' ' || content));

-- ✅ 唯一索引（强制约束并加速查找）
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- ❌ 过度索引（减慢写入）
-- 不要盲目索引每一列
-- 索引占用磁盘空间并减慢INSERT/UPDATE/DELETE

索引策略:

何时索引：
✓ 频繁用于WHERE子句
✓ 用于JOIN条件
✓ 用于ORDER BY / GROUP BY
✓ 高基数（许多唯一值）

何时不索引：
✗ 小表（< 1000行）
✗ 低基数（例如，布尔值，状态只有2-3个值）
✗ 频繁更新的列（除非读取 >> 写入）
✗ 已由复合索引覆盖

4. 关系

-- ✅ 一对多：用户有多篇帖子
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE posts (
  id INT PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id),
  title VARCHAR(200)
);

-- ✅ 多对多：用户 ↔ 角色（连接表）
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE roles (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE user_roles (
  user_id INT REFERENCES users(id),
  role_id INT REFERENCES roles(id),
  PRIMARY KEY (user_id, role_id)
);

-- ✅ 一对一：用户有一个资料
CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255));
CREATE TABLE profiles (
  id INT PRIMARY KEY,
  user_id INT UNIQUE NOT NULL REFERENCES users(id),  -- UNIQUE强制1:1
  bio TEXT,
  avatar_url VARCHAR(500)
);

-- ✅ 自引用：树形结构
CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  parent_id INT REFERENCES categories(id)  -- 指向自身
);

5. 数据类型

-- ✅ 选择合适类型
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,           -- 变长字符串
  description TEXT,                     -- 长文本
  price DECIMAL(10,2) NOT NULL,         -- 精确精度用于金钱
  stock INT DEFAULT 0,                  -- 整数
  weight FLOAT,                         -- 近似数
  is_active BOOLEAN DEFAULT true,       -- 真/假
  created_at TIMESTAMP DEFAULT NOW(),   -- 日期时间
  metadata JSONB,                       -- JSON数据（PostgreSQL）
  tags TEXT[]                           -- 数组（PostgreSQL）
);

-- ❌ 错误类型
-- price FLOAT - ❌ 金钱的浮点错误
-- created_at VARCHAR(50) - ❌ 使用适当日期时间类型
-- status INT - ❌ 使用ENUM或VARCHAR提高可读性

SQL与NoSQL选择

何时使用SQL（PostgreSQL, MySQL）

✓ 复杂关系和连接
✓ 需要ACID事务
✓ 结构化、可预测数据
✓ 复杂查询和聚合
✓ 需要强一致性
✓ 示例：金融系统、电子商务、CRM

何时使用NoSQL

文档存储（MongoDB, Firestore）：

✓ 灵活、演进模式
✓ 嵌套/层次数据
✓ 快速原型
✓ 以文档为中心的访问模式
✓ 示例：CMS、目录、用户资料

键值存储（Redis, DynamoDB）：

✓ 简单键查找
✓ 缓存层
✓ 会话存储
✓ 速率限制计数器
✓ 示例：缓存、会话、实时排行榜

时间序列（InfluxDB, TimescaleDB）：

✓ 时间戳数据
✓ 高写入吞吐量
✓ 时间聚合
✓ 示例：指标、日志、物联网传感器

查询优化

1. 识别慢查询

-- PostgreSQL - 解释查询计划
EXPLAIN ANALYZE
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name;

/*
输出显示：
- Seq Scan（差） vs Index Scan（好）
- 执行时间
- 扫描行数 vs 返回行数
*/

2. 避免N+1查询

// ❌ N+1问题 - 1查询 + N查询每个用户
const users = await db.query('SELECT * FROM users');
for (const user of users) {
  const posts = await db.query(
    'SELECT * FROM posts WHERE user_id = ?', 
    [user.id]
  ); // N查询！
}

// ✅ 单一查询与JOIN
const results = await db.query(`
  SELECT 
    u.id, u.name, u.email,
    p.id as post_id, p.title as post_title
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
`);

// ✅ 使用Prisma ORM与急切加载
const users = await prisma.user.findMany({
  include: {
    posts: true  // 高效加载相关帖子
  }
});

3. 使用高效连接

-- ✅ INNER JOIN - 仅匹配行
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON p.user_id = u.id;

-- ✅ LEFT JOIN - 所有用户，即使无帖子
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name;

-- ❌ 避免在SELECT中使用子查询（如果可能）
SELECT 
  u.name,
  (SELECT COUNT(*) FROM posts WHERE user_id = u.id) as post_count  -- 慢！
FROM users u;

-- ✅ 更好：使用JOIN
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name;

4. 分页

-- ✅ 偏移分页（简单但对深页较慢）
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- 第3页

-- ✅ 游标分页（对深页较快）
SELECT * FROM posts
WHERE created_at < '2024-01-01 12:00:00'
ORDER BY created_at DESC
LIMIT 20;

5. 聚合

-- ✅ 高效计数
SELECT COUNT(*) FROM users WHERE status = 'active';

-- ✅ 分组与聚合
SELECT 
  category,
  COUNT(*) as total,
  AVG(price) as avg_price,
  MAX(price) as max_price
FROM products
GROUP BY category
HAVING COUNT(*) > 10;  -- HAVING过滤组，WHERE过滤行

-- ✅ 窗口函数（PostgreSQL）
SELECT 
  name,
  salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;

迁移策略

1. 使用Prisma的模式迁移

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
  
  @@index([authorId])
}

# 生成迁移
npx prisma migrate dev --name add_post_table

# 应用到生产
npx prisma migrate deploy

2. 安全迁移模式

-- ✅ 添加列（安全 - 不锁表）
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- ✅ 并发添加索引（PostgreSQL）
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- ❌ 风险 - 锁整个表
ALTER TABLE users ALTER COLUMN email TYPE TEXT;

-- ✅ 更好：多步迁移
-- 步骤1：添加新列
ALTER TABLE users ADD COLUMN email_new TEXT;

-- 步骤2：回填数据
UPDATE users SET email_new = email;

-- 步骤3：交换列（在单独部署中）
ALTER TABLE users DROP COLUMN email;
ALTER TABLE users RENAME COLUMN email_new TO email;

3. 数据迁移

// migration-001-backfill-slugs.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function up() {
  const posts = await prisma.post.findMany({
    where: { slug: null }
  });
  
  for (const post of posts) {
    await prisma.post.update({
      where: { id: post.id },
      data: { slug: generateSlug(post.title) }
    });
  }
}

function generateSlug(title: string) {
  return title.toLowerCase().replace(/\s+/g, '-');
}

高级模式

1. 软删除

-- ✅ 保留删除记录
CREATE TABLE posts (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  deleted_at TIMESTAMP NULL  -- NULL = 未删除
);

-- 仅查询活动记录
SELECT * FROM posts WHERE deleted_at IS NULL;

-- “删除”记录（软删除）
UPDATE posts SET deleted_at = NOW() WHERE id = 123;

-- 创建视图以便利
CREATE VIEW active_posts AS
SELECT * FROM posts WHERE deleted_at IS NULL;

2. 审计跟踪

-- ✅ 跟踪所有变更
CREATE TABLE users_audit (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  field_changed VARCHAR(50),
  old_value TEXT,
  new_value TEXT,
  changed_by INT REFERENCES users(id),
  changed_at TIMESTAMP DEFAULT NOW()
);

-- 触发自动填充
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO users_audit (user_id, field_changed, old_value, new_value)
  VALUES (OLD.id, 'email', OLD.email, NEW.email);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_email_audit
AFTER UPDATE OF email ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_changes();

3. 多态关联

-- ✅ 对多种资源类型的评论
CREATE TABLE comments (
  id INT PRIMARY KEY,
  commentable_type VARCHAR(50) NOT NULL,  -- 'Post', 'Photo', 'Video'
  commentable_id INT NOT NULL,
  content TEXT NOT NULL,
  
  -- 复合索引用于多态查找
  INDEX idx_commentable (commentable_type, commentable_id)
);

-- 查询帖子的评论
SELECT * FROM comments 
WHERE commentable_type = 'Post' AND commentable_id = 123;

数据库清单

模式设计：
□ 适当规范化（至少3NF）
□ 所有表有主键
□ 外国键带有适当CASCADE/SET NULL
□ 检查约束用于数据验证
□ 适当NOT NULL约束
□ 唯一约束用于业务规则

索引：
□ 外国键索引
□ WHERE子句列索引
□ JOIN列索引
□ 多列查询复合索引
□ 过滤查询部分索引
□ 无冗余索引

性能：
□ 对慢查询使用EXPLAIN ANALYZE
□ 消除N+1查询
□ 正确使用JOIN vs 子查询
□ 实现分页
□ 配置连接池

迁移：
□ 所有变更在迁移文件中
□ 测试回滚能力
□ 安全列添加/移除
□ 约束前回填数据
□ 生产零停机迁移

监控：
□ 启用慢查询日志
□ 跟踪连接池指标
□ 监控磁盘使用
□ 检查复制延迟（如适用）
□ 定期VACUUM（PostgreSQL）

资源

记住: 好的数据库设计是可扩展应用的基础。前期投入时间进行适当的模式设计和索引。