Por que utilizar índices?

Em sistemas de aplicação típicos, a proporção entre leituras e escritas gira em torno de 10:1. Operações de inserção e atualização raramente apresentam problemas de desempenho. O maior gargalo reside em consultas complexas. A indexação é a técnica mais eficaz para acelerar consultas, podendo melhorar a performance em várias ordens de magnitude.

Um índice no MySQL, também chamado de "chave", é uma estrutura de dados que permite ao motor de armazenamento localizar registros rapidamente. Pense nele como o índice remissivo de um livro: em vez de folhear centenas de páginas sequencialmente, você consulta diretamente a página correta.

Princípio Fundamental dos Índices

A essência da indexação consiste em reduzir progressivamente o universo de dados até encontrar o resultado desejado, transformando buscas aleatórias em buscas sequenciais. Entretanto, bancos de dados enfrentam cenários mais complexos que um simples dicionário: consultas de igualdade, intervalos (>, <, BETWEEN, IN), buscas por padrão (LIKE) e operações de união (OR).

Entrada/Saída em Disco e Pré-leitura

A leitura de dados em disco envolve três componentes de tempo: tempo de posicionamento (seek), atraso de rotação e tempo de transferência. Um disco de 7200 RPM apresenta um tempo médio de acesso de aproximadamente 9ms. Considerando que uma máquina de 500 MIPS pode executar 500 milhões de instruções por segundo, uma única operação de I/O equivale ao tempo de executar cerca de 4,5 milhões de instruções.

Para mitigar esse custo, os sistemas operacionais implementam a pré-leitura: ao acessar um endereço, os dados adjacentes também são carregados no buffer de memória. Cada unidade de dados lida é chamada de página (tipicamente 4KB ou 8KB). Esse princípio é fundamental para o design das estruturas de índice.

Estrutura de Dados do Índice: B+ Tree

A estrutura ideal para índices de banco de dados deve garantir que o número de operações de I/O permaneça em uma escala pequena e constante. A árvore B+ (evolução da árvore binária de busca → árvore AVL balanceada → árvore B → árvore B+) atende a esse requisito.

Em uma árvore B+ de 3 níveis, é possível representar milhões de registros. Encontrar qualquer registro requer apenas 3 operações de I/O — comparado a milhões de operações sem índice.

Propriedades Essenciais da B+ Tree

1. Campos de índice compactos: A altura h da árvore depende de h = ⌈log(m+1) N⌉, onde N é o número de registros e m é o número de chaves por bloco de disco. Quanto menor o tamanho de cada chave, mais chaves cabem por bloco e menor a altura da árvore. Por isso, tipos como INT (4 bytes) são preferíveis a BIGINT (8 bytes).

2. Regra do prefixo mais à esquerda: Quando o índice é composto por múltiplas colunas — por exemplo, (sobrenome, idade, cidade) — a árvore B+ segue a ordem esquerda-direita para estabelecer a busca. A coluna mais à esquerda deve estar presente na consulta para que o índice seja utilizado.

Índices Clustered vs. Secundários

Índice Clustered (Primário)

No InnoDB, os dados da tabela são organizados fisicamente de acordo com a chave primária. O índice clustered é uma árvore B+ cujos nós folha contêm os registros completos. Cada tabela possui exatamente um índice clustered.

Se nenhuma chave primária for definida, o MySQL procura o primeiro índice UNIQUE NOT NULL. Caso não exista, o InnoDB gera internamente um identificador oculto de 6 bytes.

Vantagens do índice clustered:

• Buscas ordenadas pela chave primária são extremamente rápidas
• Consultas por intervalo na chave primária aproveitam a ordenação física dos dados

Exemplo demonstrando a melhoria com índice clustered:

-- Criação da tabela de teste
CREATE TABLE clientes (
    cliente_id INT,
    nome_completo VARCHAR(30),
    telefone VARCHAR(15),
    email VARCHAR(60)
);

-- Inserção de dados de teste (procedimento armazenado)
DELIMITER $$
CREATE PROCEDURE popular_clientes()
BEGIN
    DECLARE contador INT DEFAULT 1;
    WHILE contador < 2000000 DO
        INSERT INTO clientes VALUES (
            contador,
            CONCAT('usuario', contador),
            CONCAT('119', LPAD(contador, 8, '0')),
            CONCAT('user', contador, '@exemplo.com')
        );
        SET contador = contador + 1;
    END WHILE;
END$$
DELIMITER ;

CALL popular_clientes();

-- Sem chave primária: full table scan
EXPLAIN SELECT * FROM clientes ORDER BY cliente_id DESC LIMIT 15;
-- type: ALL, Extra: Using filesort

-- Adicionando chave primária (cria índice clustered)
ALTER TABLE clientes ADD PRIMARY KEY(cliente_id);

-- Com chave primária: scan direto no índice
EXPLAIN SELECT * FROM clientes ORDER BY cliente_id DESC LIMIT 15;
-- type: index, Extra: NULL

Índices Secundários (Auxiliares)

Todos os demais índices além do clustered são índices secundários. Seus nós folha não contêm os dados completos do registro — apenas os valores da chave do índice e um bookmark (o valor da chave primária correspondente). Para encontrar um registro completo via índice secundário, o InnoDB primeiro percorre o índice secundário para obter a chave primária e depois percorre o índice clustered.

Se ambos os índices possuírem altura 3, uma busca via índice secundário pode requerer até 6 operações de I/O lógicas.

Gerenciamento de Índices no MySQL

Tipos de Índice Disponíveis

-- Índice simples: acelera buscas
-- Índice UNIQUE: acelera buscas + garante unicidade
-- Chave primária: acelera buscas + garante unicidade + NOT NULL
-- Índice FULLTEXT: otimizado para buscas em texto extenso

-- Exemplo: criação com múltiplos tipos de índice
CREATE TABLE funcionarios (
    cpf VARCHAR(11),
    nome VARCHAR(50),
    departamento VARCHAR(30),
    observacoes TEXT,
    PRIMARY KEY(cpf),
    UNIQUE KEY uk_nome(nome),
    INDEX idx_dept(departamento)
);

-- Criar índice em tabela existente
CREATE INDEX idx_dept ON funcionarios(departamento);

-- Alternativa via ALTER TABLE
ALTER TABLE funcionarios ADD INDEX idx_dept(departamento);

-- Remover índice
DROP INDEX idx_dept ON funcionarios;

Tipos de Motor de Índice

• B+ Tree (BTree): Estrutura padrão do InnoDB, eficiente para buscas de igualdade e intervalos
• Hash: Rápido para busca individual, mas ineficiente para intervalos. Suportado pelo motor Memory

Validando a Efetividade do Índice

Após inserir uma grande volume de dados, a criação de um índice será lenta e consumirá espaço em disco. Porém, as consultas subsequentes que utilizam esse índice serão significativamente mais rápidas.

-- Consulta lenta sem índice (~0.3s para tabela com milhões de registros)
SELECT * FROM clientes WHERE cliente_id = 1500000;

-- Criar o índice
ALTER TABLE clientes ADD PRIMARY KEY(cliente_id);

-- Consulta rápida com índice (~0.00s)
SELECT * FROM clientes WHERE cliente_id = 1500000;

No InnoDB, os índices são armazenados no arquivo .ibd da tabela (arquivo de dados organizado em B+ Tree). No MyISAM, os índices possuem arquivo separado (.MYI).

Práticas Corretas de Utilização de Índices

Causas Comuns de Não Utilização do Índice

1. Operadores de intervalo e comparação não igualitária: Condições envolvendo >, <, !=, BETWEEN, LIKE '%...' podem impedir a utilização completa do índice.

2. Baixa seletividade do campo indexado: A seletividade é calculada por COUNT(DISTINCT col) / COUNT(*). Campos com valores muito repetitivos (como gênero ou status booleano) geram índices pouco eficientes. Idealmente, a seletividade deve ser superior a 0.1 para campos usados em JOIN.

-- Exemplo: campo 'ativo' com apenas 2 valores possíveis
-- Seletividade ≈ 0.000001 → índice ineficiente
-- Melhor estratégia: combinar com outros campos em índice composto
CREATE INDEX idx_status_nome ON clientes(ativo, nome_completo);

3. Índice composto e otimização de consulta: Para uma condição como a = 1 AND b = 'xyz' AND c = 3, a ordem dos campos no índice (a, b, c) pode variar — o otimizador do MySQL reorganiza conforme necessário.

4. Colunas indexadas devem estar "limpas": Evite aplicar funções nas colunas indexadas. Em vez de:

-- ERRADO: não utiliza índice
SELECT * FROM pedidos WHERE YEAR(data_pedido) = 2024;

-- CORRETO: utiliza índice
SELECT * FROM pedidos WHERE data_pedido BETWEEN '2024-01-01' AND '2024-12-31';

5. Comportamento com AND/OR:

• Com AND: o otimizador escolhe o campo com maior seletividade para iniciar a busca no índice composto
• Com OR: cada condição é avaliada na ordem em que aparece

6. Regra do prefixo à esquerda (Leftmost Prefix): Para um índice (col_a, col_b, col_c, col_d), a consulta WHERE col_a = 1 AND col_b = 2 AND col_c > 3 AND col_d = 4 utiliza o índice até col_c, mas não para col_d (o intervalo interrompe a correspondência). Reordenar para (col_a, col_b, col_d, col_c) permite usar todos os campos.

7. Outras situações que impedem o uso do índice:

-- Uso de funções sobre a coluna
SELECT * FROM usuarios WHERE REVERSE(email) = 'natseuq';

-- Incompatibilidade de tipos
SELECT * FROM usuarios WHERE cpf = 123456789;  -- cpf é VARCHAR

Recomendações Adicionais

• Evite SELECT * — selecione apenas as colunas necessárias
• Prefira CHAR a VARCHAR para campos de tamanho fixo
• Combine múltiplos índices de coluna única em um índice composto quando possível
• Utilize índices compactos (prefixo) quando aplicável
• Prefira JOIN a subconsultas quando viável
• Garanta compatibilidade de tipos em junções

Índices Compostos e Índice de Cobertura

Índice Composto

Um índice composto abrange múltiplas colunas. A árvore B+ resultante ordena os dados pela combinação das colunas, da esquerda para a direita.

CREATE TABLE transacoes (
    conta_id INT,
    data_transacao DATETIME,
    valor DECIMAL(12,2),
    PRIMARY KEY(conta_id),
    INDEX idx_conta_data(conta_id, data_transacao)
);

Uma vantagem importante do índice composto é a ordenação secundária automática: para registros com o mesmo valor da primeira coluna, a segunda coluna já está ordenada. Isso elimina a necessidade de ordenação adicional em consultas como:

-- Busca os últimos 5 registros de uma conta específica
-- Sem necessidade de filesort graças à ordenação por data_transacao no índice
SELECT * FROM transacoes 
WHERE conta_id = 42 
ORDER BY data_transacao DESC 
LIMIT 5;

Índice de Cobertura (Covering Index)

O índice de cobertura ocorre quando todas as colunas solicitadas na consulta estão presentes no índice, eliminando a necessidade de acessar os dados completos no índice clustered. Isso reduz drasticamente as operações de I/O.

-- Sem cobertura: precisa acessar o índice clustered para obter 'nome'
EXPLAIN SELECT nome FROM clientes WHERE cliente_id = 1000;
-- Extra: NULL (busca no clustered index necessária)

-- Com cobertura: todas as informações já estão no índice
EXPLAIN SELECT cliente_id FROM clientes WHERE cliente_id = 1000;
-- Extra: Using index (acesso exclusivo ao índice secundário)

Para operações de agregação, o otimizador pode optar por um índice secundário menor ao invés do clustered:

-- O otimizador escolhe o índice secundário (menor) para a contagem
EXPLAIN SELECT COUNT(*) FROM transacoes;
-- type: index, Extra: Using index

Utilizando EXPLAIN para Análise de Consultas

O comando EXPLAIN revela o plano de execução escolhido pelo otimizador. O campo rows é o indicador mais importante — valores menores geralmente indicam consultas mais rápidas.

A hierarquia de acesso (do menos eficiente ao mais eficiente):

ALL < index < range < index_merge < ref_or_null < ref < eq_ref < const/system

-- Consulta ineficiente (full table scan)
EXPLAIN SELECT * FROM clientes WHERE nome_completo = 'joao silva';
-- type: ALL

-- Consulta eficiente (busca por índice)
EXPLAIN SELECT * FROM clientes WHERE cliente_id = 500;
-- type: const

Etapas para Otimização de Consultas Lentas

1. Confirmar que a consulta realmente é lenta (utilize SQL_NO_CACHE para descartar cache)
2. Isolar a tabela com menor resultado no WHERE — analisar cada campo individualmente para identificar o de maior seletividade
3. Executar EXPLAIN e comparar com a expectativa
4. Para consultas com ORDER BY ... LIMIT, garantir que a tabela de ordenação seja acessada primeiro
5. Compreender o cenário de uso da aplicação
6. Aplicar índices seguindo as boas práticas descritas
7. Validar os resultados — se insatisfatório, retornar à etapa 1

Gerenciamento de Logs de Consultas Lentas

O log de consultas lentas registra operações que excedem um tempo limite configurado e que não utilizam índices.

Configuração via Variáveis de Sessão

-- Verificar configurações atuais
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Ativar o log de consultas lentas
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Configuração via Arquivo de Configuração

# Em my.cnf ou my.ini
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-queries.log
long_query_time = 2

Após modificar o arquivo de configuração, é necessário reiniciar o serviço MySQL.

Principais Tipos de Log no MySQL

Tipo de Log	Descrição
Erro	Registra falhas na inicialização e operação do servidor
Binário (binlog)	Registra todas as operações que modificam dados (exceto SELECT)
Consulta lenta	Registra operações que excedem o tempo limite definido
Consulta geral	Registra todas as consultas executadas (para auditoria)
Relay	Armazena eventos replicados do servidor mestre no escravo
Transação (redo)	Registra operações de transações InnoDB para recuperação

Gerenciamento do Log Binário

-- Ativar no my.cnf
-- [mysqld]
-- log-bin = /var/log/mysql/mysql-bin

-- Visualizar eventos do log binário
-- mysqlbinlog mysql-bin.000003

-- Filtrar por período
-- mysqlbinlog mysql-bin.000003 --start-datetime="2024-06-01 00:00:00" --stop-datetime="2024-06-02 00:00:00"

-- Forçar rotação do log (gera novo arquivo)
FLUSH LOGS;

-- Remover logs binários antigos
PURGE BINARY LOGS BEFORE '2024-05-01';