目录
1、项目需求
- 数据库使用PG数据库
- 架构为四个集群,每个集群为一主两从,其中一个同步从一个异步从;
- 数据库选择使用pathman工具做分区策略;
- 根据不同表的需求,进行分库分区(部分表不需要分库或分区);
- 本项目3个集群为以下表提供分库分区功能:
- 本项目中另外一个单独的集群提供数据存储服务:
- 支持读写分离:正常情况下,主库只写,从库只读;当从库故障时,主库读写,不访问从库;主库故障时,只能提供数据读取服务,无法提供数据写入服务。
分库分表需求:
前3个集群:
reserve_list_details 订单详情表
reserve_list_brief 订单拆分表
后1个集群:
ticket_seat_type_count 车次席别计数器表
train_limit_define 车次限购需求数量定义表
2、工具版本
需求分析并决定使用以下工具及版本
- Postgresql:10.0版本
- sharding-jdbc:3.x版本
3、Java工程配置
- pom文件配置:
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-orchestration-spring-boot-starter</artifactId>
<version>3.0.1-SNAPSHOT</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-orchestration-reg-zookeeper-curator</artifactId>
<version>3.0.1-SNAPSHOT</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-orchestration</artifactId>
<version>3.0.1-SNAPSHOT</version>
</dependency>
- 连接配置:
配置数据表结构自动更新策略:
validate:验证创建数据库表结构,只会和数据库中的表进行比较,不会创建新表,但是会插入新值。
update:第一次加载时根据model类会自动建立起表的结构,以后加载hibernate时根据model类自动更新表结构,即使表结构改变了但表中的行仍然存在不会删除以前的行。
create:每次加载时都会删除上一次的生成的表,然后根据你的model类再重新来生成新表。
create-drop:每次加载时根据model类生成表,但是sessionFactory一关闭,表就自动删除。
none:不进行表结构的校验及修改操作。
spring.jpa.properties.hibernate.hbm2ddl.auto=none
配置数据库引擎
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
sharding.jdbc.datasource.names=ds_master_0,ds_master_1,ds_master_2,ds_master_3,ds_master_0_slave_0,ds_master_0_slave_1,ds_master_1_slave_0,ds_master_1_slave_1,ds_master_2_slave_0,ds_master_2_slave_1,ds_master_3_slave_0,ds_master_3_slave_1
sharding.jdbc.datasource.ds_master_0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_0.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_0.url=jdbc:postgresql://192.168.6.1:5432/test
sharding.jdbc.datasource.ds_master_0.username=postgres
sharding.jdbc.datasource.ds_master_0.password=postgres
sharding.jdbc.datasource.ds_master_0_slave_0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_0_slave_0.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_0_slave_0.url=jdbc:postgresql://192.168.6.2:5432/test
sharding.jdbc.datasource.ds_master_0_slave_0.username=postgres
sharding.jdbc.datasource.ds_master_0_slave_0.password=postgres
sharding.jdbc.datasource.ds_master_0_slave_1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_0_slave_1.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_0_slave_1.url=jdbc:postgresql://192.168.6.3:5432/test
sharding.jdbc.datasource.ds_master_0_slave_1.username=postgres
sharding.jdbc.datasource.ds_master_0_slave_1.password=postgres
sharding.jdbc.datasource.ds_master_1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_1.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_1.url=jdbc:postgresql://192.168.6.4:5432/test
sharding.jdbc.datasource.ds_master_1.username=postgres
sharding.jdbc.datasource.ds_master_1.password=postgres
sharding.jdbc.datasource.ds_master_1_slave_0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_1_slave_0.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_1_slave_0.url=jdbc:postgresql://192.168.6.5:5432/test
sharding.jdbc.datasource.ds_master_1_slave_0.username=postgres
sharding.jdbc.datasource.ds_master_1_slave_0.password=postgres
sharding.jdbc.datasource.ds_master_1_slave_1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_1_slave_1.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_1_slave_1.url=jdbc:postgresql://192.168.6.6:5432/test
sharding.jdbc.datasource.ds_master_1_slave_1.username=postgres
sharding.jdbc.datasource.ds_master_1_slave_1.password=postgres
sharding.jdbc.datasource.ds_master_2.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_2.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_2.url=jdbc:postgresql://192.168.6.7:5432/test
sharding.jdbc.datasource.ds_master_2.username=postgres
sharding.jdbc.datasource.ds_master_2.password=postgres
sharding.jdbc.datasource.ds_master_2_slave_0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_2_slave_0.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_2_slave_0.url=jdbc:postgresql://192.168.6.8:5432/test
sharding.jdbc.datasource.ds_master_2_slave_0.username=postgres
sharding.jdbc.datasource.ds_master_2_slave_0.password=postgres
sharding.jdbc.datasource.ds_master_2_slave_1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_2_slave_1.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_2_slave_1.url=jdbc:postgresql://192.168.6.9:5432/test
sharding.jdbc.datasource.ds_master_2_slave_1.username=postgres
sharding.jdbc.datasource.ds_master_2_slave_1.password=postgres
sharding.jdbc.datasource.ds_master_3.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_3.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_3.url=jdbc:postgresql://192.168.6.10:5432/test
sharding.jdbc.datasource.ds_master_3.username=postgres
sharding.jdbc.datasource.ds_master_3.password=postgres
sharding.jdbc.datasource.ds_master_3_slave_0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_3_slave_0.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_3_slave_0.url=jdbc:postgresql://192.168.6.11:5432/test
sharding.jdbc.datasource.ds_master_3_slave_0.username=postgres
sharding.jdbc.datasource.ds_master_3_slave_0.password=postgres
sharding.jdbc.datasource.ds_master_3_slave_1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_3_slave_1.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_3_slave_1.url=jdbc:postgresql://192.168.6.12:5432/test
sharding.jdbc.datasource.ds_master_3_slave_1.username=postgres
sharding.jdbc.datasource.ds_master_3_slave_1.password=postgres
#分库分表配置
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.sharding-column=start_train_date
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.algorithm-inline-expression=ds_${start_train_date.hashCode() % 3}
sharding.jdbc.config.sharding.tables.reserve_list_brief.actualDataNodes=ds_${0..2}.reserve_list_brief
sharding.jdbc.config.sharding.tables.reserve_list_brief.keyGeneratorClass=com.test.order.db4pg.entity.ReserveListBriefEntityPK
sharding.jdbc.config.sharding.tables.reserve_list_details.databaseStrategy.inline.sharding-column=login_name
sharding.jdbc.config.sharding.tables.reserve_list_details.databaseStrategy.inline.algorithm-inline-expression=ds_${login_name.hashCode() % 3}
sharding.jdbc.config.sharding.tables.reserve_list_details.actualDataNodes=ds_${0..2}.reserve_list_details
sharding.jdbc.config.sharding.tables.reserve_list_details.keyGeneratorClass=com.test.order.db4pg.entity.ReserveListDetailsEntityPK
sharding.jdbc.config.sharding.tables.ticket_seat_type_count.databaseStrategy.inline.sharding-column=
sharding.jdbc.config.sharding.tables.ticket_seat_type_count.databaseStrategy.inline.algorithm-inline-expression=
sharding.jdbc.config.sharding.tables.ticket_seat_type_count.actualDataNodes=ds_${3}.ticket_seat_type_count
sharding.jdbc.config.sharding.tables.ticket_seat_type_count.keyGeneratorClass=com.test.order.db4pg.entity.TicketSeatTypeCountEntityPK
#读写分离配置
sharding.jdbc.config.sharding.master-slave-rules.ds_0.loadBalanceAlgorithmType=round_robin
sharding.jdbc.config.sharding.master-slave-rules.ds_0.masterDataSourceName=ds_master_0
sharding.jdbc.config.sharding.master-slave-rules.ds_0.slaveDataSourceNames=ds_master_0_slave_0,ds_master_0_slave_1
sharding.jdbc.config.sharding.master-slave-rules.ds_1.loadBalanceAlgorithmType=round_robin
sharding.jdbc.config.sharding.master-slave-rules.ds_1.masterDataSourceName=ds_master_1
sharding.jdbc.config.sharding.master-slave-rules.ds_1.slaveDataSourceNames=ds_master_1_slave_0,ds_master_1_slave_1
sharding.jdbc.config.sharding.master-slave-rules.ds_2.loadBalanceAlgorithmType=round_robin
sharding.jdbc.config.sharding.master-slave-rules.ds_2.masterDataSourceName=ds_master_2
sharding.jdbc.config.sharding.master-slave-rules.ds_2.slaveDataSourceNames=ds_master_2_slave_0,ds_master_2_slave_1
sharding.jdbc.config.sharding.master-slave-rules.ds_3.loadBalanceAlgorithmType=round_robin
sharding.jdbc.config.sharding.master-slave-rules.ds_3.masterDataSourceName=ds_master_3
sharding.jdbc.config.sharding.master-slave-rules.ds_3.slaveDataSourceNames=ds_master_3_slave_0,ds_master_3_slave_1
4、详细配置说明
#列出所有主从的别名,用于引用
sharding.jdbc.datasource.names=ds_master_0,ds_master_1,ds_master_2,ds_master_3,ds_master_0_slave_0,ds_master_0_slave_1,ds_master_1_slave_0,ds_master_1_slave_1,ds_master_2_slave_0,ds_master_3_slave_0,ds_master_2_slave_1,ds_master_3_slave_1
配置数据库地址
sharding.jdbc.datasource.ds_master_0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds_master_0.driverClassName=org.postgresql.Driver
sharding.jdbc.datasource.ds_master_0.url=jdbc:postgresql://192.168.6.1:5432/test
sharding.jdbc.datasource.ds_master_0.username=postgres
sharding.jdbc.datasource.ds_master_0.password=postgres
#分库分表配置
#1.使用databaseStrategy.inline.sharding-column指定表字段,根据该字段进行分库策略
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.sharding-column=start_train_date
#2.根据sharding-column字段的hash值落在actualDataNodes所指定的库中,hashCode的除数等于库的数量,若为单库,此配置可省略
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.algorithm-inline-expression=ds_${start_train_date.hashCode() % 3}
#3.actualDataNodes=ds_${0..2}指定可落入的数据库,reserve_list_brief为数据存储和查询指定的数据表名
sharding.jdbc.config.sharding.tables.reserve_list_brief.actualDataNodes=ds_${0..2}.reserve_list_brief
#4.数据表关联的实体类(该实体类中只包含主键和外键的字段)
sharding.jdbc.config.sharding.tables.reserve_list_brief.keyGeneratorClass=com.test.order.db4pg.entity.ReserveListBriefEntityPK
#读写分离配置
#1.从库负载均衡算法类型:ROUND_ROBIN, RANDOM;默认值:ROUND_ROBIN
sharding.jdbc.config.sharding.master-slave-rules.ds_0.loadBalanceAlgorithmType=round_robin
#2.masterDataSourceName指定主库可以进行读写
sharding.jdbc.config.sharding.master-slave-rules.ds_0.masterDataSourceName=ds_master_0
#3.slaveDataSourceNames指定从库只读
sharding.jdbc.config.sharding.master-slave-rules.ds_0.slaveDataSourceNames=ds_master_0_slave_0,ds_master_0_slave_1
5、其他场景的配置说明
- 使用sharding-jdbc做分库、不分区、PG也不做分区的配置
#首次加载使用create配置,然后改为validate
spring.jpa.properties.hibernate.hbm2ddl.auto=validate
#1.使用databaseStrategy.inline.sharding-column指定表字段,根据该字段进行分库策略
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.sharding-column=start_train_date
#2.该表数据会根据该字段的hash值落在actualDataNodes所指定的库中,hashCode的除数等于库的数量
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.algorithm-inline-expression=ds_${start_train_date.hashCode() % 3}
#3.actualDataNodes=ds_${0..2}指定可落入的数据库,reserve_list_brief为数据存储和查询指定的数据表名
sharding.jdbc.config.sharding.tables.reserve_list_brief.actualDataNodes=ds_${0..2}.reserve_list_brief
#4.数据表关联的实体类(该实体类中只包含主键和外键的字段)
sharding.jdbc.config.sharding.tables.reserve_list_brief.keyGeneratorClass=com.test.order.db4pg.entity.ReserveListBriefEntityPK
- 使用sharding-jdbc做分库、且分区、PG不做分区的配置
#首次加载使用create配置,然后改为validate
spring.jpa.properties.hibernate.hbm2ddl.auto=validate
#1.使用databaseStrategy.inline.sharding-column指定表字段,根据该字段进行分库策略
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.sharding-column=start_train_date
#2.该表数据会根据该字段的hash值落在actualDataNodes所指定的库中,hashCode的除数等于库的数量
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.algorithm-inline-expression=ds_${start_train_date.hashCode() % 3}
#3.tableStrategy.inline.shardingColumn指定分区策略的字段;
sharding.jdbc.config.sharding.tables.reserve_list_brief.tableStrategy.inline.shardingColumn=start_train_date
#4.数据会根据tableStrategy.inline.shardingColumn指定字段的hash值落在actualDataNodes所指定的reserve_list_brief表分区中,hashCode的除数等于分区的数量
sharding.jdbc.config.sharding.tables.reserve_list_brief.tableStrategy.inline.algorithmInlineExpression=reserve_list_brief_${start_train_date.hashCode() % 2}
#5.actualDataNodes=ds_${0..2}指定可落入的数据库,reserve_list_brief_${0..2}指定可落入的分区表范围
sharding.jdbc.config.sharding.tables.reserve_list_brief.actualDataNodes=ds_${0..2}.reserve_list_brief_${0..2}
#6.数据表关联的实体类(该实体类中只包含主键和外键的字段)
sharding.jdbc.config.sharding.tables.reserve_list_brief.keyGeneratorClass=com.test.order.db4pg.entity.ReserveListBriefEntityPK
- 使用sharding-jdbc做分库、不分区、PG的Pathman做分区的配置
#加载时使用validate配置
spring.jpa.properties.hibernate.hbm2ddl.auto=validate
#1.使用databaseStrategy.inline.sharding-column指定表字段,根据该字段进行分库策略;
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.sharding-column=start_train_date
#2.该表数据会根据该字段的hash值落在actualDataNodes所指定的库中,hashCode的除数等于库的数量
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.algorithm-inline-expression=ds_${start_train_date.hashCode() % 3}
#3.actualDataNodes=ds_${0..2}指定可落入的数据库,reserve_list_brief为数据存储和查询指定的数据表名,当使用PG做分区时,指定数据表进行操作时,会自动根据Pathman的分区策略落在指定的副表中
sharding.jdbc.config.sharding.tables.reserve_list_brief.actualDataNodes=ds_${0..2}.reserve_list_brief
#4.数据表关联的实体类(该实体类中只包含主键和外键的字段)
sharding.jdbc.config.sharding.tables.reserve_list_brief.keyGeneratorClass=com.test.order.db4pg.entity.ReserveListBriefEntityPK
- 使用sharding-jdbc不分库、不分区、使用PG的Pathman做分区
#加载使用validate配置
spring.jpa.properties.hibernate.hbm2ddl.auto=validate
#1.不使用sharding-jdbc进行分库时,sharding-column可置为空(或删除该配置)
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.sharding-column=
#2.当不分区时该配置algorithm-inline-expression可置空(或删除该配置)
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.algorithm-inline-expression=
#3.actualDataNodes=ds_0指定可落入的数据库,reserve_list_brief为数据存储和查询指定的数据表名,当使用PG做分区时,指定数据表进行操作时,会自动根据Pathman的分区策略落在指定的副表中
sharding.jdbc.config.sharding.tables.reserve_list_brief.actualDataNodes=ds_0.reserve_list_brief
#4.数据表关联的实体类(该实体类中只包含主键和外键的字段)
sharding.jdbc.config.sharding.tables.reserve_list_brief.keyGeneratorClass=com.test.order.db4pg.entity.ReserveListBriefEntityPK
- 使用sharding-jdbc不分库、不分区、不使用PG的Pathman分区
#首次加载使用create配置,然后改为validate
spring.jpa.properties.hibernate.hbm2ddl.auto=validate
#1.不使用sharding-jdbc进行分库时,sharding-column可置为空(或删除该配置)
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.sharding-column=
#2.当不分区时该配置algorithm-inline-expression可置空(或删除该配置)
sharding.jdbc.config.sharding.tables.reserve_list_brief.databaseStrategy.inline.algorithm-inline-expression=
#3.actualDataNodes=ds_0指定可落入的数据库,reserve_list_brief为数据存储和查询指定的数据表名
sharding.jdbc.config.sharding.tables.reserve_list_brief.actualDataNodes=ds_0.reserve_list_brief
#4.数据表关联的实体类(该实体类中只包含主键和外键的字段)
sharding.jdbc.config.sharding.tables.reserve_list_brief.keyGeneratorClass=com.test.order.db4pg.entity.ReserveListBriefEntityPK
- 读写分离配置
#1.从库负载均衡算法类型:ROUND_ROBIN, RANDOM;默认值:ROUND_ROBIN
sharding.jdbc.config.sharding.master-slave-rules.ds_0.loadBalanceAlgorithmType=round_robin
#2.masterDataSourceName指定主库可以进行读写
sharding.jdbc.config.sharding.master-slave-rules.ds_0.masterDataSourceName=ds_master_0
#3.slaveDataSourceNames指定从库只读
sharding.jdbc.config.sharding.master-slave-rules.ds_0.slaveDataSourceNames=ds_master_0_slave_0,ds_master_0_slave_1