Optimização de aplicações MySQL – parte I
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”! ;)
Artigos relacionados:
Optimização de aplicações MySQL – parte II
