mybatis-plus分表实现案例(附示例代码)_Java

文档说明

基于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 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_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_format = dynamic;

pom依赖

        <dependency>
            <artifactid>gpmscloud-framework-mybatis-common</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() {
        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;
    }
}

验证代码

@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="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;
    }
}