使用MySQL定期在100+ GB表上进行多路连接？

Question

背景：

我已经创建了一个web应用程序，我希望能够相当好地扩展。我知道我不是Google或Twitter，但我的应用程序为每个用户使用了相当多的数据，因此对数据的要求也相当高。我想做好准备，在不需要重新设计所有东西的情况下，进行合理的扩展。

我认为自己是一个软件开发人员，而不是一个数据库专家。这就是为什么我要在这里发帖。希望拥有更多数据库专业知识的人能给我提供建议。

由于用户数量相对较多，但与Facebook号码不同，我希望有一个如下所示的DB：

一张“大桌子”：

• 2.5亿记录
• 20列
• 大约100 GB的数据
• 有索引的bigint(20)外键
• 具有索引varchar(500) string_id列
• 具有int(11) "value“列

4其他表格：

• 一千万条记录
• 每个大约2-4 GB的数据
• 每个表都有4-8列。
• 一个列是datetime date_created
• 一列是varchar(500) string_id列
• 将在联接中从这些表中选择一个或两个列。

其中一个表用于存储平均值？？它的模式是bigint(20) id、varchar(20) string_id、datetime date_created、float average_value。

我想做的是--两个相对昂贵的查询：

1. 计算新平均值：
   • 使用外键，从大表中选择多达数百万个单独的记录。
   • 计算一个新的平均值，按string_id分组。
   • 将结果插入平均值表。
   • 按照当前的构造，此查询使用两个联接。

2. 为服务用户创建非规范化只读记录：
   • 使用外键从大表中选择1,000至40,000条记录。
   • 使用字符串id列连接最新记录上的其他四个表中的每个表。
   • 将结果插入到非规范化表中。
   • 这些记录是前端用来向用户显示信息的.
   • 按照当前的构造，此查询使用四个联接。

我计划在一个批处理后端数据库上运行这些昂贵的查询，该数据库将其结果推送到实时前端DB服务器，该服务器处理来自用户的请求。这些查询将定期运行。我还没决定多久一次。一般的查询可以每天完成一次。去正常化查询需要更频繁--也许每隔几分钟就一次。

目前，这些查询在MySQL中的每一个查询都在一台非常低端的机器上运行几秒钟，其中的数据集在“大表”中有100 K记录。我既担心我的规模能力，也担心扩大规模的成本。

问题：

1. 这种方法听起来合理吗？从宏观的角度看，它有什么明显的问题吗？
2. RDBMS是正确的工具，还是应该考虑其他“大数据”解决方案，比如Hadoop家族中的一些解决方案？我倾向于使用RDBMS，因为数据是结构化的，并且非常适合关系模型。不过，在某种程度上，我的理解是，我可能不再能够使用RDBMS。这是真的吗？什么时候需要这个开关？
3. 它能用吗？这些查询能否在合理的时间内运行？我可以为查询1等待几个小时，但是查询2应该在几分钟内完成。
4. 我应该从硬件的角度考虑什么？我的RAM和CPU瓶颈可能是什么？我认为在RAM中保存索引是很重要的。还有什么我应该考虑的吗？
5. 在某些时候，我可能不得不对数据进行分区，并使用多个服务器。我的用例似乎已经属于这个类别了，还是我能在一段时间内垂直缩放一台机器？这是否适用于10倍的数据?100倍？

Answer 1

您是否尝试过堆积更多的数据并对其进行基准测试?100 k行是无关紧要的。尝试2.5亿或500米，就像你期望的那样，你将需要处理，看看瓶颈在哪里。

一个RDBMS可以做很多事情，如果您仔细地注意到限制，并尝试和使用系统的优势。他们在某些事情上非常擅长，而在另一些事情上却很糟糕，所以你需要进行实验，以确保它是合适的。

对于某些批处理作业，您确实无法击败平面文件，将数据加载到RAM中，使用一系列循环和临时变量分解数据，并将结果转储出去。MySQL永远不可能，永远无法与这种速度相匹配，但如果适当调整和正确使用，它可以在一个数量级内。

您要做的是研究如何对数据进行分区。你是否有一套大的数据，有太多的交叉链接的方式来分割它，或者是否有天然的地方来划分它？如果可以对其进行分区，就不会有一个包含整堆行的表，但可能会有许多小得多的行。索引要小得多的表往往表现得更好。

从硬件的角度来看，您需要进行测试以查看平台的性能。有时候记忆是必不可少的。其他时候，它是磁盘I/O，这取决于您对数据所做的操作。您将需要密切关注您的CPU使用情况，并寻找高级别的IO，等待知道问题所在。

只要有可能，将数据拆分到多个系统中。如果您感觉勇敢，可以使用MySQL集群，或者简单地拆分许多独立的MySQL实例，其中每个实例使用一些有意义的分区方案存储完整数据集的任意部分。

Answer 2

总表。

每天，为当天的数据计算汇总信息。把它放在“摘要”表中(S)。对他们进行询问。很容易达到10倍的速度。

如欲进一步讨论，请提供

• 显示CREATE (按现在的状态)
• 表的大小(您已经提到了)
• 拟议选择

一些明显的事情..。

• 联非政府组织很少有正当理由。它需要8个字节。INT无符号接受4，并允许值0..4亿。还有MEDIUMINT等等。
• “事实”表上的多个索引通常是一个严重的性能问题，特别是对于插入。你在那有什么问题吗？
• 日期时间为8字节，时间戳为4
• 显式外键约束很好，但代价很高。
• 联接可能是性能问题，也可能不是问题；需要查看SELECT和CREATE。
• 对于“大型”MySQL数据库来说，100 db是一个不错的大小；我怀疑它可以在没有Hadoop的情况下工作，等等。我现在处理一个这样的数据库--大多数UI页面在一秒钟内响应，尽管数据非常复杂。
• 你会在某个时候“清除”数据吗？(这就引出了PARTITIONing的主要用例。)

“越小>越可缓存>越快。

Answer 3

为了提供您的前端数据，除非始终有大量的插入，否则您确实无法击败使用触发器将其插入到与后端保持同步但经过优化以服务数据的物化视图中。当然，在这些触发器中，您需要将联接等保持在最低限度。我使用的一种策略是将这些插入/更新排队到中间表中，然后每隔一分钟左右发送一次。发送一个记录要比发送4GB的记录容易得多。4GB的数据流需要很长时间，即使您可以快速找到正在查找的记录。

我同意塔德曼的观点。最好的方法是在你想要的系统上用你期望的那种数据来分析它。