Práticas Recomendadas para Otimização de Consultas SQL
Este artigo apresenta 30 dicas práticas para otimizar consultas SQL, focando em melhorar a performance em bancos de dados relacionais como MySQL.
- Selecione Apenas Colunas Necessárias
Em vez de usar SELECT *, especifique colunas específicas para reduzir consumo de recursos e evitar consultas desnecessárias.
-- Exemplo com seleção genérica
SELECT * FROM empregado;
-- Exemplo otimizado
SELECT identificador, nome_completo FROM empregado;
Razão: Minimiza transferência de dados e evita índices cobertos, otimizando a performance.
- Utilize LIMIT para Restringir Resultados
Quando a consulta retorna um único registro ou apenas o primeiro resultado, use LIMIT 1 para interromper a varredura após a primeira correspondência.
-- Consulta sem limite
SELECT identificador, nome_completo FROM empregado WHERE nome_completo = 'maria';
-- Consulta com limite
SELECT identificador, nome_completo FROM empregado WHERE nome_completo = 'maria' LIMIT 1;
Razão: Evita varredura completa da tabela, acelerando a execução em buscas específicas.
- Evite Condições OR em Cláusulas WHERE
O uso de OR pode desativar índices, levando a varreduras completas. Prefira alternativas como UNION ALL.
-- Exemplo com OR
SELECT * FROM usuario WHERE id_usuario = 10 OR idade = 25;
-- Exemplo otimizado com UNION ALL
SELECT * FROM usuario WHERE id_usuario = 10
UNION ALL
SELECT * FROM usuario WHERE idade = 25;
Razão: Condições OR podem forçar o otimizador a ignorar índices, aumentando o custo da consulta.
- Otimize Paginação com LIMIT
Para paginação eficiente, evite grandes deslocamentos. Use estratégias como filtro por último registro ou ORDER BY com índice.
-- Paginação com alto deslocamento
SELECT identificador, nome_completo FROM empregado LIMIT 10000, 10;
-- Alternativa 1: Filtrar pelo último registro
SELECT identificador, nome_completo FROM empregado WHERE identificador > 10000 LIMIT 10;
-- Alternativa 2: ORDER BY com índice
SELECT identificador, nome_completo FROM empregado ORDER BY identificador LIMIT 10000, 10;
Razão: Deslocamentos grandes causam ineficiência, pois o banco descarta registros anteriores.
- Otimize Consultas com LIKE
Padrões LIKE com '%' no início desativam índices. Coloque o curinga no final para aproveitar índices.
-- Consulta com padrão ineficiente
SELECT id_usuario, nome FROM usuario WHERE id_usuario LIKE '%456';
-- Consulta otimizada
SELECT id_usuario, nome FROM usuario WHERE id_usuario LIKE '456%';
Razão: Índices são usados apenas quando o padrão começa com valores fixos.
- Restrinja Dados Retornados com WHERE
Selecione apenas dados necessários para evitar processamento extra e reduzir carga na rede.
-- Exemplo ineficiente
LIST<long> idsUsuarios = consultaSQL.listar("SELECT id_usuario FROM usuario WHERE eh_vip = 1");
boolean ehVip = idsUsuarios.contem(id_usuario);
-- Exemplo otimizado
Long idRetornado = consultaSQL.consultarObjeto("SELECT id_usuario FROM usuario WHERE id_usuario = '123' AND eh_vip = '1'");
boolean ehVip = idRetornado != null;
</long>
Razão: Consultas focadas reduzem tempo de execução e uso de memória.
- Evite Funções em Colunas Indexadas
Aplicar funções em colunas com índice pode desativá-lo. Reescreva consultas para manter a coluna pura.
-- Função aplicada à coluna
SELECT id_usuario, tempo_login FROM usuario_login WHERE DATE_ADD(tempo_login, INTERVAL 7 DAY) >= NOW();
-- Consulta reformulada
SELECT id_usuario, tempo_login FROM usuario_login WHERE tempo_login >= DATE_ADD(NOW(), INTERVAL -7 DAY);
Razão: Funções impedem o uso de índices, resultando em varreduras completas.
- Evite Operações Aritméticos em Cláusulas WHERE
Expressões em colunas indexadas invalidam índices. Realize cálculos no lado da aplicação ou em constantes.
-- Operação na coluna
SELECT * FROM usuario WHERE idade - 1 = 18;
-- Consulta otimizada
SELECT * FROM usuario WHERE idade = 19;
Razão: Cálculos em colunas impedem que o otimizador utilize índices eficientemente.
- Prefira INNER JOIN a LEFT JOIN Quando Possível
INNER JOIN geralmente retorna menos dados e é mais eficiente. Se usar LEFT JOIN, minimize a tabela da esquerda.
-- LEFT JOIN sem filtro prévio
SELECT * FROM tabela_a t1 LEFT JOIN tabela_b t2 ON t1.tamanho = t2.tamanho WHERE t1.identificador > 5;
-- LEFT JOIN com filtro antecipado
SELECT * FROM (SELECT * FROM tabela_a WHERE identificador > 5) t1 LEFT JOIN tabela_b t2 ON t1.tamanho = t2.tamanho;
Razão: INNER JOIN evita processamento de linhas não correspondentes, enquanto LEFT JOIN pode aumentar o conjunto de resultados.
- Evite Operadores != ou <>
Esses operadores podem desativar índices. Use consultas alternativas com entervalos ou OR lógico.
-- Consulta com operador !=
SELECT idade, nome FROM usuario WHERE idade <> 20;
-- Alternativa com intervalos
SELECT idade, nome FROM usuario WHERE idade < 20;
SELECT idade, nome FROM usuario WHERE idade > 20;
Razão: Operadores de desigualdade ampla levam o otimizador a escolher varreduras completas.
- Siga a Regra de Correspondência à Esquerda em Índices Compostos
Em índices compostos, consultas devem incluir colunas na ordem definida para utilizar o índice.
-- Índice composto em (id_usuario, idade)
CREATE TABLE usuario (
id INT PRIMARY KEY,
id_usuario INT NOT NULL,
idade INT,
nome VARCHAR(255),
INDEX idx_id_usuario_idade (id_usuario, idade)
);
-- Consulta que não usa o índice
SELECT * FROM usuario WHERE idade = 30;
-- Consulta que usa o índice
SELECT * FROM usuario WHERE id_usuario = 100 AND idade = 30;
Razão: Índices compostos são consultados da esquerda para a direita; ignorar a primeira coluna desativa o índice.
- Crie Índices em Colunas de Filtragem e Ordenação
Adicione índices em colunas usadas em WHERE e ORDER BY para evitar varreduras completas.
-- Consulta sem índice
SELECT * FROM usuario WHERE endereco = 'sao_paulo' ORDER BY idade;
-- Adicionando índice
ALTER TABLE usuario ADD INDEX idx_endereco_idade (endereco, idade);
Razão: Índices aceleram filtros e ordenações, reduzindo custos de I/O.
- Use Inserção em Lote para Grandes Volumes
Inserções individuais são lentas; agrupe múltiplas inserções em uma única operação.
-- Inserção individual em loop
for (Usuario u : lista) {
INSERT INTO usuario (nome, idade) VALUES (u.nome, u.idade);
}
-- Inserção em lote
INSERT INTO usuario (nome, idade) VALUES
('ana', 25),
('carlos', 30),
('maria', 22);
Razão: Reduz sobrecarga de transações e melhora throughput do banco.
- Utilize Índices Cobertos Quando Possível
Índices cobertos permitem que a consulta seja resolvida apenas com dados do índice, sem acessar a tabela.
-- Consulta sem índice coberto
SELECT * FROM usuario WHERE id_usuario LIKE '%789%';
-- Consulta com índice coberto (supondo índice em id_usuario)
SELECT id, id_usuario FROM usuario WHERE id_usuario LIKE '%789%';
Razão: Elimina acesso à tabela, reduzindo tempo de resposta.
- Cautela ao Usar DISTINCT
DISTINCT consome recursos para deduplicação; use-o apenas quando necessário e em colunas específicas.
-- Uso ineficiente de DISTINCT
SELECT DISTINCT * FROM usuario;
-- Uso otimizado
SELECT DISTINCT nome FROM usuario;
Razão: Operações de deduplicação aumentam uso de CPU, especialmente em grandes conjuntos de dados.
- Remova Índices Redundantes
Índices duplicados ou subconjuntos de índices compostos ocupam espaço e desaceleram manutenção.
-- Índices redundantes
INDEX idx_id_usuario (id_usuario),
INDEX idx_id_usuario_idade (id_usuario, idade);
-- Mantendo apenas o índice composto
INDEX idx_id_usuario_idade (id_usuario, idade);
Razão: Índices extras aumentam custos de inserção e atualização sem benefício adicional.
- Divida Operações de Exclusão ou Atualização em Lotes
Operações massivas podem travar tabelas e consumir recursos excessivamente. Processe em blocos menores.
-- Exclusão em massa
DELETE FROM usuario WHERE id < 100000;
-- Exclusão em lotes
DELETE FROM usuario WHERE id < 5000;
DELETE FROM usuario WHERE id >= 5000 AND id < 10000;
Razão: Evita timeouts e sobrecarga no sistema, mantendo concorrência.
- Use Valores Padrão em Vez de NULL
Consultas com IS NULL ou IS NOT NULL podem não usar índices. Considere valores padrão para simplificar.
-- Consulta com NULL
SELECT * FROM usuario WHERE idade IS NOT NULL;
-- Consulta com valor padrão
SELECT * FROM usuario WHERE idade > 0;
Razão: Valores padrão permitem uso mais previsível de índices, dependendo do otimizador.
- Limite o Número de Tabelas em JOINs
Junções complexas aumentam tempo de compilação e execução. Divida consultas em etapas menores quando possível.
-- Consulta com muitas tabelas
SELECT * FROM a JOIN b ON a.id = b.id_a JOIN c ON b.id = b.id_c JOIN d ON c.id = d.id_c;
-- Considere simplificar ou usar subconsultas
Razão: Cada JOIN adiciona custo; muitos JOINs podem tornar a consulta ineficiente.
- Escolha Entre IN e EXISTS com Base no Tamanho dos Dados
Use IN quando a subconsulta retorna dados pequenos; EXISTS quando a tabela externa é menor.
-- Exemplo com IN (subconjunto pequeno)
SELECT * FROM empregado WHERE id_departamento IN (SELECT id FROM departamento WHERE localizacao = 'sp');
-- Exemplo com EXISTS (tabela externa pequena)
SELECT * FROM empregado WHERE EXISTS (SELECT 1 FROM departamento WHERE departamento.id = empregado.id_departamento AND localizacao = 'sp');
Razão: O desempenho depende do custo relativo; IN é otimizado para listas pequenas, EXISTS para consultas correlacionadas.
- Prefira UNION ALL a UNION
UNION remove duplicatas, o que exige ordenação; UNION ALL é mais rápido quando duplicatas são permitidas.
-- Consulta com UNION
SELECT * FROM usuario WHERE id_usuario = 10 UNION SELECT * FROM usuario WHERE idade = 25;
-- Consulta com UNION ALL
SELECT * FROM usuario WHERE id_usuario = 10 UNION ALL SELECT * FROM usuario WHERE idade = 25;
Razão: UNION ALL evita etapas de deduplicação, reduzindo tempo de execução.
- Limite a Quantidade de Índices por Tabela
Muitos índices degradam performance em operações de escrita. Avalie necessidade e mantenha menos de cinco índices por tabela.
-- Tabela com muitos índices
CREATE TABLE usuario (
id INT PRIMARY KEY,
nome VARCHAR(255),
email VARCHAR(255),
idade INT,
INDEX idx_nome (nome),
INDEX idx_email (email),
INDEX idx_idade (idade),
INDEX idx_nome_email (nome, email)
);
Razão: Índices adicionam overhead em INSERT, UPDATE e DELETE; balanceie com consultas frequentes.
- Use Tipos Numéricos para Dados Numéricos
Colunas numéricas devem usar tipos como INT em vez de VARCHAR para melhor performance em comparações e junções.
-- Tipo ineficiente
codigo_regente VARCHAR(20) NOT NULL;
-- Tipo otimizado
codigo_regente INT NOT NULL;
Razão: Comparações numéricas são mais rápidas e consomem menos espaço que strings.
- Evite Índices em Colunas com Baixa Seletividade
Colunas com muitos valores repetidos, como status ou gênero, raramente se beneficiam de índices.
-- Coluna com poucos valores únicos
genero CHAR(1); -- 'M', 'F', 'O'
-- Índice nessa coluna pode ser ignorado pelo otimizador
Razão: O otimizador pode preferir varredura completa se o índice não reduz significativamente o conjunto de dados.
- Limite a Quantidade de Dados Retornados ao Cliente
Para grandes conjuntos de dados, use paginação ou filtros para reduzir transferência.
-- Consulta sem paginação
SELECT * FROM registro_visualizacao WHERE id_usuario = 100 AND data_visualizacao >= DATE_SUB(NOW(), INTERVAL 1 YEAR);
-- Com paginação
SELECT * FROM registro_visualizacao WHERE id_usuario = 100 AND data_visualizacao >= DATE_SUB(NOW(), INTERVAL 1 YEAR) LIMIT 0, 100;
Razão: Reduz consumo de memória e rede, melhorando responsividade.
- Use Apelidos de Tabela em Consultas Multi-tabela
Apelidos melhoram legibilidade e evitam ambiguidades em colunas.
-- Sem apelidos
SELECT * FROM empregado INNER JOIN departamento ON empregado.id_departamento = departamento.id;
-- Com apelidos
SELECT e.nome, d.nome_departamento FROM empregado e INNER JOIN departamento d ON e.id_departamento = d.id;
Razão: Facilita manutenção e evita eros em nomes de colunas.
- Prefira VARCHAR a CHAR para Campos de Comprimento Variável
CHAR aloca espaço fixo, enquanto VARCHAR usa espaço dinâmico, economizando armazenamento.
-- Campo com CHAR
nome_departamento CHAR(100);
-- Campo com VARCHAR
nome_departamento VARCHAR(100);
Razão: VARCHAR reduz espaço em disco e melhora eficiência em consultas por campos menores.
28: Otimize GROUP BY com Filtragem Prévia
Aplique filtros antes de agrupar para reduzir o conjunto de dados processado.
-- Agrupamento sem filtro prévio
SELECT cargo, AVG(salario) FROM empregado GROUP BY cargo HAVING cargo = 'gerente' OR cargo = 'analista';
-- Agrupamento com filtro
SELECT cargo, AVG(salario) FROM empregado WHERE cargo = 'gerente' OR cargo = 'analista' GROUP BY cargo;
Razão: Filtrar antes diminui o número de linhas agrupadas, acelerando a operação.
- Use Aspas para Colunas de String em WHERE
Comparar strings sem aspas pode causar conversões implícitas, desativando índices.
-- Consulta sem aspas
SELECT * FROM usuario WHERE id_usuario = 123;
-- Consulta com aspas
SELECT * FROM usuario WHERE id_usuario = '123';
Razão: Conversões de tipo impedem uso de índices e aumentam custo de CPU.
- Analise Planos de Execução com EXPLAIN
Use EXPLAIN para verificar se consultas usam índices e identificar gargalos.
EXPLAIN SELECT * FROM usuario WHERE id_usuario = 100 OR idade = 30;
Razão: Ajuda a entender como o banco executa consultas e permite ajustes direcionados.
Implementar essas práticas pode melhorar significativamente o desempenho de aplicações que dependem de bancos de dados SQL.