/* Gustavo Ferreira */

Com o passar do tempo e consequentemente o aumento da base de dados, as consultas podem ficar lentas. Se você já se deparou com problema de performance nas suas consultas ao banco de dados e nem se quer sabia que existia indices de tabelas, vai adorar saber o que são e o poder de um índice para o tunning de banco de dados.

O que são índices?

De acordo com Smith (2010, p. 209), "An index is simply an organized list of values that appear in one or more columns in a table. ". Ou seja, um valor/coluna utilizada para identificar as tuplas (linha, registro) de uma tabela e facilitar a localização em uma consulta, assim como em um índice de livro. Um índice não necessariamente precisa ser uma chave primaria (que não permite chaves iguais) e pode ser usado em valores que se repetem.

Tipos de índices

Existem muitos tipos de índices e cada SGBD (Sistema Gerenciador de Banco de Dados) tem os seus tipos de índices. Nesse artigo irei abordar os dois principais, o tipo BTREE e o tipo HASH.

Índice btree

Utilizado para indexar colunas que geralmente serão consultadas por intervalo. Por exemplo o campo salário, onde geralmente serão buscado as tuplas por um intervalo de valores desse campo.

Índice hash

Utilizado para indexar colunas que serão consultadas por um valor exato. Por exemplo o campo RG que apesar de poder repetir, geralmente serão consultado as tuplas usando a condição de igualdade para esse campo.

Entendendo as estruturas de dados

Tabela hash

Os índices do tipo hash usam o conceito de tabela hash onde a tupla não é pesquisada sequencialmente e sim calculada, ou seja, a posição da tupla no banco de dados é calculado por um algoritmo e a tupla é imediatamente acessada sem precisar percorrer uma a uma procurando pela condição.

Por exemplo que exista uma tabela com duas colunas e três linhas. Se o campo 'cpf' não tiver nenhum índice, ao buscar pelo cpf '132.546.879-10' o SGBD irá percorrer as três linhas a procura desse valor. Se o campo possuir um índice do tipo hash, quando o SGBD for processar a consulta ele aplicará um algoritmo em cima desse valor e o resultado desse cálculo será a posição da tupla no banco de dados, bastando seguir esse 'endereço' e recuperar a tupla sem necessidade de percorrer todos os registros.

Árvore binária

Nesse tipo de estutura as tuplas são distribuídas em uma estutura de árvore, colocando sempre o valor maior a direita e tendo sempre duas opções (nó). No exemplo ao lado, por exemplo que se deseje recuperar a tupla com o id '7'. O SGBD chega no primeiro nó e verifica se o valor pesquisado é maior ou menor, no caso é maior, então desce um nível e vai para a direita. Chegando no nó de id '8' verifica-se novamente e dessa vez constata que o valor procurado é menor, então desce um nível e vai para a esquerda e atinge o valor procurado. Percebe-se que a cada nó a quantidade de dados a serem analisadas caem pela metade pois só há dois caminhos para percorrer e somente um passa na validação. Pelo fato de haver validações e vários caminhos, é possivel fazer pesquisas por intervalo de dados.

Criando e testando índices no PostgreSQL

Com a teoria compreendida é possível decidir melhor o tipo de índice na hora de criar um. Vamos agora colocar em prática e criar uma tabela ficticia para aplicarmos os índices do tipo hash e do tipo btree. Para isso vou disponibilizar uma function que criará uma tabela com dados aleatórios para criar volume, se não houver um volume considerável de dados será difícil perceber a melhoria de performance.

CREATE OR REPLACE FUNCTION criarTabelaPopulada() RETURNS VOID AS
	$BODY$
		DECLARE
			lorota TEXT;
			i INTEGER;
		BEGIN
			i := 1;
			lorota := 'Caros amigos, o consenso sobre a necessidade de qualificação exige a precisão e a definição de todos os recursos funcionais envolvidos. Evidentemente, a complexidade dos estudos efetuados estimula a padronização dos relacionamentos verticais entre as hierarquias. Desta maneira, a contínua expansão de nossa atividade causa impacto indireto na reavaliação dos métodos utilizados na avaliação de resultados.';
			DROP TABLE IF EXISTS tabela_populada;

			CREATE TABLE tabela_populada (
				id_numerico INTEGER, id_literal VARCHAR(32), lorota1 TEXT, lorota2 TEXT, lorota3 TEXT, lorota4 TEXT, lorota5 TEXT, lorota6 TEXT, lorota7 TEXT, lorota8 TEXT, lorota9 TEXT, lorota10 TEXT
			);

			LOOP	
				INSERT INTO tabela_populada (id_numerico, id_literal, lorota1, lorota2, lorota3, lorota4, lorota5, lorota6, lorota7, lorota8, lorota9, lorota10)
				VALUES (i, MD5(i::TEXT), lorota, lorota, lorota, lorota, lorota, lorota, lorota, lorota, lorota, lorota);
				EXIT WHEN i > 1000000; --Se desejar criar menos registros, mude aqui
				i:=i+1;
			END LOOP;
		END;
	$BODY$
LANGUAGE plpgsql VOLATILE

1000000 (um milhão) de registros

Essa function executa uma estrutura de repetição par inserir 1000000 (um milhão) de registros na tabela que a própria função cria. O esquema da tabela é um campo numérico e outro literal. Para o campo numérico criaremos um índice do tipo btree e para o literal um índice do tipo hash. Além desses dois campos há mais dez campos do tipo TEXT simplesmente para engordar a base de dados.

Ao executar a função é bom você ir tomar um cafézinho, o resultado será uma tabela com 5 gigabytes de dados e deve demorar vários minutos para ser criada. No meu computador (i7 2.0, 4gb, Ubuntu 12.10 x64) levou 7 minutos. Para criar a função basta executar esse script como se fosse uma SQL comum. No caso eu utilizei o pgAdmim III. Para executar a função chame à com um select.

SELECT criarTabelaPopulada(); --Vai tomar um café

Criando o índice do tipo hash

Com a tabela criada e populada, primeiramente vamos testar o desempenho sem o índice. Se você percebeu, o campo indice_numerico foi criado com a função MD5 que gera um hash, muito utilizado para criptografia de dados. Poderia ter sido qualquer valor, só usei essa função para gerar um valor literal aleatório.

Para executar a consulta e observar o desempenho, execute o seguinte comando SQL e observar a saída:

EXPLAIN ANALYSE SELECT * FROM tabela_populada WHERE id_literal = MD5('500000');

Comigo a saída foi a seguinte:

Um tempo bastante considerável, não é mesmo? Vamos agora criar o índice. Para isso execute o script abaixo, lembrando que a indexação também leva alguns segundos porque o SGBD vai organizar as tuplas de acordo com o índice.

CREATE INDEX index_hash ON tabela_populada USING
HASH (id_literal);

Após a criação do índice, execute novamente a consulta feita anteriormente.

EXPLAIN ANALYSE SELECT * FROM tabela_populada WHERE id_literal = MD5('500000');

Observe a saída abaixo (no meu computador):

Observou a diferença? 29216.734 ms (sem index) X 0.041 ms (com index). Incrível não? Quase instantâneo!

Criando o índice do tipo btree

Para criar o índice do tipo btree é muito simples e parecido com outro que acabamos de ver. Primeiramente vamos testar uma consulta por intervalo sem índice:

EXPLAIN ANALYSE SELECT * FROM tabela_populada WHERE id_numerico BETWEEN 499998 AND 500001

Observe a saída abaixo:

Criando o índice do tipo btree:

CREATE INDEX index_btree ON tabela_populada USING
btree (id_numerico);

Após criar o índice, execute novamente a consulta e compare os resultados.

EXPLAIN ANALYSE SELECT * FROM tabela_populada WHERE id_numerico BETWEEN 499998 AND 500001

Observe o resultado abaixo:

Observaram novamente a diferença? Chega a ser escandalosa. Terminamos por aqui esse artigo e espero que aproveitem bem esta técnica que é um dos elementos do database tunning.