Como é que um índice otimiza a execução de uma consulta?

O índice é uma estrutura de dados (geralmente uma árvore B) que armazena uma pequena parte dos dados da tabela e links para as linhas correspondentes na tabela principal.

Processo de otimização:

1. Busca rápida: Em vez de escanear toda a tabela (varredura completa), o sistema de gestão de bases de dados (SGBD) percorre o índice. O índice está ordenado e otimizado para buscas rápidas em colunas específicas.
2. Redução do volume de dados lidos: O índice contém apenas as colunas indexadas e ponteiros, o que reduz significativamente a quantidade de dados que precisam ser lidos do disco em comparação com a leitura de toda a linha ou página de dados.
3. Ausência de ordenação: O índice já está ordenado pelas colunas indexadas. Isso elimina a necessidade de realizar uma operação custosa de ordenação de conjuntos de dados se a consulta requer resultados ordenados.
4. Otimização de operações JOIN: Ao unir tabelas (JOIN) por colunas indexadas, o SGBD pode usar o índice para encontrar rapidamente as linhas correspondentes em outra tabela, em vez de realizar ciclos aninhados em toda a tabela.

Exemplo:

Sem índice na coluna LastName para a consulta SELECT * FROM Users WHERE LastName = 'Ivanov', o SGBD teria que ler cada linha na tabela Users e verificar o valor de LastName.

Com um índice em LastName, o SGBD encontra rapidamente os registros onde LastName é igual a 'Ivanov', revisando o índice ordenado e depois usando os ponteiros do índice para obter os dados completos das linhas na tabela principal.

-- Exemplo de consulta que se beneficia do índice em LastName
SELECT UserId, FirstName
FROM Users
WHERE LastName = 'Ivanov';

-- Exemplo de criação de um índice para otimizar a consulta acima
CREATE INDEX IX_Users_LastName ON Users (LastName);