Declaração MySQL CREATE INDEX

A instrução `CREATE INDEX` no MySQL é usada para criar um índice na(s) coluna(s) de uma tabela para aumentar a velocidade de recuperação da consulta. Os índices são essenciais para otimizar o desempenho do banco de dados, especialmente ao lidar com grandes conjuntos de dados.

Uso

A instrução `CREATE INDEX` é usada quando você precisa melhorar o desempenho das operações de recuperação de dados. É particularmente útil para acelerar as consultas `SELECT` que filtram ou classificam dados.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Nessa sintaxe, `CREATE INDEX` define o nome do índice e especifica a tabela e as colunas nas quais o índice é criado.

Exemplos

1. Criação de índices básicos

CREATE INDEX idx_lastname
ON employees (last_name);

Esse exemplo cria um índice chamado `idx_lastname` na coluna `last_name` da tabela `employees`, otimizando as consultas que filtram por sobrenomes.

2. Índice composto

CREATE INDEX idx_name_dob
ON employees (last_name, birth_date);

Essa sintaxe demonstra a criação de um índice composto em `last_name` e `birth_date`, útil para consultas que filtram por ambas as colunas.

3. Índice único

CREATE UNIQUE INDEX idx_email
ON users (email);

Este exemplo cria um índice exclusivo na coluna `email` da tabela `users` para garantir que todas as entradas de e-mail sejam exclusivas e para acelerar as pesquisas por e-mail.

4. Índice de texto completo

CREATE FULLTEXT INDEX idx_content
ON articles (content);

Este exemplo cria um índice `FULLTEXT` na coluna `content` da tabela `articles`, que é útil para pesquisas de texto completo.

5. Índice espacial

CREATE SPATIAL INDEX idx_location
ON locations (geo_point);

Isso cria um índice `SPATIAL` na coluna `geo_point` da tabela `locations`, o que é útil para tipos de dados geográficos.

Dicas e práticas recomendadas

• Indexar seletivamente. Crie índices somente nas colunas que são pesquisadas ou classificadas com frequência para evitar sobrecarga desnecessária.
• Monitore o desempenho. Use a instrução `EXPLAIN` para avaliar o desempenho da consulta e determinar se um índice é vantajoso.
• Considere o tipo de índice. Escolha o tipo de índice apropriado (por exemplo, exclusivo, de texto completo, espacial) com base em seus dados e necessidades de consulta.
• Equilíbrio entre leitura e gravação. Lembre-se de que, embora os índices acelerem as leituras, eles podem tornar mais lentas as operações de gravação (INSERT, UPDATE, DELETE).
• Funcionamento interno. A maioria dos índices usa estruturas de árvore B para manter os dados classificados, o que facilita as pesquisas rápidas.
• Limitações. Entenda as limitações, como o número máximo de índices por tabela ou o tamanho máximo das chaves de índice.
• Convenções de nomenclatura. Use nomes claros e descritivos para os índices para manter a legibilidade, como prefixar com `idx_`.
• Capacidade de gerenciamento. Revise e gerencie regularmente os índices usando `ALTER TABLE` para modificar e `DROP INDEX` para remover índices desnecessários.

Gerenciamento e remoção de índices

Para modificar um índice, você pode usar a instrução `ALTER TABLE`. Para remover um índice, é usada a instrução `DROP INDEX`, conforme mostrado abaixo:

DROP INDEX idx_lastname ON employees;

Considerações

• Chave primária vs. Chave de acesso Índice único. Uma chave primária é uma restrição que garante valores exclusivos e não anulabilidade, enquanto um índice exclusivo impõe apenas a exclusividade.
• Colunas de baixa cardinalidade. Evite indexar colunas com baixa cardinalidade, pois elas podem não oferecer benefícios significativos de desempenho.
• Mesas pequenas. Os índices em tabelas muito pequenas podem não gerar melhorias perceptíveis no desempenho devido à sobrecarga de manutenção do índice.