Pilares da Otimização de Banco de Dados

A otimização de um ambiente MySQL deve ser abordada de forma holística, dividindo-se em quatro camadas principais: infraestrutura de hardware, ajuste de parâmetros do servidor, modelagem de dados e engenharia de consultas (SQL e índices).

1. Infraestrutura e Recursos de Hardware

Antes de alterar qualquer linha de código, avalei o hardware. Substituir discos rígidos (HDD) por unidades de estado sólido (SSD) NVMe, aumentar a quantidade de núcleos de CPU para processamento paralelo e expandir a memória RAM para acomodar um Buffer Pool maior são as maneiras mais rápidas de obter ganhos de performance, embora impliquem custos financeiros mais elevados.

2. Ajuste Fino de Parâmetros do Servidor

Maximizando a Leitura em Memória

O InnoDB utiliza o Bufffer Pool para armazenar dados e índices na memória RAM, empregando uma variação otimizada do algoritmo LRU (Least Recently Used). Para verificar a alocação atual das páginas de memória, utilize o dicionário de desempenho:

SELECT VARIABLE_NAME, VARIABLE_VALUE 
FROM performance_schema.global_status 
WHERE VARIABLE_NAME LIKE 'Innodb_buffer_pool_pages%';

Em servidores dedicados, recomenda-se alocar entre 70% e 80% da RAM total para o Buffer Pool. Esta configuração deve ser aplicada no arquivo de configuração (my.cnf ou my.ini):

[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8

Pré-carregamento de Dados (Warm-up)

Carregar dados do disco para a memória antes do pico de acesso reduz a latência inicial. O script abaixo gera consultas para forçar a leitura dos índices de todas as tabelas InnoDB personalizadas para a memória:

SELECT CONCAT('SELECT COUNT(`', col_list, '`) FROM `', schema_name, '`.`', table_name, '` FORCE INDEX(`', index_name, '`);') AS warmup_statement
FROM (
    SELECT s.TABLE_SCHEMA AS schema_name, s.TABLE_NAME AS table_name, s.INDEX_NAME AS index_name,
           GROUP_CONCAT(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX) AS col_list
    FROM information_schema.STATISTICS s
    JOIN information_schema.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME
    WHERE t.ENGINE = 'InnoDB' AND s.TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema')
    GROUP BY s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
) AS metadata
ORDER BY schema_name, table_name;

Minimização de I/O em Disco

Reduzir a frequência de gravação em disco é crucial. Ajuste o tamanho do innodb_log_file_size para cerca de 25% do tamanho do Buffer Pool. Além disso, desabilite o log geral e o log de consultas lentas em ambientes de produção de alta carga, mantendo apenas o binlog para replicação e recuperação.

Para o parâmetro innodb_flush_log_at_trx_commit:

• 0 ou 2: Ideal para sistemas que toleram perda mínima de dados em troca de alta performance de escrita (grava no cache do SO ou no arquivo de log, mas o flush para o disco ocorre a cada segundo).
• 1: Padrão do MySQL. Garante durabilidade total (ACID), mas gera maior I/O.

Variáveis de Conexão e Sistema

• max_connections e back_log: Aumente o back_log (ex: 500) para suportar picos de requisições na fila antes de rejeitar conexões.
• wait_timeout: Reduza para 1800 segundos (30 min) para liberar conexões ociosas.
• skip_name_resolve: Desabilite a resolução de DNS reverso para acelerar o handshake de novas conexões.
• thread_cache_size: Mantenha threads em cache para reutilização, evitando a sobrecarga de criação de novas threads.

3. Modelagem e Estrutura de Tabelas

Desnormalização e Tabelas de Agregação

Para relatórios e análises complexas, crie tabelas de agregação ou adicione campos redundantes para evitar JOINs pesados. Isso troca espaço de armazenamento e complexidade de atualização por velocidade de leitura.

Particionamento e Sharding

• Vertical: Isole colunas de texto longo (como TEXT ou BLOB) ou campos raramente acessados em tabelas separadas.
• Horizontal: Divida tabelas com dezenas de milhões de registros em múltiplas tabelas menores baseadas em faixas de ID ou intervalos de tempo.

Escolha de Tipos de Dados

• Utilize TINYINT ou SMALLINT em vez de INT quando o intervalo de valores permitir.
• Defina o tamanho do VARCHAR estritamente conforme a necessidade real.
• Armazene endereços IPv4 como inteiros sem sinal (INT UNSIGNED) usando funções de conversão.
• Prefira TIMESTAMP (4 bytes) em vez de DATETIME (8 bytes) para registros de data/hora.
• Evite colunas NULL sempre que possível; use valores padrão. O tratamento de nulos consome espaço extra e dificulta a otimização do índice.

4. Engenharia de Consultas SQL e Índices

Ative o log de consultas lentas para identificar gargalos:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 3;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Diretrizes para Indexação

• Crie índices nas colunas utilizadas em cláusulas WHERE, ORDER BY e GROUP BY.
• Em operações de JOIN, indexe as colunas de associação da tabela que será varrida (a tabela à direita em um LEFT JOIN).
• Nunca aplique funções, cálculos ou conversões de tipo sobre a coluna indexada na cláusula WHERE.
• Evite índices em colunas com baixa cardinalidade (como "gênero" ou "status").

Análise com EXPLAIN

Ao executar EXPLAIN antes de uma consulta, foque na coluna type. A hierarquia de performance, da pior para a melhor, é: ALL (varredura completa), index, range, ref, eq_ref e const. Verifique também a coluna Extra para alertas como Using filesort ou Using temporary, que indicam operações custosas em memória ou disco.

Boas Práticas de Escrita SQL

• Seletividade: Evite SELECT *. Especifique apenas as colunas necessárias para permitir o uso de índices de cobertura (Covering Indexes) e reduzir o tráfego de rede.
• Limitação: Adicione LIMIT 1 quando estiver buscando um registro único para interromper a varredura prematuramente.
• Operadores: Prefira BETWEEN para intervalos contínuos em vez de múltiplas condições IN. Evite listas massivas dentro do IN.
• Uniões: Utilize UNION ALL em vez de UNION se não houver necessidade de remover duplicatas, pois o UNION força uma operação de DISTINCT implícita e custosa.
• Curingas: Nunca inicie uma busca LIKE com o caractere % (ex: LIKE '%termo'), pois isso invalida a árvore B-Tree do índice.
• Expressões: Mova cálculos para o lado direito da equação. Em vez de WHERE annual_salary / 12 = 8000, utilize WHERE annual_salary = 8000 * 12.

Otimização de Subconsultas e JOINs

A regra de ouro é fazer com que o menor conjunto de dados dirija a consulta (Small Table Drives Large Table).

-- Quando a subconsulta (departments) retorna um conjunto menor que a tabela externa (employees)
SELECT e.full_name, e.salary 
FROM employees e 
WHERE e.dept_id IN (SELECT d.dept_id FROM departments d WHERE d.is_active = 1);

-- Quando a tabela externa (departments) é menor que o conjunto da subconsulta (employees)
SELECT d.dept_name 
FROM departments d 
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id AND e.status = 'active');

Sempre que possível, substitua subconsultas correlacionadas por JOINs, pois o otimizador do MySQL consegue executar junções de forma muito mais eficiente, evitando a criação de tabelas temporárias em memória.

5. Escala Arquitetural

Replicação Master-Slave

Implemente a separação de leitura e escrita. Direcione todas as transações de escrita (INSERT, UPDATE, DELETE) e leituras críticas que exigem consistência imediata para o nó primário. Distribua as consultas de leitura pesadas e relatórios analíticos entre os nós réplicas usando balanceamento de carga (Round Robin).

Sharding de Banco de Dados

Para sistemas massivos, divida os bancos de dados por domínio de negócio (ex: banco de dados de pedidos vs. banco de dados de usuários) ou por dimensão temporal (ex: tabelas históricas separadas por ano). Isso isola falhas, reduz o tamenho dos índices e permite que o hardware seja dimensionado de forma independente para cada carga de trabalho.