MySQL表分区归档实践

当MySQL单表数据量突破千万级别，即使索引设计得再合理，查询性能也会肉眼可见地下降。这不是MySQL的缺陷，而是B+树索引在数据量膨胀时的必然瓶颈。解决这个问题的方案很多，但分区表归档是成本最低、对业务侵入最小的一种。它不需要引入中间件，不需要拆分应用逻辑，完全在数据库层面就能把历史数据“冷热分离”，让热数据查询始终保持在毫秒级响应。

分区表归档的核心思路

分区表归档的本质，是把一张大表按时间维度物理切分成多个独立的数据块，每个数据块对应一个分区。当数据过期后，直接把整个分区切走，而不是逐行删除。这个操作在MySQL内部是一个DDL级别的元数据变更，执行时间以毫秒计，完全不受数据量影响。相比DELETE逐行删除带来的锁等待、undo膨胀、主从延迟等一系列问题，分区交换几乎是零成本的清理手段。

这里有一个关键认知需要明确：分区不是为了加速单条查询，而是为了让查询只扫描必要的分区。如果你的查询条件总是带上时间范围，分区裁剪机制会自动忽略不相关的分区，这才是性能提升的根本原因。

选择合适的分区策略

MySQL支持RANGE、LIST、HASH、KEY四种分区方式。对于归档场景，RANGE分区几乎是不二之选。按时间字段做范围分区，逻辑清晰，维护简单，而且分区裁剪效果最好。常见的做法是按月份或按天分区，具体粒度取决于业务查询模式和数据增长速度。

假设我们有一张订单表orders，需要按月分区并定期归档三个月以前的数据。建表语句如下：

CREATE TABLE orders (
    id BIGINT NOT NULL AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at),
    KEY idx_user_id (user_id),
    KEY idx_order_no (order_no)
) ENGINE=InnoDB 
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
    PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01')),
    PARTITION p202503 VALUES LESS THAN (TO_DAYS('2025-04-01')),
    PARTITION p202504 VALUES LESS THAN (TO_DAYS('2025-05-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

注意主键必须包含分区键，这是MySQL分区的硬性要求。如果业务主键是id，而分区键是created_at，那么主键必须改成联合主键(id, created_at)。这个改动对业务代码通常无感知，但需要提前评估对二级索引的影响。

分区维护的自动化脚本

手动管理分区是运维噩梦，必须自动化。核心逻辑是：每月月初创建下个月的新分区，同时把三个月前的旧分区数据归档到历史表，然后删除空分区。下面是一个可以直接使用的存储过程：

DELIMITER $$
CREATE PROCEDURE sp_partition_maintenance()
BEGIN
    DECLARE v_archive_partition VARCHAR(64);
    DECLARE v_new_partition VARCHAR(64);
    DECLARE v_new_partition_value VARCHAR(32);
    DECLARE v_archive_date VARCHAR(32);
    DECLARE v_sql TEXT;
    
    -- 计算三个月前的分区名和日期
    SET v_archive_date = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m-01');
    SET v_archive_partition = CONCAT('p', DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y%m'));
    
    -- 计算下个月的分区名和边界值
    SET v_new_partition = CONCAT('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y%m'));
    SET v_new_partition_value = DATE_FORMAT(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 2 MONTH), INTERVAL -DAY(DATE_ADD(CURDATE(), INTERVAL 2 MONTH)) DAY), '%Y-%m-%d');
    
    -- 检查归档分区是否存在且数据是否需要归档
    SELECT COUNT(*) INTO @cnt FROM information_schema.partitions 
    WHERE table_schema = DATABASE() 
    AND table_name = 'orders' 
    AND partition_name = v_archive_partition;
    
    IF @cnt > 0 THEN
        -- 将归档分区数据插入历史表（历史表需提前创建，结构与orders一致）
        SET v_sql = CONCAT('INSERT INTO orders_archive SELECT * FROM orders PARTITION (', v_archive_partition, ')');
        PREPARE stmt FROM v_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 删除归档分区
        SET v_sql = CONCAT('ALTER TABLE orders DROP PARTITION ', v_archive_partition);
        PREPARE stmt FROM v_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
    
    -- 检查新分区是否需要创建
    SELECT COUNT(*) INTO @cnt FROM information_schema.partitions 
    WHERE table_schema = DATABASE() 
    AND table_name = 'orders' 
    AND partition_name = v_new_partition;
    
    IF @cnt = 0 THEN
        -- 需要先删除MAXVALUE分区再添加，或者重组MAXVALUE分区
        SET v_sql = CONCAT('ALTER TABLE orders REORGANIZE PARTITION p_future INTO (',
            'PARTITION ', v_new_partition, ' VALUES LESS THAN (TO_DAYS(''', v_new_partition_value, ''')),',
            'PARTITION p_future VALUES LESS THAN MAXVALUE)');
        PREPARE stmt FROM v_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$
DELIMITER ;

这个存储过程做了三件事：把三个月前的分区数据迁移到归档表、删除旧分区、创建下个月的新分区。通过MySQL的事件调度器每月执行一次，就能实现完全自动化的分区维护。需要注意的是REORGANIZE PARTITION操作会锁表，建议在业务低峰期执行，虽然对于InnoDB来说这个锁的时间通常很短。

归档数据的存储与查询策略

归档表orders_archive可以放在同一数据库，也可以放在独立的归档库。如果归档数据查询频率很低，甚至可以把归档表迁移到成本更低的存储上。但这里有一个容易被忽略的优化点：归档表同样需要分区。如果归档表存储了数年数据，不分区的归档表查询起来同样会很慢。建议归档表按年或按季度分区，保持每个分区大小在合理范围内。

对于需要跨热冷数据查询的场景，可以通过应用层路由实现：先查热表，查不到再查归档表。如果查询逻辑复杂，也可以创建一个视图把热表和归档表union起来，但要注意视图不能做分区裁剪，大范围查询时性能会很差。更好的做法是在DAO层封装查询逻辑，根据时间参数自动选择目标表。

分区归档的坑与应对方案

第一个坑是分区键选择不当。很多开发者习惯用timestamp类型，但TO_DAYS()函数要求参数是DATE或DATETIME类型。如果用整数时间戳，需要用UNIX_TIMESTAMP()转换，但RANGE分区的边界值计算会变得很繁琐。建议直接用DATETIME类型配合TO_DAYS()，代码可读性和维护性都更好。

第二个坑是唯一索引问题。MySQL要求分区表的所有唯一索引（包括主键）必须包含分区键。这意味着如果你的业务需要全局唯一的order_no，而分区键是created_at，那么唯一索引必须是(order_no, created_at)的联合索引。这在逻辑上并不保证order_no全局唯一，只是保证了分区内唯一。解决办法是在应用层通过分布式ID生成方案（如雪花算法）确保全局唯一性，数据库层面接受分区内唯一的限制。

第三个坑是MAXVALUE分区的存在。很多教程建议不要使用MAXVALUE分区，因为REORGANIZE MAXVALUE分区时需要扫描整个分区的数据来确定新边界。但实际上，只要MAXVALUE分区里没有数据，REORGANIZE操作就是纯元数据操作，执行很快。所以关键在于确保定时任务在MAXVALUE分区积累数据之前就完成重组，也就是说至少提前一个月创建好下个月的分区。

第四个坑是分区数量过多。MySQL单表分区数理论上限是8192个，但实际超过几百个分区后，表打开和查询优化器分析分区的时间就会明显增加。对于按月分区的场景，建议保留最近6到12个月的热数据分区，更早的数据归档后直接删除分区，不要让分区数量无限增长。

监控与告警

分区归档自动化之后，必须配套监控。需要监控的指标包括：各分区数据量、MAXVALUE分区是否有数据（说明定时任务失败）、归档表数据量增长趋势、分区维护任务的执行日志。可以通过查询information_schema.partitions表获取分区级别的详细信息：

SELECT 
    partition_name,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    partition_description
FROM information_schema.partitions
WHERE table_schema = 'your_database'
AND table_name = 'orders'
ORDER BY partition_ordinal_position;

当发现MAXVALUE分区的table_rows大于0时，说明定时任务没有正常创建新分区，需要立即处理，否则数据会持续写入MAXVALUE分区，后续REORGANIZE的代价会越来越大。

性能对比与实战数据

以一个实际案例为例：订单表约2000万行数据，未分区时，查询近一个月订单需要扫描全表，平均耗时2.3秒。按月分区后，同样的查询只扫描当月分区，耗时降至0.08秒。归档操作方面，之前用DELETE分批清理三个月前的数据，每次清理500万行需要执行4小时以上，期间主从延迟飙升至30分钟。改用分区交换后，整个归档操作在0.5秒内完成，对业务完全无感知。这就是分区归档最直接的价值。

分区表归档不是银弹，它适合数据有明显时间冷热特征、查询条件总是带时间范围的场景。如果你的查询模式是随机点查或者全表聚合分析，分区的收益就很有限。但对于绝大多数业务系统来说，订单、日志、流水、账单这类数据天然适合分区归档。相比引入ShardingSphere或自研分库分表中间件，分区表的运维复杂度低一个数量级，是中小规模业务的首选方案。当数据量进一步增长到单机无法承载时，再考虑分库分表也不迟，因为分区表可以平滑过渡到分库分表架构，每个分片内部仍然可以继续使用分区来管理数据生命周期。

服务指南

帮助中心