mysql单表多租户架构,如何创建具有共享表结构的多租户数据库？

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.