mysql - 如何重新设计每行包含多个值的数据库

Question

我正在构建一个数据库(mysql)来收集来自各个客户端的数据。我得到的数据集将是一个时间戳(日期时间)和 600 个值( float 字或 bool 变量)。

每个客户端每 5 分钟生成一个数据集。

收集所有这些数据的目的是为了稍后进行分析，并按日期时间和客户端进行过滤。

我的第一个想法是创建一个包含很多列的表，如下所示:

id 时间戳 client_id val_1 val_2 ... val_600

地点:

• 'id'是一个自动递增整数主键列
• “timestamp”是日期时间列
• 'client_id' 是一个整数列，引用另一个表中的客户端
• “val_n”是文本列(为了保持灵 active )，因为并非每个客户端都提供相同的数据集(有些只有 float ，有些可能有 200 或 300 个，有些只有 bool 值或任意数量)。

数据集的结构在另一个表中定义，也可以通过“client_id”引用。

我的数据库及其表格就像所描述的范式一样，并且可以按我的意愿工作。但是当我读了很多关于数据库设计的内容时，我看到了很多针对那些要求制作一个包含超过 20 列的表的人的激烈争论(主要是在这里)。看来这样做可能会出现很多问题。

但我现在不知道如何做不同的事情，我唯一能想到的是:

| id | 时间戳 | client_id | float_data | boolean_data |

地点:'float_data' 和 'boolean_data' 都是一个文本字段，这两个字段内将是一个序列化字典，例如:{"1": 23.4, "2": 87.2...}。

在这种情况下，我显然不会使用列数。这样做的缺点是，如果我想从客户端知道值 46 并且需要 10 天的一组数据(将是 2880 行)，我必须反序列化每个字典并根据其选择正确的值关键。

这对我来说似乎不太可行。

问题是我对数据库还很陌生，我担心这个表设计有一天会成为问题。

有人知道比我的两种方法更好的解决方案吗？或者我是否担心太多并且应该像这样运行它？

Answer 1

The downside of this is, if i want to know Value 46 from a Client and need the a Set of this Data for 10 Days (would be 2880 Rows), i had to deserialize every of this dicts and select the right Value according to it's Key. This seems not really Practicable to me.

如果 mysql 可以为您完成这项工作怎么办？最新版本的mysql支持json数据类型https://dev.mysql.com/doc/refman/8.0/en/json.html ，根据我的经验，这非常适合您需要灵活处理传入数据的情况。

https://dev.mysql.com/doc/refman/8.0/en/json.html

它主要解决了缺点，因为您可以像查询列一样查询 json 数据 - 不像真正规范化的表那么快，后者非常高效，但仍然可能比挖掘n 列来查找适当的数据(这通常非常慢，既因为格式无法满足数据库用于有效处理数据的技术，又因为每行有大量数据无法进行有意义的索引)。

您仍然希望标准化所有可能的数据。诸如客户端 ID、接收日期等内容可能需要标准化。其余的可以作为 json 对象发送并按原样存储在数据库中，根据用例需要在运行时相对有效地查询。