Dominando a Indexação MySQL para Desempenho de Consultas Mais Rápido

A indexação MySQL é geralmente o primeiro lugar que procuro quando um banco de dados parece lento, mas também é um dos lugares mais fáceis de cometer um erro confiante. Um índice pode transformar uma varredura de tabela em uma consulta rápida. Também pode desacelerar gravações, desperdiçar memória e dar uma falsa sensação de progresso se o planejador de consultas nunca o usar.

A questão prática não é "Esta coluna deve ter um índice?" A melhor pergunta é: "Qual consulta estou tentando tornar mais barata e como provarei que o índice ajudou?" Mantenha essa pergunta em mente enquanto lê. Uma boa indexação começa com consultas reais, não com uma lista de colunas que parecem importantes.

O que é um Índice MySQL?

Um índice MySQL é uma estrutura de dados que melhora a velocidade das operações de recuperação de dados em uma tabela de banco de dados. Pense nele como um índice em um livro: em vez de ler o livro inteiro para encontrar um tópico específico, você pode procurar o tópico no índice, que lhe dirá o número exato da página. Da mesma forma, um índice de banco de dados permite que o MySQL localize rapidamente as linhas que correspondem a uma condição de consulta específica sem examinar a tabela inteira.

Ao consultar uma tabela, o MySQL pode usar um índice para encontrar as linhas relevantes muito mais rápido do que poderia examinando cada linha. Isso é especialmente benéfico para tabelas com um grande número de linhas ou para consultas que envolvem filtragem (cláusulas WHERE), junção de tabelas (cláusulas JOIN) ou ordenação (cláusulas ORDER BY).

Como os Índices Funcionam

O MySQL normalmente usa índices B-tree para índices InnoDB normais. Uma B-tree mantém as chaves em ordem classificada, e é por isso que funciona bem para consultas de igualdade, intervalos, varreduras ordenadas e muitas junções. Quando você cria um índice em uma ou mais colunas, o MySQL constrói uma estrutura onde:

• Nós folha contêm os ponteiros de dados reais ou, no caso de índices clusterizados (como a chave primária do InnoDB), as próprias linhas de dados.
• Nós internos contêm chaves que ajudam a navegar pela árvore para encontrar o nó folha correto.

Quando uma consulta pode usar o lado esquerdo desse índice, o MySQL pode navegar para uma parte estreita da árvore em vez de ler a tabela inteira. Essa é a verdadeira vantagem. O índice não torna todas as consultas rápidas; ele torna certos padrões de acesso mais baratos.

Tipos de Índices MySQL

O MySQL suporta vários tipos de índices, cada um com seus próprios pontos fortes e casos de uso.

1. PRIMARY KEY

• Uma restrição PRIMARY KEY garante que cada valor na(s) coluna(s) seja único e não NULL. É implicitamente indexada.
• Uma tabela pode ter apenas uma PRIMARY KEY.
• As tabelas InnoDB são fisicamente ordenadas por sua chave primária (índice clusterizado).

Exemplo:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

2. Índice UNIQUE

• Um índice UNIQUE impõe que todos os valores na(s) coluna(s) indexada(s) sejam distintos. Permite valores NULL, mas múltiplos NULLs são permitidos (a menos que a coluna também faça parte de uma PRIMARY KEY ou outra restrição UNIQUE que o impeça).
• Útil para garantir a integridade dos dados onde uma coluna deve ser única, mas não é o identificador principal.

Exemplo:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE
);

3. INDEX (ou KEY)

• Um índice padrão, também referido como índice não único.
• Usado para acelerar a recuperação de dados. Não impõe exclusividade.

Exemplo:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer_id (customer_id)
);

4. Índice FULLTEXT

• Usado para pesquisas de texto completo em colunas CHAR, VARCHAR e TEXT.
• Permite pesquisas de palavras-chave em grandes campos de texto.
• Suportado pelo InnoDB em versões modernas do MySQL. Instalações MySQL mais antigas podem ter limites diferentes, então verifique sua versão exata antes de projetar em torno dele.

Exemplo:

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT (title, body)
);

5. Índice SPATIAL

• Usado para indexar tipos de dados espaciais, como pontos, linhas e polígonos.
• O comportamento e os requisitos variam de acordo com a versão do MySQL e o mecanismo de armazenamento. Teste a consulta espacial exata que você planeja executar; não presuma que um índice espacial ajudará em todos os predicados do tipo GIS.

6. Índice HASH (Uso Limitado)

• O mecanismo de armazenamento MEMORY do MySQL suporta índices HASH. Eles são construídos para consultas de igualdade, não para varreduras de intervalo ou ordenação.
• Não é um tipo de índice de uso geral para a maioria dos cenários comuns.

Criando e Gerenciando Índices

Como Criar um Índice

Você pode criar índices ao criar uma tabela ou alterando uma tabela existente.

1. Durante a Criação da Tabela:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX idx_department (department_id),
    INDEX idx_hire_date (hire_date)
);

2. Alterando uma Tabela Existente:

-- Adicionar um índice de coluna única
ALTER TABLE customers
ADD INDEX idx_email (email);

-- Adicionar um índice único
ALTER TABLE users
ADD UNIQUE INDEX uidx_username (username);

-- Adicionar um índice de múltiplas colunas (composto)
ALTER TABLE orders
ADD INDEX idx_customer_date (customer_id, order_date);

Como Remover um Índice

Se um índice não for mais necessário ou estiver impactando negativamente o desempenho (por exemplo, durante gravações), você pode removê-lo.

-- Remover um índice padrão
ALTER TABLE customers
DROP INDEX idx_email;

-- Remover um índice único
ALTER TABLE users
DROP INDEX uidx_username;

Índices de Múltiplas Colunas (Compostos)

Índices compostos são criados em duas ou mais colunas. A ordem das colunas em um índice composto é crucial.

• Um índice composto em (col1, col2) pode ser usado para consultas filtrando apenas em col1, ou em col1 E col2.
• Geralmente não é usado para consultas filtrando apenas em col2.

Exemplo:

Considere um índice em (customer_id, order_date). Este índice é mais eficaz para consultas como:

SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-10-27';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date;

Pode não ser muito útil para SELECT * FROM orders WHERE order_date = '2023-10-27';.

Melhores Práticas para Indexação MySQL

1. Indexar Colunas Usadas em Cláusulas WHERE, JOIN e ORDER BY

Estes são os lugares mais comuns onde os índices fornecem benefícios significativos de desempenho.

• Cláusulas WHERE: As condições de filtro são o caso de uso principal.
• Condições JOIN: Indexar colunas usadas em cláusulas ON de instruções JOIN acelera drasticamente as junções de tabelas.
• Cláusulas ORDER BY e GROUP BY: Os índices podem ajudar o MySQL a evitar operações de classificação.

2. Use Índices Compostos com Sabedoria

• A ordem importa: Coloque as colunas que correspondem à forma da consulta primeiro. Filtros de igualdade geralmente vêm antes de filtros de intervalo. Colunas usadas para ordenação podem ajudar após as colunas de filtragem.
• Não coloque cegamente a coluna mais seletiva primeiro se suas consultas reais não a filtrarem. Um índice em (status, created_at) pode ser excelente para WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50, mesmo que status tenha baixa cardinalidade, porque corresponde ao padrão de acesso.

3. Use EXPLAIN Antes e Depois

Nunca julgue um índice pela esperança. Execute EXPLAIN, adicione o índice em um ambiente de teste ou seguro para manutenção e, em seguida, execute EXPLAIN novamente.

EXPLAIN
SELECT order_id, total, created_at
FROM orders
WHERE customer_id = 123
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 20;

Observe type, key, rows e Extra. Se key for NULL, o MySQL não escolheu um índice. Se rows ainda estiver próximo ao tamanho da tabela, o índice pode não ser seletivo o suficiente para esta consulta. Se Extra disser Using filesort, isso não é automaticamente ruim, mas indica que o MySQL não conseguiu retornar as linhas na ordem solicitada a partir do índice escolhido.

Para MySQL 8.0.18 e posteriores, EXPLAIN ANALYZE pode ser ainda mais útil porque executa a consulta e relata o tempo real e as contagens de linhas:

EXPLAIN ANALYZE
SELECT order_id, total
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

Use-o com cuidado em sistemas de produção porque ele executa a instrução.

4. Construa Índices em Torno de Fluxos de Trabalho, Não de Tabelas

Imagine uma tela de administração que lista pagamentos recentes com falha:

SELECT id, customer_id, failure_code, created_at
FROM payments
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 100;

Um índice apenas em status ainda pode deixar o MySQL classificando um grande número de linhas com falha. Um índice em (status, created_at) geralmente é uma correspondência melhor porque o MySQL pode encontrar linhas com falha e lê-las em ordem de tempo. Se a consulta retornar apenas colunas no índice, você pode considerar um índice de cobertura:

CREATE INDEX idx_payments_status_created_cover
ON payments (status, created_at, id, customer_id, failure_code);

Isso pode ser rápido, mas não é gratuito. O índice é mais amplo, ocupa mais armazenamento e custa mais em cada gravação. Eu só recorro a índices de cobertura em consultas críticas que são executadas com frequência suficiente para justificar o custo extra de manutenção.

5. Cuidado com Índices que Parecem Certos, mas Não Fazem Nada

Estas são armadilhas comuns:

• Uma função esconde o valor indexado: WHERE DATE(created_at) = '2025-01-01'.
• Um curinga inicial impede o uso normal da B-tree: WHERE email LIKE '%@example.com'.
• Uma incompatibilidade de tipo força a conversão: comparar uma coluna inteira a uma string entre aspas ainda pode funcionar, mas pode confundir planos em esquemas reais.
• O índice começa com a coluna errada para a consulta: (created_at, customer_id) não é o mesmo que (customer_id, created_at).

Reescreva o predicado quando puder:

WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02'

Essa forma permite que o MySQL use uma varredura de intervalo em created_at.

6. Remova Índices Redundantes e Não Utilizados com Cuidado

O excesso de indexação é um problema de desempenho silencioso. Cada índice secundário extra precisa ser mantido durante INSERT, UPDATE e DELETE. Em uma tabela com muitas gravações, cinco índices não utilizados podem importar mais do que um SELECT lento.

No MySQL 5.7 e 8.0, o esquema sys pode ajudá-lo a encontrar candidatos:

SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema = 'app';

Trate essa saída como uma pista, não como um comando. Um índice pode parecer não utilizado porque o servidor foi reiniciado recentemente, porque um relatório mensal ainda não foi executado ou porque o tráfego no ambiente de teste não corresponde ao de produção. Antes de remover um índice, verifique o histórico de implantação, trabalhos agendados e requisitos de chave estrangeira.

7. Adicione Índices com Segurança em Tabelas Grandes

Em tabelas pequenas, ALTER TABLE ... ADD INDEX geralmente é um evento tranquilo. Em uma grande tabela de produção, pode ser uma operação real. Dependendo da versão do MySQL, mecanismo de armazenamento, definição da tabela e do DDL exato, adicionar um índice pode usar DDL online ou ainda pode criar pressão por meio de bloqueios de metadados, espaço temporário, geração de redo e lag de replicação.

Antes de adicionar um índice grande, verifique:

• O tamanho da tabela e dos índices existentes.
• Se as réplicas podem acompanhar.
• Se sua versão do MySQL suporta o algoritmo online que você espera.
• Se o aplicativo pode tolerar um bloqueio de metadados se uma transação longa bloquear o DDL.

Para sistemas sensíveis, use uma ferramenta de migração como pt-online-schema-change ou gh-ost, ou agende o DDL durante uma janela de baixo tráfego.

Uma Rotina Prática de Revisão de Índices

Quando reviso uma consulta MySQL lenta, uso esta ordem:

1. Capture o SQL exato com valores de ligação reais.
2. Execute EXPLAIN e, quando seguro, EXPLAIN ANALYZE.
3. Verifique se os índices existentes correspondem ao padrão WHERE, JOIN e ORDER BY.
4. Adicione o menor índice composto útil em um ambiente de teste.
5. Compare as linhas examinadas, o tempo de consulta e o impacto na gravação.
6. Implemente com cuidado e monitore os logs de consultas lentas e o lag de replicação.

Essa rotina mantém a indexação honesta. Você não está tentando colecionar índices. Você está tentando reduzir a quantidade de trabalho que o MySQL precisa fazer para as consultas que seu aplicativo realmente executa.