SpringBoot集成ShardingSphere-JDBC 5.3.X实现分库分表

Mr_Richard

于 2024-07-26 18:50:50 发布

阅读量573

点赞数 6

分类专栏： mysql 文章标签： spring boot 后端 java

本文链接：https://blog.csdn.net/Mr_Richard/article/details/140722451

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.导入POM依赖

pom.xml

<dependencies>
    <!-- Spring Boot Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>3.0.8</version>
    </dependency>

    <!-- MyBatis-Plus -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.5</version>
    </dependency>

    <!-- MySQL Connector -->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.0.33</version>
        <scope>runtime</scope>
    </dependency>

    <!-- Spring Boot JDBC Starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
        <version>3.0.8</version>
    </dependency>

    <!-- Spring Data Redis -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-redis</artifactId>
        <version>3.0.8</version>
    </dependency>

    <!-- Redisson Spring Boot Starter -->
    <dependency>
        <groupId>org.redisson</groupId>
        <artifactId>redisson-spring-boot-starter</artifactId>
        <version>3.21.3</version>
    </dependency>

    <!-- ShardingSphere JDBC Core -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core</artifactId>
        <version>5.3.2</version>
    </dependency>

    <!-- Lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version> <!-- 请根据实际情况调整版本号 -->
    </dependency>

    <!-- Hutool-All -->
    <dependency>
        <groupId>cn.hutool</groupId>
        <artifactId>hutool-all</artifactId>
        <version>5.8.20</version>
    </dependency>
</dependencies>

数据t_user表结构:
将其在两个数据库中分别创建16张表，编号0-15，即t_user_0到t_user_15。

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `username` varchar(256) DEFAULT NULL COMMENT '用户名',
  `password` varchar(512) DEFAULT NULL COMMENT '密码',
  `real_name` varchar(256) DEFAULT NULL COMMENT '真实姓名',
  `phone` varchar(128) DEFAULT NULL COMMENT '手机号',
  `mail` varchar(512) DEFAULT NULL COMMENT '邮箱',
  `deletion_time` bigint(20) DEFAULT NULL COMMENT '注销时间戳',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `del_flag` tinyint(1) DEFAULT NULL COMMENT '删除标识 0：未删除 1：已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.添加配置文件

application.yml:

server:
  port:
    8001  # 服务端口

spring:
  datasource:
    # ShardingSphere 对 Driver 自定义，实现分库分表等隐藏逻辑
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    # ShardingSphere 配置文件路径
    url: jdbc:shardingsphere:classpath:shardingsphere-config.yml

  # redis连接
  data:
    redis:
      host: 192.168.150.102
      database: 0
      password: 360421
      port: 6379


mybatis-plus:
  configuration:
    # 输出mybatisplus日志到控制台
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

shardingsphere-config.yml

# 数据源集合
dataSources:
  # 数据库1
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/link?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
  # 数据库2
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.150.102:3306/link?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: 123456


rules:
  - !SHARDING
    tables:
      t_user:
        # 真实数据节点，比如数据库源以及数据库在数据库中真实存在的
        actualDataNodes: ds_${0..1}.t_user_${0..15}
        # 分表策略
        tableStrategy:
          # 用于单分片键的标准分片场景
          standard:
            # 分片键
            shardingColumn: username
            # 分片算法，对应 rules[0].shardingAlgorithms
            shardingAlgorithmName: user_table_hash_mod
        # 分表策略
        databaseStrategy:
          # 用于单分片键的标准分片场景
          standard:
            # 分片键
            shardingColumn: username
            # 分片算法，对应 rules[0].shardingAlgorithms
            shardingAlgorithmName: database_hash_mod

    # 分片算法
    shardingAlgorithms:
      # 数据表分片算法
      user_table_hash_mod:
        # 根据分片键 Hash 分片
        type: HASH_MOD
        # 分片数量
        props:
          sharding-count: 16
      # 数据库分片算法
      database_hash_mod:
        # 根据分片键 Hash 分片
        type: HASH_MOD
        props:
          sharding-count: 2  # 数据库分片数量

# 展现逻辑 SQL & 真实 SQL
props:
  sql-show: true

参考链接：
ShardingSphere-JDBC 高版本和低版本配置文件差异
 ShardingSphere-JDBC 开发手册

3.测试

编写一个新增用户接口进行测试，日志如下：

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5a756f0e] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@689669131 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@6a8ebec9] will not be managed by Spring
==>  Preparing: INSERT INTO t_user ( username, password, real_name, phone, mail, create_time, update_time, del_flag ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )
==> Parameters: admintest(String), 123456(String), 基对如据史(String), 18125606346(String), e.nsut@qq.com(String), 2024-07-26T18:09:00.149410600(LocalDateTime), 2024-07-26T18:09:00.149410600(LocalDateTime), false(Boolean)
2024-07-26T18:09:00.806+08:00  INFO 25808 --- [nio-8001-exec-2] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_user  ( username, password, real_name, phone, mail,  create_time, update_time, del_flag )  VALUES (  ?, ?, ?, ?, ?,  ?, ?, ?  )
2024-07-26T18:09:00.807+08:00  INFO 25808 --- [nio-8001-exec-2] ShardingSphere-SQL                       : Actual SQL: ds_1 ::: INSERT INTO t_user_1  ( username, password, real_name, phone, mail,  create_time, update_time, del_flag )  VALUES (?, ?, ?, ?, ?, ?, ?, ?) ::: [admintest, 123456, 基对如据史, 18125606346, e.nsut@qq.com, 2024-07-26T18:09:00.149410600, 2024-07-26T18:09:00.149410600, false]
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5a756f0e]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@30e6f6c8] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@789486265 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@6a8ebec9] will not be managed by Spring
==>  Preparing: INSERT INTO t_user ( username, real_name, phone, mail, create_time, update_time, del_flag ) VALUES ( ?, ?, ?, ?, ?, ?, ? )
==> Parameters: 沈娟(String), 素生气族形(String), 18132977193(String), m.sxxrnw@qq.com(String), 2024-07-26T18:12:20.603614200(LocalDateTime), 2024-07-26T18:12:20.603614200(LocalDateTime), false(Boolean)
2024-07-26T18:12:20.605+08:00  INFO 25808 --- [nio-8001-exec-5] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_user  ( username,  real_name, phone, mail,  create_time, update_time, del_flag )  VALUES (  ?,  ?, ?, ?,  ?, ?, ?  )
2024-07-26T18:12:20.605+08:00  INFO 25808 --- [nio-8001-exec-5] ShardingSphere-SQL                       : Actual SQL: ds_1 ::: INSERT INTO t_user_7  ( username,  real_name, phone, mail,  create_time, update_time, del_flag )  VALUES (?, ?, ?, ?, ?, ?, ?) ::: [沈娟, 素生气族形, 18132977193, m.sxxrnw@qq.com, 2024-07-26T18:12:20.603614200, 2024-07-26T18:12:20.603614200, false]
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@30e6f6c8]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5322de4b] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@371214923 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@6a8ebec9] will not be managed by Spring
==>  Preparing: INSERT INTO t_user ( username, password, real_name, phone, mail, create_time, update_time, del_flag ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )
==> Parameters: 高伟(String), adipisicing eu ut magna sint(String), 已口称十农(String), 18184270408(String), f.qdlmgee@qq.com(String), 2024-07-26T18:12:32.161580900(LocalDateTime), 2024-07-26T18:12:32.162605300(LocalDateTime), false(Boolean)
2024-07-26T18:12:32.165+08:00  INFO 25808 --- [nio-8001-exec-6] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_user  ( username, password, real_name, phone, mail,  create_time, update_time, del_flag )  VALUES (  ?, ?, ?, ?, ?,  ?, ?, ?  )
2024-07-26T18:12:32.165+08:00  INFO 25808 --- [nio-8001-exec-6] ShardingSphere-SQL                       : Actual SQL: ds_1 ::: INSERT INTO t_user_7  ( username, password, real_name, phone, mail,  create_time, update_time, del_flag )  VALUES (?, ?, ?, ?, ?, ?, ?, ?) ::: [高伟, adipisicing eu ut magna sint, 已口称十农, 18184270408, f.qdlmgee@qq.com, 2024-07-26T18:12:32.161580900, 2024-07-26T18:12:32.162605300, false]
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5322de4b]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3fff3f0d] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@2063447702 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@6a8ebec9] will not be managed by Spring
==>  Preparing: INSERT INTO t_user ( username, password, real_name, phone, mail, create_time, update_time, del_flag ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )
==> Parameters: 谭洋(String), in consectetur ullamco(String), 常用式斗备克(String), 18169095185(String), x.dmfeyghsd@qq.com(String), 2024-07-26T18:36:40.128294600(LocalDateTime), 2024-07-26T18:36:40.128294600(LocalDateTime), false(Boolean)
2024-07-26T18:36:40.130+08:00  INFO 25808 --- [nio-8001-exec-8] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_user  ( username, password, real_name, phone, mail,  create_time, update_time, del_flag )  VALUES (  ?, ?, ?, ?, ?,  ?, ?, ?  )
2024-07-26T18:36:40.130+08:00  INFO 25808 --- [nio-8001-exec-8] ShardingSphere-SQL                       : Actual SQL: ds_0 ::: INSERT INTO t_user_14  ( username, password, real_name, phone, mail,  create_time, update_time, del_flag )  VALUES (?, ?, ?, ?, ?, ?, ?, ?) ::: [谭洋, in consectetur ullamco, 常用式斗备克, 18169095185, x.dmfeyghsd@qq.com, 2024-07-26T18:36:40.128294600, 2024-07-26T18:36:40.128294600, false]
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3fff3f0d]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@58e05fa2] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@501402330 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@6a8ebec9] will not be managed by Spring
==>  Preparing: INSERT INTO t_user ( username, real_name, phone, mail, create_time, update_time, del_flag ) VALUES ( ?, ?, ?, ?, ?, ?, ? )
==> Parameters: 杨磊(String), 儿增类近将(String), 18655586232(String), w.grxpger@qq.com(String), 2024-07-26T18:36:48.450052(LocalDateTime), 2024-07-26T18:36:48.450052(LocalDateTime), false(Boolean)
2024-07-26T18:36:48.452+08:00  INFO 25808 --- [io-8001-exec-10] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_user  ( username,  real_name, phone, mail,  create_time, update_time, del_flag )  VALUES (  ?,  ?, ?, ?,  ?, ?, ?  )
2024-07-26T18:36:48.452+08:00  INFO 25808 --- [io-8001-exec-10] ShardingSphere-SQL                       : Actual SQL: ds_0 ::: INSERT INTO t_user_2  ( username,  real_name, phone, mail,  create_time, update_time, del_flag )  VALUES (?, ?, ?, ?, ?, ?, ?) ::: [杨磊, 儿增类近将, 18655586232, w.grxpger@qq.com, 2024-07-26T18:36:48.450052, 2024-07-26T18:36:48.450052, false]
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@58e05fa2]

观察日志中的逻辑SQL和物理SQL：
逻辑SQL: Logic SQL:INSERT INTO t_user ( username, password, real_name, phone, mail, create_time, update_time, del_flag ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )
物理SQL：Actual SQL: ds_1 ::: INSERT INTO t_user_1 ( username, password, real_name, phone, mail, create_time, update_time, del_flag ) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ::: [admintest, 123456, 基对如据史, 18125606346, e.nsut@qq.com, 2024-07-26T18:09:00.149410600, 2024-07-26T18:09:00.149410600, false]

4.补充

ShardingSphere 数据分片核心概念是逻辑表与真实表：
逻辑表是指相同结构的水平拆分数据库（表）的逻辑名称，是 SQL 中表的逻辑标识，如本例中的t_user；
真实表在水平拆分的数据库中真实存在的物理表，如本例中的t_user_0。
本例选择username作为分片键，它被设置一个唯一索引，能够唯一标识一条用户记录。分片键是指用于将数据库（表）水平拆分的数据库字段，分片键应该保证数据的均匀分布在各个分片上，避免出现热点数据集中在某个分片上的情况。

Mr_Richard

关注

6
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot集成ShardingSphere-JDBC 5.3.X实现分库分表

pom.xml</</</</</</</</</</</</</</</</</</</</</</</</</</</</</</</</</</</</</</数据t_user表结构:将其在两个数据库中分别创建16张表，编号0-15，即t_user_0到t_user_15。
复制链接

扫一扫

专栏目录