Í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 principio 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 contem 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. Abaixo segue um exemplo de uma estrutura de B-Tree.

 
Figura 1: Estrutura 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.

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

Figura 2: Exemplo de índice.

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 das 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 seqüência do código 21 e pula para a página do nível intermediário que contem 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 contem 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

}

Algumas 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 freqüê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, porem 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, porem é utilizado para retornar sem necessidade de apontar para o cluster, e desta forma se ganha maior desempenho.

Obs: O Exemplo acima foi criado com a finalidade de demonstrar o uso de índices com colunas incluídas, sem levar em consideração análise de 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 seqüencial;

·         INDEX SCAN - Busca em todos os elementos de um índice nonclustered, de forma seqüencial;

·         INDEX SEEK - Busca binária num índice nonclustered;

·         CLUSTERED INDEX SCAN - Busca em todos os elementos de um índice clustered, de forma seqüencial;

·         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.

Figura 3: Exibição do Execution Plan.

A Figura 3 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.

Figura 4: Resumo do SELECT

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

Figura 5: Resumo das etapas para executar a instrução de SELECT conforme a Figura 4.

Obs: A tabela foi criada para demonstrar o Execution Plan. A mesma não possui nenhum registro e sendo assim os índices e as estatísticas também não possuem informações.

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, porem fica a dica.