Abstract: This article uses actual cases to explain how to horizontally sub-database and sub-table of order data by date to realize distributed query and operation of data.
This article is shared from Huawei Cloud Community " Database Sub-database Sub-table Java Actual Combat Experience Summary丨 [Bloom! Database] ", author: jackwangcumt.
We know that current applications are inseparable from the database. With more and more data in the database, when a single table breaks the performance limit record, such as MySQL single table online is estimated to be within nearly 10 million, when the number of records continues to grow , From the perspective of performance, split processing is required. The split is divided into horizontal split and vertical split. Generally speaking, there are more horizontal splits, so the table structure is the same, but different data is stored in different database tables. The horizontal split is also divided into sub-database and sub-table.
1 Sample database preparation
In order to make it clear how to use Java language and related frameworks to realize the sub-library and sub-table processing of business tables. Here, first create two independent database instances in the MySQL database, named mydb and mydb2, which can demonstrate the sub-database operation. In addition, in each database instance, 12 business tables are created, and data is split by year and month. The specific table creation script is as follows:
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;After successfully executing the script, you can see the following sample interface in the MySQL management tool:
2 Sub-database sub-table realization
In the framework of the Java language, there are many open source frameworks. Among them, you can choose Apache ShardingSphere for the framework of sub-database and table. Its official website says: ShardingSphere is an ecosystem composed of an open source distributed database solution. It is composed of three products, JDBC, Proxy, and Sidecar (planned), which can be deployed independently and support mixed deployment. They all provide standardized data horizontal expansion, distributed transactions, and distributed governance functions, and can be applied to various diversified application scenarios such as Java isomorphism, heterogeneous languages, and cloud native. Apache ShardingSphere 5.x version began to work on pluggable architecture. At present, functions such as data fragmentation, read-write separation, data encryption, shadow library pressure testing, and SQL and protocol support such as MySQL, PostgreSQL, SQLServer, and Oracle are all woven into the project through plug-ins. The official website address is: https://shardingsphere.apache.org/index_zh.html .
The following example is implemented using the Spring Boot framework, and related libraries are managed through Maven. First, give the definition of the pom.xml configuration file:
<?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>Secondly, give an entity class, which corresponds to the database table t_bill created above, which is defined as follows:
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;
}
}The mapping class BillMapper is defined as follows:
package com.example.wyd.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.wyd.dao.Bill;
public interface BillMapper extends BaseMapper<Bill> {
}The service class interface is defined as follows:
package com.example.wyd.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.wyd.dao.Bill;
public interface BillService extends IService<Bill> {
}The implementation class of the service class interface is defined as follows:
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 {
}Here we use the MybatisPlus framework, which can easily perform database-related operations without writing too much SQL to implement specific business logic. Through the above definition, by inheriting the interface, and providing the definition of the entity class, the MybatisPlus framework will use the reflection mechanism to generate SQL statements according to the database settings, including the addition, deletion, modification, and query interface. We did not specifically define the specific implementation.
The following defines a custom sub-library algorithm, the specific implementation is as follows:
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();
}
}The table logic of the data is given below. This definition is a bit more complicated. It is based on the date field value of the business data and falls into the corresponding physical data table according to the month. The implementation example code is as follows:
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();
}
}The sub-database and sub-table can be set in the Spring Boot property configuration file (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 = trueFinally, we give a defined Controller type to test whether the query and save operations of sub-databases and sub-tables are correct. The HomeController class is defined as follows:
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;
}
}At this point, we can initialize some data with the test class and do some preliminary data manipulation tests:
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();
}
}
}Perform the above test, after passing the test data will be generated.
3 verification
Open the browser and enter the URL for query test: http://localhost:8080/api/query?start=2021-02-07%2000:00:00&end=2021-03-07%2000:00:00
Enter the following URL for data addition test: http://localhost:8080/api/save?userid=999&addressId=999&status=M&date=2021-03-07%2000:00:00
Through tracking analysis, this data falls into the following table, the SQL statement is as follows:
SELECT * FROM mydb2.t_bill_2021_3 LIMIT 0, 1000
It should also be noted here that ShardingSphere also supports distributed transactions . If you are interested, you can read the relevant information on the official website for learning.
Click to follow and learn about Huawei Cloud's fresh technology for the first time~
java
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。