Início / dba / Perguntas / 21554
Accepted
StuartLC
Asked: 2012-07-27 03:13:22 +0800 CST

Implicações do uso de um índice não agrupado exclusivo com colunas de cobertura em vez de uma chave primária

  • 772

Temos uma tabela grande [MyTable]que atualmente possui a e a Primary Keyna mesmaUnique Non Clustered Index coluna ( [KeyColumn]). O índice U NC também possui colunas de cobertura adicionais.

Ter PK e índice NC exclusivo nas mesmas colunas parece redundante, então eu estava pensando em excluir a chave primária e, em vez disso, usar o índice exclusivo não agrupado para fins de integridade referencial.

Observe que a tabela é totalmente agrupada por outra coluna.

ou seja, então temos:

ALTER TABLE [MyTable]
    ADD CONSTRAINT [PK_MyTable] 
    PRIMARY KEY NONCLUSTERED ([KeyColumn])
GO

E

CREATE UNIQUE NONCLUSTERED INDEX [IX_MyTable_SomeIndex] 
    ON [MyTable] ([KeyColumn]) 
    INCLUDE ([Column1], [Column2])
GO

Pelo que sei, não é possível adicionar colunas de cobertura a uma Chave Primária, então pretendo fazer:

  • Elimine as restrições de chave estrangeira dependentes deMyTable.KeyColumn
  • Solte a chave primária MyTable.KeyColumncompletamente
  • Adicione novamente as chaves estrangeiras à tabela (ou seja, RI será aplicada via MyTable.KeyColumn)

A única implicação que posso pensar em fazer isso é que não obteremos o símbolo de chave visual em nossos diagramas ERD e que a densidade do índice (folha) será menor por causa das colunas incluídas.

Eu li https://stackoverflow.com/questions/487314/primary-key-or-unique-index e estou feliz com os aspectos de integridade e desempenho de fazer isso.

Minha pergunta é: essa abordagem é falha?

Editar o que estou tentando realizar : otimização de desempenho e limpeza de primavera . Ao remover o PK ou um índice, haverá menos páginas necessárias para meus índices = gravações mais rápidas, além de benefícios de manutenção/operacionais, ou seja, um índice a menos para manter desfragmentado, etc.

Para esclarecer isso, nunca tive uma tabela que está sendo referenciada, sem um PK. No entanto, o fato de um índice NC com colunas de cobertura ter sido adicionado à tabela significa que preciso adaptar meu pensamento.

sql-server sql-server-2008

3 respostas

  1. Best Answer

    Otimização de Desempenho. Ao remover o PK ou um índice, haverá menos páginas necessárias para meus índices = gravações mais rápidas, além de benefícios de manutenção/operacionais, ou seja, um índice a menos para manter desfragmentado, etc.

    Você precisa ser capaz de provar que o que é proposto realmente ajudará (custo x benefício). Por qual motivo essa mudança está sendo considerada? Existem realmente problemas de desempenho com esta tabela ou apenas "pareceu errado"?

    Aqui estão algumas outras perguntas que irão ajudá-lo a tomar a melhor decisão para o seu ambiente:

    • Quanto tempo seria economizado na janela de manutenção? Na janela de backup?

    • Quanto espaço de armazenamento isso economizaria (arquivos de dados, arquivos de log, backups, etc.)?

    • O INSERTdesempenho nesta mesa é realmente um gargalo agora? Quanto melhoraria? A remoção de um índice é a melhor estratégia para corrigir esse problema?

    • Isso causará problemas com ferramentas e estruturas de banco de dados (particularmente ORMs) que esperam que cada tabela tenha uma chave primária e não apenas um índice exclusivo? A replicação transacional requer uma chave primária nas tabelas publicadas.

    • Um esquema de banco de dados autodocumentado é importante?

    • Apesar de seu uso limitado, a estreiteza do índice de chave primária ainda permite que o otimizador produza planos mais eficientes para determinadas consultas? (Use sys.dm_db_index_usage_statspara descobrir.)

    Pessoalmente falando, pelo que você nos disse, eu deixaria isso de lado até que pudesse ser provado que (a) o índice extra é um problema e (b) removê-lo é a solução.

    • 3

  2. O único tipo de consulta nessa tabela que terá um desempenho pior (e apenas um pouco pior) serão aqueles que solicitam um intervalo de valores de KeyColumn - já que essas consultas no momento seriam melhor atendidas por uma busca de índice em seu PK.

    Vale a pena ter em mente que o custo da verificação de integridade referencial para tabelas que fazem referência a esta tabela será maior (novamente apenas um pouco maior).

    Contanto que você cuide do aspecto anulável, você deve ficar bem com o único índice.

    Se fosse meu banco de dados, eu provavelmente iria para o índice único único, todas as outras coisas sendo iguais.

    • 2

  3. Tanto quanto eu sei, não é possível adicionar colunas de cobertura a uma chave primária

    Se você tiver um índice clusterizado em KeyColumn, como é um índice clusterizado, todas as outras colunas são implicitamente 'cobertas'. Portanto, você não ganha nada adicionando outro índice em KeyColumn. Na verdade, você diminuirá suas inserções e usará mais espaço.

    Isso ocorre porque um índice clusterizado não é um índice como tal, mas apenas uma instrução para armazenar as linhas da tabela na ordem do índice. E depois de encontrar uma linha por chave primária, você terá todas as outras colunas ali.

    • -3

relate perguntas