当前位置： 首页 > news >正文

SpringBoot+ShardingSphere-分库分表教程（二）

news 2025/7/1 8:56:58

上次介绍了使用时间作为分片键进行分表，另外还有一种常用的分表方式是按照某个列自增的方式进行分表。比如学校里学生的学号，随着学生的增长表会越来越大，所以可以按照一批学号一张分表的形式进行分表，1-1000在表1中,1001-2000在表2中。

 1、创建两张表，一张学校表，一张学生表，两张表我们都采用按照列自增的方式进行分表。

这里注意，虽然我们的分表是从1开始的，但是对于shardingsphere来说，它认为的第一张表是0，虽然这张表没什么用，但是一定要创建出来。

CREATE TABLE `tb_school_0` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',`school_id` longblob COMMENT '学校号',`version` int NOT NULL DEFAULT '1' COMMENT '版本号',`is_logic_delete` int NOT NULL DEFAULT '0' COMMENT '逻辑删除',`create_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',`create_time` datetime DEFAULT NULL COMMENT '创建时间',`update_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',`update_time` datetime DEFAULT NULL COMMENT '修改时间',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1989 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='学校表';
CREATE TABLE `tb_student_0` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',`school_id` bigint NOT NULL COMMENT '学校号',`student_id` bigint NOT NULL COMMENT '学号',`name` varchar(100) COMMENT '姓名',`version` int NOT NULL DEFAULT '1' COMMENT '版本号',`is_logic_delete` int NOT NULL DEFAULT '0' COMMENT '逻辑删除',`create_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',`create_time` datetime DEFAULT NULL COMMENT '创建时间',`update_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',`update_time` datetime DEFAULT NULL COMMENT '修改时间',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1989 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='学生表';

 把分表也创建出来，一般使用like语句从0表复制就行，建议在实际项目中，我们做一个定时任务，自动的去提前创建分表。

2、配置sharding.yml文件

对于按照自增列进行分表的规则需要用到autotables这个配置，除了名字，我感觉跟按照时间分表没啥区别，主要还是分表规则的配置决定的，VOLUME_RANGE代表的是分片规则为按照容量进行分表，除了按容量，还可以hash取模的方式分表，取模对于非自增的列可以让分表更加均匀，根据具体需要可以查下shardingsphere的官方文档，我感觉写的还是很专业的。range-lower、range-upper代表这个列的数值上下限，一般就写一个很大的数值就行，如果使用过程中发现上限不够了也没事，更改一下即可。sharding-volume就表示每个分表的容量大小，要根据实际业务情况合理的规划这个值。

# 模式配置
mode:type: Standalonerepository:type: JDBC
# 数据源配置
dataSources:sharding:dataSourceClassName: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.18.41:3306/sharding?useSSL=false&useUnicode=true&characterEncoding=UTF-8username: rootpassword: rootdruid:test-on-borrow: truevalidation-query: SELECT 1 FROM DUALweb-stat-filter:enabled: truestat-view-servlet:enabled: truelogin-username: druidlogin-password: 12345pool-prepared-statements: falsemax-pool-prepared-statement-per-connection-size: 20
# 规则配置
rules:# 单表配置- !SINGLEtables:- sharding.*# 数据分片- !SHARDINGtables:sys_message:actualDataNodes: sharding.sys_message_${20250101..20991231}tableStrategy:standard: # 用于单分片键的标准分片场景shardingColumn: create_timeshardingAlgorithmName: sys_message_algorithmkeyGenerateStrategy: # 分布式序列策略column: idkeyGeneratorName: snowflakeauditStrategy: # 分片审计策略auditorNames: # 分片审计算法名称- sharding_key_required_auditorallowHintDisable: trueautoTables:tb_student:actualDataSources: shardingshardingStrategy:standard:shardingColumn: student_idshardingAlgorithmName: tb_student_algorithmkeyGenerateStrategy: # 分布式序列策略column: idkeyGeneratorName: snowflakeauditStrategy: # 分片审计策略auditorNames: # 分片审计算法名称- sharding_key_required_auditorallowHintDisable: truetb_school:actualDataSources: shardingshardingStrategy:standard:shardingColumn: school_idshardingAlgorithmName: tb_school_algorithmkeyGenerateStrategy: # 分布式序列策略column: idkeyGeneratorName: snowflakeauditStrategy: # 分片审计策略auditorNames: # 分片审计算法名称- sharding_key_required_auditorallowHintDisable: truebindingTables:- tb_school,tb_student# 分片算法配置shardingAlgorithms:sys_message_algorithm:type: INTERVALprops:datetime-pattern: yyyy-MM-dd HH:mm:ssdatetime-lower: "2025-01-01 00:00:00"  # 添加引号确保格式正确datetime-upper: "2099-12-31 23:59:59"  # 添加引号确保格式正确sharding-suffix-pattern: yyyyMMdddatetime-interval-amount: 7datetime-interval-unit: DAYStb_student_algorithm:type: VOLUME_RANGEprops:range-lower: 1range-upper: 200000sharding-volume: 10tb_school_algorithm:type: VOLUME_RANGEprops:range-lower: 1range-upper: 200000sharding-volume: 10# 分布式序列算法配置keyGenerators:snowflake:type: SNOWFLAKE# 分片审计算法配置auditors:sharding_key_required_auditor:type: DML_SHARDING_CONDITIONSprops:sql-show: true
#  sql-simple: false
#  max-connections-size-per-query: 1
#  check-table-metadata-enabled: false

 这里有个注意的地方——bindingTables，因为我们用到的这两个表都是分表，并且它们是有关联关系的，如果两个表的分片键是同一个的时候就必须要加bindingTables防止出现笛卡尔积关联。

3、创建DO、Mapper等

这些都是普通的一些类，很简单。

SchoolDO：

package com.mj.shardingsphere.entity;import com.baomidou.mybatisplus.annotation.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.io.Serializable;
import java.time.LocalDateTime;/*** 系统-消息表*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "tb_school")
public class SchoolDO implements Serializable {/*** id*/@TableId(value = "id", type = IdType.ASSIGN_ID)private Long id;/*** 学校号*/@TableField(value = "school_id")private Long schoolId;/*** 版本号*/@Version@TableField(value = "version")private Integer version;/*** 逻辑删除*/@TableLogic@TableField(value = "is_logic_delete")private Integer logicDelete;/*** 创建人*/@TableField(value = "create_by", fill = FieldFill.INSERT)private String createBy;/*** 创建时间*/@TableField(value = "create_time", fill = FieldFill.INSERT)private LocalDateTime createTime;/*** 修改人*/@TableField(value = "update_by", fill = FieldFill.INSERT_UPDATE)private String updateBy;/*** 修改时间*/@TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)private LocalDateTime updateTime;private static final long serialVersionUID = 1L;
}

SchoolMapper：

package com.mj.shardingsphere.dao;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mj.shardingsphere.entity.SchoolDO;
import org.apache.ibatis.annotations.Mapper;@Mapper
public interface SchoolMapper extends BaseMapper<SchoolDO> {
}

SchoolMapper.xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mj.shardingsphere.dao.SchoolMapper"><resultMap id="BaseResultMap" type="com.mj.shardingsphere.entity.SchoolDO"><!--@mbg.generated--><!--@Table sys_message--><id column="id" jdbcType="BIGINT" property="id" /><result column="school_id" jdbcType="BIGINT" property="schoolId" /><result column="version" jdbcType="INTEGER" property="version" /><result column="is_logic_delete" jdbcType="INTEGER" property="logicDelete" /><result column="create_by" jdbcType="VARCHAR" property="createBy" /><result column="create_time" jdbcType="TIMESTAMP" property="createTime" /><result column="update_by" jdbcType="VARCHAR" property="updateBy" /><result column="update_time" jdbcType="TIMESTAMP" property="updateTime" /></resultMap><sql id="Base_Column_List"><!--@mbg.generated-->id, school_id, version, is_logic_delete, create_by, create_time, update_by, update_time</sql>
</mapper>

SchoolService：

package com.mj.shardingsphere.service;import com.mj.shardingsphere.entity.SchoolDO;import java.util.List;public interface SchoolService {String add(Long schoolId);List<SchoolDO> list();
}

SchoolServiceImpl：同样查询的时候一定要固定好分片键的区间

package com.mj.shardingsphere.service.impl;import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.mj.shardingsphere.dao.SchoolMapper;
import com.mj.shardingsphere.entity.SchoolDO;
import com.mj.shardingsphere.service.SchoolService;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Service;import java.time.LocalDateTime;
import java.util.List;@AllArgsConstructor
@Service
public class SchoolServiceImpl implements SchoolService {private final SchoolMapper schoolMapper;@Overridepublic String add(Long schoolId) {SchoolDO schoolDO = new SchoolDO();schoolDO.setSchoolId(schoolId);schoolDO.setCreateBy("SYSTEM");schoolDO.setCreateTime(LocalDateTime.now());schoolDO.setUpdateBy("SYSTEM");schoolDO.setUpdateTime(LocalDateTime.now());schoolMapper.insert(schoolDO);return "OK";}@Overridepublic List<SchoolDO> list() {return schoolMapper.selectList(Wrappers.lambdaQuery(SchoolDO.class).ge(SchoolDO::getSchoolId, 0L).le(SchoolDO::getSchoolId, 20L));}
}

SchoolController：

package com.mj.shardingsphere.controller;import cn.hutool.core.util.RandomUtil;
import com.mj.shardingsphere.entity.SchoolDO;
import com.mj.shardingsphere.service.SchoolService;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;@AllArgsConstructor
@RequestMapping("/school")
@RestController
public class SchoolController {private final SchoolService schoolService;@GetMapping("/add/{schoolId}")public String add(@PathVariable Long schoolId) {return schoolService.add(schoolId);}@GetMapping("/list")public List<SchoolDO> lst() {return schoolService.list();}
}

调用接口http://127.0.0.1:8080/school/add/{schoolId}，school id可以用1和11，就能发现分别写入到不同的分表里了。

StudentDO：

package com.mj.shardingsphere.entity;import com.baomidou.mybatisplus.annotation.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.io.Serializable;
import java.time.LocalDateTime;/*** 系统-消息表*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "tb_student")
public class StudentDO implements Serializable {/*** id*/@TableId(value = "id", type = IdType.ASSIGN_ID)private Long id;/*** 学号*/@TableField(value = "student_id")private Long studentId;/*** 学校号*/@TableField(value = "school_id")private Long schoolId;/*** 姓名*/@TableField(value = "name")private String name;/*** 版本号*/@Version@TableField(value = "version")private Integer version;/*** 逻辑删除*/@TableLogic@TableField(value = "is_logic_delete")private Integer logicDelete;/*** 创建人*/@TableField(value = "create_by", fill = FieldFill.INSERT)private String createBy;/*** 创建时间*/@TableField(value = "create_time", fill = FieldFill.INSERT)private LocalDateTime createTime;/*** 修改人*/@TableField(value = "update_by", fill = FieldFill.INSERT_UPDATE)private String updateBy;/*** 修改时间*/@TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)private LocalDateTime updateTime;private static final long serialVersionUID = 1L;
}

StudentlMapper：

package com.mj.shardingsphere.dao;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mj.shardingsphere.entity.StudentDO;
import org.apache.ibatis.annotations.Mapper;@Mapper
public interface StudentlMapper extends BaseMapper<StudentDO> {
}

StudentMapper.xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mj.shardingsphere.dao.StudentlMapper"><resultMap id="BaseResultMap" type="com.mj.shardingsphere.entity.StudentDO"><!--@mbg.generated--><!--@Table sys_message--><id column="id" jdbcType="BIGINT" property="id" /><result column="school_id" jdbcType="BIGINT" property="schoolId" /><result column="student_id" jdbcType="BIGINT" property="studentId" /><result column="name" jdbcType="BIGINT" property="name" /><result column="version" jdbcType="INTEGER" property="version" /><result column="is_logic_delete" jdbcType="INTEGER" property="logicDelete" /><result column="create_by" jdbcType="VARCHAR" property="createBy" /><result column="create_time" jdbcType="TIMESTAMP" property="createTime" /><result column="update_by" jdbcType="VARCHAR" property="updateBy" /><result column="update_time" jdbcType="TIMESTAMP" property="updateTime" /></resultMap><sql id="Base_Column_List"><!--@mbg.generated-->id, school_id, student_id, name, version, is_logic_delete, create_by, create_time, update_by, update_time</sql>
</mapper>

StudentService：

package com.mj.shardingsphere.service;import com.mj.shardingsphere.entity.StudentDO;import java.util.List;public interface StudentService {String add(Long schoolId, Long studentId, String name);List<StudentDO> list();
}

StudentServiceImpl：

添加Student对象的时候，先查school对象，这时候注意要使用带有分片键的查询语句才行。

package com.mj.shardingsphere.service.impl;import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.mj.shardingsphere.dao.SchoolMapper;
import com.mj.shardingsphere.dao.StudentlMapper;
import com.mj.shardingsphere.entity.SchoolDO;
import com.mj.shardingsphere.entity.StudentDO;
import com.mj.shardingsphere.service.StudentService;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Service;import java.time.LocalDateTime;
import java.util.List;@AllArgsConstructor
@Service
public class StudentServiceImpl implements StudentService {private final SchoolMapper schoolMapper;private final StudentlMapper studentlMapper;@Overridepublic String add(Long schoolId, Long studentId, String name) {//schoolMapper.selectById(schoolId)这种不能用了，使用条件查询语句SchoolDO schoolDO = schoolMapper.selectOne(Wrappers.lambdaQuery(SchoolDO.class).eq(SchoolDO::getSchoolId, schoolId));if (ObjectUtils.isEmpty(schoolDO)) {return "failed";}StudentDO studentDO = new StudentDO();studentDO.setSchoolId(schoolId);studentDO.setStudentId(studentId);studentDO.setName(name);studentDO.setCreateBy("SYSTEM");studentDO.setCreateTime(LocalDateTime.now());studentDO.setUpdateBy("SYSTEM");studentDO.setUpdateTime(LocalDateTime.now());studentlMapper.insert(studentDO);return "OK";}@Overridepublic List<StudentDO> list() {return studentlMapper.selectList(Wrappers.lambdaQuery(StudentDO.class).ge(StudentDO::getStudentId, 0L).le(StudentDO::getStudentId, 20L));}
}

StudentController：

package com.mj.shardingsphere.controller;import cn.hutool.core.util.RandomUtil;
import com.mj.shardingsphere.entity.StudentDO;
import com.mj.shardingsphere.service.StudentService;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;@AllArgsConstructor
@RequestMapping("/student")
@RestController
public class StudentController {private final StudentService studentService;@GetMapping("/add/{schoolId}/{studentId}/{name}")public String add(@PathVariable Long schoolId, @PathVariable Long studentId, @PathVariable String name) {return studentService.add(schoolId, studentId, name);}@GetMapping("/list")public List<StudentDO> lst() {return studentService.list();}
}

调用接口：http://127.0.0.1:8080/student/add/{schoolId}/{studentId}/{name}，同样能测试不同的student id的数据落在了不同的表里。

http://www.lqws.cn/news/581311.html

相关文章：

  • 如何防止恶意软件感染服务器：防病毒与EDR方案
  • creo 2.0学习笔记
  • 云上攻防—Docker安全容器逃逸特权模式危险挂载
  • DORIS数据备份（HDFS）
  • 对称非对称加密，https和http，https通讯原理，Charles抓包原理
  • 非常详细版： dd.device.geolocation 钉钉微应用获取定位，移动端 PC端都操作，Vue实现钉钉微应用获取精准定位并渲染在地图组件上
  • GeoTools 结合 OpenLayers 实现属性查询（二）
  • Python UI自动化演进格局：从传统库到AI驱动的智能代理
  • （4）pytest-fixture
  • 04_MySQL 通过 Docker 在同一个服务器上搭建主从集群（一主一从）
  • MetaGPT: 多智能体框架(还没装好...)
  • 大数据系统架构实践（三）：Hbase集群部署
  • 机器学习06 集成学习
  • R1-Searcher使用强化学习增强语言模型解决问题的搜索能力
  • 手机屏暗点缺陷修复及相关液晶线路激光修复原理
  • 超大js文件多层级引用缓存在网络较差的时候无法调用使用问题
  • 设计模式-单例模式
  • FPGA矩阵算法实现
  • PHP安装使用教程
  • 如何在Linux服务器中配置Oracle数据库
  • 大数据(4)-spark
  • vue3 + luckysheet 实现在线编辑Excel
  • stm32 freertos下基于 hal库的模拟I2C驱动实现
  • Python 安装使用教程
  • 【知识】RPC和gRPC
  • 实现ModbusTCP转Profinet网关协议转换功能的网关设备
  • Install Ubuntu 24.04 System
  • reactor-test
  • 如何在 iOS 上线前做好安全防护？IPA 混淆与逆向防护实践详解
  • 什么是IOC（控制反转）？—— 用生活实例解读Spring核心概念