构建数据库以进行扩展的最佳方法
我正在开发一个项目，该项目可能拥有大量用户，每个用户都将管理自己独特的数据集。我认为数据可以通过两种方式之一存储。
1) 为每个用户创建一个完全不同的数据库，以便他们的数据与其他人完全分开
2) 共享同一个数据库中的数据，并使用 user_id 字段在查询级别将其隔离。
每个用户的架构始终是相同的。
最主要的是系统需要能够扩展，我不确定是否拥有数千个不同的数据库，或者在同一个表中存储数百万条记录是否会更好地扩展。
我很想听听过去处理过这种情况的人的意见，以及这两种选择可能存在的陷阱。
I am working on a project that has the potential to have a large number of users each of which will be managing their own unique data sets. I am thinking the data can be stored in one of two ways.
1) Create a completely different database for each user so that their data is fully separate from everyone elses
2) Share the data in the same database, and segregate it at the query level using a user_id field.
The schema will always be identical for each user.
The main thing is that the system will need to be able to scale, and I am not sure if having potentially several thousand different databases, or storing millions of records in the same tables would scale better.
I am interested in hearing from anyone who has dealt with this kind of situation in the past and what pitfalls might be out there with either option.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
除了您已经确定的扩展方面之外，还有一些其他问题可能会促使您做出决定 - “大量用户”也可能意味着您最好澄清的数字范围。
其他操作问题：
安全性 - 依赖于代码中的 user_id 字段依赖于不存在允许用户查看/操纵其他用户数据的错误或缺陷。
升级 - 双向，但您可以一次升级所有人（单个数据库）或通过拆分 - 允许自己在不同时间升级不同的用户组。
备份/恢复 - 根据恢复要求和 SLA，您可能会发现，在备份/恢复方面，让每个人都在一个数据库中会产生太多问题。如果单个客户端想要恢复其数据，则与所有其他客户端的数据组合时的操作开销并非微不足道。同样，拥有大量数据库 = 大量单独的备份。
可扩展性 - 能够将不同用户的数据库放置在单独的服务器上可以帮助扩展，而不需要大型数据库服务器。但同样，这是管理开销。
应用程序及其数据源的多租户并不是一个简单的问题/答案 - 详细了解在这种情况下有多少用户可能是“大”，并结合操作问题为您提供指导。
In addition to the scaling aspect that you have already identified, there are a few other concerns which may drive your decision - the 'large number of users' can also mean such a range of numbers that you would be best to clarify.
Other operational concerns:
Security - relying on a user_id field within your code relies on there being no error or flaw that allows a user to see / maniuplate other user's data.
Upgrades - goes both ways, but you either upgrade everyone at once (single DB) or by splitting - allow yourself to upgrade diffent sets of users at different times.
Backup / Restore - depending on the restore requirements and SLAs, you may find that having everyone in a single database creates too much of a problem when it comes to backup / restore. If a single client wants to restore their data, the operational overhead when it is combined with all the other client's data is not trivial. Equally, having lots of databases = lots of seperate backups.
Scalability - having the ability to place different user's databases on seperate servers can aid scale, instead of requiring a big iron DB server. But again, that is a management overhead.
Multi-tenancy of an application and it's data source is not an easy question / answer - understanding more about how many users is 'large' in this case might be, combined with the operational concerns provide you guidance.
选项 2 应该是您最好的选择。数据库通常设计用于处理数百万行和大量数据。因此，只要您正确设计架构并具有适当的索引、填充因子等，选项 2 将引导您实现所需的扩展。正如 DarthVader 所说，了解更多有关数据库设计的知识。
Option 2 should be your best bet. Databases are usually designed to work with millions and millions of rows and a lots of data. So, as long as you design your schema correctly and have proper indexes, fill factors etc., option 2 will lead you to the scaling that you are looking for. As DarthVader said, learn more about database design.
不要为每个用户创建单独的数据库。那不好。
如果你将拥有数百万用户怎么办？
为属于同一上下文的用户和实体创建表。你无法像这样扩展应用程序。在了解可扩展性之前。您需要了解数据库设计以及数据库如何工作。
Dont create seperate database for each user. That s not good.
What if you will have million users?
Create table for users and entities that belong to same context. you cant scale applications like that. and before learning about scalability. you need to learn about database design and how databases works.