一、开篇故事：小卖部vs大型超市 🏪
小卖部（简单设计）
订单本：
订单号 | 客户 | 商品 | 数量 | 金额 | 时间
特点：
→ 一页纸记录所有信息
→ 查询方便
→ 但数据量大后翻页慢
→ 一个商品占一行（多商品怎么办？）
大型超市（复杂设计）
订单主表：
订单号 | 客户 | 总金额 | 时间 | 状态
订单明细表：
明细号 | 订单号 | 商品 | 数量 | 单价
客户表：
客户ID | 姓名 | 电话 | 地址
商品表：
商品ID | 名称 | 价格 | 库存
特点：
→ 数据分离，职责清晰
→ 避免冗余
→ 查询需要JOIN
→ 但数据量大时更高效
这就是订单表设计的权衡：范式化 vs 反范式化！
二、订单表设计的核心挑战 🎯
2.1 业务特点
1. 数据量大
→ 日订单量：百万级
→ 年订单量：数亿级
2. 读多写少
→ 下单：写入
→ 查询订单、统计：大量读取
3. 数据不可变
→ 订单一旦生成，很少修改
→ 只能取消，不能删除
4. 查询场景多
→ 用户查自己的订单
→ 商家查订单
→ 客服查订单
→ 财务统计
→ 数据分析
5. 实时性要求高
→ 下单后立即可查
→ 状态变化实时更新
2.2 设计目标
✅ 性能：
- 写入快（下单不能慢）
- 查询快（各种查询场景）
✅ 可扩展性：
- 数据量增长时性能不下降
- 支持分库分表
✅ 数据一致性：
- 订单数据准确
- 库存扣减不能错
✅ 易维护：
- 表结构清晰
- 业务逻辑简单
三、订单表设计方案 📋
3.1 基础方案：主从表结构
表结构
-- 订单主表（orders）
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
order_no VARCHAR(50) NOT NULL COMMENT '订单号',
user_id BIGINT NOT NULL COMMENT '用户ID',
total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总金额',
pay_amount DECIMAL(10,2) NOT NULL COMMENT '实付金额',
discount_amount DECIMAL(10,2) DEFAULT 0 COMMENT '优惠金额',
status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态：0待付款 1已付款 2已发货 3已完成 4已取消',
pay_type TINYINT COMMENT '支付方式：1微信 2支付宝 3银行卡',
pay_time DATETIME COMMENT '支付时间',
delivery_time DATETIME COMMENT '发货时间',
finish_time DATETIME COMMENT '完成时间',
cancel_time DATETIME COMMENT '取消时间',
remark VARCHAR(500) COMMENT '备注',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
UNIQUE KEY uk_order_no (order_no),
KEY idx_user_id_time (user_id, create_time),
KEY idx_status_time (status, create_time),
KEY idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';
-- 订单明细表（order_items）
CREATE TABLE order_items (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
order_id BIGINT NOT NULL COMMENT '订单ID',
order_no VARCHAR(50) NOT NULL COMMENT '订单号（冗余）',
product_id BIGINT NOT NULL COMMENT '商品ID',
product_name VARCHAR(200) NOT NULL COMMENT '商品名称（冗余）',
product_image VARCHAR(500) COMMENT '商品图片（冗余）',
product_price DECIMAL(10,2) NOT NULL COMMENT '商品单价',
quantity INT NOT NULL COMMENT '购买数量',
total_price DECIMAL(10,2) NOT NULL COMMENT '小计金额',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
KEY idx_order_id (order_id),
KEY idx_order_no (order_no),
KEY idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';
-- 订单扩展表（order_ext）（可选）
CREATE TABLE order_ext (
order_id BIGINT PRIMARY KEY COMMENT '订单ID',
receiver_name VARCHAR(50) COMMENT '收货人',
receiver_phone VARCHAR(20) COMMENT '收货电话',
receiver_province VARCHAR(50) COMMENT '省',
receiver_city VARCHAR(50) COMMENT '市',
receiver_district VARCHAR(50) COMMENT '区',
receiver_address VARCHAR(500) COMMENT '详细地址',
invoice_type TINYINT COMMENT '发票类型',
invoice_title VARCHAR(200) COMMENT '发票抬头',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单扩展信息表';
设计亮点
✅ 主从表分离
- orders: 核心信息，查询频繁
- order_items: 明细信息，支持多商品
- order_ext: 扩展信息，不常查询
✅ 数据冗余
- order_items中冗余product_name、product_image
- 原因：商品信息可能变化，订单要保留下单时的信息
✅ 索引设计
- uk_order_no: 订单号唯一索引（快速查询）
- idx_user_id_time: 用户查自己订单（最左匹配）
- idx_status_time: 按状态查询（运营、客服使用）
- idx_create_time: 按时间范围查询（统计分析）
✅ 字段设计
- 使用BIGINT作为主键（支持大数据量）
- 金额使用DECIMAL（避免精度丢失）
- 时间字段分离（pay_time、delivery_time等，便于统计）
- create_time和update_time自动更新
3.2 优化方案：冗余设计（反范式化）⭐⭐⭐⭐⭐
问题
-- 查询用户订单，需要显示商品信息
SELECT
o.order_no,
o.total_amount,
o.create_time,
p.product_name, -- 需要JOIN products表
p.product_image
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 12345;
-- 问题：
-- → 需要JOIN 3张表
-- → 如果分库分表，可能跨库JOIN
-- → 性能差
解决方案：数据冗余
-- 在订单表中冗余常用字段
CREATE TABLE orders (
-- ... 基础字段 ...
-- 冗余用户信息（便于商家查询）
user_name VARCHAR(50) COMMENT '用户名（冗余）',
user_phone VARCHAR(20) COMMENT '用户电话（冗余）',
-- 冗余收货信息（便于查询）
receiver_name VARCHAR(50) COMMENT '收货人（冗余）',
receiver_phone VARCHAR(20) COMMENT '收货电话（冗余）',
receiver_address VARCHAR(500) COMMENT '收货地址（冗余）',
-- 冗余商品信息（单品订单）
product_id BIGINT COMMENT '商品ID（单品订单）',
product_name VARCHAR(200) COMMENT '商品名称（单品订单）',
product_image VARCHAR(500) COMMENT '商品图片（单品订单）',
-- 冗余商家信息（便于商家查询自己的订单）
shop_id BIGINT COMMENT '店铺ID',
shop_name VARCHAR(100) COMMENT '店铺名称（冗余）',
-- ...其他字段...
);
-- 查询时不需要JOIN
SELECT
order_no,
total_amount,
create_time,
product_name,
product_image,
receiver_name,
receiver_address
FROM orders
WHERE user_id = 12345;
-- ✅ 单表查询，性能好！
冗余的取舍
✅ 应该冗余的字段：
- 下单时的快照数据（商品名称、价格、图片）
- 查询频繁的字段（用户名、收货地址）
- 关联表的关键字段（shop_name）
❌ 不应该冗余的字段：
- 会频繁变化的字段（商品库存）
- 很少查询的字段
- 大字段（商品详情等）
3.3 分库分表设计 ⭐⭐⭐⭐⭐
分表策略
-- 按时间分表（推荐）
orders_202401
orders_202402
orders_202403
...
-- 或者按用户ID分库分表
-- 库：db0, db1, db2, db3（按user_id % 4）
-- 表：每个库下orders_0, orders_1, ... orders_255（按user_id % 256）
-- ShardingSphere配置
spring:
shardingsphere:
rules:
sharding:
tables:
orders:
actual-data-nodes: db${0..3}.orders_${0..255}
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: db-mod
table-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: table-mod
sharding-algorithms:
db-mod:
type: MOD
props:
sharding-count: 4
table-mod:
type: MOD
props:
sharding-count: 256
分表后的查询
-- ✅ 带user_id查询（可以路由到具体分片）
SELECT * FROM orders WHERE user_id = 12345 AND status = 1;
-- → 路由到 db1.orders_123
-- ❌ 不带user_id查询（需要扫描所有分片）
SELECT * FROM orders WHERE order_no = 'O202401150001';
-- → 需要查询所有1024个分片（4库×256表）
-- 解决方案1：订单号中包含user_id
-- O{userId}_{timestamp}_{random}
-- O12345_1705298400_001
-- 解决方案2：单独的订单号路由表
CREATE TABLE order_route (
order_no VARCHAR(50) PRIMARY KEY,
user_id BIGINT NOT NULL,
shard_key INT NOT NULL COMMENT '分片键',
KEY idx_user_id (user_id)
) ENGINE=InnoDB;
-- 先查路由表获取user_id，再查订单表
3.4 读写分离 ⭐⭐⭐⭐
架构：
主库（Master）: 处理写入（下单、更新状态）
从库（Slave1）: 处理查询（用户查订单）
从库（Slave2）: 处理统计（数据分析）
好处：
→ 读写分离，减轻主库压力
→ 从库可以针对查询优化（不同索引）
// Spring配置
@Configuration
public class DataSourceConfig {
@Bean
public DataSource masterDataSource() {
// 主库配置
}
@Bean
public DataSource slave1DataSource() {
// 从库1配置
}
@Bean
public DataSource slave2DataSource() {
// 从库2配置
}
@Bean
public DataSource routingDataSource() {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("master", masterDataSource());
targetDataSources.put("slave1", slave1DataSource());
targetDataSources.put("slave2", slave2DataSource());
DynamicDataSource dataSource = new DynamicDataSource();
dataSource.setTargetDataSources(targetDataSources);
dataSource.setDefaultTargetDataSource(masterDataSource());
return dataSource;
}
}
// 使用
@Service
public class OrderService {
@Transactional
@DataSource("master")
public void createOrder(Order order) {
// 写入主库
orderMapper.insert(order);
}
@DataSource("slave1")
public List<Order> getUserOrders(Long userId) {
// 从从库查询
return orderMapper.selectByUserId(userId);
}
}
四、索引设计详解 📊
4.1 核心索引
-- 1. 订单号唯一索引（最重要）
UNIQUE KEY uk_order_no (order_no)
-- 使用场景：
-- - 按订单号查询（最频繁）
-- - 防止重复下单
-- 2. 用户+时间联合索引（用户查询）
KEY idx_user_id_time (user_id, create_time)
-- 使用场景：
-- - 用户查自己的订单
-- - 按时间倒序排列
-- - 支持分页
-- 3. 状态+时间联合索引（运营查询）
KEY idx_status_time (status, create_time)
-- 使用场景：
-- - 查询待发货订单
-- - 查询待付款订单（超时取消）
-- 4. 时间索引（统计分析）
KEY idx_create_time (create_time)
-- 使用场景：
-- - 统计某时间段订单量
-- - 数据归档
4.2 可选索引
-- 5. 店铺+时间索引（商家查询）
KEY idx_shop_id_time (shop_id, create_time)
-- 使用场景：
-- - 商家查看自己的订单
-- 6. 用户+状态+时间索引（覆盖索引）
KEY idx_user_status_time (user_id, status, create_time, order_no, total_amount)
-- 使用场景：
-- - 用户查询某状态的订单
-- - 覆盖索引，不需要回表
-- 注意：索引不是越多越好
-- - 每个索引占用空间
-- - 每次写入都要更新索引
-- - 维护成本高
4.3 索引使用案例
-- 案例1：用户查询自己的订单
SELECT order_no, total_amount, status, create_time
FROM orders
WHERE user_id = 12345
ORDER BY create_time DESC
LIMIT 10;
-- 使用索引：idx_user_id_time
-- 分析：
-- 1. 通过idx_user_id_time快速定位user_id=12345的范围
-- 2. 索引已经按create_time排序，直接返回前10条
-- 3. 需要回表获取order_no、total_amount、status
-- 优化（覆盖索引）:
CREATE INDEX idx_user_time_cover (user_id, create_time, order_no, total_amount, status);
-- 现在不需要回表，性能提升 ✅
-- 案例2：查询待支付订单（超时取消）
SELECT order_no, user_id, create_time
FROM orders
WHERE status = 0
AND create_time < DATE_SUB(NOW(), INTERVAL 30 MINUTE);
-- 使用索引：idx_status_time
-- 分析：
-- 1. 通过idx_status_time快速定位status=0的范围
-- 2. 在status=0的范围内，create_time是有序的
-- 3. 范围查询create_time < xxx
-- 案例3：按订单号查询
SELECT * FROM orders WHERE order_no = 'O202401150001';
-- 使用索引：uk_order_no（唯一索引）
-- 分析：
-- 1. 直接通过订单号定位，最快
-- 2. 时间复杂度：O(log n)
五、性能优化技巧 🚀
5.1 订单号设计
方案1：雪花算法（推荐）
→ 分布式唯一ID
→ 趋势递增（有序，对B+树友好）
→ 包含时间戳（可反推时间）
示例：1705298400123456789
↑ 时间戳 | 机器ID | 序列号
方案2：自定义规则
→ {日期}{用户ID后4位}{时间戳后6位}{随机3位}
→ 20240115{1234}{298400}{001}
→ 可读性好，包含业务信息
示例：O20240115123429840001
↑ 日期 | 用户ID | 时间戳 | 序列
优点：
✅ 订单号中包含分片键（user_id）
✅ 可以直接路由到具体分片
✅ 减少跨库查询
5.2 状态机设计
// 订单状态枚举
public enum OrderStatus {
WAIT_PAY(0, "待付款"),
PAID(1, "已付款"),
DELIVERED(2, "已发货"),
FINISHED(3, "已完成"),
CANCELLED(4, "已取消"),
REFUND(5, "退款中"),
REFUNDED(6, "已退款");
private final int code;
private final String desc;
}
// 状态机
public class OrderStateMachine {
// 允许的状态转换
private static final Map<OrderStatus, Set<OrderStatus>> ALLOWED_TRANSITIONS =
Map.of(
WAIT_PAY, Set.of(PAID, CANCELLED),
PAID, Set.of(DELIVERED, REFUND, CANCELLED),
DELIVERED, Set.of(FINISHED, REFUND),
FINISHED, Set.of(REFUND),
REFUND, Set.of(REFUNDED, FINISHED)
);
public boolean canTransition(OrderStatus from, OrderStatus to) {
return ALLOWED_TRANSITIONS.getOrDefault(from, Set.of()).contains(to);
}
public void updateStatus(Order order, OrderStatus newStatus) {
if (!canTransition(order.getStatus(), newStatus)) {
throw new BusinessException("非法的状态转换");
}
order.setStatus(newStatus);
// 记录状态变更时间
switch (newStatus) {
case PAID:
order.setPayTime(new Date());
break;
case DELIVERED:
order.setDeliveryTime(new Date());
break;
case FINISHED:
order.setFinishTime(new Date());
break;
case CANCELLED:
order.setCancelTime(new Date());
break;
}
orderMapper.updateById(order);
}
}
5.3 缓存优化
@Service
public class OrderService {
@Autowired
private RedisTemplate<String, Order> redisTemplate;
@Autowired
private OrderMapper orderMapper;
// 查询订单（带缓存）
public Order getOrderByNo(String orderNo) {
String cacheKey = "order:" + orderNo;
// 1. 先查Redis
Order order = redisTemplate.opsForValue().get(cacheKey);
if (order != null) {
return order; // 缓存命中
}
// 2. 查数据库
order = orderMapper.selectByOrderNo(orderNo);
if (order != null) {
// 3. 写入缓存（30分钟过期）
redisTemplate.opsForValue().set(
cacheKey,
order,
30,
TimeUnit.MINUTES
);
}
return order;
}
// 更新订单（删除缓存）
@Transactional
public void updateOrder(Order order) {
// 1. 更新数据库
orderMapper.updateById(order);
// 2. 删除缓存（Cache Aside模式）
String cacheKey = "order:" + order.getOrderNo();
redisTemplate.delete(cacheKey);
}
}
5.4 分页优化
// 问题：深分页很慢
SELECT * FROM orders
WHERE user_id = 12345
ORDER BY create_time DESC
LIMIT 10000, 10;
-- 需要扫描10010行，只返回10行，浪费！
// 解决方案1：记录lastId（推荐）
SELECT * FROM orders
WHERE user_id = 12345
AND id < #{lastId} -- 上次查询的最后一条ID
ORDER BY id DESC
LIMIT 10;
-- 只扫描10行，性能好 ✅
// 解决方案2：二次查询（先查ID）
// 第一步：查ID（索引覆盖，快）
SELECT id FROM orders
WHERE user_id = 12345
ORDER BY create_time DESC
LIMIT 10000, 10;
-- 假设返回ID：[100, 99, 98, ...]
// 第二步：根据ID查完整数据
SELECT * FROM orders WHERE id IN (100, 99, 98, ...);
5.5 批量插入优化
// ❌ 不好的做法（一条一条插入）
for (OrderItem item : items) {
orderItemMapper.insert(item);
}
// 假设100个商品，需要100次数据库交互
// ✅ 好的做法（批量插入）
orderItemMapper.batchInsert(items);
// 只需要1次数据库交互
// MyBatis实现
<insert id="batchInsert" parameterType="list">
INSERT INTO order_items
(order_id, order_no, product_id, product_name, quantity, total_price)
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.orderId}, #{item.orderNo}, #{item.productId},
#{item.productName}, #{item.quantity}, #{item.totalPrice})
</foreach>
</insert>
六、数据归档策略 📦
问题：
→ 订单表数据量越来越大
→ 查询变慢
→ 备份耗时
解决方案：数据归档
策略：
1. 热数据（近3个月）：保留在主表
2. 温数据（3-12个月）：归档到历史表
3. 冷数据（1年以上）：归档到OSS或数据仓库
-- 主表（热数据）
CREATE TABLE orders (
-- ... 字段同上 ...
) ENGINE=InnoDB;
-- 历史表（温数据）
CREATE TABLE orders_history (
-- ... 字段同上 ...
) ENGINE=InnoDB;
-- 归档脚本（定时任务执行）
-- 1. 将3个月前的订单移到历史表
INSERT INTO orders_history
SELECT * FROM orders
WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);
-- 2. 删除主表中的数据
DELETE FROM orders
WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH)
LIMIT 10000; -- 分批删除，避免锁表
-- 3. 冷数据导出到OSS
SELECT * FROM orders_history
WHERE create_time < DATE_SUB(NOW(), INTERVAL 12 MONTH)
INTO OUTFILE '/data/orders_cold_2023.csv';
-- 4. 删除历史表中的冷数据
DELETE FROM orders_history
WHERE create_time < DATE_SUB(NOW(), INTERVAL 12 MONTH)
LIMIT 10000;
七、实战案例：大促订单表设计 💼
场景
双11大促：
- 日订单量：1000万
- 峰值QPS：10万/秒
- 用户查询订单：高并发
解决方案
-- 1. 分库分表（1024个分片）
-- 4个库 × 256个表
-- 2. 订单主表（极简设计，只保留核心字段）
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
order_no VARCHAR(50) NOT NULL,
user_id BIGINT NOT NULL,
shop_id BIGINT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
pay_amount DECIMAL(10,2) NOT NULL,
status TINYINT NOT NULL,
pay_type TINYINT,
create_time DATETIME NOT NULL,
pay_time DATETIME,
-- 冗余常用字段（减少JOIN）
user_name VARCHAR(50),
shop_name VARCHAR(100),
product_name VARCHAR(200), -- 主商品名称
product_image VARCHAR(500), -- 主商品图片
UNIQUE KEY uk_order_no (order_no),
KEY idx_user_time (user_id, create_time),
KEY idx_shop_time (shop_id, create_time),
KEY idx_create_time (create_time)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; -- 压缩存储
-- 3. 订单扩展表（低频字段）
CREATE TABLE order_ext (
order_id BIGINT PRIMARY KEY,
receiver_info JSON, -- 收货信息（JSON存储）
extra_info JSON, -- 扩展信息（JSON存储）
create_time DATETIME
) ENGINE=InnoDB;
-- 4. 订单明细表（独立分片）
CREATE TABLE order_items (
id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
order_no VARCHAR(50) NOT NULL,
product_id BIGINT NOT NULL,
product_name VARCHAR(200),
quantity INT,
total_price DECIMAL(10,2),
KEY idx_order_id (order_id),
KEY idx_order_no (order_no)
) ENGINE=InnoDB;
缓存策略
// 多级缓存
public Order getOrder(String orderNo) {
// L1: 本地缓存（Caffeine，1分钟）
Order order = localCache.get(orderNo);
if (order != null) {
return order;
}
// L2: Redis缓存（30分钟）
order = redisCache.get(orderNo);
if (order != null) {
localCache.put(orderNo, order);
return order;
}
// L3: 数据库
order = orderMapper.selectByOrderNo(orderNo);
if (order != null) {
redisCache.set(orderNo, order, 30, TimeUnit.MINUTES);
localCache.put(orderNo, order);
}
return order;
}
八、面试高频问题 🎤
Q1: 订单表如何设计？
答：
- 主从表分离：orders主表 + order_items明细表 + order_ext扩展表
- 数据冗余：冗余商品名称、价格（订单快照）、收货地址等
- 索引设计：uk_order_no、idx_user_id_time、idx_status_time
- 分库分表：按user_id哈希分片，或按时间分表
- 读写分离：主库写、从库读
Q2: 为什么要冗余商品信息？
答： 因为商品信息可能会变化（价格调整、名称修改），但订单要保留下单时的快照。如果不冗余，查询订单时JOIN商品表，可能显示当前价格而非下单时价格，数据不准确。
Q3: 订单号如何设计？
答： 推荐雪花算法或自定义规则：
- 雪花算法：分布式唯一、趋势递增
- 自定义：{日期}{用户ID}{时间戳}{序列号}，如O20240115123429840001
- 好处：包含时间信息、包含分片键、可读性好
Q4: 如何优化订单查询性能？
答：
- 索引优化：建立覆盖索引，避免回表
- 分库分表：减少单表数据量
- 缓存：Redis缓存热点订单
- 读写分离：查询走从库
- 数据归档：定期归档历史订单
- 分页优化：使用lastId代替offset
Q5: 订单表数据量过大如何处理？
答：
- 分库分表：水平拆分，按user_id或时间
- 数据归档：将历史数据归档到历史表或OSS
- 冷热分离：热数据MySQL，冷数据HBase/OSS
- 表压缩：使用ROW_FORMAT=COMPRESSED
- 索引优化：删除不必要的索引
九、总结口诀 📝
订单表设计学问多，
主从分离职责清。
数据冗余有必要，
快照信息要保存。
索引设计要合理，
用户查询最频繁。
订单号、用户ID，
状态时间也重要。
分库分表少不了，
按用户ID来哈希。
读写分离提性能，
主库写来从库读。
缓存优化效果好，
Redis本地两级缓。
数据归档定期做，
冷热分离是关键！
参考资料 📚
下期预告： 151-MySQL的online DDL原理和注意事项 🔧
编写时间：2025年
作者：技术文档小助手 ✍️
版本：v1.0
愿你的订单表设计如艺术般完美！ 🎨✨
