🎯 ATIVIDADE 11 — O VELOCÍMETRO DOS DADOS

Bem-vindo à décima primeira semana (4 aulas) do curso de Banco de Dados. Até aqui, trabalhamos com tabelas pequenas, onde as consultas respondem instantaneamente. Mas o que acontece quando a tabela tem 10 milhões de linhas? Sem os índices corretos, seu banco travará e a aplicação ficará lenta. Hoje, você aprenderá a criar índices e a ler o plano de execução de uma consulta para atuar como um Especialista em Performance (Tuning). 🛡️⚡

🎯 Objetivos de Aprendizagem do Laboratório

Ao final deste laboratório prático (estimativa: 4 horas presenciais / autoguiadas), você será capaz de:

• Compreender a diferença entre busca sequencial (Sequential Scan) e busca por índice (Index Scan).
• Criar e gerenciar Índices B-Tree e Hash no PostgreSQL e MySQL.
• Interpretar o plano de execução de uma consulta usando o comando EXPLAIN.
• Identificar consultas lentas e propor a indexação correta para otimização de performance.

🏢 O Cenário Prático (Seu Desafio)

O aplicativo de rastreamento de entregas da TecProExpress está enfrentando lentidão crítica. O time de suporte identificou que a consulta de busca por CPF do cliente na hora de listar as entregas está levando mais de 5 segundos para responder no banco de produção.

Como Arquiteto de Banco de Dados, você recebeu a missão de analisar as consultas de busca utilizando ferramentas de execução e aplicar índices estratégicos para reduzir o tempo de busca para menos de 5 milissegundos.

🧠 Fundamentos: A Teoria Traduzida

Buscar dados em uma tabela sem índice é como procurar uma palavra em um dicionário que não está em ordem alfabética: você precisa ler o livro inteiro, página por página (Full Table Scan / Seq Scan). Criar um índice é como criar o índice remissivo no fim do livro: você vai direto para a página certa instantaneamente.

📊 Estrutura de Busca: B-Tree vs. Hash

flowchart TD
    subgraph BTree ["Árvore B-Tree (Busca de Intervalos: >, <, >=, <=)"]
        Root["Raiz: id = 50"] --> L1["Esquerda: < 50"]
        Root --> R1["Direita: >= 50"]
        L1 --> L2["Folha: 10, 20, 30"]
        R1 --> R2["Folha: 50, 60, 70"]
    end

• B-Tree (Padrão): Organiza os dados em uma árvore balanceada. Excelente para consultas de igualdade (=) e buscas por intervalo (BETWEEN, >, <).
• Hash: Mapeia chaves diretamente para endereços físicos. Perfeito para igualdades exatas (=), mas inútil para intervalos.

📖 Exemplo Guiado: Usando o EXPLAIN

Vamos analisar como o banco de dados planeja fazer uma busca antes de criar o índice.

1. Inicializando o Banco de Dados

CREATE DATABASE tecpro_performance;
-- (Nota: No pgAdmin, abra a Query Tool apontando para o novo banco)

2. Criando Tabela e Analisando sem Índice

CREATE TABLE entrega (
    id SERIAL PRIMARY KEY,
    codigo_rastreamento VARCHAR(50),
    data_envio DATE
);

-- Analisando a busca por código de rastreamento antes do índice
EXPLAIN ANALYZE 
SELECT * FROM entrega WHERE codigo_rastreamento = 'TRK1002938';

Saída Esperada no pgAdmin (Console): Seq Scan on entrega (cost=0.00..38.25 rows=1 width=58) (actual time=0.045..0.082 rows=0 loops=1) (Nota: O termo Seq Scan indica que o banco leu a tabela inteira do início ao fim para tentar achar o registro).

🛠️ Prática Obrigatória 1: Análise e Indexação

1. Crie a tabela cliente_historico com as colunas: id (PK), nome, cpf (sem chave única física inicial) e email.
2. Popule a tabela com alguns registros fictícios.
3. Execute o comando EXPLAIN para analisar a busca por cpf:

   EXPLAIN SELECT * FROM cliente_historico WHERE cpf = '12345678901';
   

4. Crie um índice B-Tree na coluna cpf e execute novamente o EXPLAIN. Veja a diferença.

💻 Execução do EXPLAIN ANALYZE & Comparação no Terminal

Para verificar o ganho de performance após a criação do índice B-Tree no PostgreSQL:

-- 1. Antes do Índice (Seq Scan)
EXPLAIN ANALYZE SELECT * FROM cliente_historico WHERE cpf = '12345678901';

-- 2. Criando o Índice B-Tree
CREATE INDEX idx_cliente_cpf ON cliente_historico USING btree(cpf);

-- 3. Depois do Índice (Index Scan)
EXPLAIN ANALYZE SELECT * FROM cliente_historico WHERE cpf = '12345678901';

🖥️ Saída Esperada no Terminal (Antes vs Depois):

-- ANTES DO ÍNDICE:
Seq Scan on cliente_historico  (cost=0.00..1840.00 rows=1 width=58) (actual time=14.820..14.825 rows=1 loops=1)
Planning Time: 0.120 ms
Execution Time: 14.860 ms

-- DEPOIS DO ÍNDICE B-TREE:
Index Scan using idx_cliente_cpf on cliente_historico  (cost=0.28..8.30 rows=1 width=58) (actual time=0.035..0.037 rows=1 loops=1)
Planning Time: 0.145 ms
Execution Time: 0.058 ms

🌐 Exemplo de Payload JSON para Telemetria de Índices (Swagger /docs)

{
  "tabela": "cliente_historico",
  "coluna_indexada": "cpf",
  "tipo_indice": "BTREE",
  "tempo_execucao_ms_antes": 14.86,
  "tempo_execucao_ms_depois": 0.058,
  "reducao_tempo_percentual": 99.6
}

🛠️ Prática Obrigatória 2: B-Tree vs. Hash (Performance)

1. Crie um índice do tipo Hash na coluna email da tabela cliente_historico.
2. Crie um índice do tipo B-Tree na coluna id (gerado automaticamente pela PK).
3. Escreva o script SQL de criação dos dois índices (PostgreSQL e MySQL equivalentes).

📤 Instruções de Entrega (Microsoft Teams)

Após validar a performance das suas consultas locais:

1. Salve o script SQL contendo a criação da tabela, comandos de EXPLAIN e a criação dos índices em um arquivo .sql (Ex: Atividade_11_SeuNome.sql).
2. Adicione comentários no código explicando o que significa o custo (cost) retornado pelo EXPLAIN e quando se deve preferir um índice B-Tree sobre um Hash.
3. Envie o arquivo .sql no Microsoft Teams.

💡 Checkpoint de Lógica

🔥 Desafio de Fixação (Opcional)

Nível: Especialista em Performance 🏆

O que é um Covering Index (ou Índice Composto)? Como você criaria um único índice para otimizar uma busca que filtra por cidade E por status ao mesmo tempo?

🔑 Gabarito de Código/Fórmulas Completo

🐘 Padrão PostgreSQL (pgAdmin)

-- 1. Criação da Tabela
CREATE TABLE cliente_historico (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    cpf VARCHAR(11),
    email VARCHAR(100)
);

-- 2. Criação do Índice B-Tree (Foco em buscas por igualdade e ordenações)
CREATE INDEX idx_cli_cpf ON cliente_historico(cpf);

-- 3. Criação do Índice Hash (Exclusivo para igualdade exata)
CREATE INDEX idx_cli_email_hash ON cliente_historico USING HASH (email);

-- 4. Verificação de Execução
EXPLAIN ANALYZE 
SELECT * FROM cliente_historico WHERE cpf = '12345678901';

🐬 Comparativo MySQL (Workbench)

-- No MySQL, o índice Hash não é suportado no motor padrão InnoDB (apenas B-Tree)
CREATE INDEX idx_cli_cpf ON cliente_historico(cpf);
CREATE INDEX idx_cli_email ON cliente_historico(email);

🔍 Explicação do Gabarito:

• idx_cli_cpf: Índice B-Tree criado para buscas por CPF.
• USING HASH: Sintaxe do PostgreSQL para criar índices usando hashing direto. Muito rápido para buscas exatas por e-mail, mas não suporta operadores como >, <, LIKE ou ORDER BY.
• EXPLAIN ANALYZE: Mostra o planejamento do otimizador e executa a query, trazendo o tempo real de CPU em milissegundos.

📊 Rubrica Formativa de Avaliação