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

问题描述：

我正在尝试每分钟记录200个功率计。每个功率计都有uniqueid（pmid）。该模式是这样的：如何在mysql数据库中有效地存储和搜索每分钟数据？

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;

我用例是每小时检索（其中分钟= 0），每天（其中小时&分钟= 0），每月记录（其中天= 1 &小时&分钟= 0）的特定功率计。

在前两个月，查询工作得很快。但是，有更多的记录，查询时间变得非常缓慢。

我想问一下意见，如何提高性能？有一些想法在我的脑海： 1.更改日期时间到单独的领域，如：

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

2.创建每月新表。更新：今天，我在网上阅读更多，并有一种技术称为分区。我对它感兴趣，因为它不改变模式。我想按年份和月份进行分区。我是否也有关于分区的看法？

该问题似乎是缺乏索引 – Strawberry

什么是你的MySQL版本？你的查询到底是什么？ –

我的MySQL版本是5.7.18。我通常用这个来创建一个小时记录：select * from pmd其中pmid = 2和datatime在（'2017-04-01 00:00'，'2017-04-01 00:01'，...，'2017 -04-01 23:59'）限制1440; – marco

答

你的第一个想法是什么，我可能会也做了一些小的例外：

而不是

`year_2digit` tinyint

我会用

`year` year

年的数据类型具有相同的存储大小作为TINYINT（1字节）。

保留datatime列。您可能需要它来进行其他查询。例如，一个有效的范围条件（如BETWEEN）是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`

请注意，您的复合索引应该与您提到的相反，使其成为'（年，月，日，小时，分）'，这样您可以进行更粗略的查询，例如年，月，日，小时的where子句，同时仍然可以使用此复合索引。 – nos

@nos您建议的索引也可能是有用的，但对于没有固定年份的任何条件都没用。 –

答

我用例是每小时检索（其中分钟= 0），每天（其中小时&分钟= 0），每月记录（其中天= 1 &小时&分钟= 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将首要时间和午夜值复制到其他表中。（而不是使用应用程序代码。）

分区？

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

使用PARTITION BY LIST并有3个分区：'分钟'，'小时'和'日'。还有一个额外的列，这三个值（以某种方式编码，以保持分区快乐 - 所以也许是一个tinyint）。比方说，你有

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”多年。

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

相关推荐