Como Verificar e Otimizar Índices MySQL: Guia Completo com SHOW INDEX, EXPLAIN e Ajuste de Performance

1. Introdução

MySQL é um banco de dados relacional amplamente usado em muitas aplicações web e sistemas de gerenciamento de dados. Para melhorar a velocidade de recuperação de dados, existe um mecanismo chamado “índice”. No entanto, se não for gerenciado adequadamente, os índices podem realmente reduzir o desempenho.

Por que Verificar Índices é Importante?

Um índice em um banco de dados é semelhante à seção de índice de um livro. Índices projetados corretamente aumentam a velocidade de execução de consultas de busca. Contudo, os seguintes problemas podem ocorrer:

• Índices não criados corretamente → Pode causar buscas lentas
• Índices desnecessários existem → Leva a atualizações e inserções mais lentas
• Incerteza sobre quais índices estão sendo usados → Torna difícil decidir se índices não utilizados devem ser removidos

O que Você Aprenderá Neste Artigo

• O mecanismo básico dos índices MySQL
• Como verificar os índices atuais (usando comandos SQL)
• Como gerenciar e otimizar índices
• Técnicas para analisar o uso de índices

Agora, vamos aprender sistematicamente sobre índices MySQL e usar esse conhecimento para melhorar o desempenho do banco de dados.

2. O que é um Índice MySQL?

Um índice é um recurso essencial para melhorar o desempenho do banco de dados. Nesta seção, explicamos o conceito básico, tipos, vantagens e desvantagens dos índices.

Conceito Básico dos Índices

Um índice de banco de dados é um mecanismo que permite a busca rápida de valores em colunas específicas. Por exemplo, ao buscar um registro específico em uma tabela que contém grande quantidade de dados, sem um índice o banco de dados precisa percorrer todos os registros (full table scan). Aplicar um índice torna a recuperação de dados eficiente e melhora significativamente a velocidade de processamento.

Tipos de Índices MySQL

MySQL suporta vários tipos de índices, cada um adequado a casos de uso específicos.

1. PRIMARY KEY (Índice de Chave Primária)

• Pode ser definido apenas uma vez por tabela
• Garante a unicidade da tabela
• Funciona como um índice clusterizado

1. UNIQUE Index

• Assegura que os valores na coluna especificada não se repitam
• Valores NULL são permitidos (vários NULLs são aceitos)

1. INDEX (Índice Geral)

• Usado para acelerar buscas
• Dados duplicados são permitidos

1. FULLTEXT Index (para busca de texto)

• Otimiza a busca de texto
• Utilizado em combinação com a sintaxe MATCH ... AGAINST

1. SPATIAL Index (para dados geográficos)

• Projetado para dados espaciais (GIS)

Vantagens e Desvantagens dos Índices

Vantagens

• Melhora a velocidade de busca nas consultas
• Aumenta o desempenho de operações JOIN e cláusulas WHERE
• Torna a recuperação de dados específicos mais eficiente

Desvantagens

• Mais índices tornam as operações INSERT, UPDATE e DELETE mais lentas
• Consomem espaço em disco
• Índices mal projetados podem degradar o desempenho

3. Como Verificar Índices MySQL

Para gerenciar os índices MySQL adequadamente, é importante verificar quais índices estão definidos atualmente em uma tabela. Nesta seção, explicamos como verificar índices usando o comando SHOW INDEX, INFORMATION_SCHEMA.STATISTICS e o comando mysqlshow.

Comando SHOW INDEX (Método Básico)

No MySQL, você pode usar o comando SHOW INDEX para obter uma lista dos índices definidos em uma tabela específica. Esse comando permite verificar detalhes como nome do índice, colunas cobertas pelo índice e se existe uma restrição de unicidade.

Sintaxe Básica

SHOW INDEX FROM table_name;

Exemplo

Por exemplo, para verificar os índices definidos na tabela users, execute o seguinte SQL:

SHOW INDEX FROM users;

Saída de Exemplo

Usando INFORMATION_SCHEMA.STATISTICS (Obter Informações Detalhadas)

Ao usar a tabela de sistema do MySQL INFORMATION_SCHEMA.STATISTICS, você pode obter as mesmas informações do SHOW INDEX com mais flexibilidade.

Verificar Índices de uma Tabela Específica

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

Obter Índices em Todo o Banco de Dados

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name';

Comando mysqlshow (Verificar via CLI)

Você também pode recuperar informações de índices usando as ferramentas de linha de comando do MySQL. Isso é especialmente útil ao trabalhar via SSH em um servidor MySQL.

Como Executar o Comando

mysqlshow -u username -p password database_name table_name

Exemplo

mysqlshow -u root -p my_database users

O Que Fazer Se Não Existirem Índices

Se nenhum índice for exibido após executar SHOW INDEX ou consultar INFORMATION_SCHEMA.STATISTICS, a tabela pode não ter índices adequados. Nesse caso, você pode melhorar o desempenho de busca criando índices conforme necessário.

Criar um Novo Índice

CREATE INDEX idx_column ON users (email);

Definir uma Chave Primária (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Remover Índices Desnecessários

ALTER TABLE users DROP INDEX idx_column;

4. Como Verificar o Uso de Índices

Verificar se os índices estão funcionando corretamente é uma etapa crítica na otimização de desempenho do MySQL. Nesta seção, explicamos como determinar quais índices uma consulta está usando, aproveitando o comando EXPLAIN e o Performance Schema.

Análise de Consulta com EXPLAIN

O comando EXPLAIN é usado para visualizar como uma consulta SQL será executada. Ele ajuda a analisar os índices usados, métodos de acesso e plano de execução, o que o torna útil para verificar se os índices estão funcionando como esperado.

Sintaxe Básica

EXPLAIN SELECT * FROM table_name WHERE condition;

Exemplo

Por exemplo, para buscar na tabela users usando a coluna email como condição:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Saída de Exemplo

Pontos Principais

• Se type = ALL, a consulta está realizando um varredura completa da tabela, portanto provavelmente você precisa de um índice.
• Se o nome de um índice aparecer em key, esse índice está sendo usado.
• Se o valor de rows for muito grande, pode ser necessária a otimização da consulta.

Usando o Performance Schema

Com o performance_schema do MySQL, você pode analisar detalhadamente quais índices estão sendo usados e com que frequência durante a execução da consulta.

Obter Estatísticas de Execução de Consulta

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

Verificar Uso de Índices para uma Tabela Específica

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

O Que Fazer Se um Índice Não for Usado

1. Revisar a Consulta

Se um índice não estiver sendo usado, a estrutura da consulta pode ser o problema. Por exemplo, o padrão a seguir pode impedir o uso do índice.

❌ Exemplo Incorreto (Função desabilita o uso do índice)

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

→ Por causa de LOWER(email), o índice em email pode ser ignorado.

✅ Exemplo Corrigido (Evite usar uma função)

SELECT * FROM users WHERE email = 'test@example.com';

2. Recriar o Índice

Se um índice existente não estiver funcionando corretamente, removê-lo e recriá-lo pode às vezes melhorar o desempenho.

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. Atualizar Estatísticas

Se as estatísticas da tabela ficarem desatualizadas, o MySQL pode não usar os índices de forma otimizada. Você pode atualizar as estatísticas com o seguinte comando:

ANALYZE TABLE users;

5. Gerenciamento de Índices

Os índices MySQL são essenciais para melhorar o desempenho da recuperação de dados. No entanto, se não forem gerenciados adequadamente, podem degradar o desempenho geral do banco de dados. Nesta seção, explicamos em detalhes como criar, excluir e identificar índices desnecessários.

Criando Índices

Ao criar índices apropriados, você pode acelerar significativamente as buscas de dados. No MySQL, você pode adicionar índices usando CREATE INDEX ou ALTER TABLE.

Sintaxe Básica

CREATE INDEX index_name ON table_name(column_name);

Exemplo

Para adicionar um índice à coluna email na tabela users:

CREATE INDEX idx_email ON users(email);

Índice Multi-Coluna (Índice Composto)

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

Índice Único

CREATE UNIQUE INDEX idx_unique_email ON users(email);

Definindo uma Chave Primária (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Removendo Índices

Remover índices desnecessários ajuda a reduzir a sobrecarga do banco de dados.

Sintaxe Básica

ALTER TABLE table_name DROP INDEX index_name;

Exemplo

Por exemplo, para excluir um índice chamado idx_email:

ALTER TABLE users DROP INDEX idx_email;

Identificando e Removendo Índices Desnecessários

Verificar Índices Não Utilizados

SELECT * FROM sys.schema_unused_indexes;

Verificar o Status da Tabela (Impacto dos Índices)

SHOW TABLE STATUS LIKE 'users';

Remover Índices Desnecessários

ALTER TABLE users DROP INDEX idx_unused;

Após a exclusão, recomenda-se atualizar as estatísticas usando ANALYZE TABLE.

ANALYZE TABLE users;

6. Otimização de Índices (Melhoria de Desempenho)

O gerenciamento adequado de índices pode melhorar significativamente o desempenho das consultas MySQL. No entanto, simplesmente criar índices não é suficiente — design, gerenciamento e monitoramento adequados são necessários para manter o desempenho ideal.

Design Adequado de Índices

Índices bem projetados podem melhorar drasticamente a velocidade de busca no MySQL.

Casos em que os Índices Devem ser Aplicados

Casos em que os Índices Não Devem ser Aplicados

Usando o Log de Consultas Lentas

O Log de Consultas Lentas permite identificar consultas de longa duração e analisar quais índices não estão sendo aplicados.

Habilitar o Log de Consultas Lentas

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking longer than 2 seconds

Verificar o Log de Consultas Lentas

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Analisar uma Consulta Lenta

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

Exemplo de Aplicação de um Índice

CREATE INDEX idx_last_login ON users(last_login);

Atualizando Estatísticas (ANALYZE & OPTIMIZE)

ANALYZE TABLE (Atualizar Estatísticas)

ANALYZE TABLE users;

OPTIMIZE TABLE (Desfragmentação)

OPTIMIZE TABLE users;

7. Perguntas Frequentes sobre Índices (FAQ)

Os índices MySQL são um mecanismo essencial para melhorar o desempenho do banco de dados. No entanto, se não forem gerenciados adequadamente, podem ter o efeito oposto. Nesta seção, resumimos perguntas frequentes (FAQ) e respostas relacionadas aos índices MySQL.

A velocidade de busca melhorará ao adicionar mais índices?

A. Não necessariamente.

Os índices são projetados para melhorar o desempenho das consultas, mas adicionar muitos índices pode realmente degradar o desempenho do banco de dados.

• Aumenta a sobrecarga de gravação (INSERT, UPDATE, DELETE)
• Se um índice é usado depende da consulta
• Índices desnecessários consomem memória

Quais colunas devem ter índices?

A. É eficaz aplicar índices aos seguintes tipos de colunas:

Os índices são criados automaticamente?

A. Alguns índices são criados automaticamente, mas outros devem ser adicionados manualmente.

Índices Criados Automaticamente

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY index
  email VARCHAR(255) UNIQUE -- Index automatically created by UNIQUE constraint
);

Índices Criados Manualmente

CREATE INDEX idx_email ON users(email);

Como devo escolher entre índices B-Tree, Hash e FULLTEXT?

Como posso verificar o tamanho dos índices?

SHOW TABLE STATUS LIKE 'users';

Como posso verificar se um índice está sendo usado?

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

Quando devo remover um índice?

Remover um Índice Desnecessário

ALTER TABLE users DROP INDEX idx_unused;

8. Resumo

Neste artigo, cobrimos de forma abrangente os índices MySQL — dos fundamentos aos métodos de verificação, gerenciamento, otimização e perguntas frequentes. Aqui, revisamos os pontos principais de cada seção e resumimos as melhores práticas para otimizar o gerenciamento de índices MySQL.

Principais Conclusões

O que é um Índice MySQL?

• Um índice é um mecanismo que acelera a recuperação de dados.
• Existem vários tipos, incluindo índices B-Tree, Hash e FULLTEXT .
• Mais eficaz quando aplicado a colunas usadas em cláusulas WHERE, JOINs e ORDER BY.

Como Verificar os Índices MySQL

• Use o comando SHOW INDEX para visualizar a lista de índices.
• Use INFORMATION_SCHEMA.STATISTICS para obter informações detalhadas.

Como Verificar o Uso de Índices

• Use EXPLAIN para ver quais índices uma consulta utiliza.
• Use o Performance Schema para analisar a frequência de uso dos índices.

Gerenciamento de Índices

• Use CREATE INDEX para aplicar índices às colunas apropriadas.
• Use ALTER TABLE DROP INDEX para remover índices desnecessários.
• Verifique o tamanho dos índices com SHOW TABLE STATUS e otimize conforme necessário.

Otimização de Índices (Melhoria de Desempenho)

• Aplique índices às colunas WHERE, JOIN e ORDER BY usadas com frequência.
• Use o Slow Query Log para identificar e otimizar consultas lentas.
• Atualize as estatísticas com ANALYZE TABLE e OPTIMIZE TABLE.

Melhores Práticas de Gerenciamento de Índices MySQL

1. Identifique gargalos de consulta antes de aplicar índices.
2. Selecione índices apropriados.

• Use corretamente índices de coluna única e compostos.
• Use UNIQUE INDEX onde restrições de unicidade são necessárias.

1. Remova regularmente índices desnecessários.

• Identifique índices não usados usando SHOW INDEX e schema_unused_indexes.

1. Atualize as estatísticas regularmente.

• Atualize as estatísticas usando ANALYZE TABLE.
• Execute OPTIMIZE TABLE para resolver fragmentação causada por deleções e atualizações.

Próximos Passos

✅ Checklist Prático
✅ Você verificou os índices atuais usando SHOW INDEX?
✅ Você habilitou o Slow Query Log e identificou consultas lentas?
✅ Você analisou o plano de execução da consulta usando EXPLAIN?
✅ Você removeu índices desnecessários e criou os apropriados?
✅ Você atualizou as estatísticas usando ANALYZE TABLE?

Resumo Final

• Índices MySQL bem gerenciados melhoram significativamente o desempenho de busca.
• Use o Slow Query Log e EXPLAIN para analisar a eficácia dos índices e otimize conforme necessário.
• Mantenha o desempenho do banco de dados atualizando regularmente as estatísticas e otimizando as tabelas.

Este é o fim do guia completo de gerenciamento de índices MySQL.

Use esse conhecimento para construir sistemas de banco de dados mais rápidos e eficientes. 💡🚀