Busca de Índice vs Varredura de Índice

Versão curta: procurar é muito melhor

Versão menos curta: a busca geralmente é muito melhor, mas muitas buscas (causadas por um design de consulta ruim com subconsultas correlacionadas desagradáveis, por exemplo, ou porque você está fazendo muitas consultas em uma operação de cursor ou outro loop) pode ser pior do que um scan, especialmente se sua consulta pode acabar retornando dados da maioria das linhas na tabela afetada.

Ele ajuda a cobrir toda a família de operações de localização de dados para entender completamente as implicações de desempenho.

Verificações de tabela: sem índices relevantes para sua consulta, o planejador é forçado a usar uma verificação de tabela, o que significa que cada linha é examinada. Isso pode fazer com que todas as páginas relacionadas aos dados da tabela sejam lidas do disco, o que geralmente é o pior caso. Observe que, para algumas consultas, ele usará uma varredura de tabela mesmo quando um índice útil estiver presente - isso geralmente ocorre porque os dados na tabela são tão pequenos que é mais trabalhoso percorrer os índices (se esse for o caso, você esperaria que o planeja mudar à medida que os dados crescem, assumindo que a medida de seletividade do índice é boa).

Varreduras de índice com pesquisas de linha: sem nenhum índice que possa ser usado diretamente para uma busca, mas um índice contendo as colunas certas está presente, uma varredura de índice pode ser usada. Por exemplo, se você tiver uma tabela grande com 20 colunas com um índice em column1,col2,col3 e emitir SELECT col4 FROM exampletable WHERE col2=616, nesse caso, verificar o índice para consultar col2é melhor do que verificar a tabela inteira. Depois que as linhas correspondentes são encontradas, as páginas de dados precisam ser lidas para coletar col4 para saída (ou junção adicional), que é o estágio de "pesquisa de marcador" quando você o vê nos planos de consulta.

Varreduras de índice sem pesquisas de linha: Se o exemplo acima foi SELECT col1, col2, col3 FROM exampletable WHERE col2=616, o esforço extra para ler as páginas de dados não é necessário: uma vez que as linhas de índice correspondentes col2=616são encontradas, todos os dados solicitados são conhecidos. É por isso que às vezes você vê colunas que nunca serão pesquisadas, mas provavelmente serão solicitadas para saída, adicionadas ao final dos índices - isso pode salvar pesquisas de linha. Ao adicionar colunas a um índice por esse motivo e somente por esse motivo, adicione-as com a INCLUDEcláusula para informar ao mecanismo que ele não precisa otimizar o layout do índice para consulta com base nessas colunas (isso pode acelerar as atualizações feitas nessas colunas) . As verificações de índice também podem resultar de consultas sem cláusulas de filtragem: SELECT col2 FROM exampletableverificará este índice de exemplo em vez das páginas da tabela.

Buscas de índice (com ou sem pesquisas de linha) : Em uma busca, nem todo o índice é considerado. Para a consulta SELECT * FROM exampletable WHERE c1 BETWEEN 1234 AND 4567o motor de consulta pode encontrar a primeira linha que irá corresponder fazendo uma pesquisa baseada em árvore no índice em c1seguida pode navegar no índice por ordem até chegar ao fim do intervalo (istoé o mesmo com uma consulta pois c1=1234pode haver muitas linhas correspondendo à condição mesmo para uma =operação). Isso significa que apenas as páginas de índice relevantes (mais algumas necessárias para a pesquisa inicial) precisam ser lidas em vez de todas as páginas do índice (ou tabela).

Índices clusterizados: com um índice clusterizado, os dados da tabela são armazenados nos nós folha desse índice, em vez de estarem em uma estrutura de heap separada. Isso significa que nunca será necessário haver pesquisas de linha extras depois de encontrar linhas usando esse índice, não importa quais colunas sejam necessárias [a menos que você tenha dados fora da página, como TEXTcolunas ou VARCHAR(MAX)colunas contendo dados longos].

Você só pode ter um índice clusterizado por esse motivo ^[1] , o índice clusterizado é sua tabela em vez de ter uma estrutura de heap separada, portanto, se você usar um ^[2] escolha onde colocá-lo com cuidado para obter o ganho máximo.

Observe também que o índice clusterizado porque a "chave de clustering" para a tabela e está incluído em todos os índices não clusterizados na tabela, portanto, um índice clusterizado amplo geralmente não é uma boa ideia.

^{[1] Na verdade, você pode efetivamente ter vários índices clusterizados definindo índices não clusterizados que cobrem ou incluem todas as colunas da tabela, mas é provável que isso seja um desperdício de espaço e tenha um impacto no desempenho de gravação, portanto, se você considerar fazer isso, certifique-se de você realmente precisa.}

^{[2] Quando digo "se você usar um índice clusterizado", observe que geralmente é recomendável que você tenha um em cada tabela. Existem exceções, como em todas as regras práticas, tabelas que veem pouco além de inserções em massa e leituras não ordenadas (talvez tabelas de teste para processos ETL) são o exemplo de contador mais comum.}

Ponto adicional: Varreduras incompletas:

É importante lembrar que, dependendo do restante da consulta, uma varredura de tabela/índice pode não varrer toda a tabela - se a lógica permitir, o plano de consulta poderá fazer com que ele seja abortado mais cedo. O exemplo mais simples disso é SELECT TOP(1) * FROM HugeTable- se você observar o plano de consulta para isso, verá que apenas uma linha foi retornada da verificação e, se observar as estatísticas de E/S ( SET STATISTICS IO ON; SELECT TOP(1) * FROM HugeTable), verá que ele lê apenas um número muito pequeno de páginas (talvez apenas uma).

O mesmo pode acontecer se o predicado de uma cláusula WHEREor JOIN ... ONpuder ser executado simultaneamente com a varredura que é a fonte se seus dados. Às vezes, o planejador/executor de consultas pode ser muito inteligente em empurrar predicados de volta para as fontes de dados para permitir o término antecipado de varreduras dessa maneira (e às vezes você pode ser inteligente em reorganizar consultas para ajudá-lo a fazer isso!). Enquanto os dados fluem da direita para a esquerda conforme as setas na exibição do plano de consulta padrão, a lógica é executada da esquerda para a direita e cada etapa (da direita para a esquerda) não é necessariamente executada até a conclusão antes que a próxima possa começar. No exemplo simples acima, se você observar cada bloco no plano de consulta como um agente, o SELECTagente solicitará ao TOPagente uma linha que, por sua vez, solicitará aoTABLE SCANagente para um, então o SELECTagente pede outro, mas o TOPagente sabe que não há necessidade nem se incomoda em perguntar ao leitor da tabela, o SELECTagente recebe uma resposta "não mais é relevante" e sabe que todo o trabalho está feito. Muitas operações bloqueiam esse tipo de otimização com tanta frequência em exemplos mais complicados uma varredura de tabela/índice realmente lê cada linha, mas tome cuidado para não chegar à conclusão de que qualquer varredura deve ser uma operação cara.

Geralmente, buscas são boas, varreduras são ruins.

As buscas são onde a consulta é capaz de fazer uso efetivo do índice e usá-lo para encontrar as linhas de que precisa.

As varreduras são onde a consulta está examinando todo o índice tentando encontrar o que precisa.

Como o SQL escolhe? Nas profundezas do otimizador de consultas, a decisão é tomada com base em sua consulta e nos índices disponíveis e nas informações estatísticas associadas a esses índices.

Existem alguns livros para ler que podem ser interessantes aqui - Ambos da livraria Red-Gate em http://www.red-gate.com/community/books/

• Planos de execução do SQL Server por Grant Fritchey
• Dentro do Otimizador de Consultas por Benjamin Nevarez
• Estatísticas do SQL Server por Holger Schmeling

Se você quiser se aprofundar no assunto, um livro muito útil (pelo menos para mim) é SQL Server Execution Plans de Grant Fritchey, disponível gratuitamente no RedGate aqui .

Se você tiver uma consulta como

SELECT *
FROM myTable

O SQL Server provavelmente usará uma verificação de índice, pois precisa passar por todas as linhas para exibir os resultados necessários.

Pelo contrário,

SELECT *
FROM myTable
WHERE myID = 1

certamente resultará em uma busca de Índice. O SQL Server usará a estrutura de árvore B do índice myID e recuperar a linha apropriada será muito mais rápido.

Outros definiram bem as diferenças entre buscar e escanear. Nesse caso, sua própria consulta e o planejador de execução devem fornecer as informações necessárias para ver quais valores são usados ​​como predicados (filtros) para a consulta em cada parte. Normalmente, é uma boa prática sempre adicionar índices não clusterizados em chaves estrangeiras e, dependendo dos casos de uso no código do programa, você pode querer criar índices de várias colunas adicionais ou índices de coluna incluídos também. Com a terminologia apresentada aqui, uma pesquisa no google dará resultados decentes em exemplos de cada um.

Mas, como exemplo, digamos que seu código esteja consultando a Coluna A e a Coluna B em determinados filtros, mas você também deseja retornar os valores da Coluna C e da Coluna E, talvez queira criar um índice na Coluna A e B com o INCLUDE opção contendo as Colunas C e E. Dessa forma, uma única busca de índice retornará tudo o que você precisa, pois não há necessidade de fazer uma pesquisa para recuperar os outros valores (C e E) na mesma linha.