MySQL 亿级数据表平滑分表实践：基于时间分片的架构演进

-- 创建 2023 年 1 月的分表
CREATE TABLE original_table_202301 LIKE original_table;

-- 为分表添加同样的索引
ALTER TABLE original_table_202301 ADD INDEX idx_user_id(user_id);

第二步：分批迁移数据

使用 Java 编写迁移工具，控制迁移速度，避免对线上库造成过大压力：

public class DataMigrator {
    private static final int BATCH_SIZE = 5000;

    public void migrateByMonth(String month) throws SQLException {
        String sourceTable = "original_table";
        String targetTable = "original_table_" + month;
        try (Connection conn = dataSource.getConnection()) {
            long maxId = getMaxId(conn, sourceTable);
            long currentId = 0;
            while (currentId < maxId) {
                String sql = String.format(
                    "INSERT INTO %s SELECT * FROM %s WHERE create_time BETWEEN '%s-01' AND '%s-31' AND id > %d ORDER BY id LIMIT %d",
                    targetTable, sourceTable, month, month, currentId, BATCH_SIZE);
                try (Statement stmt = conn.createStatement()) {
                    stmt.executeUpdate(sql);
                    currentId = getLastInsertedId(conn, targetTable);
                }
                Thread.sleep(100); // 控制迁移速度，防止 IO 阻塞
            }
        }
    }
}

工程师提示：实际生产中建议增加重试机制和异常日志记录，确保迁移任务可监控。

第三步：建立联合视图

为了保持应用层无感知，可以创建一个视图来统一访问入口：

CREATE VIEW original_table_unified AS
SELECT * FROM original_table_202301 UNION ALL
SELECT * FROM original_table_202302 UNION ALL
... -- 根据实际月份补充
SELECT * FROM original_table; -- 当前表作为最新数据

3.2 方案二：触发器过渡方案

对于不能停机的关键业务表，可以使用触发器将新数据自动路由到分表，但需注意触发器对写入性能的影响：

DELIMITER //
CREATE TRIGGER tri_original_table_insert AFTER INSERT ON original_table FOR EACH ROW
BEGIN
    IF NEW.create_time >= '2023-01-01' THEN
        INSERT INTO original_table_new (id, user_id, create_time) VALUES (NEW.id, NEW.user_id, NEW.create_time);
    END IF;
END//
DELIMITER ;

四、Java 应用层适配

4.1 动态表名路由

实现一个简单的表名路由器，根据时间参数决定访问哪张表：

public class TableRouter {
    private static final DateTimeFormatter MONTH_FORMAT = DateTimeFormatter.ofPattern("yyyyMM");

    public static String routeTable(LocalDateTime createTime) {
        String month = createTime.format(MONTH_FORMAT);
        return "original_table_" + month;
    }
}

4.2 MyBatis 分表适配

方案一：动态 SQL

直接在 Mapper XML 中使用 ${} 拼接表名，注意这存在 SQL 注入风险，需配合白名单校验：

<select id="queryByTime" resultType="com.example.Entity">
    SELECT * FROM ${tableName} 
    WHERE user_id = #{userId} AND create_time BETWEEN #{start} AND #{end}
</select>

public List<Entity> queryByTime(Long userId, LocalDate start, LocalDate end) {
    List<String> tableNames = getTableNamesBetween(start, end);
    return tableNames.stream()
        .flatMap(table -> mapper.queryByTime(table, userId, start, end).stream())
        .collect(Collectors.toList());
}

方案二：插件拦截（高级）

通过 MyBatis 的 Interceptor 接口自动替换表名，减少代码侵入性：

@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}))
public class TableShardInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        BoundSql boundSql = ((StatementHandler) invocation.getTarget()).getBoundSql();
        String originalSql = boundSql.getSql();
        if (originalSql.contains("original_table")) {
            Object param = boundSql.getParameterObject();
            LocalDateTime createTime = getCreateTime(param);
            String newSql = originalSql.replace("original_table", "original_table_" + createTime.format(MonthFormat));
            resetSql(invocation, newSql);
        }
        return invocation.proceed();
    }
}

五、分表后的运维管理

5.1 自动建表策略

利用 Spring Scheduler 每月自动创建下月分表，避免人工遗漏：

@Scheduled(cron = "0 0 0 1 * ?") // 每月 1 号执行
public void autoCreateNextMonthTable() {
    LocalDate nextMonth = LocalDate.now().plusMonths(1);
    String tableName = "original_table_" + nextMonth.format(DateTimeFormatter.ofPattern("yyyyMM"));
    jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS " + tableName + " LIKE original_table_template");
}

5.2 数据归档策略

定期将冷数据迁移至归档表，减轻在线库压力：

public void archiveOldData(int keepMonths) {
    LocalDate archivePoint = LocalDate.now().minusMonths(keepMonths);
    String archiveTable = "archive_table_" + archivePoint.getYear();
    
    // 创建归档表
    jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS " + archiveTable + " LIKE original_table_template");
    
    // 迁移数据
    jdbcTemplate.update("INSERT INTO " + archiveTable + " SELECT * FROM original_table WHERE create_time < ?", archivePoint.atStartOfDay());
    
    // 删除原数据
    jdbcTemplate.update("DELETE FROM original_table WHERE create_time < ?", archivePoint.atStartOfDay());
}

六、踩坑与经验总结

6.1 遇到的典型问题

1. 跨分页查询问题：
   • 解决方案：使用 Elasticsearch 等中间件预聚合，或限制单次查询时间范围。
2. 分布式事务问题：
   • 解决方案：避免跨分表事务，或引入 Seata 等框架。
3. 全局唯一 ID 问题：
   • 解决方案：使用雪花算法 (Snowflake) 生成 ID，避免自增主键冲突。

6.2 性能对比数据

七、未来演进方向

1. 分库分表：当单机容量达到瓶颈时考虑
2. TiDB 迁移：对于超大规模数据考虑 NewSQL 方案
3. 数据湖架构：将冷数据迁移到 HDFS 等存储

结语

MySQL 分表是一个系统工程，需要结合业务特点选择合适的分片策略。本文介绍的按时间分表方案，在保证业务连续性的前提下，成功将 4 亿数据表的查询性能提升了 7 倍。希望这篇实践总结能为面临类似问题的开发者提供有价值的参考。

注意：任何架构改造都要先在测试环境充分验证，并准备好回滚方案！