ShardingSphere 5.0.0-alpha 分库分表解决方案(三)——公共表

timo-nbktp 1年前 ⋅ 1114 阅读

1. ShardingSphere-JDBC 公共表

对于有些数据不需要分库分表,可以使用公共表。

1.1 环境搭建

环境说明:SpringBoot 2.5.7MyBatisPlus + ShardingSphere-JDBC 5.0.0-alpha + DruidMySQL 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_1ss_course_db_2ss_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--

 

版权 本着开源共享、共同学习的精神,本文转载自 https://blog.csdn.net/zxd1435513775/article/details/122366496 , 如果侵权之处,请联系博主进行删除,谢谢~