SpringBoot 整合 shardingJDBC 轻松实现亿级文件表数据分库分表

一、方案实践

集成分为三步：添加依赖、增加配置类、添加配置、创建分片表。

 step1: 在数据库中创建file表，建表语句如下：

CREATE TABLE `file` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(100),
  `doc_id` INT,
  `file_size` INT,
  `upload_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

step2: 搭建一个Maven项目，在pom.xml中加入依赖，其中就包含访问数据库最为简单的几个组件。

<dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>2.2.1.RELEASE</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>

            <!-- mybatisplus依赖 -->
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.0.5</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.20</version>
            </dependency>
        </dependencies>
    </dependencyManagement>
 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!-- 数据源连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
        <!-- mysql连接驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- mybatisplus依赖 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3.3</version>
        </dependency>
    </dependencies>

 step3: 使用MyBatis-plus的方式，直接声明Entity和Mapper，映射数据库中的file表。

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "file")
public class FileEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "file_name", nullable = false, length = 255)
    private String fileName;

    @Column(name = "file_type", length = 100)
    private String fileType;

    @Column(name = "file_size")
    private Long fileSize; // 使用Long类型以支持更大的文件大小

    @Column(name = "upload_date", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date uploadDate;

    // Constructors
    public FileEntity() {
    }

    //省略。getter ... setter ....
}

public interface FileMapper extends BaseMapper<FileEntity> {
}

​ step4: 增加SpringBoot启动类，扫描mapper接口。

@SpringBootApplication
@MapperScan("com.roy.jdbcdemo.mapper")
public class App {
    public static void main(String[] args) {
        SpringApplication.run(App.class,args);
    }
}

​ step5: 在springboot的配置文件application.properties中增加数据库配置。

spring.datasource.druid.db-type=mysql
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql://localhost:3306/oadb?serverTimezone=UTC
spring.datasource.druid.username=root
spring.datasource.druid.password=root

 step6: 做一个单元测试，简单的把course课程信息插入到数据库，以及从数据库中进行查询。

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@SpringBootTest
public class FileJdbcTest {

    @Autowired
    private FileRepository fileRepository; // 假设你有一个FileRepository

    @Test
    public void addFile() {
        for (int i = 0; i < 10; i++) {
            FileEntity file = new FileEntity();
            file.setFileName("example" + i + ".txt");
            file.setFileType("txt");
            file.setFileSize((long) (1024 * 1024 * i)); // 示例文件大小
            file.setUploadDate(new java.util.Date()); // 当前时间
            fileRepository.save(file);
            // Save to database...
            System.out.println(file);
        }
    }

    @Test
    public void queryFile() {
        List<FileEntity> files = fileRepository.findAll(); // 查询所有文件
        files.forEach(file -> System.out.println(file));
    }
}

 OK，完成了！接下来执行单元测试，就可以完成与数据库的交互了。

2、引入ShardingSphere分库分表

接下来，我们将在这个简单案例上使用ShardingSphere快速file表的分库分表功能。

​ step1:调整pom.xml中的依赖，引入ShardingSphere。

​ ShardingSphere的实现机制和我们之前章节中使用DynamicDataSource框架实现读写分离很类似，也是在底层注入一个带有分库分表功能的DataSource数据源。因此，在调整依赖时，需要注意不要直接使用druid-sprint-boot-starter依赖了。因为这个依赖会在Spring容器中注入一个DataSource，这样再要使用ShardingSphere注入DataSource就会产生冲突了。

<dependencies>
        <!-- shardingJDBC核心依赖 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.2.1</version>
            <exclusions>
                <exclusion>
                    <artifactId>snakeyaml</artifactId>
                    <groupId>org.yaml</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- 坑爹的版本冲突 -->
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.33</version>
        </dependency>
        <!-- SpringBoot依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <exclusions>
                <exclusion>
                    <artifactId>snakeyaml</artifactId>
                    <groupId>org.yaml</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!-- 数据源连接池 -->
        <!--注意不要用这个依赖，他会创建数据源，跟上面ShardingJDBC的SpringBoot集成依赖有冲突 -->
        <!--        <dependency>-->
        <!--            <groupId>com.alibaba</groupId>-->
        <!--            <artifactId>druid-spring-boot-starter</artifactId>-->
        <!--            <version>1.1.20</version>-->
        <!--        </dependency>-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>
        <!-- mysql连接驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- mybatisplus依赖 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3.3</version>
        </dependency>
    </dependencies>

step2: 在对应数据库里创建分片表。

​ 按照我们之前的设计，去对应的数据库中自行创建file_1和file_2表。但是这里要注意，在创建分片表时，file表id字段就不要用默认的自增长了。因为数据分到两个表后，每个表都自增长，就没办法保证file表id字段的唯一性了。需要自定义主键策略一般是单独的主键生成接口或者雪花算法。

​ step3: 增加ShardingJDBC的分库分表配置

​ 然后，好玩的事情来了。应用层代码不需要做任何的修改，直接修改application.properties里的配置就可以完成我们之前设计的分库分表的目标。

# 打印SQL
spring.shardingsphere.props.sql-show = true
spring.main.allow-bean-definition-overriding = true

# ----------------数据源配置
# 指定对应的真实库
spring.shardingsphere.datasource.names=m0,m1
# 配置真实库
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/oadb?serverTimezone=UTC
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/oadb2?serverTimezone=UTC
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
#------------------------分布式序列算法配置
# 雪花算法，生成Long类型主键。
spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.props.worker.id=1
# 指定分布式主键生成策略
spring.shardingsphere.rules.sharding.tables.file.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.file.key-generate-strategy.key-generator-name=alg_snowflake
#-----------------------配置实际分片节点
spring.shardingsphere.rules.sharding.tables.file.actual-data-nodes=m$->{0..1}.file
#-----------------------配置分库策略，按id取模
spring.shardingsphere.rules.sharding.tables.file.database-strategy.standard.sharding-column=id
spring.shardingsphere.rules.sharding.tables.file.database-strategy.standard.sharding-algorithm-name=file_db_alg

spring.shardingsphere.rules.sharding.sharding-algorithms.file_db_alg.type=MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.file_db_alg.props.sharding-count=2
#给file表指定分表策略  standard-按单一分片键进行精确或范围分片
spring.shardingsphere.rules.sharding.tables.file.table-strategy.standard.sharding-column=cid
spring.shardingsphere.rules.sharding.tables.file.table-strategy.standard.sharding-algorithm-name=file_tbl_alg
# 分表策略-INLINE：按单一分片键分表
spring.shardingsphere.rules.sharding.sharding-algorithms.file_tbl_alg.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.file_tbl_alg.props.algorithm-expression=file_$->{cid%2+1}

配置过程，刚开始看会有点复杂，但是对应之前的设计图不难对应上。而且后面也会详细来解读配置过程。

这里主要需要理解一下的是配置中用到的Groovy表达式。 比如 m$->${0..1}.file_$->{1..2} 和 file_$->{cid%2+1} 。这是ShardingSphere支持的Groovy表达式，在后面会大量接触到这样的表达式。这个表达式中，$->{}部分为动态部分，大括号内的就是Groovy语句。 两个点，表示一个数据组的起点和终点。m$->${0..1}表示m0和m1两个字符串集合。file_$->{1..2}表示file_1和file_2集合。 file_$->{cid%2+1} 表示根据cid的值进行计算，计算的结果再拼凑上file_前缀。

​ 接下来再次执行addfile的单元测试，就能看到，十条课程信息被按照cid的奇偶，拆分到了m0.file_1和m1.file_2两张表中。

​ 在日志里也能看到实际的执行情况。

这个示例中，file信息只能平均分到两个表中，而无法均匀分到四个表中。这其实是根据cid进行计算的结果。而将file_tbl_alg的计算表达式改成 file_$->{((cid+1)%4).intdiv(2)+1} 后，理论上，如果cid是连续递增的，就可以将数据均匀分到四个表里。但是snowflake雪花算法生成的ID并不是连续的，所以有时候还是无法分到四个表。

补充：尽量不要使用批量插入。

​ 从这里可以看到，ShardingSphere实际上是将我们的SQL语句通过cid路由到了某一个分片上执行。那么可不可以把一个SQL语句路由到多个分片上执行呢？

​ 比如在MySQL中，是可以在一个Insert语句里批量插入多条数据的。像这样

insert into file values (1,'java',1001,1),(2,'java',1001,1);

 但是这样的SQL语句如果交给ShardingSphere去执行，那么这个语句就会造成困惑。到底应该往file_1还是file_2里插入？对于这种没有办法正确执行的SQL语句，ShardingSphere就会抛出异常。Insert statement does not support sharding table routing to multiple data nodes. 这个也是很多人在使用ShardingSphere时经常会遇到的问题。在这个示例中，可以将配置信息中 “配置分库策略，按cid取模” 的那一端配置中注释掉，也就是不生成cid，这样与批量插入的效果是一样的。也能模拟这种情况。

​ 这个示例也说明了，ShardingSphere并不是支持所有的SQL语句。

二，需求分析OA系统中大表file的分库方案

以上只是集成shardingJDBC了基本实现了将一个表拆分到了两个库中。但是对于大数据量下性能的要求需要做到找到合适的分片策略和算法来达到这个目的。对于一个数据量达到1亿的大表进行拆分。首先需要将每个表的数据量控制在3000W以下，并且是按照在目前的数据量增长下的能够支撑至少5年内的数据需求。假设这个表是在3年内达到的数据量，那么5年后新增的数据量最多在3个亿。按照一个表存3000W的数据那么需要拆分为10个库。

1，首先file表需要和字典表dict进行关联查询，那么就需要使用到 COMPLEX_INLINE复杂分片算法，并且将字典表dict配置为广播表

2，由于file和doc表都可以通过docid进行分片，因此可以通过配置绑定表优化查询性能。

最终分库后的架构为：

字典表数据库sql以及分库配置

字典表（dict）是所有的分片数据源中都存在的表，表结构及其数据在每个数据库中均完全一致，并且数据量不大且需要与海量数据的表进行关联查询的场景，因此将此配置为广播表。

建表：

CREATE TABLE `coursedb`.`dict_1`  (
                                    `dictId` bigint(0) NOT NULL,
                                    `dictkey` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
                                    `dictVal` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
                                    PRIMARY KEY (`dictId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

创建实体：

@TableName("dict")
public class Dict {
    private Long dictid;
    private String dictkey;
    private String dictval;
    
 // getter ... setter   
}

创建mapper

public interface DictMapper extends BaseMapper<Dict> {
}

配置广播规则： 配置方式很简单。 直接配置broadcast-tables就可以了。

# 打印SQL
spring.shardingsphere.props.sql-show = true
spring.main.allow-bean-definition-overriding = true

# ----------------数据源配置
# 指定对应的库
spring.shardingsphere.datasource.names=m0,m1

spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/oadb?serverTimezone=UTC
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/oadb2?serverTimezone=UTC
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
#------------------------分布式序列算法配置
# 生成字符串类型分布式主键。
spring.shardingsphere.rules.sharding.key-generators.dict_keygen.type=SNOWFLAKE
# 指定分布式主键生成策略
spring.shardingsphere.rules.sharding.tables.dict.key-generate-strategy.column=dictId
spring.shardingsphere.rules.sharding.tables.dict.key-generate-strategy.key-generator-name=dict_keygen
#-----------------------配置读写分离
# 要配置成读写分离的虚拟库
spring.shardingsphere.rules.sharding.tables.dict.actual-data-nodes=m$->{0..1}.dict
# 指定广播表。广播表会忽略分表的逻辑，只往多个库的同一个表中插入数据。
spring.shardingsphere.rules.sharding.broadcast-tables=dict

测试示例

  @Test
    public void addDict() {
        Dict dict = new Dict();
        dict.setDictkey("F");
        dict.setDictval("女");
        dictMapper.insert(dict);

        Dict dict2 = new Dict();
        dict2.setDictkey("M");
        dict2.setDictval("男");
        dictMapper.insert(dict2);
    }

 这样，对于dict字段表的操作就会被同时插入到两个库当中。

文档表数据库sql以及分库配置

​ 由于文档表和文件表会出现笛卡尔积关联或跨库关联，从而影响查询效率，为了优化查询效率因此改变file的分片逻辑从按照fileid分片按照docid分片，并配置绑定表，使成为分片规则一致的一组分片表，使用绑定表进行多表关联查询时，必须使用分片键进行关联。

创建doc表sql语句

CREATE TABLE `doc` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

​ 接下来同样增加映射实体以及Mapper。这里就略过了。

然后配置分片规则：

# 打印SQL
spring.shardingsphere.props.sql-show = true
spring.main.allow-bean-definition-overriding = true

# ----------------数据源配置
# 指定对应的库
spring.shardingsphere.datasource.names=m0

spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/oadb?serverTimezone=UTC
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
#------------------------分布式序列算法配置
# 生成字符串类型分布式主键。
spring.shardingsphere.rules.sharding.key-generators.usercourse_keygen.type=SNOWFLAKE
# 指定分布式主键生成策略
spring.shardingsphere.rules.sharding.tables.file.key-generate-strategy.column=docid
spring.shardingsphere.rules.sharding.tables.doc.key-generate-strategy.key-generator-name=doc_keygen
# ----------------------配置真实表分布
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=m0.file
spring.shardingsphere.rules.sharding.tables.user_course_info.actual-data-nodes=m0.doc
# ----------------------配置分片
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=docid
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=doc_tbl_alg

spring.shardingsphere.rules.sharding.tables.file.table-strategy.standard.sharding-column=docid
spring.shardingsphere.rules.sharding.tables.file.table-strategy.standard.sharding-algorithm-name=doc_tbl_alg
# ----------------------配置分表策略
spring.shardingsphere.rules.sharding.sharding-algorithms.doc_tbl_alg.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.doc_tbl_alg.props.algorithm-expression=doc_$->{Math.abs(docid.hashCode()%4).intdiv(2) +1}

spring.shardingsphere.rules.sharding.sharding-algorithms.file_tbl_alg.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.file_tbl_alg.props.algorithm-expression=file_$->{Math.abs(docid.hashCode()%4).intdiv(2) +1}
# 指定绑定表
spring.shardingsphere.rules.sharding.binding-tables[0]=doc,file

​ 然后把file表的数据都清空，重新插入一些有对应关系的file和doc表。

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.List;

@SpringBootTest
public class FileAndDocTest {

    @Autowired
    private DocMapper docMapper; // 假设你有一个DocRepository
    @Autowired
    private FileMapper fileMapper; // 假设你有一个FileRepository

    @Test
    public void addDocFileInfo() {
        for (int i = 0; i < 10; i++) {
            Doc doc = new Doc();
            doc.setTitle("Document Title " + i);
            doc.setDescription("This is a document description.");
            doc.setCreated_at(new java.util.Date());
            docMapper.insert(doc);

            int docId = doc.getId(); // 假设insert后返回生成的ID
            for (int j = 0; j < 5; j++) {
                File file = new File();
                file.setFileName("File Name " + j);
                file.setFileType("txt");
                file.setFileSize((long) (1024 * 1024 * j));
                file.setUploadDate(new java.util.Date());
                file.setDocId(docId); // 设置外键关联
                fileMapper.insert(file);
            }
        }
    }

    // 省略其他测试方法...
}

public interface FileMapper extends BaseMapper<File> {
    @Select("SELECT f.*, d.title, d.description FROM file f INNER JOIN doc d ON f.doc_id = d.id")
    List<FileWithDocInfo> queryFileWithDocInfo();
}

@Test
public void queryFileWithDocInfo() {
    List<FileWithDocInfo> fileWithDocInfos = fileMapper.queryFileWithDocInfo();
    for (FileWithDocInfo fileWithDocInfo : fileWithDocInfos) {
        System.out.println(fileWithDocInfo);
    }
}

​ 在进行查询时，可以先把application.properties文件中最后一行，绑定表的配置注释掉。此时两张表的关联查询将要进行笛卡尔查询。

​ 这种查询明显性能是非常低的，如果两张表的分片数更多，执行的SQL也会更多。而实际上，用户表和用户信息表，他们都是按照docid进行分片的，他们的分片规则是一致的。

​ 这样，再把绑定关系的注释加上，此时查询，就会按照相同的docid分片路由机制进行查询。

 在进行多表关联查询时，绑定表是一个非常重要的标准。

问题分析：

1，这个文件表涉及的逻辑经过分库分表之后是否会存在数据一致性问题？

（1）当某个需要支持事务的逻辑，会存在一致性问题，因为之前单库单表的事务控制不再支持多库多表形式的事务管理

（2）shardingJDBC默认的事务管理为XA模式，比较依赖数据库支持XA协议，并且需要额外引入包 shardingsphere-transaction-xa-core

参考博客

Sharding JDBC 实战：分布式事务处理-阿里云开发者社区