No meu banco de dados tenho uma tabela com dois índices. Há um índice exclusivo nº 1 nas colunas a,b que preciso por motivos de integridade de dados. Eu tenho outro índice #2 com colunas c,a,b que é usado por motivos de desempenho. Percebi que este índice #2 também é único.
A singularidade do índice #2 me parece redundante, pois você não pode ter valores duplicados no índice #2 sem também ter valores duplicados no índice #1. Estou tentado a alterar o índice #2 para que ele não seja mais exclusivo, porque imagino que o mecanismo de banco de dados possa executar uma segunda verificação em c,a,b no índice #2 para garantir a exclusividade dessas colunas toda vez que uma linha for inserida, causando um impacto no desempenho, mesmo que nunca haja valores duplicados. Isso está correto?
Existe alguma maneira de excluir o índice #1 em a,b e manter o índice #2 em c,a,b, mas ainda forçar uma restrição exclusiva apenas nas colunas a,b sem manter dois índices separados? Isso me permitiria ter apenas um índice com todas as três colunas, mas ainda aplicaria minha restrição de integridade de dados em a,b. Não preciso de um índice em a,b para desempenho porque todas as minhas consultas de seleção incluem a coluna c na cláusula where. Este seria um caso de uso para uma restrição exclusiva em vez de um índice? Eu pensei que o mecanismo de banco de dados basicamente trata essas duas construções da mesma forma (veja este post: Quando devo usar uma restrição exclusiva em vez de um índice exclusivo? ).
Tenha em mente que os índices não são redundantes, mas a "singularidade" dos índices é redundante. Parece que é um acéfalo tornar o índice # 2 não exclusivo. Mas isso resulta em algum ganho real de desempenho? O banco de dados verifica a exclusividade de ambos os índices mesmo que as colunas no índice #1 estejam totalmente incluídas no índice #2?
Algumas respostas perguntaram sobre consultas de exemplo usadas para selecionar dados dessa tabela. Aqui estão os mais comuns:
Select [some other columns] from table where c=1 and a=2
Select [some other columns] from table where c=1
Select [some other columns] from table where c=1 and a=2 and b=3
Essas consultas geralmente incluem a seleção de muitas outras colunas que não estão em nenhum índice.
O que normalmente nunca executamos é uma consulta como esta:
Select [stuff] from table where a=2 and b=3
Sim, declarar o índice #2 exclusivo é redundante. O índice nº 1 precisa existir para suportar a chave estrangeira, portanto, há pouca chance de ser descartado.
Esta é a coisa errada para se concentrar. O SQL Server sempre precisa localizar o ponto de inserção. Se encontrar uma linha existente para a(s) chave(s) fornecida(s) e o índice for marcado como exclusivo, um erro será gerado. Caso contrário, a nova linha é inserida no ponto correto.
Há uma consequência muito mais importante de marcar um índice como único desnecessariamente. Qualquer atualização que altere uma das colunas de chave requer tratamento especial para evitar violações de chave transitórias durante o processamento da atualização. No SQL Server, isso significa operadores Split , Sort e Collapse extras e manutenção ampla (por índice) para o índice afetado.
Isso é muito mais caro do que o problema com o qual você estava preocupado.
Não.
As consultas de exemplo sugerem que o predicado de igualdade na coluna c sozinho é seletivo o suficiente para que o otimizador escolha um plano de busca mais pesquisa de índice não clusterizado. O otimizador é cauteloso ao selecionar esse tipo de plano, o que implica que a coluna c é realmente muito seletiva (ou as estimativas são ruins).
Como você está recuperando colunas fora do índice de qualquer maneira, você pode descobrir que um índice não exclusivo não clusterizado na coluna c sozinho é suficiente.
Adicionar a e/ou b à chave de índice seria útil se consultas suficientes se beneficiassem do índice mais preciso para justificar o espaço extra e a manutenção. Só você tem informações suficientes para fazer esse julgamento.
O SQL Server mantém os índices sincronizados com os dados da tabela base. Quando uma linha é inserida em uma tabela, ela também é adicionada a todos os índices relevantes.
Os índices são ordenados. Portanto, a nova linha deve ser adicionada ao local correto em cada índice. Tendo encontrado o local correto para adicionar a nova linha, é trivial ver se existe um valor existente nesse local. A sobrecarga para verificar a exclusividade é trivial.
Sim, durante as inserções, há uma sobrecarga de ter vários índices na tabela. Mas isso se deve ao fato de haver vários índices que devem ser mantidos consistentes com a tabela. Não tem nada a ver com os índices serem únicos ou não.
Pode haver muitos benefícios em ter índices sobrepostos ao ler dados. A resposta de Grant cobre isso. O equilíbrio entre despesas gerais e benefícios varia de acordo com sua carga de trabalho.
Se um índice é único, é bom declará-lo como tal. O otimizador de consulta usa essas informações ao construir planos de consulta. Com ambos os índices declarados como únicos, é mais provável que o otimizador apresente planos melhores em média do que de outra forma. Forneça ao otimizador o máximo de informações possível sobre seus dados para ajudá-lo a fazer seu trabalho.
Não há como declarar um subconjunto de colunas de índice como exclusivo e redundante para um (parte de) outro índice.
Então, esses são dois índices diferentes, com estruturas de chave diferentes. Sim, #1, em A,B, é replicado dentro de #2, mas a adição da coluna C significa que ela também deve ser única, combinada com A&B. Isso não é uma duplicata do comportamento do Índice #1. É separado e diferente.
Se, foi configurado #1 é A,B e #2 é A,B,C, então, #1 seria um índice redundante, possivelmente não necessário. No entanto, mesmo aqui, a cautela tem que tomar parte. Porque o número 1 neste cenário é menor que o número 2. Portanto, uma varredura de #1 pode ser mais rápida que uma varredura de #2, dependendo. Nesse caso, #1 é usado para algumas consultas, mesmo que seja uma duplicata de #2.
Além disso, quando colunas diferentes são a primeira coluna em um índice, elas resultam em histogramas diferentes nas estatísticas. Esses diferentes histogramas podem ser úteis para diferentes consultas. Portanto, um índice exclusivo nas colunas A,B pode oferecer suporte a consultas diferentes de um índice exclusivo nas colunas B, A. Mesmo que, do ponto de vista lógico, sejam os mesmos índices.
É importante testar suas consultas para entender como os índices são usados. Monitorar o desempenho ao longo do tempo também é importante. Então, quando você encontrar um índice duplicado legítimo, poderá saber se a eliminação dele é neutra para o desempenho, ajuda no desempenho ou prejudica o desempenho. Porque simplesmente olhar para o índice e sua estrutura não lhe dirá como ele é usado no sistema.
EDIT: Esqueci de incluir isso. Claro, há sobrecarga de manutenção, possivelmente até bloqueio e contenção de recursos, com muitos índices. É por isso que eliminar índices duplicados é importante. Não estou dizendo que você não deve eliminar duplicatas. Apenas que você precisa ser muito claro sobre o que constitui uma duplicata (o que você não tem neste caso) e se essa duplicata existe ou não para outros fins.