Quão importante é a ordem das colunas nos índices?

Ouvi dizer que você deve colocar colunas que serão mais seletivas no início da declaração do índice.

Por exemplo:

CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) 

Primeiro, esse boato é correto?

Em caso afirmativo, é provável que eu veja grandes diferenças no desempenho reorganizando a ordem das colunas no meu índice, ou é mais uma prática “agradável de se fazer”?

A razão pela qual estou perguntando é porque depois de colocar uma consulta no DTA, é recomendável criar um índice que tenha quase todas as mesmas colunas como um índice existente, apenas em uma ordem diferente.

Eu estava pensando em adicionar as colunas ausentes ao índice existente e chamá-lo de bom. Pensamentos?

   

Olhe para um índice como este:

 Cols 1 2 3 ------------- | | 1 | | | A |---| | | | 2 | | |---|---| | | | | | | | 1 | 9 | | B | | | | |---| | | | 2 | | | |---| | | | 3 | | |---|---| | 

Veja como restringir o primeiro, pois a primeira coluna elimina mais resultados do que restringir a segunda coluna primeiro? É mais fácil se você imaginar como o índice deve ser atravessado, coluna 1, depois coluna 2, etc … você vê que cortar a maioria dos resultados no primeiro passo torna o segundo passo muito mais rápido.

Em outro caso, se você consultou na coluna 3, o otimizador nem usaria o índice, porque não é de todo útil restringir os conjuntos de resultados. Sempre que você estiver em uma consulta, limitar o número de resultados para lidar antes da próxima etapa significa um melhor desempenho.

Como o índice também é armazenado desta forma, não há retrocesso no índice para encontrar a primeira coluna quando você está consultando sobre ela.

Em suma: não, não é para mostrar, há benefícios reais de desempenho.

A ordem das colunas é crítica. Agora qual a ordem correta, depende de como você irá consultá-la. Um índice pode ser usado para fazer uma busca exata ou uma varredura de intervalo. Uma busca exata é quando os valores para todas as colunas no índice são especificados e a consulta é inserida exatamente na linha. Para buscas, a ordem das colunas é irrelevante. Uma varredura de intervalo é quando apenas algumas colunas são especificadas e, nesse caso, quando a ordem se torna importante. O SQL Server pode usar um índice para uma varredura de intervalo somente se a coluna mais à esquerda for especificada e somente se a próxima coluna mais à esquerda for especificada e assim por diante. Se você tiver um índice em (A, B, C), ele pode ser usado para varrer a varredura de A=@a , para A=@a AND B=@b mas não para B=@b , para C=@c nem B=@b AND C=@c . O caso A=@a AND C=@c é misturado, como na porção A=@a usará o índice, mas o C=@c não (a consulta varrerá todos os valores B para A=@a , será não “pule” para C=@c ). Outros sistemas de database têm o chamado operador ‘skip scan’, que pode tirar vantagem de colunas internas em um índice quando as colunas externas não são especificadas.

Com esse conhecimento em mãos, você pode ver as definições do índice novamente. Um índice em (MostSelective, SecondMost, Least) será efetivo somente quando a coluna MostSelective for especificada. Mas sendo isso o mais seletivo, a relevância das colunas internas irá degradar rapidamente. Muitas vezes você verá que um índice melhor está em (MostSelective) include (SecondMost, Least) ou on (MostSelective, SecondMost) include (Least) . Como as colunas internas são menos relevantes, colocar colunas de baixa seletividade em tais posições corretas no índice faz com que elas sejam apenas ruído para uma busca, por isso faz sentido movê-las para fora das páginas intermediárias e mantê-las apenas nas páginas de folhas, por propósitos de cobertura de consulta. Em outras palavras, mova-os para INCLUDE. Isso se torna mais importante à medida que o tamanho da Least coluna aumenta. A ideia é que esse índice só possa beneficiar consultas que especifiquem MostSelective como um valor exato ou um intervalo, e essa coluna sendo a mais seletiva já restringe as linhas candidatas em grande medida.

Por outro lado, um índice em (Least, SecondMost, MostSelective) pode parecer um erro, mas na verdade é um índice muito poderoso. Por ter a Least coluna como sua consulta mais externa, ela pode ser usada para consultas que precisam agregar resultados em colunas de baixa seletividade. Essas consultas são predominantes nos armazéns de dados OLAP e de análise, e é exatamente nesse ponto que esses índices têm um bom exemplo para eles. Na verdade, esses índices produzem excelentes índices de cluster , exatamente porque organizam o layout físico em grandes blocos de linhas relacionadas (mesmo valor Least , que geralmente indicam algum tipo de categoria ou tipo) e facilitam as consultas de análise.

Então, infelizmente, não há uma ordem ‘correta’. Você não deve seguir nenhuma receita de cookie, mas sim analisar o padrão de consulta que você usará nessas tabelas e decidir qual ordem de coluna de índice está correta.

você deve colocar colunas que serão mais seletivas no início da declaração do índice.

Corrigir. Os índices podem ser compostos – compostos de várias colunas – e a ordem é importante por causa do princípio mais à esquerda. A razão é que o database verifica a lista da esquerda para a direita e precisa encontrar uma referência de coluna correspondente que corresponda à ordem definida. Por exemplo, ter um índice em uma tabela de endereços com colunas:

  • Endereço
  • Cidade
  • Estado

Qualquer consulta usando a coluna de address pode utilizar o índice, mas se a consulta tiver apenas referências de city e / ou state – o índice não poderá ser usado. Isso ocorre porque a coluna mais à esquerda não é referenciada. O desempenho da consulta deve informar qual é o ideal: índices individuais ou vários compostos com ordens diferentes. Boa leitura: The Tipping Point , por Kimberley Tripp

Como Remus diz, isso depende da sua carga de trabalho.

Eu quero abordar um aspecto enganoso da resposta aceita.

Para consultas que estão executando uma pesquisa de igualdade em todas as colunas no índice, não há diferença significativa.

O abaixo cria duas tabelas e as preenche com dados idênticos. A única diferença é que uma delas tem as chaves ordenadas da maioria para a menos seletiva e a outra, o contrário.

 CREATE TABLE Table1(MostSelective char(800), SecondMost TINYINT, Least CHAR(1), Filler CHAR(4000) null); CREATE TABLE Table2(MostSelective char(800), SecondMost TINYINT, Least CHAR(1), Filler CHAR(4000) null); CREATE NONCLUSTERED INDEX MyINDX on Table1(MostSelective,SecondMost,Least); CREATE NONCLUSTERED INDEX MyINDX2 on Table2(Least,SecondMost,MostSelective); INSERT INTO Table1 (MostSelective, SecondMost, Least) output inserted.* into Table2 SELECT TOP 26 REPLICATE(CHAR(number + 65),800), number/5, '~' FROM master..spt_values WHERE type = 'P' AND number >= 0 ORDER BY number; 

Agora fazendo uma consulta contra ambas as tabelas …

 SELECT * FROM Table1 WHERE MostSelective = REPLICATE('P', 800) AND SecondMost = 3 AND Least = '~'; SELECT * FROM Table2 WHERE MostSelective = REPLICATE('P', 800) AND SecondMost = 3 AND Least = '~'; 

… Ambos usam um índice bem e ambos recebem exatamente o mesmo custo.

insira a descrição da imagem aqui

A arte ASCII na resposta aceita não é, na verdade, como os índices são estruturados. As páginas de índice da Tabela 1 estão representadas abaixo (clique na imagem para abrir em tamanho real).

insira a descrição da imagem aqui

As páginas de índice contêm linhas contendo a chave inteira (neste caso, há realmente uma coluna de chave adicional anexada ao identificador de linha, pois o índice não foi declarado como único, mas isso pode ser desconsiderado. Informações adicionais sobre isso podem ser encontradas aqui ).

Para a consulta acima, o SQL Server não se preocupa com a seletividade das colunas. Ele faz uma busca binária da página raiz e descobre que a chave (PPP...,3,~ ) é >=(JJJ...,1,~ ) e < (SSS...,3,~ ) deveria ler a página 1:118 . Em seguida, ele faz uma pesquisa binária das inputs de chave nessa página e localiza a página de folhas para viajar até.

A alteração do índice em ordem de seletividade não afeta o número esperado de comparações de chaves da pesquisa binária ou o número de páginas que precisam ser navegadas para fazer uma busca de índice. Na melhor das hipóteses, pode acelerar marginalmente a comparação de chaves em si.

Às vezes, ordenar primeiro o índice mais seletivo fará sentido para outras consultas em sua carga de trabalho.

Por exemplo, se a carga de trabalho contiver consultas dos dois formulários a seguir.

 SELECT * ... WHERE MostSelective = 'P' SELECT * ...WHERE Least = '~' 

Os índices acima não estão cobrindo nenhum deles. MostSelective é seletivo o suficiente para fazer um plano com uma busca e pesquisas que valem a pena, mas a consulta contra Least não é.

No entanto, esse cenário (índice não abrangente que procura um subconjunto de colunas principais de um índice composto) é apenas uma class possível de consulta que pode ser ajudada por um índice. Se você nunca realmente procura por MostSelective por MostSelective só ou uma combinação de MostSelective, SecondMost e sempre busca por uma combinação de todas as três colunas, então essa vantagem teórica é inútil para você.

Por outro lado, consultas como

 SELECT MostSelective, SecondMost, Least FROM Table2 WHERE Least = '~' ORDER BY SecondMost, MostSelective 

Seria ajudado por ter a ordem inversa do comumente prescrito um - como abrange a consulta, pode suportar uma busca e retorna linhas na ordem desejada para arrancar.

Portanto, este é um conselho muitas vezes repetido, mas no máximo é uma heurística sobre o potencial benefício para outras consultas - e não é substituto para realmente observar sua carga de trabalho.