Índices múltiplos versus índices de várias colunas

Acabei de adicionar um índice a uma tabela no SQL Server 2005 e isso me fez pensar. Qual é a diferença entre criar 1 índice e definir várias colunas por ter 1 índice por coluna que você deseja indexar.

Existem certas razões pelas quais uma deve ser usada sobre a outra?

Por exemplo

Create NonClustered Index IX_IndexName On TableName (Column1 Asc, Column2 Asc, Column3 Asc) 

Versus

 Create NonClustered Index IX_IndexName1 On TableName (Column1 Asc) Create NonClustered Index IX_IndexName2 On TableName (Column2 Asc) Create NonClustered Index IX_IndexName3 On TableName (Column3 Asc) 

   

Eu concordo com o Cade Roux .

Este artigo deve levá-lo no caminho certo:

  • Índices no SQL Server 2005/2008 – Melhores Práticas, Parte 1
  • Índices no SQL Server 2005/2008 – Parte 2 – Internals

Uma coisa a notar é que os índices clusterizados devem ter uma chave única (uma coluna de identidade que eu recomendaria) como a primeira coluna. Basicamente, ele ajuda seus dados a inserir no final do índice e não causa muitos E / S de disco e partições de página.

Em segundo lugar, se você estiver criando outros índices em seus dados e eles forem construídos de forma inteligente, eles serão reutilizados.

Por exemplo, imagine que você pesquise uma tabela em três colunas

estado, condado, zip.

  • às vezes você pesquisa apenas por estado.
  • você às vezes procura por estado e município.
  • você freqüentemente procura por estado, município, zip.

Em seguida, um índice com estado, município, zip. será usado em todas essas três pesquisas.

Se você pesquisar por zip sozinho bastante, o índice acima não será usado (pelo SQL Server, de qualquer forma), pois o zip é a terceira parte desse índice e o otimizador de consulta não verá esse índice como útil.

Você poderia então criar um índice no Zip sozinho que seria usado nesta instância.

Eu acho que a resposta que você está procurando é que isso depende de suas cláusulas where de suas consultas usadas com frequência e também do seu grupo.

O artigo vai ajudar muito. 🙂

Sim. Eu recomendo que você confira os artigos de Kimberly Tripp sobre indexação .

Se um índice está “cobrindo”, não há necessidade de usar nada além do índice. No SQL Server 2005, você também pode adicionar colunas adicionais ao índice que não fazem parte da chave, o que pode eliminar as viagens para o restante da linha.

Ter múltiplos índices, cada um em uma única coluna, pode significar que apenas um índice é usado – você terá que se referir ao plano de execução para ver quais efeitos os diferentes esquemas de indexação oferecem.

Você também pode usar o assistente de ajuste para ajudar a determinar quais índices tornariam uma consulta ou uma carga de trabalho melhor.

O índice de várias colunas pode ser usado para consultas que fazem referência a todas as colunas:

 SELECT * FROM TableName WHERE Column1=1 AND Column2=2 AND Column3=3 

Isso pode ser pesquisado diretamente usando o índice de várias colunas. Por outro lado, no máximo, um dos índices de coluna única pode ser usado (seria necessário procurar todos os registros com a coluna 1 = 1 e, em seguida, verificar a coluna 2 e a coluna 3 em cada um deles).

Um item que parece ter sido perdido é a transformação das estrelas. Os operadores de interseção de índice resolvem o predicado calculando o conjunto de linhas atingidas por cada um dos predicados antes que qualquer E / S seja feita na tabela de fatos. Em um esquema em estrela, você indexaria cada chave de dimensão individual e o otimizador de consulta poderá resolver quais linhas selecionar pelo cálculo de interseção de índice. Os índices em colunas individuais oferecem a melhor flexibilidade para isso.

Se você tiver consultas que usarão com frequência um conjunto de colunas relativamente estático, a criação de um único índice de cobertura que inclua todas elas melhorará drasticamente o desempenho.

Colocando várias colunas em seu índice, o otimizador só terá que acessar a tabela diretamente se uma coluna não estiver no índice. Eu uso muito isso no data warehousing. A desvantagem é que isso pode custar muita sobrecarga, especialmente se os dados forem muito voláteis.

Criar índices em colunas únicas é útil para operações de pesquisa freqüentemente encontradas em sistemas OLTP.

Você deve se perguntar por que está indexando as colunas e como elas serão usadas. Execute alguns planos de consulta e veja quando eles estão sendo acessados. A sintonização de índice é tão instintiva quanto a ciência.

O livro de Lahdenmaki e Leach, “Design de índice de database relacional e otimizadores”, introduz um sistema de três estrelas para avaliar a adequação de um índice a uma consulta.

  1. O índice ganha uma estrela se colocar linhas relevantes adjacentes umas às outras
  2. uma segunda estrela se suas linhas forem classificadas na ordem em que a consulta precisa
  3. uma estrela final se contiver todas as colunas necessárias para a consulta

Crie vários índices em colunas, essa estratégia de indexação geralmente ocorre quando as pessoas dão conselhos vagos, mas autoritativos, como “crie índices em colunas que aparecem na cláusula WHERE”. Esse conselho está muito errado. Isso resultará em índices de uma estrela na melhor das hipóteses. Esses índices podem ser muito mais lentos que os índices realmente ótimos. Às vezes, quando você não pode criar um índice de três estrelas, é muito melhor ignorar a cláusula WHERE e prestar atenção à ordem de linha ideal ou criar um índice de cobertura.