shardingsphere+springboot+mybatis 分库分表

一.前言

对于分库分表技术有很多，最近碰到一个需要对日志表按月切割，一个非常典型的横向切割数据库表的场景，在集成shardingsphere碰到了很多坑在此会说一下
官方文档地址

https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/configuration/

二.maven依赖

关键依赖

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.hs</groupId>
    <artifactId>SpringBoot_Sharding</artifactId>
    <version>1.0.1</version>


    <name>SpringBoot_Sharding</name>
    <!-- FIXME change it to the project's website -->
    <url>http://www.example.com</url>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.6.RELEASE</version>
        <relativePath/>
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>

        <mybatis-spring-boot>1.2.0</mybatis-spring-boot>
        <mysql-connector>5.1.39</mysql-connector>
        <fastjson>1.2.41</fastjson>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- Spring Boot Mybatis 依赖 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>
        <!-- MySQL 连接驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--druid数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
        </dependency>

        <!-- sharding-jdbc -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

    </dependencies>


    <build>
        <finalName>${project.artifactId}</finalName>
        <plugins>
            <!--运用SpringBoot 插件 使用spring-boot-devtools模块的应用,当classpath中的文件有改变时，会自动重启！ -->
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <fork>true</fork>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

对于依赖需要说明下，依赖的版本号最好不好修改 因为shardingsphere与springboot部分版本是不兼容的，会存在项目无法启动的情况，并且shardingsphere是在数据库连接池技术之上的，所以连接池是一定要引入的，链接池不要引入start依赖格式引入 这种非自动启动注入的，因为项目启动shardingsphere会去加载自己的链接，如果别的连接池启动会有冲突，具体原因没有探究清楚

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>

三.核心配置

# 引入配置文件
spring.profiles.active=log
server.port=8888
# 配置真实数据源
spring.shardingsphere.datasource.names=springboot0,springboot1

# 配置第 1 个数据源 注意数据源名称
#spring.shardingsphere.datasource.springboot0.type=com.alibaba.druid.pool.DruidDataSource
#spring.shardingsphere.datasource.springboot0.jdbc-url=jdbc:mysql://localhost:4806/springboot0?characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.springboot0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.springboot0.url=jdbc:mysql://localhost:4806/springboot0?characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.springboot0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.springboot0.username=root
spring.shardingsphere.datasource.springboot0.password=WEAVERemobile7!@#

# 配置第 2 个数据源
#spring.shardingsphere.datasource.springboot1.type=com.alibaba.druid.pool.DruidDataSource
#spring.shardingsphere.datasource.springboot1.jdbc-url=jdbc:mysql://localhost:4806/springboot1?characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.springboot1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.springboot1.url=jdbc:mysql://localhost:4806/springboot0?characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.springboot1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.springboot1.username=root
spring.shardingsphere.datasource.springboot1.password=WEAVERemobile7!@#

# 默认数据源，未分片的表默认执行库
spring.shardingsphere.sharding.default-data-source-name=springboot0

# 配置eb_msgpush_requestlog 表规则  
# $->{0..1}代表数据源springboot0-1  
spring.shardingsphere.sharding.tables.eb_msgpush_requestlog.actualDataNodes=springboot$->{0..1}.eb_msgpush_requestlog2021_0$->{3..4}

## 分片键 数据库字段 注意数据表eb_msgpush_requestlog
spring.shardingsphere.sharding.tables.eb_msgpush_requestlog.databaseStrategy.standard.shardingColumn=tenant_key
## 自定义 分库 算法：ClassName 为自定义算法类
spring.shardingsphere.sharding.tables.eb_msgpush_requestlog.databaseStrategy.standard.preciseAlgorithmClassName=com.example.algorithm.MyPreciseDBShardingAlgorithm

# 分表策略
## 分片键 数据库字段 注意数据表eb_msgpush_requestlog
spring.shardingsphere.sharding.tables.eb_msgpush_requestlog.tableStrategy.standard.shardingColumn=tenant_key
## 自定义 分库 算法：ClassName 为自定义算法类
spring.shardingsphere.sharding.tables.eb_msgpush_requestlog.tableStrategy.standard.preciseAlgorithmClassName=com.example.algorithm.MyPreciseTableShardingAlgorithm

# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true

#mybatis
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapperLocations=classpath:mapper/*.xml

4.分片算法(简答分片)

注意:分库分表算法的分片健 一定要注意与sql中where语句中的tenant_key条件一致 只有这样才会走分片算法 否则不会走
如

  select * from eb_msgpush_requestlog where data_type=#{dataType} and tenant_key=#{tenantKey}  and delete_type = 0

1.分库算法

/**
 * @PACKAGE_NAME: com.hs.algorithm
 * @ClassName: MyPreciseDBShardingAlgorithm
 * @Description: 自定义数据库的精确分片算法  按tenant_key分片
 * @Date: 2020-06-18 17:28
 **/
public class MyPreciseDBShardingAlgorithm implements PreciseShardingAlgorithm<String> {

    private final Logger logger = LoggerFactory.getLogger(this.getClass());
    /**
     * 数据分片
     *
     * @param dbNames              实际数据源集合; springboot0、springboot1
     * @param preciseShardingValue 分片键 preciseShardingValue 用户名值
     * @return
     */
    @Override
    public String doSharding(Collection<String> dbNames, PreciseShardingValue<String> preciseShardingValue) {
        String dbName = "e10-ebridge-" + preciseShardingValue.getValue();
        if(dbNames.contains(dbName)){
            return dbName;
        }

        throw new UnsupportedOperationException();
    }
}

2.分表算法

/**
 * @PACKAGE_NAME: com.hs.algorithm
 * @ClassName: MyPreciseTableShardingAlgorithm
 * @Description: 自定义表的精确分片算法，按月份分片
 * @Date: 2020-06-18 17:11
 **/
public class MyPreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {

    private static DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy_MM");


    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> preciseShardingValue) {
        String targetTable = preciseShardingValue.getLogicTableName() + LocalDate.now().format(formatter);
        if (availableTargetNames.contains(targetTable)){
            return targetTable;
        }
        throw new UnsupportedOperationException("无效的表名称: " + targetTable);
    }
}

源码地址
https://github.com/2250749979/shardingspheredemo