快捷導(dǎo)航

mybatis-plus分表實現(xiàn)案例(附示例代碼)

更新時間：2025年12月09日 11:34:51 作者：言一木

MyBatis-Plus是一個MyBatis的增強工具,在 MyBatis 的基礎(chǔ)上只做增強不做改變,為簡化開發(fā)、提高效率而生,這篇文章主要介紹了mybatis-plus分表實現(xiàn)的相關(guān)資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

文檔說明

基于springboot框架下mybatis-plus 3.5.5的分表案例和分表實現(xiàn)思路

數(shù)據(jù)庫水平分表思路

1. 為什么要水平分表

•單表數(shù)據(jù)量過大（千萬/億級）：索引膨脹、查詢慢、寫入壓力大
•單機存儲瓶頸：一張表的數(shù)據(jù)撐爆單庫存儲
•高并發(fā)讀寫：熱點表（如訂單表、日志表）寫入成為瓶頸

2. 核心設(shè)計要點

① 確定分片鍵（Sharding Key）

•分表路由的依據(jù)
•要保證每次數(shù)據(jù)操作都能帶上分片鍵
•常見分片鍵：user_id、zone_code、tenant_id、時間字段

② 選擇分片規(guī)則

•范圍分表
按時間區(qū)間（如 order_202501、order_202502）
? 適合日志、訂單等時間序列數(shù)據(jù)
? 跨區(qū)間查詢不方便

•哈希分表
對分片鍵取模（如 user_id % 8 → 8 張表）
? 分布均勻，避免熱點
? 不方便做范圍查詢

•混合分表
先按業(yè)務(wù)維度（庫級別），再在庫內(nèi)取模或按時間分表

3.基于數(shù)據(jù)庫水平分表注意事項

1.目前基于mybatis-plus分表方案，只能實現(xiàn)單次查詢單表操作，如果涉及到單次查詢跨表操作就必須引入三方組件來實現(xiàn)（ps：ShardingSphere ）
2.分頁或者列表查詢基于mybatis-plus組件不支持組件緯度跨片查詢，此時只能從業(yè)務(wù)或者分片規(guī)則上做調(diào)整，比如以年份作為分片條件，列表查詢強制要求用戶選擇時間再進行查詢
3.庫表必須預(yù)先創(chuàng)建好，預(yù)先規(guī)劃好數(shù)據(jù)量
4.在觸發(fā)分表操作前必須能拿到分片條件

示例代碼

測試表

-- ----------------------------
-- 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分表實現(xiàn)類

@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("未設(shè)置分表配置");
            }
        });
        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內(nèi)置構(gòu)建SQL 動態(tài)分表

驗證xml SQL 動態(tài)分表

業(yè)務(wù)分庫分表實現(xiàn)(mybatis-plus)

1.基于上下文內(nèi)tableType數(shù)據(jù)進行分表讀寫，這里需要注意Configuration類命名最好帶上服務(wù)名,MybatisPlusInterceptor類也是，這樣主要是為了避免bean名稱沖突
2.業(yè)務(wù)可以通過上下文參數(shù)進行分表參數(shù)傳遞

@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("未設(shè)置分表配置");
            }
        });
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        return interceptor;
    }
}