我看到了很多帖子都几乎回答了这个问题,但没有考虑到这个特定领域的影响:
我想在MySQL中存储大量计量器(500个及以上),每个计量器每5分钟提供一个单一的浮点值的时间序列数据。最简单的情况下,结构将是: - 计量器ID - 时间戳 - 值
(计量器ID和时间戳组合为主键)
这意味着每个计量器每年大约有105120行数据 - 所有这些数据需要保存10或20年。对于1000个计量器,我们每年将处理1亿条记录。
数据是批量写入的,通常每个通道的值都会被聚合成一个XML文件从远程源读入数据库,然后按小时或按天进行读取。因此,在每个小时内插入的次数最多与计量器数量相同。
对数据的读取操作将是基于时间范围的每个计量器(因此不涉及计量器之间的数据联接)。例如,要获取某个日期范围内计量器X的所有值。 通常,这还包括某种形式的聚合/插值函数 - 因此用户可能希望查看任意范围内的每日平均值、每周最大值等。 同样,读取次数相对较少,但这些需要在MySQL中在1秒内响应。
目前,我倾向于每个计量器使用一个表,而不是在gauge_id上分区一个巨大的MySQL表。 逻辑是这将使将来更容易进行分片,简化备份,并且如果在任何阶段存在数据错误,则本质上使计量器更容易删除/重建。 代价是写入和读取操作都会变得更加复杂。
对此有什么想法吗?
-------- 更新 --------
我在我的MacBook 2.4gHz Core 2 Duo、4GB RAM上运行了一些测试。
设置了以下表:
我想在MySQL中存储大量计量器(500个及以上),每个计量器每5分钟提供一个单一的浮点值的时间序列数据。最简单的情况下,结构将是: - 计量器ID - 时间戳 - 值
(计量器ID和时间戳组合为主键)
这意味着每个计量器每年大约有105120行数据 - 所有这些数据需要保存10或20年。对于1000个计量器,我们每年将处理1亿条记录。
数据是批量写入的,通常每个通道的值都会被聚合成一个XML文件从远程源读入数据库,然后按小时或按天进行读取。因此,在每个小时内插入的次数最多与计量器数量相同。
对数据的读取操作将是基于时间范围的每个计量器(因此不涉及计量器之间的数据联接)。例如,要获取某个日期范围内计量器X的所有值。 通常,这还包括某种形式的聚合/插值函数 - 因此用户可能希望查看任意范围内的每日平均值、每周最大值等。 同样,读取次数相对较少,但这些需要在MySQL中在1秒内响应。
目前,我倾向于每个计量器使用一个表,而不是在gauge_id上分区一个巨大的MySQL表。 逻辑是这将使将来更容易进行分片,简化备份,并且如果在任何阶段存在数据错误,则本质上使计量器更容易删除/重建。 代价是写入和读取操作都会变得更加复杂。
对此有什么想法吗?
-------- 更新 --------
我在我的MacBook 2.4gHz Core 2 Duo、4GB RAM上运行了一些测试。
设置了以下表:
CREATE TABLE `test` (
`channel_id` int(10) NOT NULL,
`time` datetime NOT NULL,
`value` int(10) NOT NULL,
KEY `channel_id` (`channel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
使用存储过程填充:
CREATE PROCEDURE `addTestData`(IN ID INT, IN RECORDS INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE j DATETIME DEFAULT '1970-01-01 00:00:00';
WHILE (i<=RECORDS) DO
INSERT INTO test VALUES(ID,j,999);
SET i=i+1;
SET j= j + INTERVAL 15 MINUTE;
END WHILE;
END $$
当时我调用创建前100万条记录的函数
call addTestData(1,1000000);
插入操作执行时间为47秒
SELECT * FROM `test` WHERE channel_id = 1 and YEAR(time) = '1970';
执行时间为0.0006秒
SELECT AVG(value) as value, DATE(time) as date FROM `test`
WHERE channel_id = 1 and YEAR(time) = '1970' group by date;
执行时间为4.6秒(最大值和总和函数同时执行)。
添加了4个仪表后:
call addTestData(2,1000000);
call addTestData(3,1000000);
call addTestData(4,1000000);
call addTestData(5,1000000);
插入操作执行时间为47秒,表格使用了78兆字节。
我运行了同样的两个查询,并且得到了与表中有一百万条记录时完全相同的执行时间(对于更大的查询,需要4.6秒)。
因此,除了潜在的用于分片、备份和将来针对任何单个测量表的硬件驱动更改(即多个读数、数据间隔的更改)之外,似乎没有必要在可预见的未来将其拆分为多个表。甚至没有尝试使用分区运行查询,似乎也没有任何理由。
--------然而-------------
由于查询需要4.6秒的时间并不理想,我们显然需要进行一些优化。作为第一步,我重新构造了查询:
SELECT
AVG(value) as value,
DATE(time) as date
FROM
(SELECT * FROM test
WHERE channel_id = 1 and YEAR(time) = '1970')
as temp
group by date;
在包含500万个记录的表格上运行查询(涉及5个频道ID),该查询需要4.3秒的时间。但如果我在只有1个频道ID、100万条记录的表格上运行该查询,只需0.36秒!!这让我有些困惑……
对这个包含500万条记录的表格进行分区。
ALTER TABLE test PARTITION BY HASH(channel_id) PARTITIONS 5;
随后,以上复合查询在0.35秒内完成,性能提升相同。