sharding-jdbc3.1.0整合SpringBoot 2.3.2数据分表

分手后的思念是犯贱 2023-03-02 07:54 15阅读 0赞

当数据量比较大的时候，比如单个表的数据量超过了1000W的数据，这时可以考虑将数据存储在不同的表中，比如将user表拆分为四个表user\_0、user\_1、 user\_2、user\_3装在四个表中。此时如图所示：

![微信截图\_20190626164245.png][20190626164245.png]

### **数据库准备** ###

<table> 
 <thead> 
  <tr> 
   <th>数据库类型</th> 
   <th>数据库</th> 
   <th>ip</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主</td> 
   <td>readwrite</td> 
   <td>192.168.39.129</td> 
  </tr> 
  <tr> 
   <td>从</td> 
   <td>readwrite</td> 
   <td>192.168.39.130</td> 
  </tr> 
  <tr> 
   <td>从</td> 
   <td>readwrite</td> 
   <td>192.168.39.131</td> 
  </tr> 
 </tbody> 
</table>

在主库初始化Mysql数据的脚本，初始化完后，从库也会创建这些表

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;
    
    /*Table structure for table `user_1` */
    
    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 AUTO_INCREMENT=150 DEFAULT CHARSET=utf8;
    
    /*Table structure for table `user_2` */
    
    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;
    
    /*Table structure for table `user_3` */
    
    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;

pom文件

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.3.2.RELEASE</version>
            <relativePath/> 
        </parent>
        <groupId>com.hzau</groupId>
        <artifactId>sharding-demo</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>sharding-demo</name>
        <description>Demo project for Spring Boot</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.3.2</version>
            </dependency>
            
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-generator</artifactId>
                <version>3.3.2</version>
            </dependency>
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-freemarker</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.13</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
    
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.23</version>
            </dependency>
            <dependency>
                <groupId>io.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>3.1.0</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

配置文件

sharding:
      jdbc:
        dataSource:
          names: db-test0,db-test1,db-test2
          # 配置主库
          db-test0: #org.apache.tomcat.jdbc.pool.DataSource
            type: com.alibaba.druid.pool.DruidDataSource
            driverClassName: com.mysql.jdbc.Driver
            url: jdbc:mysql://192.168.39.129:3306/readwrite?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
            username: root
            password: root
            #最大连接数
            maxPoolSize: 20
          db-test1: # 配置第一个从库
            type: com.alibaba.druid.pool.DruidDataSource
            driverClassName: com.mysql.jdbc.Driver
            url: jdbc:mysql://192.168.39.130:3306/readwrite?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
            username: root
            password: root 
            maxPoolSize: 20
          db-test2: # 配置第二个从库
            type: com.alibaba.druid.pool.DruidDataSource
            driverClassName: com.mysql.jdbc.Driver
            url: jdbc:mysql://192.168.39.131:3306/readwrite?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
            username: root
            password: root
            maxPoolSize: 20
    
        props:
          sql: # 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志！！！
            show: true
    
    sharding.jdbc.config.sharding.tables.user.actual-data-nodes: ds_0.user_$->{0..3}
    sharding.jdbc.config.sharding.tables.user.table-strategy.standard.sharding-column: id
    sharding.jdbc.config.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name: com.hzau.shardingdemo.config.MyPreciseShardingAlgorithm
    
    sharding.jdbc.config.sharding.master-slave-rules.ds_0.master-data-source-name: db-test0
    sharding.jdbc.config.sharding.master-slave-rules.ds_0.slave-data-source-names: db-test1,db-test2
    
    
    mybatis-plus:
      datasource: dataSource
      mapper-locations: classpath:/mapper/*Mapper.xml
      #别名扫描
      typeAliasesPackage: com.hzau.*.entity
    spring:
      main:
        allow-bean-definition-overriding: true

druid监控配置

package com.hzau.shardingdemo.config;
    
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    
    /**
     * @ClassName DruidDBConfig
     * @Description TODO
     * @Author yueyiming
     * @Date 2020/7/27 16:05
     * @Version 1.0
     * https://blog.csdn.net/hzau_itdog
     **/
    @Configuration
    public class DruidDBConfig {
    
    
        @Bean
        public ServletRegistrationBean druidServlet() {
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
            // IP白名单
            // servletRegistrationBean.addInitParameter("allow", "192.168.2.25,127.0.0.1");
            // IP黑名单(共同存在时，deny优先于allow)
            //servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
            //控制台管理用户
            servletRegistrationBean.addInitParameter("loginUsername", "admin");
            servletRegistrationBean.addInitParameter("loginPassword", "9527");
            //是否能够重置数据 禁用HTML页面上的“Reset All”功能
            servletRegistrationBean.addInitParameter("resetEnable", "false");
            return servletRegistrationBean;
        }
    
        @Bean
        public FilterRegistrationBean filterRegistrationBean() {
            FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
            filterRegistrationBean.addUrlPatterns("/*");
            filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return filterRegistrationBean;
        }
    }

MyPreciseShardingAlgorithm

根据id获取表名

package com.hzau.shardingdemo.config;
    
    import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
    import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
    
    import java.util.Collection;
    
    /**
     * @ClassName MyPreciseShardingAlgorithm
     * @Description TODO
     * @Author yueyiming
     * @Date 2020/7/28 14:00
     * @Version 1.0
     * https://blog.csdn.net/hzau_itdog
     **/
    public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
        @Override
        public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
            for (String tableName : availableTargetNames) {
                if (tableName.endsWith(shardingValue.getValue() % 4 + "")) {
                    return tableName;
                }
            }
            return null;
        }
    }

启动类上配置mapper扫描包

@SpringBootApplication
    @MapperScan({"com.hzau.*.dao","com.hzau.*.mapper"})
    public class ShardingDemoApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(ShardingDemoApplication.class, args);
        }
    
    }

### **测试** ###

user

package com.hzau.shardingdemo.entity;
    
    import lombok.Data;
    
    /**
     * @ClassName User
     * @Description TODO
     * @Author yueyiming
     * @Date 2020/7/28 14:04
     * @Version 1.0
     * https://blog.csdn.net/hzau_itdog
     **/
    @Data
    public class User {
        private Integer id;
        private String username;
        private String password;
    
    }

userMapper

/**
     * @ClassName UserMapper
     * @Description TODO
     * @Author yueyiming
     * @Date 2020/7/28 14:05
     * @Version 1.0
     * https://blog.csdn.net/hzau_itdog
     **/
    public interface UserMapper extends BaseMapper<User> {
    }

controller

package com.hzau.shardingdemo.controller;
    
    import com.hzau.shardingdemo.dao.UserMapper;
    import com.hzau.shardingdemo.entity.User;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.annotation.Resource;
    import java.util.List;
    
    /**
     * @ClassName UserController
     * @Description TODO
     * @Author yueyiming
     * @Date 2020/7/28 14:07
     * @Version 1.0
     * https://blog.csdn.net/hzau_itdog
     **/
    @RestController
    public class UserController {
        @Resource
        private UserMapper userMapper;
    
        @GetMapping("/users")
        public List<User> list() {
            return userMapper.selectList(null);
        }
    
        @GetMapping("/add")
        public Object add() {
    
            for(int i=100;i<150;i++) {
                User user = new User();
                user.setId(i);
                user.setUsername("hzau"+(i));
                user.setPassword("1233edwd");
                 userMapper.insert(user);
            }
            return "ok";
        }
    }

访问   127.0.0.1:8080/add

查看数据库

user\_0

![20200728145148735.png][]

user\_1

![20200728145224449.png][]

user\_2

![20200728145347769.png][]

user\_3

![20200728145404582.png][]

分表成功。

访问 127.0.0.1:8080/users

![20200728145734945.png][]

可以看到能查出来所有数据。

查看资料

[https://www.fangzhipeng.com/db/2019/06/30/sharding-jdbc-tables-ms.html][https_www.fangzhipeng.com_db_2019_06_30_sharding-jdbc-tables-ms.html]

[20190626164245.png]: /images/20230208/5853fabec1cd40c5a6d559af8529f066.png
[20200728145148735.png]: /images/20230208/c0aa71eef1384fba817c2a3d9663ed98.png
[20200728145224449.png]: /images/20230208/4e1f7a3298094d6cb18a5ee1e28f12cb.png
[20200728145347769.png]: /images/20230208/8a674d06c5454cb695230eca35efae05.png
[20200728145404582.png]: /images/20230208/cb1fb82d325c4c1bb7e927a4c5bc17a6.png
[20200728145734945.png]: /images/20230208/1d855ae0051f48768d1ea2f192f5d49c.png
[https_www.fangzhipeng.com_db_2019_06_30_sharding-jdbc-tables-ms.html]: https://www.fangzhipeng.com/db/2019/06/30/sharding-jdbc-tables-ms.html