Apache ShardingSphere Proxy 分库分表小练习

因为我们之前在Apache ShardingSphere Proxy 负载均衡小练习》里面已经对 1、2、3做了主从复制作为负载均衡练习,为了不影响之前的数据,我们增加4、5、6 库用来演示分表的功能,下面是 docker-compose.xml 的内容

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

我们先看一个纯分片,但不读写分离的配置

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来执行下面的建表语句

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

插入三个用户,看看分片的效果

insert into user(name) values ('user1');
insert into user(name) values ('user2');
insert into user(name) values ('user3');

控制台输出的内容,显示出了有两条进入了ds4,一条进入了ds5,因为我们的id是用雪花算法生成的,数字并不是连续的,所以按照取模的方式,并不能完全保证均匀的分到不同的shard上:

[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表:

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

控制台日志:

[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上都创建对应的表。

2、配置的类如图,蓝色为配置的类

配置领域模型类图

Leave a Comment

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据