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

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.

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

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

Figura 3. Exibiç�o do Execution Plan

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.

Figura 4. Resumo do SELECT

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

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, por�m fica a dica.

�ndices no SQL Server

Veja nesse artigo como trabalhar com índices no banco de dados SQL Server.

Dicas a serem consideradas na hora de criar �ndices:

Mantendo a integridade dos �ndices

M�todos de acessos aos �ndices e tabelas

Artigos relacionados