本文主要是介绍mybatis-plus分表实现案例(附示例代码)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
《mybatis-plus分表实现案例(附示例代码)》MyBatis-Plus是一个MyBatis的增强工具,在MyBatis的基础上只做增强不做改变,为简化开发、提高效率而生,：本文主要介绍my...
文档说明
基于springboot框架下mybatis-plus 3.5.5的分表案例和分表实现思路
数据库水平分表思路
1. 为什么要水平分表
•单表数据量过大（千万/亿级）：索引膨胀、查询慢、写入压力大
•单机存储瓶颈：一张表的数据撑爆单库存储
•高并发读写：热点表（如订单表、日志表）写入成为瓶颈
2. 核心设计要点
① 确定分片键（Sharding Key）
•分表路由的依据
•要保证每次数据操作都能带上分片键
•常见分片键：user_id、zone_code、tenant_id、时间字段
② 选择分片规则
•范围分表
按时间区间（如 order_202501、order_202502）
✅ 适合日志、订单等时间序列数据
❌ 跨区间查询不方便•哈希分表
对分片键取模（如 user_id % 8 → 8 张表）
✅ 分布均匀，避免热点
❌ 不方便做范围查询•混合分表
先按业务维度（库级别），再在库内取模或按时间分表
3.基于数据库水平分表注意事项
1.目前基于mybatis-plus分表方案，只能实现单次查询单表操作，如果涉及到单次查询跨表操作就必须引入三方组件来实现（ps：ShardingSphere ）
2.分页或者列表查询基于mybatis-plus组件不支持组件纬度跨片查询，此时只能从业务或者分片规则上做调整，比如以年份作为分片条件，列表查询强制要求用户选择时间再进行查询
3.库表必须预先创建好，预先规划好数据量
4.在触发分表操作前必须能拿到分片条件
示例代码
测试表
-- ---------------------------- -- Table structure for user_1 -- ---------------------------- DROP TABLE IF EXISTS `user_1`; CREATE TABLE `user_1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULChina编程T NULL, `age` int(2) NULL DEFAULT NULL, `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for user_2 -- ---------------------------- DROP TABLE IF EXISTS `user_2`; CREATE TABLE `user_2` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(2) NULL DEFAULT NULL, `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for user_3 -- ---------------------------- DROP TABLE IF EXISTS `user_3`; CREATE TABLE `user_3` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(2) NULL DEFAULT NULL, `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FChina编程ORMAT = Dynamic;
pom依赖
<dependency>
<artifactId>gpmscloud-framework-mybatis-commxJNhRtTUpyon</artifactId>
<groupId>com.bosssoft.gpmscloud</groupId>
<version>${gpmscloud.version}</version>
</dependency>
properties配置
spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://192.168.xxx.xxx:xxxxx/gpx_basic?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8&useSSL=false&autoReconnect=true spring.datasource.username=xxx spring.datasource.password=xxx spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver spring.datasource.druid.url=jdbc:mysql://192.168.xxx.xxx:xxxxx/gpx_basic?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8&useSSL=false&autoReconnect=true spring.datasource.druid.username=xxx spring.datasource.druid.password=xxx
*mybatisplus分表实现类
@Configuration
public class MybatisPlusBasicPlatformConfig {
@Bean
public MybatisPlusInterceptor multipleTablesBasicPlatformPlusInterceptor() {
MybphpatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> {
if (ThreadContextHandler.getThreadLocal().containsKey("tableType")) {
String tableType = (String) ThreadContextHandler.getThreadLocal().get("tableType");
return tableName + "_" + tableType;
} else {
throw new RuntimeException("未设置分表配置");
}
});
interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
return interceptor;
}
}
验证代码
@Data @Accessors(chain = true) @TableName(value = "user") public class User { private Long id; private String name; private Integer age; private String email; } public interface UserMapper extends BaseMapper<User> { User test(); User test2(); } <?xml version="1.0" encoding=js"UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.bosssoft.gpmscloud.framework.test.mapper.UserMapper"> <resultMap type="com.bosssoft.gpmscloud.framework.test.model.po.User" id="UserMapper"> <result property="id" column="id" jdbcType="INTEGER"/> <result property="name" column="resource_id" jdbcType="VARCHAR"/> <result property="age" column="white_list" jdbcType="INTEGER"/> <result property="email" column="black_list" jdbcType="VARCHAR"/> </resultMap> <select id="test" resultMap="UserMapper" parameterType="Java.lang.String"> select id, name, age, email from user </select> <select id="test2" resultMap="UserMapper" parameterType="java.lang.String"> select u1.id, u2.name, u2.age, u2.email from user u1 left join user u2 on u1.user_id = u2.user_id </select> </mapper> @RestController @Slf4j @RequestMapping("/test/multipleTables") public class MultipleTablesController { @Resource private UserMapper userMapper; /** * 验证自动生成库表 */ @ApiOperation("test") @GetMapping("/test") public Result<Boolean> testQuery() { ThreadContextHandler.getThreadLocal().put("tableType", "1"); userMapper.selectList(null); ThreadContextHandler.getThreadLocal().put("tableType", "2"); User user = new User(); user.setId(1L); user.setName("Name"); user.setAge(1); user.setEmail("EMAIL"); userMapper.insert(user); ThreadContextHandler.getThreadLocal().put("tableType", "2"); userMapper.deleteById(1L); ThreadContextHandler.getThreadLocal().put("tableType", "3"); userMapper.selectList(null); return Result.ok(true); } /** * 验证单表和多表自定义xml操作 */ @ApiOperation("testXml") @GetMapping("/testXml") public Result<Boolean> testXml() { ThreadContextHandler.getThreadLocal().put("tableType", "2"); userMapper.test(); userMapper.test2(); return Result.ok(true); } }
验证截图
验证mybatisplus内置构建SQL 动态分表
验证xml SQL 动态分表
业务分库分表实现(mybatis-plus)
1.基于上下文内tableType数据进行分表读写，这里需要注意Configuration类命名最好带上服务名,MybatisPlusInterceptor类也是，这样主要是为了避免bean名称冲突
2.业务可以通过上下文参数进行分表参数传递
@Configuration
public class MybatisPlusBasicPlatformConfig {
@Bean
public MybatisPlusInterceptor multipleTablesBasicPlatformPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> {
if (ThreadContextHandler.getThreadLocal().containsKey("tableType")) {
String tableType = (String) ThreadContextHandler.getThreadLocal().get("tableType");
return tableName + "_" + tableType;
} else {
throw new RuntimeException("未设置分表配置");
}
});
interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
return interceptor;
}
}
总结
到此这篇关于mybatis-plus分表实现的文章就介绍到这了,更多相关mybatis-plus分表实现内容请搜索编程China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持China编程(www.chinasem.cn)！
这篇关于mybatis-plus分表实现案例(附示例代码)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
