我有一个多租户数据库，我在其中使用AccountId所有表中的列作为用于租户隔离的复合主键的一部分。在作为复合主键的一部分的每个列上创建额外的非聚集索引以帮助 SQL Server 在连接到查找表时维护准确的统计信息并提高查询性能是否有益？
例如，在一个关联表中，该关联表定义了一个人和他们在其中设有办公室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'
在这两个选项中，哪种类型的指数组合最适合扩展？仅复合主键或复合主键加其他非聚集索引？或者还有其他更可行的选择吗？
