mysql - 大型数据集映射到表中

Question

我的问题有 2 个子问题:

1. 假设这样一种情况，我每秒都会收到表示为一组键/值元组的数据。每个值基本上都是一个 64 位计数器。我需要将其保存到数据库中。值的数量有数千个。在这数千个数据中，只有 1% 拥有实际数据，其他数据为空(稀疏数据集)。制作一个包含几千列的表格有意义吗？或者只是存储为“id、时间戳、键、值”？

2. 如果问题 1 的答案是“数千列”，应该使用 mysql/postgres 系列中的哪个 da？

这种情况下的读取模式主要是图表，因此 select 将是一堆基于时间戳的数据。因此，它是统一的 1/秒写入和偶尔读取所有数据或日期/时间范围内的数据。

额外的问题，可以使用什么模式在 NoSQL 数据库中存储此类数据？例如，在 MongoDB 中，可以使用包含仅占整个集合 1% 的文档的统计数据集合。在这种情况下，它如何与 read/map/reduce 一起工作？与 mysql/postgres 相比读取数据如何？

编辑:我的用例与 NewRelic 服务非常相似，但我没有大量的小数据集，而是拥有更大的数据集(在更大的数据集中稀疏填充)，但频率较低(用户也较少)

Answer 1

PostgreSQL 将空列存储为位图，但是每行的开销很大。我们来计算一下两种存储方案的存储效率:

Average row length for wide table with thousands of columns:
23 bytes row header + 1000*1bit + average 2 bytes of alignment + 4 bytes id
   + 8 bytes timestamp + 10*8 bytes values = 242 bytes

Average number of bytes for storing each value separately:
10 values * (23 bytes row header + 1 byte alignment + 4 bytes id
   + 8 bytes timestamp + 4 bytes key + 8 bytes value) = 480 bytes

因此，一千列的效率大约是按键拆分的两倍。单独存储 key 会更有效的交叉点位于about 0.45%。 .

但是，这种方法不会扩展得太远。 PostgreSQL 中的最大列数限制为 1600。要进一步扩展，您可以将值垂直拆分为许多表。这也会带来一些查询问题，因为结果集也不能大于 1600。

另一种选择是将键值对编码到数组中。在这种情况下，表的结构为(id serial、ts timestamptz、keys int2[]、values int8[])。相同 1000 个属性、1% 填充因子的存储开销为:

23 bytes row header + 1 byte alignment + 4 bytes id + 8 bytes timestamp
   + 20 bytes array header + 10*2 byte values + 20 bytes array header
   + 10*8 byte values = 176 bytes per entry

但是，在这种情况下，查询奇异值需要更多的基础设施。

如果需要更好的存储效率或灵 active ，可以添加自定义数据类型。

我知道传感器数据的大量列模式已在许多 PostgreSQL 安装中成功使用。至于数据库的选择，我可能有点偏见，但我建议使用 PostgreSQL，因为你将拥有更好的工具，如数组、谓词索引和自定义数据类型来重新安排数据存储以提高效率。要记住的最重要的事情是从一开始就使用分区。