Índices no SQL Server

Índices nos bancos de dados são utilizados para facilitar a busca de informações em uma tabela com o menor número possível de operações de leituras, tornado assim a busca mais rápida e eficiente.

O exemplo clássico para explicar a utilização de índices é comparar uma tabela do banco de dados a uma lista telefônica, onde a mesma possui um índice por ordem alfabética do sobrenome dos “participantes”. Sabendo a letra inicial do sobrenome é possível refinar a pesquisa iniciando a mesma pela página correspondente a letra do sobrenome.

O SQL Server utiliza o mesmo princípio da lista telefônica gravando as informações dos índices em uma estrutura chamada de B-Tree.

Uma estrutura B-Tree possui um nó-raiz que contém uma única página de dados, uma ou mais páginas de níveis intermediários e uma ou mais páginas de níveis folhas. Na Figura 1 segue um exemplo de uma estrutura de B-Tree.

Uma B-Tree sempre é simétrica, ou seja, possui o mesmo número de páginas à esquerda e a direita de cada nível.

Obs: Uma página no SQL Server armazena até 8.060 bytes de dados.

Na Figura 2 mostra-se um exemplo de índice em uma estrutura B-Tree para um campo código do tipo inteiro.

Para construir os níveis raiz e intermediário pega-se o primeiro valor de cada página do nível abaixo junto com o ponteiro da página de onde o valor de dados veio. A cada instrução de inserção, exclusão ou até mesmo alteração é modificado a estrutura dos índices. No caso de as páginas utilizadas pelo índice estarem cheias, acontece um processo chamado de divisão de página (page splitting) para comportar a nova estrutura com mais páginas.

Uma busca pelo índice inicia-se no nível raiz percorrendo todas as linhas até achar a cadeia de valores a qual o mesmo se encaixa e através do ponteiro pular para a página do nível intermediário que o mesmo se refere. No nível intermediário repete o mesmo processo até achar a cadeia de valores e pular para a página de nível folha conforme o ponteiro. No nível folha novamente repete-se o processo até achar o valor desejado e nesse momento é localizado os dados necessários.

Por exemplo, conforme a Figura 2, para achar o código 23 iniciaria a busca pelo nível raiz percorrendo as linhas. Como o código 23 está entre 21 e 41 o SQL Server calcula que o código 23 se encontra na sequência do código 21 e pula para a página do nível intermediário que contém os valores 21 a 31. Em seguida analisaria que a primeira opção (21) se encaixa para a busca e pularia para a página de nível folha que contém a cadeia de 21 a 30, percorreria a mesma até achar o código 23 e finalizaria a busca. No SQL Server é possível criar índices clusterizados (clustered), não clusterizados (nonclustered), XML e espaciais, sendo os dois últimos para melhorar os recursos de pesquisas em documentos XML e aplicações espaciais, e neste artigo os mesmos não serão abordados. Para maiores esclarecimentos consultar o books online.

Índices clusterizados são ordenados conforme a chave do cluster fornecendo assim uma ordem de classificação para o armazenamento da tabela. Esta ordem de classificação não é a ordem física dos dados e sim a classificação lógica das páginas do índice. É possível definir somente um índice clusterizado por tabela, pois a mesma só pode ser ordenada de uma única maneira.

Índices não clusterizados não classificam ordens e portando é possível criar até 1000 índices nonclustered por tabela tendo cada um no máximo 900 bytes na chave de índice e no máximo 16 colunas. Ao percorrer um índice não clusterizado até seu nível folha em busca do ponteiro para retornar os dados necessários, o mesmo trata os seguintes casos:

• Existe índice clusterizado na tabela, desta forma o ponteiro aponta para a chave do cluster.
• Não existe índice clusterizado na tabela, então o ponteiro aponta para a linha de dados da tabela.

A sintaxe para criação de um índice no SQL Server é a seguinte:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
[ ; ]
 
::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
        table_or_view_name
}
 
::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Dicas a serem consideradas na hora de criar índices:

Campos para serem indexados a fim de ganhar desempenho:

• Chaves Primárias;
• Chaves Estrangeiras;
• Colunas acessadas por ranges (between);
• Campos utilizados em group by ou order by;

Campos que não devem ser indexados:

• Campos dos tipos: text, image, decimais;
• Campos calculados;
• Campos com alta cardinalidade (Masculino ou Feminino);

Criar índices para campos que compreendem uma query que é utilizada com frequência. Nesse caso de um índice construído de modo que o SQL Server possa satisfazer as consultas completamente lendo apenas o mesmo é chamado de índice de cobertura (covering indexes).

Criar índice clusterizado para campos de chave primária com a propriedade identity.

Criar índice com colunas incluídas (Include). Por exemplo:

CREATE TABLE clientes
(
      Cod INT IDENTITY(1,1) PRIMARY KEY,
      Nome VARCHAR(100),
      UF VARCHAR(2),
      CEP VARCHAR(8)
)

No caso acima é automaticamente criado um índice clusterizado para a chave primária. Vamos seguir o exemplo dizendo que é necessário rodar a seguinte query:

SELECT CEP FROM clientes WHERE UF='RS'

Analisando a query (com fins de demonstrar o exemplo) poderia criar um índice nonclustered para o campo UF. Nesse caso o índice ajudaria a encontrar o valor desejado de uma forma mais rápida, porém o processo de pesquisa seria percorrer o índice e achar o valor desejado que obrigatoriamente o ponteiro apontaria para o cluster que por sua vez apontaria para o dado físico que por sua vez retornaria o valor CEP. Para facilitar esse processo pode-se criar o seguinte índice:

CREATE NONCLUSTERED INDEX IDX_UF ON clientes (UF ASC) INCLUDE(CEP)

Com o índice criado, ao percorrer o mesmo e encontrar o valor para UF desejado o campo CEP incluído não faz parte da indexação, porém é utilizado para retornar sem necessidade de apontar para o cluster, e desta forma se ganha maior desempenho.

Mantendo a integridade dos índices

Tabelas que sofrem muitas alterações (Insert, Update e Delete) refletem essas modificações nos índices, pois acabam deixando espaços em brancos nas páginas dos mesmos. Estes espaços não utilizados refletem em maior espaço em disco o que acarreta um desperdício de tempo ao percorrer a estrutura do índice.

Para resolver esses problemas é necessário manter a integridade dos índices, utilizando os seguintes comandos:

ALTER INDEX {nome_indice | ALL} ON REBUILD
ALTER INDEX {nome_indice | ALL} ON REORGANIZE

A opção REORGANIZE remove somente a fragmentação no nível folha e a opção REBUILD reconstrói todos os níveis do índice.

Para os comandos acima citados, é possível substituí-los respectivamente por:

DBCC DBREINDEX
DBCC INDEXDEFRAG

Métodos de acessos aos índices e tabelas

Os acessos aos dados das tabelas e índices podem ser de duas formas: SEEK ou SCAN.

• SCAN - busca em TODOS os elementos da estrutura (que pode ser uma tabela ou um índice). É usado quando não possui índices que atendam a instrução de select ou quando a quantidade de registros que a query retorna (em percentual) é grande.
• SEEK - busca binária nos elementos de um índice. É usado quando existe um índice que é adequado e a quantidade de registros (em percentual) retornados é pequena.

Sendo assim, é possível executar as seguintes operações para acesso nas tabelas/índices:

• TABLE SCAN - Busca em todos os elementos da tabela, de forma sequencial;
• INDEX SCAN - Busca em todos os elementos de um índice nonclustered, de forma sequencial;
• INDEX SEEK - Busca binária num índice nonclustered;
• CLUSTERED INDEX SCAN - Busca em todos os elementos de um índice clustered, de forma sequencial;
• CLUSTERED INDEX SEEK - Busca binária num índice clustered.

Quando uma tabela possui mais de um índice, o SQL Server precisa tomar uma decisão de qual (is) deles utilizar em uma consulta de dados. Esta escolhe se dá através das estatísticas (statistics) de acesso. Quando um índice é criado, o SQL Server gera uma estrutura chamada histograma, que armazena informações sobre a distribuição relativa de valores de dados de uma coluna. À medida que o número de valores exclusivos dentro de uma coluna aumenta, a seletividade de um índice aumenta. Os índices mais seletivos são os escolhidos para satisfazerem uma consulta.

Da mesma forma que um índice pode ser danificado com o passar do tempo, o mesmo acontece para as estatísticas. Para atualizar as estatísticas basta rodar a sintaxe abaixo:

UPDATE STATISTICS <nome_tabela>

O comando acima pode ser substituído pela a seguinte store procedure: SP_UPDATESTATS.

Nota-se que a store procedure não possui nenhum parâmetro e desta forma ela atualiza todas as estatísticas de todas as tabelas do banco de dados no qual a mesmo foi executada.

É possível através do SQL Server monitorar as escolhas que o mesmo fez para acessar os dados em uma instrução de busca. Utilizando a ferramenta Microsoft SQL Server Management Studio é possível visualizar todo o processo de leitura ao rodar uma query habilitando o Execution Plan, como mostra a Figura 3.

A Figura 4 demonstra como o SQL Server procedeu para realizar a query. Foi utilizado o SELECT descrito neste artigo ao abordar a criação de índices com colunas incluídas.

A Figura 5 traz as informações referentes à instrução de SELECT conforme a Figura 4.

O SQL Server dispõe de uma ferramenta chamada Database Engine Tuning Advisor que é muito útil para analisar os índices existentes em cima de instruções de acesso a uma base dados para sugerir melhorias referentes aos mesmos. Nesse artigo não será abordado como utilizar a mesma, porém fica a dica.