Aprenda quando criar índices no MySQL, MariaDB e PostgreSQL, como achar índices faltando ou sem uso e como criá-los sem travar a produção.

Para que serve

Saber como criar índices do jeito certo é a forma mais barata de acelerar um banco. Um índice bem escolhido transforma uma varredura de milhões de linhas em poucas leituras; um índice desnecessário só ocupa disco, memória e deixa as gravações mais lentas. Este artigo explica quando vale criar, como descobrir os que faltam (e os que sobram) no MySQL/MariaDB e no PostgreSQL, e como criar sem parar a aplicação.

Para encontrar primeiro a consulta lenta, veja o artigo MySQL lento: slow query log e EXPLAIN; no PostgreSQL, o artigo sobre monitorar banco de dados mostra o pg_stat_statements.

Pré-requisitos

  • Acesso a um usuário com permissão de ALTER/CREATE INDEX nas tabelas.
  • Espaço livre em disco: criar um índice em tabela grande pode precisar de espaço temporário equivalente ao tamanho do índice.
  • Backup recente antes de mexer em tabelas de produção (veja a categoria Backup e migração).

Quando criar índices: regras práticas

  • Colunas do WHERE e do JOIN que filtram bem. Chaves estrangeiras quase sempre merecem índice (o PostgreSQL não cria automaticamente; o InnoDB cria).
  • Seletividade: indexar uma coluna com dois valores (ex.: ativo = 1/0) sozinha raramente ajuda. Ela é útil dentro de um índice composto ou, no PostgreSQL, como índice parcial.
  • Índice composto e ordem das colunas: o índice (cliente_id, status, criado_em) serve para filtros em cliente_id, em cliente_id + status e nos três juntos, mas não para filtrar só por status. Coloque primeiro as colunas comparadas por igualdade e por último a de intervalo ou ordenação.
  • Índice de cobertura: se o índice contém todas as colunas que a consulta usa, o banco nem lê a tabela (Using index no MySQL, Index Only Scan no PostgreSQL). No PostgreSQL há a cláusula INCLUDE para isso.
  • Custo: cada índice é atualizado a cada INSERT/UPDATE/DELETE. Em tabelas de log com muita escrita e pouca leitura, mantenha o mínimo.

Como achar índices faltando no MySQL e MariaDB

O schema sys (presente no MySQL 8 e no MariaDB 10.6 ou mais novo, com o Performance Schema ligado) tem visões prontas:

-- Consultas que fazem varredura completa, ordenadas por tempo
SELECT query, db, exec_count, total_latency, no_index_used_count, rows_examined
FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_count DESC LIMIT 15;

-- Tabelas mais varridas sem índice
SELECT * FROM sys.schema_tables_with_full_table_scans LIMIT 15;

Pegue as consultas listadas, rode EXPLAIN e veja quais colunas aparecem no filtro. Ignore tabelas pequenas (algumas centenas de linhas): varrê-las é barato.

Como achar índices faltando no PostgreSQL

-- Tabelas com muita leitura sequencial e muitas linhas
SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
ORDER BY seq_tup_read DESC LIMIT 15;

Tabelas grandes com seq_tup_read enorme e idx_scan baixo são candidatas. Para saber qual consulta causa isso, cruze com o pg_stat_statements e rode EXPLAIN (ANALYZE, BUFFERS) na consulta: Seq Scan com Rows Removed by Filter alto indica filtro sem índice.

Chaves estrangeiras sem índice causam lentidão em DELETE na tabela pai e bloqueios. Esta consulta lista FKs de uma coluna sem índice começando por ela:

SELECT c.conrelid::regclass AS tabela, a.attname AS coluna, c.conname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f' AND array_length(c.conkey,1) = 1
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]);

Como criar o índice sem travar a produção

MySQL / MariaDB

No InnoDB, adicionar índice secundário é uma operação online: leituras e escritas continuam durante a criação. Peça isso explicitamente, para o comando falhar em vez de bloquear caso não seja possível:

ALTER TABLE pedidos
  ADD INDEX idx_cliente_status_data (cliente_id, status, criado_em),
  ALGORITHM=INPLACE, LOCK=NONE;

O comando ainda precisa de um bloqueio de metadados rápido no início e no fim; se houver uma transação longa aberta na tabela, ele espera. Rode fora do pico e acompanhe com SHOW PROCESSLIST. Para tabelas muito grandes, ferramentas como pt-online-schema-change ou gh-ost são alternativas.

PostgreSQL

CREATE INDEX CONCURRENTLY idx_pedidos_cliente_status_data
  ON pedidos (cliente_id, status, criado_em);

Sem CONCURRENTLY, o CREATE INDEX bloqueia escritas na tabela até terminar. Com ele, a criação demora mais, mas a aplicação continua gravando. O comando não roda dentro de transação e, se falhar, deixa um índice marcado como inválido: apague-o com DROP INDEX CONCURRENTLY e tente de novo.

Recursos úteis do PostgreSQL:

  • Índice parcial: CREATE INDEX ... ON pedidos (criado_em) WHERE status = 'aberto'; fica pequeno e atende só as consultas daquele filtro.
  • Índice de expressão: CREATE INDEX ... ON clientes (lower(email)); para consultas com WHERE lower(email) = ....

Como achar índices sem uso ou duplicados

MySQL/MariaDB:

SELECT * FROM sys.schema_unused_indexes;
SELECT table_name, redundant_index_name, dominant_index_name
FROM sys.schema_redundant_indexes;

PostgreSQL:

SELECT s.relname AS tabela, s.indexrelname AS indice, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS tamanho
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;
Atenção: as estatísticas zeram quando o serviço reinicia (MySQL) ou quando alguém as reseta (PostgreSQL), e não contam o que roda em réplicas. Um índice "sem uso" pode ser usado só no fechamento do mês. Antes de apagar, desative-o: no MySQL 8, ALTER TABLE t ALTER INDEX nome INVISIBLE;; no MariaDB 10.6+, ALTER TABLE t ALTER INDEX nome IGNORED;. Se nada piorar em algumas semanas, aí sim remova. Nunca apague índices de chave primária ou únicos.

Como saber se funcionou

  • O EXPLAIN da consulta passa a mostrar o novo índice (key no MySQL; Index Scan ou Bitmap Index Scan no PostgreSQL).
  • No PostgreSQL, idx_scan do novo índice em pg_stat_user_indexes começa a subir.
  • A consulta desaparece do slow log ou do topo do pg_stat_statements.

Problemas comuns

Criei o índice e o banco não usa

Verifique se a consulta aplica função ou conversão de tipo na coluna, se a ordem das colunas do índice composto atende o filtro e se as estatísticas estão atualizadas (ANALYZE TABLE t; no MySQL, ANALYZE t; no PostgreSQL). Em tabelas pequenas, a varredura completa pode ser mesmo o plano mais barato.

O ALTER TABLE ficou parado em "Waiting for table metadata lock"

Alguma transação aberta usa a tabela. Ache-a pelo processlist (veja o artigo sobre deadlocks e locks) e finalize-a, ou cancele o ALTER e tente fora do pico.

As gravações ficaram mais lentas

Muitos índices na mesma tabela. Revise os sem uso e os redundantes com as consultas acima.

Perguntas frequentes

Devo criar um índice para cada coluna?

Não. Prefira poucos índices compostos pensados para as consultas reais. Vários índices de uma coluna só costumam ser menos eficientes que um composto bem ordenado.

Chave primária já é índice?

Sim, nos dois bancos. No InnoDB, a tabela é fisicamente organizada pela chave primária, por isso chaves curtas (inteiros) são melhores que UUIDs aleatórios em texto.

Quanto tempo leva para criar um índice?

Depende do tamanho da tabela e do disco. Em NVMe, tabelas de alguns GB costumam levar minutos; teste antes em uma cópia ou réplica.

Leitura complementar

Precisa de ajuda?

Se a criação de um índice estiver consumindo disco ou I/O de forma anormal no servidor, abra um ticket com a saída de df -h e iostat -x 5 3 para verificarmos o hardware.

Esta resposta lhe foi útil? 0 Usuários acharam útil (0 Votos)

Leia também