1. ShardingSphere-JDBC 公共表
对于有些数据不需要分库分表,可以使用公共表。
1.1 环境搭建
环境说明:
SpringBoot 2.5.7
+MyBatisPlus
+ShardingSphere-JDBC 5.0.0-alpha
+Druid
+MySQL 8.0
1.1.1 pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.13</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0-alpha</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
1.1.2 创建数据库和表
按照水平分表的方式,创建数据库和数据库表
- 创建数据库:
ss_course_db_1
、ss_course_db_2
、ss_dict_db
- 在三个数据库中都创建表:
t_dict
drop table if EXISTS t_dict;
create table t_dict(
dict_id BIGINT(20) PRIMARY KEY,
uvalue VARCHAR(20) NOT NULL,
ustatus VARCHAR(10) NOT NULL
)
1.1.3 编写业务代码
此处编写业务代码略,具体代码可以下面的源码地址。代码里集成了Swagger
,用于方便测试。
1.1.4 配置文件
server.port=8003
spring.shardingsphere.enabled=true
# 打开sql输出日志
spring.shardingsphere.props.sql-show=true
# 配置数据源,给数据源起名称
spring.shardingsphere.datasource.names=m1,m2,m3
# 配置第一个数据源具体内容,包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.common.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.common.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/ss_course_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456
# 配置第二个数据源
spring.shardingsphere.datasource.m2.url=jdbc:mysql://127.0.0.1:3306/ss_course_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456
# 配置第三个数据
spring.shardingsphere.datasource.m3.url=jdbc:mysql://127.0.0.1:3306/ss_dict_db?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
spring.shardingsphere.datasource.m3.username=root
spring.shardingsphere.datasource.m3.password=123456
# 配置公共表
spring.shardingsphere.rules.sharding.tables.t_dict.actual-data-nodes=m$->{1..3}.t_dict
spring.shardingsphere.rules.sharding.broadcast-tables=t_dict
# 指定course表里面主键cid 生成策略 SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=123
# 指定分库策略 约定userId值偶数添加到m1库,userId是奇数添加到m2库
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expression=m$->{user_id%2 + 1}
# 指定分表策略 约定cid值偶数添加到t_course_1表,如果cid是奇数添加到t_course_2表
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expression=t_course_$->{cid % 2 + 1}
spring.shardingsphere.rules.sharding.tables.t_course.actual-data-nodes=m$->{1..2}.t_course_$->{1..2}
spring.shardingsphere.rules.sharding.tables.t_course.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.t_course.database-strategy.standard.sharding-algorithm-name=database-inline
spring.shardingsphere.rules.sharding.tables.t_course.table-strategy.standard.sharding-column=cid
spring.shardingsphere.rules.sharding.tables.t_course.table-strategy.standard.sharding-algorithm-name=table-inline
1.1.5 测试结果
启动程序,在浏览器输入:http://localhost:8003/swagger-ui.html
添加公共数据
查看数据库表数据
查看所有课程数据
2. 说明
源码地址:https://github.com/Hofanking/springboot-shardingsphere-example
源代码目录结构说明:
springboot-shardingsphere-example
|— shardingsphere-database (分库分表)
|— shardingsphere-database-table-write-read (分库分表读写分离)
|— shardingsphere-proxy-table (使用proxy分表)
|— shardingsphere-public (公共表)
|— shardingsphere-table (分表)
|— shardingsphere-write-read (读写分离)
--end--