Java实战：教你如何进行数据库分库分表

清疚 2023-09-30 19:47 21阅读 0赞

我们知道，当前的应用都离不开数据库，随着数据库中的数据越来越多，单表突破性能上限记录时，如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管理工具中可以看到如下的示例界面：

![img][]

### 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][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/> 
    	</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，其定义如下：

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语句，其中包含增删改查接口，具体的实现我们并未具体定义。

下面定义一个自定义的分库算法，具体实现如下：

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

![在这里插入图片描述][89a34ac718ef4605899f0235a00f55fd.png]

输入如下网址进行数据新增测试：http://localhost:8080/api/save?userid=999&addressId=999&status=M&date=2021-03-07%2000:00:00

![在这里插入图片描述][d58499a1fc4943a7a7391f330f860e56.png]

通过跟踪分析，此数据落入如下的表中，SQL语句如下：

SELECT * FROM mydb2.t_bill_2021_3 LIMIT 0, 1000

![在这里插入图片描述][9d203f0d02f54f3eb6d7caedde28bc3c.png]

[img]: https://img-blog.csdnimg.cn/img_convert/947d69aa320ed200066471aaa8b12eb8.jpeg
[https_shardingsphere.apache.org_index_zh.html]: https://link.zhihu.com/?target=https%3A//shardingsphere.apache.org/index_zh.html
[89a34ac718ef4605899f0235a00f55fd.png]: https://img-blog.csdnimg.cn/89a34ac718ef4605899f0235a00f55fd.png
[d58499a1fc4943a7a7391f330f860e56.png]: https://img-blog.csdnimg.cn/d58499a1fc4943a7a7391f330f860e56.png
[9d203f0d02f54f3eb6d7caedde28bc3c.png]: https://img-blog.csdnimg.cn/9d203f0d02f54f3eb6d7caedde28bc3c.png