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

本文分享自华为云社区《数据库分库分表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;@Servicepublic 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.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name = com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbc-url = jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=utf8spring.shardingsphere.datasource.ds0.username = unamespring.shardingsphere.datasource.ds0.password = pwd#ds1 配置spring.shardingsphere.datasource.ds1.type = com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds1.driver-class-name = com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds1.jdbc-url = jdbc:mysql://127.0.0.1:3306/mydb2characterEncoding=utf8spring.shardingsphere.datasource.ds1.username = unamespring.shardingsphere.datasource.ds1.password = pwd########################################################################################################## 默认的分库策略:id取模spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = idspring.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_idspring.shardingsphere.sharding.tables.t_bill.key-generator.type = SNOWFLAKEspring.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 还反对分布式事务,感兴趣的能够浏览官网相干材料进行学习。

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