建表

warehouse

district

customer

history

new_order

oorder

order_line

stock

索引

10个唯一索引、主键、联合唯一、联合主键

alter table bmsql_warehouse add constraint bmsql_warehouse_pkey primary key (w_id);  
alter table bmsql_district add constraint bmsql_district_pkey primary key (d_w_id, d_id);   
alter table bmsql_customer add constraint bmsql_customer_pkey  primary key (c_w_id, c_d_id, c_id);   
create index bmsql_customer_idx1 on  bmsql_customer (c_w_id, c_d_id, c_last, c_first);   
alter table bmsql_oorder add constraint bmsql_oorder_pkey  primary key (o_w_id, o_d_id, o_id);   
create unique index bmsql_oorder_idx1  on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);  
alter table bmsql_new_order add constraint bmsql_new_order_pkey  primary key (no_w_id, no_d_id, no_o_id);  
alter table bmsql_order_line add constraint bmsql_order_line_pkey  primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);  
alter table bmsql_stock add constraint bmsql_stock_pkey  primary key (s_w_id, s_i_id);  
alter table bmsql_item add constraint bmsql_item_pkey  primary key (i_id);  

外键

10个外键约束

alter table bmsql_district add constraint d_warehouse_fkey  foreign key (d_w_id)  references bmsql_warehouse (w_id);  
alter table bmsql_customer add constraint c_district_fkey   foreign key (c_w_id, c_d_id)    references bmsql_district (d_w_id, d_id);  
alter table bmsql_history add constraint h_customer_fkey  foreign key (h_c_w_id, h_c_d_id, h_c_id)   references bmsql_customer (c_w_id, c_d_id, c_id);  
alter table bmsql_history add constraint h_district_fkey  foreign key (h_w_id, h_d_id)  references bmsql_district (d_w_id, d_id);  
alter table bmsql_new_order add constraint no_order_fkey   foreign key (no_w_id, no_d_id, no_o_id)   references bmsql_oorder (o_w_id, o_d_id, o_id);  
alter table bmsql_oorder add constraint o_customer_fkey  foreign key (o_w_id, o_d_id, o_c_id)   references bmsql_customer (c_w_id, c_d_id, c_id);  
alter table bmsql_order_line add constraint ol_order_fkey  foreign key (ol_w_id, ol_d_id, ol_o_id)   references bmsql_oorder (o_w_id, o_d_id, o_id); 
alter table bmsql_order_line add constraint ol_stock_fkey foreign key (ol_supply_w_id, ol_i_id)  references bmsql_stock (s_w_id, s_i_id);  
alter table bmsql_stock add constraint s_warehouse_fkey  foreign key (s_w_id) references bmsql_warehouse (w_id);  
alter table bmsql_stock add constraint s_item_fkey  foreign key (s_i_id)   references bmsql_item (i_id);  

业务逻辑

新建订单

事务内容：对于任意一个客户端,从固定的仓库随机选取 5-15 件商品,创建新订单.其中 1%的订单要由假想的用户操作失败而回滚。 占比 : 45%

SELECT c_discount, c_last, c_credit, w_tax FROM bmsql_customer JOIN bmsql_warehouse ON (w_id = c_w_id) WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = ? AND d_id = ? FOR UPDATE
UPDATE bmsql_district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = ? AND d_id = ?
INSERT INTO bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d,o_ol_cnt, o_all_local)VALUES (?, ?, ?, ?, ?, ?, ?)
INSERT INTO bmsql_new_order (no_o_id, no_d_id, no_w_id)VALUES (?, ?, ?)
SELECT s_quantity, s_data,s_dist_01, s_dist_02, s_dist_03, s_dist_04,s_dist_05, s_dist_06, s_dist_07, s_dist_08,s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = ? AND s_i_id = ? FOR UPDATE
SELECT i_price, i_name, i_data FROM bmsql_item WHERE i_id = ?
UPDATE bmsql_stock SET s_quantity = ?, s_ytd = s_ytd + ?,s_order_cnt = s_order_cnt + 1,s_remote_cnt = s_remote_cnt + ? WHERE s_w_id = ? AND s_i_id = ?
INSERT INTO bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number,ol_i_id, ol_supply_w_id, ol_quantity,ol_amount, ol_dist_info)VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

支付订单

对于任意一个客户端,从固定的仓库随机选取一个辖区及其内用户,采用随机的金额支付一笔订单,并作相应历史纪录 占比 : 43%

SELECT w_name, w_street_1, w_street_2, w_city,  w_state, w_zip FROM bmsql_warehouse WHERE w_id = ?
SELECT d_name, d_street_1, d_street_2, d_city, d_state, d_zip FROM bmsql_district WHERE d_w_id = ? AND d_id = ?
SELECT c_id FROM bmsql_customer WHERE c_w_id = ? AND c_d_id = ? AND c_last = ? ORDER BY c_first
SELECT c_first, c_middle, c_last, c_street_1, c_street_2,c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = ? AND c_d_id = ? AND c_id = ? FOR UPDATE
SELECT c_data FROM bmsql_customer WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
UPDATE bmsql_warehouse SET w_ytd = w_ytd + ? WHERE w_id = ?
UPDATE bmsql_district SET d_ytd = d_ytd + ? WHERE d_w_id = ? AND d_id = ?
UPDATE bmsql_customer SET c_balance = c_balance - ?, c_ytd_payment = c_ytd_payment + ? c_payment_cnt = c_payment_cnt + 1 WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
UPDATE bmsql_customer SET c_balance = c_balance - ?,c_ytd_payment = c_ytd_payment + ?,c_payment_cnt = c_payment_cnt + 1,c_data = ? WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
INSERT INTO bmsql_history (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id,h_date, h_amount, h_data)VALUES (?, ?, ?, ?, ?, ?, ?, ?)

查询订单状态

对于任意一个客户端,从固定的仓库随机选取一个辖区及其内用户,读取其最后一条订单,显示订单内每件商品的状态. 占比 : 4%

SELECT c_id FROM bmsql_customer WHERE c_w_id = ? AND c_d_id = ? AND c_last = ? ORDER BY c_first
SELECT c_first, c_middle, c_last, c_balance FROM bmsql_customer WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
SELECT o_id, o_entry_d, o_carrier_id FROM bmsql_oorder WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? AND o_id = (SELECT max(o_id) FROM bmsql_oorder WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?    
SELECT ol_i_id, ol_supply_w_id, ol_quantity,ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ? ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number

发货

对于任意一个客户端,随机选取一个发货包,更新被处理订单的用户余额,并把该订单从新订单中删除. 占比 : 4%

SELECT count(*) AS low_stock FROM (	SELECT s_w_id, s_i_id, s_quantity  FROM bmsql_stock WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = ? AND d_id = ?

查询库存

对于任意一个客户端,从固定的仓库和辖区随机选取最后 20 条订单,查看订单中所有的货物的库存,计算并显示所有库存低于随机生成域值的商品数量. 占比 : 4%

SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = ? AND no_d_id = ?  ORDER BY no_o_id ASC
DELETE FROM bmsql_new_order WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?
SELECT o_c_id FROM bmsql_oorder WHERE o_w_id = ? AND o_d_id = ? AND o_id = ?
UPDATE bmsql_oorder  SET o_carrier_id = ? WHERE o_w_id = ? AND o_d_id = ? AND o_id = ?
SELECT sum(ol_amount) AS sum_ol_amount FROM bmsql_order_line WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?
UPDATE bmsql_order_line SET ol_delivery_d = ? WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?
UPDATE bmsql_customer SET c_balance = c_balance + ?, c_delivery_cnt = c_delivery_cnt + 1 WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?

TPCC测试

原文：https://www.cnblogs.com/Heoric/p/13924662.html