MySQL：使用多个表还是多个数据库？

Question

对于一个项目，我们有许多具有相同结构且没有链接在一起的数据。有两种方法来保存这些数据：

• 为每个池创建一个新数据库（大约15-25张表）
• 在一个数据库中创建所有表，并通过表名区分池。

哪种方法对于MySQL来说更容易和更快速？

编辑：我不关心数据库设计的问题，我只是想知道这两种可能性哪种更快。

编辑2：我将尝试让它更清晰。如上所述，我们将拥有数据，其中某些日期很少在不同池中归属于一起。将一个类型的所有数据放入一个表中并使用池ID进行链接并不是一个好主意：

• 备份/删除特定池很困难（即使使用大整数，我们也期望用完主键）

因此，想法是为每个池制作一个数据库或在一个数据库中创建许多表。50%的查询针对数据库的语句将是简单的inserts，49%将是基于主键的一些简单的selects。

问题是，在MySQL中，处理许多表或处理许多数据库哪个更快？

- Hippo

---

你不觉得性能和数据库设计有些关联吗？ - tuinstoel

我们的查询中99%的情况都会类似于："SELECT * FROM db.tbl WHERE primaryid=x" - TheHippo

不透露任何商业机密，您能否在问题中详细说明为什么您有这样的设计？您不一定需要更改它，但了解它为什么是这样的会有所帮助。 - aronchick

听起来有点可疑。能否澄清一下什么是对象模型将会很好。 - Mark Canlas

性能基于许多因素，包括基础设施、数据库的访问方式以及访问次数。考虑到您的限制，我建议选择多个数据库。使用多个数据库，您可以随时增加硬件来解决问题。 - Matthew Farwell

Answer 1

在一个数据库中创建多张表与在不同数据库中创建多张表之间不应该存在显著的性能差异。

在MySQL中，数据库（标准SQL使用术语“模式”）主要用作表的命名空间。一个数据库只有一些属性，例如默认字符集和排序规则。使用GRANT控制每个数据库的访问权限非常方便，但这与性能无关。

您可以从单个连接访问任何数据库中的表（前提是它们由MySQL服务器的同一个实例管理）。您只需限定表名即可：

SELECT * FROM database17.accounts_table;

这只是一种纯粹的语法差异，不会影响性能。

关于存储，你不能像@Chris猜测的那样将表组织到每个数据库的文件中。使用MyISAM存储引擎，你始终有一个文件对应一个表。使用InnoDB存储引擎，你可以拥有一个将所有表合并在一起的存储文件集，或者你可以为每个表创建一个文件（这是针对整个MySQL服务器配置的，而不是每个数据库）。在任何情况下，将表创建在单个数据库中与创建在多个数据库中没有性能优势或劣势。

没有太多MySQL配置参数可以按数据库工作。大多数影响服务器性能的参数在服务器范围内生效。

关于备份，你可以将一组特定的表指定为mysqldump命令的参数。按数据库逻辑集合进行备份可能更方便，而无需在命令行上命名所有表。但这只是为了你输入备份命令时方便，并不会对性能产生影响。

- Bill Karwin

---

MySQL配置中的一个数据库是binlog。如果您不想为所有数据库启用binlog以获得小的性能优势，仍然会有一些需要binlogging的表。您可以将这些表推送到单独的数据库中以在它们上启用binlog。 - Ethan

唯一的问题可能是安全漏洞，因为所有访问都在同一个用户下。 - nodws

Answer 2

为什么不创建一个单独的表格来跟踪您的池（使用PoolID和PoolName作为您的列名，以及其他您想要跟踪的内容），然后在您的15-25个表格上添加一个列，该列将是对您的池表的外键，以便您知道特定记录属于哪个池。

如果您不想这样混合数据，我建议创建多个数据库。创建多个表格来完成同一功能会让我感到不安。

- TheTXI

---

赞同。可能是数据设计不当。 - user82238

通常情况下，执行相同操作的多个表格通常是设计不够完善的标志。 - Matthew Farwell

你说得没错，但这不是我问题的答案。我问的是性能，而不是数据库设计。 - TheHippo

@MatthieuF - 我认为他在谈论“分片”他的数据库。Flickr，YouTube等所有这样的网站都这样做。当一个单一的、完全规范化的数据库无法处理所承受的负载时，就会出现这种情况。 - Paul Suart

Answer 3

如果您不想像TheTXI建议的那样使用带有poolId poolname的一组表，请使用单独的数据库而不是多个执行相同操作的表。

这样，您可以通过初始的“use database”语句限制访问不同池的变化，无需每次重新编写SELECT或使用动态SQL。

此方法的其他优点包括：

• 易于备份/还原
• 易于启动/停止数据库实例。

缺点是：

• 可能需要稍微增加一些管理工作量，但不会太多。

我不知道您的应用程序是什么，但在创建所有表格的单个数据库中之前，请仔细考虑。那样会导致疯狂。

编辑：如果性能是唯一关心的事情，您需要测量它。获取一组代表查询并测试它们的性能。

编辑2：对于单个查询在多个表/多个数据库模型之间的性能差异将是微不足道的。如果您只有一个数据库，则可以调整其性能。如果您有多个数据库，则可以针对所有数据库进行调整。

我们（也许？-不能替其他人说话）的观点是，对于良好调整的数据库，三个选项（表中的poolid，多个表，多个数据库）之间的性能差异几乎不存在，因此您可以选择在短期和长期内最容易的选项。

对于我来说，最好的选择仍然是具有poolId的单个数据库，就像TheTXI建议的那样，然后根据您的（主要是管理）需求使用多个数据库。如果您需要确切地知道两个选项之间性能差异是多少，我们无法提供该答案。您需要进行设置和测试。

使用多个数据库，可以轻松投入硬件以改善性能。

- MatthieuF

Answer 4

在您所描述的情况下，根据我的经验，当您有大量的池子时，将不同的数据库分开使用会更快。

但是这里有一个非常重要的通用原则：不要考虑它有多快，可以对其进行性能分析。

- chaos

Answer 5

我不太确定我完全理解您的情况。您是想让所有池使用相同的表，只是通过区分键进行区分吗？还是您想在一个数据库中有单独的表池，每个表都带有后缀以区分这些池？

无论如何，您应该拥有多个数据库，原因有两点。首先，如果您需要更改一个池的架构，不会影响其他池。

第二，如果负载增加（或出于任何其他原因），您可能希望将池移动到单独的物理机器上，具有新的数据库服务器。

此外，对数据库服务器的安全访问可以更加严格地锁定。

所有这些事情仍然可以在不需要单独的数据库的情况下完成-但是分离将使所有这些操作更容易，并减少必须在脑海中跟踪要操作的表的复杂性。

- Josh Smeaton

Answer 6

将池按表名区分或将它们放在不同的数据库中基本上是一样的事情。但是，如果你在一个数据库中有很多表，MySQL 在登录/连接时必须加载表信息并对所有这些表进行安全检查。

正如其他人所提到的，单独的数据库将允许您移动和创建特定于某个池的优化（例如压缩表）。这是额外的管理开销，但有更大的灵活性。

此外，您始终可以使用联合或合并表来“汇总”位于不同数据库中的表，以简化查询。

至于用完主键的问题，如果您正在使用 MyISAM 表，可以始终使用复合主键。例如，如果您有一个名为 groupCode（任何类型）的字段和另一个名为 sequenceId（自动递增）的字段，并将主键创建为 groupCode+sequenceId，则 sequenceId 将根据组代码集中的下一个唯一 ID 递增。

例如： AAA 1 AAA 2 BBB 1 AAA 3 CCC 1 AAA 4 BBB 2 ...

尽管在处理大文件时需要小心缓存并确保您使用的文件系统能够处理大文件。

- Brent Baisley

Answer 7

我不是很熟悉mysql，但我认为我必须给出标准的性能答案——“这取决于情况”。

以下是一些想法（仅涉及性能/维护，而非数据库设计）：

- 创建一个新数据库意味着在文件系统中有一个单独的文件（或多个文件）。 如果其中一个的性能需要与其他文件系统分开，则可以将这些文件放置在不同的文件系统上。 - 新数据库可能会处理缓存方式不同；例如，一个数据库中的所有表格将意味着共享一个数据库的缓存，而将表格拆分为不同的数据库意味着每个数据库都可以有一个单独的缓存[显然，所有数据库将共享相同的物理内存以进行缓存，但可能每个数据库有一个限制等]。 - 与单独的文件相关，这意味着如果其中一个数据集比其他数据集更重要，它可以很容易地转移到新服务器上。 - 分离数据库还有一个额外的好处，就是可以比单个数据库更容易地逐个部署更新。

然而，相反地，具有多个数据库意味着服务器可能会使用更多的内存（因为它具有多个缓存）。 我确信对于多数据库方法存在更多的“缺点”，但是现在我没有想到。

因此，我认为我会推荐使用多数据库方法。 显然，这只是在理解实际情况下处理方式可能会更好的“数据库设计”方面。

- Chris

Answer 8

鉴于您所设置的限制，我更愿意在现有数据库中启动更多的表格，而不是连接到多个数据库。管理连接字符串往往更加困难，此外还要管理可能存在的不同数据库优化。

- aronchick

Answer 9

就正常情况而言，我会采用TheTXI所描述的方法。

然而，针对你具体的问题，我发现这取决于使用情况。（我知道这有点含糊不清，但请听我解释。）

一个单一的数据库可能更容易。你只需要关注一个连接，并且仍然需要指定表格。在某些情况下，多个数据库可能会更快。

如果我是你，我会尝试两种方法。我们无法给你一个有用的答案。

- Tom Wright