MySQL亿级数据平滑分表方案全解析

一、分表策略设计

1.1 时间维度分表模型

graph TD
    A[原始大表] --> B{分表策略}
    B --> C[按年分表]
    B --> D[按月分表]
    B --> E[按周分表]
    style C fill:#6f9,stroke:#333
    style D fill:#6f9,stroke:#333
    style E fill:#6f9,stroke:#333

1.2 策略选择矩阵

数据规模	查询特点	推荐策略	示例表名格式
1-5亿	按季度分析	按月分表	orders_202301
5-10亿	高频月度查询	按周分表	orders_2023_w01
10亿+	混合时间范围	年表+月表	orders_2023 / orders_202301

二、技术实现方案

2.1 分表路由逻辑

// Java分表路由示例
public String getTableSuffix(Date date) {
    Calendar cal = Calendar.getInstance();
    cal.setTime(date);
    return String.format("_%04d%02d", 
        cal.get(Calendar.YEAR),
        cal.get(Calendar.MONTH) + 1);
}

// MyBatis动态表名示例
@Select("SELECT * FROM orders${suffix} WHERE id=#{id}")
Order selectById(@Param("id") Long id, @Param("suffix") String suffix);

2.2 分表DDL生成器

-- 按月分表生成脚本示例
DELIMITER //
CREATE PROCEDURE create_month_tables(IN start_date DATE, IN months INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < months DO
        SET @table_name = CONCAT('orders_', DATE_FORMAT(DATE_ADD(start_date, INTERVAL i MONTH), '%Y%m'));
        SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ', @table_name, ' LIKE orders_template');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

三、数据迁移方案

3.1 在线迁移流程

sequenceDiagram
    应用->>中间件: 1. 双写开启
    中间件->>旧表: 2. 存量数据迁移
    中间件->>新表: 3. 实时增量同步
    中间件->>应用: 4. 读流量切换
    中间件->>旧表: 5. 最终校验

3.2 迁移工具对比

工具	优点	限制	适用场景
pt-online-schema-change	不锁表	单线程较慢	中小表迁移
gh-ost	可暂停/低延迟	需要触发器	大表在线迁移
DataX	批量高速	需要停机窗口	历史数据迁移
Canal+Spark	实时同步	架构复杂	持续增量同步

四、查询路由设计

4.1 多表查询方案

-- 跨月查询UNION ALL方案
SELECT * FROM (
    SELECT * FROM orders_202301 WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31'
    UNION ALL
    SELECT * FROM orders_202302 WHERE create_time BETWEEN '2023-02-01' AND '2023-02-28'
) AS combined 
WHERE user_id = 12345
LIMIT 1000;

4.2 视图封装方案

-- 创建年份视图
CREATE VIEW orders_2023 AS
SELECT * FROM orders_202301 UNION ALL
SELECT * FROM orders_202302 UNION ALL
...
SELECT * FROM orders_202312;

五、ShardingSphere实战

5.1 分片配置示例

# ShardingSphere-JDBC配置
spring:
  shardingsphere:
    datasource:
      names: ds0
    sharding:
      tables:
        orders:
          actual-data-nodes: ds0.orders_$->{2023..2025}$->{(1..12).collect{t -> t.toString().padLeft(2,'0')}}
          table-strategy:
            standard:
              sharding-column: create_time
              precise-algorithm-class-name: com.example.TimePreciseShardingAlgorithm
              range-algorithm-class-name: com.example.TimeRangeShardingAlgorithm

5.2 自定义分片算法

public class TimeRangeShardingAlgorithm implements RangeShardingAlgorithm<Date> {
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, 
                                        RangeShardingValue<Date> shardingValue) {
        // 处理 BETWEEN AND 查询
        List<String> result = new ArrayList<>();
        LocalDateTime start = shardingValue.getValueRange().lowerBound()
            .toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
        LocalDateTime end = shardingValue.getValueRange().upperBound()
            .toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
        
        while (start.isBefore(end)) {
            String tableSuffix = "_" + start.format(DateTimeFormatter.ofPattern("yyyyMM"));
            if (availableTargetNames.contains("orders" + tableSuffix)) {
                result.add("orders" + tableSuffix);
            }
            start = start.plusMonths(1);
        }
        return result;
    }
}

六、性能优化要点

6.1 索引设计规范

索引类型	推荐设置	分表注意事项
主键	保持全局唯一	考虑时间戳前缀
时间索引	分区键必须包含	每个分表独立创建
联合索引	遵循最左匹配	避免跨表索引扫描
全文索引	考虑ES替代方案	分表后维护困难

6.2 查询优化技巧

-- 优化前（全表扫描）
SELECT * FROM orders_* WHERE product_id = 123;

-- 优化后（精准路由）
SELECT * FROM orders_202301 WHERE product_id = 123 AND create_time BETWEEN '2023-01-01' AND '2023-01-31';

七、平滑过渡方案

7.1 双写架构设计

classDiagram
    class OrderService {
        +save(order): void
        +query(id): Order
    }
    class DualWriteProxy {
        -primaryDS: DataSource
        -shadowDS: DataSource
        +writeBoth(): void
        +migrateHistorical(): void
    }
    OrderService --> DualWriteProxy

7.2 灰度切换流程

阶段一：10%读流量走新表
阶段二：开启双写，校验一致性
阶段三：50%读流量切换
阶段四：100%读写切新表
阶段五：旧表转归档

八、监控与维护

8.1 关键监控指标

指标	预警阈值	监控工具
单表数据量	>5000万行	Prometheus+Grafana
查询响应时间	>500ms	SkyWalking
磁盘空间增长率	>10%/天	Zabbix
跨表查询占比	>5%	慢查询日志

8.2 自动化运维脚本

#!/bin/bash
# 自动创建下月分表
next_month=$(date -d "next month" +"%Y%m")
mysql -uadmin -p$PASSWORD -e "
CREATE TABLE orders_${next_month} (
    id BIGINT PRIMARY KEY,
    user_id INT NOT NULL,
    create_time DATETIME NOT NULL,
    INDEX idx_ctime (create_time)
) ENGINE=InnoDB PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p${next_month}_start VALUES LESS THAN (TO_DAYS('${next_month}01')),
    PARTITION p${next_month}_end VALUES LESS THAN MAXVALUE
);"

九、常见问题解决方案

9.1 热点问题处理

场景	解决方案	实施要点
历史表访问频繁	建立热点数据缓存层	Redis集群+本地缓存
当前月份写入热点	预分表+队列削峰	Kafka异步写入
跨表扫描性能差	建立汇总物化视图	定时刷新机制

9.2 扩容方案对比

方案	优点	缺点	适用阶段
垂直分表	改动小	单表容量有限	初期(<1亿)
水平分表	扩展性好	应用改造成本高	中期(1-10亿)
分库分表	彻底解决瓶颈	架构复杂度高	长期(10亿+)

十、最佳实践总结

10.1 实施路线图

gantt
    title 亿级数据分表实施计划
    dateFormat  YYYY-MM-DD
    section 准备阶段
    容量评估 : 2023-09-01, 7d
    架构设计 : 2023-09-08, 5d
    section 实施阶段
    分表改造 : 2023-09-13, 14d
    数据迁移 : 2023-09-27, 10d
    section 验证阶段
    功能测试 : 2023-10-07, 7d
    性能测试 : 2023-10-14, 7d
    section 上线阶段
    灰度发布 : 2023-10-21, 5d
    全量切换 : 2023-10-26, 3d