多租户数据库架构 [英] 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.

我们的plataform应该是高度可定制的每个客户端。 （它们应该能够具有自定义表并向现有表中添加自定义字段）。
在这种情况下，多数据库aproach似乎很棒。

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.

问题是。我的用户表是在主数据库还是在每个客户端数据库？
用户可能有一个或多个组织，因此它会出现在多个数据库中。
此外，对于通用表如countries表等，

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?

在master数据库中是有意义的。但我有很多表有一个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.

您对此有何看法？
我的应用程序的主要特性应该是灵活性，所以如果客户端需要比基本plataform更具体的东西，它应该是可以为他做的。

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屋！