Usando o Index Scan (Covered Index)

  • Article

Acabei de escrever sobre os conceitos do Table Scan (Aprendi SQL com Comandos Antigos), na qual ressalto a importância do Table Scan: retornar um grande número de registros de forma eficiente. Em seguida, comento sobre o Fim do Table Scan – ou melhor, não há motivos para realizar IAM Scan em um banco de dados. O motivo é que o Index Scan é geralmente uma estratégia mais eficiente.

Por exemplo, considere essa consulta:

SELECT nome, sobrenome FROM tbPessoas

Como a consulta não tem filtro, o caminho óbvio é realizar o Table Scan da tabela e retornar todos os registros. Entretanto, podemos criar um índice para “cobrir a consulta” usando somente os campos “nome” e “sobrenome”

CREATE INDEX coveredIndex ON tbPessoas ( nome, sobrenome )

É mais eficiente realizar um Index Scan sobre o “Covered Index” ao invés de fazer um Table Scan porque o índice é mais “magro” que uma tabela. Assim, podemos definir a estratégia de “covered index” como uma forma de reduzir o consumo de I/O.

Um exemplo ligeiramente diferente seria quando um índice para auxiliar a contagem:

SELECT COUNT(*) FROM tbPessoas WHERE nome LIKE N'F%';

A forma ineficiente para contar número de registros de uma tabela é através de um Table Scan. Por outro lado, poderíamos criar um índice sobre a coluna “nome” para facilitar a consulta, que usa esse índice para contar quantas pessoas começam com a letra “F”.

CREATE INDEX index ON tbPessoas (nome)

Entretanto, vamos supor que, ao invés de contar, precisamos retornar o nome e sobrenome das pessoas:

SELECT nome, sobrenome FROM tbPessoas WHERE nome LIKE N'F%';

Nesse caso, o ideal é criar um índice que inclua nome e sobrenome:

CREATE INDEX index ON tbPessoas ( nome , sobrenome )

 

Ordem dos Campos

Uma pergunta comum é se a ordem dos campos do índice importa. A resposta é SIM.

Em um índice tradicional, os dados são organizados em uma estrutura de dados denominada BTree (mais precisamente B+ Tree). Isso significa que os dados ficam ordenados pela primeira coluna e, em caso de empate, pela segunda coluna, depois pela terceira e assim por diante (se houver mais colunas). Por isso, os índices IDX1 e IDX2 ilustrados abaixo são diferentes:

CREATE INDEX idx1 ON tbPessoas ( nome, sobrenome )

CREATE INDEX idx2 ON tbPessoas ( sobrenome, nome )

O primeiro índice, idx1, está ordenado pela coluna “nome” e pode auxiliar as consultas que filtram pela coluna “nome”. O segundo índice, idx2, está ordenado por “sobrenome”. Por isso, cada tipo de consulta pode usar diferentes índices:

SELECT COUNT(*) FROM tbPessoas WHERE nome = ‘Fabricio’ -- Usando o primeiro índice

Enquanto que:

SELECT COUNT(*) FROM tbPessoas WHERE sobrenome = ‘Catae’ -- Vai pelo segundo índice

A regra é simples: somente as primeiras colunas são usadas para filtrar consultas, enquanto que as demais podem ser normalmente usadas para “cobrir a consulta”.

Veja alguns exemplos:

SELECT nome, email FROM tbPessoasl WHERE id = 1

Podemos criar um índice para filtrar “id = 1” ao mesmo tempo que cobrimos as colunas “nome” e “email”. Assim, o índice criado seria:

CREATE INDEX idxEmail ON tbPessoas ( id , nome, email )

Esse índice (idxEmail) seria usado para filtrar qualquer consulta baseada no ID. Entretanto, ela poderia cobrir somente consultas que retornam id, nome ou email. Por exemplo:

SELECT nome FROM tbPessoas WHERE id = 2

SELECT email FROM tbPessoas WHERE id = 3

SELECT id, nome, email FROM tbPessoas WHERE id = 4

 

Sintaxe do INCLUDE

Como vimos anteriormente, quando criamos um índice a ordem dos campos importa para filtrar a consulta.

Entretanto, a ordem dos campos não é importante para fazer a “cobertura da consulta”. Voltando ao exemplo inicial:

SELECT nome, sobrenome FROM tbPessoas

Podemos criar um covered index sem nos preocupar com a ordem das colunas:

CREATE INDEX coveredIndex ON tbPessoas ( nome, sobrenome )

CREATE INDEX coveredIndex ON tbPessoas ( sobrenome, nome )

Quando estamos incluindo campos para cobertura de consulta e sem filtros associados, podemos usar a sintaxe INCLUDE e deixar a sintaxe explicita:

CREATE INDEX coveredIndex ON tbPessoas (id) INCLUDE  ( sobrenome, nome )

Estamos especificando que o índice poderá ser usado para filtrar a coluna “id” ao mesmo tempo que pode cobrir os campos de “id”, “nome”, “sobrenome”.

 

Apesar do assunto ser fácil, o artigo ficou um pouco complicado. Por isso, se tiver dúvidas, não deixe de escrever um comentário.

Comments

  • Anonymous
    June 23, 2016
    Post bem resumido e de fácil entendimento. Muito bom!
  • Anonymous
    July 13, 2016
    Catae, ótimo artigo!Poderia falar um pouco mais sobre como você faz para definir a ordem dos campos para o índice, considerando consultas que tenham mais de um filtro (sendo assim necessário ter mais de um campo no índice, e não no INCLUDE)?
    • Anonymous
      July 15, 2016
      The comment has been removed