如何创建具有共享表结构的多租户数据库？ [英] How to create a multi-tenant database with shared table structures?
问题描述
到目前为止，我已经看到三个选项：
- 多数据库（每个租户都拥有自己的 - 与每个客户的服务器几乎相同）
- 模式（在MySQL中不可用，每个租户在共享数据库中获取自己的模式）
- 共享模式（我们当前的方法，也许在每个列上都有其他标识记录）
多模式是我最喜欢的（考虑成本）。但是，创建一个新账户并进行迁移似乎相当痛苦，因为我必须迭代所有模式并更改其表/列/定义。
Q：多模式似乎设计为每个租户的表格略有不同 - 我不想这样做。有没有RDBMS允许我使用多模式多租户解决方案，其中所有租户之间共享表格结构？
P.S。多点我的意思是像超多（10.000+的租户）。
一些b $ b课程的公司担心他们的数据可能会损失
，所以我们正在评估
其他解决方案。
这是不幸的，因为客户有时会误以为只有物理隔离可以提供足够的安全性。
有一个有趣的MSDN文章，标题为多租户数据架构，您可能需要检查。这是作者如何解决对共同方法的误解：
一个常见的误解是，
只有物理隔离才能提供
适当的安全级别。在
的事实中，使用共享
方法存储的数据也可以提供强大的数据
安全性，但需要使用更多
复杂的设计模式。
关于技术和业务考虑，本文简要分析了某种方法可能比另一种方法更合适：
您希望服务的
租户的数量，性质和需求都会以
的不同方式影响
您的数据架构决策。以下
的一些问题可能会使您偏向更多
的隔离方法，而其他人可能会将
偏向于更为共享的
方法。
您希望定位多少潜在租户？您可能无处可以估计
预期使用权限，但
以数量级计算：
您正在为
建立一个应用程序的租户？数千？十万美元b $ b千更多？
越大，您的租户基数就越多，
更可能要考虑
a更多的共享方式。
- <您希望平均租户的数据占用多少存储空间？
如果您希望一些或所有租户
存储非常大量的数据，那么
单独数据库方法最好是
。 （实际上，数据存储
的要求可能会迫使您采用
的独立数据库模型，如果是这样，
将更容易设计
应用程序，
开始比稍后移动到
单独数据库方法。）
您期望平均的并发最终用户数量租客支持？
数量越大，
越适合一个更为孤立的方法
将满足最终用户的需求。
您是否希望提供任何租户的增值服务，例如租户备份和
能力的
？这样的服务通过一个更加孤立的
方法来提供更简单的
。
更新：进一步了解预期的租户人数。
对于大多数（如果不是全部）情况，预期的租户人数（10k）应排除多数据库方法。我不认为你会想到维护10,000个数据库实例的想法，每天都要创建数百个新的数据库实例。
从该参数中，它看起来像共享数据库一样，单模式方法是最合适的。事实上，您将每个租户存储大约50Mb，而且不会有租户附加组件，这样做更合适。
上面引用的MSDN文章提到了解决共享数据库方法的安全考虑的三种安全模式：
当您对应用程序的数据安全措施很满意时，您将能够为您的客户提供服务级别构建，提供强大的数据安全保障。在您的SLA中，除了担保之外，您还可以描述您将采取的措施，以确保数据不受影响。
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:
- Multi-Database (each tenant gets its own - nearly the same as 1 server per customer)
- Multi-Schema (not available in MySQL, each tenant gets its own schema in a shared database)
- Shared Schema (our current approach, maybe with additional identifying record on each column)
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.
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?
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.
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.
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.
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.
The MSDN article cited above mentions three security patterns that tackle security considerations for the shared-database approach:
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.
这篇关于如何创建具有共享表结构的多租户数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
