Java實戰：教你如何進行資料庫分庫分表

華為雲開發者社群發表於2021-07-28

摘要：本文通過實際案例，說明如何按日期來對訂單資料進行水平分庫和分表，實現資料的分散式查詢和操作。

本文分享自華為雲社群《資料庫分庫分表Java實戰經驗總結丨【綻放吧！資料庫】》，作者： jackwangcumt。

我們知道，當前的應用都離不開資料庫，隨著資料庫中的資料越來越多，單表突破效能上限記錄時，如MySQL單表上線估計在近千萬條內，當記錄數繼續增長時，從效能考慮，則需要進行拆分處理。而拆分分為橫向拆分和縱向拆分。一般來說，採用橫向拆分較多，這樣的表結構是一致的，只是不同的資料儲存在不同的資料庫表中。其中橫向拆分也分為分庫和分表。

1 示例資料庫準備

為了說清楚如何用Java語言和相關框架實現業務表的分庫和分表處理。這裡首先用MySQL資料庫中建立兩個獨立的資料庫例項，名字為mydb和mydb2，此可演示分庫操作。另外在每個資料庫例項中，建立12個業務表，按年月進行資料拆分。具體的建立表指令碼如下：

CREATE TABLE `t_bill_2021_1` (
  `order_id` bigint(20) NOT NULL  COMMENT '訂單id',
  `user_id` int(20) NOT NULL COMMENT '使用者id',
  `address_id` bigint(20) NOT NULL COMMENT '地址id',
  `status` char(1) DEFAULT NULL COMMENT '訂單狀態',
  `create_time` datetime DEFAULT NULL COMMENT '建立時間',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t_bill_2021_2` (
  `order_id` bigint(20) NOT NULL  COMMENT '訂單id',
  `user_id` int(20) NOT NULL COMMENT '使用者id',
  `address_id` bigint(20) NOT NULL COMMENT '地址id',
  `status` char(1) DEFAULT NULL COMMENT '訂單狀態',
  `create_time` datetime DEFAULT NULL COMMENT '建立時間',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 省略....
CREATE TABLE `t_bill_2021_12` (
  `order_id` bigint(20) NOT NULL  COMMENT '訂單id',
  `user_id` int(20) NOT NULL COMMENT '使用者id',
  `address_id` bigint(20) NOT NULL COMMENT '地址id',
  `status` char(1) DEFAULT NULL COMMENT '訂單狀態',
  `create_time` datetime DEFAULT NULL COMMENT '建立時間',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

成功執行指令碼後，在MySQL管理工具中可以看到如下的示例介面：

Java實戰：教你如何進行資料庫分庫分表

2 分庫分表實現

在Java語言下的框架中，有眾多的開源框架，其中關於分庫分表的框架，可以選擇Apache ShardingSphere，其官網介紹說：ShardingSphere 是一套開源的分散式資料庫解決方案組成的生態圈，它由 JDBC、Proxy 和 Sidecar（規劃中）這 3 款既能夠獨立部署，又支援混合部署配合使用的產品組成。它們均提供標準化的資料水平擴充套件、分散式事務和分散式治理等功能，可適用於如 Java 同構、異構語言、雲原生等各種多樣化的應用場景。Apache ShardingSphere 5.x 版本開始致力於可插拔架構。目前，資料分片、讀寫分離、資料加密、影子庫壓測等功能，以及 MySQL、PostgreSQL、SQLServer、Oracle 等 SQL 與協議的支援，均通過外掛的方式織入專案。官網地址為： https://shardingsphere.apache.org/index_zh.html 。

下面的示例採用Spring Boot框架來實現，相關的庫通過Maven進行管理。首先給出pom.xml配置檔案的定義：

<?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.5.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>wyd</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>wyd</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
        <mybatis-plus.version>3.1.1</mybatis-plus.version>
        <sharding-sphere.version>4.0.0-RC2</sharding-sphere.version>
        <shardingsphere.version>5.0.0-beta</shardingsphere.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.9.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

其次，給出一個實體類，它對應於上述建立的資料庫表t_bill，其定義如下：

package com.example.wyd.dao;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.util.Date;
@Data
@TableName("t_bill")
public class Bill {
    private Long orderId;
    private Integer userId;
    private Long addressId;
    private String status;
    private Date createTime;
    public void setOrderId(Long orderId) {
        this.orderId = orderId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public void setAddressId(Long addressId) {
        this.addressId = addressId;
    }
    public void setStatus(String status) {
        this.status = status;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
}

對映類BillMapper定義如下：

package com.example.wyd.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.wyd.dao.Bill;
public interface BillMapper extends BaseMapper<Bill> {

}

服務類介面定義如下：

package com.example.wyd.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.wyd.dao.Bill;
public interface BillService extends IService<Bill> {

}

服務類介面的實現類定義如下：

package com.example.wyd.service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.wyd.dao.Bill;
import com.example.wyd.mapper.BillMapper;
import org.springframework.stereotype.Service;
@Service
public class BillServiceImpl extends ServiceImpl<BillMapper, Bill> implements BillService {

}

這裡我們採用了MybatisPlus框架，它可以很方便的進行資料庫相關操作，而無需過多寫SQL來實現具體業務邏輯。通過上述定義，通過繼承介面的方式，並提供實體類的定義，MybatisPlus框架會通過反射機制來根據資料庫設定來生成SQL語句，其中包含增刪改查介面，具體的實現我們並未具體定義。

下面定義一個自定義的分庫演算法，具體實現如下：

package com.example.wyd;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
//自定義資料庫分片演算法
public class DBShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        //真實資料庫節點
        availableTargetNames.stream().forEach((item) -> {
           System.out.println("actual db:" + item);
        });
        //邏輯表以及分片的欄位名
        System.out.println("logicTable:"+shardingValue.getLogicTableName()+";shardingColumn:"+ shardingValue.getColumnName());
        //分片資料欄位值
        System.out.println("shardingColumn value:"+ shardingValue.getValue().toString());
        //獲取欄位值
        long orderId = shardingValue.getValue();
        //分片索引計算 0 , 1
        long db_index = orderId & (2 - 1);
        for (String each : availableTargetNames) {
            if (each.equals("ds"+db_index)) {
                //匹配的話，返回資料庫名
                return each;
            }
        }
        throw new IllegalArgumentException();
    }
}

下面給出資料的分表邏輯，這個定義稍顯複雜一點，就是根據業務資料的日期欄位值，根據月份落入對應的物理資料表中。實現示例程式碼如下：

package com.example.wyd;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
import java.util.Date;
//表按日期自定義分片
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
        //真實資料庫節點
        availableTargetNames.stream().forEach((item) -> {
            System.out.println("actual db:" + item);
        });
        //邏輯表以及分片的欄位名
        System.out.println("logicTable:"+shardingValue.getLogicTableName()+";shardingColumn:"+ shardingValue.getColumnName());
        //分片資料欄位值
        System.out.println("shardingColumn value:"+ shardingValue.getValue().toString());
        //獲取表名字首
        String tb_name = shardingValue.getLogicTableName() + "_";
        //根據日期分表
        Date date = shardingValue.getValue();
        String year = String.format("%tY", date);
        String mon =String.valueOf(Integer.parseInt(String.format("%tm", date)));
        //String dat = String.format("%td", date); //也可以安裝年月日來分表
        // 選擇表
        tb_name = tb_name + year + "_" + mon;
        //實際的表名
        System.out.println("tb_name:" + tb_name);
        for (String each : availableTargetNames) {
            //System.out.println("availableTableName:" + each);
            if (each.equals(tb_name)) {
                //返回物理表名
                return each;
            }
        }
        throw new IllegalArgumentException();
    }
}

資料的分庫分表可以在Spring Boot的屬性配置檔案中進行設（application.properties）：

server.port=8080
#########################################################################################################
# 配置ds0 和ds1兩個資料來源
spring.shardingsphere.datasource.names = ds0,ds1

#ds0 配置
spring.shardingsphere.datasource.ds0.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url = jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=utf8
spring.shardingsphere.datasource.ds0.username = uname
spring.shardingsphere.datasource.ds0.password = pwd

#ds1 配置
spring.shardingsphere.datasource.ds1.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url = jdbc:mysql://127.0.0.1:3306/mydb2characterEncoding=utf8
spring.shardingsphere.datasource.ds1.username = uname
spring.shardingsphere.datasource.ds1.password = pwd
#########################################################################################################
# 預設的分庫策略：id取模
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{id % 2}
#########################################################################################################
spring.shardingsphere.sharding.tables.t_bill.actual-data-nodes=ds$->{0..1}.t_bill_$->{2021..2021}_$->{1..12}
#資料庫分片欄位
spring.shardingsphere.sharding.tables.t_bill.database-strategy.standard.sharding-column=order_id
#自定義資料庫分片策略
spring.shardingsphere.sharding.tables.t_bill.database-strategy.standard.precise-algorithm-class-name=com.example.wyd.DBShardingAlgorithm
#表分片欄位
spring.shardingsphere.sharding.tables.t_bill.table-strategy.standard.sharding-column=create_time
#自定義表分片策略
spring.shardingsphere.sharding.tables.t_bill.table-strategy.standard.precise-algorithm-class-name=com.example.wyd.TableShardingAlgorithm
#########################################################################################################
# 使用SNOWFLAKE演算法生成主鍵
spring.shardingsphere.sharding.tables.t_bill.key-generator.column = order_id
spring.shardingsphere.sharding.tables.t_bill.key-generator.type = SNOWFLAKE
spring.shardingsphere.sharding.tables.t_bill.key-generator.props.worker.id=123
#########################################################################################################
spring.shardingsphere.props.sql.show = true

最後，我們給出一個定義的Controller型別，來測試分庫分表的查詢和儲存操作是否正確。HomeController類定義如下：

package com.example.wyd.controller;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.wyd.dao.Bill;
import com.example.wyd.service.BillService;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
@RestController
@RequestMapping("/api")
public class HomeController {
    @Autowired
    private BillService billService;
    //http://localhost:8080/api/query?start=2021-02-07%2000:00:00&end=2021-03-07%2000:00:00
    @RequestMapping("/query")
    public List<Bill> queryList(@RequestParam("start") String start, @RequestParam("end") String end) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date date = sdf.parse(start);
            Date date2 = sdf.parse(end);
            QueryWrapper<Bill> queryWrapper = new QueryWrapper<>();
            queryWrapper.ge("create_time",date)
                    .and(qw-> qw.le("create_time", date2)).last("limit 1,10");
            List<Bill> billIPage = billService.list(queryWrapper);
            System.out.println(billIPage.size());
            billIPage.forEach(System.out::println);
            return billIPage;
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
    //http://localhost:8080/api/save?userid=999&addressId=999&status=M&date=2021-03-07%2000:00:00
    @RequestMapping("/save")
    public String Save(@RequestParam("userid") int userId, @RequestParam("addressId") long AddressId,
                       @RequestParam("status") String status
            ,@RequestParam("date") String strDate) {
        String ret ="0";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date date = sdf.parse(strDate);
            Bill bill = new Bill();
            bill.setUserId(userId);
            bill.setAddressId(AddressId);
            bill.setStatus(status);
            bill.setCreateTime(date);
            boolean isOk = billService.save(bill);
            if (isOk){
                ret ="1";
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return ret;
    }
}

至此，我們可以用測試類初始化一些資料，並做一些初步的資料操作測試：

package com.example.wyd;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.wyd.dao.Bill;
import com.example.wyd.dao.Order;
import com.example.wyd.service.BillService;
import com.example.wyd.service.OrderService;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class OrderServiceImplTest extends WydApplicationTests {
    @Autowired
    private BillService billService;
    @Test
    public void testBillSave(){
        for (int i = 0 ; i< 120 ; i++){
            Bill bill = new Bill();
            bill.setUserId(i);
            bill.setAddressId((long)i);
            bill.setStatus("K");
            bill.setCreateTime((new Date(new DateTime(2021,(i % 11)+1,7,00, 00,00,000).getMillis())));
            billService.save(bill);
        }
    }
    @Test
    public void testGetByOrderId(){
        long id = 626038622575374337L; //根據資料修改，無資料會報錯
        QueryWrapper<Bill> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("order_id", id);
        Bill bill = billService.getOne(queryWrapper);
        System.out.println(bill.toString());
    }

    @Test
    public void testGetByDate(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date date = sdf.parse("2021-02-07 00:00:00");
            QueryWrapper<Bill> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("create_time",date);
            List<Bill> billIPage = billService.list(queryWrapper);
            System.out.println(billIPage.size());
            System.out.println(billIPage.toString());
        } catch (ParseException e) {
            e.printStackTrace();
        }

    }

    @Test
    public void testGetByDate2(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date date = sdf.parse("2021-02-07 00:00:00");
            Date date2 = sdf.parse("2021-03-07 00:00:00");
            QueryWrapper<Bill> queryWrapper = new QueryWrapper<>();
            queryWrapper.ge("create_time",date)
            .and(qw-> qw.le("create_time", date2));
            List<Bill> billIPage = billService.list(queryWrapper);
            System.out.println(billIPage.size());
            billIPage.forEach(System.out::println);

        } catch (ParseException e) {
            e.printStackTrace();
        }

    }
}

執行上述測試，通過後會生成測試資料。

3 驗證

開啟瀏覽器，輸入網址進行查詢測試：http://localhost:8080/api/query?start=2021-02-07%2000:00:00&end=2021-03-07%2000:00:00

輸入如下網址進行資料新增測試：http://localhost:8080/api/save?userid=999&addressId=999&status=M&date=2021-03-07%2000:00:00

通過跟蹤分析，此資料落入如下的表中，SQL語句如下：

SELECT * FROM mydb2.t_bill_2021_3 LIMIT 0, 1000

這裡還需要注意，ShardingSphere 還支援分散式事務，感興趣的可以閱讀官網相關資料進行學習。

點選關注，第一時間瞭解華為雲新鮮技術~

資料庫分庫分表
2016-11-04
資料庫
分庫分表如何進行極致的優化
2022-02-16
優化
億萬級分庫分表後如何進行跨表分頁查詢
2023-03-29
資料庫怎麼分庫分表
2019-11-12
資料庫
大資料資料庫讀寫分離分庫分表
2021-01-01
大資料資料庫
分庫分表插入資料
2020-12-09
MariaDB Spider 資料庫分庫分表實踐
2022-02-07
IDE資料庫
貝聊億級資料庫分庫分表實踐
2017-08-15
資料庫
資料庫分庫分表的總結
2019-02-16
資料庫
[資料庫][分庫分表]分庫分表之後，id主鍵如何處理
2019-02-19
資料庫
分散式資料庫中介軟體 MyCat | 分庫分表實踐
2020-08-20
分散式資料庫
基於代理的資料庫分庫分表框架 Mycat實踐
2018-12-19
資料庫框架
《資料儲存》之《分庫，分表》
2021-07-20
oracle分表效率,資料庫分庫分表是什麼，什麼情況下需要用分庫分表
2022-09-12
Oracle資料庫
手把手0基礎專案實戰（二）——微服務架構下的資料庫分庫分表實戰
2019-03-01
微服務架構資料庫
MySQL資料庫之分庫分表方案
2019-07-24
MySql資料庫
java 取模運算% 實則取餘簡述例子應用在資料庫分庫分表
2015-01-05
Java資料庫
關係型資料庫分庫分表系列之一
2022-01-05
資料庫
實現報表資料分庫儲存
2019-12-18
分庫分表系列：分庫分表的前世今生
2022-03-19
分庫分表
2024-08-20
淺談高效能資料庫叢集——分庫分表
2018-07-03
資料庫
20分鐘資料庫索引設計實戰
2019-02-16
資料庫索引
3.1 MYSQL分庫分表實踐
2020-01-12
MySql
報表資料分庫儲存
2019-12-10
億級大表分庫分表實戰總結（萬字乾貨，實戰覆盤）
2020-11-17
MySQL 資料庫之網際網路常用分庫分表方案
2020-02-08
MySql資料庫
在多資料來源中對部分資料表使用shardingsphere進行分庫分表
2021-08-29
Java後端微服務架構下的資料庫分庫分表：Sharding-Sphere
2024-08-28
Java後端微服務架構資料庫
分散式資料庫中介軟體的實現原理介紹一：分庫分表
2018-07-03
分散式資料庫
分庫分表注意
2019-03-18
MySQL分庫分表
2015-10-30
MySql
[Mysql]分庫分表
2024-07-23
MySql
資料庫分庫分表中介軟體 Sharding-JDBC 原始碼分析 —— SQL 執行
2017-10-08
資料庫JDBC原始碼SQL
徹底搞清MySQL分庫分表（垂直分庫，垂直分表，水平分庫，水平分表）
2021-02-01
MySql
效能優化之資料庫篇5-分庫分表與資料遷移
2021-07-29
優化資料庫
【乾貨】分庫分表最佳實踐
2021-09-10
資料庫分庫分表之後，如何解決事務問題？
2020-07-06
資料庫

Java實戰：教你如何進行資料庫分庫分表

1 示例資料庫準備

2 分庫分表實現

3 驗證

相關文章