第35讲｜AI 辅助数据库设计与迁移：零停机变更的艺术

金句：数据库迁移是软件开发中最危险的操作之一——线上数据只要丢一行，就是无法挽回的事故。AI 不能帮你避免所有风险，但可以帮你看到所有陷阱。

---

一、数据库变更的风险等级

风险等级    操作类型                     可回滚性
─────────────────────────────────────────────────
低风险    添加新表、添加新列（有默认值）    容易回滚
中风险    修改列类型、添加约束             需要计划
高风险    重命名列、删除列                 困难
极高风险  删除表、清空数据                 几乎不可回滚

---

二、AI 辅助数据库设计

从需求生成 Schema

提示词：

设计一个电商平台的数据库 Schema（PostgreSQL），
包含以下核心功能：
- 用户（支持普通用户和商家）
- 商品（多规格、多属性，如颜色+尺寸）
- 订单（支持拆单、合并）
- 购物车
- 优惠券（金额/折扣，使用限制）
- 收货地址

要求：
1. 使用 Prisma Schema 格式
2. 考虑查询性能（添加必要的索引）
3. 支持软删除
4. 考虑多租户（未来可能支持）
5. 字段命名使用 snake_case

AI 生成的 Prisma Schema 片段：

// schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id          String    @id @default(cuid())
  email       String    @unique
  name        String
  role        UserRole  @default(CUSTOMER)
  isDeleted   Boolean   @default(false)
  deletedAt   DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  
  orders      Order[]
  addresses   Address[]
  cart        CartItem[]
  store       Store?    // 如果是商家，有关联店铺
  
  @@index([email])
  @@index([isDeleted, role])  // 常见查询组合
}

enum UserRole {
  CUSTOMER
  MERCHANT
  ADMIN
}

model Product {
  id          String      @id @default(cuid())
  name        String
  description String?
  storeId     String
  isDeleted   Boolean     @default(false)
  deletedAt   DateTime?
  createdAt   DateTime    @default(now())
  
  store       Store       @relation(fields: [storeId], references: [id])
  variants    ProductVariant[]
  categories  Category[]
  
  @@index([storeId, isDeleted])
}

// 商品规格（如 颜色:红色 + 尺寸:L）
model ProductVariant {
  id          String    @id @default(cuid())
  productId   String
  sku         String    @unique
  price       Decimal   @db.Decimal(10, 2)
  stock       Int       @default(0)
  attributes  Json      // {"color": "红色", "size": "L"}
  
  product     Product   @relation(fields: [productId], references: [id])
  orderItems  OrderItem[]
  cartItems   CartItem[]
  
  @@index([productId])
  @@index([sku])
}

---

三、零停机数据库迁移策略

扩展-收缩（Expand-Contract）模式

这是处理高危数据库变更的标准方法：

场景：将 users 表的 full_name 列拆分为 first_name 和 last_name

传统做法（危险）：
直接修改 → 服务中断 → 数据可能丢失

扩展-收缩做法（安全）：

第1步（扩展）：添加新列，不删旧列
ALTER TABLE users ADD COLUMN first_name VARCHAR;
ALTER TABLE users ADD COLUMN last_name VARCHAR;

第2步（回填）：异步填充新列数据
UPDATE users SET first_name = split_part(full_name, ' ', 1),
                 last_name = split_part(full_name, ' ', 2);

第3步（双写）：应用层同时写入新旧列（部署新代码）
// 写入时：
user.full_name = `${firstName} ${lastName}`
user.first_name = firstName
user.last_name = lastName

第4步（验证）：确认新列数据正确后，切换读取逻辑

第5步（收缩）：删除旧列（下一个版本）
ALTER TABLE users DROP COLUMN full_name;

AI 辅助生成迁移计划

提示词：

我需要对生产数据库进行以下变更：
将 orders 表的 amount 字段从 FLOAT 改为 DECIMAL(10,2)

当前情况：
- 生产环境，7×24 小时运行
- orders 表约 500 万行数据
- 每秒约 50 次写入

请生成：
1. 这个变更的风险评估
2. 零停机迁移方案（分步骤）
3. 回滚方案
4. 完整的 SQL 迁移脚本
5. 迁移前后的数据验证 SQL

AI 生成的迁移方案：

-- 步骤1：添加新列（在线操作，无锁）
ALTER TABLE orders ADD COLUMN amount_decimal DECIMAL(10,2);

-- 步骤2：异步回填（分批执行，避免锁表）
DO $$
DECLARE
    batch_size INT := 10000;
    last_id BIGINT := 0;
    max_id BIGINT;
BEGIN
    SELECT MAX(id) INTO max_id FROM orders;
    
    WHILE last_id < max_id LOOP
        UPDATE orders 
        SET amount_decimal = ROUND(amount::DECIMAL, 2)
        WHERE id > last_id AND id <= last_id + batch_size;
        
        last_id := last_id + batch_size;
        PERFORM pg_sleep(0.01);  -- 避免过度占用 CPU
    END LOOP;
END $$;

-- 步骤3：添加非空约束（回填完成后）
ALTER TABLE orders ALTER COLUMN amount_decimal SET NOT NULL;

-- 步骤4：验证数据一致性
SELECT COUNT(*) as mismatch_count
FROM orders
WHERE ABS(amount - amount_decimal) > 0.01;
-- 期望结果：0

-- 步骤5：在应用层切换到新字段（代码发布）

-- 步骤6：重命名字段（应用稳定后的下个版本）
ALTER TABLE orders RENAME COLUMN amount TO amount_old;
ALTER TABLE orders RENAME COLUMN amount_decimal TO amount;

-- 步骤7：确认无问题后删除旧列
ALTER TABLE orders DROP COLUMN amount_old;

---

四、数据库性能诊断

慢查询日志分析

-- 开启慢查询日志（PostgreSQL）
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 记录超过1秒的查询
SELECT pg_reload_conf();

-- 查看最慢的查询（pg_stat_statements）
SELECT 
    query,
    calls,
    total_exec_time / calls AS avg_time_ms,
    rows / calls AS avg_rows,
    shared_blks_hit / calls AS cache_hits
FROM pg_stat_statements
ORDER BY avg_time_ms DESC
LIMIT 20;

把这个输出发给 AI：

以下是我们最慢的 20 个查询，请分析：
1. 哪些查询最急需优化？
2. 每个查询的问题在哪里？
3. 建议添加哪些索引？
4. 是否有可以合并的查询？

[粘贴 pg_stat_statements 输出]

---

五、自动化数据库健康检查

# db_health_check.py
import psycopg2
from datetime import datetime

def check_database_health(connection_string: str) -> dict:
    """数据库健康检查"""
    conn = psycopg2.connect(connection_string)
    cur = conn.cursor()
    
    issues = []
    
    # 检查表膨胀
    cur.execute("""
        SELECT schemaname, tablename,
               n_live_tup, n_dead_tup,
               ROUND(n_dead_tup::NUMERIC / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS bloat_pct
        FROM pg_stat_user_tables
        WHERE n_dead_tup > 10000
        ORDER BY bloat_pct DESC
        LIMIT 10
    """)
    bloat_tables = cur.fetchall()
    for table in bloat_tables:
        if table[4] and table[4] > 20:
            issues.append(f"表膨胀：{table[1]} 死元组比例 {table[4]}%，建议 VACUUM ANALYZE")
    
    # 检查缺少索引的外键
    cur.execute("""
        SELECT c.conrelid::regclass AS table, a.attname AS column
        FROM pg_constraint c
        JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
        WHERE c.contype = 'f'
        AND NOT EXISTS (
            SELECT 1 FROM pg_index i
            WHERE i.indrelid = c.conrelid
            AND a.attnum = ANY(i.indkey)
        )
    """)
    missing_fk_indexes = cur.fetchall()
    for fk in missing_fk_indexes:
        issues.append(f"缺少外键索引：{fk[0]}.{fk[1]}")
    
    return {
        "check_time": datetime.now().isoformat(),
        "issues": issues,
        "is_healthy": len(issues) == 0
    }

---

章节小结：AI 辅助数据库设计和迁移的核心价值在于：快速发现设计缺陷（N+1、缺少索引、不合理的数据类型），生成安全的迁移计划（扩展-收缩模式），以及在迁移前进行风险预警。记住：数据库是最不可原谅的失误区域，AI 的帮助让你多一双眼睛，但最终决策和验证仍需要人。