当前位置：日志文章 > 详细内容

MySQL按时间维度对亿级数据表进行平滑分表

2025年08月17日 • Mysql •

引言在互联网应用快速发展的今天，数据量呈现爆炸式增长。作为后端开发者，我们常常会遇到单表数据量过亿导致的性能瓶颈问题。本文将以一个真实的4亿数据表分表案例为基础，详细介绍如何在不影响线上业务的情况下，

引言

在互联网应用快速发展的今天，数据量呈现爆炸式增长。作为后端开发者，我们常常会遇到单表数据量过亿导致的性能瓶颈问题。本文将以一个真实的4亿数据表分表案例为基础，详细介绍如何在不影响线上业务的情况下，完成按时间维度分表的完整过程，包含架构设计、具体实施方案、java代码适配以及注意事项等全方位内容。

一、为什么我们需要分表

1.1 单表数据量过大的问题

当mysql单表数据量达到4亿级别时，会面临诸多挑战：

索引膨胀，b+树层级加深，查询效率下降
备份恢复时间呈指数级增长
ddl操作（如加字段、改索引）锁表时间不可接受
高频写入导致锁竞争加剧

1.2 分表方案选型

常见的分表策略有：

水平分表：按行拆分，如按id范围、哈希、时间等
垂直分表：按列拆分，将不常用字段分离
分区表：mysql内置分区功能

本文选择按时间水平分表，因为：

业务查询大多带有时间条件
天然符合数据冷热特征
便于历史数据归档

二、分表前的准备工作

2.1 数据评估分析

-- 分析数据时间分布
select 
    date_format(create_time, '%y-%m') as month,
    count(*) as count
from original_table
group by month
order by month;

2.2 分表命名规范设计

制定明确的分表命名规则：

主表：original_table
月度分表：original_table_202301
年度分表：original_table_2023
归档表：archive_table_2022

2.3 应用影响评估

检查所有涉及该表的sql：

是否都有时间条件
是否存在跨时间段的复杂查询
事务是否涉及多表关联

三、分表实施方案详解

3.1 方案一：平滑迁移方案（推荐）

第一步：创建分表结构

-- 创建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); // 控制迁移速度
            }
        }
    }
}

第三步：建立联合视图

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 方案二：触发器过渡方案

对于不能停机的关键业务表：

-- 创建分表
create table original_table_new like original_table;

-- 创建触发器
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 values (new.*);
    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

<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(month_format));
            
            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(month_format);
    
    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());
}