Índices em MySQL

Índices em MySQL

Os índices são empregados em uma consulta para ajudar a encontrar registros com um valor específico em uma coluna de forma rápida – ou seja, para aumentar o desempenho na execução de consultas. Sem índices, o MySQL faz uma busca iniciando no primeiro registro e varrendo toda a tabela até encontrar os registros que importam.

Idealmente, devemos criar índices nos campos que são usados em cláusulas WHERE e também envolvidos em JOINS nas consultas.

Tipos de índices em MySQL

  • B-Tree (árvore balanceada): Tipo mais comum (padrão), suportado pela maioria dos engines.
  • Hash: Suportado pela engine MEMORY, e pelo InnoDB via adaptative hash indexes.
  • R-Tree: Adequado para tipos de dados espaciais. Mais usado em sistemas como o PostgreSQL. Suportado pela engine InnoDB.
  • Full-Text Index: Usado em operações do tipo MATCH AGAINST. Suportado pelas engines MyISAM e InnoDB.

Como criar Índices em MySQL

Podemos criar índices no momento em que criamos uma tabela, ou posteriormente, alterando a estrutura de uma tabela já existente.

Criar índice junto com a tabela

Para criar um índice no momento em que criamos uma tabela, usamos a declaração INDEX (coluna)

Sintaxe

CREATE [UNIQUE] INDEX nome_índice
ON nome_tabela (
  coluna1 [ASC | DESC],
  [coluna2 [ASC | DESC]]...
);
  • UNIQUE significa que o índice não permitirá valores duplicados na coluna.
  • ASC e DESC se referem à ordem de indexação, se ascendente (ASC) ou descendente (DESC). O padrão é ASC, se nada for informado.

Exemplo:

CREATE TABLE tbl_Editoras (
IdEditora SMALLINT PRIMARY KEY AUTO_INCREMENT,
NomeEditora VARCHAR(40) NOT NULL,
INDEX (NomeEditora)
);

Quando criamos uma tabela com uma chave primária ou com um campo de chave única (UNIQUE), o MySQL automaticamente cria nesse campo um índice especial de nome PRIMARY. Este índice é do tipo CLUSTERED (clusterizado), também chamado de índice primário.

Esse índice é armazenado juntamente com os dados na própria tabela, e impõe a ordem das linhas na tabela.

Podemos criar mais índices em uma tabela, do tipo não-clusterizados (NON-CLUSTERED), que são índices secundários. Em nosso caso, o índice criado na coluna NomeEditora é um índice secundário, e a chave primária IdEditora possui um índice primário, criado automaticamente.

Criar índice em tabela já existente

Para adicionar um índice a uma tabela já existente usamos a declaração CREATE INDEX:

CREATE INDEX nome_índice ON tabela(colunas);

Exemplo:

CREATE INDEX idx_editora ON tbl_Editoras(NomeEditora);

Visualizar os índices

Podemos visualizar os índices associados com uma tabela por meio do comando SHOW INDEX, como segue:

SHOW INDEX FROM tabela;

Por exemplo:

SHOW INDEX FROM tbl_Editoras;

Resultado:

CREATE INDEX - criar índices no MySQL

Testando os índices

Vamos testar a busca com os índices. Para isso vamos primeiramente recriar a tabela de editoras sem especificar um índice secundário, apenas com a chave primária:

CREATE TABLE tbl_Editoras (
IdEditora SMALLINT PRIMARY KEY AUTO_INCREMENT,
NomeEditora VARCHAR(40) NOT NULL
);

Logo após, vamos inserir registros e então realizar uma consulta na tabela.

Inserimos 20 registros na tabela:

INSERT INTO tbl_Editoras (NomeEditora)
VALUES 
('Mc Graw-Hill'),('Apress'),('Bookman'),('Bookboon'),
('Packtpub'),('O´Reilly'),('Springer'),('Érica'),
('For Dummies'),('Novatec'),('Microsoft Press'),('Cisco Press'),
('Addison-Wesley'),('Companhia das Letras'),('Artech House'),('Wiley'),
('CRC Press'),('Manning'),('Penguin Books'),('Sage Publishing');

Realizamos uma consulta simples agora para ver todos os registros inseridos:

SELECT * FROM tbl_Editoras;

Consulta com índices MySQL

Vamos agora realizar uma consulta mais específica, filtrando o resultado pelo nome de uma editora, como por exemplo a editora Springer:

SELECT * FROM tbl_Editoras WHERE NomeEditora = 'Springer';

Podemos ver como o MySQL realiza esta consulta internamente acrescentando a cláusula EXPLAIN à consulta:

EXPLAIN SELECT * FROM tbl_Editoras WHERE NomeEditora = 'Springer';

Resultado:

Consulta sem índices em MySQL - comando EXPLAIN

Como podemos ver, o MySQL teve de varrer as 20 linhas da tabela para encontrar o registro requisitado (valor 20 na coluna “rows”). A coluna Extra nos informa que a consulta realizou um filtro usando a cláusula WHERE (“Using where”).

Vamos agora criar um índice na coluna NomeEditora:

CREATE INDEX idx_editora ON tbl_Editoras(NomeEditora);

E então repetir o comando EXPLAIN para ver como o MySQL se comportará agora que a coluna consultada possui um índice:

EXPLAIN SELECT * FROM tbl_Editoras WHERE NomeEditora = 'Springer';

Resultado:

Como podemos ver, o MySQL teve de ler apenas uma linha para encontrar o registro solicitado. Muito mais rápido e efetivo. Veja que na coluna “Extra” temos a informação de que a consulta usou o índice (“Using index”).

Como excluir um índice

Para excluir um índice (sem excluir a coluna associada), usamos o comando DROP INDEX, conforme a sintaxe a seguir;

DROP INDEX nome_índice ON tabela;

Por exemplo, para excluir o índice idx_editora executamos o comando:

DROP INDEX idx_editora ON tbl_Editoras;

É isso aí!

Anterior: Aliases com AS

Próximo: Funções de Agregação

 

Sobre Fábio dos Reis (1212 Artigos)
Fábio dos Reis trabalha com tecnologias variadas há mais de 30 anos, tendo atuado nos campos de Eletrônica, Telecomunicações, Programação de Computadores e Redes de Dados. É um entusiasta de Ciência e Tecnologia em geral, adora Viagens e Música, e estuda idiomas, além de ministrar cursos e palestras sobre diversas tecnologias em São Paulo e outras cidades do Brasil.

5 Comentários em Índices em MySQL

  1. VINICIUS RESENDE DOS SANTOS // 01/05/2020 em 13:01 // Responder

    Para eu aproveitar os índices nas consultas, eu devo sempre usar o explain na consulta?

    • É bom rodar um explain antes de aplicar um índice efetivamente, para ter uma ideia do seu impacto, pois nem sempre usar índices melhora efetivamente a performance de uma consulta.

  2. isso funciona para numeros ao invez de frases

  3. arlindo jonatão // 10/09/2020 em 15:12 // Responder

    muito bom o artigo adorei

Escreva um comentário

Seu e-mail não será divulgado


*