SQL Server-是基于GUID的PK最佳实践，可支持基于租户的水平分区 [英] SQL Server -is a GUID based PK the best practice to support tenant based horizontal partitioning
问题描述
在设计将来需要水平划分的多租户数据库架构时，我试图找出最好的方法.
数据库上有一些粗糙数字.
租户总数约为10,000.每个租户存储的数据量在500MB-> 3GB之间变化.租户的数量开始时将很小，并在几年内增长到10,000，因此最初我们可以从单个多租户数据库开始，但从长远来看，这将需要水平扩展以提高性能 原因.
更新-一个复杂的因素是，租户(公司)有时可以合并在一起，而我也需要对此进行支持...
如本文所述，将使用共享数据库，共享架构体系结构实现多租户解决方案
GUID似乎是主键的自然选择-如果确实需要，您可能会争辩说将其用于表的PRIMARY KEY .我强烈建议不要做，是使用GUID列作为集群键，默认情况下，SQL Server会这样做，除非您明确指示不要这样做.
您确实需要分开两个问题:
1)主键是一种逻辑结构-候选键之一，可唯一且可靠地标识表中的每一行.确实可以是任何东西-一个INT，一个GUID，一个字符串-选择最适合您的情况的
2)集群键(在表上定义集群索引"的一列或多列)-这是与存储相关的物理事物，在这里，稳定，不断增长的小型数据类型是您的最佳选择-INT或BIGINT作为默认选项.
默认情况下，SQL Server表上的主键也用作群集键-但这不是必须的！当我将以前基于GUID的主键/集群键分解为两个单独的键时，我个人已经看到了巨大的性能提升-GUID上的主(逻辑)键和单独的INT IDENTITY(1， 1)栏.
为金伯利Tripp -索引女王-其他人已经说了很多次-GUID并不是最佳的聚类键，因为它的随机性会导致大量的页面和索引碎片，而且通常很糟糕性能.
是的，我知道-SQL Server 2005及更高版本中有newsequentialid()-但这甚至不是真正且完全顺序的，因此也遇到了与GUID相同的问题-不太明显.
然后还有一个要考虑的问题:表上的聚簇键也将添加到表上每个非聚簇索引的每个条目中，因此，您真的要确保它尽可能小.通常，具有2+十亿行的INT应该足以容纳绝大多数表-与GUID作为集群键相比，您可以在磁盘和服务器内存中节省数百兆的存储空间.
快速计算-使用INT vs. GUID作为主键和聚类键:
- 具有1 000 000行的基本表(3.8 MB与15.26 MB)
- 6个非聚集索引(22.89 MB与91.55 MB)
总计:25 MB和106 MB -只是在一张桌子上！
还有更多值得深思的东西-金伯利·特里普(Kimberly Tripp)的优秀著作-读它，再读一次，消化它！确实，这是SQL Server索引的福音.
马克
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'
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:
- Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
- 6 nonclustered indexes (22.89 MB vs. 91.55 MB)
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.
- GUIDs as PRIMARY KEY and/or clustered key
- The clustered index debate continues
- Ever-increasing clustering key - the Clustered Index Debate..........again!
- Disk space is cheap - that's not the point!
Marc
这篇关于SQL Server-是基于GUID的PK最佳实践，可支持基于租户的水平分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
