Técnicas de otimização de banco de dados MySQL

1. Fundamentos de Performance e Diagnóstico

A otimização de banco de dados MySQL começa com a identificação precisa de gargalos. A ferramenta mais fundamental é o comando EXPLAIN, que revela o plano de execução das consultas.

mysql> EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 123 AND data > '2024-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pedidos
   partitions: NULL
         type: ref
possible_keys: idx_cliente_data
          key: idx_cliente_data
      key_len: 5
          ref: const
         rows: 450
     filtered: 33.33
        Extra: Using where; Using index

Para consultas lentas, ative o log específico:

mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL long_query_time = 2;
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

O SHOW PROFILE permite analisar o tempo gasto em cada etapa:

mysql> SET profiling = 1;
mysql> SELECT COUNT(*) FROM clientes WHERE status = 'ativo';
mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000034 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000018 |
| System lock          | 0.000004 |
| init                 | 0.000012 |
| optimizing           | 0.000003 |
| statistics           | 0.000039 |
| preparing            | 0.000010 |
| executing            | 0.000002 |
| Sending data         | 0.000452 |
| end                  | 0.000004 |
| query end            | 0.000002 |
| closing tables       | 0.000005 |
| freeing items        | 0.000012 |
| cleaning up          | 0.000007 |
+----------------------+----------+

Métricas essenciais com SHOW STATUS:

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
mysql> SHOW STATUS LIKE 'Created_tmp_disk_tables';

2. Indexação Estratégica

Índices bem planejados são o coração da performance. Índices compostos exigem atenção à ordem das colunas:

CREATE TABLE vendas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data DATE NOT NULL,
    cliente_id INT NOT NULL,
    valor DECIMAL(10,2) NOT NULL,
    status ENUM('pendente','pago','cancelado') DEFAULT 'pendente',
    INDEX idx_data_cliente_status (data, cliente_id, status)
);

Índices de cobertura (covering index) evitam acesso à tabela:

mysql> EXPLAIN SELECT data, cliente_id FROM vendas WHERE data BETWEEN '2024-01-01' AND '2024-01-31'\G
*************************** 1. row ***************************
        table: vendas
         type: range
possible_keys: idx_data_cliente_status
          key: idx_data_cliente_status
      key_len: 3
         rows: 1200
        Extra: Using where; Using index

Índices parciais para colunas longas:

CREATE INDEX idx_email_prefix ON usuarios (email(10));

Monitore índices duplicados ou não utilizados:

mysql> SELECT * FROM sys.schema_unused_indexes;
mysql> SELECT * FROM sys.schema_redundant_indexes;

3. Otimização de Consultas SQL

Subconsultas muitas vezes podem ser reescritas com melhor performance:

-- Ineficiente
SELECT * FROM clientes WHERE id IN (SELECT cliente_id FROM pedidos WHERE total > 1000);

-- Otimizado
SELECT DISTINCT c.* FROM clientes c
INNER JOIN pedidos p ON c.id = p.cliente_id
WHERE p.total > 1000;

Uso correto de ORDER BY e GROUP BY:

-- Evite ORDER BY RAND()
SELECT * FROM produtos ORDER BY RAND() LIMIT 10;

-- Alternativa eficiente
SELECT * FROM produtos WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM produtos))) LIMIT 10;

Paginação eficiente sem OFFSET:

-- Lento para páginas avançadas
SELECT * FROM pedidos ORDER BY id LIMIT 100000, 20;

-- Rápido com busca por chave
SELECT * FROM pedidos WHERE id > 100000 ORDER BY id LIMIT 20;

4. Configuração de Parâmetros do Servidor

Ajustes críticos no my.cnf:

[mysqld]
# Buffer pool - 70-80% da RAM disponível em servidores dedicados
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8

# Log de transações
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M

# Cache de consultas (desativado no MySQL 8.0+)
# query_cache_size = 0 (removido no MySQL 8.0)
# query_cache_type = 0

# Conexões
max_connections = 500
thread_cache_size = 50

# Outros ajustes
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
tmp_table_size = 64M
max_heap_table_size = 64M

Verifique o impacto dos ajustes:

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';

5. Estrutura e Modelagem de Dados

Escolha tipos de dados otimizados:

CREATE TABLE usuarios (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sexo ENUM('M','F') NOT NULL,          -- 1 byte vs 1+ byte VARCHAR
    idade TINYINT UNSIGNED NOT NULL,       -- 1 byte vs 4 bytes INT
    ativo BOOLEAN NOT NULL DEFAULT TRUE,   -- 1 byte
    nome VARCHAR(100) NOT NULL,            -- apenas o necessário
    cpf CHAR(11) NOT NULL,                 -- fixo, melhor que VARCHAR(11)
    data_cadastro TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

Desnormalização controlada para consultas frequentes:

-- Tabela normalizada
CREATE TABLE pedidos_itens (
    pedido_id INT,
    produto_id INT,
    quantidade INT,
    preco_unitario DECIMAL(10,2)
);

-- Tabela desnormalizada para relatórios
CREATE TABLE pedidos_resumo (
    pedido_id INT PRIMARY KEY,
    cliente_nome VARCHAR(100),
    total_itens INT,
    valor_total DECIMAL(10,2),
    data_pedido DATE,
    INDEX idx_data (data_pedido)
);

Particionamento de tabelas:

CREATE TABLE logs_acesso (
    id INT AUTO_INCREMENT,
    data_hora DATETIME NOT NULL,
    usuario_id INT NOT NULL,
    acao VARCHAR(50),
    PRIMARY KEY (id, data_hora)
) PARTITION BY RANGE (YEAR(data_hora)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

6. Estratégias de Particionamento e Sharding

Particionamento por chave primária:

CREATE TABLE transacoes (
    id INT AUTO_INCREMENT,
    conta_id INT NOT NULL,
    valor DECIMAL(10,2),
    data DATE,
    PRIMARY KEY (id, conta_id)
) PARTITION BY HASH(conta_id) PARTITIONS 16;

Sharding lógico para dados de múltiplos tenants:

-- Tabelas separadas por região
CREATE TABLE clientes_br LIKE clientes_template;
CREATE TABLE clientes_ar LIKE clientes_template;
CREATE TABLE clientes_mx LIKE clientes_template;

-- View unificada para consultas
CREATE VIEW clientes_todos AS
SELECT 'BR' as pais, * FROM clientes_br
UNION ALL
SELECT 'AR' as pais, * FROM clientes_ar
UNION ALL
SELECT 'MX' as pais, * FROM clientes_mx;

7. Manutenção e Monitoramento Contínuo

Rotinas de manutenção programadas:

-- Analisar e otimizar tabelas
mysql> ANALYZE TABLE vendas;
mysql> OPTIMIZE TABLE logs_acesso;

-- Verificar fragmentação
mysql> SELECT table_name, ROUND(data_length/1024/1024) as data_mb,
       ROUND(index_length/1024/1024) as index_mb,
       ROUND(data_free/1024/1024) as free_mb
FROM information_schema.tables
WHERE table_schema = 'meu_banco';

Uso do MySQLTuner para recomendações:

$ perl mysqltuner.pl --host 127.0.0.1 --user root --pass senha

Backup e replicação para alta disponibilidade:

# Backup completo com mysqldump
$ mysqldump --single-transaction --routines --events meu_banco > backup_$(date +%Y%m%d).sql

# Configuração de replicação no master
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server-id = 1
binlog_do_db = meu_banco

# Configuração no slave
[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
read_only = 1

Monitoramento com Performance Schema:

mysql> SELECT * FROM performance_schema.events_statements_summary_by_digest
       ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

A otimização de banco de dados MySQL é um processo contínuo que combina diagnóstico preciso, indexação inteligente, consultas eficientes e configuração adequada do servidor. A implementação sistemática dessas técnicas resulta em ganhos significativos de performance, escalabilidade e confiabilidade.

Referências