java的Springboot+mybatis如何平行分表

以下是使用Spring Boot + MyBatis实现按月分表及分页查询的详细方案：

一、分表设计

1. 表命名规则

user_{YYYYMM} # 如 user_202309 表示2023年9月的表

1. 字段设计

CREATE TABLE `user_202309` (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    group_id BIGINT NOT NULL,
    create_time DATETIME NOT NULL,
    ... -- 其他业务字段
    INDEX idx_user_group_time (user_id, group_id, create_time)
) ENGINE=InnoDB;

二、动态表名处理

1. 工具类生成表名

public class TableNameUtil {
    public static String getTableName(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        return "user_" + sdf.format(date);
    }
}

2. MyBatis动态SQL

<insert id="insertUser" parameterType="user">
    INSERT INTO ${tableName} 
    (user_id, group_id, create_time, ...)
    VALUES (#{userId}, #{groupId}, #{createTime}, ...)
</insert>

<select id="queryUsers" resultType="user">
    SELECT * FROM ${tableName}
    WHERE user_id = #{userId}
      AND group_id = #{groupId}
      AND create_time BETWEEN #{startTime} AND #{endTime}
    ORDER BY create_time DESC
    LIMIT #{limit} OFFSET #{offset}
</select>

三、核心实现逻辑

1. 插入逻辑

@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;

    public void addUser(User user) {
        String tableName = TableNameUtil.getTableName(user.getCreateTime());
        userMapper.insertUser(tableName, user);
    }
}

2. 分页查询逻辑

public Page<User> queryUsers(Long userId, Long groupId, Date startDate, Date endDate, int pageNum, int pageSize) {
    // 1. 计算涉及的月份区间
    Calendar startCal = Calendar.getInstance();
    startCal.setTime(startDate);
    Calendar endCal = Calendar.getInstance();
    endCal.setTime(endDate);
    
    // 2. 收集所有需要查询的表名
    List<String> tableNames = new ArrayList<>();
    while (!startCal.after(endCal)) {
        tableNames.add(TableNameUtil.getTableName(startCal.getTime()));
        startCal.add(Calendar.MONTH, 1);
    }

    // 3. 并行查询各个表
    List<User> results = new ArrayList<>();
    for (String table : tableNames) {
        int currentPage = pageNum; // 需要改进：按总数据量重新计算分页
        int currentOffset = (currentPage - 1) * pageSize;
        results.addAll(userMapper.queryUsers(table, userId, groupId, startDate, endDate, pageSize, currentOffset));
    }

    // 4. 合并排序（内存中完成）
    results.sort(Comparator.comparing(User::getCreateTime).reversed());
    
    // 5. 执行分页截取
    int fromIndex = (pageNum - 1) * pageSize;
    int toIndex = Math.min(fromIndex + pageSize, results.size());
    List<User> pagedResults = results.subList(fromIndex, toIndex);
    
    // 6. 构建分页对象
    int total = userMapper.countUsers(userId, groupId, startDate, endDate); // 需实现统计逻辑
    return new PageImpl<>(pagedResults, PageRequest.of(pageNum-1, pageSize), total);
}

四、关键优化点

1. 索引优化

ALTER TABLE `user_202309` ADD INDEX idx_uid_gid_time (user_id, group_id, create_time);

1. 批量操作

public void batchInsert(List<User> users) {
    Map<String, List<User>> groupedData = users.stream()
        .collect(Collectors.groupingBy(u -> TableNameUtil.getTableName(u.getCreateTime())));
    
    groupedData.forEach((table, batch) -> {
        userMapper.batchInsert(table, batch); // 需实现批量插入方法
    });
}

1. 分页优化

• 使用键值分页替代偏移量分页
• 预计算总记录数时使用COUNT(*)而非实际加载数据

五、辅助功能实现

1. 自动建表机制

@Component
public class TableInitializer {
    @PostConstruct
    public void init() {
        // 检查并创建未来6个月的表
        Calendar cal = Calendar.getInstance();
        for (int i = 0; i < 6; i++) {
            createTableIfNotExist(cal.getTime());
            cal.add(Calendar.MONTH, 1);
        }
    }
    
    private void createTableIfNotExist(Date date) {
        String tableName = TableNameUtil.getTableName(date);
        // 执行DDL语句创建表
    }
}

2. 历史表清理

@Component
public class CleanupTask {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Scheduled(cron = "0 0 3 1 * ?") // 每月1日3点执行
    public void cleanupExpiredTables() {
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.MONTH, -12); // 保留最近12个月
        String pattern = "your_table_%d%02d";

        for (int year = cal.get(Calendar.YEAR); ; ) {
            for (int month = cal.get(Calendar.MONTH) + 1; month <= 12; month++) {
                String targetTable = String.format(pattern, year, month);
                if (!jdbcTemplate.queryForObject("SELECT COUNT(*) FROM information_schema.tables WHERE table_name = ?", Integer.class, targetTable) > 0) continue;
                jdbcTemplate.execute("DROP TABLE IF EXISTS " + targetTable);
            }
            year++;
            if (year == cal.get(Calendar.YEAR) && month == 1) break;
        }
    }
}

六、注意事项

1. 防止SQL注入：表名生成必须基于系统时间，禁止用户输入
2. 事务处理：跨月分表操作需分布式事务管理
3. 水平扩展：建议使用ShardingSphere等中间件进行代理
4. 监控告警：监控各表大小、查询延迟等指标

该方案通过动态表名+索引优化可支撑高并发读写，分页查询通过并行查询+内存排序保证准确性，适合中等规模分表场景。对于超大规模数据建议结合ES等搜索引擎实现二级索引。