因为我们之前在《Apache ShardingSphere Proxy 负载均衡小练习》里面已经对 1、2、3做了主从复制作为负载均衡练习，为了不影响之前的数据，我们增加4、5、6 库用来演示分表的功能，下面是 docker-compose.xml 的内容
代码语言：javascript复制version: '3.7'
services:
mysql8_1:
image: "mysql:8.0.19"
container_name: mysql8_1
ports:
- "33080:3306"
environment:
MYSQL_ROOT_PASSWORD: 12345678
mysql8_2:
image: "mysql:8.0.19"
container_name: mysql8_2
ports:
- "33081:3306"
environment:
MYSQL_ROOT_PASSWORD: 12345678
mysql8_3:
image: "mysql:8.0.19"
container_name: mysql8_3
ports:
- "33082:3306"
environment:
MYSQL_ROOT_PASSWORD: 12345678
mysql8_4:
image: "mysql:8.0.19"
container_name: mysql8_4
ports:
- "33083:3306"
environment:
MYSQL_ROOT_PASSWORD: 12345678
mysql8_5:
image: "mysql:8.0.19"
container_name: mysql8_5
ports:
- "33084:3306"
environment:
MYSQL_ROOT_PASSWORD: 12345678
mysql8_6:
image: "mysql:8.0.19"
container_name: mysql8_6
ports:
- "33085:3306"
environment:
MYSQL_ROOT_PASSWORD: 12345678我们先看一个纯分片，但不读写分离的配置
代码语言：javascript复制schemaName: master_slave_db
dataSources:
ds1:
url: jdbc:mysql://mysql8_1:3306/test?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 12345678
connectionTimeoutMilliseconds: 2000
ds2:
url: jdbc:mysql://mysql8_2:3306/test?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 12345678
readOnly: true
connectionTimeoutMilliseconds: 2000
ds3:
url: jdbc:mysql://mysql8_3:3306/test?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 12345678
connectionTimeoutMilliseconds: 2000
ds4:
url: jdbc:mysql://mysql8_4:3306/test?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 12345678
connectionTimeoutMilliseconds: 2000
ds5:
url: jdbc:mysql://mysql8_5:3306/test?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 12345678
connectionTimeoutMilliseconds: 2000
ds6:
url: jdbc:mysql://mysql8_6:3306/test?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 12345678
connectionTimeoutMilliseconds: 2000
masterSlaveRule:
name: ds_ms
masterDataSourceName: ds1
slaveDataSourceNames:
- ds2
- ds3
loadBalanceAlgorithmType: ROUND_ROBIN
shardingRule:
tables:
category:
actualDataNodes: ds${4..6}.category
user:
actualDataNodes: ds${4..6}.user
databaseStrategy:
inline:
shardingColumn: id
algorithmExpression: ds${id%3 4}
order:
actualDataNodes: ds${4..6}.order${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds${user_id%3 4}
tableStrategy:
inline:
shardingColumn: id
algorithmExpression: order${id%2}
keyGenerator:
type: SNOWFLAKE
column: id
order_item:
actualDataNodes: ds${4..6}.order_item${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds${user_id%3 4}
tableStrategy:
inline:
shardingColumn: id
algorithmExpression: order_item${order_id%2}
bindingTables:
- user,order,order_item
broadcastTables:
- category
defaultDataSourceName: ds4
defaultDatabaseStrategy:
none:
defaultTableStrategy:
none:
defaultKeyGenerator:
type: SNOWFLAKE
column: id
在我们配置好分片后，需要通过shardingProxy来执行下面的建表语句
代码语言：javascript复制create table category
(
id bigint not null,
name varchar(50) null,
constraint category_id_uindex
unique (id)
);
alter table category
add primary key (id);
create table user
(
id bigint null,
name varchar(60) null,
create_date timestamp default now() null
);
create unique index user_id_uindex
on user (id);
alter table user
add constraint user_pk
primary key (id);
create table `order`
(
id bigint null,
user_id bigint null,
fee decimal(8,2) null
);
create unique index order_id_uindex
on `order` (id);
alter table `order`
add constraint order_pk
primary key (id);
create table order_item
(
id bigint null,
order_id bigint null,
item_name varchar(50) null,
buy_num int null
);
create unique index order_item_id_uindex
on order_item (id);
alter table order_item
add constraint order_item_pk
primary key (id);
注意，我这里的主键用的是 bigint，是因为雪花算法算出来的数字比较大，int 存不下。我们插入几条user信息，因为我们默认已经指定了id是用 snowflake自动生成，所以insert 语句中就不用给id了，proxy会自动为id赋值。
通过上面的建表语句，mysql中实际建立的表有这些：
Tables_in_test |
|---|
category |
order0 |
order1 |
order_item0 |
order_item1 |
user |
插入三个用户，看看分片的效果
代码语言：javascript复制insert into user(name) values ('user1');
insert into user(name) values ('user2');
insert into user(name) values ('user3');控制台输出的内容，显示出了有两条进入了ds4，一条进入了ds5，因为我们的id是用雪花算法生成的，数字并不是连续的，所以按照取模的方式，并不能完全保证均匀的分到不同的shard上：
代码语言：javascript复制[INFO ] 06:31:41.364 [ShardingSphere-Command-0] ShardingSphere-SQL - Rule Type: sharding
[INFO ] 06:31:41.365 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: insert into user(name) values ('user1')
[INFO ] 06:31:41.365 [ShardingSphere-Command-0] ShardingSphere-SQL - SQLStatement: InsertSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@27b96d8f, tablesContext=TablesContext(tables=[Table(name=user, alias=Optional.absent())], schema=Optional.absent())), columnNames=[name], insertValueContexts=[InsertValueContext(parametersCount=0, valueExpressions=[LiteralExpressionSegment(startIndex=31, stopIndex=37, literals=user1), DerivedLiteralExpressionSegment(super=LiteralExpressionSegment(startIndex=0, stopIndex=0, literals=458519221849030657))], parameters=[])])
[INFO ] 06:31:41.365 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds4 ::: insert into user(name, id) values ('user1', 458519221849030657)
[INFO ] 06:33:52.327 [ShardingSphere-Command-2] ShardingSphere-SQL - Rule Type: sharding
[INFO ] 06:33:52.327 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: insert into user(name) values ('user2')
[INFO ] 06:33:52.327 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: InsertSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@28723307, tablesContext=TablesContext(tables=[Table(name=user, alias=Optional.absent())], schema=Optional.absent())), columnNames=[name], insertValueContexts=[InsertValueContext(parametersCount=0, valueExpressions=[LiteralExpressionSegment(startIndex=31, stopIndex=37, literals=user2), DerivedLiteralExpressionSegment(super=LiteralExpressionSegment(startIndex=0, stopIndex=0, literals=458519771147665408))], parameters=[])])
[INFO ] 06:33:52.327 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds4 ::: insert into user(name, id) values ('user2', 458519771147665408)
[INFO ] 06:34:30.208 [ShardingSphere-Command-3] ShardingSphere-SQL - Rule Type: sharding
[INFO ] 06:34:30.208 [ShardingSphere-Command-3] ShardingSphere-SQL - Logic SQL: insert into user(name) values ('user3')
[INFO ] 06:34:30.208 [ShardingSphere-Command-3] ShardingSphere-SQL - SQLStatement: InsertSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6bdfe200, tablesContext=TablesContext(tables=[Table(name=user, alias=Optional.absent())], schema=Optional.absent())), columnNames=[name], insertValueContexts=[InsertValueContext(parametersCount=0, valueExpressions=[LiteralExpressionSegment(startIndex=31, stopIndex=37, literals=user3), DerivedLiteralExpressionSegment(super=LiteralExpressionSegment(startIndex=0, stopIndex=0, literals=458519930032095233))], parameters=[])])
[INFO ] 06:34:30.209 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds5 ::: insert into user(name, id) values ('user3', 458519930032095233)
当我们用查询语句查询的时候会分别的查询三个库里面的user表：
代码语言：javascript复制mysql root@localhost:master_slave_db> select * from user;
-------------------- -------- ---------------------
| id | name | create_date |
|-------------------- -------- ---------------------|
| 458519221849030657 | user1 | 2020-04-19 06:31:41 |
| 458519771147665408 | user2 | 2020-04-19 06:33:52 |
| 458519930032095233 | user3 | 2020-04-19 06:34:30 |
-------------------- -------- ---------------------
3 rows in set
Time: 0.019s控制台日志：
代码语言：javascript复制[INFO ] 06:38:44.308 [ShardingSphere-Command-4] ShardingSphere-SQL - Rule Type: sharding
[INFO ] 06:38:44.308 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from user
[INFO ] 06:38:44.308 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@56721809, tablesContext=TablesContext(tables=[Table(name=user, alias=Optional.absent())], schema=Optional.absent())), projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.absent())], columnLabels=[id, name, create_date]), groupByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.groupby.GroupByContext@761320d4, orderByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.orderby.OrderByContext@7e256b1e, paginationContext=org.apache.shardingsphere.sql.parser.relation.segment.select.pagination.PaginationContext@14c8421a, containsSubquery=false)
[INFO ] 06:38:44.308 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds4 ::: select * from user
[INFO ] 06:38:44.309 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds5 ::: select * from user
[INFO ] 06:38:44.309 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds6 ::: select * from user常见问题
1、数据库分库分表的结构ShardingSphere会自动创建吗？
因为配置文件中并不定义数据表的结构，所以无法自动创建，创建符合分片规则的表是通过proxy来执行创建原始表的语句来实现的，proxy会解析创建表的语句给各个分片的ds上都创建对应的表。
