MySQL: Otimização de desempenho do MySQL

Última atualização: 2026-08-26

Durante a promoção de fim de ano, a plataforma de comércio eletrônico da Alice viu o tempo de resposta do banco de dados disparar de 50 ms para 5 segundos, com os usuários reclamando que as páginas não carregavam e que os envios de pedidos expiravam. A equipe de operações conduziu uma investigação de emergência e descobriu que várias consultas lentas e não otimizadas estavam deixando todo o sistema extremamente lento. Ao ativar o registro de consultas lentas para identificar as instruções SQL problemáticas, analisar os planos de execução com EXPLAIN, adicionar índices ausentes, reescrever consultas ineficientes e ajustar os parâmetros de configuração do InnoDB, eles conseguiram, por fim, reduzir o tempo de resposta para menos de 100 ms.

1. O que você vai aprender

• Métodos para configurar, coletar e analisar registros de consultas lentas
• Uma análise detalhada dos campos no plano de execução EXPLAIN (type/key/rows/Extra)
• Estratégias fundamentais para a otimização de índices: evitar índices inválidos, índices de cobertura e o prefixo mais à esquerda dos índices compostos
• Técnicas de reescrita de consultas: evite SELECT *, substitua subconsultas por JOIN, otimize a paginação profunda
• Ajuste dos principais parâmetros de configuração: innodb_buffer_pool_size, max_connections, etc.

2. O processo de otimização de desempenho de ponta a ponta

A otimização de desempenho não é uma tarefa pontual, mas sim um processo cíclico de “descoberta → análise → otimização → validação”.

flowchart TD
    A[Identifying Slow Queries] --> B[EXPLAIN Analyze the Execution Plan]
    B --> C{Issue Type?}
    C -->|Missing Index| D[Index Optimization]
    C -->|Inefficient query syntax| E[Query Rewrite]
    C -->|Unreasonable configuration| F[Configuration Tuning]
    D --> G[Verifying Performance Improvements]
    E --> G
    F --> G
    G -->|Still does not meet the standards| A
    G -->|Meet the requirements| H[Deployment Monitoring]

flowchart TD
    A[Identifying Slow Queries] --> B[EXPLAIN Analyze the Execution Plan]
    B --> C{Issue Type?}
    C -->|Missing Index| D[Index Optimization]
    C -->|Inefficient query syntax| E[Query Rewrite]
    C -->|Unreasonable configuration| F[Configuration Tuning]
    D --> G[Verifying Performance Improvements]
    E --> G
    F --> G
    G -->|Still does not meet the standards| A
    G -->|Meet the requirements| H[Deployment Monitoring]

3. Registro de consultas lentas

O log de consultas lentas é a primeira linha de defesa para identificar problemas de desempenho; ele registra automaticamente as instruções SQL cujo tempo de execução ultrapassa um determinado limite.

(1) Introdução e configuração

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

(2) Ferramentas de análise de logs

mysqldumpslow é uma ferramenta de agregação de registros de consultas lentas integrada ao MySQL que classifica as consultas por tempo de execução ou frequência.

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

(3) Alternativas ao Performance Schema

O MySQL 5.6 e versões posteriores oferecem suporte ao uso do Performance Schema para coletar consultas lentas, recurso que pode ser ativado dinamicamente sem a necessidade de modificar o arquivo de configuração.

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

▶ Exemplo: Ativando o log de consultas lentas e identificando as 5 consultas mais lentas

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL min_examined_row_limit = 100;
SHOW VARIABLES LIKE 'slow_query_log_file';

mysqldumpslow -s t -t 5 /var/lib/mysql/slow.log

▶ Exemplo: Visualizar rapidamente consultas lentas usando a visão sys

SELECT query_id, LEFT(query, 80) AS query_text,
       exec_count, avg_timer_ms, rows_examined
FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY avg_timer_ms DESC LIMIT 10;

4. Uma análise aprofundada do plano de execução do EXPLAIN

O EXPLAIN é a ferramenta de diagnóstico mais essencial para o ajuste de SQL; ele mostra como o MySQL executa as consultas.

(1) Visão geral dos campos de saída do EXPLAIN

(2) Explicação detalhada do campo “type”

O campo type é o campo mais importante em EXPLAIN; ele reflete diretamente a eficiência da consulta.

(3) Valores-chave de campos adicionais

• Using index: Índice de cobertura; não é preciso consultar a tabela; melhor desempenho
• Using where: Depois que o mecanismo de armazenamento retorna os dados, eles são filtrados pela camada do servidor
• Using filesort: Não é possível classificar usando o índice; é necessária uma operação adicional de classificação.
• Using temporary: Foi utilizada uma tabela temporária; isso é comum quando uma cláusula GROUP BY não possui índice.
• Using index condition: Index Pushdown (ICP), reduzindo o número de consultas à tabela

▶ Exemplo: Analisando uma consulta com uma única tabela usando o EXPLAIN

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE user_id = 42 AND status = 'PAID';

+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | rows | Extra                     |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | orders | ref  | idx_user      | idx_user | 4    |  120 | Using where               |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+

▶ Exemplo: Analisando uma consulta com junção usando o EXPLAIN

EXPLAIN SELECT o.order_id, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.create_time > '2025-01-01';

5. Estratégias de otimização de índices

Os índices são ferramentas poderosas para acelerar consultas, mas usar o índice errado é mais perigoso do que não ter nenhum índice.

(1) Índice de cobertura

Um índice abrangente é aquele em que todos os campos utilizados em uma consulta estão incluídos no índice, eliminando a necessidade de acessar a tabela para ler os dados das linhas.

ALTER TABLE orders ADD INDEX idx_cover (user_id, status, total_amount);

Uma consulta por SELECT user_id, status, total_amount FROM orders WHERE user_id = 42 recupera os dados inteiramente do índice.

(2) Índices compostos e o prefixo mais à esquerda

Os índices compostos seguem o princípio do prefixo mais à esquerda: as condições da consulta devem começar a corresponder a partir da coluna mais à esquerda do índice.

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

(3) Referência rápida para cenários de falha de índice

▶ Exemplo: Validação do prefixo mais à esquerda de um índice composto

ALTER TABLE products ADD INDEX idx_cat_brand_price (category_id, brand_id, price);

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND brand_id = 10;
EXPLAIN SELECT * FROM products WHERE brand_id = 10;

A segunda consulta tem um type de ALL porque ignora a coluna mais à esquerda, category_id.

▶ Exemplo: Como usar um índice de cobertura para eliminar consultas à tabela

ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, total_amount);

EXPLAIN SELECT user_id, status, total_amount
FROM orders WHERE user_id = 42;

Se Using index aparecer na coluna “Extra”, isso significa que não é necessária nenhuma consulta à tabela.

6. Técnicas de otimização de consultas

Mesmo com um índice, consultas mal elaboradas ainda não conseguem tirar proveito dele.

(1) Evite usar SELECT *

A instrução SELECT * lê todas as colunas, aumenta a carga de E/S e pode fazer com que o índice de cobertura se torne ineficaz.

SELECT id, username, email FROM users WHERE id = 100;

(2) Reescrever subconsultas como JOINs

A subconsulta relacionada é executada uma vez para cada linha; reescrevê-la como um JOIN pode reduzir significativamente o número de varreduras.

SELECT o.order_id, o.total_amount
FROM orders o
WHERE o.user_id IN (SELECT id FROM users WHERE vip_level >= 3);

Otimizado para:

SELECT o.order_id, o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id AND u.vip_level >= 3;

(3) Otimização aprofundada da paginação

O método tradicional LIMIT offset, n apresenta um desempenho extremamente ruim quando o deslocamento é grande.

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

Plano de otimização — Paginação por cursor:

SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

(4) Referência rápida sobre técnicas de otimização de consultas

▶ Exemplo: Comparação da otimização de paginação profunda

SELECT * FROM orders ORDER BY create_time LIMIT 500000, 20;

Otimizado para associação diferida:

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY create_time LIMIT 500000, 20) t
ON o.id = t.id;

A subconsulta recupera apenas a chave primária, utiliza um índice de cobertura para localizar rapidamente o ID e, em seguida, recupera todos os dados da tabela.

▶ Exemplo: Otimização de inserções em massa

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 201, 2), (1001, 202, 1), (1001, 203, 5);

Em comparação com a execução de uma única instrução INSERT três vezes em um loop, isso reduz pela metade o número de idas e voltas na rede e a sobrecarga do commit da transação.

7. Otimização da estrutura das tabelas

Uma estrutura de tabela bem projetada é a base do desempenho; reescrever o código SQL serve apenas para otimizar a estrutura existente.

(1) Seleção do tipo de campo

(2) Design antiparadigma

Em cenários com alta carga de consultas, uma redundância moderada pode reduzir o número de operações JOIN.

CREATE TABLE order_summary (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(64),
    total_amount DECIMAL(10,2),
    INDEX idx_user (user_id)
);

Replique a coluna username na tabela orders para evitar ter que fazer um JOIN com a tabela users em cada consulta.

▶ Exemplo: Comparação da otimização do tipo de campo

ALTER TABLE products MODIFY COLUMN weight DECIMAL(8,2);
ALTER TABLE products MODIFY COLUMN description TEXT;

Altere a coluna weight de VARCHAR para DECIMAL e altere a coluna description de VARCHAR(5000) para TEXT para reduzir o comprimento do registro primário.

8. Ajuste dos parâmetros de configuração

Os parâmetros de configuração do MySQL afetam diretamente o comportamento dos mecanismos de armazenamento e a alocação de recursos.

(1) Principais parâmetros de configuração e valores recomendados

(2) Ajuste dinâmico de parâmetros

Alguns parâmetros podem ser alterados online, sem a necessidade de reiniciar a instância.

SET GLOBAL innodb_buffer_pool_size = 8589934592;
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

▶ Exemplo: Verificando a taxa de acertos atual do buffer pool do InnoDB

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests      | 1024000   |
| Innodb_buffer_pool_reads              | 512       |
+---------------------------------------+-----------+

Taxa de acertos = 1 - (512 / 1024000) ≈ 99,95%; o tamanho do buffer pool está adequado.

9. Introdução ao Performance Schema

O Performance Schema é o mecanismo integrado de monitoramento de desempenho do MySQL, que fornece dados de diagnóstico mais detalhados do que o log de consultas lentas.

(1) Ativar o Performance Schema

SHOW VARIABLES LIKE 'performance_schema';
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

(2) Visões comuns de monitoramento

▶ Exemplo: Visualização de índices não utilizados

SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'ecommerce'
ORDER BY object_name;

Índices não utilizados desperdiçam espaço de armazenamento e exigem manutenção adicional durante as operações INSERT e UPDATE; eles devem ser removidos imediatamente.

10. Exercício prático abrangente: o processo completo de diagnóstico de desempenho

Usando a plataforma de comércio eletrônico da Alice como exemplo, vamos demonstrar todo o processo, desde a identificação de consultas lentas até a verificação das melhorias de desempenho.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

mysqldumpslow -s t -t 5 /var/lib/mysql/slow.log

Count: 328  Time=4.52s  Rows=1.0  Rows_examined=890000
SELECT * FROM orders WHERE YEAR(create_time)=2025 AND status='PAID';

EXPLAIN SELECT * FROM orders
WHERE YEAR(create_time) = 2025 AND status = 'PAID';

type: ALL | key: NULL | rows: 890000 | Extra: Using where

Motivo da falha no índice: A função YEAR() foi utilizada em create_time. Solução:

ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);

SELECT order_id, user_id, total_amount
FROM orders
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
  AND status = 'PAID';

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
  AND status = 'PAID';

type: range | key: idx_status_time | rows: 3200 | Extra: Using index condition

O número de linhas analisadas caiu de 890.000 para 3.200, e o tempo de consulta diminuiu de 4,5 segundos para 0,08 segundos. Por fim, ajuste o buffer pool:

SET GLOBAL innodb_buffer_pool_size = 8589934592;

❓ Perguntas Frequentes

P: O log de consultas lentas afeta o desempenho em produção? R: O impacto é mínimo. Quando long_query_time é definido como 1 segundo, apenas as consultas que atingem o tempo limite são registradas, e a sobrecarga da gravação no log é insignificante. Se você estiver preocupado com a pressão de E/S, pode redirecionar o log para um arquivo em vez de uma tabela ou usar o Performance Schema como alternativa.

P: O campo rows em EXPLAIN é preciso? R: rows é uma estimativa baseada em estatísticas, não um valor exato. Para colunas com distribuição de dados assimétrica, a estimativa pode apresentar um desvio significativo. Você pode usar ANALYZE TABLE para atualizar as estatísticas e melhorar a precisão.

P: Ter mais índices torna as consultas mais rápidas? R: Não. Os índices ocupam espaço em disco, e toda operação INSERT, UPDATE ou DELETE exige a atualização de todos os índices. Recomenda-se que uma única tabela não tenha mais do que 5 a 6 índices; priorize o uso de índices compostos para reduzir o número total de índices.

P: Quando se deve considerar o particionamento de bancos de dados e tabelas? R: Considere essa opção somente quando uma única tabela contiver mais de 50 milhões de linhas e a otimização de SQL e de índices tiver atingido seus limites. O particionamento de bancos de dados e tabelas aumenta a complexidade do sistema (JOINs entre bancos de dados, transações distribuídas) e não deve ser a primeira opção.

P: Qual é o valor adequado para innodb_buffer_pool_size? R: Para servidores de banco de dados dedicados, recomenda-se definir esse valor entre 60% e 80% da memória física. Para servidores compartilhados, certifique-se de reservar memória suficiente para o sistema operacional e outros processos. Você pode avaliar isso verificando a taxa de acertos de innodb_buffer_pool_reads: uma taxa inferior a 99% indica que o buffer pool é muito pequeno.

P: A mensagem “Using filesort” em uma consulta sempre requer otimização? R: Não necessariamente. Se o conjunto de resultados for pequeno (algumas dezenas de linhas), a classificação por arquivo (filesort) é realizada na memória, e a sobrecarga é insignificante. A otimização só é necessária quando o número de linhas a serem classificadas é muito grande e faz com que arquivos temporários sejam gravados no disco; isso pode ser mitigado aumentando o sort_buffer_size.

P: Como posso determinar se um índice está sendo usado? R: Use a visualização sys.schema_unused_indexes para consultar os índices que nunca foram usados ou verifique a visualização Performance Schema em busca de registros nos quais a coluna count_star na visualização table_io_waits_summary_by_index_usage seja 0.

📖 Resumo

• O registro de consultas lentas é o ponto de partida para a otimização do desempenho; use mysqldumpslow ou as visualizações do sistema para identificar rapidamente os gargalos.
• O campo “type” do comando EXPLAIN varia de “system” a “ALL” em ordem decrescente de qualidade; o objetivo é atingir, no mínimo, o nível “ref”.
• Otimização de índices Pontos-chave: Evite incompatibilidades de índices causadas por funções ou conversões implícitas; faça bom uso de índices abrangentes e do prefixo mais à esquerda dos índices compostos.
• Reescrita de consultas: Substitua SELECT * por colunas específicas, substitua subconsultas por JOINs e utilize cursores ou junções preguiçosas para paginação profunda
• Ajuste de configuração: innodb_buffer_pool_size é o parâmetro mais crítico; a taxa de acertos do buffer pool deve ser mantida em 99% ou mais.
• O Performance Schema oferece um monitoramento detalhado capaz de identificar consultas curtas e de alta frequência, bem como índices não utilizados que não são registrados no log de lentidão

📝 Exercícios

1. Questão básica (Dificuldade: ⭐): Ative o log de consultas lentas, defina o limite para 0,5 segundos e use mysqldumpslow para identificar as 5 consultas lentas com o maior número de execuções.

2. Exercício avançado (Dificuldade ⭐⭐): Para uma consulta com um type de ALL, use EXPLAIN para analisá-la e, em seguida, adicione os índices apropriados para verificar se o type foi atualizado para o nível ref ou range.

3. Questão de desafio (Dificuldade: ⭐⭐⭐): Projete um esquema de indexação para uma tabela de pedidos com 1 milhão de linhas, utilizando um índice de cobertura para otimizar a consulta SELECT user_id, status, total_amount FROM orders WHERE user_id = ? AND status = ?, e compare as diferenças nos campos rows e Extra do resultado EXPLAIN antes e depois da otimização.

4. Exercício prático (Dificuldade: ⭐⭐⭐): Reescreva uma consulta SQL lenta que contenha uma subconsulta usando um JOIN e, em seguida, otimize-a com índices para reduzir o tempo de execução da consulta de segundos para menos de 100 milissegundos. Documente todo o processo de otimização.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós