Sharding-JDBC+ Mybatis-Plus 实现分库分表

男娘i 2022-11-09 11:23 124阅读 0赞

## 前言 ##

Sharding-jdbc是开源的数据库操作中间件；这篇文章主要通过Sharding-JDBC 和mybatis-plus 需要的配置文件和maven依赖。

## 实践 ##

**一、添加maven依赖。**

<dependencies>
           <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.3.2</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
                <version>8.0.13</version>
    
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.10</version>
                <scope>compile</scope>
            </dependency>
    
            <dependency>
                <groupId>com.github.xiaoymin</groupId>
                <artifactId>knife4j-spring-boot-starter</artifactId>
                <version>2.0.5</version>
            </dependency>
    
            <dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.5.7</version>
            </dependency>
    
            <dependency>
                <groupId>io.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>3.1.0</version>
                <exclusions>
                    <exclusion>
                        <artifactId>guava</artifactId>
                        <groupId>com.google.guava</groupId>
                    </exclusion>
                </exclusions>
            </dependency>
    
        </dependencies>

**二、appliaction.yml**

server:
      port: 8082
    spring:
      main:
       allow-bean-definition-overriding: true #当遇到同样名字的时否允许覆盖注册候，是
    sharding:
      jdbc:
        datasource:
          names: ds0,ds1
          ds0: 
            type: com.zaxxer.hikari.HikariDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            jdbc-url: jdbc:mysql://1.15.63.xxx:3306/sync_test?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
            username: root
            password: 123456
            maxPoolSize: 30
            minPoolSize: 5
          ds1: 
             driver-class-name: com.mysql.cj.jdbc.Driver
             type: com.zaxxer.hikari.HikariDataSource
             jdbc-url: jdbc:mysql://1.15.63.xxx:3306/sync_test2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
             username: root
             password: 123456
             maxPoolSize: 30
             minPoolSize: 5
        config:
          sharding:
            tables: 
               order_master:
                 actual-data-nodes: ds$->{ 0..1}.order_master$->{ 0..2}
                 database-strategy: 
                   #standard:
                     #sharding-column: order_time
                     #preciseAlgorithmClassName: com.yin.generateCode.provider.config.OrderTimePreciseShardingAlgorithm
                   inline:
                     sharding-column: order_id
                     algorithm-expression: ds$->{ order_id % 2}
                 table-strategy: 
                   inline:
                     sharding-column: order_id
                     algorithm-expression: order_master$->{ order_id % 3}
                 keyGeneratorColumnName: order_id
               order_detail:
                 actual-data-nodes: ds$->{ 0..1}.order_detail$->{ 0..1}
                 database-strategy:
                   # standard:
                      #sharding-column: order_time
                      #preciseAlgorithmClassName: com.yin.generateCode.provider.config.OrderTimePreciseShardingAlgorithm
                    inline:
                      sharding-column: order_id
                      algorithm-expression: ds$->{ order_id % 2}
                 table-strategy:
                    inline:
                      sharding-column: order_id
                      algorithm-expression: order_detail->{ order_id % 3}
                    keyGeneratorColumnName: order_id
    
                 #key-generator-column-name: order_id
                 #keyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
                 #将相互关联数据表进行关联，可以减少笛卡尔积
            binding-tables:
              - order_master,order_detail
              #每个数据库中都创建一张数据字典表base_dict,然后实现新增，修改，删除，查询，查询所有表是同步更新。
            broadcast-tables:
              - base_dict
            #defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
            #defaultKeyGenerator:
              #type: SNOWFLAKE
          props:
            sql.show: true
    
            
            
    mybatis:
      configuration:
        map-underscore-to-camel-case: true

**三、通过范围来分库定义类**

package com.yin.generateCode.provider.config;
    
    import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
    import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
    
    import java.util.Calendar;
    import java.util.Collection;
    import java.util.Date;
    import java.util.Iterator;
    import java.util.concurrent.CompletableFuture;
    
    
    public class OrderTimePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date>{ 
    	
    	Date[] dateRanges = new Date[2];
    	
    	{ 
    		Calendar cal = Calendar.getInstance();
    		cal.set(2019, 1, 1, 0, 0, 0);
    		dateRanges[0] = cal.getTime();
    		cal.set(2020, 1, 1, 0, 0, 0);
    		dateRanges[1] = cal.getTime();
    	}
    
    	@Override
    	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) { 
    		
    		Date d = shardingValue.getValue();
    		Iterator<String> ite = availableTargetNames.iterator();
    		String target = null;
    		for(Date s : dateRanges) { 
    			target = ite.next();
    			if(d.before(s)) { 
    				break;
    			}
    		}
    		return target;
    	}
    
    }

## 验证 ##

1、插入语句是否偶数在ds0 奇数点u在ds1,且是否因为是否被3整除分成3张表。  
2、 配置了binding-tables 是否减少笛卡尔积，是否关联关系的时候。  
3、配置 broadcast-tables 是否同时操作多个库数据。

## 主要事项 ##

1、由于我们分库分表是根据orderId 是否被2、被3整除来区分的，所以我们orderId一定是数值类型。

2、由于每毫秒从0开始计数，如果id生成不频繁，即每毫秒只会生成一个甚至几毫秒才需要生成一个，那么计数器就永远都是0，生成的id用于取模分表的话，就会出现永远都是插入到一个表（或规律的那几个表） 这里版本sharding-jdbc 3.1 之前会出现这个问题。需要用重写雪花算法