Índice de cobertura (covering index)

Em vários artigos na web e na documentação do SQL Server são fornecidas dicas de como criar índices para atender às junções definidas na cláusula “FROM/ON” ou ainda aos predicados da cláusula WHERE. Mas o que nem sempre esses textos explicam é sobre os critérios que o otimizador de consultas (query optimizer, vide artigo O Plano Perfeito) utiliza para que um índice seja escolhido ao criar o plano de execução da consulta.

O banco de dados utilizado para demonstração neste artigo é o Northwind.

Caso 1. Como primeiro exemplo temos o seguinte código SQL que obtém informações do produto “Guaraná Fantástica”:

-- código #1
USE Northwind;

declare @NomeProd nvarchar(40);
set @NomeProd= 'Guaraná Fantástica';

SELECT P.ProductName, P.UnitPrice
  from dbo.Products as P
  where P.ProductName = @NomeProd;

que retorna o seguinte resultado:

p040_codigo #1

Há vários índices na tabela [Products] e um deles possui a coluna [ProductName] como chave:

p040_Products - indices

O que se espera é que as linhas cujo produto tenha como nome “Guaraná Fantástica” sejam obtidas de forma rápida, através de Index Seek.

Eis o plano de execução do código #1:

p040_codigo #1 - plano

Ops! O índice escolhido pelo otimizador de consultas não foi o índice [ProductName] (pela coluna de mesmo nome), com o otimizador de consultas optando por ler toda a tabela de produtos pelo índice [PK_Products]. Qual o motivo?

Podemos forçar que a consulta utilize o índice [ProductName] ao definirmos a dica de tabela “index”:

-- código #2
USE Northwind;

declare @NomeProd nvarchar(40);
set @NomeProd= 'Guaraná Fantástica';

SELECT P.ProductName, P.UnitPrice
  from dbo.Products as P with (index=ProductName)
  where P.ProductName = @NomeProd;

Eis o plano de execução do código #2:

p040_codigo #2 - plano

Agora sim o índice escolhido foi utilizado e com uma leitura direta! Mas ao visualizar o plano de execução acima observa-se a presença do operador “Key Lookup”. Qual o motivo?

Caso 2. Vamos agora analisar outro caso que é a junção de duas tabelas, [Customers] e [Suppliers], e estabelecer a relação por cidade/país entre clientes e fornecedores disponíveis na mesma cidade. O código SQL é o seguinte:

-- código #3
USE Northwind;

SELECT S.Country, S.City, C.CompanyName, S.CompanyName
  from dbo.Customers as C
       inner join dbo.Suppliers as S on S.City = C.City
                                     and S.Country = C.Country;

Ao rodar o código #3 temos o seguinte plano de execução:

p040_codigo #3 - plano

Ao visualizar o plano de execução acima nele também se observa a presença do operador “Key Lookup”. Qual o motivo?

Em alguns textos é mencionado que uma das regras para estabelecer junções com desempenho é ter índices pelas colunas que façam parte da cláusula ON. Sendo assim, neste caso bastaria criar índices pelas colunas [City] e [Country] nas duas tabelas e então rodar novamente o código SQL da consulta:

-- código #4
USE NorthWind;

CREATE nonclustered INDEX I4_Suppliers on Suppliers (City, Country);
CREATE nonclustered INDEX I5_Customers on Customers (City, Country);
go

Índices criados e então analisamos o plano de execução do código #3, após criação dos índices:

p040_codigo #3 - plano 2

O índice criado para a tabela [Customers] foi utilizado, e com leitura direta, mas o operador “Key Lookup” continuou no plano de execução. Qual o motivo?

Qual o motivo? No caso 1 são listadas 2 colunas, [ProductName] e [UnitPrice], sendo que no índice somente consta a coluna [ProductName]. Considerando isto, bem como outros fatores, o otimizador de consultas optou por fazer uma leitura completa pelo índice [PK_Products], pois nele estão presentes todas as colunas da tabela por ser índice do tipo clustered.

No caso 2 são listadas as colunas [Country] e [City], além das colunas [CompanyName] de cada tabela. Nos índices criados no código #3 estão presentes as colunas [Country] e [City] mas sem a coluna [CompanyName]. Considerando isto, bem como outros fatores, na tabela [Customers] o otimizador de consultas optou por fazer uma leitura direta (Index Seek) pelo índice [I5_Customers], mas associado a uma busca na tabela do conteúdo da coluna [CompanyName] através do operador “Key Lookup”.

Índice de cobertura. Para (tentar) entender o motivo das escolhas do otimizador de consultas é necessário conhecer também o conceito de índice de cobertura, que é o índice non clustered que contém todas as colunas que fazem parte da consulta: cláusulas ON, WHERE, GROUP BY e HAVING; e na lista de colunas a serem exibidas. Em resumo, que não seja necessário ir na tabela buscar informações adicionais, que é a função do operador “Key Lookup”.

Além disso, para obter melhor desempenho é necessário que sejam escolhidas colunas para atuar como chave, sendo geralmente as utilizadas nos predicados da cláusula WHERE ou da cláusula ON; a escolha depende das características da tabela e se se quer priorizar a leitura ou a junção.

No caso 1 foram utilizadas as colunas [ProductName] e [UnitPrice]; então o índice de cobertura tem que conter estas 2 colunas, seja fazendo parte da chave ou como coluna incluída. Assim, para o caso 1 é necessário criar o seguinte índice de cobertura:

-- código #5
USE Northwind;

CREATE nonclustered INDEX I7_Products 
     on Products (ProductName) include (UnitPrice);

A coluna [ProductName] consta como chave, pois ela é utilizada em predicado da cláusula WHERE. Já a coluna [UnitPrice], que consta somente na lista de colunas a exibir, é definida como coluna incluída.

Ao rodar novamente o código #1 temos o seguinte plano de execução:

p040_codigo #1 - plano 2

Pronto!

No caso 2 foram utilizadas as colunas [CompanyName], [City] e [Country], para cada tabela. Então o índice de cobertura terá que conter estas 3 colunas, seja fazendo parte da chave ou como coluna incluída. Assim, para o caso 2 é necessário criar o seguinte índice de cobertura:

-- código #6
USE Northwind;

-- apaga índices que não foram úteis
DROP INDEX I4_Suppliers on Suppliers;
DROP INDEX I5_Customers on Customers;
go

-- cria índices de cobertura
CREATE nonclustered INDEX I4_Suppliers 
     on Suppliers (City, Country) include (CompanyName);
CREATE nonclustered INDEX I5_Customers 
     on Customers (City, Country) include (CompanyName);
go

No código SQL acima inicialmente são apagados os índices criados no código #4, que foram inúteis, e a seguir criados os índices de cobertura. As colunas [City] e [Country] constam como chave, pois são utilizadas em predicado da cláusula FROM/ON. Já a coluna [CompanyName], que consta somente na lista de colunas a exibir, é definida como coluna incluída.

Ao rodar novamente o código #3 temos o seguinte plano de execução:

p040_codigo #3 - plano 3

Pronto! Tanto na leitura da tabela [Suppliers] quanto na tabela [Customers] foram utilizados os índices criados e, no caso desta tabela, com leitura direta.

Ganha e perde. É preciso ficar atento que ao criar novos índices:

  • ganha-se de um lado: melhora na execução de algumas consultas SQL; mas
  • perde-se de outro: espaço físico adicional para armazenar o índice bem como trabalho do database engine para manter o índice atualizado a cada ação INSERT e DELETE; e também UPDATE caso ocorra alteração nas colunas que fazem parte do índice.

Finalizando. Para este artigo foram utilizados exemplos simples pois o objetivo foi o de somente introduzir o leitor no conceito de “índice de cobertura”. Sabemos que os critérios que o otimizador de consultas utiliza para escolher índices (e modo leitura) é bem mais complexo do que o descrito neste artigo e que há contextos em que é preferível deixar que o plano de execução utilize o operador “Key Lookup” do que criar índice de cobertura. Mas após ler este artigo, da próxima vez que um índice criado não for utilizado você já sabe uma das prováveis causas: o índice não é de cobertura!

Embora os exemplos fossem de consulta (SELECT), os índices de cobertura também são úteis nas atualizações (UPDATE).

Aproveito para recomendar a leitura do Guia de arquitetura e design de índices do SQL Server como ponto de partida para compreender o que são índices, como criá-los etc.


PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para obter o artigo completo.

Índice de cobertura

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Crie seu site com o WordPress.com
Comece agora
%d blogueiros gostam disto: