数据库设计与优化

1. 数据库选型

1.1 关系型数据库

MySQL 8.0 - 核心业务数据

用途	说明
用户信息	账号、KYC、安全设置
订单数据	委托订单、成交记录
账户资产	余额、流水、锁定
仓位信息	合约持仓、保证金

优势：

ACID事务保证
成熟的主从复制
丰富的索引类型
完善的生态工具

1.2 NoSQL数据库

Redis - 缓存和实时数据

用途	数据结构	说明
行情数据	String	Ticker、最新价
订单簿	Sorted Set	价格档位排序
K线数据	Hash	多周期K线
用户会话	String	JWT token
限流计数	String	滑动窗口计数

MongoDB - 半结构化数据

用途	说明
操作日志	用户行为、系统日志
配置数据	交易对配置、费率配置
统计数据	日报、月报

InfluxDB - 时序数据

用途	说明
K线数据	多周期历史K线
监控指标	系统性能、业务指标
资金费率	历史资金费率

2. 核心表设计

2.1 用户表

CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    `email` VARCHAR(255) NOT NULL COMMENT '邮箱',
    `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
    `password_hash` VARCHAR(255) NOT NULL COMMENT '密码哈希',

    -- KYC信息
    `kyc_level` TINYINT NOT NULL DEFAULT 0 COMMENT 'KYC等级: 0-未认证, 1-L1, 2-L2, 3-L3',
    `real_name` VARCHAR(100) DEFAULT NULL COMMENT '真实姓名',
    `id_number` VARCHAR(50) DEFAULT NULL COMMENT '身份证号',
    `country` VARCHAR(10) DEFAULT NULL COMMENT '国家代码',

    -- 安全设置
    `totp_secret` VARCHAR(255) DEFAULT NULL COMMENT '2FA密钥',
    `is_2fa_enabled` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否启用2FA',
    `login_password_updated_at` DATETIME DEFAULT NULL COMMENT '登录密码最后更新时间',
    `trade_password_hash` VARCHAR(255) DEFAULT NULL COMMENT '交易密码哈希',

    -- 状态
    `status` ENUM('active', 'suspended', 'closed') NOT NULL DEFAULT 'active' COMMENT '账户状态',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_email` (`email`),
    UNIQUE KEY `uk_phone` (`phone`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

2.2 账户余额表

CREATE TABLE `account_balances` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `currency` VARCHAR(20) NOT NULL COMMENT '币种',
    `account_type` ENUM('spot', 'margin', 'futures') NOT NULL DEFAULT 'spot' COMMENT '账户类型',

    -- 余额
    `available` DECIMAL(36, 18) NOT NULL DEFAULT 0 COMMENT '可用余额',
    `frozen` DECIMAL(36, 18) NOT NULL DEFAULT 0 COMMENT '冻结余额',

    -- 版本号（乐观锁）
    `version` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '版本号',

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_currency_type` (`user_id`, `currency`, `account_type`),
    KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='账户余额表';

乐观锁更新示例：

-- 冻结余额（下单）
UPDATE account_balances
SET available = available - 100,
    frozen = frozen + 100,
    version = version + 1
WHERE user_id = 1001
  AND currency = 'USDT'
  AND account_type = 'spot'
  AND version = 5        -- 乐观锁
  AND available >= 100;  -- 余额检查

-- 检查affected_rows，如果为0则表示更新失败，需要重试

2.3 资金流水表

CREATE TABLE `account_transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `currency` VARCHAR(20) NOT NULL COMMENT '币种',
    `account_type` ENUM('spot', 'margin', 'futures') NOT NULL COMMENT '账户类型',

    -- 交易信息
    `type` ENUM('deposit', 'withdraw', 'trade', 'fee', 'funding', 'transfer', 'airdrop') NOT NULL COMMENT '交易类型',
    `amount` DECIMAL(36, 18) NOT NULL COMMENT '金额（正数为入账，负数为出账）',
    `balance_after` DECIMAL(36, 18) NOT NULL COMMENT '交易后余额',

    -- 关联信息
    `ref_type` VARCHAR(50) DEFAULT NULL COMMENT '关联类型: order, trade, deposit, withdraw',
    `ref_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '关联ID',

    -- 描述
    `comment` VARCHAR(255) DEFAULT NULL COMMENT '备注',

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_user_currency` (`user_id`, `currency`, `created_at`),
    KEY `idx_ref` (`ref_type`, `ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='资金流水表';

-- 分区策略：按月分区
ALTER TABLE account_transactions PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    -- ... 继续添加分区
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

2.4 订单表

CREATE TABLE `orders` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(64) NOT NULL COMMENT '订单ID（全局唯一）',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `symbol` VARCHAR(20) NOT NULL COMMENT '交易对',

    -- 订单信息
    `side` ENUM('buy', 'sell') NOT NULL COMMENT '买卖方向',
    `type` ENUM('limit', 'market', 'stop_limit', 'stop_market') NOT NULL COMMENT '订单类型',
    `price` DECIMAL(36, 18) DEFAULT NULL COMMENT '价格',
    `quantity` DECIMAL(36, 18) NOT NULL COMMENT '数量',
    `stop_price` DECIMAL(36, 18) DEFAULT NULL COMMENT '止损价',

    -- 成交信息
    `filled_quantity` DECIMAL(36, 18) NOT NULL DEFAULT 0 COMMENT '已成交数量',
    `filled_amount` DECIMAL(36, 18) NOT NULL DEFAULT 0 COMMENT '已成交金额',
    `avg_price` DECIMAL(36, 18) DEFAULT NULL COMMENT '平均成交价',

    -- 手续费
    `fee` DECIMAL(36, 18) NOT NULL DEFAULT 0 COMMENT '手续费',
    `fee_currency` VARCHAR(20) DEFAULT NULL COMMENT '手续费币种',

    -- 状态
    `status` ENUM('pending', 'partial', 'filled', 'cancelled', 'rejected') NOT NULL DEFAULT 'pending' COMMENT '订单状态',
    `time_in_force` ENUM('GTC', 'IOC', 'FOK') NOT NULL DEFAULT 'GTC' COMMENT '有效期',

    -- 时间
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `filled_at` DATETIME DEFAULT NULL COMMENT '完全成交时间',

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_order_id` (`order_id`),
    KEY `idx_user_symbol_status` (`user_id`, `symbol`, `status`, `created_at`),
    KEY `idx_symbol_status` (`symbol`, `status`, `created_at`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

-- 分库分表策略（稍后详述）

2.5 成交表

CREATE TABLE `trades` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `trade_id` VARCHAR(64) NOT NULL COMMENT '成交ID',
    `symbol` VARCHAR(20) NOT NULL COMMENT '交易对',

    -- 订单信息
    `buy_order_id` VARCHAR(64) NOT NULL COMMENT '买单ID',
    `sell_order_id` VARCHAR(64) NOT NULL COMMENT '卖单ID',
    `buy_user_id` BIGINT UNSIGNED NOT NULL COMMENT '买方用户ID',
    `sell_user_id` BIGINT UNSIGNED NOT NULL COMMENT '卖方用户ID',

    -- 成交信息
    `price` DECIMAL(36, 18) NOT NULL COMMENT '成交价',
    `quantity` DECIMAL(36, 18) NOT NULL COMMENT '成交量',
    `amount` DECIMAL(36, 18) NOT NULL COMMENT '成交额',

    -- Maker/Taker
    `buyer_is_maker` BOOLEAN NOT NULL COMMENT '买方是否为Maker',

    -- 手续费
    `buy_fee` DECIMAL(36, 18) NOT NULL COMMENT '买方手续费',
    `sell_fee` DECIMAL(36, 18) NOT NULL COMMENT '卖方手续费',

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_trade_id` (`trade_id`),
    KEY `idx_symbol_created` (`symbol`, `created_at`),
    KEY `idx_buy_user` (`buy_user_id`, `created_at`),
    KEY `idx_sell_user` (`sell_user_id`, `created_at`),
    KEY `idx_buy_order` (`buy_order_id`),
    KEY `idx_sell_order` (`sell_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='成交表';

2.6 合约仓位表

CREATE TABLE `positions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `symbol` VARCHAR(20) NOT NULL COMMENT '合约',

    -- 仓位信息
    `side` ENUM('long', 'short') NOT NULL COMMENT '多空方向',
    `quantity` DECIMAL(36, 18) NOT NULL COMMENT '持仓量',
    `entry_price` DECIMAL(36, 18) NOT NULL COMMENT '开仓均价',
    `mark_price` DECIMAL(36, 18) NOT NULL COMMENT '标记价格',
    `liquidation_price` DECIMAL(36, 18) NOT NULL COMMENT '强平价格',

    -- 保证金
    `leverage` INT NOT NULL COMMENT '杠杆倍数',
    `margin_mode` ENUM('isolated', 'cross') NOT NULL COMMENT '保证金模式',
    `initial_margin` DECIMAL(36, 18) NOT NULL COMMENT '起始保证金',
    `maintenance_margin` DECIMAL(36, 18) NOT NULL COMMENT '维持保证金',
    `margin` DECIMAL(36, 18) NOT NULL COMMENT '仓位保证金',

    -- 盈亏
    `unrealized_pnl` DECIMAL(36, 18) NOT NULL DEFAULT 0 COMMENT '未实现盈亏',
    `realized_pnl` DECIMAL(36, 18) NOT NULL DEFAULT 0 COMMENT '已实现盈亏',

    -- 版本号
    `version` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '版本号',

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_symbol` (`user_id`, `symbol`),
    KEY `idx_symbol` (`symbol`),
    KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='合约仓位表';

2.7 充值提现表

CREATE TABLE `deposits` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `currency` VARCHAR(20) NOT NULL COMMENT '币种',

    -- 金额
    `amount` DECIMAL(36, 18) NOT NULL COMMENT '充值金额',

    -- 区块链信息
    `chain` VARCHAR(20) NOT NULL COMMENT '链: ETH, BSC, TRX',
    `txid` VARCHAR(255) NOT NULL COMMENT '交易哈希',
    `from_address` VARCHAR(255) NOT NULL COMMENT '发送地址',
    `to_address` VARCHAR(255) NOT NULL COMMENT '充值地址',
    `confirmations` INT NOT NULL DEFAULT 0 COMMENT '确认数',
    `required_confirmations` INT NOT NULL COMMENT '所需确认数',

    -- 状态
    `status` ENUM('pending', 'confirming', 'completed', 'failed') NOT NULL DEFAULT 'pending',

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `completed_at` DATETIME DEFAULT NULL,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_txid_currency` (`txid`, `currency`),
    KEY `idx_user_status` (`user_id`, `status`, `created_at`),
    KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充值表';

CREATE TABLE `withdrawals` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `withdrawal_id` VARCHAR(64) NOT NULL COMMENT '提现ID',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `currency` VARCHAR(20) NOT NULL COMMENT '币种',

    -- 金额
    `amount` DECIMAL(36, 18) NOT NULL COMMENT '提现金额',
    `fee` DECIMAL(36, 18) NOT NULL COMMENT '手续费',
    `actual_amount` DECIMAL(36, 18) NOT NULL COMMENT '实际到账金额',

    -- 区块链信息
    `chain` VARCHAR(20) NOT NULL COMMENT '链',
    `to_address` VARCHAR(255) NOT NULL COMMENT '提现地址',
    `txid` VARCHAR(255) DEFAULT NULL COMMENT '交易哈希',

    -- 审核
    `status` ENUM('pending', 'reviewing', 'approved', 'processing', 'completed', 'rejected', 'failed') NOT NULL DEFAULT 'pending',
    `reviewer_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '审核员ID',
    `review_comment` VARCHAR(500) DEFAULT NULL COMMENT '审核备注',

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `completed_at` DATETIME DEFAULT NULL,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_withdrawal_id` (`withdrawal_id`),
    KEY `idx_user_status` (`user_id`, `status`, `created_at`),
    KEY `idx_status_created` (`status`, `created_at`),
    KEY `idx_txid` (`txid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='提现表';

3. 分库分表

3.1 分库策略

按用户ID分库，将用户数据分散到多个数据库。

Database Sharding:
┌────────────┐  ┌────────────┐  ┌────────────┐  ┌────────────┐
│   DB_00    │  │   DB_01    │  │   DB_02    │  │   DB_03    │
│ user_id%4=0│  │ user_id%4=1│  │ user_id%4=2│  │ user_id%4=3│
└────────────┘  └────────────┘  └────────────┘  └────────────┘

路由算法：

type DatabaseRouter struct {
    shardCount int
    databases  []*sql.DB
}

func NewDatabaseRouter(shardCount int) *DatabaseRouter {
    router := &DatabaseRouter{
        shardCount: shardCount,
        databases:  make([]*sql.DB, shardCount),
    }

    // 初始化数据库连接
    for i := 0; i < shardCount; i++ {
        dsn := fmt.Sprintf("user:pass@tcp(db-%02d:3306)/exchange", i)
        db, err := sql.Open("mysql", dsn)
        if err != nil {
            panic(err)
        }
        router.databases[i] = db
    }

    return router
}

func (dr *DatabaseRouter) GetDB(userID int64) *sql.DB {
    shardID := int(userID % int64(dr.shardCount))
    return dr.databases[shardID]
}

// 使用示例
func (dr *DatabaseRouter) GetUserBalance(userID int64, currency string) (*Balance, error) {
    db := dr.GetDB(userID)

    var balance Balance
    err := db.QueryRow(`
        SELECT available, frozen FROM account_balances
        WHERE user_id = ? AND currency = ?
    `, userID, currency).Scan(&balance.Available, &balance.Frozen)

    return &balance, err
}

3.2 分表策略

订单表分表：按 symbol 和时间分表

orders_BTCUSDT_202401
orders_BTCUSDT_202402
orders_ETHUSDT_202401
orders_ETHUSDT_202402

分表路由：

type TableRouter struct{}

func (tr *TableRouter) GetOrderTable(symbol string, createTime time.Time) string {
    // 标准化symbol：BTC/USDT -> BTCUSDT
    symbol = strings.ReplaceAll(symbol, "/", "")
    symbol = strings.ToUpper(symbol)

    // 按月分表
    month := createTime.Format("200601")

    return fmt.Sprintf("orders_%s_%s", symbol, month)
}

// 插入订单
func (tr *TableRouter) InsertOrder(db *sql.DB, order *Order) error {
    tableName := tr.GetOrderTable(order.Symbol, order.CreatedAt)

    query := fmt.Sprintf(`
        INSERT INTO %s (order_id, user_id, symbol, side, type, price, quantity, status, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, tableName)

    _, err := db.Exec(query,
        order.OrderID, order.UserID, order.Symbol, order.Side,
        order.Type, order.Price, order.Quantity, order.Status,
        order.CreatedAt,
    )

    return err
}

// 查询订单（需要指定时间范围）
func (tr *TableRouter) QueryOrders(db *sql.DB, userID int64, symbol string,
    startTime, endTime time.Time) ([]*Order, error) {

    // 生成所有需要查询的表
    tables := tr.getTablesInRange(symbol, startTime, endTime)

    var allOrders []*Order

    for _, table := range tables {
        query := fmt.Sprintf(`
            SELECT order_id, symbol, side, type, price, quantity, status, created_at
            FROM %s
            WHERE user_id = ? AND created_at BETWEEN ? AND ?
            ORDER BY created_at DESC
        `, table)

        rows, err := db.Query(query, userID, startTime, endTime)
        if err != nil {
            // 表可能不存在，跳过
            continue
        }
        defer rows.Close()

        for rows.Next() {
            var order Order
            err := rows.Scan(
                &order.OrderID, &order.Symbol, &order.Side, &order.Type,
                &order.Price, &order.Quantity, &order.Status, &order.CreatedAt,
            )
            if err != nil {
                return nil, err
            }
            allOrders = append(allOrders, &order)
        }
    }

    return allOrders, nil
}

func (tr *TableRouter) getTablesInRange(symbol string, start, end time.Time) []string {
    var tables []string

    current := time.Date(start.Year(), start.Month(), 1, 0, 0, 0, 0, time.UTC)
    endMonth := time.Date(end.Year(), end.Month(), 1, 0, 0, 0, 0, time.UTC)

    for current.Before(endMonth) || current.Equal(endMonth) {
        tables = append(tables, tr.GetOrderTable(symbol, current))
        current = current.AddDate(0, 1, 0)
    }

    return tables
}

3.3 全局ID生成

分库分表后，需要全局唯一ID。

Snowflake算法：

type Snowflake struct {
    mu sync.Mutex

    // 配置
    epoch      int64 // 起始时间戳（毫秒）
    workerID   int64 // 机器ID（10bit，支持1024台机器）
    datacenter int64 // 数据中心ID（10bit）

    // 状态
    sequence      int64 // 序列号（12bit，每毫秒4096个ID）
    lastTimestamp int64 // 上次生成ID的时间戳
}

func NewSnowflake(workerID, datacenter int64) *Snowflake {
    return &Snowflake{
        epoch:      1640995200000, // 2022-01-01 00:00:00
        workerID:   workerID,
        datacenter: datacenter,
        sequence:   0,
        lastTimestamp: 0,
    }
}

func (sf *Snowflake) NextID() (int64, error) {
    sf.mu.Lock()
    defer sf.mu.Unlock()

    timestamp := time.Now().UnixNano() / 1e6

    if timestamp < sf.lastTimestamp {
        return 0, fmt.Errorf("clock moved backwards")
    }

    if timestamp == sf.lastTimestamp {
        // 同一毫秒内，序列号递增
        sf.sequence = (sf.sequence + 1) & 0xFFF // 12bit mask
        if sf.sequence == 0 {
            // 序列号用完，等待下一毫秒
            for timestamp <= sf.lastTimestamp {
                timestamp = time.Now().UnixNano() / 1e6
            }
        }
    } else {
        // 新的毫秒，序列号重置
        sf.sequence = 0
    }

    sf.lastTimestamp = timestamp

    // 组装ID：
    // | 1bit (未使用) | 41bit (时间戳) | 10bit (数据中心+机器) | 12bit (序列号) |
    id := ((timestamp - sf.epoch) << 22) |
          (sf.datacenter << 17) |
          (sf.workerID << 12) |
          sf.sequence

    return id, nil
}

// 使用示例
func main() {
    sf := NewSnowflake(1, 1)

    for i := 0; i < 10; i++ {
        id, _ := sf.NextID()
        fmt.Printf("ID: %d\n", id)
    }
}

// 输出：
// ID: 1234567890123456789
// ID: 1234567890123456790
// ...

4. 索引优化

4.1 索引设计原则

1. 选择性高的列优先

-- 好的索引：email唯一性高
CREATE INDEX idx_email ON users(email);

-- 不好的索引：status只有几个值
-- CREATE INDEX idx_status ON users(status); -- 不建议

2. 复合索引遵循最左前缀

-- 复合索引
CREATE INDEX idx_user_symbol_status ON orders(user_id, symbol, status, created_at);

-- 可以利用该索引的查询：
--  WHERE user_id = ?
--  WHERE user_id = ? AND symbol = ?
--  WHERE user_id = ? AND symbol = ? AND status = ?
--  WHERE user_id = ? AND symbol = ? AND status = ? AND created_at > ?

-- 无法利用该索引的查询：
-- ✗ WHERE symbol = ?
-- ✗ WHERE status = ?
-- ✗ WHERE created_at > ?

3. 覆盖索引减少回表

-- 查询订单ID和创建时间
SELECT order_id, created_at FROM orders
WHERE user_id = ? AND symbol = ?;

-- 创建覆盖索引（包含查询所需的所有列）
CREATE INDEX idx_user_symbol_cover ON orders(user_id, symbol, order_id, created_at);

4.2 慢查询优化

案例1：查询用户历史订单

-- 慢查询（全表扫描）
SELECT * FROM orders
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 20;

-- EXPLAIN 分析
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 20;

-- 优化：添加复合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

案例2：统计用户成交量

-- 慢查询（需要扫描大量数据）
SELECT COUNT(*), SUM(amount)
FROM trades
WHERE buy_user_id = 1001 AND created_at >= '2024-01-01';

-- 优化：添加复合索引 + 覆盖索引
CREATE INDEX idx_buy_user_created_amount ON trades(buy_user_id, created_at, amount);

案例3：避免函数索引失效

-- 错误：对索引列使用函数
SELECT * FROM orders
WHERE DATE(created_at) = '2024-01-01';

-- 正确：使用范围查询
SELECT * FROM orders
WHERE created_at >= '2024-01-01 00:00:00'
  AND created_at < '2024-01-02 00:00:00';

5. 查询优化

5.1 避免N+1查询

问题：

// 查询订单（1次查询）
orders := queryOrders(userID)

// 为每个订单查询成交记录（N次查询）
for _, order := range orders {
    trades := queryTradesByOrder(order.OrderID) // N次查询！
    order.Trades = trades
}

优化：

// 一次性查询所有订单
orders := queryOrders(userID)

// 提取所有订单ID
orderIDs := make([]string, len(orders))
for i, order := range orders {
    orderIDs[i] = order.OrderID
}

// 批量查询成交记录（1次查询）
trades := queryTradesByOrderIDs(orderIDs)

// 组装数据
tradeMap := make(map[string][]*Trade)
for _, trade := range trades {
    tradeMap[trade.BuyOrderID] = append(tradeMap[trade.BuyOrderID], trade)
    tradeMap[trade.SellOrderID] = append(tradeMap[trade.SellOrderID], trade)
}

for _, order := range orders {
    order.Trades = tradeMap[order.OrderID]
}

5.2 分页查询优化

深度分页问题：

-- 慢查询：深度分页需要扫描+跳过大量数据
SELECT * FROM orders
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 100000, 20;  -- 需要扫描100020行！

优化：使用游标分页：

-- 第一页
SELECT * FROM orders
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 20;

-- 记录最后一条的created_at和id

-- 第二页（使用WHERE代替OFFSET）
SELECT * FROM orders
WHERE user_id = 1001
  AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT 20;

Go实现：

type OrderCursor struct {
    CreatedAt time.Time
    ID        int64
}

func QueryOrdersWithCursor(db *sql.DB, userID int64, cursor *OrderCursor, limit int) ([]*Order, *OrderCursor, error) {
    var query string
    var args []interface{}

    if cursor == nil {
        // 第一页
        query = `
            SELECT id, order_id, symbol, side, price, quantity, created_at
            FROM orders
            WHERE user_id = ?
            ORDER BY created_at DESC, id DESC
            LIMIT ?
        `
        args = []interface{}{userID, limit}
    } else {
        // 后续页
        query = `
            SELECT id, order_id, symbol, side, price, quantity, created_at
            FROM orders
            WHERE user_id = ?
              AND (created_at < ? OR (created_at = ? AND id < ?))
            ORDER BY created_at DESC, id DESC
            LIMIT ?
        `
        args = []interface{}{userID, cursor.CreatedAt, cursor.CreatedAt, cursor.ID, limit}
    }

    rows, err := db.Query(query, args...)
    if err != nil {
        return nil, nil, err
    }
    defer rows.Close()

    var orders []*Order
    var newCursor *OrderCursor

    for rows.Next() {
        var order Order
        err := rows.Scan(&order.ID, &order.OrderID, &order.Symbol,
            &order.Side, &order.Price, &order.Quantity, &order.CreatedAt)
        if err != nil {
            return nil, nil, err
        }
        orders = append(orders, &order)

        // 更新游标
        newCursor = &OrderCursor{
            CreatedAt: order.CreatedAt,
            ID:        order.ID,
        }
    }

    return orders, newCursor, nil
}

6. 事务处理

6.1 下单事务

func PlaceOrder(db *sql.DB, order *Order) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // 1. 冻结余额（乐观锁）
    result, err := tx.Exec(`
        UPDATE account_balances
        SET available = available - ?,
            frozen = frozen + ?,
            version = version + 1
        WHERE user_id = ?
          AND currency = ?
          AND version = ?
          AND available >= ?
    `, order.Amount, order.Amount, order.UserID, order.Currency,
       order.Version, order.Amount)

    if err != nil {
        return err
    }

    affected, _ := result.RowsAffected()
    if affected == 0 {
        return errors.New("insufficient balance or version conflict")
    }

    // 2. 插入订单
    _, err = tx.Exec(`
        INSERT INTO orders (order_id, user_id, symbol, side, type, price, quantity, status, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, order.OrderID, order.UserID, order.Symbol, order.Side, order.Type,
       order.Price, order.Quantity, "pending", time.Now())

    if err != nil {
        return err
    }

    // 3. 记录流水
    _, err = tx.Exec(`
        INSERT INTO account_transactions (user_id, currency, type, amount, ref_type, ref_id, created_at)
        VALUES (?, ?, 'freeze', ?, 'order', ?, ?)
    `, order.UserID, order.Currency, -order.Amount, order.OrderID, time.Now())

    if err != nil {
        return err
    }

    return tx.Commit()
}

6.2 分布式事务

对于跨库的分布式事务，使用两阶段提交（2PC）或TCC模式。

TCC模式示例（转账）：

type TransferService struct {
    fromDB *sql.DB
    toDB   *sql.DB
}

// Try阶段：冻结资金
func (ts *TransferService) TryTransfer(fromUserID, toUserID int64, amount float64) (string, error) {
    transferID := generateTransferID()

    // 1. 冻结转出方资金
    _, err := ts.fromDB.Exec(`
        UPDATE account_balances
        SET available = available - ?,
            frozen = frozen + ?
        WHERE user_id = ? AND currency = 'USDT' AND available >= ?
    `, amount, amount, fromUserID, amount)

    if err != nil {
        return "", err
    }

    // 2. 记录TCC事务
    _, err = ts.fromDB.Exec(`
        INSERT INTO tcc_transactions (transfer_id, from_user, to_user, amount, status)
        VALUES (?, ?, ?, ?, 'trying')
    `, transferID, fromUserID, toUserID, amount)

    return transferID, err
}

// Confirm阶段：确认转账
func (ts *TransferService) ConfirmTransfer(transferID string) error {
    // 1. 获取转账信息
    var fromUserID, toUserID int64
    var amount float64
    err := ts.fromDB.QueryRow(`
        SELECT from_user, to_user, amount FROM tcc_transactions
        WHERE transfer_id = ? AND status = 'trying'
    `, transferID).Scan(&fromUserID, &toUserID, &amount)

    if err != nil {
        return err
    }

    // 2. 转出方：解冻并扣除
    _, err = ts.fromDB.Exec(`
        UPDATE account_balances
        SET frozen = frozen - ?
        WHERE user_id = ? AND currency = 'USDT'
    `, amount, fromUserID)

    if err != nil {
        return err
    }

    // 3. 转入方：增加余额
    _, err = ts.toDB.Exec(`
        UPDATE account_balances
        SET available = available + ?
        WHERE user_id = ? AND currency = 'USDT'
    `, amount, toUserID)

    if err != nil {
        return err
    }

    // 4. 更新TCC状态
    _, err = ts.fromDB.Exec(`
        UPDATE tcc_transactions SET status = 'confirmed' WHERE transfer_id = ?
    `, transferID)

    return err
}

// Cancel阶段：取消转账
func (ts *TransferService) CancelTransfer(transferID string) error {
    // 1. 获取转账信息
    var fromUserID int64
    var amount float64
    err := ts.fromDB.QueryRow(`
        SELECT from_user, amount FROM tcc_transactions
        WHERE transfer_id = ? AND status = 'trying'
    `, transferID).Scan(&fromUserID, &amount)

    if err != nil {
        return err
    }

    // 2. 解冻资金
    _, err = ts.fromDB.Exec(`
        UPDATE account_balances
        SET available = available + ?,
            frozen = frozen - ?
        WHERE user_id = ? AND currency = 'USDT'
    `, amount, amount, fromUserID)

    if err != nil {
        return err
    }

    // 3. 更新TCC状态
    _, err = ts.fromDB.Exec(`
        UPDATE tcc_transactions SET status = 'cancelled' WHERE transfer_id = ?
    `, transferID)

    return err
}

7. 数据归档与清理

7.1 冷热数据分离

策略：

热数据：最近3个月的订单，存储在MySQL
温数据：3-12个月的订单，存储在只读从库
冷数据：12个月以上的订单，归档到对象存储（S3）

归档脚本：

func ArchiveOldOrders(db *sql.DB, archiveDate time.Time) error {
    // 1. 导出旧订单到CSV
    rows, err := db.Query(`
        SELECT * FROM orders
        WHERE created_at < ? AND status IN ('filled', 'cancelled')
    `, archiveDate)

    if err != nil {
        return err
    }
    defer rows.Close()

    csvFile := fmt.Sprintf("orders_%s.csv", archiveDate.Format("20060102"))
    file, err := os.Create(csvFile)
    if err != nil {
        return err
    }
    defer file.Close()

    writer := csv.NewWriter(file)
    defer writer.Flush()

    for rows.Next() {
        // 写入CSV
        // ... 省略代码
    }

    // 2. 上传到S3
    err = uploadToS3(csvFile, "archive/orders/")
    if err != nil {
        return err
    }

    // 3. 删除已归档数据
    _, err = db.Exec(`
        DELETE FROM orders
        WHERE created_at < ? AND status IN ('filled', 'cancelled')
        LIMIT 10000
    `, archiveDate)

    return err
}

7.2 定期清理

-- 删除3个月前的操作日志
DELETE FROM operation_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH)
LIMIT 10000;

-- 删除1年前的成交记录（保留在归档中）
DELETE FROM trades
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 10000;

小结

本章介绍了交易所数据库设计与优化：

数据库选型：MySQL、Redis、MongoDB、InfluxDB的使用场景
核心表设计：用户、余额、订单、成交、仓位等表结构
分库分表：水平分库、分表路由、全局ID生成
索引优化：复合索引、覆盖索引、慢查询优化
查询优化：避免N+1查询、游标分页
事务处理：本地事务、分布式事务（TCC）
数据归档：冷热分离、定期清理

下一章将讲解缓存与消息队列的设计，包括Redis缓存策略和Kafka消息处理。