1.需求
单表数据越来越大,影响到查询效率,需要聚合各种函数也比较麻烦,所以使用分表来解决这一问题,主要依赖的还是水平分表
2.框架
springboot,mybatisplus,shardingsphere-jdbc
3.代码实现
3.1 sql文件
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user_1
-- ----------------------------
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_1
-- ----------------------------
-- ----------------------------
-- Table structure for user_2
-- ----------------------------
DROP TABLE IF EXISTS `user_2`;
CREATE TABLE `user_2` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_2
-- ----------------------------
-- ----------------------------
-- Table structure for user_3
-- ----------------------------
DROP TABLE IF EXISTS `user_3`;
CREATE TABLE `user_3` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_3
-- ----------------------------
-- ----------------------------
-- Table structure for user_4
-- ----------------------------
DROP TABLE IF EXISTS `user_4`;
CREATE TABLE `user_4` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_4
-- ----------------------------
-- ----------------------------
-- Table structure for user_5
-- ----------------------------
DROP TABLE IF EXISTS `user_5`;
CREATE TABLE `user_5` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_5
-- ----------------------------
-- ----------------------------
-- Table structure for user_6
-- ----------------------------
DROP TABLE IF EXISTS `user_6`;
CREATE TABLE `user_6` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_6
-- ----------------------------
-- ----------------------------
-- Table structure for user_7
-- ----------------------------
DROP TABLE IF EXISTS `user_7`;
CREATE TABLE `user_7` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_7
-- ----------------------------
-- ----------------------------
-- Table structure for user_8
-- ----------------------------
DROP TABLE IF EXISTS `user_8`;
CREATE TABLE `user_8` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_8
-- ----------------------------
-- ----------------------------
-- Table structure for user_9
-- ----------------------------
DROP TABLE IF EXISTS `user_9`;
CREATE TABLE `user_9` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_9
-- ----------------------------
-- ----------------------------
-- Table structure for user_10
-- ----------------------------
DROP TABLE IF EXISTS `user_10`;
CREATE TABLE `user_10` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_10
-- ----------------------------
-- ----------------------------
-- Table structure for user_11
-- ----------------------------
DROP TABLE IF EXISTS `user_11`;
CREATE TABLE `user_11` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_11
-- ----------------------------
-- ----------------------------
-- Table structure for user_12
-- ----------------------------
DROP TABLE IF EXISTS `user_12`;
CREATE TABLE `user_12` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_12
-- ----------------------------
SET FOREIGN_KEY_CHECKS = 1;
表截图
其中我们通过birth字段进行分表
3.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.5.2</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>springboot-Sharding-Jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-Sharding-Jdbc</name>
<description>单库分表</description>
<properties>
<sharding.jdbc.version>3.0.0</sharding.jdbc.version>
<mybatis.version>1.3.0</mybatis.version>
<druid.version>1.1.10</druid.version>
<mysql.version>5.1.38</mysql.version>
<lombok.version>1.18.16</lombok.version>
<java.version>1.8</java.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>
<scope>test</scope>
</dependency>
<!-- druid 数据源,一定不能用druid-spring-boot-starter -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<!--一定不能引入dynamic-datasource-spring-boot-starter包-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0-beta</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.6.3</version>
</dependency>
<!-- 使用 XA 事务时,需要引入此模块 -->
<!-- <dependency>-->
<!-- <groupId>org.apache.shardingsphere</groupId>-->
<!-- <artifactId>shardingsphere-transaction-xa-core</artifactId>-->
<!-- <version>5.0.0-beta</version>-->
<!-- </dependency>-->
<!-- <!– https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-transaction-xa-atomikos –>-->
<!-- <dependency>-->
<!-- <groupId>org.apache.shardingsphere</groupId>-->
<!-- <artifactId>shardingsphere-transaction-xa-atomikos</artifactId>-->
<!-- <version>5.0.0-beta</version>-->
<!-- </dependency>-->
<!-- <!– https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-transaction-xa-spi –>-->
<!-- <dependency>-->
<!-- <groupId>org.apache.shardingsphere</groupId>-->
<!-- <artifactId>shardingsphere-transaction-xa-spi</artifactId>-->
<!-- <version>5.0.0-beta</version>-->
<!-- </dependency>-->
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
3.3. 配置文件
server:
port: 9090
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://ip:3367/lyb?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
username: root
password: 123456
shardingsphere:
# 是否启用 Sharding
enabled: true
# 打印sql
props:
sql-show: true
datasource:
names: ds
ds:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: ${spring.datasource.driver-class-name}
url: ${spring.datasource.url}
username: ${spring.datasource.username}
password: ${spring.datasource.password}
rules:
sharding:
defaultDataSourceName: ds
# 表策略配置
tables:
# user 是逻辑表
user:
actualDataNodes: ds.user_$->{1..12} #不知道为什么user_01会变成user1?
tableStrategy:
# 使用标准分片策略
standard:
# 配置分片字段
shardingColumn: birth
# 分片算法名称,不支持大写字母和下划线,否则启动就会报错
shardingAlgorithmName: birth-sharding-altorithm
# 分片算法配置
shardingAlgorithms:
# 分片算法名称,不支持大写字母和下划线,否则启动就会报错
birth-sharding-altorithm:
# 类型:自定义策略
type: CLASS_BASED
props:
# 分片策略
strategy: standard
# 分片算法类
algorithmClassName: com.example.springbootshardingjdbc.config.BirthShardingAlgorithmA
#mybatis-plus配置控制台打印完整带参数SQL语句
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
整体来说这个配置文件和选对依赖是最难的
3.4 Java配置类
3.4.1. 配置1
package com.example.springbootshardingjdbc.config;
import cn.hutool.core.date.DateUtil;
import com.google.common.collect.Range;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.springframework.stereotype.Component;
import java.util.*;
import java.util.stream.Collectors;
@Component
public class BirthShardingAlgorithmA implements StandardShardingAlgorithm<Date> {
private static final String TABLE_NAME = "user_";
private final static String CUT = "_";
/*
*availableTargetNames:配置文件中定义的表名称集合
*shardingValue:insert,select,update,delate时,birth字段值
*函数返回值:返回函数值shardingValue年份对应的表名。比如 1995 =3,返回表名user_3
*/
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
if (CollectionUtils.isEmpty(availableTargetNames) || (shardingValue == null || shardingValue.getValue() == null)) {
throw new IllegalArgumentException("sharding jdbc not find logic table,please check config");
}
String month = null;
if (shardingValue != null) {
Date value = shardingValue.getValue();
month = getDateMonth(value);
return TABLE_NAME + month;
} else {
throw new UnsupportedOperationException("传递参数有误");
}
}
/*
*availableTargetNames:配置文件中定义的表名称集合
*函数返回值:返回函数值rangeShardingValue 月份对应的表名集合比如 between 2020-01-01 and 2021-04-01 ,根据这时间段年份,得到返回的集合是user_1,user_2,user_3,user_4
*/
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> rangeShardingValue) {
System.out.println("调用方法");
Collection<String> resList = new ArrayList<>();
List<Integer> monthList = availableTargetNames.stream()
.map(table -> Integer.valueOf(table.substring(table.lastIndexOf(CUT) + 1)))
.collect(Collectors.toList());
// 数据库的最大月份
Integer maxMonth = monthList.stream().max(Comparator.comparingInt(p -> p)).get();
// 数据库的最小月份
Integer minMonth = monthList.stream().min(Comparator.comparingInt(p -> p)).get();
Range<Date> valueRange = rangeShardingValue.getValueRange();
Date beginDate = valueRange.lowerEndpoint();
// 获取截止时间
Date endDate = valueRange.upperEndpoint();
// 获取月份差值
long diff = DateUtil.betweenMonth(beginDate, endDate, true);
if (diff >= 12 && diff < 36) {
return availableTargetNames;
}
if (diff >= 36 ) {
throw new RuntimeException("查询时间间隔不能超过三年");
}
// 截取开始月份
int beginMonth = valueRange.lowerEndpoint().getMonth() + 1;
// 截取结束月份
int endMonth = valueRange.upperEndpoint().getMonth() + 1;
if (endMonth < minMonth || beginMonth > maxMonth) {
throw new IllegalArgumentException("参数解析无法获取表名" + beginMonth + " to " + endMonth);
}
for (String tableName : availableTargetNames) {
for (int i = beginMonth; i <= endMonth; i++) {
String tag = String.valueOf(i);
if (tableName.substring(tableName.lastIndexOf(CUT)+1).equals(tag)) {
resList.add(tableName);
}
}
}
if (CollectionUtils.isEmpty(resList)) {
throw new RuntimeException("参数分表结果为空");
}
return resList;
}
@Override
public void init() {
}
@Override
public String getType() {
return null;
}
public String getDateMonth(Date date) {
int month = date.getMonth() + 1; //获取月份
return String.valueOf(month);
}
}
3.4.2.配置2
package com.example.springbootshardingjdbc.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MybatisPlusConfig{
/**
* mybatisPlus分页配置
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
paginationInnerInterceptor.setOptimizeJoin(true);
paginationInnerInterceptor.setDbType(DbType.MYSQL);
paginationInnerInterceptor.setOverflow(true);
interceptor.addInnerInterceptor(paginationInnerInterceptor);
OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor = new OptimisticLockerInnerInterceptor();
interceptor.addInnerInterceptor(optimisticLockerInnerInterceptor);
return interceptor;
}
}
3.5 controller层
package com.example.springbootshardingjdbc.controller;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.springbootshardingjdbc.entity.User;
import com.example.springbootshardingjdbc.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
@RestController
public class UesrController {
@Autowired
private UserService userService;
/**
* 插入数据
*/
@PostMapping("/insert")
public void insertUser(){
userService.insertUser();
}
/**
* 根据时间搜索
*/
@PostMapping("/select")
public List<User> selectByBirth(String date){
Date birth = new Date();
try {
birth = new SimpleDateFormat("yyyy-MM-dd").parse(date);
} catch (ParseException e) {
//LOGGER.error("时间转换错误, string = {}", s, e);
}
return userService.selectByBirth(birth);
}
/**
* 根据时间搜索分页
*/
@PostMapping("/selectPage")
public IPage<User> selectPage(String date,Integer start,Integer length){
Date birth = new Date();
try {
birth = new SimpleDateFormat("yyyy-MM-dd").parse(date);
} catch (ParseException e) {
//LOGGER.error("时间转换错误, string = {}", s, e);
}
return userService.selectPage(new Page<>(start, length), birth);
}
/**
* 根据时间范围搜索
*/
@PostMapping("/selectRange")
public List<User> selectRange(String beginDate,String endDate){
Date beginbirth = new Date();
Date endbirth = new Date();
try {
beginbirth = new SimpleDateFormat("yyyy-MM-dd").parse(beginDate);
endbirth = new SimpleDateFormat("yyyy-MM-dd").parse(endDate);
} catch (ParseException e) {
//LOGGER.error("时间转换错误, string = {}", s, e);
}
return userService.selectRange(beginbirth,endbirth);
}
}
3.3.6 serviceImpl层
package com.example.springbootshardingjdbc.service.impl;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.springbootshardingjdbc.dao.UserMapper;
import com.example.springbootshardingjdbc.entity.User;
import com.example.springbootshardingjdbc.service.UserService;
import org.apache.shardingsphere.transaction.annotation.ShardingTransactionType;
import org.apache.shardingsphere.transaction.core.TransactionType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userDao;
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
@Override
public void insertUser() {
for (int i = 0; i < 1000; i++) {
User user = new User();
user.setSex("12");
try {
user.setBirth(randomDate());
} catch (ParseException e) {
System.out.println("======================");
throw new RuntimeException(e);
}
userDao.insert(user);
}
}
@Override
public List<User> selectByBirth(Date birth) {
return userDao.selectByBirth(birth);
}
@Override
public IPage<User> selectPage(Page<User> userPage, Date birth) {
return userDao.selectByPage(userPage,birth);
}
@Override
public List<User> selectRange(Date beginbirth, Date endbirth) {
return userDao.selectRange(beginbirth,endbirth);
}
public static Date randomDate() throws ParseException {
//获取当前时间
Calendar calendar = Calendar.getInstance();
long end = calendar.getTimeInMillis();
//设置指定时间
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = sdf.parse("1970-01-01 00:00:00");
long start = date.getTime();
Random random = new Random();
//产生long类型指定范围随机数
long randomDate = start + (long) (random.nextFloat() * (end - start + 1));
return new Date(randomDate);
}
}
其中entity的id使用 @TableId(value = “id”, type = IdType.ASSIGN_ID)来实现,其余代码已省略
4.执行结果
5.遗留问题
插入的时候事务没有解决,查询到网上各种方法暂时也不生效,后续再持续探索