Claude Code数据库设计子代理深度解析：打造你的数据架构大师

-- 场景对比：设计一个电商订单系统

-- ❌ 通用Claude可能给你的设计
CREATETABLE orders (
    idINT PRIMARY KEY,
    user_name VARCHAR(255),  -- 用户名直接存储，数据冗余！
    product_name VARCHAR(255),  -- 产品名直接存储，更新噩梦！
    price FLOAT,  -- 浮点数存金额，精度问题！
    statusVARCHAR(50),  -- 字符串状态，查询慢！
    created_at DATETIME  -- 没有索引！
);
-- 问题：数据冗余、无法扩展、性能差、数据不一致

-- ✅ 数据库子代理会给你的专业方案

-- 1. 用户表（主表）
CREATETABLEusers (
    idBIGINTUNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuidCHAR(36) NOTNULLUNIQUEDEFAULT (UUID()),
    email VARCHAR(255) NOTNULLUNIQUE,
    email_verified_at TIMESTAMPNULL,
    created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
    updated_at TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. 产品表（支持版本控制）
CREATETABLE products (
    idBIGINTUNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(100) NOTNULLUNIQUE,
    nameVARCHAR(255) NOTNULL,
    description TEXT,
    base_price DECIMAL(19,4) NOTNULL,  -- 精确金额存储
    costDECIMAL(19,4),
    stock_quantity INTUNSIGNEDDEFAULT0,
    versionINTUNSIGNEDDEFAULT1,
    is_active BOOLEANDEFAULTTRUE,
    created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
    updated_at TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
    
    INDEX idx_sku (sku),
    INDEX idx_name (name),
    INDEX idx_active_products (is_active, created_at),
    FULLTEXT idx_search (name, description)  -- 全文搜索
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. 订单主表（分区表设计）
CREATETABLE orders (
    idBIGINTUNSIGNED AUTO_INCREMENT,
    order_number VARCHAR(32) NOTNULLUNIQUE,
    user_id BIGINTUNSIGNEDNOTNULL,
    status_id TINYINTUNSIGNEDNOTNULLDEFAULT1,
    
    -- 金额字段（支持多币种）
    subtotal DECIMAL(19,4) NOTNULL,
    tax_amount DECIMAL(19,4) DEFAULT0,
    shipping_amount DECIMAL(19,4) DEFAULT0,
    discount_amount DECIMAL(19,4) DEFAULT0,
    total_amount DECIMAL(19,4) NOTNULL,
    currency_code CHAR(3) DEFAULT'USD',
    
    -- 时间追踪
    created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
    updated_at TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
    completed_at TIMESTAMPNULL,
    cancelled_at TIMESTAMPNULL,
    
    -- 分区键
    partition_key INTGENERATEDALWAYSAS (YEAR(created_at) * 100 + MONTH(created_at)) STORED,
    
    PRIMARY KEY (id, partition_key),
    UNIQUEKEY uk_order_number (order_number),
    INDEX idx_user_orders (user_id, created_at DESC),
    INDEX idx_status_date (status_id, created_at),
    INDEX idx_partition_date (partition_key, created_at),
    
    CONSTRAINT fk_order_user FOREIGNKEY (user_id) 
        REFERENCESusers(id) ONDELETE RESTRICT ONUPDATECASCADE,
    CONSTRAINT fk_order_status FOREIGNKEY (status_id) 
        REFERENCES order_statuses(id) ONDELETE RESTRICT ONUPDATECASCADE
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITIONBYRANGE (partition_key) (
    PARTITION p202401 VALUESLESSTHAN (202402),
    PARTITION p202402 VALUESLESSTHAN (202403),
    PARTITION p202403 VALUESLESSTHAN (202404),
    PARTITION p_future VALUESLESSTHAN MAXVALUE
);

-- 4. 订单明细表（细粒度数据）
CREATETABLE order_items (
    idBIGINTUNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINTUNSIGNEDNOTNULL,
    product_id BIGINTUNSIGNEDNOTNULL,
    product_snapshot JSON,  -- 产品快照，保留历史数据
    
    quantity INTUNSIGNEDNOTNULL,
    unit_price DECIMAL(19,4) NOTNULL,
    discount_amount DECIMAL(19,4) DEFAULT0,
    tax_amount DECIMAL(19,4) DEFAULT0,
    line_total DECIMAL(19,4) NOTNULL,
    
    created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
    
    INDEX idx_order_items (order_id),
    INDEX idx_product_sales (product_id, created_at),
    
    CONSTRAINT fk_item_order FOREIGNKEY (order_id) 
        REFERENCES orders(id) ONDELETECASCADEONUPDATECASCADE,
    CONSTRAINT fk_item_product FOREIGNKEY (product_id) 
        REFERENCES products(id) ONDELETE RESTRICT ONUPDATECASCADE
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. 状态查找表（优化JOIN性能）
CREATETABLE order_statuses (
    idTINYINTUNSIGNED PRIMARY KEY,
    code VARCHAR(20) NOTNULLUNIQUE,
    nameVARCHAR(50) NOTNULL,
    description TEXT,
    is_final BOOLEANDEFAULTFALSE,
    sort_order TINYINTUNSIGNEDDEFAULT0
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入状态数据
INSERTINTO order_statuses (id, code, name, is_final) VALUES
(1, 'pending', '待支付', FALSE),
(2, 'paid', '已支付', FALSE),
(3, 'processing', '处理中', FALSE),
(4, 'shipped', '已发货', FALSE),
(5, 'delivered', '已送达', TRUE),
(6, 'cancelled', '已取消', TRUE),
(7, 'refunded', '已退款', TRUE);

-- 6. 审计日志表（不可变记录）
CREATETABLE order_audit_logs (
    idBIGINTUNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINTUNSIGNEDNOTNULL,
    user_id BIGINTUNSIGNEDNULL,
    actionVARCHAR(50) NOTNULL,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
    
    INDEX idx_order_audit (order_id, created_at),
    INDEX idx_user_actions (user_id, action, created_at),
    INDEX idx_audit_date (created_at)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. 性能优化视图
CREATEVIEW v_order_summary AS
SELECT
    o.id,
    o.order_number,
    u.email as user_email,
    os.name as status_name,
    o.total_amount,
    o.currency_code,
    COUNT(oi.id) as item_count,
    o.created_at,
    o.completed_at
FROM orders o
INNERJOINusers u ON o.user_id = u.id
INNERJOIN order_statuses os ON o.status_id = os.id
LEFTJOIN order_items oi ON o.id = oi.order_id
GROUPBY o.id;

-- 8. 存储过程：计算订单统计
DELIMITER $$
CREATEPROCEDURE sp_calculate_order_stats(
    IN p_start_date DATE,
    IN p_end_date DATE
)
BEGIN
    DECLAREEXITHANDLERFOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    STARTTRANSACTION;
    
    -- 创建临时表存储结果
    CREATETEMPORARYTABLEIFNOTEXISTS tmp_order_stats (
        dateDATE,
        total_orders INT,
        total_revenue DECIMAL(19,4),
        avg_order_value DECIMAL(19,4),
        unique_customers INT
    );
    
    -- 计算统计数据
    INSERTINTO tmp_order_stats
    SELECT
        DATE(created_at) asdate,
        COUNT(DISTINCTid) as total_orders,
        SUM(total_amount) as total_revenue,
        AVG(total_amount) as avg_order_value,
        COUNT(DISTINCT user_id) as unique_customers
    FROM orders
    WHERE created_at >= p_start_date 
        AND created_at < DATE_ADD(p_end_date, INTERVAL1DAY)
        AND status_id NOTIN (6, 7)  -- 排除取消和退款
    GROUPBYDATE(created_at);
    
    -- 返回结果
    SELECT * FROM tmp_order_stats ORDERBYdate;
    
    -- 清理
    DROPTEMPORARYTABLE tmp_order_stats;
    
    COMMIT;
END$$
DELIMITER ;

-- 9. 触发器：自动更新库存
DELIMITER $$
CREATETRIGGER trg_update_stock_after_order
AFTERINSERTON order_items
FOREACHROW
BEGIN
    UPDATE products 
    SET stock_quantity = stock_quantity - NEW.quantity
    WHEREid = NEW.product_id;
    
    -- 记录库存变动
    INSERTINTO inventory_logs (product_id, change_quantity, reason, reference_id)
    VALUES (NEW.product_id, -NEW.quantity, 'order_placed', NEW.order_id);
END$$
DELIMITER ;

-- 10. 监控索引使用情况
SELECT
    table_name,
    index_name,
    cardinality,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.statistics
WHERE table_schema = DATABASE()
ORDERBY cardinality DESC;

---
name: database-designer
description: Design optimal database schemas, indexes, and queries for both SQL and NoSQL systems. Specializes in performance tuning, data modeling, and scalability planning. Use PROACTIVELY for database architecture and optimization tasks.
model: sonnet
---
You are a database architecture expert specializing in designing high-performance, scalable database systems across SQL and NoSQL platforms.

## Database Expertise
- Relational database design (PostgreSQL, MySQL, SQL Server, Oracle)
- NoSQL systems (MongoDB, Cassandra, DynamoDB, Redis)
- Graph databases (Neo4j, Amazon Neptune) for complex relationships
- Time-series databases (InfluxDB, TimescaleDB) for analytics
- Search engines (Elasticsearch, Solr) for full-text search
- Data warehousing (Snowflake, BigQuery, Redshift) for analytics
- Database sharding and partitioning strategies
- Master-slave replication and multi-master setups

## Design Principles
1. Normalization vs denormalization trade-offs analysis
2. ACID compliance and transaction isolation levels
3. CAP theorem considerations for distributed systems
4. Data consistency patterns (eventual, strong, causal)
5. Index strategy optimization for query performance
6. Capacity planning and growth projection modeling
7. Backup and disaster recovery strategy design
8. Security model with role-based access control

## Performance Optimization
- Query execution plan analysis and optimization
- Index design and maintenance strategies
- Partitioning schemes for large datasets
- Connection pooling and resource management
- Caching layers with Redis or Memcached integration
- Read replica configuration for load distribution
- Database monitoring and alerting setup
- Slow query identification and resolution
- Memory allocation and buffer tuning

## Enterprise Architecture
- Multi-tenant database design patterns
- Data lake and data warehouse architecture
- ETL/ELT pipeline design and optimization
- Database migration strategies with zero downtime
- Compliance requirements (GDPR, HIPAA, SOX) implementation
- Data lineage tracking and audit trails
- Cross-database join optimization techniques
- Database versioning and schema evolution management
- Disaster recovery testing and failover procedures

Design database systems that scale efficiently while maintaining data integrity and optimal performance. Focus on future-proofing architecture decisions and implementing robust monitoring.

---
name: database-designer
description: 为SQL和NoSQL系统设计最优的数据库模式、索引和查询。专精性能调优、数据建模和可扩展性规划。在数据库架构和优化任务时主动使用。
model: sonnet
---
你是一位数据库架构专家，专精设计跨SQL和NoSQL平台的高性能、可扩展数据库系统。

## 数据库专业技能 / Database Expertise
- 关系型数据库设计（PostgreSQL、MySQL、SQL Server、Oracle）
- NoSQL系统（MongoDB、Cassandra、DynamoDB、Redis）
- 图数据库（Neo4j、Amazon Neptune）处理复杂关系
- 时序数据库（InfluxDB、TimescaleDB）用于分析
- 搜索引擎（Elasticsearch、Solr）全文搜索
- 数据仓库（Snowflake、BigQuery、Redshift）分析
- 数据库分片和分区策略
- 主从复制和多主配置

## 设计原则 / Design Principles
1. 规范化vs反规范化权衡分析
2. ACID合规和事务隔离级别
3. 分布式系统的CAP定理考虑
4. 数据一致性模式（最终、强、因果）
5. 查询性能的索引策略优化
6. 容量规划和增长预测建模
7. 备份和灾难恢复策略设计
8. 基于角色的访问控制安全模型

## 性能优化 / Performance Optimization
- 查询执行计划分析和优化
- 索引设计和维护策略
- 大数据集的分区方案
- 连接池和资源管理
- Redis或Memcached缓存层集成
- 负载分配的读副本配置
- 数据库监控和告警设置
- 慢查询识别和解决
- 内存分配和缓冲区调优

## 企业架构 / Enterprise Architecture
- 多租户数据库设计模式
- 数据湖和数据仓库架构
- ETL/ELT管道设计和优化
- 零停机数据库迁移策略
- 合规要求（GDPR、HIPAA、SOX）实施
- 数据血缘追踪和审计轨迹
- 跨数据库连接优化技术
- 数据库版本控制和模式演进管理
- 灾难恢复测试和故障转移程序

设计高效扩展的数据库系统，同时保持数据完整性和最佳性能。
专注于架构决策的前瞻性和实施健壮的监控。

-- 1. 什么是索引？
-- 就像书的目录，帮你快速找到内容
CREATEINDEX idx_user_email ONusers(email);
-- 查询时：不用翻遍整本书，直接通过目录找到页码

-- 2. 什么是分区？
-- 就像把文件按月份放在不同文件夹
CREATETABLE orders_2024_01 PARTITIONOF orders
FORVALUESFROM ('2024-01-01') TO ('2024-02-01');
-- 查询1月数据时，只需要打开1月的文件夹

-- 3. 什么是主从复制？
-- 就像复印机，主库是原件，从库是复印件
-- 主库：处理写入
-- 从库：处理读取，分担压力

-- 4. 什么是事务？
-- 就像银行转账，要么全部成功，要么全部失败
BEGINTRANSACTION;
UPDATE accounts SET balance = balance - 100WHEREid = 1;  -- 扣款
UPDATE accounts SET balance = balance + 100WHEREid = 2;  -- 加款
COMMIT;  -- 两个操作都成功才提交

# 在Claude Code中输入
/agents

设计一个支持亿级用户的社交平台数据库，包含用户、帖子、评论、点赞功能

-- 用户表（分表策略）
CREATETABLE users_{0-99} (
    idBIGINT PRIMARY KEY,
    username VARCHAR(50) UNIQUENOTNULL,
    email VARCHAR(255) UNIQUENOTNULL,
    avatar_url VARCHAR(500),
    bio TEXT,
    followers_count INTDEFAULT0,
    following_count INTDEFAULT0,
    posts_count INTDEFAULT0,
    created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
    
    INDEX idx_username (username),
    INDEX idx_created (created_at DESC)
) ENGINE=InnoDB;

-- 帖子表（按时间分区）
CREATETABLE posts (
    idBIGINT AUTO_INCREMENT,
    user_id BIGINTNOTNULL,
    contentTEXTNOTNULL,
    media_urls JSON,
    likes_count INTDEFAULT0,
    comments_count INTDEFAULT0,
    shares_count INTDEFAULT0,
    visibility ENUM('public', 'friends', 'private') DEFAULT'public',
    created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
    
    PRIMARY KEY (id, created_at),
    INDEX idx_user_posts (user_id, created_at DESC),
    INDEX idx_hot_posts (likes_count DESC, created_at DESC),
    FULLTEXT idx_content (content)
) PARTITIONBYRANGE (UNIX_TIMESTAMP(created_at)) (
    -- 按月分区
);

-- Redis缓存策略
-- 热门帖子：ZSET hot:posts score=likes_count
-- 用户时间线：LIST timeline:user:{id}
-- 计数器：HINCRBY counters:post:{id} likes 1

为实时聊天系统设计MongoDB数据结构

// MongoDB聊天系统设计

// 1. 用户集合
db.users.createIndex({ "username": 1 }, { unique: true });
db.users.createIndex({ "email": 1 }, { unique: true });
db.users.createIndex({ "lastActive": -1 });

// 用户文档结构
{
_id: ObjectId(),
username: "john_doe",
email: "john@example.com",
profile: {
    avatar: "https://...",
    status: "online",
    bio: "Hello world"
  },
contacts: [
    { userId: ObjectId(), addedAt: ISODate() }
  ],
settings: {
    notifications: true,
    privacy: "friends"
  },
lastActive: ISODate(),
createdAt: ISODate()
}

// 2. 消息集合（分片）
db.messages.createIndex({ "conversationId": 1, "timestamp": -1 });
db.messages.createIndex({ "sender": 1, "timestamp": -1 });
db.messages.createIndex({ "timestamp": 1 }, { expireAfterSeconds: 7776000 }); // 90天自动过期

// 消息文档结构
{
_id: ObjectId(),
conversationId: ObjectId(),
sender: ObjectId(),
recipients: [ObjectId()],
content: {
    type: "text|image|video|file",
    text: "Hello!",
    media: { url: "...", thumbnail: "..." }
  },
status: {
    sent: ISODate(),
    delivered: [{ userId: ObjectId(), at: ISODate() }],
    read: [{ userId: ObjectId(), at: ISODate() }]
  },
timestamp: ISODate()
}

// 3. 对话集合
db.conversations.createIndex({ "participants": 1 });
db.conversations.createIndex({ "lastMessageAt": -1 });

// 聚合管道：获取用户的对话列表
db.conversations.aggregate([
  { $match: { participants: userId } },
  { $sort: { lastMessageAt: -1 } },
  { $lookup: {
      from: "messages",
      let: { convId: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$conversationId", "$$convId"] } } },
        { $sort: { timestamp: -1 } },
        { $limit: 1 }
      ],
      as: "lastMessage"
  }},
  { $lookup: {
      from: "users",
      localField: "participants",
      foreignField: "_id",
      as: "participantDetails"
  }},
  { $project: {
      participants: 1,
      lastMessage: { $arrayElemAt: ["$lastMessage", 0] },
      participantDetails: {
        $filter: {
          input: "$participantDetails",
          as: "user",
          cond: { $ne: ["$$user._id", userId] }
        }
      },
      unreadCount: 1
  }}
]);

## Database Expertise
- 分库分表：ShardingSphere
- 读写分离：ProxySQL
- 缓存策略：Redis Cluster
- 搜索引擎：Elasticsearch
- 消息队列：Kafka

## Database Expertise
- 数据仓库：Snowflake
- 实时分析：ClickHouse
- 流处理：Apache Flink
- 数据湖：Delta Lake
- OLAP引擎：Apache Druid

## Company Standards
- 命名规范：snake_case
- 主键策略：UUID v4
- 时间字段：UTC时区
- 字符集：utf8mb4
- 备份策略：每日全量+实时增量