高效存储7.3亿行数据

Question

您如何解决以下存储和检索问题？

每天将添加大约2,000,000个行（每年365天），每行包含以下信息：

- id（唯一的行标识符） - entity_id（取值范围为1至2,000,000，包括1和2,000,000） - date_id（每天递增，取值范围为1至3,650（十年：1 * 365 * 10）） - value_1（取值范围为1至1,000,000，包括1和1,000,000） - value_2（取值范围为1至1,000,000，包括1和1,000,000）

entity_id与date_id相结合是唯一的。因此，最多可以向表中添加一个与实体和日期对应的行。数据库必须能够容纳10年的每日数据（7,300,000,000行（3,650*2,000,000））。

上述是写入模式。读取模式很简单：所有查询都将针对特定的entity_id进行。即检索描述entity_id = 12345的所有行。

不需要事务支持，但存储解决方案必须是开源的。理想情况下，我希望使用MySQL，但我也愿意听取建议。

现在 - 您将如何处理所描述的问题？

更新：要求详细说明读写模式。将以每天的一个批次进行表格写入，其中新的2M条目将一次性添加。读取将连续进行，每秒钟进行一次读取。

- knorv

---

十年很可能不是精确的3650天，因为有闰年的存在。 - unwind

你将如何访问它？从 PHP 通过网络？还是从本地机器上的 C++...？ - Jimmy J

@unwind：说得好。要记住的正确天数是4年里的1461天，这更接近正确答案。虽然还不是完全准确，但误差在处理100年数据之前并不重要。 - S.Lott

@knorv：那个是指每秒总共读取一次还是每秒每个实体读取一次？ - vartec

Answer 1

"现在 - 你如何解决这个问题？"

使用简单的平面文件。

原因如下：

"所有查询都将基于特定的entity_id进行。即，检索描述entity_id = 12345的所有行。"

您有200万个实体。根据实体编号进行分区：

level1= entity/10000
level2= (entity/100)%100
level3= entity%100

数据中的每个文件都是 level1/level2/level3/batch_of_data

然后，您可以读取目录中给定部分的所有文件以返回要处理的样本。

如果有人想要关系型数据库，则加载给定实体 ID 的文件到数据库供他们使用。

---

编辑 关于日期编号。

1. date_id/entity_id 唯一性规则不需要处理。它（a）可以轻松地强制执行文件名，并且（b）与查询无关。

2. date_id 的“翻转”没有任何意义--没有查询，因此没有必要重新命名任何内容。 date_id 应该从时期日期开始无限增长。如果要清除旧数据，请删除旧文件。

由于没有查询依赖于 date_id，因此永远不需要对其进行任何操作。它可以成为文件名，因为它不重要。

要将 date_id 包括在结果集中，请将其与文件中的其他四个属性一起写入文件。

---

编辑 关于打开/关闭

对于写入，您必须保留文件处于打开状态。 进行周期性刷新（或关闭/重新打开）以确保内容确实写入磁盘。

设置写入器的架构有两个选择：

1. 有一个单一的“写入器”进程，它从各种来源中合并数据。如果查询相对频繁，则这很有帮助。您需在写入时支付合并数据的代价。

2. 同时打开多个文件进行写入。当查询时，将这些文件合并为单个结果。如果查询相对较少，则这很有帮助。您需在查询时支付合并数据的代价。

- S.Lott

---

@S.Lott 这里有额外的价值，您可以获得一个唯一索引，用于检查 data_id 是否真正唯一（数据一致性）。由于 SQLite 缓存 SQL 语句，因此 SQL 并不会带来太大的开销。 - tuinstoel

@S.Lott 我不知道分区的MySQL表是否会变慢？我从未尝试过，但为什么会变慢呢？MySQL只需要读取一个分区。如果您想进行一些不同的报告，可以这样做，因为所有数据都在一个表中。这更加灵活。 - tuinstoel

@S.Lott，不明白您希望如何使用文件名来确保entity_id和data_id的组合唯一性？ - tuinstoel

那不是意味着最终你会有七十三亿个文件吗？这可是相当多的。 - tuinstoel

"entity_id和date_id的组合是唯一的" -- 这是提供的唯一数据。我不确定是否需要采取任何措施来确保这一点。我认为这是从存储模式之外强制执行的。 - S.Lott

Answer 2

使用分区技术。根据读取模式，您需要按entity_id哈希值进行分区。

- vartec

---

我认为这是最好的解决方案。例如，它使得可以在不打开2000个文件的情况下计算记录数量，或者像select count(*) from table where value_1 =100这样的操作。您的数据变得更加生动。 - tuinstoel

@S.Lott：为什么不呢？它将使用对数访问成本的索引。 - vartec

@tuinstoel: 那不是一个使用案例。使用案例是写入数据并按特定实体ID获取数据。为非使用案例进行优化没有帮助，对吧？ - S.Lott

@S.Lott。这不是关于优化，而是在早期阶段让某些事情变得不可能。数据本身就具有价值。 - tuinstoel

@usr：你不认为将数据均匀分布在多个服务器上有好处吗？ - vartec

这与分片有什么关系？ - tonix

Answer 3

你可能想看看这些问题：

大型主键：10亿行MySQL + InnoDB？

大型MySQL表

个人建议，还要考虑计算行宽，以便了解表的大小（根据第一个链接中的分区说明）。

HTH.,

S

- Simon

Answer 4

你的应用程序似乎具有与我的相同的特征。我编写了一个MySQL自定义存储引擎来高效地解决这个问题，可以在这里找到相关描述。

想象一下，你的数据在磁盘上按照2M固定长度条目的数组布局（每个实体一个）排列，每个条目包含3650行（每天一个）20字节（每个实体每天一行）。

你的读取模式读取一个实体。它在磁盘上是连续的，因此需要1次寻道（大约8毫秒），并且以大约100MB /秒的速度读取3650x20=大约80K…因此可以在几秒钟内完成，轻松满足你每秒1个查询的读取模式。

更新必须在磁盘上的2M个不同位置中写入20字节。在最简单的情况下，这将需要2M次寻道，每次寻道需要大约8毫秒，因此需要2M * 8ms = 4.5小时。如果你将数据分散到4个“raid0”磁盘中，则可能需要1.125小时。

但是这些位置之间只有80K的距离。这意味着16MB块（典型磁盘缓存大小）中有200个这样的位置，因此它可以以高达200倍的速度运行。（1分钟）实际情况介于两者之间。

我的存储引擎基于这种哲学运作，尽管它比固定长度数组更通用。

你可以编写我所描述的代码。将代码放入MySQL可插拔存储引擎中意味着你可以使用MySQL查询数据，并使用各种报表生成器等工具。

顺便说一下，你可以从存储的行中删除日期和实体ID（因为它们是数组索引），如果你不需要唯一ID，则可以删除它-因为（实体ID，日期）是唯一的，并将2个值存储为3字节整数。然后你的存储行是6个字节，每16M有700次更新，因此插入更快，文件更小。 编辑与平面文件比较

我注意到评论普遍支持平面文件。不要忘记，目录只是由文件系统实现的索引，它们通常针对相对较少的相对较大的项目进行优化。访问文件通常会进行优化，因此它期望打开相对较少的文件，并且对于每个打开的文件都具有相对较高的开销。所有这些“相对”都是相对于数据库的典型用法而言的。

使用文件系统名称作为实体ID的索引是违反直觉的。在编程中，例如，你将使用数组而不是哈希表，并且你不可避免地会遭受昂贵访问路径的大量开销，它可以简单地是一个数组索引操作。

因此，如果您使用平面文件，为什么不只使用一个平面文件并对其进行索引呢？

关于性能的编辑 该应用程序的性能将受到磁盘搜索时间的支配。我上面做的计算确定了您可以做到的最好水平（尽管您可以通过减慢SELECT来加快INSERT - 您不能使它们都更好）。无论您是使用数据库、平面文件还是一个平面文件，除非您可以添加更多不需要的查找并进一步减慢速度。例如，索引（无论是文件系统索引还是数据库索引）与“数组查找”相比会导致额外的I/O，并且这些操作会减慢您的速度。

有关基准测试测量的编辑 我有一个表格看起来与您的非常相似（或几乎完全类似于您的某个分区）。它有64K实体而不是2M（是您的1/32），以及2788个“天”。该表格按照与您的相同的INSERT顺序创建，并具有相同的索引（entity_id，day）。选择单个实体需要20.3秒来检查2788天，预期大约需要130个寻道每秒（在平均寻道时间为8毫秒的磁盘上）。SELECT时间将与天数成比例，而与实体数量无关（在寻道时间更快的磁盘上速度会更快。我正在使用一对SATA2 RAID0，但这并没有太大的区别）。

如果您将表格重新排序为实体顺序 ALTER TABLE x ORDER BY (ENTITY,DAY) 那么同样的SELECT需要198毫秒（因为它是在单个磁盘访问中读取实体顺序）。 但是ALTER TABLE操作花费了13.98天才能完成（用于182M行）。

测量还告诉您其他几件事情： 1. 您的索引文件将与数据文件一样大。对于此示例表，它为3GB。这意味着（在我的系统上）所有索引都以磁盘速度而非内存速度运行。

2. 您的INSERT速率将对数下降。数据文件的插入是线性的，但键的插入是对数的。在180M条记录时，我每秒得到153个INSERT，这也非常接近寻道速率。它显示MySQL几乎为每个INSERT更新一个叶索引块（因为它是按实体索引但按天顺序插入的）。因此，您要花费2M / 153秒= 3.6小时来完成每天的2M行插入。（除以您可以通过系统或磁盘分区获得的任何效果）。

- Dave Pullin

Answer 5

我有一个类似的问题（尽管规模更大——每天使用量约为您的年度使用量）。使用一个大表让我停滞不前——你可以拉出几个月，但我猜你最终会对它进行分区。不要忘记给表建立索引，否则每次查询都会处理微小的数据流；哦，如果你想进行大规模查询，使用平面文件。其中 similar problem 和 use flat files是链接。

- Dani

Answer 6

你对读取模式的描述不够充分。你需要描述将检索多少数据，多久一次以及查询中会有多少偏差。

这将使你考虑在某些列上进行压缩。

还要考虑归档和分区。

- Jonathan Parker

Answer 7

如果你想处理数百万行的大量数据，可以考虑使用类似于时间序列数据库的方式记录时间并将数据保存到数据库中。存储数据的一些方法包括使用InfluxDB和MongoDB。

- Arun Raja