关于数据库:Java实战教你如何进行数据库分库分表

43次阅读

共计 14564 个字符，预计需要花费 37 分钟才能阅读完成。

摘要： 本文通过理论案例，阐明如何按日期来对订单数据进行程度分库和分表，实现数据的分布式查问和操作。

本文分享自华为云社区《数据库分库分表 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 管理工具中能够看到如下的示例界面：

2 分库分表实现

在 Java 语言下的框架中，有泛滥的开源框架，其中对于分库分表的框架，能够抉择 Apache ShardingSphere，其官网介绍说：ShardingSphere 是一套开源的分布式数据库解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（布局中）这 3 款既可能独立部署，又反对混合部署配合应用的产品组成。它们均提供标准化的数据程度扩大、分布式事务和分布式治理等性能，可实用于如 Java 同构、异构语言、云原生等各种多样化的利用场景。Apache ShardingSphere 5.x 版本开始致力于可插拔架构。目前，数据分片、读写拆散、数据加密、影子库压测等性能，以及 MySQL、PostgreSQL、SQLServer、Oracle 等 SQL 与协定的反对，均通过插件的形式织入我的项目。官网地址为：https://shardingsphere.apache…。

上面的示例采纳 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/que…

输出如下网址进行数据新增测试：http://localhost:8080/api/sav…

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

SELECT * FROM mydb2.t_bill_2021_3 LIMIT 0, 1000

这里还须要留神，ShardingSphere 还反对分布式事务 ，感兴趣的能够浏览官网相干材料进行学习。

点击关注，第一工夫理解华为云陈腐技术~

正文完
数据库
发表至： 数据库
2021-07-28
 0
关于数据库:统信操作系统下数据库管理利器
关于数据库:MYSQL深潜-剖析Performance-Schema内存管理
关于数据库:备份恢复学的这么差试用期你都过不了
关于数据库:思迈特软件Smartb数据库设计的主要步骤