mysql - 将温度值集合存储到 MYSQL 中的最有效方法是什么？

Question

每隔 30 分钟从动物收容所的几个动物笼中收集每小时的温度读数，并将它们转储到一个文件中。 cron 处理该数据并将其插入 MYSQL 数据库。目前，当天所有 48 个温度读数都存储在一个表中，我让它在数据进入时更新它们，或者如果不存在记录，则会创建一个新记录来存储第一个温度。

我们目前有一张笼子信息表和一张笼子温度读数表。我们的笼子总数是 45 个。我们拥有的数据量是 7 年(大约 2557 天)。温度表的记录总数为:115065

我们将向系统添加不同的位置和额外的笼子，因此笼子的总数将超过 1,000 个。我们预计数据使用量会快速增长。

是否有更有效的方式来构建下表以优化读取速度？这些数据用于生成每天早上显示的每个笼子的图表，并用 30 分钟的时间检查笼子内是否通风不足。

当前温度表如下:

CREATE TABLE `temperature_readings` (
  `CAGE_ID` int(10) NOT NULL DEFAULT '0',
  `INT_VALUE_0000` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0030` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0100` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0130` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0200` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0230` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0300` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0330` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0400` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0430` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0500` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0530` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0600` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0630` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0700` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0730` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0800` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0830` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0900` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_0930` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1000` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1030` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1100` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1130` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1200` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1230` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1300` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1330` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1400` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1430` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1500` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1530` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1600` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1630` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1700` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1730` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1800` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1830` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1900` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_1930` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_2000` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_2030` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_2100` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_2130` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_2200` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_2230` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_2300` decimal(5,2) DEFAULT NULL,
  `INT_VALUE_2330` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`CAGE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我的想法是将多个温度读数标准化为 halfhour_read 表，例如

halfhour_read{
 - cage_id
 - datetime
 - temperature reading
} 

或通过 cage_id 或今天(日期)哈希 temperature_readings，以便对其进行分区。

据我所知，第一个选项会将记录数从 115,065 条增加到 5,523,120 条，并且相比之下会快速增长，从而产生 future 的空间问题。

Answer 1

是的，标准化你的结构。只是为了好玩，尝试使用您当前的结构编写以下查询:笼子 A 上周的温度峰值是多少？

跟随你的直觉并使用这个结构:

CREATE TABLE readings (
    cage_id INT,
    dateofreading DATETIME,
    temperature DECIMAL(10,2),
    PRIMARY KEY (cage_id, dateofreading),
    INDEX (dateofreading, cage_id) -- suggested index, useful for time-based queries
)

预期行大小(仅数据):4 + 8 + 4 = 16 字节。

16 字节 x 每天 48 个读数 x 10,000 个笼子 x 365 天 = 每年 2.6 GB。如果需要，乘以 3 或 4 以提供索引。无论如何，不​​用担心存储空间。

由于适当的索引，从这个表中提取数据几乎是即时的，即使它包含数十亿条记录。无论如何，您的工作集(过去几周的数据)可能总是适合内存。

(如果您的要求是“100,000 个笼子，每天有 4,800,000 个读数”，您主要关心的不是存储空间，而是每秒处理数百万次插入)

为了将工作数据集保持在合理的大小，是的，对您的表进行分区，或者时不时地将较旧的记录移动到存档表中。