MySQL Partitioning/Sharding/Splitting - 走哪条路？

Question

我们有一个大约 70 GB 的 InnoDB 数据库，我们预计它会在未来 2 到 3 年内增长到数百 GB。大约 60% 的数据属于单个表。目前数据库运行良好，因为我们有一个 64 GB RAM 的服务器，所以几乎整个数据库都可以放入内存，但我们担心 future 数据量会变得相当大。现在我们正在考虑用某种方法来拆分表(尤其是占数据最大部分的表)，我现在想知道，最好的方法是什么。

我目前知道的选项是

• 使用 MySQL 5.1 自带的 Partitioning
• 使用某种封装数据分区的第三方库(如休眠分片)
• 在我们的应用程序中自行实现

我们的应用程序基于 J2EE 和 EJB 2.1 构建(希望有一天我们会切换到 EJB 3)。

你有什么建议？

编辑(2011-02-11):
更新一下:目前数据库大小为 380 GB，我们的“大”表数据大小为 220 GB，其索引大小为 36 GB。因此，虽然整个表不再适合内存，但索引却可以。
系统仍然运行良好(仍然在相同的硬件上)，我们仍在考虑对数据进行分区。

编辑(2014-06-04):还有一个更新:整个数据库的大小是 1.5 TB，我们的“大”表的大小是 1.1 TB。我们将服务器升级到具有 128 GB RAM 的 4 处理器机器(Intel Xeon E7450)。该系统仍然运行良好。我们接下来计划将大表放在单独的数据库服务器上(我们已经对软件进行了必要的更改)，同时升级到具有 256 GB RAM 的新硬件。

此设置应持续两年。然后我们要么必须最终开始实现分片解决方案，要么只购买具有 1 TB RAM 的服务器，这应该会让我们持续一段时间。

编辑(2016-01-18):

我们已经把我们的大表放在它自己的数据库中，放在一个单独的服务器上。目前这个数据库的大小大约是 1.9 TB，另一个数据库(除了“大”表之外的所有表)的大小是 1.1 TB。

当前硬件设置:

• HP ProLiant DL 580
• 4 x Intel(R) Xeon(R) CPU E7-4830
• 256 GB 内存

此设置的性能很好。

Answer 1

一旦 42 GB 表不再适合内存，您肯定会开始遇到问题。事实上，一旦它不再适合内存，性能就会迅速下降。一种测试方法是将该表放在另一台 RAM 较少的机器上，看看它的性能有多差。

First of all, it doesn't matter as much splitting out tables unless you also move some of the tables to a separate physical volume.

这是不正确的。分区(通过 MySQL 5.1 中的功能，或者使用 MERGE 表的相同功能)可以提供显着的性能优势，即使这些表位于同一驱动器上。

例如，假设您正在使用日期范围在大表上运行 SELECT 查询。如果表是整个表，查询将被迫扫描整个表(在那个大小下，即使使用索引也会很慢)。分区的优点是您的查询只会在绝对必要的分区上运行。如果每个分区大小为 1 GB，而您的查询只需要访问 5 个分区即可完成自身，那么对于 MySQL 来说，组合的 5 GB 表比 42 GB 的怪物版本更容易处理。

您需要问自己一件事是如何查询数据。如果您的查询有可能只需要访问某些数据 block (即日期范围或 ID 范围)，那么某种分区将证明是有益的。

我听说 MySQL 5.1 分区仍然存在一些错误，特别是与 MySQL 选择正确的键有关。 MERGE 表可以提供相同的功能，尽管它们需要稍微多一点的开销。

希望能有所帮助……祝你好运!