5 个单独的数据库还是 1 个数据库中的 5 个表？
假设我想建立一个游戏网站并且我有很多游戏部分。它们都有大量需要存储的数据。是制作一个数据库并用一张代表每场比赛的表更好，还是用一个数据库代表比赛的每个部分更好？我非常期待一个“取决于”的答案。
Let's say I want to build a gaming website and I have many game sections. They ALL have a lot of data that needs to be stored. Is it better to make one database with a table representing each game or have a database represent each section of the game? I'm pretty much expecting a "depends" kind of answer.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（17）
管理 5 个不同的数据库将是一件令人头疼的事情。我建议使用一个包含 5 个不同表的数据库。抛开其他事情不说，如果发现您拥有 5 者之间的一些共同信息（例如用户身份），我不会感到惊讶。
请注意，您对“大量数据”的想法可能与数据库的想法不同......数据库通常是为了处理巨大数据团而编写的。
Managing 5 different databases is going to be a headache. I would suggest using one database with 5 different tables. Aside from anything else, I wouldn't be surprised to find you've got some common info between the 5 - e.g. user identity.
Note that your idea of "a lot of data" may well not be the same as the database's... databases are generally written to cope with huge globs of data.
视情况而定。
只是在开玩笑。如果这是一个项目，并且数据以任何方式相互关联，我总是会选择一个数据库，除非有具体且令人信服的理由不这样做。为什么？因为我不记得自己曾想“天哪，我真希望看到这些信息更难”。
Depends.
Just kidding. If this is one project and the data are in any way related to each other I would always opt for one database absent a specific and convincing reason for doing otherwise. Why? Because I can't ever remember thinking to myself "Boy, I sure wish it were harder to see that information."
虽然您的问题中没有足够的信息来给出一个好的答案，但我想说，除非您预见到同一用户（或查询）同时需要来自两个游戏的数据，否则没有理由合并数据库。
您可能应该为任何常见的内容创建一个数据库，然后为任何独特的内容创建独立的数据库。数据库与代码一样，最终往往会针对不同的应用程序朝不同的方向发展。将它们放在一起可能会导致您破坏事物或在更改中更加保守。
此外，某些数据库是在数据库级别而不是表级别进行优化、管理和备份的。由于它们可能具有不同的性能特征和使用情况，因此一刀切的解决方案可能无法扩展。
如果您使用 ORM 框架，您可以（几乎）免费访问多个数据库，同时仍然避免代码复制。所以除非你有联合查询，否则我认为不值得付出共享数据库的风险。
当然，如果您花钱请人托管您的数据库，那么使用单个数据库可能会更便宜，但这实际上是一个业务问题，而不是软件问题。
如果您确实选择使用单个数据库，请帮自己一个忙，并确保每个游戏的代码仅了解特定的表。这将使您以后更容易维护事物或将其分离到多个数据库中。
While there is not enough information in your question to give a good answer, I would say that unless you foresee needing data from two games at the same time for the same user (or query), there is no reason to combine databases.
You should probably have a single database for anything common, and then create independent databases for anything unique. Databases, like code, tend to end up evolving in different directions for different applications. Keeping them together may lead you to break things or to be more conservative in your changes.
In addition, some databases are optimized, managed, and backed-up at a database level rather than a table level. Since they may have different performance characteristics and usage profiles, a one-size-fit-all solution may not be scalable.
If you use an ORM framework, you get access to multiple databases (almost) for free while still avoiding code replication. So unless you have joint queries, I don't think it's worth it to pay the risk of shared databases.
Of course, if you pay someone to host your databases, it may be cheaper to use a single database, but that's really a business question, not software.
If you do choose to use a single database, do yourself a favour and make sure the code for each game only knows about specific tables. It would make it easier for you to maintain things later or separate into multiple databases.
一个数据库。
您合理地想要存储的大多数内容都是文本或原始数据类型（例如整数）。您可能喜欢将二进制内容放入 blob 中，但对于媒体密集型网站来说，这是一个疯狂的计划，因为 Web 服务器将通过 HTTP 免费提供文件。
我在一家大型游戏发行商的网站上担任首席编程职务。我们设法用三种欧洲语言覆盖了他们当前和以前的大部分内容。
尽管每个标题都充满了视频和图像资源，但我们从未考虑过使用多个数据库来存储所有这些内容。
我无法想象为什么多数据库配置会满足您的需求，无论是在开发中还是在开发之外。您必须进行的同步量和出错的可能性是巨大的。试图从所有中提取属于所有这些的数据将是一场噩梦。
您迁移的每个站点范围的更新都将困难且容易出错n倍，其中n是您最终选择的数据库数量。
说真的，一个数据库 - 这与您预期的取决于答案相差甚远。
One database.
Most of the stuff you are reasonably going to want to store is going to be text, or primitive data types such as integers. You might fancy throwing your binary content into blobs, but that's a crazy plan on a media-heavy website when the web server will serve files over HTTP for free.
I pulled lead programming duties on a web-site for a major games publisher. We managed to cover a vast portion of their current and previous content, in three European languages.
At no point did we ever consider having multiple databases to store all of this, despite the fact that each title was replete with video and image resources.
I cannot imagine why a multiple database configuration would suit your needs here, either in development or outside of it. The amount of synchronisation you'll have to pull and capacity for error is immense. Trying to pull data that pertains to all of them from all of them will be a nightmare.
Every site-wide update you migrate will be n times as hard and error prone, where n is the number of databases you eventually plump for.
Seriously, one database - and that's about as far from your anticipated depends answer as you're going to get.
如果不同的游戏不共享任何数据，那么使用单独的数据库是有意义的。另一方面，如果游戏数据的结构相同，则使用一个数据库是有意义的——否则您将必须分别对每个游戏数据库进行更改。
更新：如果有疑问，您应该始终使用一个数据库，因为在大多数情况下它更容易管理。如果您确定应用程序是完全独立的并且具有完全不同的结构，您应该使用更多的数据库。唯一真正的优势是更加清晰。
If the different games don't share any data it would make sense to use separate databases. On the other hand it would make sense to use one database if the structure of the games' data is the same--you would have to make changes in every game database separately otherwise.
Update: In case of doubt you should always use one database because it's easier to manage in the most cases. Just if you're sure that the applications are completely separate and have completely different structures you should use more databases. The only real advantage is more clarity.
一般来说，“每个应用程序一个数据库”往往是一个很好的经验法则。
如果您正在构建一个包含许多部分来讨论不同游戏（或不同类型的游戏）的网站，那么这就是一个应用程序，因此一个数据库可能是最佳选择。我不太肯定，但我想这可能就是你所问的情况。
另一方面，如果您的“一个站点”是一个 Battle.net 类型的匹配服务，用于五个不同游戏的集合，那么该站点本身就是一个应用程序，并且这五个游戏中的每一个都是一个单独的应用程序，因此您因为您总共有六个基本上独立的应用程序，所以可能需要六个数据库。不过，我的印象是，这不是您所问的情况。
Generally speaking, "one database per application" tends to be a good rule of thumb.
If you're building one site that has many sections for talking about different games (or different types of games), then that's a single application, so one database is likely the way to go. I'm not positive, but I think this is probably the situation you're asking about.
If, on the other hand, your "one site" is a battle.net-type matching service for a collection of five distinct games, then the site itself is one application and each of the five games is a separate application, so you'd probably want six databases since you have a total of six largely-independent applications. Again, though, my impression is that this is not the situation you're asking about.
如果您要为每个游戏存储相同的数据，那么使用 1 个数据库来存储所有信息是有意义的。跨不同数据库复制表结构是没有意义的，同样，如果 5 个游戏都存储相同的信息，则为 5 个游戏创建 5 个表也是没有意义的。
If you are going to be storing the same data for each game, it would make sense to use 1 database to store all the information. There would be no sense in replicating table structures across different databases, likewise there would be no sense in creating 5 tables for 5 games if they are all storing the same information.
我不确定这是否正确，但我认为您想要使用一个包含 5 个表的数据库，因为（以及其他原因）替代方案对连接池的影响（例如，如果您正在使用 ADO.Net）。在 ADO.Net 连接池中，连接由连接字符串作为键控，因此对于 5 个不同的数据库，您可能最终会得到每个数据库 20 个连接，而不是一个数据库 100 个连接，这可能会影响连接分配的灵活性。
如果有人知道更多信息或有其他信息，请在此处添加，因为我不确定我所说的是否准确。
I'm not sure this is correct, but I think you want to do one database with 5 tables because (along with other reasons) of the alternative's impact on connection pooling (if, for example, you're using ADO.Net). In the ADO.Net connection pool, connections are keyed by the connection string, so with five different databases you might end up with 20 connections to each database instead of 100 connections to one database, which would potentially affect the flexibility of the allocation of connections.
If anybody knows better or has additional info, please add it here, as I'm not sure if what I'm saying is accurate.
您对“大量数据”有何看法？您需要将其拆分到多个数据库的唯一原因是，如果您想通过共享托管节省一些钱（即获得便宜的共享主机并将其拆分到服务器上），或者如果您认为每个数据库将在 500GB 以上范围并且无法访问适当的存储。
请注意，这两个原因都与架构无关，并且完全基于扩展过程中的金钱问题。
但由于您还没有创建该网站，您就本末倒置了。全新站点不太可能使用接近此级别的存储，因此只需创建 1 个数据库。
一些公司的单个数据库大小在 1,000+ TB 范围内……数据库大小基本上没有上限。
What's your idea of "a lot of data"? The only reason that you'd need to split this across multiple databases is if you are trying to save some money with shared hosting (i.e. getting cheap shared hosts and splitting it across servers), or if you feel each database will be in the 500GB+ range and do not have access to appropriate storage.
Note that both of these reasons have nothing to do with architecture, and entirely based on monetary concerns during scaling.
But since you haven't created the site yet, you're putting the cart before the horse. It is very unlikely that a brand new site would use anywhere near this level of storage, so just create 1 database.
Some companies have single databases in the 1,000+ TB range ... there is basically no upper bound on database size.
您要创建的数据库数量不取决于游戏的数量，而是取决于数据库中存储的数据，或者更好地说，取决于如何在数据库之间交换这些数据。
如果数据库彼此不相关，那么它们当然是单独的数据库。
在我的一个项目中，我区分了内部数据和外部数据（存储在单独的数据库中）。
区别非常简单：
外部数据库仅存储您无法更改或撤消的事实。在我们的案例中，这就是电话、
短信消息和收到的付款。内部数据库存储了通常存储的内容：用户、密码等。
外部数据库仅使用自然
主键，即电话号码、银行交易id等。数据库是 赋予完全不同的权利，它们之间交换数据是导入和导出的问题，而不是关系的问题。
这确保了实际数据不会发生任何事情：将付款重新链接到用户很容易，但如果付款丢失则很难恢复。
The number of databases you want to create depends not on the number of your games, but on the data stored in the databases, or, better say, how do you exchange these data between the databases.
If the databases are not related to each other, then they are separate databases, of course.
In one of my projects, I distinguished between the internal and external data (which were stored in separate databases).
The difference was quite simple:
External database stored only the facts you cannot change or undo. That was phone calls,
SMSmessages and incoming payments in our case.Internal database stored the things that are usually stored: users, passwords etc.
The external database used only the natural
PRIMARY KEY's, that were the phone numbers, bank transaction id's etc.The databases were given with completely different rights and exchanging data between them was a matter of import and export, not relationships.
This made sure that nothing would happen with actual data: it is easy to relink a payment to a user, but it's very hard to restore a payment if it's lost.
我可以把我遇到类似情况的经验传授给大家。
我们有 4 个“通用”数据库和大约 30 个“特定”数据库，出于相同的空间问题而分开。缺点是空间问题只是将 dBase 的缺点投射到 SQL Server 上。我们最终在 SQL Server Enterprise 上得到的所有这些数据库都远低于桌面版允许的最大大小。
从关注点分离的数据库角度来看，4 个通用数据库可能是 2 个。30 个特定数据库可能是 3 个（如果有足够的操作/泛化，甚至是 1 个）。低效的代码（存储过程和数据访问层代码）和表模式决定了众多数据库；归根结底，它与空间毫无关系。
我会尽早整合并保留您的设计和设计。实现足够灵活，可以在必要时提取组件。简而言之，规划多个数据库，但作为一个数据库实施。
I can pass on my experience with a similar situation.
We had 4 "Common" databases and about 30 "Specific" databases, separated for the same space concerns. The downside is that the space concerns were just projecting dBase shortcomings onto SQL Server. We ended up with all these databases on SQL Server Enterprise that were well under the maximum size allowed by the Desktop edition.
From a database perspective with respect to separation of concerns, the 4 Common databases could've been 2. The 30 Specific databases could've been 3 (or even 1 with enough manipulation / generalization). It was inefficient code (both stored procs and data access layer code) and table schema that dictated the multitude of databases; in the end it had nothing at all to do with space.
I would consolidate as much as possible early and keep your design & implementation flexible enough to extract components if necessary. In short, plan for several databases but implement as one.
请记住，尤其是在网站上。如果您有多个数据库，您通常会失去查询缓存和连接池的性能优势。坚持一个。
Remember, especially on web sites. If you have multiple databases, you often lose the performance benefits of query caching and connection pooling. Stick to one.
毫无疑问，一个数据库
Defenitively, one database
我工作的一个地方有很多数据库，一个是所有客户使用的通用数据库，另一个是客户定制的客户特定数据库。最终发生的情况是，由于客户要求进行更改，因此它们最终会出现在客户数据库中而不是通用数据库中，因此将有 27 种方法来做本质上相同的事情，因为没有从特定于客户的重构为“嘿这个是其他客户也需要做的事情”所以让我们把它放在一起。因此，一个数据库往往会减少重新发明轮子的情况。
One place I worked had many databases, a common one for the stuff all clients used and client specifc ones for customizing by client. What ended up happening was that since the clients asked for the changes, they woudl end up inthe client database instead of common and thus there would be 27 ways of doing essentially the same thing becasue there was no refactoring from client-specific to "hey this is something other clients will need to do as well" so let's put it in common. So one database tends to lead to less reinventing the wheel.
安全模型
如果每个游戏都有一组特定于该游戏的不同权限/角色，请将其分开。
查询性能/复杂性
如果您需要频繁查询游戏之间的数据，我建议将它们保存在单个数据库中。
可扩展性
另一个考虑因素是您的可扩展性计划。如果游戏非常受欢迎，您可能需要为每个游戏购买单独的数据库硬件。从一开始就将它们分离到不同的数据库中会让事情变得更容易。
数据大小
数据大小不应成为此决定的因素。
Security Model
If each game will have a distinct set of permissions/roles specific to that game, split it out.
Query Performance /Complexity
I'd suggest keeping them in a single database if you need to frequently query across the data between the games.
Scalability
Another consideration is your scalability plans. If the games get extremely popular, you might want to buy separate database hardware for each game. Separating them into different databases from the start would make that easier.
Data Size
The size of the data should not be a factor in this decision.
只是补充一点。当你在一款游戏中拥有数以百万计的玩家并且你的游戏是实时的并且你有数以万计的同时在线玩家时，你必须至少在数据库中保持一些重要数据尽可能最新（例如，玩家的虚拟数据）钱）。然后，您将希望将表分成独立的数据库，即使它们都是“连接的”。
这确实取决于。无论您如何尝试避免结垢，洁牙都会带来痛苦。但如果你真的期望有很多玩家、更新和数据，我建议你在决定“一个数据库用于多个项目”解决方案之前三思而后行。
是的，管理多个数据库可能会很困难。但无论如何你都必须这样做。
Just to add a little. When you have millions and millions of players in one game and your game is realtime and you have tens of thousand simultaneous players online and you have to at least keep some essential data as up-to-date in DB as possible (say, player's virtual money). Then you will want to separate tables into independent DBs even though they are all "connected".
It really depends. And scaling will be painful whatever you may try to do to avoid it being painful. But if you really expect A LOT of players and updates and data I would advise on thinking twice, thrice and more before settling on a "one DB for several projects" solution.
Yes it will be difficult to manage several DBs probably. But you will have to do this anyway.
真的取决于:)..
问自己这些问题：
是否有我可能想要考虑的可重用性（用户表）？
是否值得将这些实体分开或者它们几乎相同？
这些实体是否有共同的特定事件/需求？
是否值得我花时间和精力来构建 5 个不同的数据库系统（请记住，如果您正在编写游戏，这将意味着不同的连接字符串并且还提供更多的安全性等）。
或者您可以创建一个数据库 OnlineGames 并有一个表来存储游戏名称和类别：
吃豆人街机
塞尔达角色扮演
等。
这实际上取决于您的意图......
Really depends :)..
Ask yourself these questions:
Could there be a resuability (users table) that I may want to think about?
Is it worth seperating these entities or are they pretty much the same?
Do any of these entities share specific events / needs?
Is it worth my time and effort to build 5 different database systems (remember if you are writing the games that would mean different connection strings and also present more security, etc).
Or you could create one database OnlineGames and have a table that stores the game name and a category:
PacMan Arcade
Zelda Role playing
etc etc..
It really depends on what your intentions are...