我试图找出设计未来需要水平分区的多租户数据库架构时最好的方法是什么。
数据库中的一些粗略数字。
租户总数约为 10,000 人。每个租户存储的数据量在 500MB -> 500MB 之间变化。 3GB。租户数量一开始会很小，几年后会增长到 10,000 个，因此最初我们可以从单个多租户数据库开始，但从长远来看，这将需要水平扩展以提高性能
原因。
更新 - 一个复杂的因素是，有时租户（公司）可以合并在一起，我也需要支持这一点......，
多租户将使用共享数据库、共享架构架构来实现，如本文所述 http://msdn.microsoft.com/en-us/library/aa479086.aspx
鉴于我们将来将面临水平分区，并且很可能我们会在事情稳定下来之前将客户端从一个数据库移动到另一个数据库多次我认为最好使用 GUID 作为每个表上的主键以及唯一的tenantID 列。
我知道使用 GUID 作为主键会带来性能开销，但这是否是我需要接受的权衡？未来是否有另一种方式来设计水平分区？
这是一个例子 - 假设我想在未来合并租户为 100 和 200 的公司，
如果 PK 是整数，则当我将行从数据库 2 复制到数据库 1 时可能会发生冲突，使用 {guid} 我保证不会发生冲突...
数据库 1 数据库 2
租户ID、ID、描述 租户ID、ID、描述
100 ,1 ,'富' 200 ,1 ,'xxx'
100 ,2 , 'boo' 200 ,2 , 'yyy'
数据库 1 数据库 2
租户ID、ID、描述 租户ID、ID、描述
100 ,{aaa} , 'foo' 200 ,{ccc} , 'xxx'
100 ,{bbb} , '嘘' 200 ,{ddd} , 'yyy'
I’m trying to figure out what the best approach is when designing a multi tenant database schema that will need to be horizontally partitioned in the future.
Some Rough Numbers on the database..
Total number of tenants will be approx 10,000. The amount of data stored per tenant varies between 500MB -> 3GB. The number of tenants will start off small and grow to 10,000 over a few years so initially we can start with a single multi tenant database but in the longer term this will need to scale horizontally for performance
reasons.
Update - a complicating factor is that occasionally tenants (companies) can merge together and I need to support this as well...,
The multi tenancy will be implemented using a Shared Database, Shared Schema architecture as described in this paper http://msdn.microsoft.com/en-us/library/aa479086.aspx
Given that we’ll be faced with horizontally partitioning in the future and that it’s likely that we’ll be moving clients from one database to another several times before things settle down I think that it’s best to use GUID’s as the primary keys on every table along with a unique tenantID column.
I know that there is a performance overhead in using GUID’sas a primary keybut is this a trade off that I just need to accept? Is there another way to design for horizontal partitioning in the future ??
Heres an example - lets say I want to merge companies with tenants 100 and 200 in the future,
if the PK is an integer there may be a collision when I copy the rows fromn database 2 to database 1, with {guids} i am guaranteed there wont be a collision...
database 1 database 2
tenantid, id, description tenantid, id, description
100 ,1 , 'foo' 200 ,1 , 'xxx'
100 ,2 , 'boo' 200 ,2 , 'yyy'
database 1 database 2
tenantid, id, description tenantid, id, description
100 ,{aaa} , 'foo' 200 ,{ccc} , 'xxx'
100 ,{bbb} , 'boo' 200 ,{ddd} , 'yyy'
发布评论
评论（3）
GUID 似乎是主键的自然选择 - 如果您确实必须这样做，您可能会争论将其用作表的主键。我强烈建议不要这样做，即使用 GUID 列作为聚集键，这是 SQL Server 默认执行的操作，除非您明确告诉它不要这样做。
您确实需要区分两个问题：
1）主键是一个逻辑构造 - 唯一且可靠地标识表中每一行的候选键之一。实际上，这可以是任何东西 - INT、GUID、字符串 - 选择对您的场景最有意义的。
2）聚集键（定义表上“聚集索引”的一个或多个列） - 这是一个物理与存储相关的东西，这里是一个小的、稳定、不断增加的数据类型是您的最佳选择 - INT 或 BIGINT 作为您的默认选项。
默认情况下，SQL Server 表上的主键也用作聚簇键 - 但不必如此！我个人看到，将以前基于 GUID 的主键/聚集键分解为两个单独的键 - GUID 上的主（逻辑）键和单独的 INT IDENTITY(1, 1）专栏。
正如金伯利·特里普 - 索引女王 - 和其他人已经说过很多次 - GUID 作为集群键并不是最佳的，因为由于其随机性，它将导致大量页面和索引碎片并且表现普遍不佳。
是的，我知道 - SQL Server 2005 及更高版本中有
newsequentialid()- 但即便如此，它也不是真正完全顺序的，因此也遇到了与 GUID 相同的问题 - 只是稍微不那么突出，所以。然后还有另一个问题需要考虑：表上的聚集键也将添加到表上每个非聚集索引的每个条目中 - 因此您确实希望确保它尽可能小。通常，具有 2+ 十亿行的 INT 对于绝大多数表来说应该足够了 - 与作为集群键的 GUID 相比，您可以在磁盘和服务器内存中节省数百兆字节的存储空间。
快速计算 - 使用 INT 与 GUID 作为主键和聚集键：
总计：25 MB 与. 106 MB - 而且仅在一个表上！
还有一些值得深思的东西 - Kimberly Tripp 写的很棒的东西 - 读它，再读它，消化它！这确实是 SQL Server 索引的福音。
Marc
GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.
You really need to keep two issues apart:
1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.
2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.
By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.
As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.
Yes, I know - there's
newsequentialid()in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.
Quick calculation - using INT vs. GUID as Primary and Clustering Key:
TOTAL: 25 MB vs. 106 MB - and that's just on a single table!
Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.
Marc
这里有两件事需要注意：
第 2 点是主键。
使用 GUID 来识别租户非常有用，因为您无法猜测另一个租户的 ID（就像您按照白皮书使用 IDENTITY 一样）列。但是聚集键的 GUID 是一个坏主意（根据 marc_s 的回答）。
这导致 GUID 和 IDENTITY 列的复合 PK，可能
这应该是一个合理的折衷方案来覆盖该表的大多数查询模式和 FK。
当然，这取决于最终的设计：我在这里假设这是某种“事实”或“事实的父项”表
There are 2 things of note here:
Point 2 is the primary key.
Using a GUID to identify a tenant is useful because you can't guess back to another tenant's ID (like if you use IDENTITY as per that whitepaper) column. But a GUID for the clustered key is a bad idea (as per marc_s' answer).
This leads to a composite PK of GUID and an IDENTITY column, probably
This should be a reasonable compromise to cover most query patterns and FKs of this table.
Of course, it depends on the final design: I've assumed here that this is some kind of "fact" or "parent of fact" table
您是否考虑过 SQL Azure 联合？横向扩展水平分区，包括支持开箱即用的重新平衡操作（即跨分区移动租户）以及提供高可用性解决方案的服务。 SQL Server 盒装产品与此完全不同。请参阅在 SQL Azure 中引入联合 或 如何使用 SQL Azure 进行分片。
至于让每个实体PK成为GUID的问题，我真的不明白有什么意义。让每个租户数据表都以租户 ID 为前缀是的，绝对可以。使所有租户数据聚集索引都以
tenantId作为最左边的键：(tenantId, key, key, key))：同上，必须的。将实体主键设为(tenantId,entityId)：很有可能。但是将entityId设为 guid 呢？我真的不明白为什么。除非您在租户之间共享实体，否则tenantId就像entityId所应用的命名空间一样。跨分片移动数据是可以的，即使它会导致重复entityId值，因为实体 PK 范围由tenantId< /代码>。Have you considered SQL Azure Federations instead? Scale out horizontal partitioning that includes support for re-balancing operations out-of-the-box (ie. move tenants across partitions), and a service provided high-availability solution. there is simply nothing like on the SQL Server box product. See Introducing Federation in SQL Azure or How to Shard with SQL Azure.
As to the question of making every entity PK be a GUID, I really don't see the point. Making every tenant data table be prefixed with a tenant ID yes, absolutely. Making all tenant data clustered index have the
tenantIdas the leftmost key:(tenantId, key, key, key)): ditto, a must. Making the entity primary key be(tenantId, entityId): very much likely. But making theentityIda guid? I really don't see why. Unless you have entities shared between tenants, thetenantIdacts like a namespace into which theentityIdapplies. Moving data across shards is fine, even if it leads to duplicateentityIdvalues, since the entities PK are scoped by thetenantId.