Aula 22: Performance tuning — EXPLAIN, slow query log e otimização

A performance tuning em MySQL é, sem exagero, a competência que separa profissionais que apenas operam bancos de dados daqueles que conseguem sustentar aplicações críticas de alta concorrência. Nesta aula, você vai mergulhar fundo nas ferramentas nativas do MySQL para diagnosticar e corrigir gargalos: o comando EXPLAIN, o slow query log e técnicas práticas de otimização de consultas e estrutura. Esta é a Aula 22 do curso MySQL — Do Zero ao Avançado, um conteúdo pensado para profissionais de TI, analistas de infraestrutura, desenvolvedores backend, administradores de sistemas e entusiastas de segurança da informação que precisam extrair o máximo do MySQL em produção. Em nossos projetos na JRT Technology Solutions, lidamos diariamente com bases de dados que precisam responder em milissegundos para milhares de usuários simultâneos. O que você aprenderá aqui é exatamente o tipo de conhecimento que aplicamos para reduzir o tempo de resposta de consultas, diminuir o consumo de CPU e I/O e evitar degradação em momentos de pico.

Muitas vezes, a causa de uma aplicação lenta não está no código da aplicação, mas na forma como o MySQL executa as instruções SQL. Uma consulta mal planejada pode varrer milhões de linhas desnecessariamente, gerar ordenações caras em disco e consumir toda a memória disponível para buffers. O performance tuning ataca exatamente isso: por meio da análise do plano de execução, da captura de consultas lentas e da aplicação de índices, reescrita de SQL e ajuste de parâmetros, você consegue transformar uma consulta que levava vários segundos em uma operação que retorna em poucos milissegundos. Ao final desta aula, você terá autonomia para auditar uma instância MySQL, identificar as consultas problemáticas, entender o motivo da lentidão e aplicar correções comprovadas, sem depender de achismos.

O foco desta aula é 100% prático. Você vai criar uma base de dados de exemplo com dezenas de milhares de registros, executar consultas que propositalmente sofrem com falta de índices, interpretar o EXPLAIN tradicional e o EXPLAIN ANALYZE, ativar o slow query log em servidores Ubuntu/Debian e CentOS/RHEL/Rocky Linux, utilizar ferramentas como mysqldumpslow e pt-query-digest e aplicar otimizações reais com índices compostos e reescrita de SQL. Cada comando será mostrado de forma completa, com sua saída esperada e explicação linha a linha. Não haverá atalhos ou resumos que deixem lacunas: se você executar os passos na ordem apresentada, os resultados serão reproduzíveis.

Para aproveitar 100% desta aula, você já deve ter concluído as aulas anteriores do curso, especialmente aquelas sobre modelagem de dados, índices básicos, stored procedures e administração de instâncias. Ter um ambiente MySQL 8.0 local ou em máquina virtual com acesso root ou sudo é essencial, pois realizaremos alterações em arquivos de configuração e reiniciaremos o serviço do banco. Recomendamos também ter pelo menos 2 GB de RAM livres na máquina, pois os testes de performance tuning envolvem a criação de tabelas com um volume considerável de dados. Se você está no Windows, pode usar uma máquina virtual com Ubuntu Server 22.04 ou 24.04; os comandos para CentOS/Rocky Linux também serão apresentados, pois são ambientes comuns em servidores corporativos.

O que você vai aprender nesta aula

• Compreender os fundamentos do performance tuning no MySQL e o papel do otimizador de consultas, das estatísticas e do storage engine InnoDB.
• Criar bases de dados de teste com milhares ou centenas de milhares de registros utilizando Common Table Expressions recursivas no MySQL 8.0.
• Utilizar o comando EXPLAIN nos formatos TRADITIONAL, JSON e TREE para inspecionar planos de execução e detectar full table scans, filesorts e seleções ineficientes.
• Utilizar o EXPLAIN ANALYZE para obter métricas reais de execução, como tempo, número de linhas retornadas e número de loops.
• Ativar e configurar o slow query log de forma completa, incluindo parâmetros como long_query_time, min_examined_row_limit e log_queries_not_using_indexes.
• Instalar e usar ferramentas de análise de logs lentos, como mysqldumpslow e pt-query-digest, em distribuições Linux baseadas em Debian e Red Hat.
• Criar índices simples e compostos com base nos sinais fornecidos pelo EXPLAIN e pelo slow query log, reduzindo drasticamente o número de linhas examinadas.
• Reescrever consultas SQL problemáticas, aplicar boas práticas de filtragem e ordenação e monitorar os resultados de forma objetiva.

Pré-requisitos e Ambiente

Antes de começar, verifique se o seu ambiente atende aos seguintes requisitos. A aula foi testada em MySQL 8.0.36 no Ubuntu Server 24.04, em MySQL 8.0.34 no CentOS Stream 9 e em MySQL 8.0.32 no Rocky Linux 9.3. Se você estiver usando uma versão 5.7, os conceitos continuam válidos, mas alguns parâmetros, como log_slow_extra, não existem nessa versão; adapte os trechos de configuração conforme indicado.

• MySQL Server 8.0 instalado e em execução. Caso ainda não tenha instalado, revise a aula de instalação do curso.
• Acesso ao terminal do servidor com privilégios de sudo ou root, pois vamos editar arquivos em /etc e reiniciar o serviço mysql ou mysqld.
• Cliente de linha de comando mysql instalado, normalmente já incluso no servidor.
• Um usuário no MySQL com privilégios para criar bancos de dados, tabelas e índices, como o root ou um usuário administrativo.
• Pelo menos 2 GB de RAM livres para os testes com InnoDB e innodb_buffer_pool_size. Se sua máquina tiver pouca memória, ajuste os valores indicados para não comprometer o sistema.
• Editor de texto de sua preferência: nano, vim ou vi.

O procedimento de configuração será mostrado para duas famílias de sistemas operacionais. Em distribuições Ubuntu/Debian, o arquivo principal de configuração do MySQL é /etc/mysql/mysql.conf.d/mysqld.cnf, e o serviço é gerenciado pelo systemctl com o nome mysql. Em distribuições CentOS/RHEL/Rocky Linux, o arquivo principal é /etc/my.cnf, e o serviço é gerenciado pelo systemctl com o nome mysqld. Essa diferença de nomes é uma fonte comum de erros quando se administra ambientes heterogêneos, portanto preste atenção em cada bloco de comandos.

Fundamentos do Performance Tuning no MySQL

Antes de partir para a execução de comandos, é necessário compreender como o MySQL decide o caminho de acesso aos dados. Quando você envia uma instrução SELECT, o servidor a analisa sintaticamente, valida as referências a tabelas e colunas, aplica regras de escopo e, em seguida, entrega a consulta ao otimizador. O otimizador gera vários planos de execução possíveis, estima o custo de cada um com base em estatísticas armazenadas no dicionário de dados e escolhe o plano de menor custo estimado. Esse custo é calculado principalmente a partir do número de linhas que precisam ser examinadas, do uso de índices, do custo de CPU, de memória temporária e de I/O. Se as estatísticas estiverem desatualizadas ou se os índices forem inadequados, o otimizador pode tomar decisões ruins, e a consulta passará a varrer tabelas inteiras quando poderia acessar poucas linhas por um índice.

O performance tuning no MySQL começa justamente pela análise do plano de execução escolhido pelo otimizador. O comando EXPLAIN permite visualizar esse plano sem executar a consulta, mostrando em quais tabelas o otimizador pretende acessar, qual índice será usado, quantas linhas ele estima examinar e quais operações extras, como ordenação temporária, agrupamentos e junções, serão necessárias. Com essa informação, você pode decidir se a consulta está boa ou se precisa de um novo índice, de reescrita ou de ajuste no banco. O complemento natural é o slow query log, que registra as consultas que realmente estão consumindo tempo e recursos em produção. Ele fornece o ponto de partida real: em vez de adivinhar qual consulta está lenta, você captura provas objetivas. Em nossos projetos na JRT Technology Solutions, utilizamos sempre a combinação slow query log + EXPLAIN para priorizar esforços de otimização, pois conserta-se primeiro aquilo que tem maior impacto mensurável.

Outro conceito fundamental é o papel do InnoDB, o storage engine padrão do MySQL. O InnoDB organiza os dados em páginas de 16 KB, usa B-trees para índices primários e secundários, e mantém em memória um buffer pool que armazena páginas de dados e índices. Quando uma consulta precisa acessar linhas que já estão nesse cache, o custo de leitura é muito menor. O tamanho do innodb_buffer_pool_size deve ser, como regra geral, cerca de 50% a 70% da memória RAM disponível em servidores dedicados. Se esse buffer for pequeno, o MySQL será obrigado a fazer mais leituras em disco, e qualquer consulta ficará mais lenta. Além disso, parâmetros como innodb_flush_log_at_trx_commit e innodb_log_file_size influenciam a durabilidade e a velocidade de gravação de logs. Ajustar esses valores sem entender o impacto é perigoso, mas nesta aula mostraremos uma configuração segura e revisada para um servidor de aplicação típico.

O performance tuning também envolve monitoramento contínuo. Não basta aplicar uma otimização pontual e abandonar o banco. O MySQL oferece o Performance Schema, uma série de tabelas que registram métricas detalhadas de execução, espera por locks, uso de memória, estágios de execução e muito mais. No contexto desta aula, o slow query log e o EXPLAIN serão os protagonistas, mas o Performance Schema aparece como suporte para confirmação e análise mais profunda. Ao final, você entenderá que performance tuning é um ciclo: medir, diagnosticar, alterar, medir novamente e documentar os ganhos.

Passo a Passo: Criando a Base de Dados para Testes de Performance Tuning

Para demonstrar as técnicas de performance tuning de forma didática e realista, vamos criar uma base de dados chamada tuning_demo, com duas tabelas: clientes e pedidos. A tabela pedidos terá um volume considerável de dados — 100.000 linhas — para que a diferença entre uma consulta sem índice e uma consulta otimizada seja perceptível não apenas no plano de execução, mas também no tempo real de resposta. A criação de dados em massa no MySQL 8.0 pode ser feita de diversas formas; utilizaremos Common Table Expressions recursivas, que geram uma sequência numérica de 1 a 100.000 sem depender de tabelas externas.

1. Conecte-se ao MySQL como usuário administrativo. Se estiver em um servidor local, execute sudo mysql -u root -p e informe a senha de root. Em seguida, crie o banco de dados com o conjunto de caracteres utf8mb4 e a collation utf8mb4_unicode_ci, recomendada para aplicações modernas com suporte a caracteres internacionais.
2. Selecione o banco de dados tuning_demo para que todos os comandos subsequentes sejam executados nele.
3. Crie a tabela clientes com uma chave primária id, além de colunas para nome, e-mail, cidade, unidade federativa e data de criação. A chave primária será um índice clustered no InnoDB.
4. Crie a tabela pedidos com a chave primária id, uma coluna cliente_id que referencia logicamente a tabela clientes, colunas produto_id, data_pedido, valor e status. Inclua um índice simples em cliente_id e outro em data_pedido para simular um cenário inicial parcialmente otimizado, mas ainda longe do ideal para consultas analíticas.
5. Ajuste a variável de sessão cte_max_recursion_depth para 100000, pois o valor padrão em MySQL 8.0 é 1000 e impede que a CTE recursiva gere 100.000 linhas.
6. Insira 1.000 clientes na tabela clientes usando uma CTE recursiva que gera números de 1 a 1000. Para cada número, geramos um nome, um e-mail, uma cidade e uma UF com a função ELT, que retorna um elemento de uma lista com base no índice. Isso cria dados variados sem depender de scripts externos.
7. Insira 100.000 pedidos na tabela pedidos usando uma CTE recursiva de 1 a 100000. Os valores de cliente_id, produto_id, data_pedido, valor e status são gerados aleatoriamente com RAND(), FLOOR() e DATE_SUB, cobrindo um período de 365 dias e diversos status.

# Conexão ao MySQL

sudo mysql -u root -p

# Dentro do prompt do MySQL, execute os comandos:

CREATE DATABASE tuning_demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE tuning_demo;
CREATE TABLE clientes (

  id INT PRIMARY KEY AUTO_INCREMENT,

  nome VARCHAR(100),

  email VARCHAR(100),

  cidade VARCHAR(80),

  uf CHAR(2),

  criado_em DATETIME DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB;
CREATE TABLE pedidos (

  id INT PRIMARY KEY AUTO_INCREMENT,

  cliente_id INT,

  produto_id INT,

  data_pedido DATE,

  valor DECIMAL(10,2),

  status VARCHAR(20),

  INDEX idx_cliente (cliente_id),

  INDEX idx_data (data_pedido)

) ENGINE=InnoDB;
# Necessário para gerar 100.000 linhas com CTE recursiva

SET SESSION cte_max_recursion_depth = 100000;
# Inserção de 1.000 clientes

INSERT INTO clientes (nome, email, cidade, uf)

WITH RECURSIVE seq AS (

  SELECT 1 AS n

  UNION ALL

  SELECT n + 1 FROM seq WHERE n < 1000

)

SELECT

  CONCAT('Cliente ',

Quer aprender na prática com especialistas?
A JRT Technology Solutions oferece treinamentos e implementação de MySQL para equipes corporativas.
  

    

    Falar no WhatsApp