MES追溯过程表按月做范围分区、按序列号单独建一张不分区的路由索引表，是兼顾写入、反查和历史归档的稳妥做法。按序列号查追溯时，先查路由表拿到这个码出现过的月份，再带着时间条件去查分区表，才能触发分区裁剪；如果只按序列号直接查分区表，数据库会把每个分区的本地索引都探一遍，分区越多越慢。

什么是“一码一路由”？

“一码一路由”是指：每个产品序列号在一张不分区的路由表里只有一行，记录它初次和末次出现的时间，所有按码追溯的查询都先经过这张表定位月份，再进入按月分区的过程表。它解决的是“表按时间分区了，但车间常用的查询条件却是序列号而不是时间”这一矛盾。

追溯库需要哪几张表？

追溯库的核心是一张按月分区的过程记录表加一张按序列号定位的路由表，其他表都围绕这两张展开。

分区键怎么选？按月、按天还是按工单？

对多数离散制造车间，按扫码时间按月分区是写入、查询和归档三方面都能接受的折中。

选型时先回答两个问题：在线库要保留几个月；归档是删除还是迁到历史库。答案确定后，分区粒度基本就定了。

建表时有哪些硬约束？

在 MySQL 中，分区表上的每个 UNIQUE 约束（包括主键）都必须包含分区表达式用到的全部列，所以主键要写成 (id, scan_time) 这种组合。另外，MySQL 的 InnoDB 分区表不支持外键，引用完整性要靠应用或校验脚本保证。

CREATE TABLE trace_record (
  id          BIGINT       NOT NULL AUTO_INCREMENT,
  sn          VARCHAR(40)  NOT NULL,
  work_order  VARCHAR(30)  NOT NULL,
  op_code     VARCHAR(20)  NOT NULL,
  station     VARCHAR(20)  NOT NULL,
  result      CHAR(2)      NOT NULL,
  operator    VARCHAR(20)  NOT NULL,
  scan_time   DATETIME     NOT NULL,
  PRIMARY KEY (id, scan_time),
  KEY idx_sn_time (sn, scan_time),
  KEY idx_wo_op (work_order, op_code, scan_time)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (scan_time) (
  PARTITION p202609 VALUES LESS THAN ('2026-10-01'),
  PARTITION p202610 VALUES LESS THAN ('2026-11-01'),
  PARTITION p202611 VALUES LESS THAN ('2026-12-01'),
  PARTITION p202612 VALUES LESS THAN ('2027-01-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE sn_route (
  sn           VARCHAR(40) NOT NULL PRIMARY KEY,
  product_code VARCHAR(30) NOT NULL,
  work_order   VARCHAR(30) NOT NULL,
  first_time   DATETIME    NOT NULL,
  last_time    DATETIME    NOT NULL
) ENGINE=InnoDB;

PostgreSQL 的声明式分区同样要求主键包含分区键；Oracle 可以用间隔分区让数据库按月自动建分区，但要注意本地索引与全局索引在删除分区时的维护差异。

按序列号查追溯，SQL怎么写？

按码查询要拆成两步：先用路由表拿到时间范围，再把时间范围作为条件交给分区表，执行计划里才会只出现命中的分区。

-- 步骤一：定位时间范围
SELECT first_time, last_time FROM sn_route WHERE sn = 'SN示例0001';
-- 步骤二：带时间条件进分区表，触发分区裁剪
SELECT op_code, station, result, operator, scan_time
FROM trace_record
WHERE sn = 'SN示例0001'
  AND scan_time >= '2026-10-03 00:00:00'
  AND scan_time <  '2026-10-09 00:00:00'
ORDER BY scan_time;

上线前用 EXPLAIN 检查 partitions 列：只出现一两个月份的分区才算合格；如果列出了全部分区，说明时间条件没有生效，常见原因是对 scan_time 套了函数或者做了隐式类型转换。路由表的 last_time 要在每次过站时更新，返修件、返工件隔几个月再次过站时尤其要注意，否则第二段记录会查不到。

分区怎么维护和归档？

分区维护要做成定时脚本：每月提前建好下下个月的分区，到期的月份先交换到归档表、核对行数后再删除。

-- 从 pmax 中拆出新月份（每月执行，提前一个月）
ALTER TABLE trace_record REORGANIZE PARTITION pmax INTO (
  PARTITION p202701 VALUES LESS THAN ('2027-02-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);
-- 归档：把整月分区与结构相同的空表交换
CREATE TABLE trace_archive_202609 LIKE trace_record;
ALTER TABLE trace_archive_202609 REMOVE PARTITIONING;
ALTER TABLE trace_record EXCHANGE PARTITION p202609 WITH TABLE trace_archive_202609;
-- 核对行数、抽查若干序列号后再删除空分区
ALTER TABLE trace_record DROP PARTITION p202609;

交换分区要求两张表结构完全一致、目标表不分区；交换完成后原分区变空，数据整体落到归档表，比按条件大批量 DELETE 产生的日志和锁少得多。归档表可以迁到历史库，路由表保留全部序列号，这样历史件被客户投诉时仍能定位到它属于哪个归档月份。

实施步骤

步骤一，和质量部门确认在线保留月数与归档保存年限，写进 partition_plan。

步骤二，按上面的结构建过程表和路由表，主键带上分区列。

步骤三，改造过站写入逻辑：写过程表的同时对路由表做插入或更新（INSERT … ON DUPLICATE KEY UPDATE last_time）。

步骤四，把所有按码查询改成“先路由、后分区”的两步写法，并逐条做 EXPLAIN 检查。

步骤五，部署分区巡检脚本：每天检查 pmax 是否有数据、下个月分区是否已建。

步骤六，做一次归档演练：交换、核对、删除、从归档库反查，全流程走通再上生产。

常见坑

1. 主键只写 id，建分区表直接报错；改成 (id, scan_time) 后，按 id 单查也要带时间条件。

2. 查询条件写成 DATE(scan_time) = '2026-10-05'，函数包住分区列导致无法裁剪，应改成左闭右开区间。

3. 忘记建新月份分区，数据全部落进 pmax，几个月后 pmax 变成体量很大的分区，再拆分代价很高。

4. 路由表只在初次过站时写入，返修件再次过站不更新 last_time，追溯结果缺一段。

5. 分区数过多（按天分区保留多年），元数据打开开销和维护脚本复杂度都会上升。

6. 依赖外键保证过程表与工单表一致，但分区表不支持外键，需改为写入校验加夜间核对。

7. 归档时直接 DROP 分区而没有先交换和核对行数，误删后只能靠备份恢复。

车间场景

以一条装配线为例：每台产品在上线、关键件装配、功能测试、包装四个工序扫码，每次扫码写一行 trace_record，同时刷新 sn_route 的 last_time。三个月后客户投诉一台产品，质量工程师在追溯页面输入序列号，系统先查到它在10月上旬生产、12月因返修再次过站，于是只查 p202610 和 p202612 两个分区，把装配记录、测试结果和返修记录按时间排在一起。一年后该月份数据已交换到历史库，查询页面根据 partition_plan 的状态自动切换到归档库查询。

验收项

1. 每条按码查询的 EXPLAIN 只命中路由表给出的分区。

2. 随机抽取一批序列号，两步查询的结果与全表查询结果一致。

3. 下个月分区提前存在，pmax 中没有数据。

4. 归档演练后，归档表行数与原分区行数一致，历史序列号仍可反查。

5. 返修件跨月再次过站后，路由表 last_time 已更新，追溯结果完整。

6. 查询响应时间在实际数据量下测试，并把测试数据量和结果写进验收记录（以现场实测为准，不套用别处数字）。

FAQ

问：只有一张过程表，数据量不大，有必要分区吗？

答：如果在线保留期短、删除也不频繁，可以先不分区，但建议主键和查询从一开始就带上时间条件，以后改分区时代价小。

问：序列号能不能直接作为分区键？

答：不建议。按序列号做 HASH 分区无法按时间归档，而追溯数据常见的生命周期操作恰恰是按月归档。

问：路由表会不会成为新的瓶颈？

答：路由表一码一行、主键直接定位，写入是主键上的插入或更新，通常比过程表轻得多；需要关注的是高峰期的行锁冲突，同一序列号短时间内多次过站时应合并更新。

问：PostgreSQL 或 Oracle 也是这个思路吗？

答：思路相同：时间分区加按码定位。PostgreSQL 可以在分区表上建分区本地索引，并可用 DETACH PARTITION 归档；Oracle 可以用间隔分区自动建月分区，删除分区时注意同步维护全局索引。

问：历史数据交换到归档库后，追溯页面怎么查？

答：保留全量路由表和 partition_plan，查询层根据月份状态决定访问在线库还是归档库，用户侧仍然只输入一个序列号。

更新于2026年11月

本文为作者原创，转载请注明出处与作者。