电商数据库设计

电商数据库设计实战

电商系统是 MySQL 应用最广泛的场景之一，涵盖商品管理、库存扣减、订单处理、支付流水等核心模块。本章从真实业务出发，设计高并发、可扩展的数据库方案，并深入分析每个设计决策的原因。

核心模块关系图

用户 (user) ──┐
              ├── 地址 (address)
              ├── 购物车 (cart_item)
              └── 订单 (order) ──── 订单商品 (order_item)
                                         │
商品 (product) ─── SKU (sku) ────────────┘
       │                 └── 库存 (inventory)
       ├── 属性 (product_attr)
       └── 分类 (category)

商品模型

电商商品的核心挑战：SPU（标准产品单元）与 SKU（库存单元）的分离，以及灵活的属性扩展（颜色/尺寸/型号）。

-- SPU: 商品基本信息（如"耐克跑鞋 AirMax 2024"）
CREATE TABLE product (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id INT UNSIGNED    NOT NULL,
    name        VARCHAR(200)    NOT NULL,
    brand       VARCHAR(100),
    description TEXT,
    main_image  VARCHAR(500),
    status      TINYINT         NOT NULL DEFAULT 1 COMMENT '1=在售 0=下架 2=预售',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_category (category_id),
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SKU: 具体销售单元（如"耐克 AirMax 红色 42码"）
CREATE TABLE sku (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_id  BIGINT UNSIGNED NOT NULL,
    sku_code    VARCHAR(64)     NOT NULL COMMENT '商家 SKU 编号',
    attrs       JSON            NOT NULL COMMENT '{"color":"红色","size":"42"}',
    price       DECIMAL(10,2)   NOT NULL COMMENT '单位：元',
    cost_price  DECIMAL(10,2)   COMMENT '成本价',
    image       VARCHAR(500),
    sort_order  TINYINT         NOT NULL DEFAULT 0,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_sku_code (sku_code),
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

设计决策：SKU 的属性（颜色、尺寸等）使用 JSON 列存储，而不是 EAV 模式（实体-属性-值）。EAV 虽然灵活，但查询效率极低，JOIN 层数多；JSON 列在 MySQL 8.0+ 有虚拟列和函数索引支持，在灵活性和查询性能之间取得最佳平衡。

商品分类（支持无限层级）

CREATE TABLE category (
    id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=顶级',
    name      VARCHAR(50)  NOT NULL,
    path      VARCHAR(500) NOT NULL COMMENT '路径：/1/5/12/' ,
    level     TINYINT      NOT NULL DEFAULT 1,
    sort      INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    INDEX idx_parent (parent_id),
    INDEX idx_path (path(100))  -- 前缀索引，支持 path LIKE '/1/5/%'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查询某分类及其所有子分类
SELECT * FROM category WHERE path LIKE '/1/5/%';

库存扣减

库存扣减是电商系统并发控制的核心难题：超卖（overselling）会导致严重的业务损失。

CREATE TABLE inventory (
    sku_id      BIGINT UNSIGNED NOT NULL,
    warehouse   VARCHAR(32)     NOT NULL DEFAULT 'default',
    total_stock INT             NOT NULL DEFAULT 0 COMMENT '总库存',
    locked_stock INT            NOT NULL DEFAULT 0 COMMENT '已锁定（已下单未支付）',
    sold_stock  INT             NOT NULL DEFAULT 0 COMMENT '已售出',
    version     INT             NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (sku_id, warehouse),
    -- available = total_stock - locked_stock - sold_stock
    CHECK (total_stock >= 0 AND locked_stock >= 0 AND sold_stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

下单时锁定库存（乐观锁）

-- 方案 1: 乐观锁（适合并发不极高的场景）
UPDATE inventory
SET    locked_stock = locked_stock + ?,
       version      = version + 1
WHERE  sku_id    = ?
  AND  warehouse = 'default'
  AND  version   = ?    -- 乐观锁版本检查
  AND  (total_stock - locked_stock - sold_stock) >= ?; -- 可用库存检查
-- 受影响行数为 0 → 库存不足或被并发修改，需重试或报错

-- 方案 2: 直接减法（推荐，原子操作）
UPDATE inventory
SET    locked_stock = locked_stock + ?
WHERE  sku_id = ?
  AND  (total_stock - locked_stock - sold_stock) >= ?;
-- 依赖 InnoDB 行锁保证原子性，无需 version 字段

秒杀场景需要 Redis 预扣库存：高并发秒杀时，数据库库存扣减会变成热点行，大量 UPDATE 排队等锁。正确做法：Redis 中存储库存数量，用 DECR 原子扣减，成功后异步写数据库。详见后文"秒杀场景"。

订单模型

CREATE TABLE `order` (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_no     VARCHAR(32)     NOT NULL COMMENT '业务订单号 (雪花ID)',
    user_id      BIGINT UNSIGNED NOT NULL,
    status       TINYINT         NOT NULL DEFAULT 10
                 COMMENT '10=待支付 20=已支付 30=已发货 40=已收货 50=已完成 -10=已取消',
    total_amount DECIMAL(10,2)   NOT NULL COMMENT '订单总金额',
    pay_amount   DECIMAL(10,2)   NOT NULL COMMENT '实付金额（减优惠后）',
    freight      DECIMAL(8,2)    NOT NULL DEFAULT 0.00 COMMENT '运费',
    coupon_id    BIGINT UNSIGNED COMMENT '使用的优惠券 ID',
    discount     DECIMAL(8,2)    NOT NULL DEFAULT 0.00 COMMENT '优惠金额',
    remark       VARCHAR(500),
    pay_time     DATETIME        COMMENT '支付时间',
    ship_time    DATETIME        COMMENT '发货时间',
    finish_time  DATETIME        COMMENT '完成时间',
    cancel_time  DATETIME        COMMENT '取消时间',
    expire_time  DATETIME        COMMENT '支付超时时间',
    address_snapshot JSON        NOT NULL COMMENT '收货地址快照（JSON）',
    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_order_no (order_no),
    INDEX idx_user_status  (user_id, status, created_at),
    INDEX idx_status_expire (status, expire_time) COMMENT '定时任务查询超时未支付订单'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT '订单主表'
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

CREATE TABLE order_item (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id   BIGINT UNSIGNED NOT NULL,
    order_no   VARCHAR(32)     NOT NULL,
    sku_id     BIGINT UNSIGNED NOT NULL,
    sku_code   VARCHAR(64)     NOT NULL,
    product_name VARCHAR(200)  NOT NULL COMMENT '下单时商品名称快照',
    sku_image  VARCHAR(500),
    attrs      JSON            COMMENT '下单时 SKU 属性快照',
    price      DECIMAL(10,2)   NOT NULL COMMENT '下单时单价快照',
    quantity   INT             NOT NULL,
    subtotal   DECIMAL(10,2)   NOT NULL COMMENT 'price * quantity',
    PRIMARY KEY (id),
    INDEX idx_order_id (order_id),
    INDEX idx_sku_id (sku_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

为什么冗余 address_snapshot 和商品名称快照？：地址和商品信息随时可能变更，订单必须固化下单时的状态。不冗余的话，用户修改地址或商家改价后，历史订单会显示错误信息，引发纠纷。这是电商设计的黄金法则：订单数据永远不能依赖关联查询获取最新数据。

支付流水

CREATE TABLE payment_transaction (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_no      VARCHAR(32)     NOT NULL,
    txn_no        VARCHAR(64)     NOT NULL COMMENT '第三方支付流水号',
    pay_channel   TINYINT         NOT NULL COMMENT '1=微信 2=支付宝 3=银行卡',
    amount        DECIMAL(10,2)   NOT NULL,
    status        TINYINT         NOT NULL DEFAULT 0 COMMENT '0=待支付 1=成功 2=失败 3=退款中 4=已退款',
    notify_data   JSON            COMMENT '第三方回调原始数据',
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at       DATETIME,
    PRIMARY KEY (id),
    UNIQUE KEY uk_txn_no (txn_no),
    INDEX idx_order_no (order_no),
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 支付成功后，在同一事务中：
BEGIN;
  UPDATE payment_transaction SET status = 1, paid_at = NOW() WHERE txn_no = ?;
  UPDATE `order` SET status = 20, pay_time = NOW(), pay_amount = ? WHERE order_no = ?;
  UPDATE inventory SET locked_stock = locked_stock - ?,
                       sold_stock   = sold_stock + ?
         WHERE sku_id = ?;
COMMIT;

秒杀场景设计

-- 秒杀活动表
CREATE TABLE flash_sale (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    sku_id      BIGINT UNSIGNED NOT NULL,
    sale_price  DECIMAL(10,2) NOT NULL COMMENT '秒杀价',
    total_stock INT           NOT NULL,
    sold_count  INT           NOT NULL DEFAULT 0,
    per_limit   TINYINT       NOT NULL DEFAULT 1 COMMENT '每人限购',
    start_time  DATETIME      NOT NULL,
    end_time    DATETIME      NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_sku_time (sku_id, start_time, end_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 秒杀用户记录（防止超买）
CREATE TABLE flash_sale_record (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sale_id     INT UNSIGNED    NOT NULL,
    user_id     BIGINT UNSIGNED NOT NULL,
    order_no    VARCHAR(32)     NOT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_sale_user (sale_id, user_id)  -- 防重
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

// Go 秒杀核心逻辑（Redis 预扣 + 异步落库）
func FlashSale(ctx context.Context, saleID int, userID int64) error {
    key := fmt.Sprintf("flash:stock:%d", saleID)
    userKey := fmt.Sprintf("flash:users:%d", saleID)

    // 1. 检查用户是否已抢购（SISMEMBER O(1)）
    if rdb.SIsMember(ctx, userKey, userID).Val() {
        return ErrAlreadyBought
    }

    // 2. Lua 脚本原子性执行：扣库存 + 记录用户
    script := redis.NewScript(`
        local stock = redis.call('GET', KEYS[1])
        if tonumber(stock) <= 0 then return -1 end
        redis.call('DECR', KEYS[1])
        redis.call('SADD', KEYS[2], ARGV[1])
        return 1
    `)
    result, err := script.Run(ctx, rdb, []string{key, userKey}, userID).Int()
    if err != nil || result == -1 {
        return ErrSoldOut
    }

    // 3. 异步创建订单（发 MQ）
    mq.Publish("flash-sale-orders", FlashSaleOrderMsg{
        SaleID: saleID, UserID: userID,
    })
    return nil
}

用户地址

CREATE TABLE user_address (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id      BIGINT UNSIGNED NOT NULL,
    receiver     VARCHAR(50)     NOT NULL COMMENT '收货人姓名',
    mobile       VARCHAR(20)     NOT NULL,
    province     VARCHAR(20)     NOT NULL,
    city         VARCHAR(20)     NOT NULL,
    district     VARCHAR(20)     NOT NULL,
    detail       VARCHAR(200)    NOT NULL COMMENT '详细地址',
    zip_code     CHAR(6),
    is_default   TINYINT(1)      NOT NULL DEFAULT 0,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 确保每个用户只有一个默认地址（应用层控制 + 数据库约束）
-- 设置新默认地址前先清除旧默认
UPDATE user_address SET is_default = 0 WHERE user_id = ?;
UPDATE user_address SET is_default = 1 WHERE id = ? AND user_id = ?;

电商场景索引策略总结

分库分表时机：订单表单库超过 5000 万行、写 TPS 持续 >1000 时考虑按 user_id 取模分库，保证同一用户订单在同一库（方便用户维度查询）。商品表通常不需要分，百万级 SKU 加好索引完全够用。