如何在mysql DB中有效地存储和搜索每分钟的数据？

Question

我正在为200个电能表每分钟记录一次。每个功率计都有其独特性(Pmid)。模式类似于：

CREATE TABLE `pmd` (
  `datatime` datetime NOT NULL,
  `pmid` smallint(5) unsigned NOT NULL,
  `statusid` tinyint(3) unsigned NOT NULL,
  `I1` double NOT NULL,
  `I2` double NOT NULL,
  `I3` double NOT NULL,
  `I0` double NOT NULL,
  PRIMARY KEY (`datatime`,`pmid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我的用例是检索特定功率计的每小时(其中minute=0)、日(其中hour&minute=0)和每月记录(其中day =1 & hour&minute=0)。

在前两个月中，查询工作迅速。然而，记录越多，查询时间就变得非常缓慢。

我想征求意见，如何提高业绩？在我的脑海中有一些想法:1.将日期时间改为分隔字段，例如：

`year_2digit` tinyint NOT NULL,
`month` tinyint NOT NULL,
`day` tinyint NOT NULL,
`hour` tinyint NOT NULL,
`minute` tinyint NOT NULL,

2.为每个月创建新的表格。更新:今天我在网上读到了更多，有一种叫做分区的技术。我对它感兴趣，因为它不改变架构。我想按年份和月份划分。我也可以知道你对分区的看法吗？

Answer 1

您的第一个想法是，我可能也会做一些小的例外：

而不是

`year_2digit` tinyint

我会用

`year` year

年份数据类型的存储大小与TINYINT (1字节)相同。

保留datatime列。您可能需要它进行其他查询。例如，在具有多列的MySQL中，有效的范围条件(如介于两者之间)是一个噩梦。

最新的MySQL和MariaDB版本支持生成(虚拟)列。您可以使用该特性自动从datetime列生成值。如果您的版本不支持它，我将使用触发器代替。

在(minute, hour, day, month, year)上定义一个复合索引。它将支持以下所有条件：

WHERE `minute` = 0
WHERE `minute` = 0 AND `hour` = 0
WHERE `minute` = 0 AND `hour` = 0 and `day` = 1
WHERE `minute` = 0 AND `hour` = 0 and `day` = 1 AND `month` = 1
WHERE `minute` = 0 AND `hour` = 0 and `day` = 1 AND `month` = 1
  AND `year` BETWEEN `2010` AND `2020`

Answer 2

我的用例是每小时(其中是minute=0)、每日(其中是hour&minute=0)和每月记录(其中day =1 & hour&minute=0)检索特定电量计的。

第1部分--获得正确的PK

由于您正在寻找一个pmid，请按该顺序使用PRIMARY KEY(pmid, datetime)。并使用InnoDB，使PK与数据“集群”在一起。

这样，所需的行不是分散在整个表上，而是聚集在一起。至少在一分钟内。

您的查询必须是以下形式：

WHERE pmid = <constant>
  AND `datetime` >= '2016-07-11'
  AND `datetime`  < '2016-07-11' + INTERVAL 3 DAY
  AND MINUTE(`datetime`) = 0

也就是说，指定特定的pmid并提供一个日期时间范围--这将使扫描集中在表的有限部分。然后进行每小时/每小时的过滤。

第2部分-简表

上面的技术在很短的时间范围内对“一分钟一分钟”的效果很好。在很长的一段时间内，它的工作时间非常长。这不能通过索引来修正。

嗯，“汇总表”这个词在这里不太合适，但无论如何.再做两张桌子，一张是高峰时段，一张是午夜。冗余地将这些读数存储在这些较小的表中。这些表有相同的模式，只是更少的数据。查询的速度要快得多，因为同样，它们不会在数据中跳过。

这方面的一个变体是使用TRIGGER将最高时值和午夜值复制到其他表中。(而不是使用应用程序代码)

Partitioning?

这是一个半生不熟的想法，所以我不确定它是否可行和有效.

使用PARTITION BY LIST并有三个分区：“分钟”、“小时”和“日”。有一个包含这三个值的额外列(以某种方式编码以保持分区愉快-因此可能是微不足道的)。假设你有

scale TINYINT UNSIGNED NOT NULL -- 1=minute, 2=hour, 3=day

将其添加到WHERE中

AND scale >= 2   -- to get hourly data

插入新数据时：

INSERT INTO pmd
    (scale, pmd, `datetime`, ...)
    VALUES
    (...<see below>, $pmd, $datetime, ...)

其中，scale值是通过(在客户端代码或存储函数中)分离datetime来计算的。

这避免了第2部分隐含的冗余数据，同时提供了3个表(以3个分区的形式)。“聚类”是很好的。

这需要：

PRIMARY KEY(pmd, `datetime`, scale)

我说“半生不熟”，但当我把所有这些都打出来时，它似乎还在一起。

如果您在分区方面取得成功，那么您将获得http://mysql.rjweb.org/doc.php/partitionmaint中的“案例5”奖--我已经寻找“案例5”已有几年了。