MySQL：将大表拆分为分区或单独的表？

我有一个包含 20 多个表的 MySQL 数据库，但其中一个非常大，因为它从不同的传感器收集测量数据。它的磁盘大小约为 145 GB，包含超过 10 亿条记录。所有这些数据也被复制到另一台 MySQL 服务器。

我想将数据分成更小的“碎片”，所以我的问题是以下哪种解决方案更好。我会使用记录的“时间戳”将数据除以年份。几乎所有在此表上执行的 SELECT 查询都在查询的“where”部分包含“timestamp”字段。

因此，以下是我无法决定的解决方案：

1. 使用MySQL分区并按年份划分数据（例如partition1 - 2010，partition2 - 2011等）
2. 创建单独的表并按年份划分数据（例如测量_2010、测量_2011 等表）

还有我不知道的其他（更新的）可能选项吗？

我知道在第一种情况下，MySQL 本身会从“分片”获取数据，而在第二种情况下，我必须为其编写一种包装器并自己完成。对于第二种情况，是否有其他方法可以使所有单独的表被视为“一个大表”来从中获取数据？

我知道这个问题过去已经被问过，但也许有人提出了一些新的解决方案（我不知道）或者最佳实践解决方案现在已经改变。 :)

非常感谢你的帮助。

Edit:

该架构与此类似：

device_id (INT)
timestamp (DATETIME)
sensor_1_temp (FLOAT)
sensor_2_temp (FLOAT)
etc. (30 more for instance)

所有传感器温度每分钟在同一时刻写入一次。请注意，大约有 30 个不同的传感器测量值连续写入。这些数据主要用于显示图表和其他一些统计目的。

好吧，如果你希望得到一个新的答案，那就意味着你可能已经阅读了我的答案，而我听起来就像一张破唱片。看分区博客对于分区可以提高性能的少数用例。你的确实如此not听起来像这 4 种情况中的任何一种。

Shrink device_id. INT是 4 个字节；您真的拥有数百万台设备吗？TINYINT UNSIGNED为 1 个字节，范围为 0..255。SMALLINT UNSIGNED为 2 个字节，范围为 0..64K。这将使桌子缩小一点。

If your real问题是如何管理这么多数据，那么让我们“跳出框框思考”。请继续阅读。

绘图...您要绘制什么日期范围？

• “最后”一小时/天/周/月/年？
• 任意的小时/天/周/月/年？
• 任意范围，与日/周/月/年界限无关？

你在画什么图形？

• 一天的平均值？
• 一天中的最大/分钟？
• 日或周的烛台（等）或其他？

无论哪种情况，您都应该构建（并增量维护）包含数据的汇总表。一行将包含一小时的摘要信息。我会建议

CREATE TABLE Summary (
    device_id SMALLINT UNSIGNED NOT NULL,
    sensor_id TINYINT UNSIGNED NOT NULL,
    hr TIMESTAMP NOT NULL,
    avg_val FLOAT NOT NULL,
    min_val FLOAT NOT NULL,
    max_val FLOAT NOT NULL
    PRIMARY KEY (device_id, sensor_id, hr)
) ENGINE=InnoDB;

一个汇总表可能为 9GB（对于当前数据量）。

SELECT hr,
       avg_val,
       min_val,
       max_val
    FROM Summary
    WHERE device_id = ?
      AND sensor_id = ?
      AND hr >= ?
      AND hr  < ? + INTERVAL 20 DAY;

将为您提供 480 小时的高/低/平均值；足够绘制图表吗？从汇总表中抓取 480 行比从原始数据表中抓取 60*480 行要快得多。

一年内获取类似的数据可能会让绘图包感到窒息，所以它may值得建立一个摘要的摘要——并以一天的决心。大约是0.4GB。

有几种不同的方法来构建汇总表；在你思考它的美丽并阅读之后我们可以讨论这个问题汇总表博客。收集一小时的数据，然后扩充汇总表可能是最好的方法。这有点像讨论的触发器我的暂存表博客.

而且，如果您有每小时的摘要，您真的需要每分钟的数据吗？考虑把它扔掉。或者，也许是一个月后的数据。这导致使用分区，但是只是为了删除旧数据的好处正如“案例1”中所讨论的分区博客。也就是说，您将拥有每日分区，使用DROP and REORGANIZE每天晚上都会移动“Fact”表的时间。这将减少您的 145GB 占用空间，但不会丢失太多数据。新足迹：约12GB（每小时摘要+过去30天的每分钟详细信息）

PS：摘要表博客展示如何获得标准差。