Criar índice no MySQL: Tutorial de índice do MySQL [2022]
Publicados: 2021-01-01Um Cientista de Dados ou um programador terá que trabalhar com grandes quantidades de dados e precisa ser capaz de lidar com eles de forma eficiente para uma execução mais rápida. Eles também precisam saber como os dados estão organizados e os métodos mais rápidos para acessar facilmente os dados específicos necessários.
Como o MySQL é um sistema de gerenciamento de banco de dados relacional que possui aplicações em diversos setores, como bancos de dados web, aplicativos de comércio eletrônico, mídias sociais etc. os cientistas de dados precisam conhecer todas as técnicas relacionadas ao MySQL, uma das quais são os índices. O artigo descreve como criar índice no MySQL.
Índice
O que é um Índice?
No MySQL, os índices são usados para classificar os dados de forma sequencial organizada e recuperar os dados de forma eficiente e rápida. Eles são criados usando uma ou mais colunas que serão usadas para filtrar dados. Índices são um tipo de tabela que mantém um campo primário ou de índice e um ponteiro para cada registro na tabela principal.
Para uma melhor compreensão, um índice de livros pode ser usado como exemplo. Em vez de procurar uma página percorrendo todas as páginas de um livro, o índice do livro pode ser verificado para encontrar a página que se está procurando.
Na maioria dos sistemas de banco de dados, é necessário um desempenho alto e rápido. Muitas empresas investem grandes somas de dinheiro em hardware para recuperação e manipulação de dados mais rápidas. Mas otimizando o banco de dados, os custos podem ser reduzidos.
O tempo de resposta geralmente é mais longo porque os registros são armazenados aleatoriamente e as consultas de pesquisa precisam encontrar os dados necessários percorrendo todos os registros armazenados. Os índices tornam essa pesquisa mais fácil e rápida.
Ler: Renomear o nome da coluna no SQL
Criar índice
Você pode criar um índice no MySQL em uma tabela usando a seguinte sintaxe básica.
CREATE INDEX index_name table_name (column_name);
Você também pode criar um índice exclusivo em uma tabela. Isso significa que o nome é exclusivo e duas linhas não podem ter o mesmo nome. A sintaxe a seguir pode ser usada para isso.
CREATE UNIQUE INDEX index_name ON table_name (column_name);
Se um índice secundário para uma coluna for criado, o MySQL armazena os valores em uma estrutura de dados separada, como B-Tree e Hash. No MySQL, se as colunas são colunas de string, você pode criar um índice para a parte inicial dos valores de coluna da coluna de string usando a sintaxe:
nome_coluna(comprimento)
Para criar a parte da chave do prefixo da coluna quando a tabela é criada, a seguinte sintaxe pode ser usada:
CREATE TABLE nome_tabela(
lista_coluna,
ÍNDICE(nome_coluna(comprimento))
);
E para adicionar um índice a uma tabela existente:
CREATE INDEX index_name ON table_name (column_name(comprimento));
O comprimento aqui é o número de caracteres se for um tipo de string não-binário como CHAR, VARCHAR e TEXT e o número de bytes se for um tipo de string binário como BINARY, VARBINARY e BLOB. Você também pode criar partes de chave de prefixo de coluna para colunas CHAR, VARCHAR, BINARY e VARBINARY no MySQL. É necessário especificar as partes da chave do prefixo da coluna se você criou índices para as colunas BLOB e TEXT.
No caso de índices FULLTEXT que são suportados apenas por tabelas InnoDB e MyISAM, eles podem incluir apenas colunas CHAR, VARCHAR e TEXT. Ele não oferece suporte à indexação de prefixo de coluna e, se qualquer comprimento de prefixo for especificado, ele será ignorado.
Deve ler: Ideias de projetos SQL
Alterando tabelas para adicionar ou remover Índice
Para adicionar índices a uma tabela, existem quatro tipos de instruções que podem ser usadas:
- Para adicionar uma chave primária:
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
Os valores indexados aqui devem ser exclusivos e não podem ser NULL.
- Para um índice com valores exclusivos:
ALTER TABLE table_name ADD UNIQUE index_name (column_list);
Os valores devem ser exclusivos, exceto NULL, que pode aparecer várias vezes.
- Para adicionar um índice comum:
ALTER TABLE table_name ADD INDEX index_name (column_list);
Aqui os valores podem aparecer mais de uma vez.
- Para criar um índice FULLTEXT especial:
ALTER TABLE table_name ADD FULLTEXT index_name (column_list);
O índice FULLTEXT pode ser usado para fins de pesquisa de texto.
Nas tabelas existentes
Para adicionar índice em qualquer tabela existente, a seguinte sintaxe pode ser usada:
ALTER TABLE table_name ADD INDEX (index_name);
Para excluir um índice em uma tabela, a instrução DROP INDEX é usada:
ALTER TABLE nome_tabela DROP INDEX (nome_índice);
Exibir informações de índice
Para listar todos os índices associados a qualquer tabela, é utilizado o comando SHOW INDEX.
SHOW INDEX FROM table_name\G
O '\G' é usado para criar a lista em formato vertical, isso evita o contorno de linhas longas.
Leia também: Perguntas e respostas da entrevista SQL
Índices descendentes
O InnoDB pode armazenar as entradas em ordem decrescente quando o índice é um índice decrescente. Assim, quando a ordem decrescente é solicitada na consulta, o otimizador escolhe esse índice. Isso é mais eficiente para consultas com cláusulas ORDER BY. Eles são suportados apenas pelo mecanismo de armazenamento InnoDB.
A sintaxe para criar ou adicionar índices descendentes é como alterar ou criar sintaxes usadas acima.
ALTER TABLE nome_tabela ADD nome_índice (nome_coluna desc, nome_coluna asc);
CREATE INDEX index_name ON table_name (col1_name desc, col2_name asc);
Índices invisíveis
Para marcar índices como indisponíveis para o otimizador de consulta, podem ser usados índices invisíveis. O MySQL atualiza índices invisíveis quando os dados nas colunas associadas ao índice são alterados. Os índices são visíveis por padrão e para torná-los invisíveis, você deve declarar explicitamente a visibilidade no momento da criação ou usando o comando ALTER TABLE.
Para criar um índice invisível, a seguinte sintaxe é usada:
CREATE INDEX nome_índice ON nome_tabela (c1, c2…) INVISÍVEL;
Para alterar a visibilidade dos índices existentes, a seguinte sintaxe é usada:
ALTER TABLE nome_tabela ALTER INDEX nome_índice [VISIBLE | INVISÍVEL];
Inscreva-se em cursos de desenvolvimento de software online das melhores universidades do mundo. Ganhe Programas PG Executivos, Programas de Certificado Avançado ou Programas de Mestrado para acelerar sua carreira.
Conclusão
O desempenho das consultas de pesquisa do MySQL pode ser melhorado usando índices. Eles podem ser definidos quando uma tabela é criada ou podem ser adicionados posteriormente se a tabela já estiver criada.
Este artigo forneceu uma breve descrição do que é um índice e por que ele é usado. A próxima seção explica como criar um índice no MySQL, como ele pode ser adicionado a uma tabela existente e como criar um índice exclusivo.
Os diferentes comandos para alterar tabelas e criar ou eliminar um índice também foram descritos juntamente com a exibição de informações de índice. Índices descendentes e invisíveis também foram brevemente explicados.
Em suma, aprender e praticar em todos os bancos de dados mencionados neste artigo aumentará sua experiência. Percorra a visão geral do curso, aprendizado e oportunidades de trabalho envolvidas com ele - plataformas como o upGrad oferecem cursos poderosos projetados por institutos de renome como o IIIT-B em Desenvolvimento de pilha completa do programa PG executivo.
