mysql - 库存历史记录 - 版本控制风格方法

Question

情况

我正在为一个跟踪超过 200,000 个序列化设备的应用程序创建库存历史记录。目标是能够回顾过去并准确了解设备在 X 天的位置。

我认识到，理想的情况可能是在项目立即更改时触发触发器来更新历史记录，但这将是一项非常大规模的任务，并且当前应用程序无法轻松实现。

考虑到这一点，我们决定运行一个夜间脚本来检查当前库存并将其存储到 inventory_history 表中，以跟踪库存的位置、状态等。最初，我们尝试每天都在历史中翻阅。 IE 每天插入 200,000 行，每 5 天就会产生超过一百万条记录。我们发现这将在不到一年的时间内产生 GB 的数据。我建议的解决方案是在版本控制样式历史记录中实现它。因此，不要每天插入 200,000 条记录，而是只插入已更改的记录。 (并为已删除的记录插入已删除的记录。)

问题

• 这种方法有什么明显的问题吗？对于不是为历史设计的已构建应用程序，是否有更好的替代方法？
• 如果这种方法是好的，那么我可能会遗漏哪些需要实现的内容？目前我已经实现了以下场景:
  • 如果不存在具有完全相同值的则插入。
  • 如果当天未找到设备，则插入删除记录。
  • 选择时，使用历史搜索允许的最新日期按设备 ID 进行分组。 (如果我们想了解 2014 年 1 月 1 日的库存状态，请不要选择此后发生的任何记录，而是对记录进行分组，以便显示的是最新的记录。)

注释

当我们查看历史记录时，有时我们想了解特定的设备，有时我们想了解当天的库存摘要报告。

Answer 1

您可能想要创建两个历史记录表，第一个表快速确定自上次运行流程以来发生的更改，第二个表跟踪库存更改的历史记录。

第一个表是您的库存表的副本，因为它是您上次运行此每晚(每天、每小时等)流程时存在的。 (我会添加一个日期时间字段来跟踪该进程上次运行的时间)。该表与您的实际库存表一起使用序列号和相关字段(位置、状态等)确定已插入、删除或更新的内容。

第二个表是库存更改日志(意味着 SERIAL XXX 有多个条目)。每当库存发生变化时，通过将上一个表中找到的记录复制到此表中来填充此表。添加另一个字段:带有值(INSERT、UPDATE、DELETE)的 ACTION。

伪代码:

填充插入和更新

INSERT INTO inventory_transaction (serial_number,lastupdated,ACTION, location, status)
SELECT inventory.serial_number, NOW()
  , IF(inventory_history.serial_number IS NULL, 'INSERT', 'UPDATE')
  , inventory.location
  , inventory.status
FROM inventory
LEFT JOIN inventory_history
  ON inventory.serial_number = inventory_history.serial_number
WHERE NOT (inventory.location  <=> inventory_history.location
           AND inventory.status <=> inventory_history.status);

删除:

INSERT INTO inventory_transaction (serial_number, lastupdated, ACTION)
SELECT inventory_history.serial_number, NOW(), 'DELETE'
FROM inventory_history
LEFT JOIN inventory
  ON inventory.serial_number = inventory_history.serial_number
WHERE inventory.serial_number IS NULL;

填充库存 inventory_history 表:

TRUNCATE TABLE inventory_history;
INSERT INTO inventory_history(serial_number,lastchecked, location, status)
SELECT serial_number, NOW(), location, status
FROM inventory;

要查看给定时间点的库存情况:

SELECT inventory_transaction.*
FROM (SELECT serial_number, MAX(lastupdated) as last_date
      FROM inventory_transaction
      WHERE lastupdated <= 'point in time'
      GROUP BY serial_number) AS correct_time
JOIN inventory_transaction
  ON inventory_transaction.serial_number = correct_time.serial_number
    AND inventory_transaction.lastupdated = correct_time.lastupdated
    AND inventory_transaction.ACTION <> 'DELETED'