一张日志表 vs 每个表都有日志列

Question

我会将插入和更新信息记录到每个表中

create_date    TIMESTAMP
create_user_id INT
update_date    TIMESTAMP
update_user_id INT

我认为不需要在每张表格上都放置日志，只需创建一个日志表，并在每个表格上引用该日志表，这样我只需要在需要时检索日志信息。我知道这取决于应用程序（我正在开发一个类似商业ERP的小型应用程序），但您对此类型的表格有何经验？是否存在性能、维护问题？您更喜欢哪种方法？谢谢。

log_id         LONG
create_date    TIMESTAMP
create_user_id INT
update_date    TIMESTAMP
update_user_id INT

编辑：我得出了一个解决方案，只使用每个表上的update_date（插入将被视为更新，不会删除数据，只是停用）和update_user_id列。我使用MySQL，在生产环境中将有主服务器和从服务器。如果发生任何情况需要追溯数据，我将在从服务器上使用复制日志来审计数据更改，因此设计更简单，审计虽然不容易，但仍然可行。

- Gok Demir

Answer 1

单个日志表如果除其他写入操作外还有写入，就很容易成为瓶颈。有些查询语句可能需要进行额外的 JOIN 操作。 在我看来，分开单独的表并没有什么好处，只不过是为了让数据库中的其他表更加“干净”而已。

- Fiona - myaccessible.website

---

使用单独的表，您可以记录每个交易，而不仅仅是第一个和最后一个。但我看到这个问题并没有建议这样做，这让我感到好奇。 - recursive

递归：历史数据不会经常使用，比较每周备份可以记录每笔交易的某种特征，尽管这更困难。 - Gok Demir

Answer 2

单个日志是一个非常好的东西。

在每个表上，都有一个ID列专门用于记录目的。将其称为LOG_ID或其他名称。

无论何时进行INSERT、UPDATE或DELETE操作，都应按照以下方式进行：

1. 插入日志条目，获取分配的LOG_ID。

2. 执行INSERT或UPDATE操作，并在更改的行上设置LOG_ID外键。对于DELETE操作，您有两个选择：实际删除该行或将该行标记为“不活动”，但不要实际删除它。这第二个选择使您所有更改的日志完全完整，但由于必须跳过不活动行，因此使您的表变得非常大且缓慢。

3. 提交。

确保您的日志设计可以包括以下类型的信息：

1. 数据库行更改（插入、更新、删除）。插入和更新更改将在某个地方具有对已更改行的FK引用。请务必包括表名，以便应用程序可以正确定位表格。删除更改仅将具有表名。

2. 其他处理信息，如批处理作业运行。这样，您就可以记录批处理作业的启动/停止和运行时间，并保留完整的处理历史记录。

- S.Lott

---

如果用户更新了多列，您该如何处理呢？您是将每个已更新的列记录到不同的行中吗？ - Gok Demir

原始设计没有记录列更改。为什么要将其添加到此设计中？记录完全相同的信息：更改日期和用户名。 - S.Lott

是的，原始设计并没有这样做。实际上，对于单个日志表，我的计划是仅保留每行的一条记录。如果用户更新任何行，则其具有指向单个日志表单行的log_id，并且相关行将使用时间戳和用户ID进行更新。当您提到包括表名称修改类型时，我猜测您会将值与它们一起存储。我的建议设计不包括任何此类内容，只包括我上面展示的模式。无论如何，感谢您的回复。 - Gok Demir

@Gok Demir：不要更新日志。插入。对于给定的表，您可以找到所有修改。对于给定的行，您也可以找到修改历史记录。 - S.Lott

Answer 3

我们通常在大多数表格上使用这些：

LastChgID      int
LastChgDate    datetime

有时会在一些情况下使用这个：

CreateID       int
CreateDate     datetime
LastChgID      int
LastChgDate    datetime

在其他地方，我们有一张完整的镜像表，记录每一列的更改类型标志、日期时间和用户 ID。

我会避免更新频繁的表，只需添加列即可。这就是数据库的作用，存储信息。我们曾经有一张表，每次调用存储过程都会增加（通过更新），结果成为了死锁的诱因。

我们确实有一个通用日志表，但它只是插入记录，包含调试和错误信息。它并不会在每次更改行时更新，只有当开发人员决定在那里写入内容时才会更新，主要包括几个头字段（标识符、日期时间、用户 ID、从哪个存储过程调用）和一个长格式字符串，其中包含消息。

- KM.

---

死锁磁铁。是的，这是需要考虑的问题。 - Gok Demir

Answer 4

大约20年前，我学到了处理这种信息的最佳方法是仅通过向数据库插入数据来完成。您不应修改现有记录，绝对不要删除任何记录。基本上，您存储的不是数据本身，而是对数据的修改。因此，如果您拥有足够的CPU /磁盘速度以根据这些插入计算数据，则最终只会得到一个可以完成所有所需功能的单个表格。

仅存储修改将使您能够保留所有数据的完整历史记录，从而使您变得非常灵活。基本上，您只需要一个包含所有内容的表格。缺点是您需要进行更多的计算并需要更多的磁盘读取，因此您需要使用技巧来加快进程。灵活性的代价是性能...

在这种情况下，您最终会遇到类似的问题。任何需要添加或更新记录的内容都将访问单个表格。它将成为您的瓶颈。在单用户环境中仍然很好，因为只有一个用户会使用它。在低用户环境中，它仍然可以具有非常好的性能。但是，如果有250多个用户一直访问此表格，它将降低整体性能。

此外，当有人删除记录时，您还会引入维护问题。在这种情况下，此日志表中的记录也需要被删除。

这让我想起了我一开始提到的事情...您可以将历史记录表和常规表合并。每当插入、修改或删除记录时，您都会向此修改表中添加一条记录，包括时间戳和用户参考。这将保持数据的历史记录，并且通常情况下，您只需要向此表中插入记录。这应该仍然很快。作为额外的奖励，您可以通过重新播放此修改表中的所有操作来重新创建整个数据库的内容，以防万一。

插入速度可以相当快，因此性能损失相对较小，尽管这取决于您的实现方式。

但出于任何原因，在学习使用修改表之后，我从未遇到过可以直接使用此技术的实际情况。

- Wim ten Brink