Início / dba / Perguntas / 313784
Accepted
fmi21
Asked: 2022-06-27 00:40:46 +0800 CST

Um índice clusterizado de 3 colunas é muito grande?

  • 772

Meu objetivo é projetar uma tabela, que possa ser consultada através de um id externo( uniqueidentifier), um id interno( bigint), sempre em combinação com companyId(bigint), userId(bigint)e dashboardId(bigint)ou em combinação com ( dashboardId IN @0, ..., @n, n=0,10), ambas condições papel de uma verificação de propriedade.

Eu criei as seguintes composições de índice:

CREATE CLUSTERED INDEX Mytable_createdBy_cix ON Mytable(companyId, createdBy, dashboardId)

CREATE UNIQUE NONCLUSTERED INDEX Mytable_extId_nix ON Mytable(extId) INCLUDE (valueD, valueN)

CREATE UNIQUE NONCLUSTERED INDEX Mytable_chartId_nix ON Mytable (chartId) INCLUDE (valueD, valueN)

Não sei a resposta das seguintes perguntas:

  • O índice clusterizado é ruim por não ser exclusivo? Devo adicionar a chave segregada e não usar a atribuição automática uniqueifier?
  • Colunas de 3 * 8 bytes + exclusivo de 4 bytes (total de 28 bytes) são demais para um índice clusterizado? Eu li que é incluído nas páginas de inclusão de cada índice não clusterizado exclusivo (onde 16 ou 8 bytes adicionais são adicionados de acordo com a chave usada).
  • Esse design de índice faz sentido para as consultas abaixo?

Eu planejo executar consultas, semelhantes a estas:

SELECT chartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId

SELECT chartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId

SELECT chartId, valueD, valueN FROM Mytable WHERE dashboardId IN (@0, @1, @2)

SELECT chartId, valueD, valueN FROM Mytable WHERE (companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId) OR dashboardId IN (@0, @1, @2)

UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND chartId = @chartId

UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND extChartId= @extId

UPDATE Mytable SET valueD = @valueD WHERE ((companyId = @companyId AND createdBy = @userId) OR dashboardId IN (@0, @1, @2)) AND extChartId= @extId

Eu sei , é melhor testar, avaliar planos de execução, compartilhá-los ao fazer perguntas no stackexchange, mas esta é a fase de design, então ainda não existem dados ou tabelas reais.

Posso ajustar a estrutura de chaves/índices/tabela para melhor se adequar às consultas. Só espero acertar pelo menos parcialmente desta primeira vez ao criá-los, para que a questão não seja revisitada.

Muito obrigado por qualquer ajuda com antecedência.

sql-server index-tuning

2 respostas

  1. Best Answer

    Em primeiro lugar, observe que o exclusivo em uma chave não exclusiva é adicionado apenas nos casos em que existem valores duplicados. Não ocupa espaço se não houver duplicatas na mesma página de índice. Portanto, a menos que haja duas linhas exatamente iguais companyId, createdBy, dashboardId, isso não acontecerá.

    Chaves de cluster amplas podem ser problemáticas, mas também resolvem alguns problemas de deadlock, então isso pode ser um fator. Não está claro que sua escolha de chave de cluster esteja correta, mas por outro lado: como dois UNIQUEíndices não clusterizados fazem sentido considerando o design da tabela? Se eles são únicos, por que todos os predicados são necessários nessas consultas?

    Parece de seus comentários que o extra chartIdé simplesmente ter uma coluna de índice menor. Acho que essa é provavelmente uma otimização prematura: ela simplesmente adiciona custos extras de indexação porque agora você também precisa indexar essa coluna. Eu recomendaria que você o removesse e confiasse apenas no exrChartIdmesmo, embora seja mais amplo.

    Para as consultas fornecidas, você precisa trabalhar com elas e decidir a melhor forma de satisfazê-las com índices. A questão de qual deve ser o índice de clustering é um tanto ortogonal, pois um índice de clustering efetivamente INCLUDEs todas as colunas automaticamente.

    Cada um pode usar um índice que também satisfaça um índice diferente, desde que as colunas de chave à esquerda sejam as mesmas, independentemente de quaisquer colunas adicionais na chave ou INCLUDE.

    1. SELECT extChartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId
      Isso pode ser satisfeito com o seguinte índice
      (companyId, createdBy) INCLUDE (extChartId, valueD, valueN)
    1. SELECT extChartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId
      Isso pode ser satisfeito com o seguinte índice
      (companyId, createdBy, dashboardId) INCLUDE (extChartId, valueD, valueN)
    1. SELECT extChartId, valueD, valueN FROM Mytable WHERE dashboardId IN (@0, @1, @2)
      Isso pode ser satisfeito com o seguinte índice
      (dashboardId) INCLUDE (extChartId, valueD, valueN)
    1. SELECT extChartId, valueD, valueN FROM Mytable WHERE (companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId) OR dashboardId IN (@0, @1, @2) Este é mais difícil e precisa de uma união de índice (pode ser necessário reescrever a consulta para obter isso). Os índices necessários seriam os mesmos que #1 e #3
    1. Mudamos este para usar apenas a chave natural, então é exatamente o mesmo que o nº 6
    1. UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND extChartId = @extId
      Por extChartIdser único, as outras colunas podem ir no INCLUDE
      Isso, portanto, precisa de um índice (extChartId) INCLUDE (companyId, createdBy, valueD)
    1. UPDATE Mytable SET valueD = @valueD WHERE ((companyId = @companyId AND createdBy = @userId) OR dashboardId IN (@0, @1, @2)) AND extChartId = @extId
      Novamente, isso é difícil de satisfazer, devido ao OR. Pode ter sido necessário dividir isso em duas atualizações separadas. Mas dado que extChartIdé único, podemos novamente confiar nesse mesmo índice.

    Olhando para esses índices, chegamos às seguintes conclusões:

    • Os predicados são todos =predicados de igualdade ou INem uma lista curta, portanto, as colunas-chave podem estar em qualquer ordem. Isso nos ajuda imensamente na combinação dos índices.
    • Um índice adequado para #1 pode ter colunas extras para atender #2, mas não #3. Da mesma forma, um adequado para o nº 3 também pode funcionar para o nº 2, mas não para o nº 1. Portanto, precisamos de índices separados. A questão permanece qual dessas combinações também poderia satisfazer outras perguntas.
    • #4 pode trabalhar com os mesmos índices que os três primeiros, então não vamos nos preocupar com isso.
    • #6 e #7 precisam extChartId, o que você diz ser único. Portanto, todo o resto das colunas pode entrar INCLUDEcom pouco impacto no desempenho.

    Portanto, segue-se que a melhor combinação de índices é algo assim

    (companyId, createdBy, dashboardId) INCLUDE (extChartId, valueD, valueN)
(dashboardId) INCLUDE (extChartId, valueD, valueN)
(extChartId) INCLUDE (companyId, createdBy, dashboardId, valueD)

    A questão permanece qual deles você escolhe para sua chave de cluster. O que você escolher, INCLUDEtodas as outras colunas.

    O primeiro ou terceiro índice faz mais sentido para mim. Dado que extChartIdé único por si só, poderia fazer mais sentido usá-lo devido ao tamanho, como você observou corretamente.

    Mas o deadlock também pode ser um problema, dependendo da complexidade de suas atualizações transacionais etc. Comece com isso e troque a chave de cluster se perceber que é um problema.

    • 4

  2. Eu sei, é melhor testar, avaliar planos de execução, compartilhá-los ao fazer perguntas no stackexchange, mas esta é a fase de design, então ainda não existem dados ou tabelas reais.

    Na fase de design, adicione um índice exclusivo para cada chave e um índice não clusterizado adicional para cada chave estrangeira não suportada por uma chave.

    Em seguida, conforme você desenvolve, avalie a execução da consulta e considere índices adicionais.

    • 2

relate perguntas