mysql - 一张表多索引优化数据库性能

Question

我有一些关于我存储(在这个玩具示例中)的项目的时间序列数据在一对简单的表中。目前，这是在 MySQL 中完成的，但如果有足够充分的理由尝试在不同的 DBMS 中解决我的问题，我会洗耳恭听!

ITEM 表有一个主键和一个可以被认为是描述的类似文本的列，我们称它为 descr。 DATAPOINT 表有一个主键和 3 个其他列:ITEM 表中的外键(称为 fk_item)，一个日期时间 i'我们将调用 timestamp 和我们将调用的浮点值 value。此外，(fk_item, timestamp) 列对存在联合唯一性约束(对于给定时间的给定项目，我们只希望数据库中有一个值)。

用实数表示，DATAPOINT 表有大约 10 亿行，这是 1 万个不同项目中的每一个都有大约 10 万行的结果。

我的问题是关于在这种情况下优化读写性能的能力，以及实现唯一性约束的最佳方式。

从该数据库中进行的一次典型读取将涉及少量项目(六个？)，我们希望获得给定日期时间范围内的所有值(每个项目包含大约 1k 个点)。为此，拥有一个 (fk_item, timestamp) 索引并在该索引上强制执行联合唯一性标准会非常方便。这种类型的阅读背后的动机是:“我想为这 3 年的范围制作一个包含 2 或 3 个项目的折线图”。

但是，此数据库的典型写入看起来非常不同。这将是为数千个项目中的每一个插入一个数据点，所有项目都具有相同(或少量)的时间戳。这种写入的动机可以直观地认为是:“我想为每一个项目添加昨天的数据点”。因此，对于此类写入，使用 (timestamp, fk_item) 索引并对该索引强制执行唯一性限制会更实用。

重要的是，对于我的数据和硬件的规模，这些索引都不能完全放入 RAM。

通常，绝大多数写入发生在每天的短时间内:即在每天结束时，当天的所有数据都在 15 分钟内写入，然后读取发生在一天中(但通常不会在那 15 分钟内)。

因此，据我了解，如果我使用读取优化的 (fk_item, timestamp) 索引构建表(并将唯一性约束放在那里)，那么我的典型读取会很好并且迅速。但我担心我的写入速度会很慢，因为我们需要以非连续的方式更新索引。但是，如果我使用写入优化的 (timestamp, fk_item) 索引构建表(并将唯一性约束放在那里)，那么我的典型写入速度会很快，但我的典型读取会受到影响。

有什么方法可以两全其美吗？例如，如果我构建两个索引:(fk_item, timestamp) 和 (timestamp, fk_item) 并放置唯一性仅 关于两者中的后者，效果好吗？或者写入仍会以“慢”速度进行，因为即使有一个写优化索引(例如检查唯一性约束)，读优化索引也需要在任何插入时更新，并且该更新将不连续？

提前致谢!

Answer 1

简答:仅限(fk_item, timestamp)。

长答案:

就唯一性而言，(fk_item, timestamp) 和(timestamp, fk_item) 是相同的。虽然他们都同样出色地声明了独特性，但他们都对独特性感到厌恶。有一天，一个特定的项目会在同一秒内出现两次。

你确实提到了“昨天”。因此，如果条目确实是 day 的小计，则 (fk_item, date) 是合理的。

建立索引时，最好让日期/时间项最后。这样 WHERE fk_item = 123 AND date BETWEEN ... AND ... 就可以使用该索引。写入不关心(很多)事物的顺序。

PRIMARY KEY 怎么样？它是，但是 MySQL 的定义，UNIQUE 和一个 INDEX。所以，如果 (fk_item, date) 合理，就让它成为 PK。这将使需要查看特定项目的多行的查询更加高效。

“我想为这 3 年的范围制作 2 或 3 项的折线图”。 -- 如果这涉及数百万行，那么您的模式设计效率低下。您需要构建和维护一个汇总表，例如每个项目的每日值(value)。那么它将有数百行，而不是数百万行——更加可行。

回到 INSERTs。如果有 10k 个不同的项目和 PRIMARY KEY(fk_item, date)，表中将有 10K 个位置发生插入。这实际上没问题，而且速度与其他一些订单大致相同。

每日 INSERTs 最好使用 LOAD DATA INFILE 或多行 INSERTs 完成。

我是从 MySQL 的角度讲的。我所说的一些(但可能不是全部)适用于其他产品。

PARTITIONing 对 MySQL 来说是一个无用的想法，除非你打算清除“旧”数据。 (我不能代表 Posgres。)

如果您随机插入行，您可能会遇到不切实际的性能问题。这是因为您的真实情况将不那么“随机”。今天只有 10,000 个点可以用来INSERTs，而不是 10 亿个。明天，它将是“相同的”10K 点。

“应该如何构建这样的表”——尽量减少数据类型(例如，不要使用 8 字节的 BIGINT 作为是/否标志)；提供最佳 PK(我建议 (item, day))。但是您必须有试探性的SELECTs 才能确定二级索引。在适当的地方规范化(item_id)，但不要过度规范化(日期)。