分享springboot配置Sharding-JDBC分库分表,主从架构的配置文件

• 数据库设计如下 ，其中商品与店铺信息之间进行了垂直分库 ，分为了PRODUCT_DB(商品库)和STORE_DB(店铺 库) ；商品信息还进行了垂直分表 ，分为了商品基本信息(product_info)和商品描述信息(product_descript) ，地理区 域信息(region)作为公共表 ，冗余在两库中 ：
  

• 考虑到商品信息的数据增长性 ，对PRODUCT_DB(商品库)进行了水平分库 ，分片键使用店铺id ，分片策略为店铺 ID%2 + 1 ，因此商品描述信息对所属店铺ID进行了冗余 ；

• 对商品基本信息(product_info)和商品描述信息(product_descript)进行水平分表 ，分片键使用商品id ，分片策略为 商品ID%2 + 1,并将为这两个表设置为绑定表 ，避免笛卡尔积join ；

• 为避免主键冲突 ，ID生成策略采用雪花算法来生成全局唯一ID ，最终数据库设计为下图 ：
  

• sharding官方地址

https://shardingsphere.apache.org/document/current/cn/overview/

• SpringBoot配置文件中的Sharding-JDBC配置

#sharding-jdbc分片规则配置
#数据源
spring.shardingsphere.datasource.names = m0,m1,m2,s0,s1,s2

spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url = jdbc:mysql://192.168.200.110:3306/store_db?useUnicode=true
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = 123456

spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://192.168.200.110:3306/product_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = 123456

spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://192.168.200.110:3306/product_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = 123456


spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.url = jdbc:mysql://192.168.200.110:3307/store_db?useUnicode=true
spring.shardingsphere.datasource.s0.username = root
spring.shardingsphere.datasource.s0.password = 123456

spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.url = jdbc:mysql://192.168.200.110:3307/product_db_1?useUnicode=true
spring.shardingsphere.datasource.s1.username = root
spring.shardingsphere.datasource.s1.password = 123456

spring.shardingsphere.datasource.s2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s2.url = jdbc:mysql://192.168.200.110:3307/product_db_2?useUnicode=true
spring.shardingsphere.datasource.s2.username = root
spring.shardingsphere.datasource.s2.password = 123456

# 主库从库逻辑数据源定义 ds0为store_db ds1为product_db_1 ds2为product_db_2
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1
spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=s2

# 默认分库策略，以store_info_id为分片键，分片策略为store_info_id % 2 + 1，也就是store_info_id为双数的数据进入ds1，为单数的进入ds2
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = store_info_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{store_info_id % 2 + 1}


# store_info分表策略，固定分配至ds0的store_info真实表，
spring.shardingsphere.sharding.tables.store_info.actual-data-nodes = ds$->{0}.store_info
spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.sharding-column = id
spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.algorithm-expression =store_info


# product_info分表策略，分布在ds1,ds2的product_info_1 product_info_2表 ，分片策略为product_info_id% 2 + 1，product_info_id生成为雪花算法，为双数的数据进入product_info_1表，为单数的进入product_info_2表
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes = ds$->{1..2}.product_info_$->{1..2}
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column =product_info_id
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression =product_info_$->{product_info_id % 2 + 1}
spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_info_id 
spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE


# product_descript分表策略，分布在ds1,ds2的product_descript_1 product_descript_2表 ，分片策略为product_info_id % 2 + 1，id生成为雪花算法，product_info_id为双数的数据进入product_descript_1表，为单数的进入product_descript_2表
spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes = ds$->{1..2}.product_descript_$->{1..2}
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.sharding-column =product_info_id
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.algorithm-expression = product_descript_$->{product_info_id % 2 + 1}
spring.shardingsphere.sharding.tables.product_descript.key-generator.column=id
spring.shardingsphere.sharding.tables.product_descript.key-generator.type=SNOWFLAKE
# 设置product_info,product_descript为绑定表, 设置绑定表效率更高,不设置效率低一点,但是数据不会出错
spring.shardingsphere.sharding.binding‐tables[0] = product_info,product_descript
# 设置region为广播表(公共表)，每次更新操作会发送至所有数据源
spring.shardingsphere.sharding.broadcast‐tables=region