Tutorial de CREATE INDEX no MySQL: otimizando consultas MySQL

Introdução

Na otimização de bancos de dados, criar índices é uma técnica fundamental para melhorar o desempenho das consultas. Este guia mostra o porquê e o como da criação de índices, para que suas consultas MySQL rodem da forma mais eficiente possível. Entendendo como usar índices de forma eficaz, você pode reduzir bastante o tempo de execução das consultas, especialmente em bancos com grandes volumes de dados.

Entendendo índices no MySQL

Um índice no MySQL é uma estrutura de dados que aumenta a velocidade das operações de leitura em uma tabela, ao custo de escritas adicionais e espaço de armazenamento para manter a estrutura do índice. Os índices servem para localizar dados rapidamente, sem precisar percorrer todas as linhas da tabela sempre que ela é acessada.

Como criar um índice no MySQL

Criação básica de índice

Para criar um índice no MySQL, use a instrução CREATE INDEX:

Consulta:

CREATE INDEX idx_lastname ON employees(lastname);

Este comando cria um índice chamado idx_lastname na coluna lastname da tabela employees, deixando mais rápidas as buscas pela coluna lastname.

Exemplo de saída:

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Criando um índice único

Um índice único garante que todos os valores da coluna indexada sejam distintos.

Consulta:

CREATE UNIQUE INDEX idx_employee_id ON employees(employee_id);

Este comando cria um índice único na coluna employee_id, garantindo que duas linhas nunca tenham o mesmo employee_id.

Exemplo de saída:

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Usando índices com chaves compostas

Você pode criar um índice em várias colunas para otimizar consultas que filtram ou ordenam por essas colunas.

Consulta:

CREATE INDEX idx_name_department ON employees(lastname, department_id);

Este comando cria um índice que inclui as colunas lastname e department_id, útil para consultas que envolvem esses dois campos.

Exemplo de saída:

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Casos de uso práticos

Melhorando o desempenho das consultas

Os índices melhoram muito o desempenho das consultas, especialmente em instruções SELECT que envolvem grandes conjuntos de dados.

Antes da indexação:

Considere uma consulta SELECT na tabela employees sem índice na coluna lastname.

Depois da indexação:

A mesma consulta SELECT após a criação de idx_lastname.

Consulta:

SELECT * FROM employees WHERE lastname = 'Doe';

Exemplo de saída sem índice:

/* Takes significantly longer, e.g., 1.5 seconds */

Exemplo de saída com índice:

/* Takes significantly less time, e.g., 0.02 seconds */

Garantindo a integridade dos dados

Índices únicos impedem valores duplicados em uma coluna, garantindo a integridade dos dados.

Cenário:

Tentativa de inserir um employee_id duplicado após criar um índice único.

Exemplo de saída:

ERROR 1062 (23000): Duplicate entry '12345' for key 'idx_employee_id'

Conclusão

Criar índices no MySQL é uma técnica importante para otimizar suas consultas. Escolhendo as colunas certas para indexar — especialmente as usadas com frequência em condições de busca ou cláusulas de join — você pode obter ganhos de desempenho significativos.

Lembre-se: embora os índices acelerem a leitura dos dados, eles também deixam mais lentas as operações de inserção e atualização, por causa do custo extra de manter a estrutura do índice. Use-os com critério para encontrar o equilíbrio certo entre desempenho de leitura e de escrita.

Experimente com base nesses exemplos nos seus bancos MySQL para ver os benefícios da indexação. Com planejamento e implementação cuidadosos, os índices se tornarão uma parte valiosa do seu kit de otimização de bancos de dados.

Boas otimizações!