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 sequencial 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 sequêncial 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 sequêncial 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 sequêncial 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.
Fica também aberto ao leitor utilizar um banco de dados existente para a realização de testes. Os exemplos citados neste artigo são meramente ilustrativos, não considerando regras de normalização, segurança ou orientação a objetos.
Está sendo disponibilizado no portal da revista SQL Magazine um script gerado pelo pg_dump.exe (formato texto plano) para criação do banco de dados com os registros utilizados na elaboração deste artigo. Com isso, os leitores podem acompanhar todos os passos seguidos nesse artigo com os mesmo resultados descritos aqui.
O arquivo sqlindex.sql contendo o script para criação do banco de dados possui 70 Mbytes aproximadamente. Para restaurá-lo, crie um novo banco de dados chamado sqlindex através do pgAdmin 3 utilizando o encoding Latin1, e através da janela de execução de comandos abra o arquivo e execute-o. Um método alternativo pode ser feito através do MS-DOS, utilizando o utilitário psql.exe (para usuários de Windows será necessário estar dentro da subpasta “bin” do diretório de instalação do PostgreSQL):
psql –U postgres –f -d sqlindex
Substitua o parâmetro pelo local relativo onde o arquivo foi salvo e descompactado. Por exemplo: se o arquivo encontra-se na pasta “teste” dentro da unidade raiz do sistema operacional, utilize:
...
