Boas práticas de SQL: consultas corretas, rápidas e fáceis de manter

SQL é uma linguagem declarativa: você descreve o resultado que quer e o otimizador do banco decide como produzi-lo. Isso é libertador, e também é por isso que tanto SQL lento parece perfeitamente razoável. O otimizador só consegue escolher um bom plano se a consulta lhe der a chance, se o esquema lhe oferecer os índices e se a aplicação não puser tudo a perder chamando a consulta mil vezes dentro de um loop. As práticas abaixo valem para qualquer banco relacional; os exemplos usam a sintaxe do PostgreSQL 16, com as diferenças do Oracle indicadas quando importam.

Peça exatamente as linhas e colunas de que você precisa

A linha mais barata é aquela que você nunca busca. Daí decorrem diretamente dois hábitos.

Nomeie suas colunas. SELECT * transfere colunas que você não vai usar, impede os index-only scans (o índice não consegue atender uma consulta que toca colunas que ele não contém) e amarra seu código à ordem das colunas, o que cobra seu preço no dia em que alguém adiciona uma coluna no meio da tabela.

Filtre e agregue no banco, não na aplicação. Carregar 200.000 linhas na memória para somar uma coluna em Java é mais lento, consome mais memória e segura uma conexão por mais tempo do que SELECT SUM(amount) FROM payment WHERE .... A mesma lógica vale para ordenação e contagem: ORDER BY e COUNT(*) pertencem ao SQL, onde podem usar índices e workers paralelos.

ORMs tornam esse erro fácil de cometer. Com uma coleção lazy, percorrer 1.000 pedidos chamando order.getLines() em cada um dispara 1.001 consultas. Esse é o problema N+1, e a correção é sempre a mesma: uma única consulta que faça o join ou busque as linhas filhas como um conjunto.

-- One round trip instead of 1,001
SELECT o.id, o.placed_at, l.sku, l.quantity, l.unit_price
FROM   customer_order o
JOIN   order_line l ON l.order_id = o.id
WHERE  o.customer_id = $1
ORDER  BY o.placed_at DESC, o.id, l.line_no;

Em JPA, isso significa um JOIN FETCH ou um entity graph; no Spring Data, @EntityGraph ou uma projeção. O guia de Spring Data JPA mostra as duas abordagens.

Escreva predicados que o otimizador consiga usar

Um índice em email é inútil para a consulta WHERE LOWER(email) = '[email protected]'. A função transforma a coluna, então os valores armazenados no índice já não correspondem ao que a comparação precisa. Um predicado que pode ser resolvido com uma busca no índice é chamado de sargable (de "search argument"). A regra é simples: deixe a coluna intacta de um lado do operador e coloque o cálculo do outro.

A conversão implícita de tipos é a versão traiçoeira do mesmo problema. Se customer_ref é uma coluna VARCHAR e você escreve WHERE customer_ref = 12345, o PostgreSQL lança um erro, mas o Oracle converte silenciosamente o customer_ref de cada linha com TO_NUMBER e varre a tabela. Faça o bind dos parâmetros com o tipo JDBC correto e compare datas com literais de data, não com strings.

Predicados de intervalo merecem mais uma observação: um índice em (status, placed_at) responde com eficiência a WHERE status = 'PAID' AND placed_at > :since, mas um índice em (placed_at, status) não consegue aproveitar a igualdade em status depois do intervalo em placed_at. A ordem das colunas num índice composto é uma decisão de design; o guia de índices e planos de execução explica como escolhê-la.

Trate NULL como o que ele é: desconhecido

O SQL usa lógica de três valores. Qualquer comparação com NULL não é verdadeira nem falsa, e sim UNKNOWN, e o WHERE só mantém as linhas cuja condição é TRUE. Todas as surpresas abaixo vêm dessa regra:

• WHERE discount <> 0 descarta as linhas em que discount IS NULL. Se o que você quer dizer é "sem desconto ou com desconto zero", escreva WHERE discount IS NULL OR discount = 0, ou defina a coluna como NOT NULL DEFAULT 0 para que a dúvida nem exista.
• NULL = NULL é UNKNOWN, e é por isso que WHERE a.col = b.col nunca junta dois NULLs. O PostgreSQL oferece IS NOT DISTINCT FROM para quando você realmente quer que NULL seja igual a NULL.
• x NOT IN (SELECT y FROM t) não retorna absolutamente nada se algum y for NULL. Use NOT EXISTS.
• COUNT(col) ignora NULLs; COUNT(*) conta linhas. AVG(col) calcula a média apenas dos valores não nulos, o que pode ou não ser o que o relatório espera.
• O Oracle trata a string vazia '' como NULL em colunas VARCHAR2. O PostgreSQL, não. Código portado entre os dois não deve depender de nenhum desses comportamentos.

COALESCE(col, fallback) é a forma padrão e portável de substituir um valor; NVL e DECODE do Oracle e IFNULL do MySQL fazem o mesmo trabalho com pior portabilidade. Prefira expressões CASE para qualquer coisa mais complexa do que um único valor padrão.

Joins, EXISTS e IN

Use a sintaxe explícita de join ANSI (JOIN ... ON) em vez de listar as tabelas no FROM e fazer o join no WHERE. Ela separa as condições de join dos filtros, elimina a ambiguidade dos outer joins e é o que todo otimizador moderno e todo revisor de código esperam. A notação (+) do Oracle para outer joins ainda funciona, mas é legado.

Escolha o tipo de join pelo resultado que você quer, não por hábito:

• INNER JOIN quando uma linha sem correspondência deve sumir.
• LEFT JOIN quando a linha da esquerda precisa sobreviver mesmo sem correspondência. Um filtro sobre a tabela da direita no WHERE (em vez de no ON) transforma o left join de volta num inner join, porque as linhas sem correspondência têm NULL ali e não passam no filtro.
• EXISTS quando você faz uma pergunta de sim ou não sobre linhas relacionadas e não precisa das colunas delas. Ele para na primeira correspondência e nunca duplica linhas, o que um join faria se a tabela filha tivesse várias correspondências.
• IN (subquery) para conjuntos de valores pequenos e sem NULL. Os otimizadores reescrevem a maioria das subconsultas IN como semi-joins de qualquer forma, então a diferença de desempenho em relação a EXISTS costuma ser nula; a diferença semântica em torno de NULL, não.

-- Customers with at least one unpaid invoice: no duplicates, no NULL trap
SELECT c.id, c.name
FROM   customer c
WHERE  EXISTS (
  SELECT 1
  FROM   invoice i
  WHERE  i.customer_id = c.id
  AND    i.status = 'UNPAID'
);

Adicionar DISTINCT para fazer as duplicatas sumirem é um mau sinal: as duplicatas indicam que o join está multiplicando linhas, e o DISTINCT esconde a causa atrás de uma ordenação extra. Da mesma forma, UNION remove duplicatas e ordena; UNION ALL não, e é o que você quer quando os dois lados não podem se sobrepor.

Bind parameters, sempre

Concatenar a entrada do usuário no SQL é a causa da injeção de SQL, que continua entre os riscos de injeção do OWASP Top 10 décadas depois de os prepared statements terem tornado essa concatenação desnecessária. Os bind parameters (? no JDBC, $1 no protocolo do PostgreSQL, :name no Oracle e no NamedParameterJdbcTemplate do Spring) enviam o comando e os valores separadamente, de modo que um valor nunca consegue alterar a estrutura do comando.

Há também um motivo de desempenho. O Oracle guarda em cache os comandos analisados no shared pool, indexados pelo texto; mil variantes da mesma consulta cheias de literais causam mil hard parses e contenção de latches. Os prepared statements do lado do servidor do PostgreSQL e o prepareThreshold do driver JDBC trazem o mesmo benefício para consultas repetidas.

// JDBC: the value is a parameter, never part of the SQL text
try (PreparedStatement ps = conn.prepareStatement(
        "SELECT id, total FROM customer_order WHERE customer_id = ? AND placed_at >= ?")) {
    ps.setLong(1, customerId);
    ps.setObject(2, since);           // java.time.LocalDate maps to DATE
    try (ResultSet rs = ps.executeQuery()) { /* ... */ }
}

A única coisa que não dá para fazer bind é um identificador: nomes de tabelas e colunas devem vir de uma whitelist no seu código, nunca da entrada do usuário.

Paginação e ordenação

Um ORDER BY sobre um resultado grande, sem um índice que já forneça essa ordem, força uma ordenação que pode transbordar para o disco. Ordene só quando quem consome os dados precisa de ordem e, ao paginar, garanta que a ordem seja total: ORDER BY placed_at sozinho é ambíguo para linhas com o mesmo timestamp e pode mostrar a mesma linha em duas páginas. Adicione a chave primária como critério de desempate.

A paginação com OFFSET lê e descarta todas as linhas anteriores à página; a página 1.000 custa mil vezes a página 1. A paginação keyset guarda a última linha vista e pede as linhas seguintes:

-- First page
SELECT id, placed_at, total
FROM   customer_order
WHERE  customer_id = $1
ORDER  BY placed_at DESC, id DESC
LIMIT  20;

-- Next page: pass the last row's (placed_at, id) back in
SELECT id, placed_at, total
FROM   customer_order
WHERE  customer_id = $1
AND    (placed_at, id) < ($2, $3)      -- row-value comparison
ORDER  BY placed_at DESC, id DESC
LIMIT  20;

Com um índice em (customer_id, placed_at DESC, id DESC), cada página é uma varredura curta de um intervalo do índice, qualquer que seja a profundidade. O Oracle usa FETCH FIRST 20 ROWS ONLY (a partir do 12c) em vez de LIMIT; a ideia do keyset é a mesma.

Transações e lotes a partir do código da aplicação

Todo comando roda dentro de uma transação; a questão é se você controla os limites dela. O auto-commit transforma um loop de 10.000 inserts em 10.000 transações, cada uma esperando um flush do log. Envolva o lote numa única transação e use a API de batch do driver para que os comandos trafeguem em bloco:

conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO event (id, occurred_at, payload) VALUES (?, ?, ?::jsonb)")) {
    for (Event e : events) {
        ps.setObject(1, e.id());           // UUID
        ps.setObject(2, e.occurredAt());   // OffsetDateTime -> timestamptz
        ps.setString(3, e.payloadJson());
        ps.addBatch();
        if (++n % 500 == 0) ps.executeBatch();   // keep memory bounded
    }
    ps.executeBatch();
    conn.commit();
}

O driver JDBC do PostgreSQL reescreve inserts em lote como comandos INSERT de várias linhas quando reWriteBatchedInserts=true; o driver do Oracle faz batch nativamente. Em qualquer caso, mantenha as transações curtas: uma transação que fica aberta enquanto a aplicação conversa com uma API remota segura locks e, no PostgreSQL, impede o vacuum de fazer a limpeza.

Para atualizações que atingem uma grande parte de uma tabela, um único UPDATE ... WHERE baseado em conjuntos ganha de qualquer loop; para mudanças realmente enormes, considere fatiar por intervalos de chave para que cada transação continue pequena o bastante para não inchar o undo log ou o WAL.

Decisões de esquema que moldam cada consulta

Alguns problemas "de consulta" são, na verdade, problemas de modelagem. Três regras evitam a maioria deles:

1. Declare as chaves estrangeiras e indexe a coluna que faz a referência. Sem a constraint, registros órfãos vão aparecendo e o otimizador perde uma informação que poderia usar para eliminar joins. Sem o índice, cada delete na tabela pai varre a tabela filha para verificar referências.
2. Use o tipo certo. Datas em colunas DATE/TIMESTAMP, não em strings; valores monetários em NUMERIC, não em ponto flutuante; identificadores do mesmo tipo dos dois lados de cada join, para que nenhuma conversão seja necessária. Se você gera os identificadores na aplicação, colunas UUID os armazenam em 16 bytes em vez de 36 caracteres (um gerador de UUID ajuda na hora de popular dados de teste).
3. Planeje o crescimento e o expurgo. Tabelas que só crescem precisam de uma política de retenção desde o primeiro dia; apagar 100 milhões de linhas mais tarde é muito mais difícil do que descartar uma partição.

O guia de normalização aborda a parte de design em profundidade.

Erros comuns

• Função sobre a coluna indexada. WHERE UPPER(name) = 'SMITH' varre a tabela. Armazene os dados no formato em que você os busca ou crie um índice de expressão em UPPER(name).
• Filtrar o lado externo de um LEFT JOIN no WHERE. LEFT JOIN address a ON ... WHERE a.country = 'FR' vira silenciosamente um inner join. Mova a condição para a cláusula ON.
• NOT IN com uma subconsulta que aceita NULL. Não retorna nada. Use NOT EXISTS.
• Buscar tudo e filtrar no código. Um findAll() seguido de um filtro num stream faz a tabela inteira trafegar pela rede. Leve o predicado para a consulta.
• ORDER BY sem critério de desempate em consultas paginadas. Linhas se repetem ou somem entre as páginas. Adicione a chave primária.
• Hints como primeira resposta à lentidão. Um /*+ PARALLEL */ do Oracle ou um índice forçado esconde o predicado ruim que causou o problema e para de funcionar no dia em que a distribuição dos dados muda. Corrija a consulta e depois meça com o plano.
• Fazer join de views sobre views. Views genéricas arrastam colunas e joins de que a consulta não precisa, e o otimizador nem sempre consegue podá-los. Consulte as tabelas base ou escreva uma view específica.

Boas práticas

• Formate o SQL de maneira consistente e use aliases de tabela curtos e significativos (customer_order o, não t1); uma consulta é lida muito mais vezes do que é escrita.
• Prefira o SQL padrão (COALESCE, CASE, FETCH FIRST, joins ANSI) às funções proprietárias, a menos que o recurso do fornecedor traga um ganho real de desempenho.
• Examine o plano de execução de toda consulta que toca uma tabela grande antes de ela ir para produção, e de novo quando os dados crescerem dez vezes.
• Mantenha as transações curtas e com limites explícitos; nunca deixe uma aberta durante uma interação com o usuário ou uma chamada externa.
• Deixe o banco garantir a integridade: NOT NULL, CHECK, UNIQUE e chaves estrangeiras pegam bugs que a validação da aplicação deixa passar sob concorrência.
• Armazene JSON numa coluna jsonb apenas para dados realmente variáveis; colunas relacionais continuam mais rápidas de indexar, restringir e consultar. Quando fizer isso, valide os documentos com um formatador de JSON antes de depurar um insert malformado.

Principais pontos

• Busque só as colunas e linhas de que precisa, e faça filtragem, ordenação e agregação no banco.
• Um predicado é sargable quando a coluna indexada fica sozinha de um lado da comparação; funções e conversões implícitas aplicadas à coluna forçam uma varredura.
• NULL significa desconhecido: comparações com ele resultam em UNKNOWN, NOT IN com uma subconsulta que contém NULL não retorna nada e COUNT(col) o ignora.
• Use EXISTS para saber "se existe alguma linha relacionada", JOIN quando precisar das colunas relacionadas e UNION ALL, a menos que você realmente precise remover duplicatas.
• Faça bind de todo valor; a concatenação é ao mesmo tempo um risco de injeção e um veneno para o cache de parsing.
• Pagine com keysets e uma ordem total, não com OFFSET profundo.
• Agrupe as escritas em lotes dentro de transações explícitas e curtas, e indexe toda chave estrangeira.