PostgreSQL

Criando índices otimizados

Técnicas para criação de índices otimizados em bancos de dados relacionais

Índices em tabelas de bancos de dados são ponteiros (ou atalhos) para os locais físicos dos registros, e possuem o mesmo objetivo que os índices de livros ou revistas. Quando criado, o SGBD automaticamente define um novo objeto, organizando fisicamente os ponteiros de registros do banco de dados na ordem das colunas (ou campos) selecionadas. O local onde este objeto é armazenado varia em cada SGBD.

Isto significa que ao acessar um índice, os registros da tabela são recuperados seguindo os ponteiros na ordem especificada durante sua criação, evitando que seja feita uma consulta entre todos os registros da tabela. Os índices não armazenam uma nova cópia ordenada dos registros.

Índices são os grandes responsáveis pela rapidez no acesso às informações, e por isso merecem atenção especial. Neste artigo, vamos verificar algumas técnicas para a criação de índices que ofereçam alto desempenho em consultas SQL.

Seletividade

Em uma tabela (ou relação) é possível existir diversos índices, que podem contemplar colunas anteriormente indexadas. Qualquer combinação de colunas é permitida e em qualquer ordem.

Como definir então qual índice será utilizado? Como escolher o mais rápido?

Quem faz esta escolha é o próprio SGBD, na função de otimizador de consultas. O SGBD coleta estatísticas de desempenho para os índices cada vez que uma consulta é realizada. A forma como são coletadas ou criadas estas estatísticas também varia conforme o SGBD utilizado, porém esta informação não faz parte do escopo deste artigo e, portanto, será ignorada.

Pesquisando as estatísticas, o SGBD escolhe o melhor índice para buscar os registros que atendem às condições SQL. Mas existem situações onde nenhum índice é escolhido. Neste caso ocorre a leitura seqüencial de toda tabela, também conhecida como “scan”, “table scan”, “sequential scan” ou “seq scan”.

Ao processo de escolha de um índice ou de uma leitura seqüencial através das estatísticas, dá-se o nome de plano de acesso. Nos testes realizados com PostgreSQL, pode-se utilizar o comando “EXPLAIN” antes da clausula SQL SELECT para obter o plano de acesso executado (“EXPLAIN SELECT”).

Sem dúvida, uma leitura seqüencial consome muito mais recursos do que uma consulta indexada. Isto porque nem sempre todos os registros da tabela estão armazenados na memória cache, forçando a leitura de todos os registros do disco para a memória.

A leitura seqüencial pode ocorrer nas situações:

·         Utilização de colunas não indexadas na clausula SQL “WHERE”;

·         Omissão da clausula SQL “WHERE” de uma consulta;

·         Tabela sem índices;

·         Índices mal estruturados.

Planos de acesso indexados dependem de alguns fatores decisivos. Neste artigo, iremos abordar alguns desses fatores nessa seqüência: seleção das colunas, disposição das colunas, ordem dos registros, quantidade de índices e índices individuais.

Preparando o ambiente de testes

Vamos utilizar como referência o SGBD PostgreSQL para citar os exemplos de comandos SQL. Certamente será possível executar os mesmos comandos em outro SGBD, com alguma pequena adequação nos códigos.

Os comandos SQL serão executados através do aplicativo pgAdmin III. Para usuários Windows, o instalador do PostgreSQL já inclui este aplicativo na instalação padrão. Para usuários Linux, será necessário baixá-lo através do gerenciador de pacotes da sua distribuição ou através do download do código fonte para compilação manual no site oficial do pgAdmin (http://www.pgadmin.org).

Não será descrito neste artigo os métodos para instalação e manuseio do PostgreSQL e do pgAdmin III. Esse tema já foi abordado outras vezes em artigos publicados na SQL Magazine, como no artigo “Utilizando a nova versão do PostgreSQL (8.1)” publicado por Arilo Cláudio Dias Neto na edição 28 da SQL Magazine, de forma que os leitores podem utilizá-lo como base para obter esta informação.

"