如何创建具有共享表结构的多租户数据库?
我们的软件目前在 MySQL 上运行.所有租户的数据都存储在同一个模式中.由于我们使用的是 Ruby on Rails，因此我们可以轻松确定哪些数据属于哪个租户.但是，当然有些公司担心他们的数据可能会受到损害，因此我们正在评估其他解决方案.
Our software currently runs on MySQL. The data of all tenants is stored in the same schema. Since we are using Ruby on Rails we can easily determine which data belongs to which tenant. However there are some companies of course who fear that their data might be compromised, so we are evaluating other solutions.
到目前为止，我看到了三个选项:
So far I have seen three options:
- 多数据库(每个租户都有自己的 - 几乎相当于每个客户 1 个服务器)
- 多架构(在 MySQL 中不可用，每个租户在共享数据库中都有自己的架构)
- 共享架构(我们目前的方法，可能每列都有额外的识别记录)
多架构是我最喜欢的(考虑到成本).然而，创建一个新帐户并进行迁移似乎非常痛苦，因为我必须遍历所有模式并更改它们的表/列/定义.
Multi-Schema is my favourite (considering costs). However creating a new account and doing migrations seems to be quite painful, because I would have to iterate over all schemas and change their tables/columns/definitions.
问: 多架构似乎旨在为每个租户提供略有不同的表 - 我不想要这样.是否有任何 RDBMS 允许我使用多模式多租户解决方案，其中表结构在所有租户之间共享?
Q: Multi-Schema seems to be designed to have slightly different tables for each tenant - I don't want this. Is there any RDBMS which allows me to use a multi-schema multi-tenant solution, where the table structure is shared between all tenants?
附言多我指的是超多(10.000 多个租户)之类的东西.
P.S. By multi I mean something like ultra-multi (10.000+ tenants).
推荐答案
但是有一些公司当然谁担心他们的数据可能会受到损害，所以我们正在评估其他解决方案.
However there are some companies of course who fear that their data might be compromised, so we are evaluating other solutions.
这很不幸，因为客户有时会误以为只有物理隔离才能提供足够的安全性.
This is unfortunate, as customers sometimes suffer from a misconception that only physical isolation can offer enough security.
有一篇有趣的 MSDN 文章，标题为 多租户数据架构，您可能想检查一下.这就是作者如何解决对共享方法的误解:
There is an interesting MSDN article, titled Multi-Tenant Data Architecture, which you may want to check. This is how the authors addressed the misconception towards the shared approach:
一个普遍的误解认为只有物理隔离才能提供适当的安全级别.在事实上，使用共享存储的数据方法还可以提供强有力的数据安全，但需要使用更多复杂的设计模式.
A common misconception holds that only physical isolation can provide an appropriate level of security. In fact, data stored using a shared approach can also provide strong data safety, but requires the use of more sophisticated design patterns.
关于技术和业务方面的考虑，本文简要分析了某种方法可能比另一种方法更合适的地方:
As for technical and business considerations, the article makes a brief analysis on where a certain approach might be more appropriate than another:
的数量、性质和需求您希望为所有影响服务的租户您的数据架构决策不同的方式.以下一些问题可能会使您偏向于更多孤立的方法，而其他人可能偏向于更共享的方法.
The number, nature, and needs of the tenants you expect to serve all affect your data architecture decision in different ways. Some of the following questions may bias you toward a more isolated approach, while others may bias you toward a more shared approach.
您希望定位多少潜在租户?你可能无处可去接近能够估计授权的预期用途，但从数量级考虑:您正在构建一个应用程序数百名租户?几千?十数以千计?更多的?你越大希望您的租户基础是你更有可能考虑一种更共享的方法.
How many prospective tenants do you expect to target? You may be nowhere near being able to estimate prospective use with authority, but think in terms of orders of magnitude: are you building an application for hundreds of tenants? Thousands? Tens of thousands? More? The larger you expect your tenant base to be, the more likely you will want to consider a more shared approach.
您预计租户的数据平均占用多少存储空间?如果您希望部分或所有租户存储大量数据，单独的数据库方法可能是最好的事物.(确实，数据存储要求可能会迫使您采用无论如何，单独的数据库模型.如果是这样的话，设计起来会容易得多应用程序从开始比移动到稍后使用单独的数据库方法.)
How much storage space do you expect the average tenant's data to occupy? If you expect some or all tenants to store very large amounts of data, the separate-database approach is probably best. (Indeed, data storage requirements may force you to adopt a separate-database model anyway. If so, it will be much easier to design the application that way from the beginning than to move to a separate-database approach later on.)
您希望普通租户支持多少并发最终用户?数字越大，越多适当的更孤立的方法将满足最终用户的要求.
How many concurrent end users do you expect the average tenant to support? The larger the number, the more appropriate a more isolated approach will be to meet end-user requirements.
您是否希望提供任何按租户的增值服务，例如作为每个租户的备份和恢复能力?这样的服务更容易通过一个更孤立的提供方法.
Do you expect to offer any per-tenant value-added services, such as per-tenant backup and restore capability? Such services are easier to offer through a more isolated approach.
<小时>
更新:关于预期租户数量的进一步更新.
UPDATE: Further to update about the expected number of tenants.
对于大多数情况(如果不是所有情况)，预期的租户数量 (10k) 应排除多数据库方法.我认为您不会喜欢维护 10,000 个数据库实例并且每天必须创建数百个新实例的想法.
That expected number of tenants (10k) should exclude the multi-database approach, for most, if not all scenarios. I don't think you'll fancy the idea of maintaining 10,000 database instances, and having to create hundreds of new ones every day.
仅从该参数来看，似乎共享数据库、单模式方法是最合适的.事实上，您将为每个租户存储大约 50Mb，并且没有每个租户的附加组件，这使得这种方法更加合适.
From that parameter alone, it looks like the shared-database, single-schema approach is the most suitable. The fact that you'll be storing just about 50Mb per tenant, and that there will be no per-tenant add-ons, makes this approach even more appropriate.
上面引用的 MSDN 文章提到了解决共享数据库方法安全注意事项的三种安全模式:
The MSDN article cited above mentions three security patterns that tackle security considerations for the shared-database approach:
- 可信数据库连接
- 租户视图过滤器
- 租户数据加密
当您对应用程序的数据安全措施有信心时，您就可以为您的客户提供服务级别提供强大数据安全保证的协议.在您的 SLA 中，除了保证之外，您还可以描述您将采取哪些措施来确保数据不受损害.
When you are confident with your application's data safety measures, you would be able to offer your clients a Service Level Agrement that provides strong data safety guarantees. In your SLA, apart from the guarantees, you could also describe the measures that you would be taking to ensure that data is not compromised.
更新 2: 显然，微软的人移动/制作了一篇关于这个主题的新文章，原来的链接不见了，这是新的:多租户 SaaS 数据库租赁模式(感谢 Shai Kerer)
UPDATE 2: Apparently the Microsoft guys moved / made a new article regarding this subject, the original link is gone and this is the new one: Multi-tenant SaaS database tenancy patterns (kudos to Shai Kerer)
相关文章