Artigo SQL Magazine 10 - Entendendo e utilizando índices na otimização de queries no SQL Server

Entendendo e utilizando índices na otimização de queries no SQL Server

Criar um índice eficiente não é uma tarefa simples: requer conhecimento das queries em execução e dos diferentes tipos de índice disponíveis. A finalidade desse artigo é explicar como funcionam os índices e sua adaptabilidade as queries no universo do SQL Server 2000.

Estrutura interna de um índice

Índices são estruturas que possuem algoritmos otimizados para acessar dados. Assim como nas tabelas, páginas de índices também ocupam espaço físico. O corpo de um índice é formado pelas colunas da tabela cujos dados se deseja classificar seguido de uma referência conhecida como “ponteiro”, que serve para localizar a chave na página de dados da tabela. Existe também um tipo de índice especial que não utiliza ponteiros - conhecidos por cluster - que será detalhado adiante.

Índices no SQL Server 2000 são construídos sobre estruturas denominadas árvores balanceadas (=”B-Tree”), cujo desenho lembra o esqueleto de uma pirâmide. A idéia desse algoritmo é fornecer um modelo de pesquisa que agilize o processo de busca, efetuando um número reduzido de leituras nas páginas do índice para que se obtenha a localização da chave pesquisada. Quando procuramos por determinada palavra num livro, localizamos a(s) página(s) desejada(s) através de uma busca em seu índice. Se fossemos ensinar alguém como procurar a palavra “ADMIN” num livro de SQL Server, provavelmente ensinaríamos alguma coisa assim:

1.Localize o índice remissivo no final do livro;

2.Procure o bloco de palavras que iniciam pela letra “A”;

3.Efetue uma leitura seqüencial nesse bloco até localizar a palavra desejada. As páginas do livro que contêm referências à palavra “ADMIN” encontram-se listadas ao lado da palavra.

A figura 1 ilustra um processo de busca envolvendo a mesma pesquisa acima numa árvore B-Tree de um índice não-cluster. O processo tem início numa página-mestre conhecida como ”root page”, procurando pela maior chave da página cujo valor é menor ou igual à palavra pesquisada. Em nosso exemplo, a primeira palavra cujo código alfabético é menor ou igual à “ADMIN” é “ACESSO”, portanto seguiremos nessa direção até a página de número 2, localizada num nível intermediário conhecido por “non leaf level”. A busca é finalizada no nível folha ou “leaf level page”, onde encontramos a referência para a página de dados onde se localiza a palavra.

Tipos de índice existentes no SQL Server 2000

Existem dois tipos básicos de índice: cluster e não-cluster. Índices cluster impõem uma organização na própria página de dados da tabela, fazendo com que permaneçam classificadas de acordo com a composição de sua chave. Se você executar o comando select * from NorthWind.dbo.Orders irá notar que os pedidos são ordenados pela coluna OrderId, que faz parte do índice cluster PK_Orders. Podemos então afirmar que o leaf level de um índice cluster representa a própria página de dados da tabela, descartando a utilização de ponteiros para página de dados. Já índices não-cluster possuem estrutura própria, mantendo-se vinculados às páginas de dados pela utilização de ponteiros.

A tabela de sistema SysIndexes é responsável pelo armazenamento dos metadados do índice.  Nessa tabela localizamos o nome do índice, uma indicação de seu tipo (cluster/não cluster), o número de páginas utilizadas, o número de alterações desde que o último cálculo de estatísticas foi executado etc. Tabelas sem índice cluster - conhecidas por heaps - possuem uma linha em SysIndexes para IndId=0. Cada índice não cluster possuirá uma entrada para IndId no intervalo 2..250. Se uma tabela possuir índice cluster, este será identificado por IndId=1. Tabelas com índice cluster NÃO possuem entrada para IndId=0. Portanto, se você quiser listar as tabelas que não possuem índice cluster em seu database, basta selecionar as entradas em SysIndexes para IndId=0.

Figura 1. Exemplo de busca na árvore B-Tree.

Alocação e gerenciamento de espaço para índices e tabelas no SQL Server 2000

Páginas de dados de tabelas com índice cluster são “ligadas” umas às outras, isto é, no cabeçalho de cada página são encontradas referências à página anterior e posterior (=Next/Previous Page in Chain). Para um processo efetuar leitura seqüencial numa tabela com índice cluster – conhecida por clustered index scan – precisará apenas localizar a página inicial em "