多模式、多租戶數據庫——通過複合主鍵進行數據隔離

我們正在為 SQL Server 2016 設計一個多模式、多租戶數據庫，該數據庫將服務於一個基本的 CRUD 應用程序，該應用程序將看到中小型事務吞吐量並由 15-20 個表組成。為了數據隔離和安全，我們正在探索使用具有[TenantId] [int]和的複合主鍵[TenantIsolationId] [uniqueidentifier]。這兩列將以分層方式在整個表中重複，使用適當的外鍵強制引用完整性，並允許我們強制執行更深層次的行級安全性。

在我讀過的大多數情況下，[TenantIsolatonId]由於性能影響，GUID 的使用似乎存在爭議，尤其是在空間方面。但是，我們認為，對於物理文件分區、資源池委派、複製、可移植性和一般參照完整性的管理，整數和 GUID 的配對將允許在多租戶數據庫中實現更好的隔離和安全性。我們受到一系列安全規定的約束，因此這進一步引導我們走上使用這種複合密鑰類型的道路。

在考慮子組合鍵時，我知道指定事項的列順序。但是，如果我們應該將相同的複合鍵結構提供給錶鍊，或者是否以更傳統的方式將其隔離更好，我似乎無法達成共識。

例如，給定兩個表結構：

CREATE TABLE [Auth].[Tenant] (
   [TenantId] [int] IDENTITY(1,1) NOT NULL
   ,[TenantIsolationId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Tenant_TenantIsolationId] DEFAULT NEWID()
   ,[TenantName] [varchar](256) NOT NULL

   ,CONSTRAINT [PK_Tenant_TenantId_TenantIsolationId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [TenantIsolationId] ASC)
   ,CONSTRAINT [AK_Tenant_TenantName] UNIQUE NONCLUSTERED ([TenantName] ASC)
);

CREATE TABLE [Auth].[User] (
   [UserId] [int] IDENTITY(1,1) NOT NULL
   ,[TenantId] [int] NOT NULL
   ,[TenantIsolationId] [uniqueidentifier] NOT NULL
   ,[FirstName] [varchar](32) NOT NULL
   ,[LastName] [varchar](32) NOT NULL
   ,[UserName] [varchar](64) NOT NULL

   ,CONSTRAINT [PK_User_TenantId_TenantIsolationId_UserId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [TenantIsolationId] ASC, [UserId] ASC)
   ,CONSTRAINT [AK_User_UserName] UNIQUE NONCLUSTERED ([UserName] ASC)
);

如果我們正在執行行級安全性並基於 and 隔離數據[TenantId]，[TenantIsolationId]主[UserId]鍵是在復合鍵中更好地服務，還是單獨提供？

例如：

CREATE TABLE [Auth].[User] (
   [UserId] [int] IDENTITY(1,1) NOT NULL
   ,[TenantId] [int] NOT NULL
   ,[TenantIsolationId] [uniqueidentifier] NOT NULL
   ,[FirstName] [varchar](32) NOT NULL
   ,[LastName] [varchar](32) NOT NULL
   ,[UserName] [varchar](64) NOT NULL

   ,CONSTRAINT [PK_User_UserId] PRIMARY KEY CLUSTERED ([UserId] ASC)
   ,INDEX [IX_User_TenantId_TenantIsolationId] NONCLUSTERED ([TenantId] ASC, [TenantIsolationId] ASC)
   ,CONSTRAINT [FK_Tenant_TenantId_TenantIsolationId] FOREIGN KEY ([TenantId],[TenantIsolationId]) REFERENCES [Tenant]([TenantId],[TenantIsolationId])
   ,CONSTRAINT [AK_User_UserName] UNIQUE NONCLUSTERED ([UserName] ASC)
);

根據我對我所研究的內容的理解，如果我們將始終查找所有三列上的數據，那麼複合鍵只是一個好主意。由於我們想要隔離數據，因此我無法預見我們不想在查找. 然而，也許我誤解了利弊，最好只使用主鍵，再加上針對and的索引。我的想法有問題嗎？[TenantId]``[TenantIsolationId]``[UserId]``[UserId]``[TenantId]``[TenantIsolationId]

我仍處於模式開發的初級階段，因此一旦完成初始草圖，我將使用大量虛擬數據執行大量性能測試。但作為一般做法，在這種情況下推薦什麼？

此外，通常封裝了必須確保高水平數據隔離的多租戶數據庫架構，是否有任何不傾向於使用雙值組合鍵的重大進展？我已經閱讀並觀看了很多關於該主題的文章，主要參考了Salesforce 的 Mulitenant Magic Webinar和Google 的 F1 白皮書。即使在他們那個時代，最近的文章仍然傾向於遵循他們所概述的概念，雖然我正在為一個不會接近 Salesforce 和 AdWords 規模的數據庫建構架構，但我發現自己傾向於他們的原則產生了共鳴。

但作為一般做法，在這種情況下推薦什麼？

Database-per-tenant 是這裡的最佳實踐。在某些情況下它是不切實際的，但應該是您在 SQL Server 上設計任何多租戶系統時的強烈偏好。

每個租戶的數據庫為您提供：

• 出色的安全性和數據隔離，可驗證且易於銷售。
• 最佳性能，沒有共享查詢計劃。
• 根據需要跨多個實例/彈性池的水平可擴展性。
• 性能隔離，可以為租戶選擇隔離的專用資源。
• 每租戶服務，包括升級或修復單個租戶的能力。
• 每租戶備份和恢復，以及可選的區分 HA/DR。
• 每租戶臨時報告。

除此之外：

TenantIsolationID 的意義何在？

您應該在每個包含租戶數據的聚集索引中包含 TenantID。

它應該是前導列，除非您將其用於分區，在這種情況下，它可以是尾隨列。

您必須計劃將單個數據庫拆分為較小的數據庫作為擴展計劃。但是拆分可以是一種單向操作。

TenantID 是 INT 還是 UNIQUEIDENTIFIER 僅與索引大小有關。如果您使用 UNIQUEIDENTIFIER，您的所有二級索引都會更大。但這並不是一個巨大的成本。碎片和頁面拆分在這裡不會有什麼大不了的。有關在表中具有多個插入點的性能影響的詳細資訊，請參閱良好的頁面拆分和順序 GUID 密鑰生成。

我不確定在其中TenantIsolationID放置 UniqueIdentifier 的目的。我建議刪除它或在問題中解釋它的明確目的。如果它有一個可以提供給客戶端的外部 ID，這樣您就不會暴露 IDENTITY 值，那麼它TenantIsolationID只會出現在Tenant表中，而不會出現在其他任何表中。在這種情況下，當有人登錄時，它會被查找一次，並且TenantID應該記憶體查找到的內容，以便從那時起使用。

您最初的直覺是正確的，TenantID應該首先放在聚集索引中（無論是否是 PK）。

有關處理此問題的更多詳細資訊，包括潛在的性能缺陷，請參閱我在 DBA.SE 上對一個非常相似的問題的回答：

多租戶 SQL Server 數據庫中的複合主鍵

關於始終查詢TenantID，甚至是TenantID, TenantIsolationID複合鍵：由於您不希望客戶看到彼此的數據，是的，大多數時候都是這種情況。但是，仍然會有維護操作，例如對超過 X 天/月/年的記錄進行垃圾收集，這些操作不是針對每個租戶的。對於那些您可以使用創建日期的前導列或與特定表相關的任何內容來創建非聚集索引。

引用自：https://dba.stackexchange.com/questions/198406