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

问题描述

我建立一个SaaS应用程序，我们正在讨论每个客户大约一个数据库VS共享数据库。我也读了不少，在所以这里incluisve一些话题，但我还有很多疑惑。

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应该是每一个客户高度定制。 （他们应该能够定制表格和自定义字段添加到现有表）。
在多个数据库的形式给出在这种情况下似乎很大。

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.

问题是。应我的用户表在master数据库或每个客户端数据库？
用户可能有一个或多个组织，所以它会在多个数据库present。
此外，什么样关于国家通用桌桌等？

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.

你是什么opiniion这件事吗？
我的应用程序的主要caracteristic应该是灵活因此，如果客户需要的不仅仅是基本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.

考虑从一个中央数据库设置复制。这将抽空每个用户分区/数据库需要，您无需编写大量code的全局信息。

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