MODULO 2.1

⚡ Indices e Transacoes

Domine indices para acelerar buscas, transacoes ACID para garantir consistencia e boas praticas SQL para queries profissionais.

6
Topicos
45
Minutos
Inter
Nivel
Pratico
Tipo
1

📈 Por que Indices

Um indice e uma estrutura auxiliar que acelera consultas evitando o full table scan. Sem indice, toda busca varre a tabela inteira - e a performance cai exponencialmente conforme os dados crescem.

🎯 Analogia: Indice de um Livro

Imagine procurar um assunto em um livro de 500 paginas sem indice - voce leria pagina por pagina. Com o indice, vai direto ao capitulo certo. Um indice de banco de dados funciona exatamente assim.

  • • B-tree: Estrutura balanceada, ideal para ranges e ordenacao (padrao na maioria dos SGBDs)
  • • Hash: Busca exata por igualdade, O(1) mas sem suporte a ranges
  • • Indice cobridor: Contem todas as colunas da query, evita acesso a tabela
  • • Seletividade: Quanto mais valores unicos, melhor o indice funciona

📊 Impacto Real

  • Tabela com 1 milhao de linhas: full scan ~500ms, index scan ~2ms
  • Cada indice consome espaco em disco e desacelera escritas (INSERT/UPDATE/DELETE)
  • Indice em coluna com baixa seletividade (ex: boolean) geralmente nao ajuda
  • PostgreSQL cria indices automaticos para PRIMARY KEY e UNIQUE
2

🔧 CREATE INDEX na pratica

Criar um indice e simples. Criar o indice certo exige entender a query, os dados e o trade-off entre leitura e escrita. Indice errado e pior que nenhum - consome espaco e lentifica escritas sem beneficio.

💻 Exemplos SQL

-- Indice simples: acelera buscas por email
CREATE INDEX idx_cliente_email ON cliente (email);

-- Indice composto: otimiza queries com WHERE cidade AND estado
CREATE INDEX idx_endereco_cidade_estado ON endereco (cidade, estado);

-- Indice parcial: apenas registros ativos (economia de espaco)
CREATE INDEX idx_pedido_ativo ON pedido (data_criacao)
WHERE status = 'ativo';

-- Indice com INCLUDE: colunas extras sem afetar a arvore
CREATE INDEX idx_produto_categoria ON produto (categoria_id)
INCLUDE (nome, preco);

-- Indice unico: garante unicidade + acelera buscas
CREATE UNIQUE INDEX idx_usuario_cpf ON usuario (cpf);

✅ FACA

  • • Indexe colunas usadas em WHERE, JOIN e ORDER BY
  • • Prefira indices compostos para queries multi-coluna
  • • Use indice parcial para filtrar subconjuntos
  • • Monitore indices nao utilizados e remova-os

❌ NAO FACA

  • • Indexar colunas de baixa seletividade (boolean, status)
  • • Criar indice para cada coluna sem analise
  • • Ignorar o custo de escrita em tabelas com muitos INSERTs
  • • Esquecer de REINDEX apos grandes cargas de dados
3

🔬 EXPLAIN e EXPLAIN ANALYZE

EXPLAIN mostra o plano de execucao que o banco vai usar. EXPLAIN ANALYZE executa a query e mostra tempos reais. Sem essas ferramentas, voce otimiza no escuro. E o raio-X da performance.

💻 Exemplos de Uso

-- Ver plano estimado (nao executa a query)
EXPLAIN SELECT * FROM cliente WHERE email = 'ana@example.com';

-- Ver plano + tempo real (executa a query)
EXPLAIN ANALYZE SELECT * FROM cliente WHERE email = 'ana@example.com';

-- Exemplo de saida:
-- Index Scan using idx_cliente_email on cliente
--   (cost=0.29..8.31 rows=1 width=64)
--   (actual time=0.027..0.028 rows=1 loops=1)
-- Planning Time: 0.152 ms
-- Execution Time: 0.065 ms

🔍 Operadores do Plano de Execucao

Seq Scan

Varredura sequencial da tabela inteira. Sinal de que falta indice.

Index Scan

Usa o indice para localizar registros. O que queremos ver.

Nested Loop

JOIN linha a linha. Eficiente para poucos registros.

Hash Join

Constroi hash table na memoria. Eficiente para muitos registros.

💡 Dica Pratica

Se EXPLAIN mostra Seq Scan em uma tabela grande com filtro WHERE, provavelmente falta um indice nessa coluna. Crie o indice e rode EXPLAIN novamente para confirmar que mudou para Index Scan.

4

🔒 Transacoes ACID

Uma transacao e um conjunto de operacoes que executa como unidade atomica: tudo ou nada. Se qualquer operacao falhar, todas sao revertidas. Transferencia bancaria sem transacao pode perder dinheiro. ACID e fundamental.

💻 Transacao na Pratica

-- Transferencia bancaria atomica
BEGIN;
  UPDATE conta SET saldo = saldo - 500.00 WHERE id = 1;
  UPDATE conta SET saldo = saldo + 500.00 WHERE id = 2;

  -- Verifica se saldo ficou negativo
  -- Se sim, desfaz tudo
  DO $$
  BEGIN
    IF (SELECT saldo FROM conta WHERE id = 1) < 0 THEN
      RAISE EXCEPTION 'Saldo insuficiente';
    END IF;
  END $$;
COMMIT;

-- Se algo der errado, ROLLBACK desfaz tudo
-- ROLLBACK;

🎯 As 4 Propriedades ACID

A - Atomicidade

Tudo ou nada. Se uma operacao falha, todas sao revertidas.

C - Consistencia

O banco vai de um estado valido para outro estado valido. Constraints sao respeitadas.

I - Isolamento

Transacoes concorrentes nao interferem entre si. Cada uma ve um snapshot consistente.

D - Durabilidade

Apos COMMIT, os dados persistem mesmo com crash do servidor. WAL garante isso.

5

🔄 Niveis de Isolamento

O nivel de isolamento define o que uma transacao ve das mudancas feitas por outras transacoes concorrentes. Isolamento errado causa dirty reads, phantom reads e dados inconsistentes.

💻 Configurando Isolamento

-- Definir nivel para a transacao atual
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT * FROM estoque WHERE produto_id = 42;
  -- ... operacoes ...
COMMIT;

-- Definir nivel padrao da sessao
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Verificar nivel atual
SHOW transaction_isolation;

📊 Comparativo dos Niveis

Nivel Dirty Read Non-Repeatable Phantom Uso
READ UNCOMMITTED Sim Sim Sim Quase nunca
READ COMMITTED Nao Sim Sim Padrao PG
REPEATABLE READ Nao Nao Sim Relatorios
SERIALIZABLE Nao Nao Nao Critico

💡 Dica Pratica

Para a maioria das aplicacoes, READ COMMITTED (padrao do PostgreSQL) e suficiente. Use REPEATABLE READ para relatorios que precisam de snapshot consistente. SERIALIZABLE somente para operacoes financeiras criticas onde consistencia total e obrigatoria.

6

📋 Boas Praticas SQL

SQL mal escrito e divida tecnica. Padroes e habitos para queries seguras, performaticas e manteniveis. Boa pratica hoje evita incidente amanha.

✅ FACA

  • • Use prepared statements para evitar SQL injection
  • • Selecione apenas as colunas necessarias (nunca SELECT *)
  • • Use parametros em vez de concatenar valores
  • • Monitore queries lentas com pg_stat_statements
  • • Mantenha transacoes curtas e focadas

❌ NAO FACA

  • • SELECT * em producao (transfere dados desnecessarios)
  • • Concatenar input do usuario direto na query
  • • Transacoes longas que travam locks por minutos
  • • Ignorar EXPLAIN em queries que rodam em producao
  • • Criar indice sem testar o impacto em escritas

💻 Exemplos de Boas Praticas

-- RUIM: SELECT * e concatenacao
SELECT * FROM usuario WHERE nome = '" + input + "';

-- BOM: colunas especificas e parametros
SELECT id, nome, email FROM usuario WHERE nome = $1;

-- RUIM: transacao longa com logica de negocio
BEGIN;
  SELECT * FROM pedido WHERE id = 1;
  -- ... 30 segundos de processamento em app ...
  UPDATE pedido SET status = 'aprovado' WHERE id = 1;
COMMIT;

-- BOM: transacao curta e focada
-- Faz o processamento ANTES de abrir a transacao
BEGIN;
  UPDATE pedido SET status = 'aprovado' WHERE id = 1;
  INSERT INTO log_auditoria (pedido_id, acao) VALUES (1, 'aprovado');
COMMIT;

🛡️ Checklist de Seguranca SQL

  • • Prepared statements: Sempre usar para queries com input externo
  • • Principio do menor privilegio: Cada app conecta com usuario de banco com permissoes minimas
  • • Monitoramento: pg_stat_statements + alertas para queries acima de X ms
  • • Revisao de queries: EXPLAIN em toda query nova antes de ir para producao
← Voltar para Trilha 2 Modulo 2.2 - Tipos de SGBDs →