使用 Sharding-JDBC 实现数据库分表
站长
· 阅读数 27
1. 简单介绍
ShardingSphere 官方网站：shardingsphere.apache.org/index_zh.ht…
点击了解更多就会打开当前版本的官方文档。文档地址：shardingsphere.apache.org/document/cu…
在文档概览里面可以看到下图：
上图可以看到有几个大版本，部分大版本的变化较大，配置内容都不一样了，所以选择好要用的版本后，需要参考对应版本的文档才行，不同版本文档地址不一样，比如我们准备使用 4.x 版本，文档地址：shardingsphere.apache.org/document/4.…
这里只是实现分表功能，使用 sharding-jdbc 4.x 最后的一个版本 4.1.1：
2. 搭建服务
2.1. 数据库表
根据下面见表语句，自己复制重命名分表即可。
CREATE TABLE `tb_article_type` (
`id` bigint NOT NULL,
`type_name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`status` tinyint DEFAULT NULL COMMENT '状态：0可用，1不可用',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`parent_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tb_pay_order` (
`id` bigint NOT NULL AUTO_INCREMENT,
`order_id` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
`user_id` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
`product_id` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
`product_name` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
`count` int NOT NULL,
`amount` bigint NOT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
2.2. 项目配置
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.1.3.RELEASE</version>
<relativePath/>
</parent>
<groupId>com.example</groupId>
<artifactId>shardingdemo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>shardingdemo</name>
<description>shardingdemo</description>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.1.3.RELEASE</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.18</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>${spring-boot.version}</version>
</plugin>
</plugins>
<resources>
<!-- src/main/java下的xml资源编译到classes下 -->
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<!-- src/main/resources下的资源编译到classes下 -->
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.*</include>
</includes>
</resource>
</resources>
</build>
</project>
application.properties
#mybatis的一些配置
mybatis.mapper-locations=classpath:**/mapper/xml/*.xml
mybatis.type-aliases-package=com.example.shardingdemo.entity
mybatis.configuration.map-underscore-to-camel-case=true
logging.level.com.example.demo.mapper=trace
#数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=rcp
spring.shardingsphere.datasource.rcp.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.rcp.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.rcp.url=jdbc:mysql://xxxxx:3306/rcp?characterEncoding=UTF-8&useSSL=false&rewriteBatchedStatements=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.rcp.username=xxx
spring.shardingsphere.datasource.rcp.password=xxx
#指定表的分布：配置主键分片
spring.shardingsphere.sharding.tables.tb_article_type.actual-data-nodes=rcp.tb_article_type$->{1..2}
#指定分片策略
spring.shardingsphere.sharding.tables.tb_article_type.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.tb_article_type.table-strategy.inline.algorithm-expression=tb_article_type$->{id % 2 + 1}
#指定主键生成策略
spring.shardingsphere.sharding.tables.tb_article_type.key-generator.column=id
spring.shardingsphere.sharding.tables.tb_article_type.key-generator.type=SNOWFLAKE
#指定表的分布：配置时间分片
spring.shardingsphere.sharding.tables.tb_pay_order.actual-data-nodes=rcp.tb_pay_order_$->{2023..2024}_$->{1..12}
#指定分片策略
spring.shardingsphere.sharding.tables.tb_pay_order.table-strategy.standard.sharding-column=create_time
#精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.tb_pay_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingdemo.config.TableShardingAlgorithm
#范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器。这个不配置的话涉及到范围查询就会进行全路由检索，可能会影响性能
#spring.shardingsphere.sharding.tables.tb_pay_order.table-strategy.standard.range-algorithm-class-name=
#打开SQL输出日志
spring.shardingsphere.props.sql.show=true
这里我设置了两张表的分表方式：
tb_article_type 表根据主键分表，分为了 tb_article_type1 和 tb_article_type2 两张表。
tb_pay_order 表根据 create_time 字段按月分表，分为了 24 张表，tb_pay_order_2023_1 到 tb_pay_order_2024_12。
时间分片我这里指定了对应的精确分片自定义算法，需要实现一下：
TableShardingAlgorithm.java
实际使用中其实还需要实现一下范围分片算法的，不配置的话涉及到范围查询就会进行全路由检索，可能会影响性能。这里没有实现，以后再补上吧。
package com.example.shardingdemo.config;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.springframework.stereotype.Component;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
@Slf4j
@Component
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
log.info("开始执行表精确路由算法");
//配置的所有表名称：tb_pay_order_2023_1 到 tb_pay_order_2024_12
availableTargetNames.forEach(item -> log.info("actual node table : {}", item));
//逻辑表名称：tb_pay_order
log.info("logic table name : {}", shardingValue.getLogicTableName());
//路由列字段名称：create_time
log.info("rout column : {}", shardingValue.getColumnName());
//路由列字段的值：2024-02-24 13:54:49.0
log.info("rout column value : {}", shardingValue.getValue());
//开始拼装真实的表名
String tableName = shardingValue.getLogicTableName();
//根据createTime的值来计算分表后缀
Object object = shardingValue.getValue();
Date date;
if (object instanceof String){
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
date = simpleDateFormat.parse(object.toString());
} catch (ParseException e) {
throw new RuntimeException(e);
}
}else {
date = shardingValue.getValue();
}
String year = String.format("%tY", date);
//去掉前缀0
String mon = String.valueOf(Integer.parseInt(String.format("%tm", date)));
String dat = String.format("%td", date);
tableName = tableName + "_" + year + "_" + mon;
//拼装出的真实表名称：tb_pay_order_2024_2
log.info("tableName : {}", tableName);
if (availableTargetNames.contains(tableName)) {
return tableName;
} else {
log.error("availableTargetNames 未包含计算出的表, tableName : {}", tableName);
throw new IllegalArgumentException();
}
}
}
这里的时间分表是固定的分成了 24 张表，仅用于测试 demo，实际上时间分片表一般都是动态的，随着时间推移，会有越来越多的新的月表产生。这里没有实现，以后有机会研究下，据说高版本的 sharding-sphere 已经支持了动态分表。
3. 代码编写
3.1. 主键分片
TbArticleType.java
package com.example.shardingdemo.entity;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;
import java.time.LocalDateTime;
import java.util.Date;
@Data
public class TbArticleType {
private Long id;
private String typeName;
private int status;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date createTime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date updateTime;
private Long parentId;
}
TbArticleTypeMapper.java
package com.example.shardingdemo.mapper;
import com.example.shardingdemo.entity.TbArticleType;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@Mapper
public interface TbArticleTypeMapper {
int insert(TbArticleType tbArticleType);
TbArticleType queryById(@Param("id") Long id);
TbArticleType queryByParentId(@Param("parentId") Long parentId);
}
TbArticleTypeMapper.xml
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.shardingdemo.mapper.TbArticleTypeMapper">
<insert id="insert">
insert into tb_article_type (type_name, status, create_time, update_time, parent_id)
values (#{typeName}, #{status}, NOW(), NOW(), #{parentId})
</insert>
<select id="queryById" resultType="com.example.shardingdemo.entity.TbArticleType">
select * from tb_article_type where id = #{id}
</select>
<select id="queryByParentId" resultType="com.example.shardingdemo.entity.TbArticleType">
select * from tb_article_type where parent_id = #{parentId}
</select>
</mapper>
TbArticleTypeController.java
package com.example.shardingdemo.controller;
import com.example.shardingdemo.entity.TbArticleType;
import com.example.shardingdemo.mapper.TbArticleTypeMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("/type")
public class TbArticleTypeController {
@Autowired
private TbArticleTypeMapper tbArticleTypeMapper;
@PostMapping("/insert")
public String insert(@RequestBody TbArticleType tbArticleType) {
int insert = tbArticleTypeMapper.insert(tbArticleType);
return "插入 " + insert + " 条数据";
}
@GetMapping("/queryById")
public TbArticleType queryById(@RequestParam("id") Long id) {
return tbArticleTypeMapper.queryById(id);
}
@GetMapping("/queryByParentId")
public TbArticleType queryByParentId(@RequestParam("parentId") Long parentId) {
return tbArticleTypeMapper.queryByParentId(parentId);
}
}
三个接口分别测试数据插入、分片键字段查询，其他字段查询。
3.2. 时间分片
TbPayOrder.java
package com.example.shardingdemo.entity;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.util.Date;
@Data
public class TbPayOrder {
private Long id;
private String orderId;
private String userId;
private String productId;
private String productName;
/**
* 数量
*/
private Integer count;
/**
* 金额
*/
private Long amount;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date createTime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date updateTime;
}
TbPayOrderMapper.java
package com.example.shardingdemo.mapper;
import com.example.shardingdemo.entity.TbPayOrder;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.Date;
@Mapper
public interface TbPayOrderMapper {
int insert(TbPayOrder tbPayOrder);
TbPayOrder queryById(@Param("id") Long id);
TbPayOrder queryByCreateTime(String createTime);
TbPayOrder queryByCreateTime1(Date createTime);
TbPayOrder queryByCreateTime2(@Param("tbPayOrder") TbPayOrder tbPayOrder);
}
TbPayOrderMapper.xml
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.shardingdemo.mapper.TbPayOrderMapper">
<insert id="insert">
insert into tb_pay_order (order_id, user_id, product_id, product_name, `count`, amount, create_time, update_time)
values (#{orderId}, #{userId}, #{productId}, #{productName}, #{count}, #{amount}, #{createTime}, NOW())
</insert>
<select id="queryById" resultType="com.example.shardingdemo.entity.TbPayOrder">
select * from tb_pay_order where id = #{id}
</select>
<select id="queryByCreateTime" resultType="com.example.shardingdemo.entity.TbPayOrder">
select * from tb_pay_order where create_time = #{createTime}
</select>
<select id="queryByCreateTime1" resultType="com.example.shardingdemo.entity.TbPayOrder">
select * from tb_pay_order where create_time = #{createTime}
</select>
<select id="queryByCreateTime2" resultType="com.example.shardingdemo.entity.TbPayOrder">
select * from tb_pay_order where create_time = #{tbPayOrder.createTime}
</select>
</mapper>
TbPayOrderController.java
package com.example.shardingdemo.controller;
import com.example.shardingdemo.entity.TbPayOrder;
import com.example.shardingdemo.mapper.TbPayOrderMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.SimpleFormatter;
@RestController
@RequestMapping("/order")
public class TbPayOrderController {
@Autowired
private TbPayOrderMapper tbPayOrderMapper;
@PostMapping("/insert")
public String insert(@RequestBody TbPayOrder tbPayOrder) {
int insert = tbPayOrderMapper.insert(tbPayOrder);
return "插入 " + insert + " 条数据";
}
@GetMapping("/queryById")
public TbPayOrder queryById(@RequestParam("id") Long id) {
return tbPayOrderMapper.queryById(id);
}
@GetMapping("/queryByCreateTime")
public TbPayOrder queryByCreateTime(@RequestParam("createTime") String createTime) throws ParseException {
TbPayOrder tbPayOrder = tbPayOrderMapper.queryByCreateTime(createTime);
System.out.println("查询到的数据：" + tbPayOrder);
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date cTime = simpleDateFormat.parse(createTime);
TbPayOrder tbPayOrder1 = tbPayOrderMapper.queryByCreateTime1(cTime);
System.out.println("查询到的数据1：" + tbPayOrder1);
TbPayOrder param = new TbPayOrder();
param.setCreateTime(cTime);
TbPayOrder tbPayOrder2 = tbPayOrderMapper.queryByCreateTime2(param);
System.out.println("查询到的数据2：" + tbPayOrder2);
return tbPayOrder;
}
}
三个接口分别测试数据插入、其他字段查询、分片键字段查询。
queryById 接口是其他字段查询接口，由于查询条件中没有分片键，所以会全路由进行查询，也就是会查询配置中指定的所有的表，如果数据库中少了哪个日期的分表，就会报表不存在的错误。
queryByCreateTime 接口是分片键字段查询接口，这里写了三种 mybatis 的时间参数的传递方式，只有字符串正常查询出数据了，其他两种传递 Date 类型的参数无法查询得到数据，问题未解决，以后再说。
4. 启动服务
服务启动的时候加载了数据库的所有表，表很多的话会启动好慢，下面是服务启动日志：
2024-05-09 17:16:42.349 INFO 30656 --- [ main] ShardingSphere-metadata : Loading 2 logic tables' meta data.
2024-05-09 17:16:43.319 INFO 30656 --- [ main] ShardingSphere-metadata : Loading 187 tables' meta data.
2024-05-09 17:18:05.534 INFO 30656 --- [ main] ShardingSphere-metadata : Meta data load finished, cost 83278 milliseconds.
2024-05-09 17:18:05.864 INFO 30656 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2024-05-09 17:18:05.983 INFO 30656 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2024-05-09 17:18:05.985 INFO 30656 --- [ main] c.e.s.ShardingdemoApplication : Started ShardingdemoApplication in 86.499 seconds (JVM running for 87.033)
转载自:https://juejin.cn/post/7366972839779631154