mysql - 如何优化 MySQL 的变更历史数据

Question

此数据存储在前一个表中接近 3-4gb，但数据在存储之前/之后没有压缩。我不是 DBA，所以我对好的策略有点不知所措。

该表用于记录对我的应用程序(用户配置文件)中特定模型的更改，但有一个棘手的要求:我们应该能够在任何给定日期获取配置文件的状态。

数据(单表):

id, username, email, first_name, last_name, website, avatar_url, address, city, zip, phone

唯一的两个要求:

1. 能够获取给定模型的更改列表
2. 能够在给定日期获取模型状态

以前，所有配置文件数据 都是针对单个更改 存储的，即使只有一列发生更改也是如此。但是获取特定日期的“快照”非常容易。

我在优化数据结构方面的前几个解决方案:

(1) 只存储改变的列。这将大大减少存储的数据，但会使获取数据快照变得非常复杂。我必须合并到给定日期(可能是数千个)的所有更改，然后将其应用于模型。但是该模型不能是新模型(仅存储更改的数据)。为此，我必须首先从当前 profiles 表中复制所有数据，然后将更改应用于这些基本模型以获取快照。

(2) 存储整个数据，但转换为压缩格式，如 gzip 或二进制或诸如此类的东西。这将消除查询数据而不是获取更改的能力。例如，我无法获取所有更改 where email = ''。我基本上只有一个包含转换后数据的列，用于存储整个配置文件。

然后，我想使用相关的 MySQL 表选项，如 ARCHIVE 来进一步减少空间。

所以我的问题是，您认为是否有任何其他选项比上述 1/2 方法更好，如果没有，哪个更好？

Answer 1

首先，我完全不担心 3GB 的表(除非它在很短的时间内增长到这个大小)。 MySQL 可以接受它。空间应该不是问题，请记住，500 GB 的硬盘大约需要 4 个工时(在我的国家/地区)。

也就是说，为了降低您的存储需求，为您要监控的表的每个字段创建一个表。假设一个像这样的 profile 表:

CREATE TABLE profile (
    profile_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50) -- and so on
);

...创建两个历史表:

CREATE TABLE profile_history_username (
    profile_id INT NOT NULL,
    username VARCHAR(50) NOT NULL, -- same type as profile.username
    changedAt DATETIME NOT NULL,
    PRIMARY KEY (profile_id, changedAt),
    CONSTRAINT profile_id_username_fk
        FOREIGN KEY profile_id_fkx (profile_id)
        REFERENCES profile(profile_id)
);

CREATE TABLE profile_history_email (
    profile_id INT NOT NULL,
    email VARCHAR(50) NOT NULL, -- same type as profile.email
    changedAt DATETIME NOT NULL,
    PRIMARY KEY (profile_id, changedAt),
    CONSTRAINT profile_id_fk
        FOREIGN KEY profile_id_email_fkx (profile_id)
        REFERENCES profile(profile_id)
);

每次您更改profile 中的一个或多个字段时，将更改记录在每个相关的历史表中:

START TRANSACTION;

-- lock all tables
SELECT @now := NOW()
FROM profile
JOIN profile_history_email USING (profile_id)
WHERE profile_id = [a profile_id]
FOR UPDATE;

-- update main table, log change
UPDATE profile SET email = [new email] WHERE profile_id = [a profile_id];
INSERT INTO profile_history_email VALUES ([a profile_id], [new email], @now);

COMMIT;

您可能还想在 profile 上设置适当的 AFTER 触发器，以便自动填充历史表。

检索历史信息应该很简单。要在给定时间点获取配置文件的状态，请使用此查询:

SELECT
    (
        SELECT username FROM profile_history_username
        WHERE profile_id = [a profile_id] AND changedAt = (
            SELECT MAX(changedAt) FROM profile_history_username
            WHERE profile_id = [a profile_id] AND changedAt <= [snapshot date]
        )
    ) AS username,

    (
        SELECT email FROM profile_history_email
        WHERE profile_id = [a profile_id] AND changedAt = (
            SELECT MAX(changedAt) FROM profile_history_email
            WHERE profile_id = [a profile_id] AND changedAt <= [snapshot date]
        )
    ) AS email;