TPCC测试

建表

warehouse

int	decimal(12,2)	decimal(4,4)	varchar(10)	varchar(20)	varchar(20)	varchar(20)	char(2)	char(9)
w_id	w_ytd	w_tax	w_name	w_street_1	w_street_2	w_city	w_state	w_zip

district

int	int	decimal(12,2)	decimal(4,4)	integer	varchar(10)	varchar(20)	varchar(20)	varchar(20)	char(2)	char(9)
d_w_id	d_id	d_ytd	d_tax	d_next_o_id	d_name	d_street_1	d_street_1	d_city	d_state	d_zip

customer

int	int	int	decimal(4,4)	char(2)	varchar(16)	varchar(16)	decimal(12,2)	decimal(12,2)	decimal(12,2)	int	int	varchar(20)	varchar(20)	varchar(20)	char(2)	char(9)	char(16)	timestamp	char(2)	varchar(500)
c_w_id	c_d_id	c_id	c_discount	c_credit	c_last	c_first	c_credit_lim	c_balance	c_ytd_payment	c_payment_cnt	c_delivery_cnt	c_street_1	c_street_2	c_city	c_state	c_zip	c_phone	c_since	c_middle	c_data

history

int	int	int	int	int	int	timestamp	decimal(6,2)	varchar(24)
hist_id	h_c_id	h_c_d_id	h_c_w_id	h_d_id	h_w_id	h_data	h_amount	h_data

new_order

int	int	int
no_w_id	no_d_id	no_o_id

oorder

int	int	int	int	int	int	int	timestamp
o_w_id	o_d_id	o_id	o_c_id	o_carrier_id	o_ol_cnt	o_all_local	o_entry_d

order_line

int	int	int	int	int	timestamp	decimal(6,2)	int	int	char(24)
ol_w_id	ol_d_id	ol_o_id	ol_number	ol_i_id	ol_deliver_d	ol_amount	ol_supply_w_id	ol_quantity	ol_dist_info

stock

int	int	int	int	int	int	varchar(50)	char(24)	char(24)	char(24)	char(24)	char(24)	char(24)	char(24)	char(24)	char(24)	char(24)
s_w_id	s_i_id	s_quantity	s_ytd	s_order_cnt	s_remote_cnt	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

索引

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 = ?

常用工具

 benchmarksql
 tpcc-mysql