AN>
SQL Server
Microsoft SQL SERVER 2005 – Trabalhando com Índices
Dicas para definição e manutenção
Desde que começamos a trabalhar com bancos de dados, sempre nos vem a pergunta: como fazer para que as consultas X, Y ou Z rodem mais rapidamente?
A solução que temos é simples: criando índices nas tabelas consultadas.
Tem muita gente que acha que a criação de índices tem mais a ver com “arte” do que com ciência, mas esta questão é facilmente resolvida usando um pouco de bom senso.
Como funcionam os índices
No SQL SERVER, os índices funcionam literalmente como o índice de um livro. Quando o SQL SERVER armazena um registro de uma tabela, ele está escrevendo este registro no que chamamos de página. Este nome não é uma mera coincidência como veremos a seguir.
As páginas são áreas físicas do disco rígido que são gerenciadas pelo servidor SQL. Cada tabela pode usar centenas ou milhares de páginas, dependendo do tamanho desta tabela.
Quando apagamos um registro da tabela, estamos fazendo algo parecido como passar uma borracha e apagar uma linha de uma página do livro. Assim, ao apagarmos o registro, estamos deixando um espaço em branco perdido no meio do livro.
Um índice da tabela trabalha de forma idêntica ao índice de um livro. Ele aponta em que página devemos procurar a palavra que buscamos. Com isto não precisamos pesquisar o livro inteiro (ou a tabela toda, no nosso caso). Basta pesquisarmos na página indicada.
Naturalmente, os índices também são escritos
Quando pesquisamos uma tabela, o SQL SERVER verifica se ela possui um índice e se este índice será útil para o tipo de pesquisa a ser realizada. No caso da resposta ser positiva, ele pesquisa as páginas de índice, localizando mais rapidamente os registros desejados. Se a resposta for negativa, o SQL SERVER é obrigado a varrer todas as páginas de registros, o que acaba requerendo muito mais tempo de processamento (chamamos isso de table scan).
Os índices do SQL SERVER usam a estrutura lógica conhecida como B-tree. Existem outros modelos de indexação, mas os índices B-tree têm um bom desempenho para a grande maioria das situações em que usamos o SQL SERVER.
Ilustrando a criação de índices
Para ajudar nos nossos exemplos, vamos usar a mesma tabela que foi usada no Desafio SQL apresentado nesta revista. Trata-se de uma tabela com informações de endereços de clientes.
Para fins do nosso exemplo, vamos imaginar que a tabela que criamos contém 10 milhões de registros.
Mostramos na Listagem 1 o script de criação desta tabela, que chamamos de tblEndereco.
Listagem 1. Criação da tabela de exemplo para indexação.
CREATE TABLE tblEndereco (
pkEndereco INT NOT NULL,
Endereco VARCHAR(200) NOT NULL,
CEP INT NOT NULL,
Cidade VARCHAR(100),
Estado VARCHAR(2)
);
Tipos de índice do SQL SERVER
Basicamente, temos cinco tipos de índice no SQL SERVER 2005. O principal deles é o índice clustered (ou aglomerado, numa tradução livre).
Índice Clustered
Quando criamos um índice clustered em um campo de uma tabela, estamos informando ao SQL SERVER que todos os registros da tabela devem ser escritos ordenados pelo campo indexado. Lembra-se da cláusula ORDER BY? Pois é. É como se o SQL SERVER fizesse esta ordenação antes de escrever cada registro na tabela.
Portanto, o índice clustered afeta a maneira como os registros são fisicamente armazenados pelo SQL SERVER. Por conta desta característica, cada tabela pode ter um único índice clustered.
Quando definimos uma chave primária em uma tabela do SQL SERVER, estamos automaticamente definindo o índice clustered desta tabela. Este é o default. Portanto, uma tabela pode não ter chave primária, mas pode ter um índice clustered. Mas toda tabela que tem chave primária terá um índice clustered. Isso só não acontecerá se, no momento da criação da chave primária, definirmos explicitamente que não usaremos o índice clustered.
A vantagem do uso de índices clustered é simples: se os registros são escritos já corretamente ordenados em cada página, as páginas de índice acabam naturalmente sendo ordenadas também. Então vai ser mais fácil de localizar cada registro.
Vamos ilustrar o uso de índices clustered usando a tabela tblEndereco. Temos duas formas de implementar um índice clustered nesta tabela. Uma é explícita, ou seja, definindo diretamente um índice clustered. Escolhemos o campo pkEndereco para receber o índice. A Listagem 2 apresenta este exemplo.
Listagem 2. Criando um índice clustered.
CREATE CLUSTERED INDEX ix_tblEndereco_Endereco ON tblEndereco(pkEndereco);
Outra maneira de conseguir o mesmo resultado é definindo uma chave primária nesta tabela (ver Listagem 3). Este método eu chamo de implícito. Existe uma diferença sobre a definição de um índice clustered e a definição de chave primária. Voltaremos a esta questão mais adiante.
Listagem 3. Criando uma chave primária.
ALTER TABLE tblEndereco ADD CONSTRAINT pk_pkEndereco PRIMARY KEY (pkEndereco);
Índice Non-Clustered
O segundo tipo de índice é chamado de non-clustered. Índices non-clustered não afetam a forma como os dados são armazenados. Eles apenas mapeiam os valores distintos do campo indexado e gravam estes resultados nas páginas de índice.
Uma tabela pode ter vários índices non-clustered.
Caso a tabela possua um índice clustered, a criação dos índices non-clustered será baseada no índice clustered já existente. Isso quer dizer que o SQL SERVER usará as informações contidas nas páginas do índice clustered para construir as páginas dos índices non-clustered.
