Otimizar tabelas MySQL para melhorar performance do zero em 15 min

17 min de leitura  ·  Guia técnico

Otimizar tabelas MySQL é o processo de reorganizar o armazenamento físico dos dados, recuperar espaço fragmentado e atualizar estatísticas de índices para que o banco de dados execute consultas com mais eficiência. Para realizar essa otimização do zero em cerca de 15 minutos, siga estes passos:

1. Conecte ao MySQL via terminal e identifique o banco de dados alvo.
2. Consulte a tabela information_schema.TABLES para localizar tabelas fragmentadas.
3. Execute ANALYZE TABLE para atualizar estatísticas do otimizador de consultas.
4. Execute OPTIMIZE TABLE nas tabelas com maior fragmentação (DATA_FREE elevado).
5. Verifique o resultado com SHOW TABLE STATUS e confirme que Data_free zerou.
6. Agende a rotina com cron para manutenção periódica automática.

Pré-requisitos para otimizar tabelas MySQL

• Acesso SSH ao servidor com usuário root ou com permissão sudo.
• MySQL 5.7, MySQL 8.0 ou MariaDB 10.6+ instalado e em execução.
• Credenciais de um usuário MySQL com privilégio SELECT, INSERT e ALTER no banco alvo.
• Espaço em disco livre equivalente ao dobro do tamanho da maior tabela a ser otimizada (o MySQL cria uma cópia temporária durante o processo).
• Janela de manutenção disponível ou tráfego reduzido no banco de dados.
• Backup recente do banco de dados — consulte o artigo Dicas de Otimização de Servidores Linux para boas práticas gerais antes de qualquer intervenção.

Como identificar tabelas MySQL fragmentadas antes de otimizar

O primeiro passo para otimizar tabelas MySQL com eficiência é identificar quais tabelas realmente precisam de atenção. Executar OPTIMIZE TABLE em todas as tabelas indiscriminadamente desperdiça tempo e pode causar bloqueios desnecessários. A coluna DATA_FREE da tabela de sistema information_schema.TABLES revela quantos bytes de espaço fragmentado cada tabela possui.

Conecte ao MySQL com o comando abaixo:

mysql -u root -p

Após autenticar, execute a query de diagnóstico substituindo seu_banco pelo nome real do seu banco de dados:

SELECT
  TABLE_NAME,
  ENGINE,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS dados_MB,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS indices_MB,
  ROUND(DATA_FREE / 1024 / 1024, 2) AS fragmentado_MB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'seu_banco'
  AND DATA_FREE > 0
ORDER BY DATA_FREE DESC;

+------------------+--------+----------+------------+----------------+
| TABLE_NAME       | ENGINE | dados_MB | indices_MB | fragmentado_MB |
+------------------+--------+----------+------------+----------------+
| pedidos          | InnoDB |   245.30 |      88.10 |          52.00 |
| sessoes          | InnoDB |    18.70 |       4.20 |          14.30 |
| logs_acesso      | MyISAM |    92.40 |      11.50 |           8.60 |
+------------------+--------+----------+------------+----------------+

Tabelas com fragmentado_MB acima de alguns megabytes são candidatas prioritárias. No exemplo acima, a tabela pedidos tem 52 MB de espaço fragmentado — resultado típico de muitas operações de DELETE e UPDATE ao longo do tempo.

Para verificar o status detalhado de uma tabela específica, use:

SHOW TABLE STATUS FROM seu_banco LIKE 'pedidos'\G

*************************** 1. row ***************************
           Name: pedidos
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1482310
 Avg_row_length: 174
    Data_length: 257425408
Max_data_length: 0
   Index_length: 92381184
      Data_free: 54525952
 Auto_increment: 2100001
    Create_time: 2024-08-15 10:22:01
    Update_time: 2025-01-10 03:14:55
     Check_time: NULL
      Collation: utf8mb4_unicode_ci

Como executar ANALYZE TABLE e OPTIMIZE TABLE no MySQL 8.0

Com as tabelas fragmentadas identificadas, o próximo passo é executar os comandos de manutenção na ordem correta. A reconstrução de índices e a atualização de estatísticas são operações distintas com impactos diferentes no desempenho de queries.

Comece sempre pelo ANALYZE TABLE, que é mais rápido e não reorganiza dados físicos — apenas atualiza as estatísticas usadas pelo otimizador de consultas para escolher o melhor plano de execução:

USE seu_banco;
ANALYZE TABLE pedidos, sessoes, logs_acesso;

+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| seu_banco.pedidos | analyze | status   | OK       |
| seu_banco.sessoes | analyze | status   | OK       |
| seu_banco.logs_acesso | analyze | status | OK    |
+-------------------+---------+----------+----------+

Atenção: o comando OPTIMIZE TABLE a seguir reconstrói a tabela inteira. Em tabelas grandes (acima de 1 GB), o processo pode levar vários minutos e consumir espaço temporário em disco. Confirme que há espaço suficiente antes de prosseguir.

Execute o OPTIMIZE TABLE nas tabelas com maior fragmentação:

OPTIMIZE TABLE pedidos;

+-------------------+----------+----------+-------------------------------------------------------------------+
| Table             | Op       | Msg_type | Msg_text                                                          |
+-------------------+----------+----------+-------------------------------------------------------------------+
| seu_banco.pedidos | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| seu_banco.pedidos | optimize | status   | OK                                                                |
+-------------------+----------+----------+-------------------------------------------------------------------+

A mensagem "Table does not support optimize, doing recreate + analyze instead" é normal para tabelas InnoDB no MySQL 8.0 — o banco executa internamente um ALTER TABLE ... FORCE seguido de ANALYZE TABLE, que é o comportamento correto.

Para otimizar todas as tabelas de um banco de dados de uma só vez via linha de comando (sem entrar no prompt do MySQL), use o utilitário mysqlcheck:

mysqlcheck -u root -p --optimize seu_banco

seu_banco.pedidos                          OK
seu_banco.sessoes                          OK
seu_banco.logs_acesso                      OK

Para otimizar todos os bancos de dados do servidor de uma vez:

mysqlcheck -u root -p --optimize --all-databases

Como verificar o resultado da otimização de tabelas MySQL

Após executar os comandos de manutenção, confirme que a fragmentação foi eliminada consultando novamente o information_schema. A coluna DATA_FREE deve estar zerada ou próxima de zero para as tabelas otimizadas.

SELECT
  TABLE_NAME,
  ROUND(DATA_FREE / 1024 / 1024, 2) AS fragmentado_MB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'seu_banco'
ORDER BY DATA_FREE DESC;

+------------------+----------------+
| TABLE_NAME       | fragmentado_MB |
+------------------+----------------+
| pedidos          |           0.00 |
| sessoes          |           0.00 |
| logs_acesso      |           0.00 |
+------------------+----------------+

Você também pode verificar o tempo de execução de uma query que estava lenta antes da otimização usando o EXPLAIN:

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 12345 AND status = 'pendente';

Observe as colunas type, key e rows no output. Após o ANALYZE TABLE, o otimizador deve escolher o índice correto e o valor de rows deve ser significativamente menor do que antes, indicando que o plano de execução foi aprimorado.

Como automatizar a otimização de tabelas MySQL com cron

Executar a manutenção de tabelas manualmente todo mês é ineficiente. A automação via cron garante que a desfragmentação aconteça regularmente, em horários de baixo tráfego, sem intervenção manual. Esta abordagem é especialmente útil em aplicações com alto volume de escritas, como e-commerces e sistemas de log.

Crie um script de otimização em /usr/local/bin/otimizar_mysql.sh:

#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="sua_senha_aqui"
BANCO="seu_banco"
LOG="/var/log/mysql_optimize.log"

echo "=== Otimização iniciada: $(date) ===" >> "$LOG"
mysqlcheck -u "$MYSQL_USER" -p"$MYSQL_PASS" --optimize "$BANCO" >> "$LOG" 2>&1
echo "=== Otimização concluída: $(date) ===" >> "$LOG"

Atenção: armazenar senha em texto plano em scripts é um risco de segurança. Prefira usar um arquivo de opções do MySQL (~/.my.cnf) com permissão restrita:

[client]
user=root
password=sua_senha_aqui

chmod 600 ~/.my.cnf

Com o arquivo .my.cnf configurado, simplifique o script:

#!/bin/bash
BANCO="seu_banco"
LOG="/var/log/mysql_optimize.log"

echo "=== Otimização iniciada: $(date) ===" >> "$LOG"
mysqlcheck --optimize "$BANCO" >> "$LOG" 2>&1
echo "=== Otimização concluída: $(date) ===" >> "$LOG"

Torne o script executável:

chmod +x /usr/local/bin/otimizar_mysql.sh

Adicione a entrada no cron para executar todo primeiro domingo do mês às 2h da manhã:

crontab -e

0 2 * * 0 [ $(date +\%d) -le 7 ] && /usr/local/bin/otimizar_mysql.sh

Para verificar se o cron está registrado corretamente:

crontab -l

Se você gerencia múltiplos bancos de dados em hospedagem compartilhada com cPanel, veja também como conectar remotamente ao MySQL via cPanel para executar esses comandos a partir de ferramentas externas como MySQL Workbench ou HeidiSQL.

Otimização avançada: índices e configuração do InnoDB

A desfragmentação de tabelas resolve parte do problema de performance, mas a criação de índices adequados e o ajuste do buffer pool do InnoDB têm impacto ainda maior na velocidade de consultas. Estas são as alavancas mais poderosas para quem quer ir além do básico.

Verificar índices existentes em uma tabela:

SHOW INDEX FROM pedidos;

Criar índice composto para queries frequentes com múltiplos filtros:

ALTER TABLE pedidos ADD INDEX idx_cliente_status (cliente_id, status);

Remover índice duplicado ou não utilizado:

ALTER TABLE pedidos DROP INDEX idx_antigo;

Para o InnoDB, o parâmetro mais importante é o innodb_buffer_pool_size. Ele define quanto de RAM o MySQL usa para cachear dados e índices. Em servidores dedicados ao banco de dados, configure para 70-80% da RAM disponível. Edite o arquivo /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu/Debian) ou /etc/my.cnf (Rocky Linux / AlmaLinux):

[mysqld]
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
query_cache_type = 0

Após editar, reinicie o MySQL:

systemctl restart mysql

Confirme que o novo valor foi aplicado:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+------------+

Nota sobre query_cache_type = 0: o Query Cache foi removido no MySQL 8.0. Se você ainda usa MySQL 5.7, desabilitar o Query Cache em ambientes com muitas escritas simultâneas melhora a performance, pois ele causa contenção de lock.

Problemas comuns e como resolver

Sintoma: OPTIMIZE TABLE retorna erro "Table is full"

Causa: o MySQL não tem espaço em disco suficiente para criar a tabela temporária durante a reconstrução. O processo exige espaço livre equivalente ao tamanho atual da tabela mais seus índices.
Solução: verifique o espaço disponível com df -h /var/lib/mysql. Libere espaço removendo logs antigos (PURGE BINARY LOGS BEFORE NOW();), arquivos temporários ou snapshots desnecessários. Após liberar espaço, execute o OPTIMIZE TABLE novamente.

Sintoma: mysqlcheck retorna "Table 'X' doesn't exist" para tabelas que existem

Causa: o nome do banco de dados ou da tabela foi digitado com capitalização incorreta em sistemas Linux, onde o MySQL é case-sensitive por padrão para nomes de tabelas.
Solução: verifique o nome exato com SHOW TABLES FROM seu_banco; dentro do prompt MySQL. Use o nome exato retornado pelo comando, respeitando maiúsculas e minúsculas. Alternativamente, configure lower_case_table_names = 1 no my.cnf para normalizar nomes (requer reinicialização e dump/restore do banco).

Sintoma: OPTIMIZE TABLE trava outras queries por muito tempo

Causa: em tabelas MyISAM, o OPTIMIZE TABLE adquire um lock exclusivo durante toda a operação. Em tabelas InnoDB muito grandes, mesmo com reconstrução online, o impacto em escritas simultâneas pode ser perceptível.
Solução: para tabelas MyISAM críticas, considere migrar para InnoDB com ALTER TABLE nome ENGINE=InnoDB;. Para tabelas InnoDB em produção com SLA rigoroso, use a ferramenta pt-online-schema-change do Percona Toolkit, que realiza a reconstrução sem bloquear leituras ou escritas. Execute sempre em janelas de manutenção quando possível.

Sintoma: DATA_FREE continua alto após OPTIMIZE TABLE

Causa: o MySQL pode reservar algum espaço livre intencionalmente para futuras inserções, especialmente em tabelas InnoDB com innodb_file_per_table = ON. Um valor residual pequeno (alguns MB) é normal.
Solução: se o valor de DATA_FREE ainda for muito alto após a otimização, verifique se a tabela usa o engine correto com SHOW TABLE STATUS. Tabelas com engine MEMORY ou CSV não suportam OPTIMIZE TABLE. Para InnoDB, confirme que innodb_file_per_table está ativo: SHOW VARIABLES LIKE 'innodb_file_per_table';

Perguntas frequentes sobre otimizar tabelas MySQL

Quando devo executar OPTIMIZE TABLE no MySQL?

Execute OPTIMIZE TABLE após deletar ou atualizar grandes volumes de registros, pois essas operações deixam espaço fragmentado no arquivo de dados. Em tabelas InnoDB com alto volume de escritas, rodar o comando mensalmente ou após migrações de dados é uma boa prática. Evite executar em horário de pico, pois o comando pode bloquear leituras temporariamente em versões mais antigas do MySQL.

Qual a diferença entre OPTIMIZE TABLE e ANALYZE TABLE no MySQL?

OPTIMIZE TABLE reorganiza o armazenamento físico dos dados, recupera espaço fragmentado e reconstrói os índices da tabela. ANALYZE TABLE apenas atualiza as estatísticas de distribuição de dados usadas pelo otimizador de consultas para escolher o melhor plano de execução. Para melhorar a velocidade de queries lentas, comece com ANALYZE TABLE; para recuperar espaço em disco e desfragmentar, use OPTIMIZE TABLE.

O OPTIMIZE TABLE bloqueia a tabela durante a execução?

Em tabelas InnoDB com MySQL 5.6 ou superior, o OPTIMIZE TABLE usa reconstrução online e minimiza o bloqueio de leituras, mas ainda pode causar impacto em escritas simultâneas. Em tabelas MyISAM, o bloqueio é exclusivo durante toda a operação. Para ambientes de produção críticos, prefira executar durante janelas de manutenção ou use a ferramenta pt-online-schema-change do Percona Toolkit.

Como identificar quais tabelas MySQL estão fragmentadas?

Consulte a tabela information_schema.TABLES com a query: SELECT TABLE_NAME, DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'seu_banco' AND DATA_FREE > 0 ORDER BY DATA_FREE DESC. O campo DATA_FREE indica bytes de espaço fragmentado não utilizado. Tabelas com DATA_FREE acima de alguns megabytes são candidatas prioritárias para otimização.

Otimizar tabelas MySQL resolve queries lentas?

Otimizar tabelas resolve lentidão causada por fragmentação e estatísticas desatualizadas, mas não substitui a criação de índices adequados. Se uma query está lenta por ausência de índice na coluna filtrada pelo WHERE, o OPTIMIZE TABLE não ajudará. Use EXPLAIN antes da query para identificar se o problema é falta de índice, full table scan ou estatísticas incorretas, e então aplique a solução correspondente.

Conclusão

• Identifique antes de agir: use a query no information_schema.TABLES para priorizar tabelas com maior DATA_FREE e evite otimizar tabelas que não precisam.
• Siga a ordem correta: execute ANALYZE TABLE primeiro para atualizar estatísticas, depois OPTIMIZE TABLE nas tabelas fragmentadas — e sempre fora do horário de pico.
• Automatize com cron: configure o mysqlcheck --optimize para rodar mensalmente via cron e mantenha um arquivo de log para auditar as execuções.

Leia também

• Solucionar lentidão no MySQL 8.0 antes que derrube sua aplicação
• Otimizar MySQL: como reduzir uso de memória e acelerar consultas
• Guia: Laravel não conecta ao MySQL no Debian 12

Precisa de ajuda com performance de banco de dados MySQL?

Configurar e manter um banco de dados MySQL com alta performance exige atenção contínua a índices, fragmentação e parâmetros do InnoDB. Um VPS com recursos dedicados e suporte especializado pode fazer toda a diferença para aplicações que dependem de consultas rápidas e disponibilidade constante.

Conheça os planos de hospedagem da AviraHost com suporte a MySQL otimizado