多租户主键的最佳方法

Question

我有一个数据库被多个客户使用。我不想让代理增量键值在客户之间混淆。我希望编号从1开始，且是特定于客户的。

我将使用tenant_id和递增id的两部分组成的复合键。

创建每个租户的递增键的最佳方法是什么？

我正在使用SQL Server Azure。我担心锁定表、重复键等问题。通常我会将主键设置为IDENTITY并继续进行。

谢谢

- Paul Deen

---

“我不希望代理增量键值在客户端之间混淆…” 我很好奇，为什么您不希望这种情况发生？ - Michael Fredrickson

主要是因为这会让人们了解我正在处理多少行数据，在我的理想世界里，它们应该完全彼此隔离。 - Paul Deen

实际上，您可以同时拥有代理主键以确保参照完整性（对客户隐藏）和双列唯一索引，例如：（Cust_id，Order_Number）。 - alexm

非常正确。我会再考虑一下。不确定是否完全正确，但绝对是一个好主意。谢谢。 - Paul Deen

您也可以使用GUID（全局唯一标识符）/UUID（通用唯一标识符），而不会在键值之后留下任何隐含的含义。 - Glenn

如果键是按租户分段的，它们就不再是代理键了，您也不需要租户ID列。如果它是一个代理键，最好永远不要向最终用户显示它。 - Larry Lustig

Answer 1

你是否计划在未来使用SQL Azure联合？如果是这样，当前版本的SQL Azure联合不支持将IDENTITY作为聚集索引的一部分。有关详细信息，请参见此链接。

如果你还没有看过联合，请查看一下，因为它提供了一种有趣的方式来分片数据库并在数据库内进行租户隔离。

根据你的最终目标，使用联合，你可能能够在表上使用GUID作为主要聚集索引，并且还可以在表上使用增量INT IDENTITY字段。这个INT IDENTITY字段可以显示给最终用户。如果你在TenantID上进行联合，每个“租户表”实际上就成为一个独立的存储空间（至少我是这么理解的），因此在该表中使用IDENTITY字段将有效地生成一个递增的自动生成值，在给定的租户内递增。

当数据合并在一起时（从多个租户组合数据），你会在这个INT IDENTITY字段上遇到冲突（这就是为什么IDENTITY不支持联合中作为主键的原因），但只要你不在整个系统中使用这个字段作为唯一标识符，你应该没问题。

- Tim Lentine

Answer 2

如果您想要复制在插入时自动分配唯一INT键的便利性，可以添加一个INSTEAD OF INSERT触发器，该触发器使用现有列的MAX值+1来确定下一个值。

如果具有标识值的列是索引中的第一个键，则MAX查询将是简单的索引查找，非常高效。

事务将确保分配唯一值，但此方法将具有与标准标识列不同的锁定语义。如果我没记错的话，SQL Server可以为每个并行请求它的事务分配不同的标识值，如果事务回滚，则分配给它的值将被丢弃。 MAX方法只允许一个事务同时向表中插入行。

相关方法可能是拥有一个专用的键值表，由表名、租户ID和当前标识值组成。它将需要相同的INSTEAD OF INSERT触发器以及更多的样板文件来查询和更新该关键表。它不会改善并行操作；锁定只是在不同表的记录上。

解决锁定瓶颈的一种可能性是在键的值中包含当前的 SPID（现在身份标识键是顺序 int 和任何分配它的 SPID 的组合，而不仅仅是顺序），使用专用的身份值表，并根据需要在其中插入每个 SPID 的记录；身份表的主键将是（表名、租户、SPID），并具有当前顺序值的非键列。这样，每个 SPID 将拥有自己动态分配的身份池，并且只会锁定自己的 SPID 特定记录。

另一个缺点是维护触发器，必须在更改任何特殊身份表的列时进行更新。

- Chris Smith