时间戳

首页 > 技术文章 > sharding-jdbc分库分表配置，多数据源

sharding-jdbc分库分表配置，多数据源

huqi96 2021-02-07 15:45 原文

# 以下是分片规则配置
# 定义数据源
spring.shardingsphere.datasource.names = m1
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://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root
# 指定t_order表的数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.t_order.actual‐data‐nodes = m1.t_order_$‐>{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key‐generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key‐generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.sharding‐column = order_id
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.algorithm‐expression = t_order_$‐>{order_id % 2 + 1}
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true

 多数据源配置

# 定义多个数据源
spring.shardingsphere.datasource.names = m1,m2
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://localhost:3306/order_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root
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://localhost:3306/order_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = root
...
# 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作m1数据源，否则操作m2。
spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.sharding‐column = user_id
spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.algorithm‐expression = m$‐>{user_id % 2 + 1}
#分库策略，如何将一个逻辑表映射到多个数据源
spring.shardingsphere.sharding.tables.<逻辑表名称>.database‐strategy.<分片策略>.<分片策略属性名>= #分片策略属性值
#分表策略，如何将一个逻辑表映射为多个实际表
spring.shardingsphere.sharding.tables.<逻辑表名称>.table‐strategy.<分片策略>.<分片策略属性名>= #分片策略属性值

详细配置

# 真实数据源定义 m为主库 s为从库
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://localhost:3306/store_db?useUnicode=true
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = root
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://localhost:3306/product_db_1?
useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root
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://localhost:3306/product_db_2?
useUnicode=true
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = root
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://localhost:3307/store_db?useUnicode=true
spring.shardingsphere.datasource.s0.username = root
spring.shardingsphere.datasource.s0.password = root
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://localhost:3307/product_db_1?
useUnicode=true
spring.shardingsphere.datasource.s1.username = root
spring.shardingsphere.datasource.s1.password = root
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://localhost:3307/product_db_2?
useUnicode=true
spring.shardingsphere.datasource.s2.username = root
spring.shardingsphere.datasource.s2.password = root
# 主库从库逻辑数据源定义 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
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true

 

推荐阅读