log - smart software development. great user experience

Melhorar o desempenho do MySQL sem aborrecer DBAs

No acto de desenhar e desenvolver sobre uma base de dados MySQL, é bem possível que surjam problemas de desempenho, principalmente quando é preciso tratar quantidades exorbitantes de dados. Alguns destes problemas podem ser colmatados com uma arquitectura eficiente, uma correcta definição dos índices das tabelas ou, em último caso, escalando o hardware.

No entanto, nem sempre é praticável investir num upgrade das máquinas, ou alterar o motor de armazenamento, a estrutura da base de dados ou sequer a política de indexação das tabelas. Seguem-se algumas técnicas de optimização de expressões SQL que programadores podem adoptar unilateralmente no sentido de melhorar o desempenho das suas consultas. Tudo sem ser preciso aborrecer os administradores da base de dados.

O MySQL explica

O primeiro passo de qualquer optimização de expressões SQL, uma vez determinada a sua necessidade, é compreender o funcionamento interno dos pedidos que fazemos à base de dados.

O statement EXPLAIN deve ser a arma de eleição para este combate. Até ao MySQL 5.6.3, servia “apenas” para avaliar expressões do tipo SELECT, mas nas versões mais recentes pode ser aplicado a todas as operações, sejam de leitura, inserção, actualização ou eliminação. Para fazer uso desta funcionalidade basta prefaciar qualquer instrução SQL com a palavra EXPLAIN.

A principal utilidade do EXPLAIN está em revelar o mecanismo de indexação usado para obter os resultados de um pedido. A indexação das colunas de uma tabela permite pesquisas mais rápidas, da mesma forma que o índice de capítulos de um livro ou a arrumação alfabética de uma lista telefónica ou enciclopédia nos facilita a consulta e leitura. A troco de algum espaço em disco e velocidade de escrita, o sistema gestor da base de dados que use índices pode encontrar o que procura directamente em vez de o fazer percorrendo as tabelas registo a registo.

Olhando para o resultado de uma chamada ao EXPLAIN, em especial para as colunas possible_keys (contendo os candidatos a índice a utilizar) e key (o índice realmente usado), obtemos informação crucial para identificar se uma tabela precisa que determinada coluna esteja indexada ou se, pelo contrário, a coluna tem índices que não se usam porque as expressões de consulta se encontram indevidamente construídas. Este artigo irá debruçar-se sobre algumas destas, e oferecer alternativas práticas.

Outras indicações do EXPLAIN a ter em conta são as colunas Extra (onde qualquer referência a índices é perfeita) e type (onde a palavra index significa full index scan, o que é péssimo para o desempenho).

Contra relógio

A função BENCHMARK(), que permite executar uma expressão SQL repetidas vezes e retornar o tempo de resposta, é outra ferramenta relativamente útil, principalmente quando é preciso testar expressões aritméticas ou com parâmetros constantes.

Embora possa ser usada para avaliar expressões do tipo SELECT, importa realçar que a execução de um pedido SELECT BENCHMARK(N, SELECT ...) irá resultar na reutilização de estruturas de memória e outros mecanismos de optimização automática do MySQL, pelo que os tempos reportados irão certamente parecer mais optimistas do que na realidade. A função BENCHMARK() também nos diz pouco sobre o tempo consumido pelo servidor, sendo por vezes necessário calcular a média de múltiplos benchmarks para nos aproximarmos de um valor real.

O que não vale a pena fazer

Felizmente, o optimizador interno do MySQL tem inteligência para melhorar, de forma transparente, determinadas expressões. De entre as melhorias automáticas que o MySQL faz contam-se:

  • Eliminação de parênteses desnecessários;
  • Substituição de variáveis por constantes que lhes estejam atribuídas;
  • Junção das condições em WHERE e HAVING quando não existem funções de agregação ou GROUP BY;
  • Ordenação ideal das tabelas num INNER JOIN.

Atacar estas situações no código pode melhorar a legibilidade do mesmo, mas não terá efeitos práticos sobre o desempenho das pesquisas.

A união faz a força

A junção de condições com o operador OR, no MySQL e noutros sistemas de bases de dados, é puro veneno.

Isto porque, quando confrontado com um OR, o sistema de base de dados ignora a indexação de muitas das colunas, uma vez que esta deixa de ser limitativa para os resultados a apresentar. Embora alguns sistemas consigam optimizar sequências de condições OR mais simples, é má ideia confiar numa optimização automática que não está garantida.

A boa notícia é que há uma solução para contornar este problema. A má notícia é que a solução é feia.

Adoptemos o seguinte exemplo, muito simples, que pesquisa a tabela de artigos de um blogue e retorna todos os registos cujo título comece por A ou tenha um artigo-pai cujo título comece por A:

SELECT p.ID
    FROM wp_posts p, wp_posts pai
    WHERE p.post_title LIKE 'A%'
        OR (p.post_parent = pai.ID AND pai.post_title LIKE 'A%')

Esta expressão pode ser reescrita à custa de múltiplos SELECTs cujos resultados são depois agregados usando a palavra-chave UNION (ou UNION ALL, que é ainda mais eficiente, mas só no caso de não ser preciso limpar registos duplicados).

SELECT p.ID
    FROM wp_posts p
    WHERE p.post_title LIKE 'A%'
UNION
SELECT p.ID
    FROM wp_posts p, wp_posts pai
    WHERE p.post_parent = pai.ID AND pai.post_title LIKE 'A%'

Embora a vantagem de reescrever a expressão desta forma não pareça nada intuitiva, o que acontece é que várias pesquisas feitas recorrendo a índices são consideravelmente mais rápidas do que uma única pesquisa feita sem essa ajuda. Esta diferença torna-se bastante perceptível com o aumento do tamanho da tabela a consultar, podendo a optimização traduzir-se numa redução do tempo de pesquisa de várias horas para poucos minutos.

Aplicando o EXPLAIN às duas expressões da consulta, torna-se evidente, olhando para o campo key, qual delas não está a usar os índices que tem à disposição.

> EXPLAIN SELECT p.ID, p.post_title FROM wp_posts p, wp_posts pai WHERE (p.post_parent = pai.ID AND pai.post_title LIKE 'A%') OR p.post_title LIKE 'A%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | p     | ALL  | post_parent   | NULL | NULL    | NULL | 2412 |             |
|  1 | SIMPLE      | pai   | ALL  | PRIMARY       | NULL | NULL    | NULL | 2412 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

Se, contudo, pedirmos ao MySQL para “explicar” a expressão com UNION, detectamos que a chave PRIMARY é usada para relacionar o pai de um artigo através do seu identificador, o que não acontecia antes, e por conseguinte reduzindo o número de linhas (em rows) que é preciso considerar.

> EXPLAIN SELECT p.ID, p.post_title FROM wp_posts p, wp_posts pai WHERE p.post_parent = pp.ID AND pai.post_title LIKE 'A%' UNION SELECT p.ID, p.post_title FROM wp_posts p WHERE p.post_title LIKE 'A%';
+----+--------------+------------+--------+---------------+---------+---------+--------------------+------+-------------+
| id | select_type  | table      | type   | possible_keys | key     | key_len | ref                | rows | Extra       |
+----+--------------+------------+--------+---------------+---------+---------+--------------------+------+-------------+
|  1 | PRIMARY      | p          | ALL    | post_parent   | NULL    | NULL    | NULL               | 2412 |             |
|  1 | PRIMARY      | pai        | eq_ref | PRIMARY       | PRIMARY | 8       | blog.p.post_parent |    1 | Using where |
|  2 | UNION        | p          | ALL    | NULL          | NULL    | NULL    | NULL               | 2412 | Using where |
|NULL| UNION RESULT | <union1,2> | ALL    | NULL          | NULL    | NULL    | NULL               | NULL |             |
+----+--------------+------------+--------+---------------+---------+---------+--------------------+------+-------------+
4 rows in set (0.00 sec)

Felizmente para quem tem de ler e entender o código, a utilização do UNION nem sempre é necessária. Quando basta escolher de entre um leque de valores constantes para uma coluna, como no exemplo abaixo, estes podem ser refeitos usando a comparação IN.

SELECT p.ID
FROM wp_posts p
WHERE p.ID = 1
    OR p.ID = 2
    OR p.ID = 3
    OR p.ID = 5
    OR p.ID = 8
    OR p.ID = 13

Isto pode ser transformado em:

SELECT p.ID
FROM wp_posts p
WHERE p.ID IN (1, 2, 3, 5, 8, 13)

A comparação IN verifica a presença de uma variável numa lista de valores e é extremamente eficiente quando a lista é pequena e composta apenas por constantes. (É importante reforçar esta condição porque muito embora IN (constante1, constante2, ...) seja eficiente, IN (SELECT ...) já não é.)

Encadeamento de consultas

Outro deslize comum em que a facilidade de escrita e leitura choca com a optimização das consultas é no caso dos SELECTs encadeados, que ocorrem quando o programador está a pensar em termos procedimentais e não nos dados e nas suas relações. Por exemplo:

SELECT
    c.id,
    c.nome,
    (SELECT SUM(horas)
        FROM relatorio
        WHERE colaborador_id = c.id) AS total_horas
FROM colaboradores c

Esta expressão não é a ideal porque o segundo SELECT (dentro do SELECT principal) obriga a um esforço suplementar do sistema da base de dados, que é pago em tempo de resposta e ocupação de memória. Embora haja situações em que o uso de pedidos encadeados é inevitável, neste caso a expressão pode ser facilmente refeita através de um INNER JOIN muito mais eficiente.

SELECT
    c.id,
    c.nome,
    SUM(h.horas) AS total_horas
FROM colaboradores c
INNER JOIN horas h
    ON h.colaborador_id = c.id

Funções e os pedidos que funcionam mal

Outra atenção a tomar reside na aplicação de funções a comparações com campos indexados. Estas situações atrapalham a cache de pedidos e reduzem (tal como acima) o desempenho da base de dados. Deve-se pesar a necessidade de ter estas funções na consulta versus a possibilidade de passar parâmetros condicionais pré-processados ou de obter um conjunto de dados maior e aplicar posteriormente uma rotina de filtragem.

Dois em um

Programadores que desconhecem as palavras mágicas ON DUPLICATE KEY UPDATE tendem a incorrer noutro erro comum: o de fazer um SELECT para verificar a existência de determinado registo, seguido por uma instrução de INSERT para o criar caso não exista ou então UPDATE caso esteja presente.

Não só a estrutura de decisão envolvida neste processo complica desnecessariamente o código, como obriga a duas ligações à base de dados: uma para o SELECT inicial, e outra para o pedido de inserção ou actualização. A solução mais prática é fazer um único INSERT ... ON DUPLICATE KEY UPDATE, matando estes dois coelhos com uma cajadada só.

Mas há mais!

Embora este artigo esteja centrado no desenvolvimento sobre MySQL, os cuidados e recomendações são aplicáveis a muitos outros sistemas de gestão de bases de dados, como Oracle ou Microsoft SQL Server, bastando adaptar a sintaxe SQL.

As possíveis optimizações ao SQL das consultas também não se esgotam aqui, pelo que não termino o artigo sem recomendar um conjunto de recursos online úteis para quem pretenda alargar os seus horizontes neste capítulo do desenvolvimento sobre bases de dados.