Blog do Durães

Vimos na primeira parte deste artigo que uma das melhores formas de optimizar o acesso à informação no MySQL era o uso de índices. Para além do uso de índices, a optimização de joins entre tabelas é também bastante influente na performance do MySQL. Assim, este artigo tentará explicar como se comporta o MySQL na presença de joins e de que forma estes podem ser melhorados. Tudo em prol da optimização, claro! :)

Só para garantir que todos sabem do que estamos a falar, vou começar por definir um join. Um join, em termos matemáticos, é a intersecção de dois conjuntos. O que isto significa é que dois conjuntos são combinados e todos os valores comuns a ambos constituem o resultado do join.

Entendida a definição de join, é importante referir que ao efectuar um join é sempre necessário especificar uma condição de join. Caso contrário, o resultado obtido será o produto cartesiano das duas tabelas envolvidas no join, ou seja, cada registo na tabela A irá aparecer uma vez para cada registo da tabela B. Isto é o mesmo que dizer que o resultado vai ser composto por X * Y linhas, onde X é o número de registos da primeira tabela e Y o número de registos da segunda tabela.

Para completar as definições: a condição de join especifica quais as colunas em cada uma das tabelas que vão ser comparadas e como vão ser comparadas (na maioria dos casos as colunas são comparadas pela igualdade, mas nem sempre isto acontece).

Para ilustrar o que foi dito, deixo um pequeno exemplo:

Tabela de empregados (emp)

Tabela de departamentos (dpto)

Resultado do join

A operação de join da tabelas de empregados com a tabela de departamentos, pelo campo dpto.id produziria o seguinte resultado:

O query SQL para obter este resultado seria:

SELECT emp.id, emp.nome, dpto.id, dpto.nome
FROM emp, dept
WHERE emp.dpto = dpto.id

Optimização de joins

A primeira coisa a ter em conta acerca da optimização da performance de joins é que esta pode diminuir consideravelmente à medida que a terceira, quarta ou mesmo quinta tabela é adicionada à consulta. Quanto mais tabelas são adicionadas, mais importante é garantir que a consulta está optimizada e testada.

Existem diversas razões para este decréscimo de performance:

• Quanto mais tabelas estiverem envolvidas, mais registos terão de ser lidos e a probabilidade de estes ficarem em cache será cada vez menor, o que implica leituras a partir do disco rígido;
• Quanto mais tabelas estiverem envolvidas, mais filtragens e ordenações têm de ser feitas. Estas são operações intensivas que consomem bastante CPU e memória, provocando assim um decréscimo da performance;
• À medida que o tamando do resultado (número de linhas e colunas a serem devolvidos) aumenta, é cada vez menos provável que as operações de ordenação possam ser feitas em memória e mais provável que os dados tenham de ser escritos em disco rígido para que possam ser ordenados. O acesso ao disco é relativamente mais lento e consome também muito CPU;
• Num ambiente interactivo, quanto mais tabelas estiverem envolvidas e quanto mais tempo demorar uma consulta, maior é a probabilidade de alguém efectuar actualizações aos registos nas tabelas que estão a ser lidas, o que faz com que porções do resultado tenham de ser lidos novamente de forma a garantir uma visualização consistente dos dados.

Construir bases de dados com bom desempenho

Relativamente aos joins, existem duas formas de maximizar a performance. Uma delas é desenhar a estrutura da base de dados de forma a minimizar o uso de joins. A outra forma consiste em maximizar a performance, optimizando as consultas.

Tanto de uma forma como outra, é bom saber de que forma determinada estrutura de tabelas e determinada consulta poderão afectar a performance da nossa aplicação. Para percebermos o que está a acontecer à medida que decorre o desenvolvimento, existe o comando EXPLAIN do MySQL. Este comando examina uma dada consulta, para verificar se esta está o mais optimizada possível, usando índices sempre que estes podem ser usados.

A sintaxe deste comando é a seguinte:

EXPLAIN SELECT *
FROM tabA, tabB, tabC
WHERE tabA.col1 = tabB.col1 AND tabA.col2 = tabC.col1;

Este comando devolve uma tabela de informação semelhante à representada em seguida:

+-------+------+---------------+------+---------+------+------+-------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+-------+------+---------------+------+---------+------+------+-------------+
| tabA  | ALL  | NULL          | NULL |  NULL   | NULL | 1000 |             |
| tabB  | ALL  | NULL          | NULL |  NULL   | NULL | 1000 | Using where |
| tabC  | ALL  | NULL          | NULL |  NULL   | NULL | 1000 | Using where |
+-------+------+---------------+------+---------+------+------+-------------+

Mas o que significam todas as colunas devolvidas?

• table – mostra o nome da tabela à qual o resultado diz respeito (para quando são efectuados JOINs entre tabelas);
• type – tipo de join usado. Do melhor para o pior tipo temos: system, const, eq_ref, ref, range, index, all;
• possible_keys – indica quais os índices que o MySQL pode usar para encontrar resultados nesta tabela;
• key – índice usado na consulta, ou NULL caso não tenham sido usados índices;
• key_len – tamanho do índice usado, caso exista;
• ref – coluna(s) usada(s) com a key para devolver resultados;
• rows – número de registos que o MySQL tem que examinar para executar a consulta;
• extra – informação adicional acerca de como o MySQL vai executar a consulta. A evitar o aparecimento de “using filesort” e “using temporary“.

Sabendo isto, e analisando o resultado obtido, podemos dizer que a consulta efectuada está muito pouco optimizada. Não existem chaves possíveis (possible_keys) para usar, o que implica que o MySQL tenha de percorrer todos os registos da tabela (1000 no exemplo, mas imagine uma tabela com milhões de resultados).

De acordo com o manual do MySQL, as tabelas são listadas na mesma ordem que são lidas enquanto a consulta é processada. O MySQL resolve todos os joins através de um método multi-join que percorre todos os registos. Isto significa que o MySQL lê o primeiro registo da primeira tabela (tabA), de seguida procura um registo correspondente na segunda tabela (tabB), fazendo o mesmo para a terceira tabela (tabC) e assim sucessivamente. Após todas as tabelas serem processadas o MySQL devolve os registos encontrados e volta atrás. Não para a tabA, mas sim para a tabB para procurar se existem mais registos que façam match com a tabA. Caso existam, vai procurar novamente registos da tabC.

É importante reter que os valores da tabela precendente (como listado no resultado do EXPLAIN) são usados para pesquisa na tabela actual, ou seja, o MySQL usa os valores da tabA para encontrar registos na tabB e assim sucessivamente. Sabendo isto, onde adicionar índices para ajudar o MySQL nesta pesquisa? Para responder a esta pergunta é necessário saber o que é que o MySQL procura. O join da tabA com a tabB é feito pela condição “tabA.col1=tabB.col1″. O MySQL já possui o valor para o campo tabA.col1, logo precisa de saber o valor de tabB.col1 de modo a terminar a equação. Assim, um índice deverá ser criado neste campo. O resultado do EXPLAIN após a criação deste campo é o seguinte:

+-------+------+---------------+----------+---------+-----------+------+-------------+
| table | type | possible_keys | key      | key_len | ref       | rows | Extra       |
+-------+------+---------------+----------+---------+-----------+------+-------------+
| tabA  | ALL  | NULL          | NULL     |    NULL | NULL      | 1000 |             |
| tabB  | ref  | idx_col1      | idx_col1 |       5 | tabA.col1 |    1 | Using where |
| tabC  | ALL  | NULL          | NULL     |    NULL | NULL      | 1000 | Using where |
+-------+------+---------------+----------+---------+-----------+------+-------------+

Como é visível, o MySQL usa o idx_col1 para efectuar o join entre tabA e tabB. Agora, ao procurar ocorrências de tabA.col1 na tabB, em vez de percorrer todos os registos como anteriormente, usa o idx_col1 para apanhar apenas os valores pretendidos. Para a pesquisa na tabC continua a ser precisa a pesquisa em todos os registos, mas esta questão pode ser resolvida da mesma forma que a anterior. Neste caso a condição de join é “tabA.col2=tabC.col1″, o que faz com que o campo a ser indexado seja o tabC.col1, uma vez que o valor de tabA.col2 já é conhecido. Após a criação deste índice, o resultado do EXPLAIN é:

+-------+------+---------------+----------+---------+-----------+------+-------------+
| table | type | possible_keys | key      | key_len | ref       | rows | Extra       |
+-------+------+---------------+----------+---------+-----------+------+-------------+
| tabA  | ALL  | NULL          | NULL     |    NULL | NULL      | 1000 |             |
| tabB  | ref  | idx_col1      | idx_col1 |       5 | tabA.col1 |    1 | Using where |
| tabC  | ref  | idx_col1      | idx_col1 |       5 | tabA.col2 |    1 | Using where |
+-------+------+---------------+----------+---------+-----------+------+-------------+

Concluindo…

A tarefa de fazer com que os joins complexos passem a funcionar de uma forma simples, e com que a indexação seja feita nos campos correctos, torna-se relativamente fácil se forem tomados em conta os seguintes pontos:

• Independentemente da complexidade da consulta, apenas é necessário abordar o join analisando duas tabelas de cada vez, na ordem pela qual estas são listadas pelo EXPLAIN;
• Os valores das tabelas precedentes já são conhecidos; o esforço tem de ser concentrado em ajudar o MySQL a usar esses valores com índices das tabelas actuais, com vista a encontrar registos.

Para mais informações acerca do comando EXPLAIN, consultar o tópico respectivo do manual do MySQL.

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