Sharding-Sphere 实战：tpcc-MySQL 压测

一、环境

MySQL版本：5.7.34
ShardingSphere-Proxy：5.1.2

MySQL客户端与tpcc-mysql：172.18.16.156
ShardingSphere-Proxy Cluster模式：172.18.10.66:3307; 172.18.18.102:3307
底层资源库：172.18.10.66:3306 db1、db2; 172.18.18.102:3306 db1、db2

二、准备

在172.18.16.156执行以下步骤：

1. 建库

# 连接ShardingSphere-Proxy
mysql -u root -h 172.18.10.66 -P 3307 -p123456

-- 建库
create database sharding_db;
use sharding_db;

-- 查看运行模式
show instance mode\G

2. 添加资源

add resource 
resource_1 (host=172.18.10.66, port=3306, db=db1, user=wxy, password=mypass),
resource_2 (host=172.18.10.66, port=3306, db=db2, user=wxy, password=mypass),
resource_3 (host=172.18.18.102, port=3306, db=db1, user=wxy, password=mypass),
resource_4 (host=172.18.18.102, port=3306, db=db2, user=wxy, password=mypass);

3. 创建分片规则

-- 用以各个表中的 warehouse id 作为分片键。

create sharding table rule warehouse (
resources(resource_1,resource_2,resource_3,resource_4),
sharding_column=w_id,type(name=hash_mod,properties("sharding-count"=16)));

create sharding table rule district (
resources(resource_1,resource_2,resource_3,resource_4),
sharding_column=d_w_id,type(name=hash_mod,properties("sharding-count"=16)));

create sharding table rule customer (
resources(resource_1,resource_2,resource_3,resource_4),
sharding_column=c_w_id,type(name=hash_mod,properties("sharding-count"=16)));

create sharding table rule history (
resources(resource_1,resource_2,resource_3,resource_4),
sharding_column=h_c_w_id,type(name=hash_mod,properties("sharding-count"=16)));

create sharding table rule new_orders (
resources(resource_1,resource_2,resource_3,resource_4),
sharding_column=no_w_id,type(name=hash_mod,properties("sharding-count"=16)));

create sharding table rule orders (
resources(resource_1,resource_2,resource_3,resource_4),
sharding_column=o_w_id,type(name=hash_mod,properties("sharding-count"=16)));

create sharding table rule order_line (
resources(resource_1,resource_2,resource_3,resource_4),
sharding_column=ol_w_id,type(name=hash_mod,properties("sharding-count"=16)));

-- 表 item 没有 warehouse id，取 i_id 作为分片键
create sharding table rule item (
resources(resource_1,resource_2,resource_3,resource_4),
sharding_column=i_id,type(name=hash_mod,properties("sharding-count"=16)));

create sharding table rule stock (
resources(resource_1,resource_2,resource_3,resource_4),
sharding_column=s_w_id,type(name=hash_mod,properties("sharding-count"=16)));

-- 创建绑定表规则
create sharding binding table rules (warehouse, customer);
create sharding binding table rules (stock, district, order_line);

4. 建表

create table warehouse (
w_id smallint not null,
w_name varchar(10), 
w_street_1 varchar(20), 
w_street_2 varchar(20), 
w_city varchar(20), 
w_state char(2), 
w_zip char(9), 
w_tax decimal(4,2), 
w_ytd decimal(12,2),
primary key (w_id) );

create table district (
d_id tinyint not null, 
d_w_id smallint not null, 
d_name varchar(10), 
d_street_1 varchar(20), 
d_street_2 varchar(20), 
d_city varchar(20), 
d_state char(2), 
d_zip char(9), 
d_tax decimal(4,2), 
d_ytd decimal(12,2), 
d_next_o_id int,
primary key (d_w_id, d_id) );

create table customer (
c_id int not null, 
c_d_id tinyint not null,
c_w_id smallint not null, 
c_first varchar(16), 
c_middle char(2), 
c_last varchar(16), 
c_street_1 varchar(20), 
c_street_2 varchar(20), 
c_city varchar(20), 
c_state char(2), 
c_zip char(9), 
c_phone char(16), 
c_since datetime, 
c_credit char(2), 
c_credit_lim bigint, 
c_discount decimal(4,2), 
c_balance decimal(12,2), 
c_ytd_payment decimal(12,2), 
c_payment_cnt smallint, 
c_delivery_cnt smallint, 
c_data text,
PRIMARY KEY(c_w_id, c_d_id, c_id) );

create table history (
h_c_id int, 
h_c_d_id tinyint, 
h_c_w_id smallint,
h_d_id tinyint,
h_w_id smallint,
h_date datetime,
h_amount decimal(6,2), 
h_data varchar(24) );

create table new_orders (
no_o_id int not null,
no_d_id tinyint not null,
no_w_id smallint not null,
PRIMARY KEY(no_w_id, no_d_id, no_o_id));

create table orders (
o_id int not null, 
o_d_id tinyint not null, 
o_w_id smallint not null,
o_c_id int,
o_entry_d datetime,
o_carrier_id tinyint,
o_ol_cnt tinyint, 
o_all_local tinyint,
PRIMARY KEY(o_w_id, o_d_id, o_id) );

create table order_line ( 
ol_o_id int not null, 
ol_d_id tinyint not null,
ol_w_id smallint not null,
ol_number tinyint not null,
ol_i_id int, 
ol_supply_w_id smallint,
ol_delivery_d datetime, 
ol_quantity tinyint, 
ol_amount decimal(6,2), 
ol_dist_info char(24),
PRIMARY KEY(ol_w_id, ol_d_id, ol_o_id, ol_number) );

create table item (
i_id int not null, 
i_im_id int, 
i_name varchar(24), 
i_price decimal(5,2), 
i_data varchar(50),
PRIMARY KEY(i_id) );

create table stock (
s_i_id int not null, 
s_w_id smallint not null, 
s_quantity smallint, 
s_dist_01 char(24), 
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24), 
s_dist_05 char(24), 
s_dist_06 char(24), 
s_dist_07 char(24), 
s_dist_08 char(24), 
s_dist_09 char(24), 
s_dist_10 char(24), 
s_ytd decimal(8,0), 
s_order_cnt smallint, 
s_remote_cnt smallint,
s_data varchar(50),
PRIMARY KEY(s_w_id, s_i_id) );

此时每个底层库中共建了36张表。

5. 创建索引

CREATE INDEX idx_customer ON customer (c_w_id,c_d_id,c_last,c_first);
CREATE INDEX idx_orders ON orders (o_w_id,o_d_id,o_c_id,o_id);
CREATE INDEX fkey_stock_2 ON stock (s_i_id);
CREATE INDEX fkey_order_line_2 ON order_line (ol_supply_w_id,ol_i_id);

三、测试