风哥教程参考DB2官方文档E-commerce Solutions、Scalability、Performance等内容，详细介绍电商系统库在DB2上的设计、商品管理、购物车、订单支付。更多视频教程www.fgedu.net.cn
目录大纲
Part01-电商系统特点
1.1 电商业务特点
电商系统具有以下特点：
- 高并发：大促期间并发量巨大
- 数据量大：商品、订单、用户数据量巨大
- 热点商品：爆款商品访问量极高
- 事务严格：库存扣减、订单创建一致性要求高
- 读写分离：读多写少，需要读写分离
- 分库分表：数据量大，需要分库分表
Part02-核心表结构设计
2.1 商品表设计
CREATE TABLE PRODUCT (
PRODUCT_ID VARCHAR(32) NOT NULL,
PRODUCT_CODE VARCHAR(50) NOT NULL,
PRODUCT_NAME VARCHAR(200) NOT NULL,
CATEGORY_ID VARCHAR(20),
BRAND_ID VARCHAR(20),
ORIGIN_PRICE DECIMAL(10, 2),
SALE_PRICE DECIMAL(10, 2) NOT NULL,
STOCK_QUANTITY INTEGER DEFAULT 0,
SALES_COUNT INTEGER DEFAULT 0,
VIEW_COUNT INTEGER DEFAULT 0,
PRODUCT_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID)
) ORGANIZE BY ROW;
# SKU表
CREATE TABLE SKU (
SKU_ID VARCHAR(32) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
SKU_CODE VARCHAR(50) NOT NULL,
SKU_ATTRS VARCHAR(500),
SKU_PRICE DECIMAL(10, 2) NOT NULL,
STOCK_QUANTITY INTEGER DEFAULT 0,
SKU_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_SKU PRIMARY KEY (SKU_ID)
) ORGANIZE BY ROW;
# 创建索引
CREATE UNIQUE INDEX IDX_PRODUCT_CODE ON PRODUCT(PRODUCT_CODE);
CREATE INDEX IDX_PRODUCT_CATEGORY ON PRODUCT(CATEGORY_ID, PRODUCT_STATUS);
CREATE INDEX IDX_PRODUCT_STATUS ON PRODUCT(PRODUCT_STATUS, SALES_COUNT DESC);
CREATE INDEX IDX_SKU_PRODUCT ON SKU(PRODUCT_ID);
2.2 用户表设计
CREATE TABLE USER (
USER_ID VARCHAR(32) NOT NULL,
USERNAME VARCHAR(50) NOT NULL,
PASSWORD VARCHAR(100) NOT NULL,
NICKNAME VARCHAR(100),
PHONE VARCHAR(20),
EMAIL VARCHAR(100),
AVATAR VARCHAR(500),
GENDER VARCHAR(10),
BIRTH_DATE DATE,
USER_LEVEL VARCHAR(20),
POINTS INTEGER DEFAULT 0,
BALANCE DECIMAL(10, 2) DEFAULT 0,
TOTAL_ORDER_COUNT INTEGER DEFAULT 0,
TOTAL_ORDER_AMOUNT DECIMAL(10, 2) DEFAULT 0,
LAST_LOGIN_TIME TIMESTAMP,
USER_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_USER PRIMARY KEY (USER_ID)
) ORGANIZE BY ROW;
# 用户地址表
CREATE TABLE USER_ADDRESS (
ADDRESS_ID VARCHAR(32) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
RECEIVER_NAME VARCHAR(100) NOT NULL,
RECEIVER_PHONE VARCHAR(20) NOT NULL,
PROVINCE VARCHAR(50),
CITY VARCHAR(50),
DISTRICT VARCHAR(50),
DETAIL_ADDRESS VARCHAR(500),
POSTAL_CODE VARCHAR(10),
IS_DEFAULT CHAR(1) DEFAULT ‘N’,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_USER_ADDRESS PRIMARY KEY (ADDRESS_ID)
) ORGANIZE BY ROW;
# 创建索引
CREATE UNIQUE INDEX IDX_USER_USERNAME ON USER(USERNAME);
CREATE INDEX IDX_USER_PHONE ON USER(PHONE);
CREATE INDEX IDX_USER_ADDRESS_USER ON USER_ADDRESS(USER_ID);
Part03-购物车与订单
3.1 购物车
CREATE TABLE CART (
CART_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
USER_ID VARCHAR(32) NOT NULL,
SKU_ID VARCHAR(32) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
QUANTITY INTEGER NOT NULL DEFAULT 1,
SELECTED CHAR(1) DEFAULT ‘Y’,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CART PRIMARY KEY (CART_ID)
) ORGANIZE BY ROW;
# 创建索引
CREATE UNIQUE INDEX IDX_CART_USER_SKU ON CART(USER_ID, SKU_ID);
# 订单表
CREATE TABLE ORDER (
ORDER_ID VARCHAR(32) NOT NULL,
ORDER_NO VARCHAR(50) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
TOTAL_AMOUNT DECIMAL(10, 2) NOT NULL,
DISCOUNT_AMOUNT DECIMAL(10, 2) DEFAULT 0,
FREIGHT_AMOUNT DECIMAL(10, 2) DEFAULT 0,
ACTUAL_AMOUNT DECIMAL(10, 2) NOT NULL,
PAYMENT_STATUS VARCHAR(20) NOT NULL,
ORDER_STATUS VARCHAR(20) NOT NULL,
RECEIVER_NAME VARCHAR(100),
RECEIVER_PHONE VARCHAR(20),
RECEIVER_ADDRESS VARCHAR(500),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ORDER PRIMARY KEY (ORDER_ID)
) ORGANIZE BY ROW;
# 订单明细表
CREATE TABLE ORDER_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
ORDER_ID VARCHAR(32) NOT NULL,
SKU_ID VARCHAR(32) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
PRODUCT_NAME VARCHAR(200) NOT NULL,
SKU_ATTRS VARCHAR(500),
QUANTITY INTEGER NOT NULL,
UNIT_PRICE DECIMAL(10, 2) NOT NULL,
TOTAL_AMOUNT DECIMAL(10, 2) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (DETAIL_ID)
) ORGANIZE BY ROW;
# 创建索引
CREATE UNIQUE INDEX IDX_ORDER_NO ON ORDER(ORDER_NO);
CREATE INDEX IDX_ORDER_USER ON ORDER(USER_ID, CREATE_TIME DESC);
CREATE INDEX IDX_ORDER_STATUS ON ORDER(ORDER_STATUS, CREATE_TIME DESC);
CREATE INDEX IDX_ORDER_DETAIL_ORDER ON ORDER_DETAIL(ORDER_ID);
Part04-支付与评价
4.1 支付
CREATE TABLE PAYMENT (
PAYMENT_ID VARCHAR(32) NOT NULL,
ORDER_ID VARCHAR(32) NOT NULL,
PAYMENT_NO VARCHAR(50) NOT NULL,
PAYMENT_TYPE VARCHAR(20) NOT NULL,
PAYMENT_AMOUNT DECIMAL(10, 2) NOT NULL,
PAYMENT_STATUS VARCHAR(20) NOT NULL,
THIRD_PARTY_NO VARCHAR(100),
PAYMENT_TIME TIMESTAMP,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PAYMENT PRIMARY KEY (PAYMENT_ID)
) ORGANIZE BY ROW;
# 评价表
CREATE TABLE REVIEW (
REVIEW_ID VARCHAR(32) NOT NULL,
ORDER_ID VARCHAR(32) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
SKU_ID VARCHAR(32),
RATING INTEGER NOT NULL,
CONTENT VARCHAR(2000),
IMAGES VARCHAR(2000),
REVIEW_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_REVIEW PRIMARY KEY (REVIEW_ID)
) ORGANIZE BY ROW;
# 创建索引
CREATE INDEX IDX_PAYMENT_ORDER ON PAYMENT(ORDER_ID);
CREATE INDEX IDX_REVIEW_PRODUCT ON REVIEW(PRODUCT_ID, CREATE_TIME DESC);
CREATE INDEX IDX_REVIEW_USER ON REVIEW(USER_ID, CREATE_TIME DESC);
Part05-风哥经验总结与分享
5.1 电商库设计要点
- 商品库存要加锁，防止超卖
- 订单创建要使用事务
- 热点商品要做缓存
- 订单表按时间分区
- 用户和商品表要合理索引
- 大促前要做性能测试
5.2 性能优化建议
| 场景 | 优化方案 |
|---|---|
| 库存扣减 | 行级锁，短事务 |
| 商品查询 | 合理索引，缓存热点数据 |
| 订单查询 | 按时间分区，订单号索引 |
| 大促应对 | 读写分离，分库分表 |
5.3 运维要点
- 大促前做好扩容准备
- 实时监控系统性能
- 定期RUNSTATS和REORG
- 完善的备份和恢复方案
- 监控锁等待和死锁
- 定期归档历史订单
学习交流加群风哥微信: itpux-com
风哥Oracle/MySQL/PostgreSQL/Greenplum/DB2/Redis等数据库培训课程，10年一线实战经验，企业级培训，真正掌握数据库核心技术！
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
