ShardingSphere 分片与配置笔记

版本说明：本文的 spring.shardingsphere.sharding 配置属于旧版 Sharding-JDBC 风格，不适合作为新版配置直接粘贴。数据库地址、账号与口令已改成示例值，自定义算法类需要项目自行实现。

# 所有数据源
spring.shardingsphere.datasource.names=ds-0,ds-1

# 数据源 ds-0
spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-0.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-0.url=jdbc:mysql://192.0.2.5:3306/ds-0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=${DB_PASSWORD}

# 数据源 ds-1
spring.shardingsphere.datasource.ds-1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-1.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-1.url=jdbc:mysql://192.0.2.5:3306/ds-1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=${DB_PASSWORD}

# 默认数据源，未分片的表默认执行库
spring.shardingsphere.sharding.default-data-source-name=ds-0


### 分库策略
# 分库分片健
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=order_id
# 分库分片算法（标准分片-精准分片算法）
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.xiaofu.sharding.algorithm.dbAlgorithm.MyDBPreciseShardingAlgorithm
# 分库分片算法（标准分片-范围分片算法）
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.range-algorithm-class-name=com.xiaofu.sharding.algorithm.dbAlgorithm.MyDBRangeShardingAlgorithm

### 分表策略
# 分表分片健
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
# 分表分片算法（标准分片-精准分片算法）
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.xiaofu.sharding.algorithm.tableAlgorithm.MyTablePreciseShardingAlgorithm
# 分表分片算法（标准分片-范围分片算法）
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.range-algorithm-class-name=com.xiaofu.sharding.algorithm.tableAlgorithm.MyTableRangeShardingAlgorithm

spring:
shardingsphere:
datasource:
ds-0:
driverClassName: com.mysql.jdbc.Driver
password: CHANGE_ME
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.0.2.5:3306/ds-0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
ds-1:
driverClassName: com.mysql.jdbc.Driver
password: CHANGE_ME
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.0.2.5:3306/ds-1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
names: ds-0,ds-1
sharding:
default-data-source-name: ds-0
tables:
t_order:
database-strategy:
standard:
precise-algorithm-class-name: com.xiaofu.sharding.algorithm.dbAlgorithm.MyDBPreciseShardingAlgorithm
range-algorithm-class-name: com.xiaofu.sharding.algorithm.dbAlgorithm.MyDBRangeShardingAlgorithm
sharding-column: order_id
table-strategy:
standard:
precise-algorithm-class-name: com.xiaofu.sharding.algorithm.tableAlgorithm.MyTablePreciseShardingAlgorithm
range-algorithm-class-name: com.xiaofu.sharding.algorithm.tableAlgorithm.MyTableRangeShardingAlgorithm
sharding-column: order_id

#分表配置
spring.shardingsphere.datasource.names=m1

spring.main.allow-bean-definition-overriding=true
spring.shardingsphere.props.max.connections.size.per.query=50

spring.shardingsphere.datasource.m1.type=com.example.config.ShardingDruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://192.0.2.15:3306/example_db?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.shardingsphere.datasource.m1.connectionInitSqls=set names utf8mb4;

spring.shardingsphere.datasource.m1.username=example_user
spring.shardingsphere.datasource.m1.password=${DB_PASSWORD}
spring.shardingsphere.datasource.m1.connection-properties=password=${DB_PASSWORD}
spring.shardingsphere.datasource.m1.password-callback-class-name=com.example.callback.DBPasswordCallback

spring.shardingsphere.datasource.m1.min-idle=30
spring.shardingsphere.datasource.m1.initial-size=30
spring.shardingsphere.datasource.m1.max-active=300
spring.shardingsphere.datasource.m1.filters=stat
spring.shardingsphere.datasource.m1.maxWait=60000
spring.shardingsphere.datasource.m1.timeBetweenEvictionRunsMillis=60000
spring.shardingsphere.datasource.m1.minEvictableIdleTimeMillis=300000
spring.shardingsphere.datasource.m1.validationQuery=SELECT 'x'
spring.shardingsphere.datasource.m1.testWhileIdle=true
spring.shardingsphere.datasource.m1.testOnBorrow=false
spring.shardingsphere.datasource.m1.testOnReturn=false
spring.shardingsphere.datasource.m1.poolPreparedStatements=false
spring.shardingsphere.datasource.m1.maxOpenPreparedStatements=100
spring.shardingsphere.datasource.m1.slowSqlMillis=5000
spring.shardingsphere.datasource.m1.isLogSlowSql=true

spring.shardingsphere.props.sql.show=true

spring.shardingsphere.sharding.tables.t_chat_group.actual-data-nodes=m1.t_chat_group_$->{1..64}
spring.shardingsphere.sharding.tables.t_chat_group.key-generator.column=id
spring.shardingsphere.sharding.tables.t_chat_group.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_chat_group.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_chat_group.table-strategy.inline.algorithm-expression=t_chat_group_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_chat_group.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.tables.t_chat_group_member.actual-data-nodes=m1.t_chat_group_member_$->{1..64}
spring.shardingsphere.sharding.tables.t_chat_group_member.key-generator.column=id
spring.shardingsphere.sharding.tables.t_chat_group_member.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_chat_group_member.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_chat_group_member.table-strategy.inline.algorithm-expression=t_chat_group_member_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_chat_group_member.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.tables.t_chat_group_statistics.actual-data-nodes=m1.t_chat_group_statistics_$->{1..64}
spring.shardingsphere.sharding.tables.t_chat_group_statistics.key-generator.column=id
spring.shardingsphere.sharding.tables.t_chat_group_statistics.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_chat_group_statistics.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_chat_group_statistics.table-strategy.inline.algorithm-expression=t_chat_group_statistics_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_chat_group_statistics.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.tables.t_contact.actual-data-nodes=m1.t_contact_$->{1..64}
spring.shardingsphere.sharding.tables.t_contact.key-generator.column=id
spring.shardingsphere.sharding.tables.t_contact.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_contact.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_contact.table-strategy.inline.algorithm-expression=t_contact_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_contact.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.tables.t_contact_staff.actual-data-nodes=m1.t_contact_staff_$->{1..64}
spring.shardingsphere.sharding.tables.t_contact_staff.key-generator.column=id
spring.shardingsphere.sharding.tables.t_contact_staff.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_contact_staff.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_contact_staff.table-strategy.inline.algorithm-expression=t_contact_staff_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_contact_staff.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.tables.t_cs_statistics.actual-data-nodes=m1.t_cs_statistics_$->{1..64}
spring.shardingsphere.sharding.tables.t_cs_statistics.key-generator.column=id
spring.shardingsphere.sharding.tables.t_cs_statistics.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_cs_statistics.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_cs_statistics.table-strategy.inline.algorithm-expression=t_cs_statistics_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_cs_statistics.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm


spring.shardingsphere.sharding.tables.t_statistics.actual-data-nodes=m1.t_statistics_$->{1..64}
spring.shardingsphere.sharding.tables.t_statistics.key-generator.column=id
spring.shardingsphere.sharding.tables.t_statistics.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_statistics.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_statistics.table-strategy.inline.algorithm-expression=t_statistics_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_statistics.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.tables.t_statistics_total.actual-data-nodes=m1.t_statistics_total_$->{1..64}
spring.shardingsphere.sharding.tables.t_statistics_total.key-generator.column=id
spring.shardingsphere.sharding.tables.t_statistics_total.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_statistics_total.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_statistics_total.table-strategy.inline.algorithm-expression=t_statistics_total_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_statistics_total.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.tables.t_clc_statistics.actual-data-nodes=m1.t_clc_statistics_$->{1..64}
spring.shardingsphere.sharding.tables.t_clc_statistics.key-generator.column=id
spring.shardingsphere.sharding.tables.t_clc_statistics.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_clc_statistics.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_clc_statistics.table-strategy.inline.algorithm-expression=t_clc_statistics_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_clc_statistics.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.tables.t_group_send.actual-data-nodes=m1.t_group_send_$->{1..64}
spring.shardingsphere.sharding.tables.t_group_send.key-generator.column=id
spring.shardingsphere.sharding.tables.t_group_send.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_group_send.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_group_send.table-strategy.inline.algorithm-expression=t_group_send_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_group_send.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.tables.t_group_send_mission.actual-data-nodes=m1.t_group_send_mission_$->{1..64}
spring.shardingsphere.sharding.tables.t_group_send_mission.key-generator.column=id
spring.shardingsphere.sharding.tables.t_group_send_mission.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_group_send_mission.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_group_send_mission.table-strategy.inline.algorithm-expression=t_group_send_mission_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_group_send_mission.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.tables.t_group_send_mission_contact.actual-data-nodes=m1.t_group_send_mission_contact_$->{1..64}
spring.shardingsphere.sharding.tables.t_group_send_mission_contact.key-generator.column=id
spring.shardingsphere.sharding.tables.t_group_send_mission_contact.key-generator.type=MyShardingKey
spring.shardingsphere.sharding.tables.t_group_send_mission_contact.table-strategy.standard.sharding-column=corp_id
#spring.shardingsphere.sharding.tables.t_group_send_mission_contact.table-strategy.inline.algorithm-expression=t_group_send_mission_contact_$->{corp_id % 64 + 1}
spring.shardingsphere.sharding.tables.t_group_send_mission_contact.table-strategy.standard.precise-algorithm-class-name=com.example.config.TableShardingAlgorithm

spring.shardingsphere.sharding.broadcast-tables=t_corp
sharding.jdbc.config.sharding.binding-tables[0]=t_chat_group,t_chat_group_member,t_chat_group_statistics,t_contact,t_contact_staff,t_cs_statistics,t_statistics,t_statistics_total,t_group_send,t_group_send_mission,t_group_send_mission_contact,t_clc_statistics
#sharding.jdbc.config.sharding.binding-tables[0]=t_chat_group,t_chat_group_member,t_chat_group_statistics
#sharding.jdbc.config.sharding.binding-tables[1]=t_contact,t_contact_staff,t_cs_statistics
#sharding.jdbc.config.sharding.binding-tables[2]=t_statistics,t_statistics_total
#sharding.jdbc.config.sharding.binding-tables[3]=t_group_send,t_group_send_mission,t_group_send_mission_contact