电商系统核心表结构设计与优化实践-恒创新域建站

★ FEATURED ARTICLE

1. 电商系统表结构设计概述电商平台的核心表结构设计是整个系统的基础骨架直接影响着业务扩展性、数据一致性和系统性能。一套合理的表结构设计能够支撑日均百万级订单处理同时保持清晰的业务逻辑关系。我在多个电商项目中积累的经验表明前期表结构设计的合理性决定了后期80%的技术债务。新手开发者常犯的错误是直接照搬开源电商系统的表结构却忽略了自身业务的特殊性。比如跨境电商和本地电商在库存管理、物流跟踪上的差异或者内容电商与传统货架电商在商品展示维度的不同需求。本文将基于标准电商业务模型拆解用户、商品、订单三大核心模块的表结构设计要点。2. 用户模块设计2.1 用户主表结构用户表(user)是电商系统的起点需要平衡查询效率与扩展性。基础字段包括CREATE TABLE user ( user_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 用户ID, username varchar(64) NOT NULL COMMENT 登录账号, password_hash varchar(255) NOT NULL COMMENT 加密密码, mobile varchar(20) DEFAULT NULL COMMENT 手机号, email varchar(128) DEFAULT NULL COMMENT 邮箱, status tinyint(4) NOT NULL DEFAULT 1 COMMENT 状态(0-禁用 1-正常), created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (user_id), UNIQUE KEY idx_username (username), KEY idx_mobile (mobile) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户主表;关键设计要点密码必须存储哈希值而非明文手机号/邮箱建议做唯一索引但允许NULL第三方登录用户可能缺失使用utf8mb4字符集支持emoji等特殊符号2.2 用户扩展信息用户画像数据建议采用垂直分表设计避免主表字段膨胀CREATE TABLE user_profile ( user_id bigint(20) NOT NULL COMMENT 关联用户ID, real_name varchar(64) DEFAULT NULL COMMENT 真实姓名, gender tinyint(4) DEFAULT NULL COMMENT 性别(0-未知 1-男 2-女), birthday date DEFAULT NULL COMMENT 出生日期, id_card varchar(32) DEFAULT NULL COMMENT 身份证号, avatar varchar(255) DEFAULT NULL COMMENT 头像URL, region_code varchar(20) DEFAULT NULL COMMENT 行政区划代码, address varchar(255) DEFAULT NULL COMMENT 详细地址, PRIMARY KEY (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户档案表;2.3 用户收货地址收货地址需要独立设计以支持多地址管理CREATE TABLE user_address ( address_id bigint(20) NOT NULL AUTO_INCREMENT, user_id bigint(20) NOT NULL COMMENT 所属用户, receiver_name varchar(64) NOT NULL COMMENT 收货人, receiver_mobile varchar(20) NOT NULL COMMENT 联系电话, region_code varchar(20) NOT NULL COMMENT 行政区划代码, address_detail varchar(255) NOT NULL COMMENT 详细地址, is_default tinyint(1) NOT NULL DEFAULT 0 COMMENT 是否默认(0-否 1-是), created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (address_id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户收货地址;避坑指南行政区划使用国家标准代码而非纯文本便于后续数据分析默认地址标记需要保证每个用户只有一个默认地址通过程序逻辑控制3. 商品模块设计3.1 商品SPU/SKU模型电商商品采用SPU(标准产品单元)SKU(库存量单位)两级结构CREATE TABLE product_spu ( spu_id bigint(20) NOT NULL AUTO_INCREMENT, spu_code varchar(64) NOT NULL COMMENT SPU编码, title varchar(128) NOT NULL COMMENT 商品标题, subtitle varchar(255) DEFAULT NULL COMMENT 副标题, category_id bigint(20) NOT NULL COMMENT 分类ID, brand_id bigint(20) DEFAULT NULL COMMENT 品牌ID, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 状态(0-下架 1-上架), created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (spu_id), UNIQUE KEY idx_spu_code (spu_code), KEY idx_category (category_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品SPU表; CREATE TABLE product_sku ( sku_id bigint(20) NOT NULL AUTO_INCREMENT, spu_id bigint(20) NOT NULL COMMENT 关联SPU, sku_code varchar(64) NOT NULL COMMENT SKU编码, specs json NOT NULL COMMENT 规格属性(JSON格式), price decimal(10,2) NOT NULL COMMENT 销售价, cost_price decimal(10,2) DEFAULT NULL COMMENT 成本价, stock int(11) NOT NULL DEFAULT 0 COMMENT 库存, stock_warn int(11) DEFAULT 10 COMMENT 库存预警值, weight decimal(10,3) DEFAULT NULL COMMENT 重量(kg), volume decimal(10,3) DEFAULT NULL COMMENT 体积(m³), barcode varchar(64) DEFAULT NULL COMMENT 条形码, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 状态(0-禁用 1-启用), PRIMARY KEY (sku_id), UNIQUE KEY idx_sku_code (sku_code), KEY idx_spu_id (spu_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品SKU表;3.2 商品分类与属性分类设计采用无限级树形结构CREATE TABLE product_category ( category_id bigint(20) NOT NULL AUTO_INCREMENT, parent_id bigint(20) NOT NULL DEFAULT 0 COMMENT 父分类ID, name varchar(64) NOT NULL COMMENT 分类名称, level tinyint(4) NOT NULL COMMENT 分类层级, sort int(11) NOT NULL DEFAULT 0 COMMENT 排序权重, icon varchar(255) DEFAULT NULL COMMENT 图标URL, is_display tinyint(1) NOT NULL DEFAULT 1 COMMENT 是否显示, PRIMARY KEY (category_id), KEY idx_parent (parent_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品分类表; CREATE TABLE product_attribute ( attr_id bigint(20) NOT NULL AUTO_INCREMENT, category_id bigint(20) NOT NULL COMMENT 所属分类, attr_name varchar(64) NOT NULL COMMENT 属性名称, attr_type tinyint(4) NOT NULL COMMENT 属性类型(1-销售属性 2-关键属性 3-普通属性), input_type tinyint(4) NOT NULL COMMENT 输入类型(1-输入框 2-单选 3-多选 4-下拉框), optional_values text DEFAULT NULL COMMENT 可选值列表(逗号分隔), is_required tinyint(1) NOT NULL DEFAULT 0 COMMENT 是否必填, sort int(11) NOT NULL DEFAULT 0 COMMENT 排序, PRIMARY KEY (attr_id), KEY idx_category (category_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品属性表;3.3 商品图片与详情多媒体信息单独存储CREATE TABLE product_image ( image_id bigint(20) NOT NULL AUTO_INCREMENT, spu_id bigint(20) NOT NULL COMMENT 关联SPU, sku_id bigint(20) DEFAULT NULL COMMENT 关联SKU(NULL表示SPU通用图), img_url varchar(255) NOT NULL COMMENT 图片URL, img_type tinyint(4) NOT NULL COMMENT 图片类型(1-主图 2-详情图 3-白底图 4-场景图), sort int(11) NOT NULL DEFAULT 0 COMMENT 排序, PRIMARY KEY (image_id), KEY idx_spu (spu_id), KEY idx_sku (sku_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品图片表; CREATE TABLE product_detail ( spu_id bigint(20) NOT NULL COMMENT 关联SPU, detail_html text COMMENT 商品详情HTML, detail_text text COMMENT 商品详情纯文本, after_service text COMMENT 售后服务说明, PRIMARY KEY (spu_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品详情表;实战经验商品图片建议使用CDN加速并配置不同尺寸规格详情HTML和纯文本双存储便于不同场景使用大文本字段建议与主表分离提升查询效率4. 订单模块设计4.1 订单主表结构订单主表需要记录交易核心信息CREATE TABLE order_master ( order_id bigint(20) NOT NULL COMMENT 订单ID(雪花算法生成), order_no varchar(32) NOT NULL COMMENT 订单编号, user_id bigint(20) NOT NULL COMMENT 用户ID, order_status tinyint(4) NOT NULL DEFAULT 0 COMMENT 订单状态(0-待支付 1-已支付 2-已发货 3-已完成 4-已关闭 5-已退款), pay_amount decimal(10,2) NOT NULL COMMENT 实付金额, total_amount decimal(10,2) NOT NULL COMMENT 订单总额, freight_amount decimal(10,2) DEFAULT 0.00 COMMENT 运费, discount_amount decimal(10,2) DEFAULT 0.00 COMMENT 优惠金额, pay_type tinyint(4) DEFAULT NULL COMMENT 支付方式(1-支付宝 2-微信 3-银联), pay_time datetime DEFAULT NULL COMMENT 支付时间, delivery_time datetime DEFAULT NULL COMMENT 发货时间, receive_time datetime DEFAULT NULL COMMENT 收货时间, close_time datetime DEFAULT NULL COMMENT 关闭时间, source tinyint(4) DEFAULT 1 COMMENT 订单来源(1-PC 2-APP 3-小程序 4-H5), note 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, PRIMARY KEY (order_id), UNIQUE KEY idx_order_no (order_no), KEY idx_user_id (user_id), KEY idx_create_time (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单主表;4.2 订单商品明细订单商品需要快照当时信息CREATE TABLE order_item ( item_id bigint(20) NOT NULL AUTO_INCREMENT, order_id bigint(20) NOT NULL COMMENT 订单ID, sku_id bigint(20) NOT NULL COMMENT 商品SKU, sku_code varchar(64) NOT NULL COMMENT SKU编码, sku_name varchar(128) NOT NULL COMMENT SKU名称, sku_specs json DEFAULT NULL COMMENT SKU规格(JSON格式), sku_price decimal(10,2) NOT NULL COMMENT 购买单价, quantity int(11) NOT NULL COMMENT 购买数量, actual_price decimal(10,2) NOT NULL COMMENT 实付单价, discount_amount decimal(10,2) DEFAULT 0.00 COMMENT 优惠金额, coupon_amount decimal(10,2) DEFAULT 0.00 COMMENT 优惠券抵扣, gift_growth int(11) DEFAULT 0 COMMENT 赠送成长值, gift_integral int(11) DEFAULT 0 COMMENT 赠送积分, PRIMARY KEY (item_id), KEY idx_order_id (order_id), KEY idx_sku_id (sku_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单商品明细;4.3 订单收货信息收货信息独立存储便于物流管理CREATE TABLE order_delivery ( order_id bigint(20) NOT NULL COMMENT 订单ID, receiver_name varchar(64) NOT NULL COMMENT 收货人, receiver_mobile varchar(20) NOT NULL COMMENT 联系电话, region_code varchar(20) NOT NULL COMMENT 行政区划代码, address_detail varchar(255) NOT NULL COMMENT 详细地址, delivery_company varchar(64) DEFAULT NULL COMMENT 物流公司, delivery_no varchar(64) DEFAULT NULL COMMENT 物流单号, delivery_time datetime DEFAULT NULL COMMENT 发货时间, receive_time datetime DEFAULT NULL COMMENT 签收时间, PRIMARY KEY (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单收货信息;4.4 订单操作日志完整记录订单状态变更CREATE TABLE order_operation_log ( log_id bigint(20) NOT NULL AUTO_INCREMENT, order_id bigint(20) NOT NULL COMMENT 订单ID, operator varchar(64) NOT NULL COMMENT 操作人, operation varchar(32) NOT NULL COMMENT 操作类型, note varchar(255) DEFAULT NULL COMMENT 操作备注, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (log_id), KEY idx_order_id (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单操作日志;关键设计原则订单号建议使用日期随机数生成如2023081500123456订单商品必须保存快照而非关联实时SKU金额字段统一使用DECIMAL(10,2)避免浮点精度问题状态变更必须记录完整操作日志5. 扩展模块设计5.1 购物车设计购物车需要区分登录/未登录状态CREATE TABLE cart_item ( cart_id bigint(20) NOT NULL AUTO_INCREMENT, user_id bigint(20) DEFAULT NULL COMMENT 用户ID(未登录为NULL), session_id varchar(64) DEFAULT NULL COMMENT 会话ID(未登录用户), sku_id bigint(20) NOT NULL COMMENT 商品SKU, quantity int(11) NOT NULL DEFAULT 1 COMMENT 数量, selected tinyint(1) NOT NULL DEFAULT 1 COMMENT 是否选中, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (cart_id), KEY idx_user (user_id), KEY idx_session (session_id), KEY idx_sku (sku_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT购物车;5.2 优惠券系统优惠券需要设计领取规则和使用限制CREATE TABLE coupon_template ( template_id bigint(20) NOT NULL AUTO_INCREMENT, name varchar(64) NOT NULL COMMENT 优惠券名称, type tinyint(4) NOT NULL COMMENT 类型(1-满减 2-折扣 3-立减), rule json NOT NULL COMMENT 优惠规则(JSON格式), platform tinyint(4) NOT NULL DEFAULT 0 COMMENT 使用平台(0-全平台 1-PC 2-移动端), category_ids varchar(255) DEFAULT NULL COMMENT 适用分类(逗号分隔), product_ids varchar(255) DEFAULT NULL COMMENT 适用商品(逗号分隔), start_time datetime NOT NULL COMMENT 有效期开始, end_time datetime NOT NULL COMMENT 有效期结束, total_count int(11) NOT NULL COMMENT 发放总量, limit_count int(11) NOT NULL DEFAULT 1 COMMENT 每人限领, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 状态(0-未开始 1-进行中 2-已结束), created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (template_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT优惠券模板; CREATE TABLE coupon_user ( coupon_id bigint(20) NOT NULL AUTO_INCREMENT, template_id bigint(20) NOT NULL COMMENT 模板ID, user_id bigint(20) NOT NULL COMMENT 用户ID, code varchar(32) NOT NULL COMMENT 优惠券码, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 状态(0-未使用 1-已使用 2-已过期), order_id bigint(20) DEFAULT NULL COMMENT 使用订单ID, used_time datetime DEFAULT NULL COMMENT 使用时间, start_time datetime NOT NULL COMMENT 有效期开始, end_time datetime NOT NULL COMMENT 有效期结束, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (coupon_id), UNIQUE KEY idx_code (code), KEY idx_user (user_id,status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户优惠券;6. 分库分表策略当订单量达到百万级时需要考虑分库分表6.1 水平分表方案按订单ID哈希分表-- 订单表分表规则按order_id的哈希值分16张表 CREATE TABLE order_master_0 LIKE order_master; CREATE TABLE order_master_1 LIKE order_master; ... CREATE TABLE order_master_f LIKE order_master;6.2 历史订单归档冷热数据分离设计-- 归档表结构与主表相同但使用归档存储引擎 CREATE TABLE order_master_archive ( ... ) ENGINEARCHIVE DEFAULT CHARSETutf8mb4 COMMENT订单归档表;6.3 全局ID生成使用雪花算法生成分布式IDpublic class SnowflakeIdGenerator { private final long datacenterId; // 数据中心ID private final long machineId; // 机器ID private long sequence 0L; // 序列号 private long lastTimestamp -1L; // 上次时间戳 public synchronized long nextId() { long timestamp timeGen(); if (timestamp lastTimestamp) { throw new RuntimeException(时钟回拨异常); } if (lastTimestamp timestamp) { sequence (sequence 1) 0xFFF; if (sequence 0) { timestamp tilNextMillis(lastTimestamp); } } else { sequence 0L; } lastTimestamp timestamp; return ((timestamp - 1288834974657L) 22) | (datacenterId 17) | (machineId 12) | sequence; } }7. 性能优化实践7.1 索引优化策略关键查询必须走索引-- 商品搜索常用组合索引 ALTER TABLE product_spu ADD INDEX idx_search (category_id, status, created_at); -- 订单查询组合索引 ALTER TABLE order_master ADD INDEX idx_user_status (user_id, order_status);7.2 字段类型选择精确数值使用DECIMAL-- 金额字段必须使用DECIMAL price DECIMAL(10,2) NOT NULL COMMENT 价格 -- 大文本使用TEXT并分离存储 description TEXT COMMENT 商品描述7.3 查询优化示例避免全表扫描-- 反例使用OR导致索引失效 SELECT * FROM orders WHERE status 1 OR user_id 100; -- 正例使用UNION ALL优化 SELECT * FROM orders WHERE status 1 UNION ALL SELECT * FROM orders WHERE user_id 100 AND status ! 1;8. 常见问题解决方案8.1 库存超卖问题使用乐观锁控制库存UPDATE product_sku SET stock stock - 1, version version 1 WHERE sku_id 123 AND stock 1 AND version 5;8.2 订单状态同步使用状态机模式保证状态流转正确public enum OrderStatus { UNPAID(0), PAID(1), DELIVERED(2), COMPLETED(3), CLOSED(4), REFUNDED(5); private static final MapInteger, OrderStatus transitions Map.of( 0, Set.of(1, 4), 1, Set.of(2, 4, 5), 2, Set.of(3, 4, 5), 3, Collections.emptySet(), 4, Collections.emptySet(), 5, Collections.emptySet() ); public static boolean canTransition(int from, int to) { return transitions.getOrDefault(from, Collections.emptySet()).contains(to); } }8.3 分布式事务处理使用最终一致性方案// 订单创建分布式事务示例 Transactional public void createOrder(OrderDTO orderDTO) { // 1. 创建订单主记录 orderMapper.insert(order); // 2. 发送扣减库存消息 rocketMQTemplate.sendMessageInTransaction( stock_topic, MessageBuilder.withPayload(order.getItems()).build(), null ); // 3. 记录事务日志 transactionLogService.savePrepareLog(); }

阅读完成 · 觉得有帮助？

咨询建站