Funcionamento de Indices de Banco de Dados

O que sao Indices

Indices sao estruturas de dados que aceleram as buscas em bancos de dados. Assim como o indice remissivo de um livro, permitem localizar rapidamente a posicao dos dados desejados.

Por que acelera: Sem indice, e necessario escanear todas as linhas (full scan), mas com indice, apenas as linhas necessarias sao acessadas.

Indice B-Tree

E a estrutura de indice mais comum. Utilizada no InnoDB do MySQL e no PostgreSQL.

Caracteristicas do B-Tree

• Estrutura de arvore balanceada
• Todos os nos folha tem a mesma profundidade
• Suporta busca por intervalo
• Mantem os dados em estado ordenado

Operacoes Adequadas para B-Tree

• Busca por correspondencia exata: WHERE id = 100
• Busca por intervalo: WHERE price BETWEEN 1000 AND 5000
• Busca por prefixo: WHERE name LIKE 'John%'
• Ordenacao: ORDER BY created_at

Indice Hash

Indice que utiliza funcao hash para calcular a posicao dos dados.

Caracteristicas do Indice Hash

• Busca por correspondencia exata extremamente rapida (O(1))
• Nao pode ser usado para busca por intervalo
• Nao pode ser usado para ordenacao

-- Criacao de indice Hash no MySQL (engine MEMORY)
CREATE TABLE sessions (
    id VARCHAR(64),
    data TEXT,
    INDEX USING HASH (id)
) ENGINE=MEMORY;

Criacao de Indices

-- Indice de coluna unica
CREATE INDEX idx_email ON users(email);

-- Indice composto
CREATE INDEX idx_name_created ON posts(user_id, created_at);

-- Indice unico
CREATE UNIQUE INDEX idx_unique_email ON users(email);

Pontos de Atencao sobre Indices

Desvantagens

• Consome espaco de armazenamento
• INSERT/UPDATE/DELETE ficam mais lentos
• Excesso de indices se torna overhead

Casos em que o Indice Nao Funciona

• Aplicacao de funcao na coluna: WHERE YEAR(created_at) = 2024
• Busca por sufixo: WHERE name LIKE '%smith'
• Condicao de negacao: WHERE status != 'deleted'
• Busca por NULL (depende do caso)

Verificacao do plano de execucao: O comando EXPLAIN permite verificar se a consulta esta utilizando o indice.

Resumo

Indices apropriados melhoram drasticamente a performance das consultas, mas criar indices indiscriminadamente pode ter efeito contrario. E importante verificar o plano de execucao e identificar os indices necessarios.