提问人：root66 提问时间：8/14/2023 最后编辑：root66 更新时间：8/14/2023 访问量：47
MySQL：如何加速归档表
MySQL: How to speed up archive table
问：
我有一个存档表，其中仅存储了对记录的更改以及有效期。例如，产品的价格存档。
如果您现在想选择给定日期的状态，我有以下 SELECT。 问题在于，更改越多，速度就越慢，因为在第一步中，数据库首先读取所有记录，然后在下一步中附加那些具有更大有效期的记录。最后，只有有效期最长的那些留在最左边。
有什么方法可以使它更有效吗？
SELECT d1.*
FROM data d1
LEFT JOIN data d2
ON d1.id = d2.id AND
d1.valid_from_date < d2.valid_from_date
WHERE
d2.id IS NULL AND
d1.valid_from_date <= '2023-01-01';
CREATE TABLE data (
id int,
valid_from_date DATE,
foo varchar(255),
bar varchar(255),
PRIMARY KEY (id, valid_from_date)
);
答：
1赞
O. Jones
8/14/2023
#1
尝试在 date 和 id 列上添加此索引。
CREATE INDEX x_valid_from_date_id ON data(valid_from_date, id);
编辑根据您的评论，您已经有一个索引（您的 PK）。(id, valid_from_date)
编辑如果要查找每个日期在特定日期或之前的最新行，此查询可能会有所帮助。id
SELECT a.*
FROM data a
JOIN (
SELECT id, MAX(valid_from_date) valid_from_date
FROM data
WHERE valid_from_date <= '2023-01-01'
GROUP BY id
) b ON a.id = b.id AND a.valid_from_date = b.valid_from_date
子查询获取每个 ID 所需的日期，然后 JOIN 检索详细信息。MariaDB / MySQL查询计划器对MAX非常聪明...GROUP BY 查询给定适当的索引。
阅读马库斯·温南德（Markus Winand）的优秀电子书，了解有关此主题的更多信息。
评论
0赞
root66
8/14/2023
我试过这个，在 50 条测试记录上，它慢了 0.0009 倍（0.047 秒对 40,000 秒）。当我使用 EXPLAIN 查看执行时，整个表运行了两次。和我的，只有一次。但我还不知道当桌子变大时它的表现如何。如果访问时间保持稳定，那么这将是有益的。
0赞
root66
8/14/2023
我不明白为什么这么慢。使用 GROUP BY 的子选择只需 0.0005 秒，即可完成大部分工作。EXPLAIN也只向我显示一种SIMPLE选择类型。但是，如果我随后执行完整的语句，它就变成了三个步骤，第一个步骤说“<derived2>”作为表，possible_keys = NULL，extra 是“where”。
1赞
Slava Rozhnev
8/14/2023
#2
您可以尝试使用代替和条件，例如：NOT EXISTSLEFT JOINNOT NULL
SELECT d1.*
FROM data d1
WHERE
d1.valid_from_date <= '2023-01-01' AND
NOT EXISTS (
SELECT 1
FROM data d2
WHERE d1.id = d2.id AND
d1.valid_from_date < d2.valid_from_date
);
评论
0赞
O. Jones
8/14/2023
'安安'安安。原来的提问者正在执行反联接操作。切换到普通的内在并不完全正确。LEFT JOIN ... IS NULLJOIN
0赞
Slava Rozhnev
8/14/2023
对不起，琼斯@O，我的错。编辑
0赞
root66
8/14/2023
@SlavaRozhnev谢谢！我试了一下，让桌子长大。虽然更改的条目越来越多，但当前数据的总数几乎保持不变。表中大约有 57,000 个条目，三个 SELECT 返回了大约 11,000 个条目。您的查询耗时了 0.0011 秒。在我最初的 SELECT 中，它是 0.0010 秒，在 O.Jones 的另一个示例中，时间显着增加到 0.089 秒。难道“不存在”的优势后来才显现出来吗？
评论