使用多张表还是使用分区？

Question

使用多张表还是使用分区？

mysqldatabase-designpartitioning

3

我看到了很多帖子都几乎回答了这个问题，但没有考虑到这个特定领域的影响：

我想在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秒内完成，性能提升相同。

- Rean

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- nobody · Accepted Answer

对我来说，你的情况中没有任何理由通过规格分区，如果你在gauge_id上有一个索引，性能不会成为问题，因为MySQL将使用索引立即找到与某个规格相关的行，之后其他操作就像处理每个规格的专用表一样。

唯一可能需要分区的情况是，如果您经常访问非常新的规格数据（例如最新的10％），而不是旧数据（剩余90％），如果是这种情况，则将其分成两个“最近”和“归档”表可能会给您带来很多性能优势。

如果您对单个表的操作不涉及索引，则同样的操作在合并的表上不应该花费更长时间，因为MySQL首先使用gauge_id索引将结果缩小到特定的规格行，如果操作涉及索引，则应该将索引设置为从“gauge_id”开始的多列索引，例如，在单个表上的INDEX（timestamp）应变为INDEX（gauge_id，timestamp），然后在大多数情况下，操作将花费与单个表相同的时间。此外，不要被“5亿行”等数字吓到，数据库设计可处理该数量的数据。

我的评论大多基于我在你的情况下几乎每次都选择使用单独的表格的经验。由于某种原因，最终我将这些表格合并回一个表格，而大多数情况下，当项目成熟时会发生这种情况，这是一个痛苦的过程。我真正体验到了“关系数据库不是设计成这样使用的”的感觉。

顺便说一句，在采取任何方式之前，请进行大量测试，MySql有很多意外行为。