Aula 17 - Otimização de Consultas SQL e Índices B-Tree ⚡

📑 1. Fundamentos Teóricos & Análise Técnica

A performance de sistemas de bancos de dados relacionais em produção depende criticamente da capacidade do Otimizador Baseado em Custos (Cost-Based Optimizer - CBO) de escolher o melhor caminho de acesso aos dados.

O Índice B-Tree (Balanced Tree) é a estrutura de dados canônica para indexação relacional: 1. Estrutura Balanceada: Árvore com alto fator de ramificação (fan-out) onde todas as folhas residem exatamente na mesma profundidade, garantindo tempo de busca \(O(\log n)\) para operadores de igualdade (=) e intervalo (BETWEEN, <, >). 2. Sequential Scan vs. Index Scan: Uma consulta sem índice apropriado obriga o motor a ler todas as páginas de disco da tabela (Seq Scan). Com o índice B-Tree, o motor percorre poucos nós intermediários e acessa diretamente o ponteiro físico da linha (TID / Row ID). 3. Covering Indexes (Índices de Cobertura): Quando a cláusula SELECT solicita apenas colunas contidas na chave do índice ou na cláusula INCLUDE (...), o motor realiza um Index Only Scan, dispensando completamente o acesso à tabela física (Heap Fetch).

📐 Arquitetura Conceitual & Diagrama de Fluxo

graph TD
    Query["SELECT id, email FROM users WHERE email = 'dev@empresa.com'"] --> Parser["Otimizador CBO (Explain Analyze)"]
    Parser --> BTree["Índice B-Tree (idx_users_email)"]
    BTree --> Root["Nó Raiz"]
    Root --> Branch["Nós Intermediários"]
    Branch --> Leaf["Nó Folha (TID: Página 42, Slot 5)"]
    Leaf --> FastRead["Index Only Scan: Resposta em < 1ms (Zero Seq Scan)"]
    style Query fill:#e1f5fe,stroke:#01579b
    style Parser fill:#fff3e0,stroke:#e65100
    style BTree fill:#e8f5e9,stroke:#2e7d32
    style FastRead fill:#f3e5f5,stroke:#7b1fa2

🔍 Pilares e Diretrizes Técnicas

Nesta unidade, aprofundamos os seguintes conceitos fundamentais: - Plano EXPLAIN ANALYZE: Inspeção do custo estimado versus tempo real de execução e páginas de buffer lidas em cache (Shared Hit). - Regra do Prefixo Mais à Esquerda: Em índices compostos (colA, colB), o índice só pode ser utilizado se colA estiver presente no filtro. - Custo de Manutenção de Índices: Cada índice adicional penaliza operações de INSERT, UPDATE e DELETE. - Estatísticas Atualizadas (ANALYZE): Garantia de que o histograma de distribuição de dados do planejador esteja atualizado.

🛠️ 2. Implementação Prática em Bancos de Dados Relacionais e Tuning SQL

Abaixo está a implementação técnica de referência, estruturada com padrões de engenharia de software e foco em robustez:

// query_tuning.sql (Criação de Índice Composto e Análise de Plano)
-- 1. Criação de Índice Composto com Colunas Incluídas (Covering Index)
CREATE INDEX idx_orders_customer_date 
ON orders (customer_id, created_at DESC) 
INCLUDE (total_amount, status);

-- 2. Consulta Otimizada que usará Index Only Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, created_at, total_amount, status
FROM orders
WHERE customer_id = 42 
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 10;

💡 Análise Passo a Passo do Código

1. INCLUDE Clause: Armazena total_amount e status apenas nas folhas do índice sem compor a chave de ordenação.
2. Index Only Scan Garantido: O banco satisfaz a query inteira lendo apenas as páginas do índice sem tocar na tabela principal.
3. EXPLAIN com BUFFERS: Exibe a quantidade exata de blocos lidos do cache de memória RAM (Shared Hit Blocks).

🎯 3. Próximos Passos & Sequência Didática