大数据MySQL数据库设计

Question

我不是数据库专家，因此我来这里寻求一点帮助。我有大量测量数据，我想帮助自己处理数据。这是我的情况:大约有 10 个站点，每天测量。每天，一个人会产生大约 3000 行(大约 15 列)的数据。数据必须每天从每个站点下载一次到中央服务器。这意味着每天大约有 30 000 行插入到数据库中。 (每日计数是可变的)

现在，我已经有了过去几年的数据，所以对于每个站，我都有几百万行。还有 cca​​ 20 个“死”站 - 不再工作，但有几年的数据。总而言之，我们将获得大约 50+ 百万行，由 30 个站点生成，每天插入大约 30,000 行。展望 future ，让我们假设数据库中有 1 亿行。

我的问题很明显 - 您建议如何存储这些数据？测量值(列)只是数字(整数或 double + 日期时间)- 没有文本或全文搜索，基本上我需要的唯一索引是 DATETIME。数据不会更新，也不会删除。我只需要快速选择一系列数据(例如，从 1.1.2010 到 3.2.2010)

正如我所写，我想使用 MySQL，因为这是我最了解的数据库。我读过，它应该很容易处理这么多数据，但是，我仍然感谢针对这种情况的任何建议。再次:

• 10 个站，每个站每天 3000 行 => 每天约 30000 次插入
• 大约 40-50 百万行尚未从二进制文件中插入
• 数据库将增长(100+ 百万行)
• 我唯一需要做的就是尽可能快地选择数据。

据我所知，MySQL应该能处理这么大的数据量。我也知道，我唯一的索引是 DATETIME 类型的日期和时间(应该比其他索引快，对吗？)我无法决定的是，是创建一个包含 50+ 百万行(带有站点 ID)的巨大表，还是分别为每个站点创建表。基本上，我不需要在这些站上执行任何 JOIN。如果我需要做时间重合，我可以选择相同的站时间范围。这些方法有什么缺点/优点吗？

任何人都可以确认/拒绝我的想法吗？您认为有更好的解决方案吗？感谢任何帮助或讨论。

Answer 1

MySQL 应该能够很好地处理这个问题。我建议您创建两个复合索引，而不是仅索引您的 DATETIME 列，如下所示:

(datetime, station)
(station, datetime)

拥有这两个索引将有助于加速选择日期范围和按站点分组的查询，反之亦然。第一个索引也将用于仅索引 datetime 的目的。

您还没有告诉我们您的典型查询是什么。您也没有告诉我们您是否打算淘汰旧数据。您的数据显然是范围分区 (http://dev.mysql.com/doc/refman/5.6/en/partitioning-range.html) 的候选者，但我们需要更多信息来帮助您设计可行的分区标准。

阅读您的评论后

编辑。

构建此系统时需要牢记几件事。

首先，暂时不要为分区烦恼。

其次，我会让所有的东西都在一张表上工作。不要按车站或年份拆分内容。给自己买一个你能负担得起的最快的磁盘存储系统，并为你的 MySQL 服务器配备大量 RAM，你应该没问题。

第三，偶尔停下来做OPTIMIZE TABLE；这将确保您的索引良好。

第四，不要使用SELECT *，除非您知道您需要表中的所有列。为什么？因为

      SELECT datetime, station, temp, dewpoint
        FROM table
       WHERE datetime >= DATE(NOW() - INTERVAL 60 DAY)
       ORDER BY station, datetime

可以直接通过对复合覆盖索引的顺序访问来满足

     (station, datetime, temp, dewpoint)

鉴于

      SELECT *
        FROM table
       WHERE datetime >= DATE(NOW() - INTERVAL 60 DAY)
       ORDER BY station, datetime

需要随机访问您的表。您应该阅读复合覆盖索引。

第五，避免在 WHERE 子句中使用带有列名的函数。不要说

      WHERE YEAR(datetime) >= 2003 

或类似的东西。 MySQL 不能为这种查询使用索引。而是说

      WHERE datetime >= '2003-01-01'

允许索引被利用。