DEV Community

Isaias Velasquez
Isaias Velasquez

Posted on

How to criar indices no SQL que realmente fazem diferenca

#database #performance #sql #tutorial

Ter um indice certo pode fazer uma query ir de 10 segundos para milissegundos. Mas indices demais ou errados atrapalham mais que ajudam.

O comando basico para criar um indice:

CREATE INDEX idx_clientes_email ON clientes(email);
Enter fullscreen mode Exit fullscreen mode

Isso acelera buscas por email. Sem o indice, o banco faz uma varredura completa na tabela.

Para indices compostos (mais de uma coluna):

CREATE INDEX idx_pedidos_cliente_data ON pedidos(cliente_id, data_criacao);
Enter fullscreen mode Exit fullscreen mode

A ordem das colunas importa. Esse indice ajuda queries que filtram por cliente_id ou por cliente_id + data_criacao. Mas nao ajuda queries que filtram so por data_criacao.

Para ver se uma query esta usando indice, use EXPLAIN:

EXPLAIN ANALYZE SELECT * FROM clientes WHERE email = 'joao@email.com';
Enter fullscreen mode Exit fullscreen mode

O resultado mostra se o banco fez um Index Scan (bom) ou Seq Scan (ruim).

Indices parciais sao otimos para filtrar um subconjunto especifico:

CREATE INDEX idx_pedidos_pendentes ON pedidos(status)
WHERE status = 'pendente';
Enter fullscreen mode Exit fullscreen mode

Se voce sempre busca pedidos pendentes, esse indice e muito menor que um indice completo.

Indices unicos garantem que nao ha valores duplicados:

CREATE UNIQUE INDEX idx_cpf_unico ON clientes(cpf);
Enter fullscreen mode Exit fullscreen mode

Isso e equivalente a uma constraint UNIQUE, mas explicito como indice.

Para buscas de texto parcial (LIKE), use indices com varchar_pattern_ops:

CREATE INDEX idx_clientes_nome ON clientes(nome varchar_pattern_ops);
Enter fullscreen mode Exit fullscreen mode

Isso acelera consultas como WHERE nome LIKE 'Silva%'.

Indices em tabelas grandes tem custo: eles desaceleram INSERT, UPDATE e DELETE. Cada vez que voce insere uma linha, o banco atualiza todos os indices.

Para monitorar indices nao usados:

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

Indices com idx_scan baixo sao candidatos a remocao.

Uma dica pratica: indices em colunas com baixa cardinalidade (como status com 3 valores possiveis) raramente ajudam. O banco prefere ler a tabela toda em vez de usar o indice.

Para colunas booleanas ou de poucos valores, indices parciais sao a melhor opcao.

That's all for now.
Thanks for reading!

Top comments (0)