主页 / dba / 问题 / 231407
Accepted
PicoDeGallo
Asked: 2019-03-06 15:54:59 +0800 CST

复合主键中定义的列的非聚集索引

  • 772

我有一个多租户数据库，我在其中使用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'

在这两个选项中，哪种类型的指数组合最适合扩展？仅复合主键或复合主键加其他非聚集索引？或者还有其他更可行的选择吗？

sql-server database-design

1 个回答

  1. Best Answer

    我不确定示例查询是否是推荐的合理示例。示例查询不是典型的多租户应用程序查询，因为它不特定于特定客户端。它更像是一种支持或管理查询，希望深入了解所有（或至少多个）客户。当然，它也可能与维护相关（例如，垃圾收集会寻找最早的日期而不关心AccountId）。所以让我们把它分开：

    1. 一般的

      我看不出有一个非聚集索引有任何好处，它只是聚集索引中的前导/最左边的键列。聚簇索引已经按该顺序排列，因此存在它的统计信息。所以，IX_AccountState_Account选项2中的索引纯粹是浪费，而且是对系统的拖累。

    2. 支持/管理/维护查询

      这些查询，尤其是维护查询，可以跨AccountId值工作。因此，某些查询肯定会受益于非聚集索引键列上的非聚集索引（AccountId或者更一般地说：不是最左边/前导键列）。这假设您有仅根据实体 ID 进行过滤/排序的查询。没有任何这样的查询意味着您可能不需要这个索引。

    3. 应用查询

      这些查询应始终包含AccountId，因此我看不出它们如何从仅基于实体 ID 的索引中获益。

      虽然我曾在类似于您所描述的多租户系统上工作，但我只是想到了一些我以前从未想过的事情：因为聚簇索引统计信息基于最左边/前导列而不是键列的组合，为这些对象手动创建统计信息可能会有所帮助。我似乎记得它只是密度部分将占所有键列（不仅仅是前导列），但这可能有助于查询优化。这需要测试，因为我还没有尝试过（而且我不会很快做这样的测试）。

    • 2

相关问题