mysql sensor表_mysql – 数据库重新设计机会：用于此传感器数据收集的表设计是什么？...

背景

我有一个大约2000个传感器的网络,每个传感器有大约100个数据点,我们每隔10分钟收集一次.这些数据点通常是int值,但有些是字符串和浮点数.这些数据应存储90天,如果可能,应保存更多并且仍然有效.

数据库设计

当我最初负责这个项目时,我写了一个C#应用程序,为每个传感器编写逗号分隔文件.当时没有那么多,当有人想看趋势时,我们会在Excel中打开csv并根据需要绘制图形.

事情增长了,我们切换到MySQL数据库.我为每个传感器创建了一个表(是的,我知道,很多表！);它一直运作良好,但它有一些局限性.有这么多表,显然不可能编写一个查询,在查找特定值时会在所有传感器中查找数据.

对于下一个版本,我切换到Microsoft SQL Server Express,并将所有传感器数据放入一个大表中.这也有效,并且让我们进行查询以在所有感兴趣的传感器中查找值.但是,我遇到了Express版本的10GB限制,并决定切换回MySQL而不是投资SQL Server Standard.

问题

我对MySQL性能和可伸缩性感到满意,但我不确定是否最好坚持采用全数据一卡式方法.单个表中的10GB似乎要求采用不同的设计.我应该提一下,查询图形数据的需求仍然存在,我担心查询会出现性能问题,例如,整个90天内一个传感器的温度数据. (换句话说,图形应该是快速生成的东西,而不是等待SQL对数据进行分类以隔离感兴趣的传感器.)

我应该以某种方式拆分此表以提高性能吗？或者拥有如此大的桌子并不罕见？

我在Sensor ID和Timestamp列上有索引,这几乎是任何查询的定义边界. (即从时间A到时间B获得传感器X的数据).

我已经阅读了一些关于分片和分区的内容,但在这种情况下感觉不合适.

编辑：

根据目前为止的评论和答案,一些其他信息可能会有所帮助：

不是无限期存储：目前我不存储过去90天的数据.每天,我运行一个查询,删除超过90天的数据.如果它在未来变得重要,我会存储更多,但现在就足够了.这有助于保持尺寸检查和性能高(呃).

引擎类型：使用MyISAM的原始MySQL实现.这次为新实现(一个数据表而不是多个数据表)创建表时,它们默认为InnoDB.我不相信我对其中一个要求.

规范化：除了数据收集表之外,当然还有其他表.这些支持表存储诸如传感器的网络信息,用户的登录信息等内容.没有太多规范化(据我所知).数据表包含这么多列的原因是每个传感器都有很多变量. (多个温度,亮度,气压等)对我的归一化意味着没有冗余数据或重复组. (至少对于1NF.)对于给定的传感器,在特定时间存储所有值需要一行数据,并且那里没有涉及1：N关系(我看到).

我可以在功能上拆分表格,例如在一个表格中制作(例如)所有与温度相关的值,在另一个表格中制作所有与气压相关的值.虽然这可能会提高制作仅温度查询的人的效率,但我仍然需要一次插入所有数据.但是,效率增益对于SELECT操作来说可能是值得的.显然,我最好根据用户请求数据的频率垂直拆分表格.也许这就是我应该做的.我想在问我的问题时,我正在寻找确认,这样做是值得的.

编辑2：

数据使用：最终,大部分数据从未被查看或需要,因为我们通常只关注有问题的项目.但在尝试发现问题时,我们使用各种工具来搜索数据并确定要放大的项目.

例如,我们注意到内存使用价值(客户特定的专有软件程序)与重启/崩溃之间存在关联.我收集的一个数据点与此内存使用情况有关,我能够查看历史数据,以显示在超过特定内存使用量后设备变得不稳定.今天,对于运行该软件的设备子集,我检查此值并发出重启命令(如果它太高).在发现之前,我并不认为收集这些数据是有价值的.

出于这个原因,我坚持认为,即使值有问题,也要收集和存储大约100个数据点.但在正常的日常使用中,用户通常会检查这些参数中的十几个.如果用户对特定地理区域感兴趣,他可以(使用软件)为可能的几十个传感器生成图表或数据电子表格.通过两条或三条曲线显示温度,气压和光照水平等30天图表并不罕见.执行此操作将运行类似于此的查询：

SELECT sensor_id, location, data_timestamp, temp1, air1, light1

FROM data

WHERE data_timestamp >= '2012-02-01'

AND sensor_id IN (1, 2, 3);

(在最初的MySQL版本中,每个传感器都有自己的表,将发出三个单独的查询,但结果在软件中组合以创建图形.)

因为数据表包含很多行(~1000万),尽管id和data_timestamp上有索引,但性能明显比多表情况差(在9秒内返回4500行,而不是这个例子不到一秒) ).在多表模式中找到哪些传感器满足特定标准的能力实际上为零,因此是移动到单个表的原因.

这种类型的查询可以由多个用户快速连续完成,因为他们选择不同的数据组并比较每个结果的图表.每个图表或电子表格等待近10秒可能会非常令人沮丧.

90天后丢弃数据.它可以存档但目前不是必需的.

希望此信息有助于更充分地显示收集和存储后数据的使用方式.

解决方法:

你应该考虑分区表有一个很大的原因.

您在巨型表上拥有的所有索引,甚至只有一个索引,都可以生成大量CPU负载和磁盘I / O,以便在执行INSERT,UPDATE和DELETE时执行索引维护.

我写了一篇关于为什么Table Partitioning会有很大帮助的earlier post back on October 7, 2011.以下是我过去发表的一篇摘录：

Partitioning of data should serve to group data that are logically and

cohesively in the same class. Performance of searching each partition

need not be the main consideration as long as the data is correctly

grouped. Once you have achieved the logical partitioning, then

concentrate on search time. If you are just separating data by id

only, it is possible that many rows of data may never be accessed for

reads or writes. Now, that should be a major consideration: Locate all

ids most frequently accessed and partition by that. All less

frequently accessed ids should reside in one big archive table that is

still accessible by index lookup for that ‘once in a blue moon’ query.

要切入正题,您需要研究并找出10GB表中很少使用的数据.如果您需要针对历史性质的特殊查询,那么该数据应放在易于访问的存档表中.从10GB迁移该归档,然后在10GB表上迁移OPTIMIZE TABLE,可以使工作集更快地运行SELECT,INSERT,UPDATE和DELETE.甚至DDL在2GB工作集上也会比10GB表更快.

更新2012-02-24 16:19美国东部时间

要考虑两点

>从您的评论中,听起来像您可能需要的规范化.

>您可能需要将超过90天的所有内容迁移到存档表中,但仍然可以同时访问存档和工作集.如果您的数据都是MyISAM,我建议使用MERGE存储引擎.首先,创建一个MERGE表映射,它将工作集MyISAM表和存档MyISAM表结合在一起.您可以在一个MyISAM表中保留少于91天的数据,并将90天以前的任何数据翻转到存档中.您只能查询MERGE表映射.

以下是我如何使用它的两篇文章：

这是我在有很多列的表格上发表的另一篇文章

标签：mysql,database-design

来源： https://codeday.me/bug/20190805/1588272.html