sharding-jdbc02-分库分表实践

一、前文已经搭配好mysql主从环境，接着用springboot+druid+mybatisplus+shardingjdbc实践

springboot:2.3.5

pom.xml


        
            com.alibaba
            druid-spring-boot-starter
            1.1.10
        
        
        
            com.baomidou
            mybatis-plus-boot-starter
            3.4.0
        
        
        
            org.springframework.boot
            spring-boot-starter-jdbc
        
        
            org.springframework.boot
            spring-boot-starter-web
        
        
            mysql
            mysql-connector-java
            runtime
        
        
            org.projectlombok
            lombok
            true
        

        
            org.apache.shardingsphere
            sharding-jdbc-spring-boot-starter
            4.1.1

数据库表用到cool1,cool2

USE `cool`;




DROP TABLE IF EXISTS `user_0`;

CREATE TABLE `user_0` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAr(12) NOT NULL,
  `password` VARCHAr(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS `user_1`;

CREATE TABLE `user_1` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAr(12) NOT NULL,
  `password` VARCHAr(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS `user_2`;

CREATE TABLE `user_2` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAr(12) NOT NULL,
  `password` VARCHAr(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS `user_3`;

CREATE TABLE `user_3` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAr(12) NOT NULL,
  `password` VARCHAr(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_4`;
CREATE TABLE `user_4` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAr(12) NOT NULL,
  `password` VARCHAr(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8;


USE `cool2`;




DROP TABLE IF EXISTS `user_0`;

CREATE TABLE `user_0` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAr(12) NOT NULL,
  `password` VARCHAr(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS `user_1`;

CREATE TABLE `user_1` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAr(12) NOT NULL,
  `password` VARCHAr(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS `user_2`;

CREATE TABLE `user_2` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAr(12) NOT NULL,
  `password` VARCHAr(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS `user_3`;

CREATE TABLE `user_3` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAr(12) NOT NULL,
  `password` VARCHAr(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_4`;

CREATE TABLE `user_4` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAr(12) NOT NULL,
  `password` VARCHAr(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8;

配置文件：

spring:
  shardingsphere:
    datasource:
      names: master01,slave01,slave02,master11,slave11,slave12
      # 配置主库
      master01: #org.apache.tomcat.jdbc.pool.DataSource
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.0.197:3306/cool?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: Root@123456
        #最大连接数
        maxPoolSize: 20
      slave01: # 配置第一个从库
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.1.129:3306/cool?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: Root@123456
        maxPoolSize: 20
      slave02: # 配置第二个从库
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.0.125:3306/cool?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: Root@123456
        maxPoolSize: 20
      # 配置第二个主库
      master11: #org.apache.tomcat.jdbc.pool.DataSource
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.0.197:3306/cool2?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: Root@123456
        #最大连接数
        maxPoolSize: 20
      slave11: # 配置第二个主库的第一个从库
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.1.129:3306/cool2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: Root@123456
        maxPoolSize: 20
      slave12: # 配置第二个主库的第二个从库
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.0.125:3306/cool2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: Root@123456
        maxPoolSize: 20
#    masterslave: # 配置读写分离
#      load-balance-algorithm-type: ROUND_ROBIN # 配置从库选择策略，提供轮询与随机，这里选择用轮询//random 随机 //round_robin 轮询
#      name: db1s2
#      master-data-source-name: master01
#      slave-data-source-names: slave01,slave02
    props:
      sql: # 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志！！！
      show: true
      # 分表配置
#    sharding:
#      tables:
#        user: #分表，数据库实际的表名称
#          actual-data-nodes: ds_0.user_$->{0..3}
#          table-strategy:
#            standard:
#              sharding-column: id
#              precise-algorithm-class-name: com.sharding.ms.config.MyPreciseShardingAlgorithm # 分表策略
#      master-slave-rules:
#        ds_0:
#          master-data-source-name: master01
#          slave-data-source-names: slave01,slave02
      # 分库分表配置
    sharding:
      default-database-strategy:
        inline:
          sharding-column: id
          algorithm-expression: ds_$->{id % 2} # 分库策略
      tables:
        user:
          actual-data-nodes: ds_$->{0..1}.user_$->{0..4}
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: user_$->{id % 5} # 分表策略
      master-slave-rules:
        ds_0:
          master-data-source-name: master01
          slave-data-source-names: slave01,slave02
        ds_1:
          master-data-source-name: master11
          slave-data-source-names: slave11,slave12

启动类

// 使用druid多数据源需要把自身的数据源类排除
@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class, JtaAutoConfiguration.class})
@MapperScan("com.sharding.ms.dao")
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

测试类，基本的增删改查

    @Autowired
    private UserService userService;

    @GetMapping("/list")
    public Object list() {
        return userService.list();
    }

//    @GetMapping("/add")
//    public Object add(@RequestParam Integer id, @RequestParam String username, @RequestParam String  password) {
//        User user = new User();
//        user.setId(id);
//        user.setUsername(username);
//        user.setPassword(password);
//        return userService.save(user);
//    }

    @GetMapping("/add")
    public Object add() {

        for(int i=100;i<150;i++) {
            User user = new User();
            user.setId(i);
            user.setUsername("test"+(i));
            user.setPassword("1233edwd");
            userService.save(user);
        }
        return "ok";
    }

测试结果：

可以看到已经根据设置的分库分表策略存储数据。

本文参考了Sharding-JDBC教程：Spring Boot整合Sharding-JDBC实现分库分表+读写分离_方志朋的博客-CSDN博客_sharding-jdbc springboot

其实，这个只是体验了解sharding-jdbc而已，了解的过程我就有一些疑问的，比如分页查询，排序，分页查询中的一对多关系查询，还有一些sharding-jdbc的局限等等。

sharding-jdbc02-分库分表实践

Java相关栏目本月热门文章