高效能表设计（电商场景实战）-优快云博客

本文链接：https://blog.youkuaiyun.com/futurn_hero/article/details/146013649

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

前言
业务要求
核心表设计（MySQL 示例）
设计思路解析
查询示例
总结

前言

以下是一个电商场景下的订单表设计 Demo，包含核心设计思路、字段说明和索引策略。重点体现业务驱动、扩展性、性能优化等原则

业务要求

核心功能：记录用户下单信息、商品快照、支付状态、物流信息

扩展需求：支持订单状态流转、优惠券使用、售后记录

性能要求：高频查询用户最新订单、商家按时间范围查订单

核心表设计（MySQL 示例）

-- 订单主表（存储核心交易信息）
CREATE TABLE `order` (
  -- 主键 & 唯一标识
  `order_id` BIGINT UNSIGNED AUTO_INCREMENT COMMENT '订单ID（自增主键）',
  `order_no` VARCHAR(32) NOT NULL COMMENT '订单号（业务唯一，如20240501123456）',
  
  -- 基础业务字段
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `merchant_id` BIGINT UNSIGNED NOT NULL COMMENT '商家ID',
  `total_amount` DECIMAL(10,2) NOT NULL COMMENT '订单总金额（含优惠）',
  `pay_amount` DECIMAL(10,2) NOT NULL COMMENT '实际支付金额',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1-待支付,2-已支付,3-已发货,4-已完成,5-已取消',
  
  -- 时间相关字段
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `pay_time` DATETIME COMMENT '支付时间',
  `delivery_time` DATETIME COMMENT '发货时间',
  
  -- 扩展性设计
  `coupon_info` JSON COMMENT '优惠券信息（如{"coupon_id":1001, "discount":50}）',
  `extra_data` JSON COMMENT '扩展字段（预留业务变更）',
  
  -- 索引 & 约束
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_status` (`user_id`,`status`),          -- 高频查询：用户查看订单列表
  KEY `idx_merchant_time` (`merchant_id`,`create_time`) -- 商家按时间查订单
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

-- 订单商品表（存储商品快照，解决多对多关系）
CREATE TABLE `order_item` (
  `item_id` BIGINT UNSIGNED AUTO_INCREMENT,
  `order_id` BIGINT UNSIGNED NOT NULL COMMENT '关联订单ID',
  `product_id` BIGINT NOT NULL COMMENT '商品ID',
  `product_name` VARCHAR(255) NOT NULL COMMENT '商品名称（下单快照）',
  `price` DECIMAL(10,2) NOT NULL COMMENT '单价',
  `quantity` INT NOT NULL COMMENT '购买数量',
  PRIMARY KEY (`item_id`),
  KEY `idx_order_id` (`order_id`)  -- 快速查询订单的商品明细
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品明细表';

-- 订单状态变更日志表（记录状态流转）
CREATE TABLE `order_status_log` (
  `log_id` BIGINT UNSIGNED AUTO_INCREMENT,
  `order_id` BIGINT UNSIGNED NOT NULL,
  `old_status` TINYINT NOT NULL,
  `new_status` TINYINT NOT NULL,
  `operator` VARCHAR(64) COMMENT '操作人（用户/系统）',
  `operate_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单状态变更日志';

设计思路解析

① 业务驱动设计
核心字段：order_no（业务唯一标识）、user_id（用户维度查询）、status（状态机驱动流程）

金额精确存储：使用 DECIMAL(10,2) 避免浮点精度问题

商品快照：order_item 表冗余商品下单时的名称和价格，确保历史数据不受商品信息变更影响

② 扩展性设计
JSON 字段：coupon_info 存储优惠券信息（灵活应对优惠规则变化）

冷热分离：主表存储核心数据，日志表（order_status_log）记录状态变更历史

分表策略：当订单量过大时，可按 merchant_id 分库分表

③ 性能优化
索引策略：

唯一索引 uk_order_no：快速通过订单号定位订单

联合索引 idx_user_status：加速用户查看「待支付/已完成」订单

覆盖索引 idx_merchant_time：商家后台按时间筛选订单

数据归档：可定期将完成超过1年的订单迁移到 order_history 归档表

④ 数据一致性
状态机管理：通过 order_status_log 表记录状态变更轨迹，确保状态流转可追溯

事务控制：创建订单时，需在事务内同时写入 order 和 order_item 表

查询示例

-- 查询用户最近10条待支付订单（命中索引 idx_user_status）
SELECT * FROM `order` 
WHERE user_id = 1001 AND status = 1 
ORDER BY create_time DESC 
LIMIT 10;

-- 查询商家某天的订单（命中索引 idx_merchant_time）
SELECT order_id, total_amount, create_time 
FROM `order` 
WHERE merchant_id = 2001 
  AND create_time BETWEEN '2024-05-01 00:00:00' AND '2024-05-01 23:59:59';