複合主鍵中定義的列的非聚集索引

我有一個多租戶數據庫，我在其中利用AccountId所有表中的列作為複合主鍵的一部分來進行租戶隔離。在作為複合主鍵的一部分的每個列上創建額外的非聚集索引以幫助 SQL Server 在加入查找表時維護準確的統計資訊並提高查詢性能是否有益？

例如，在一個關聯表中，該表定義了 an 和他們在其中設有辦事處Account的美國人之間的一對多關係，理論上，考慮到以下結構和範例查詢，這兩個選項中的哪一個更可取？State

創建Account和State表並填充範例數據。

DROP TABLE IF EXISTS [dbo].[Account];
DROP TABLE IF EXISTS [dbo].[State];

-- [Account] table and sample values.
IF OBJECT_ID('[dbo].[Account]', 'U') IS NULL
BEGIN
   CREATE TABLE [dbo].[Account] (
       [AccountId] [int] IDENTITY(1,1) NOT NULL
       ,[AccountAlias] [varchar](3) NOT NULL
       ,[AccountName] [varchar](128) NOT NULL
       ,CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ([AccountId] ASC)
       ,CONSTRAINT [UQ_Account_Alias] UNIQUE NONCLUSTERED ([AccountAlias] ASC)
       ,CONSTRAINT [UQ_Account_Name] UNIQUE NONCLUSTERED ([AccountName] ASC)
   );

   SET IDENTITY_INSERT [dbo].[Account] ON;

   INSERT INTO [dbo].[Account] ([AccountId], [AccountAlias], [AccountName])
   VALUES (1, 'SA1', 'Sample Account 1'), (2, 'SA2', 'Sample Account 2'), (3, 'SA3', 'Sample Account 3')

   SET IDENTITY_INSERT [dbo].[Account] OFF;
END;
GO

-- [State] table and sample values.
IF OBJECT_ID('[dbo].[State]', 'U') IS NULL
BEGIN
   CREATE TABLE [dbo].[State] (
       [StateId] [tinyint] IDENTITY(1,1) NOT NULL
       ,[StateCode] [varchar](2) NOT NULL
       ,[StateName] [varchar](32) NOT NULL
       ,CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED ([StateId] ASC)
       ,CONSTRAINT [UQ_State_Code] UNIQUE NONCLUSTERED ([StateCode] ASC)
       ,CONSTRAINT [UQ_State_Name] UNIQUE NONCLUSTERED ([StateName] ASC)
   );

   SET IDENTITY_INSERT [dbo].[State] ON;

   INSERT INTO [dbo].[State] ([StateId], [StateCode], [StateName])
   VALUES (1, 'AL', 'Alabama'), (2, 'AK', 'Alaska'), (3, 'AZ', 'Arizona'), (4, 'AR', 'Arkansas'), (5, 'CA', 'California')

   SET IDENTITY_INSERT [dbo].[State] OFF;
END;
GO

創建AccountState選項 1 - 僅複合主鍵

DROP TABLE IF EXISTS [dbo].[AccountState];

IF OBJECT_ID('[dbo].[AccountState]', 'U') IS NULL
BEGIN
   CREATE TABLE [dbo].[AccountState] (
       [AccountId] [int] NOT NULL
       ,[StateId] [tinyint] NOT NULL
       ,CONSTRAINT [PK_AccountState] PRIMARY KEY CLUSTERED ([AccountId] ASC, [StateId] ASC)
       ,CONSTRAINT [FK_AccountState_Account] FOREIGN KEY ([AccountId]) REFERENCES [dbo].[Account]([AccountId])
       ,CONSTRAINT [FK_AccountState_State] FOREIGN KEY ([StateId]) REFERENCES [dbo].[State]([StateId])
   );

   INSERT INTO [dbo].[AccountState] ([AccountId], [StateId])
   SELECT A.[AccountId], S.[StateId]
   FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
   WHERE A.[AccountId] = 1 AND S.[StateId] IN (1, 2, 3)
   UNION
   SELECT A.[AccountId], S.[StateId]
   FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
   WHERE A.[AccountId] = 2 AND S.[StateId] IN (3, 4, 5)
   UNION
   SELECT A.[AccountId], S.[StateId]
   FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
   WHERE A.[AccountId] = 3 AND S.[StateId] IN (1, 3, 5)
END;
GO

創建AccountState選項 2 - 複合主鍵 + 非聚集索引

DROP TABLE IF EXISTS [dbo].[AccountState];

IF OBJECT_ID('[dbo].[AccountState]', 'U') IS NULL
BEGIN
   CREATE TABLE [dbo].[AccountState] (
       [AccountId] [int] NOT NULL
       ,[StateId] [tinyint] NOT NULL
       ,CONSTRAINT [PK_AccountState] PRIMARY KEY CLUSTERED ([AccountId] ASC, [StateId] ASC)
       ,CONSTRAINT [FK_AccountState_Account] FOREIGN KEY ([AccountId]) REFERENCES [dbo].[Account]([AccountId])
       ,CONSTRAINT [FK_AccountState_State] FOREIGN KEY ([StateId]) REFERENCES [dbo].[State]([StateId])
       ,INDEX [IX_AccountState_Account] NONCLUSTERED ([AccountId])
       ,INDEX [IX_AccountState_State] NONCLUSTERED ([StateId])
   );

   INSERT INTO [dbo].[AccountState] ([AccountId], [StateId])
   SELECT A.[AccountId], S.[StateId]
   FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
   WHERE A.[AccountId] = 1 AND S.[StateId] IN (1, 2, 3)
   UNION
   SELECT A.[AccountId], S.[StateId]
   FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
   WHERE A.[AccountId] = 2 AND S.[StateId] IN (3, 4, 5)
   UNION
   SELECT A.[AccountId], S.[StateId]
   FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
   WHERE A.[AccountId] = 3 AND S.[StateId] IN (1, 3, 5)
END;
GO

範例查詢

SELECT
   A.[AccountName]
   ,S.[StateName]
FROM
   [dbo].[AccountState] A_S
   JOIN [dbo].[Account] A
       ON A_S.[AccountId] = A.[AccountId]
   JOIN [dbo].[State] S
       ON A_S.[StateId] = S.[StateId]
WHERE
   S.[StateCode] = 'CA'

在這兩個選項中，哪種類型的指數組合最適合擴展？僅複合主鍵或複合主鍵加上額外的非聚集索引？還是有其他更可行的選擇？

我不確定範例查詢是否是作為推薦依據的公平範例。範例查詢不是典型的多租戶應用程序查詢，因為它不是特定於特定客戶端的。它更多的是尋求了解所有（或至少多個）客戶的支持或管理查詢。當然，它也可能與維護相關（例如垃圾收集會尋找最舊的日期而不關心AccountId）。所以讓我們把它分開：

1. 一般的

我認為擁有一個僅作為聚集索引中前導/最左側鍵列的非聚集索引沒有任何好處。聚集索引已按該順序排列，因此存在統計資訊。因此，IX_AccountState_Account選項 2 中的索引純粹是浪費，對系統造成拖累。 2. 支持/管理/維護查詢

這些查詢，尤其是維護查詢，可以跨AccountId值工作。因此，某些查詢肯定會受益於非聚集索引鍵列上的非聚集索引*（AccountId或更一般地說：不是最左邊/前導鍵列）。這假設您有僅對*實體 ID 進行過濾/排序的查詢。沒有這樣的查詢意味著您可能不需要此索引。 3. 應用程序查詢

這些查詢應始終包含AccountId，因此我看不出它們將如何從僅基於實體 ID 的索引中受益。

雖然我曾在與您所描述的類似的多租戶系統上工作，但我只是想到了以前從未發生過的事情：因為聚集索引統計資訊基於最左邊/前導列而不是鍵列的組合，為這些對象手動創建統計資訊*可能會有​​所幫助。*我似乎記得只是密度部分將解釋所有關鍵列（不僅僅是前導列），但這可能有助於查詢優化。這需要測試，因為我還沒有嘗試過（而且我很快就無法進行這樣的測試）。

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