Acontece frequentemente não se pensar muito bem na estrutura de uma base de dados para um site aquando da criação da mesma. O que é certo é que, mais tarde ou mais cedo, dependendo do crescimento do site em termos de informação, os erros de estruturação da base de dados vão-se fazer notar. E normalmente, quando damos conta, temos em mãos um sério problema para resolver, com algumas implicações.

Aconteceu-me recentemente isto que acabei de descrever, e, na tentativa de optimizar estrutura da informação da base de dados em questão, procurei saber mais acerca da indexação da informação e da forma como o MySQL usa esta indexação.

Índices: o que são?

Os índices podem ser entendidos como o turbo do MySQL. São uma versão organizada de campos específicos de uma tabela e são usados para facilitar a consulta de registos. Sem índices, o MySQL necessita percorrer todos os registos de uma tabela para encontrar o registo pretendido, o que não acontece quando se usam índices. Neste caso, o MySQL consegue “saltar” directamente para o registo pretendido. Com o seu uso, as consultas de informação tornam-se mais rápidas e eficazes, poupando tempo de processamento.

Tipos de Índices

Existem vários tipos de índices disponíveis no MySQL:

• Índices normais - são o tipo de índice mais básico, e não possuem restrições tais como a unicidade;
• Índices únicos - semelhantes aos índices normais, mas com uma diferença: todos os valores do(s) campo(s) indexado(s) apenas podem aparecer uma vez;
• Chaves primárias - índices únicos com nome “PRIMARY” e com a particularidade de apenas poder existir um por tabela;
• Índices full-text - usados pelo MySQL para pesquisas em campos de texto (para não fugir ao tema, sugiro a leitura da documentação do MySQL para mais detalhes sobre este tipo de índices).

Definição de Índices

Os índices podem ser aplicados individualmente a campos de uma tabela da base de dados (índices simples), ou então aplicados a vários campos de uma tabela (índices compostos).

Suponhamos que temos a seguinte tabela:

CREATE TABLE pessoas (
    id INT NOT NULL AUTO_INCREMENT,
    primeiro_nome CHAR(30) NOT NULL,
    ultimo_nome CHAR(30) NOT NULL,
    idade SMALLINT NOT NULL,
    PRIMARY KEY (id)
);

O objectivo é usar esta tabela para obter os valores do campo id para registos cujos valores do primeiro nome, último nome e idade são conhecidos. Um exemplo de consulta seria pesquisar todos os registos com o nome Hugo Durães e 24 anos de idade:

SELECT id FROM pessoas WHERE primeiro_nome='Hugo' AND ultimo_nome='Durães' AND idade=24;

Como não queremos que o MySQL faça uma pesquisa em todos os registos da tabela, o uso de índices é altamente recomendado.

Na minha opinião, o primeiro passo seria criar um índice para um dos campos da claúsula WHERE (primeiro_nome, ultimo_nome ou idade). Se o índice fosse criado no campo primeiro_nome, o MySQL iria limitar a pesquisa aos registos para os quais o campo primeiro_nome fosse ‘Hugo’. Com este resultado temporário, iria aplicar as restantes condições: primeiro iria eliminar todos os registos cujo ultimo_nome fosse diferente de ‘Durães’ e de seguida eliminaria os registos nos quais a idade fosse diferente de 24. Só após isto o MySQL poderia devolver os resultados da consulta.

A criação deste índice torna a consulta mais eficiente, mas ainda obriga o MySQL a trabalhar com registos não necessários. Poderíamos colocar o índice em qualquer outro dos campos, mas os resultados em termos de eficiência seriam muito idênticos.

É aqui que entram os índices compostos. Um índice composto é na verdade um array ordenado que contém concatenados os diferentes valores dos campos que pertencem ao índice composto.

Assim, para o exemplo mostrado acima, seria criando um índice composto pelos campos primeiro_nome, ultimo_nome e idade. Desta forma, o MySQL pode “saltar” imediatamente para o primeiro_nome correcto, depois para o ultimo_nome e finalmente para a idade correcta. A informação foi assim encontrada sem ser necessário percorrer qualquer registo da tabela.

Mas não será a criação deste índice composto a mesma coisa que criar um índice para cada campo da claúsula WHERE? Não, porque ao executar uma consulta, o MySQL apenas usa um índice. Se tiver que existir uma escolha entre vários índices, o MySQL usa normalmente o índice mais restritivo, ou seja, o que devolve o menor número de registos. Ainda assim, qualquer um destes índices nunca é tão restritivo como o índice composto pelos três campos da claúsula WHERE.

Da esquerda para a direita

Os índices múltiplos tem uma vantagem acrescida: são lidos da esquerda para a direita. Assim, e usando o exemplo acima, o índice será usado para consultas de acordo com a seguinte combinação:

• primeiro_nome, ultimo_nome, idade
• primeiro_nome, ultimo_nome
• primeiro_nome

Assim, o índice criado é usado nas seguintes consultas (alguns exemplos):

SELECT * FROM pessoas WHERE primeiro_nome='Hugo';

SELECT * FROM pessoas
  WHERE primeiro_nome='Hugo' AND ultimo_nome='Durães';

SELECT * FROM pessoas
  WHERE primeiro_nome='Hugo' AND ultimo_nome='Durães' AND idade=24;

SELECT * FROM pessoas
  WHERE primeiro_nome='Hugo'
  AND (ultimo_nome='Durães' OR ultimo_nome='Fernandes') AND idade=24;

SELECT * FROM pessoas
  WHERE primeiro_nome='Hugo'
  AND ultimo_nome='Durães' AND idade > 20 AND idade < 26;

No entanto, o índice criado não é usado nas seguintes consultas (alguns exemplos):

SELECT * FROM pessoas WHERE ultimo_nome='Durães';

SELECT * FROM pessoas
  WHERE primeiro_nome='Hugo' OR ultimo_nome='Durães';

SELECT * FROM pessoas
  WHERE idade=24 AND primeiro_nome='Hugo';

Em que campos devem ser criados índices?

Um dos passos mais importantes é a escolha dos campos onde criar os índices. Existem dois locais fulcrais para a criação de índices: campos referenciados na claúsula WHERE e campos usados na claúsula JOIN.

Então basta seguir a “regra” e criar índices em todos os campos que estejam nessas condições? Quase, mas nem sempre. É necessário ter em conta o tipo de comparações que vão ser efectuadas. O MySQL apenas usa índices para comparações do tipo <, <=, =, >, >=, BETWEEN, IN e em algumas do tipo LIKE. Nas comparações do tipo LIKE, o MySQL não usa índices caso o primeiro caracter de pesquisa seja uma wildcard (% ou _).

Desvantagens dos índices

É bastante comum, em aplicações web, existirem mais pedidos simultâneos de consultas do que pedidos de actualização de informação, visto que o número de pessoas a consultar uma aplicação deste tipo é, normalmente, bem maior que o número de pessoas que actualizam a informação. Assim, o ideal será dar prioridade a estas consultas, deixando para segundo plano as actualizações de informação.

A criação de índices, com vista a indexar a informação, resulta numa melhoraria das consultas de informação. Mas, em contrapartida, estamos a dificultar os pedidos de actualização de informação. Logo, a definição dos índices tem de ser bem estudada, de modo a que seja aplicada apenas sobre pontos críticos.

O uso de índices provoca um aumento do espaço ocupado em disco. Assim, a criação de índices não estudada pode provocar um aumento exagerado do tamanho da informação indexada, podendo esta chegar ao seu limite físico em termos de tamanho em disco.

Concluindo…

O uso de índices é um dos aspectos mais importantes para optimizar bases de dados. Não importa o quão simples é uma tabela: uma pesquisa numa tabela não indexada com 1.000.000 de registos nunca será rápida e leve.

Este artigo tentou abordar os aspectos mais importantes relativos ao tema. No entanto, se pretender saber mais, aconselho a consulta do tópico Optimizing Database Structure do manual do MySQL.

Agora quero ver o MySQL a correr “sem espinhas”!