A otimização de bancos de dados MySQL envolve múltiplos aspectos, desde recursos de hardware até configurações do sistema, design de tabelas, consultas SQL e uso de índices. Este guia foca em práticas para melhorar o desempenho através de ajustes em SQL e estrutura de dados.

Otimização de Consultas SQL

O objteivo principal é maximizar o uso de índices, evitar varreduras completas de tabelas e reduzir a consulta de dados desnecessários.

Claúsulas WHERE

1. Evite operações de comparação com valores nulos diretamente em WHERE. Defina valores padrão como NOT NULL ou use marcadores como 0 ou -1.

2. Prefira UNION ao invés de OR para condições múltiplas, pois OR pode levar à desconsideração de índices.

-- Exemplo ineficiente
SELECT * FROM produtos WHERE categoria = 'eletrônicos' OR preco > 100;
-- Otimizado com UNION
SELECT * FROM produtos WHERE categoria = 'eletrônicos'
UNION
SELECT * FROM produtos WHERE preco > 100;

3. Evite operações aritméticas ou funções à esquerda do operador de igualdade em WHERE. Reeescreva as condições para operar sobre a coluna indexada.

-- Varredura completa
SELECT id FROM pedidos WHERE valor_total / 2 = 50;
-- Utiliza índice
SELECT id FROM pedidos WHERE valor_total = 100;

4. Ordene as condições em WHERE do mais restritivo para o menos restritivo para acelerar a redução do conjunto de resultados.

5. Evite operadores != ou <>, pois limitam o uso de índices. Utilize operadores suportados como <, =, >, BETWEEN, IN ou LIKE (quando não iniciado por curinga).

6. Alinhe ORDER BY com WHERE para permitir a ordenação via índices.

-- Ordenação sem índice
SELECT * FROM clientes ORDER BY data_cadastro;
-- Com restrição em WHERE para usar índice
SELECT * FROM clientes WHERE data_cadastro > '2020-01-01' ORDER BY data_cadastro;

Consultas SELECT

Em junções multi-tabela, posicione tabelas menores primeiro para otimizar o processo de varredura inicial.

Use aliases para tabelas e prefixe colunas com o alias para evitar ambiguidades e acelerar a análise sintática.

Cuidado com conversões implícitas de tipos; por exemplo, comparar uma coluna VARCHAR com um número pode impedir o uso de índices.

-- Conversão implícita ineficiente
SELECT codigo FROM usuarios WHERE login = 123;
-- Usar valor do tipo correto
SELECT codigo FROM usuarios WHERE login = '123';

Evite IN ou NOT IN quando possível; utilize BETWEEN para intervalos contínuos ou EXISTS para subconsultas.

-- Subconsulta com IN
SELECT * FROM pedidos WHERE cliente_id IN (SELECT id FROM clientes WHERE ativo = 1);
-- Alternativa com EXISTS
SELECT * FROM pedidos p WHERE EXISTS (SELECT 1 FROM clientes c WHERE c.id = p.cliente_id AND c.ativo = 1);

Em vez de SELECT *, especifique apenas as colunas necessárias para reduzir o volume de dados transferidos.

Comandos INSERT

Combine múltiplas inserções em uma única instrução para minimizar o overhead de transações e logs.

INSERT INTO log_eventos (timestamp, descricao, nivel)
VALUES
('2023-01-01 10:00:00', 'Login do usuário', 'INFO'),
('2023-01-01 10:05:00', 'Falha na autenticação', 'ERROR'),
('2023-01-01 10:10:00', 'Dados atualizados', 'AUDIT');

Use transações explícitas para agrupar inserções, reduzindo a frequência de commits e melhorando a eficiência.

Insira dados em ordem crescente de chave primária para otimizar o acesso a índices, especialmente em grandes volumes.

Para cargas massivsa, desative índices e restrições temporariamente, insira os dados e reative-os.

-- Para tabelas InnoDB
ALTER TABLE tabela_nova DROP INDEX idx_nome, ADD INDEX idx_novo (coluna);
BEGIN;
-- Dados ordenados por chave primária
INSERT INTO tabela_nova ...
COMMIT;
ALTER TABLE tabela_nova ADD INDEX idx_nome (coluna);

Exclusão e Agrupamento

Para DELETE em larga escala, divida a operação em lotes para evitar sobrecarga de CPU.

Em GROUP BY, filtre registros antecipadamente com WHERE para reduzir o custo de agrupamento.

Prefira WHERE sobre HAVING para condições que não envolvam funções de agregação, pois WHERE filtra antes do agrupamento.

Paginação e União

Evite LIMIT com grandes offsets; use condições baseadas em índices para posicionar os dados.

-- Ineficiente com offset grande
SELECT * FROM registros ORDER BY id LIMIT 1000000, 10;
-- Eficiente com filtro por chave
SELECT * FROM registros WHERE id > 1000000 ORDER BY id LIMIT 10;

Utilize UNION ALL em vez de UNION quando a eliminação de duplicatas não for necessária, pois evita a criação de tabelas temporárias com verificação de unicidade.

-- UNION ALL mais rápido
SELECT nome, email FROM contatos WHERE ativo = 1
UNION ALL
SELECT nome, email FROM leads WHERE fonte = 'web';

Otimização de Índices

Crie índices em colunas frequentemente usadas em WHERE e ORDER BY.

Priorize campos numéricos sobre caracteres para colunas com valores discretos, pois comparações numéricas são mais eficientes.

Use VARCHAR em vez de CHAR para campos de comprimento variável, economizando espaço de armazenamento e acelerando consultas.

Design de Tabelas

Siga as formas normais para reduzir redundâncias e melhorar a integridade.

Primeira Forma Normal (1NF): Cada tabela deve ter uma chave primária e os campos devem ser atômicos.

-- Exemplo não normalizado
CREATE TABLE funcionarios_temp (
    id INT,
    dados TEXT  -- Contém nome, telefone, email em um único campo
);
-- Normalizado para 1NF
CREATE TABLE funcionarios (
    id INT PRIMARY KEY,
    nome VARCHAR(100),
    telefone VARCHAR(20),
    email VARCHAR(100)
);

Segunda Forma Normal (2NF): Elimine dependências parciais em tabelas com chaves compostas, usando tabelas de junção.

-- Para relacionamentos muitos-para-muitos
CREATE TABLE projetos (
    id_projeto INT PRIMARY KEY,
    nome VARCHAR(100)
);
CREATE TABLE membros (
    id_membro INT PRIMARY KEY,
    nome VARCHAR(100)
);
CREATE TABLE atribuicoes (
    id_projeto INT,
    id_membro INT,
    PRIMARY KEY (id_projeto, id_membro),
    FOREIGN KEY (id_projeto) REFERENCES projetos(id_projeto),
    FOREIGN KEY (id_membro) REFERENCES membros(id_membro)
);

Terceira Forma Normal (3NF): Remova dependências transitivas, segregando dados em tabelas separadas e conectando-as via chaves estrangeiras.

-- Para relacionamentos um-para-muitos
CREATE TABLE departamentos (
    id_departamento INT PRIMARY KEY,
    nome VARCHAR(100)
);
CREATE TABLE funcionarios_norm (
    id_funcionario INT PRIMARY KEY,
    nome VARCHAR(100),
    id_departamento INT,
    FOREIGN KEY (id_departamento) REFERENCES departamentos(id_departamento)
);