多租户数据库架构 [英] multi-tenant database architecture

问题描述

我正在构建一个 SAAS 应用程序，我们正在讨论每个客户端一个数据库与共享数据库的问题.我已经阅读了很多，包括 SO 上的一些主题，但我仍然有很多疑问.

I am building a SAAS application and we are discussing about one database per client vs shared databases. I have read a lot, incluisve some topics here at SO but I have still many doubts.

我们的平台应该是每个客户都可以高度定制的.(他们应该能够拥有自定义表格并将自定义字段添加到现有表格中).在这种情况下，多数据库方法似乎很棒.

Our plataform should be highly customizable by each client. (they should be able to have custom tables and add custom fields to existing tables). The multiple database aproach seems great in this case.

问题是.我的用户"表应该在主数据库中还是在每个客户端数据库中?一个用户可能有一个或多个组织，因此它会出现在多个数据库中.另外，国家表等通用表怎么样?

The problem is. should my "users" table be in the master database or in each client database?. A user might have one or more organizations, so it would be present in multiple databases. Also, what about generic tables like countries table, etc?

在主数据库中是有意义的.但是我有很多带有 created_by 字段的表，这些表有一个用户的外键.还有一些客户端权限相关的表.

It makes sense to be in the master database. But I have many tables with a created_by field which have a foreign key to the user. Also have some permission related tables by client.

如果有多个数据库，我会失去外键的力量，这意味着对数据库的更多查询.我知道如果数据库位于同一台服务器中，我可以在它们之间使用交叉联接，但随后我失去了可扩展性.(我将来可能需要有多个数据库服务器).我对联合表有意见.不确定性能.

I would loose the power of foreign keys if multiple databases, which means more queries to the database. I know I can use cross-join between databases if they are in the same server but then i loose scalability. (I might need to have multiple database servers in future). I have tought about federated tables. Not sure about performance.

我使用的技术是用于数据库的 php 和 symfony 2 框架和 mysql.

The technologies I am using are php and symfony 2 framework and mysql for the database.

还有，这种系统的维护，我也很担心.我们可以创建一些脚本来自动化所有数据库中的架构更改，但如果我们有 10k 个客户端，那就意味着 10k 个数据库.

Also, I am afraid about the maintenance of such a system. We could create some scripts to automate the schema changes in all databases, but if we have 10k clients that would mean 10k databases.

您对此有何看法?我的应用程序的主要特征应该是灵活性，所以如果客户需要比基础平台没有的更具体的东西，应该可以为他做.

What is your opiniion about this? The main caracteristic of my app should be flexibility so if a client needs something more specific than the base plataform doesnt have, it should be possible to do it for him.

推荐答案

这里有一些经典问题.您去过 http://highscalability.com/ 吗?那里有一些很好的案例研究.

Some classic problems here. Have you ever been to http://highscalability.com/? Some good case studies there.

根据个人经验，如果您尝试在一台服务器上共享客户端，您会发现一个非常成功/活跃的用户会随着时间的推移占用机器的所有资源.我们在 SAAS 中有一个客户端破坏了共享服务器，我们不得不将他转移到其他地方.

From personal experience if you try to share clients on one server, you will find that a very successful/active user will take up all the resources of the machine over time. We had one client in a SAAS that destroyed a shared server and we had to move him somewhere else.

我会把全局枚举撕成一个服务.您可以为国家列表、州列表等内容创建一个中央数据库，并将其置于 Web 服务层之后.同样在该数据库中，您可以让用户管理/管理哪个服务器属于哪个用户等.您可以创建一个管理门户来读取/写入该数据库以管理您的用户群.

I would rip out global enumerations into a service. You can make one central database for things like list of countries, list of states, etc. and put it behind a web service layer. Also in that database you can have user management/managing what server belongs to what user etc. You can make a management portal that reads/writes to this database for managing your user base.

如果我再做一次 SAAS，我会从小处着手，等待疼痛袭来.您真正想要的是在发生扩展问题时解决扩展问题的好工具.准备好一些脚本来在服务器之间进行滚动模式更改(一旦您有多个服务器，就无法避免这种情况).在修改架构时使用脚本来关闭机器.拥有将用户从共享服务器迁移到专用服务器的脚本.

If I was doing a SAAS again, I would start small and wait for the pain to hit. What you really want are good tools to address the scaling issues when they happen. Have some scripts ready to do rolling schema changes across servers (no way to avoid this once you have more than one server). Have scripts to take down machines while you are modifying the schema. Have scripts to migrate a user from a shared server to a dedicated one.

考虑从中央数据库设置复制.这将抽取每个用户分区/数据库需要的全局信息，而无需编写大量代码.

Consider setting up replication from a central database. This would pump down global information that each user partition/database would need without you having to write a lot of code.

但我所见过的最重要的建议 - 并亲身体验 - 不要太努力地建立下一个 Facebook 以扩大规模.从简单开始，看看实际发生了什么，然后再担心主要的可扩展性问题.随着用户群的增长，哪些可以很好地扩展，哪些不能扩展，您可能会感到惊讶.

But the biggest piece of advice I've seen - and experienced first hand - don't try too hard to build the next Facebook for scale. Start simple and see what actually happens before worrying about major scalability issues. You might be surprised as the user base grows what scales well and what does not.

这篇关于多租户数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！