主页 / dba / 问题 / 198568
Accepted
PicoDeGallo
Asked: 2018-02-23 06:47:19 +0800 CST

与复合主键配对时的单个或多个非聚集索引

  • 772

我有一个数据库，我在其中使用TenantId需要唯一标识特定租户的所有表，并且由于复合键中的排序要求，我TenantId在索引列表中排在第一位。现在有一个问题，Authentication其中User表包含TenantId, UserId（IDENTITY列）以及Email其他特定于登录的项目。

登录门户不是特定于租户的，因此在登录时，用户只需输入他们的Email，从而查找验证其登录信息的行。在这种情况下，我们无法立即利用 和 的复合主键，TenantId直到UserId找到适用于 的行Email。

TenantId和上的复合主键UserId将始终在所有其他条件子句中使用。但是，为了首先利用此键，我们必须首先根据 的查询查找该行Email。如果没有索引Email，将改为进行表扫描。

我的问题是，哪种类型的指数组合最适合这种情况？单独的单个非聚集索引Email，另一个复合键UserId以及与其他相关数据上的 INCLUDESEmail结合的单个非聚集索引，或者以上都不是？Email

该架构类似于：

CREATE TABLE [User] (
     [TenantId] [int] NOT NULL
    ,[UserId] [int] IDENTITY(1,1) NOT NULL
    ,[Email] [varchar](64) NOT NULL
    ,[FirstName] [varchar](32) NOT NULL
    ,[MiddleName] [varchar](32) NULL
    ,[LastName] [varchar](32) NOT NULL
    ,[PasswordHash] [varbinary](64) NOT NULL
    ,[PasswordSalt] [varbinary](32) NOT NULL
    ,[HashMethodId] [tinyint] NOT NULL
    ,[IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive] DEFAULT 1
    ,[IsLocked] [bit] NOT NULL CONSTRAINT [DF_User_IsLocked] DEFAULT 0

    ,CONSTRAINT [PK_User_TenantId_UserId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [UserId] ASC)
    ,INDEX [IX_User_UserId_Email] NONCLUSTERED ([UserId] ASC, [Email] ASC)
    ,CONSTRAINT [FK_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [Tenant]([TenantId])
    ,CONSTRAINT [FK_HashMethod_HashMethodId] FOREIGN KEY ([HashMethodId]) REFERENCES [HashMethod]([HashMethodId])
);
CREATE NONCLUSTERED INDEX [IX_User_Email] ON [User]([Email]) INCLUDE ([PasswordHash],[PasswordSalt],[HashMethodId],[IsActive],[IsLocked])

-- Note for research: Why can an index that has INCLUDE not be specified in CREATE TABLE?

我的理解是，[IX_User_UserId_Email]在这种情况下，快速绑定回 很有用[PK_User_TenantId_UserId]，从而更有效地寻求适当的隔离级别。这是一个错误的假设吗？我使用 just 会更好[IX_User_Email]吗？

  • 所有表都将 JOIN 到UseronTenantId和UserId。
  • 没有表会严格 JOIN 到User基于UserId.
  • 将严格根据 的查询进行查找Email。TenantId并且UserId在获取该行之前不会知道。获取行后，其余查询将使用TenantIdand UserId。

我一直在考虑的另一个选项是在Tenant表中，包括一个Domain指定租户源电子邮件域的列（在租户中始终相同）。一旦用户在登录页面上输入他们的Email选项卡/选择Password字段，它将解析出电子邮件域 ( @sample.com)，允许我们查询较小的Tenant表以找到他们的TenantId，从而能够利用复合键[PK_User_TenantId_UserId]，从而只需要在Email. 然而，这可能是一种不必要的方法。

sql-server database-design

3 个回答

  1. Best Answer

    这是一个奇怪的，[UserId] [int] IDENTITY(1,1) NOT NULL也是独一无二的。

    可能存在单独使用 [UserId] 作为 PK 的情况。与复合聚集索引 TenantId、UserId 相比，您将获得更少的碎片。

    我知道您计划在所有查询中使用 TenantId、UserId，但您不需要。UserID 将唯一标识每个用户。

    如果您有 TenantId 的报告，您可以将其包含在表中并在其上放置一个非聚集索引。

    至于电子邮件，我只会在其上放置一个非聚集的唯一索引。如果您愿意，可以包括其他字段，但它只是从单行中获取数据，因此并不是真正必要的。现在没有什么可以防止重复的电子邮件。

    可以争辩说在所有表中重复 TenantId 不是 3NF，因为它可以从 UserId 派生。

    我个人不会重复 TenantId 并view为每个引入它的表都有一个。我知道你不想那样做，但这仍然是我的答案。

    select t.*, u.tenant 
  from table t 
  join [user] u 
    on u.userid = t.userid

    User 不是一个好的表名，因为它是一个关键字。

    • 1

  2. 我的问题主要是 [IX_User_UserId_Email] 是否有必要，或者 [IX_User_Email] 是否令人满意。

    IX_User_Email 就足够了，尽管您可能希望将其设为唯一索引以防止多个用户使用同一电子邮件。

    而这个查询，

    select * 
from [User]
where email = @email

    即使没有额外包含的列，也只有几个逻辑 IO。3 或 4 向下遍历 IX_User_UserId_Email 以查找与电子邮件关联的 (TenantID,UserId)，然后 3 或 4 遍历聚集索引到包含所有用户数据的叶页。

    • 1

  3. 如果您将通过 TenantID 和 UserID 访问此表，那么主键选择是好的，尽管 UserID 本身是唯一的。为什么在非聚集索引中将 UserId 放在 Email 之前。仅将电子邮件放入索引中，您显然可以通过电子邮件进行搜索。如果您知道 UserId，那么您也知道 Email。

    • -1

相关问题