Para otimizar o desempenho das queries em web apps, o MySQL Tuning pode ser uma ótima ajuda! Vê aqui algumas boas práticas para aplicares aos teus projetos.

Índice de conteúdos

1. Introdução
2. Estratégias
    2.1. Modelo de dados
    2.2. Usar o “EXPLAIN”
    2.3. Entender os resultados do “EXPLAIN”
    2.4. Lógica de negócio
    2.5 Alteração de configurações
3. Onde saber mais

1. Introdução

Durante o desenvolvimento, detalhes relacionados com o desempenho e escalabilidade geralmente são deixados para segundo plano, muitas vezes, devido aos prazos do projeto. Até mesmo programadores experientes podem descurar esta questão. Queries lentas são um problema comum encontrado em muitas aplicações web.

Provavelmente, existe um sistema de cache para reduzir o “workload” da base de dados, mas, mesmo com esse tipo de medida, isso pode não ser suficiente.

Para ajudar neste processo, abaixo estão algumas boas práticas que podem ser usadas para verificar se as queries estão otimizadas e o que pode ser feito para melhorar o desempenho dessas queries.

Este artigo foi escrito assumindo que é usada uma base de dados MySQL 5.7. No entanto, estas sugestões também são úteis para qualquer outro SGBD1.

2. Estratégias

2.1. Modelo de dados

Quando uma chave estrangeira é criada para uma determinada coluna, um índice é criado automaticamente. No entanto, para outros casos, deve ser considerada a criação de um índice para acelerar as queries. Isso não implica que devem ser criados índices para todas as colunas (os índices implicam consumo de memória e espaço em disco).

Os índices são atualizados após cada “insert”, “update” e “delete”, portanto, as operações de escrita são afetadas. Problemas relacionados com a indexação incorreta têm um alto custo após a aplicação escalar.

Para decidir se um índice é realmente necessário, deve ser executado o comando “EXPLAIN”.

2.2 Usar o “EXPLAIN”

EXPLAIN SELECT(…)

2.3. Entender os resultados do EXPLAIN

Coluna Significado
id O identificador do tipo SELECT
select_type O tipo de SELECT
table A tabela associada à linha do resutado
partitions Partições com correspondência
type Tipo de “join
possible_keys Índices possíveis de ser utilizados
key Índice escolhido
key_len O comprimento do índice escolhido
ref Colunas comparadas com o índice
rows Número estimado de linhas assinaladas
filtered Percentagem de linhas filtradas por condição da tabela
Extra Dados extra

Apenas serão detalhados os campos mais relevantes da tabela acima:

select_type:

O tipo de SELECT, que pode ser um dos apresentados na tabela a seguir (listados apenas os mais importantes):

select_type Significado
SIMPLE SELECT simples (quando não existe UNION ou subqueries
PRIMARY SELECT base que contém os SELECTS das subqueries
UNION Segundo ou seguintes expressões de SELECT numa query com uma cláusula UNION
DEPENDENT UNION Segundo ou seguintes expressões de SELECT numa query com uma cláusula UNION, query dependente da query “externa”
UNION RESULT Resultado de uma cláusula UNION
SUBQUERY Primeiro SELECT numa subquery
DEPENDENT SUBQUERY Primeiro SELECT numa subquery, dependente da query “externa”
DERIVED Tabela derivada

key:

A coluna “key” indica a chave (índice) que foi usada pelo MySQL. Se o MySQL decidir usar um dos índices que constam no campo “possible_keys” para procurar linhas, esse índice será listado como o valor da chave. Se a coluna “key” for “null”, deve ser criado um índice para uma ou mais colunas. Ver a explicação para o campo “rows” abaixo.

key_len:

A coluna “key_len” indica o comprimento da chave que o MySQL usou. O valor do campo “key_len” permite determinar quantas partes de uma chave composta o MySQL realmente usa.

rows:

A coluna “rows” indica o número de linhas que o MySQL que irá analisar para executar a query. Esse número deve ser muito menor que o total de resultados para evitar um “full table scan”.
Se o resultado da coluna “keys” for “null”, provavelmente esse resultado será igual ao total de resultados.

filtered:

A coluna “filtered” indica uma percentagem estimada de linhas que serão filtradas pela condição da tabela. O valor máximo é 100, o que significa que não ocorreu nenhuma filtragem de linhas.

extra:

Este valor indica como o MySQL processa a query. Ter em atenção as queries usando a cláusula “ORDER BY”. Por exemplo, se for obtido um resultado do tipo: “Using where; Using filesort”.
Segundo a documentação do MySQL:

Em alguns casos, o MySQL não consegue usar os índices para processar o ORDER BY, embora sejam usados índices para retornar as linhas que correspondem ao descrito na cláusula WHERE.
A chave usada para obter as linhas não é a mesma da que é usada para processar o ORDER BY.

Se for necessário ordenar resultados por várias colunas, verificar se está definido um índice usando essas colunas.

2.4. Lógica de negócio

Não usar operações de DML2 dentro de ciclos. Por exemplo, para inserir vários registos numa tabela, deve ser criado um único “insert” com os valores. Se se tratar de centenas ou milhares de registos, devem ser iniciadas várias transações para evitar erros como “Lock wait timeout exceeded; try restarting transaction“.

Deve ser evitada a geração de resultados usando várias queries simples. Para retornar os dados de uma só vez, poderá ser usada a cláusula UNION. Provavelmente, se estiver a ser usado um ORM3, não será possível usar UNION ou outras funcionalidades do SQL.

Nesses casos, é recomendado que seja usada uma “query nativa” (usando “prepared statements”!) em vez de usar o “query builder”.

2.5 Alteração de configurações

Antes de considerar esta abordagem, devem ser usadas as otimizações de queries sugeridas acima.
A alteração de configurações só deve ser usada se o descrito acima não for suficiente.

innodb_buffer_pool_size:

Aplicável às tabelas com engine “InnoDB” (este valor deverá ser 70% da RAM disponível).

max_allowed_packet:

Esta variável permite definir o tamanho máximo de um pacote para envio (sending packet). Um pacote é um único estado SQL, uma única linha a ser enviada para um cliente ou um log a ser enviado desde uma sincronização master/slave.

max_connections:

Esta variável define o valor limite de ligações concorrentes.

Onde saber mais

https://dev.mysql.com/doc/refman/5.7/en/optimization.html
https://www.percona.com/resources/technical-presentations/mysql-query-optimization-scale-17x
http://mysql.rjweb.org/doc.php/memory

SGDB1: Sistema Gestão de Base de Dados
DML2: Data manipulation language (INSERT, UPDATE, DELETE)
ORM3: Data-relational mapping