Eu tenho um banco de dados em que estou utilizando um TenantIdem todas as tabelas que precisam ser identificadas exclusivamente para um inquilino específico e, devido aos requisitos de ordenação nas chaves compostas, tenho TenantIdcomo o primeiro na lista de índices. Agora entra em questão uma Authenticationpeça onde a Usertabela contém o TenantId, UserId(a IDENTITYcoluna), e Emailentre outros itens específicos de login.
O portal de login não é específico do locatário, portanto, ao fazer login, o usuário simplesmente inserirá seu Email, buscando assim a linha que verifica suas informações de login. Nesse cenário, não podemos aproveitar imediatamente a chave primária composta de TenantIde UserIdaté encontrarmos a linha que se aplica a Email.
A chave primária composta em TenantIde sempreUserId será utilizada em todas as outras cláusulas condicionais. No entanto, para aproveitar essa chave em primeiro lugar, devemos primeiro procurar essa linha com base em uma consulta de . Sem um índice em , uma verificação de tabela ocorrerá.EmailEmail
Minha pergunta é, que tipo de combinação de índice seria visto como mais adequado neste cenário? Um único índice não clusterizado Emailsozinho, outra chave composta em UserIde Emailem conjunto com o único índice não clusterizado Emailcom INCLUDES em outros dados relevantes, ou nenhuma das opções acima?
O esquema é semelhante assim:
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?
Meu entendimento é que [IX_User_UserId_Email]é útil neste cenário ligar rapidamente para [PK_User_TenantId_UserId], buscando assim o nível adequado de isolamento de forma mais eficiente. Essa é uma suposição incorreta? Estou melhor servido usando apenas [IX_User_Email]?
- Todas as tabelas irão JOIN para
UseronTenantIdeUserId. - Nenhuma tabela será estritamente JOIN com
Userbase emUserId. - Uma pesquisa acontecerá com base estritamente em uma consulta de
Email.TenantIdeUserIdnão será conhecido até que a linha seja buscada. Depois que a linha for buscada, as consultas restantes utilizarãoTenantIdeUserId.
Outra opção que tenho usado está dentro da Tenanttabela, incluindo uma Domaincoluna que especifica o domínio de email de origem do locatário (que sempre será o mesmo em um locatário). Uma vez que o usuário tenha inserido seu Emaile tabulado/selecionado o Passwordcampo na página de login, ele analisará o domínio de e-mail ( @sample.com), permitindo-nos consultar a Tenanttabela menor para encontrar seu TenantId, podendo assim aproveitar a chave composta [PK_User_TenantId_UserId]e, assim, apenas ter que utilizar um índice não clusterizado no Email. Esta pode ser uma abordagem desnecessária, no entanto.
Este é um estranho como
[UserId] [int] IDENTITY(1,1) NOT NULLé único.Pode haver um caso para usar [UserId] sozinho como o PK. Você obterá menos fragmentação em comparação com um índice clusterizado composto TenantId, UserId.
Entendo que você planeja usar TenantId, UserId em todas as consultas, mas não precisa. UserID identificará exclusivamente cada usuário.
Se você tiver relatórios por TenantId, poderá incluí-los nas tabelas e colocar um índice não clusterizado nele.
No que diz respeito ao e-mail, eu apenas colocaria um índice exclusivo não agrupado nele. Você pode incluir os outros campos, se quiser, mas está apenas coletando dados de uma única linha, portanto, não é realmente necessário. No momento, não há nada que impeça emails duplicados.
Poderia argumentar que repetir TenantId em todas as tabelas não é 3NF, pois pode ser derivado de UserId.
Eu pessoalmente não repetiria o TenantId e teria um
viewpara cada tabela que o trouxesse. Entendo que você não queira fazer dessa maneira, mas ainda é minha resposta.User não é um bom nome para uma tabela, pois é uma palavra-chave.
IX_User_Email é suficiente, embora você possa querer torná-lo um índice exclusivo para evitar que vários usuários com o mesmo email.
E esta consulta,
mesmo sem as colunas extras incluídas, apenas alguns IOs lógicos. 3 ou 4 para percorrer o IX_User_UserId_Email para encontrar o (TenantID,UserId) associado ao email e 3 ou 4 para percorrer o índice clusterizado até a página folha que contém todos os dados do usuário.
Se você estiver acessando esta tabela por TenantID e UserID, a escolha da chave primária é boa, embora o UserID seja exclusivo por si mesmo. Por que você colocou UserId antes de Email no índice não clusterizado. Coloque apenas Email no índice e você obviamente poderá pesquisar por Email. Se você conhece o UserId, também conhece o e-mail.