MySQL归档表最优分区方案咨询：sd_servicerequest_history表分区建议

针对你的工单历史表分区难题，我结合表结构和常见的业务查询场景，整理了几个实用的最优方案：

首先先明确你的表结构（方便参考）：

CREATE TABLE `sd_servicerequest_history` ( 
  `ProblemId` int(11) NOT NULL, 
  `CurrentTime` datetime NOT NULL, 
  `NatureOfChange` varchar(255) NOT NULL, 
  `ActionPerformedBy` varchar(255) NOT NULL, 
  `HistoryID` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `OldValue` varchar(5120) NOT NULL, 
  `NewValue` varchar(5120) NOT NULL, 
  `Parameter` varchar(255) NOT NULL, 
  `FIELDID` int(10) unsigned NOT NULL DEFAULT '0', 
  `ChildOf` int(10) unsigned NOT NULL DEFAULT '0', 
  `OldStateID` int(10) unsigned NOT NULL DEFAULT '0', 
  `NewStateID` int(10) unsigned NOT NULL DEFAULT '0', 
  `Userid` int(10) DEFAULT '0', 
  PRIMARY KEY (`HistoryID`), 
  KEY `FK_servicehistory_ProblemId` (`ProblemId`), 
  KEY `ChildOfIndex` (`ChildOf`), 
  KEY `Userid` (`Userid`), 
  CONSTRAINT `FK_servicehistory_1` FOREIGN KEY (`Userid`) REFERENCES `userdetails` (`userid`) ON DELETE CASCADE, 
  CONSTRAINT `FK_servicehistory_ProblemId` FOREIGN KEY (`ProblemId`) REFERENCES `sd_servicereqmaster` (`ProblemId`) ON DELETE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

你之前尝试按ProblemId分区但无法在WHERE子句传入该字段，说明这个字段不是你查询时的常用过滤条件，那我们得换一个符合查询习惯的分区键：

1. 优先推荐：按CurrentTime时间范围分区 #

这是工单历史表最通用的分区方案，因为几乎所有查询历史记录的场景都会按时间范围过滤（比如查近3个月的状态变更、清理1年前的历史数据）。

实现方式（按月RANGE分区示例）： #

-- 注意：InnoDB引擎要求分区键必须包含在主键中，所以需要调整主键结构
CREATE TABLE `sd_servicerequest_history` ( 
  -- 字段结构与原表一致，此处省略重复字段
  `ProblemId` int(11) NOT NULL, 
  `CurrentTime` datetime NOT NULL, 
  `HistoryID` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  -- 其他字段...
  PRIMARY KEY (`HistoryID`, `CurrentTime`), 
  KEY `FK_servicehistory_ProblemId` (`ProblemId`), 
  KEY `ChildOfIndex` (`ChildOf`), 
  KEY `Userid` (`Userid`), 
  CONSTRAINT `FK_servicehistory_1` FOREIGN KEY (`Userid`) REFERENCES `userdetails` (`userid`) ON DELETE CASCADE, 
  CONSTRAINT `FK_servicehistory_ProblemId` FOREIGN KEY (`ProblemId`) REFERENCES `sd_servicereqmaster` (`ProblemId`) ON DELETE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(CurrentTime)) (
  PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
  -- 可提前创建未来几个月的分区，或用脚本自动维护
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

核心优点： #

• 查询性能飙升：当你执行WHERE CurrentTime BETWEEN '2024-01-01' AND '2024-01-31'时，MySQL只会扫描p202401分区，完全避免全表扫描。
• 数据维护超便捷：清理旧历史数据时，直接执行ALTER TABLE sd_servicerequest_history DROP PARTITION p202401;，比DELETE语句高效N倍，不会产生大量冗余日志。
• 完美匹配业务：工单历史的查询、归档都是按时间维度进行的，这个方案完全贴合业务习惯。

2. 备选方案：按Userid列表/范围分区 #

如果你的系统经常需要按操作人（ActionPerformedBy关联的Userid）查询历史记录，且Userid的分布比较有规律（比如按部门分组的连续ID），可以考虑这个方案。

实现方式（LIST分区示例）： #

CREATE TABLE `sd_servicerequest_history` ( 
  -- 字段结构与原表一致
  `Userid` int(10) DEFAULT '0', 
  PRIMARY KEY (`HistoryID`, `Userid`), -- 主键需包含分区键
  -- 其他索引、外键...
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(Userid) (
  PARTITION p_dept1 VALUES IN (1,2,3,4,5), -- 部门1的用户ID集合
  PARTITION p_dept2 VALUES IN (6,7,8,9,10), -- 部门2的用户ID集合
  PARTITION p_other VALUES IN (DEFAULT)
);

注意事项： #

• 这个方案局限性较大，如果用户ID频繁新增，需要手动维护分区，适合用户规模稳定的场景。
• 查询时必须带Userid过滤条件，才能触发分区裁剪，否则会扫描所有分区。

3. 进阶方案：复合分区（时间+ProblemId哈希） #

如果你的业务既需要按时间查询，又偶尔需要按ProblemId批量查询，可以用RANGE-HASH复合分区：先按CurrentTime做RANGE分区，每个时间分区内再按ProblemId做HASH分区，进一步缩小查询范围。

实现方式： #

CREATE TABLE `sd_servicerequest_history` ( 
  -- 字段结构与原表一致
  PRIMARY KEY (`HistoryID`, `CurrentTime`, `ProblemId`), -- 主键需包含所有分区键
  -- 其他索引、外键...
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(CurrentTime))
SUBPARTITION BY HASH(ProblemId)
SUBPARTITIONS 4 ( -- 每个时间分区下分4个子分区
  PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

核心优势： #

• 按时间查询时，先定位到时间分区，再在子分区里按ProblemId哈希快速定位，比单一分区更高效。
• 适合既有时间范围查询，又有ProblemId批量查询的复杂业务场景。

关键注意事项 #

• InnoDB引擎要求分区键必须包含在主键中，所以修改分区方案时需要调整主键结构。
• 无论选择哪种分区，查询时一定要带上分区键的过滤条件，否则MySQL会扫描所有分区，反而降低性能。
• 提前规划分区维护策略，比如时间分区可以写个脚本每月自动创建下一个月的分区，避免数据写入到p_future分区后无法拆分。

内容的提问来源于stack exchange，提问作者Pradeep kumar Tripathi