多表MariaDB数据库分区归档方案技术咨询

背景与现状 #

• 数据库环境：3节点跨城Galera集群，NVMe存储，当前容量近500GB，备份体积持续增大，SST同步耗时数小时，效率低下
• 数据特性：类遥测数据，入库后10-15分钟内可能因冲突更新，之后无变更
• 归档目标：每年将历史数据归档至低速存储服务器，生产集群仅保留近期数据；每个客户对应3张关联表（无外键，通过字段关联JOIN）：
  • summary_X：事件汇总表
  • details_X：事件详情表（1-10条/汇总事件）
  • digits_X：数字记录表（0-10条/详情记录）

表结构 #

CREATE TABLE `summary_X` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_utc` datetime DEFAULT NULL,
  `end_utc` datetime DEFAULT NULL,
  `total_duration` smallint(6) DEFAULT NULL,
  `legs` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `start_utc` (`start_utc`)
) ENGINE=InnoDB;

CREATE TABLE `details_X` (
  `xid` bigint(20) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  `duration` smallint(6) DEFAULT NULL,
  `start_utc` timestamp NULL DEFAULT NULL,
  `end_utc` timestamp NULL DEFAULT NULL,
  `event` varchar(2) DEFAULT NULL,
  `event_time` smallint(6) DEFAULT NULL,
  `event_a` varchar(7) DEFAULT NULL,
  `event_b` varchar(7) DEFAULT NULL,
  `ani` varchar(20) DEFAULT NULL,
  `dnis` varchar(10) DEFAULT NULL,
  `first_time` varchar(30) DEFAULT NULL,
  `final_time` varchar(30) DEFAULT NULL,
  `digits_count` int(2) DEFAULT 0,
  `sys_a` varchar(3) DEFAULT NULL,
  `sys_b` varchar(3) DEFAULT NULL,
  `log_id_a` varchar(12) DEFAULT NULL,
  `seq_a` varchar(1) DEFAULT NULL,
  `log_id_b` varchar(12) DEFAULT NULL,
  `seq_b` varchar(1) DEFAULT NULL,
  `assoc_log_id_a` varchar(12) DEFAULT NULL,
  `assoc_log_id_b` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`xid`),
  KEY `start_utc` (`start_utc`),
  KEY `end_utc` (`end_utc`),
  KEY `event_a` (`event_a`),
  KEY `event_b` (`event_b`),
  KEY `id` (`id`),
  KEY `final_digits` (`final_digits`),
  KEY `log_id_a` (`log_id_a`),
  KEY `log_id_b` (`log_id_b`)
) ENGINE=InnoDB;

CREATE TABLE `digits_X` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `leg_id` bigint(20) DEFAULT NULL,
  `sequence` int(2) NOT NULL,
  `digits` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `digits` (`digits`),
  KEY `leg_id` (`leg_id`)
) ENGINE=InnoDB;

现有方案的痛点 #

1. 按年份分区的问题：
   • digits_X无日期字段，无法直接按年份分区，归档后易与关联的details_X记录孤立
   • 部分details_X记录时间跨年度，会导致与summary_X记录分离（如2021年汇总事件关联2022年详情记录，归档2021分区后详情记录孤立）
2. 无分区直接执行SELECT/INSERT/DELETE的问题：单表年数据量3000-4000万行，且有400+客户独立表，资源消耗过大，不可行
3. 新增统一“年份”字段的问题：会额外占用存储空间，并非最优解

优化方案建议 #

方案1：基于关联主键的分区+归档事务保障 #

核心思路

利用summary_X.id -> details_X.id -> digits_X.leg_id的层级关联关系，以summary_X的start_utc作为分区依据，归档时通过关联字段批量操作，保证数据完整性。

具体步骤

1. 为summary_X按年份+季度（或月份）做范围分区（缩小分区粒度，减少跨年关联数据的影响）：

   ALTER TABLE summary_X PARTITION BY RANGE (YEAR(start_utc)*100 + QUARTER(start_utc)) (
     PARTITION p2024q1 VALUES LESS THAN (202402),
     PARTITION p2024q2 VALUES LESS THAN (202403),
     PARTITION p2024q3 VALUES LESS THAN (202404),
     PARTITION p2024q4 VALUES LESS THAN (202501),
     PARTITION p_future VALUES LESS THAN MAXVALUE
   );
   

2. 归档操作（事务中执行，保证原子性）：
   • 导出归档数据（可使用SELECT ... INTO OUTFILE或mysqldump带过滤条件）：

     -- 导出summary_X归档分区数据
     SELECT * FROM summary_X PARTITION (p2023q1) INTO OUTFILE '/path/to/archive/summary_X_2023q1.csv';
     -- 导出关联的details_X数据
     SELECT d.* FROM details_X d JOIN summary_X s ON d.id = s.id WHERE s.start_utc BETWEEN '2023-01-01' AND '2023-03-31';
     -- 导出关联的digits_X数据
     SELECT dg.* FROM digits_X dg JOIN details_X d ON dg.leg_id = d.xid JOIN summary_X s ON d.id = s.id WHERE s.start_utc BETWEEN '2023-01-01' AND '2023-03-31';
     

   • 删除生产集群数据：

     START TRANSACTION;
     -- 删除digits_X关联数据
     DELETE dg FROM digits_X dg JOIN details_X d ON dg.leg_id = d.xid JOIN summary_X s ON d.id = s.id WHERE s.start_utc BETWEEN '2023-01-01' AND '2023-03-31';
     -- 删除details_X关联数据
     DELETE d FROM details_X d JOIN summary_X s ON d.id = s.id WHERE s.start_utc BETWEEN '2023-01-01' AND '2023-03-31';
     -- 直接DROP分区，效率远高于DELETE
     ALTER TABLE summary_X DROP PARTITION p2023q1;
     COMMIT;
     

3. 优势：无需新增字段，利用现有关联关系保证数据完整性；DROP分区操作高效，避免全表扫描；细粒度分区减少跨年关联数据的影响。

方案2：添加派生虚拟字段实现统一分区 #

核心思路

为details_X和digits_X添加虚拟生成字段（Virtual Column），基于关联表的日期字段自动计算，无需存储实际数据（或仅存储少量计算结果），避免存储空间浪费。

具体步骤

1. 为details_X添加关联summary_X.start_utc的生成字段：

   -- 存储型生成字段（占用少量空间，提升查询效率）
   ALTER TABLE details_X ADD COLUMN summary_year INT GENERATED ALWAYS AS (YEAR((SELECT start_utc FROM summary_X WHERE id = details_X.id))) STORED;
   CREATE INDEX idx_details_summary_year ON details_X(summary_year);
   

2. 为digits_X添加关联summary_year的生成字段：

   ALTER TABLE digits_X ADD COLUMN summary_year INT GENERATED ALWAYS AS (YEAR((SELECT s.start_utc FROM summary_X s JOIN details_X d ON s.id = d.id WHERE d.xid = digits_X.leg_id))) STORED;
   CREATE INDEX idx_digits_summary_year ON digits_X(summary_year);
   

3. 三张表统一按summary_year做范围分区，归档时直接DROP对应分区即可，保证关联数据同时归档。

注意事项

• 需保证summary_X.id到details_X.id、details_X.xid到digits_X.leg_id的关联关系无脏数据，否则生成字段会出现错误；
• 存储型生成字段仅占用少量存储空间，相比新增普通字段的空间开销可忽略。

方案3：按客户+时间维度拆分表（分表+分区结合） #

核心思路

针对400+客户的独立表，进一步按时间维度拆分（如每年一张表），结合分区实现更细粒度的管理。

具体步骤

1. 为每个客户创建年度表，如summary_X_2023、summary_X_2024，表结构与原表一致；
2. 利用视图统一对外查询接口，应用层无需修改：

   CREATE VIEW summary_X AS 
     SELECT * FROM summary_X_2023 
     UNION ALL 
     SELECT * FROM summary_X_2024 
     UNION ALL 
     SELECT * FROM summary_X_future;
   

3. 归档时直接将整表迁移至低速存储，生产集群仅保留近1-2年的表。

优势

• 单表数据量大幅降低，操作效率提升；
• 归档迁移简单直接，无需复杂关联查询。

内容的提问来源于stack exchange，提问作者Jemson