MySQL Tutorial de índice – Criar, adicionar e descartar

Por: Marcus Allen Marcus Allen
Hours Atualização do

O que é um índice?

Índices em MySQL classificar os dados de maneira sequencial organizada. Eles são criados na(s) coluna(s) que serão utilizadas para filtrar os dados. Pense em um índice como uma lista ordenada em ordem alfabética. É mais fácil procurar nomes que foram classificados em ordem alfabética do que aqueles que não foram classificados.

Usar um índice em tabelas que são atualizadas com frequência pode resultar em desempenho insatisfatório. Isto é porque MySQL cria um novo bloco de índice sempre que dados são adicionados ou atualizados na tabela. Geralmente, os índices devem ser usados ​​em tabelas cujos dados não mudam com frequência, mas são muito usados ​​em consultas de pesquisa selecionadas.

O que usar um índice?

Ninguém gosta de sistemas lentos. O alto desempenho do sistema é de primordial importância em quase todos os sistemas de banco de dados. A maioria das empresas investe pesadamente em hardware para que as recuperações e manipulações de dados possam ser mais rápidas. Mas há limites para os investimentos em hardware que uma empresa pode fazer. Otimizar seu banco de dados é uma solução melhor e mais barata.

MySQL Índice

A lentidão no tempo de resposta geralmente se deve ao fato dos registros serem armazenados aleatoriamente nas tabelas do banco de dados. As consultas de pesquisa precisam percorrer todos os registros armazenados aleatoriamente, um após o outro, para localizar os dados desejados. Isso resulta em bancos de dados de baixo desempenho quando se trata de recuperar dados de tabelas grandes. Conseqüentemente, índices são usados ​​para classificar os dados para facilitar a pesquisa.

Sintaxe: Criar Índice

Os índices podem ser definidos de 2 maneiras

  1. No momento da criação da tabela
  2. Depois que a tabela for criada

Exemplo:

Para nosso myflixdb, esperamos muitas pesquisas no banco de dados pelo nome completo.

Adicionaremos a coluna “full_names” ao Index em uma nova tabela “members_indexed”.

O script mostrado abaixo nos ajuda a conseguir isso.

CREATE TABLE `members_indexed` (
  `membership_number` int(11) NOT NULL AUTO_INCREMENT,
  `full_names` varchar(150) DEFAULT NULL,
  `gender` varchar(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `physical_address` varchar(255) DEFAULT NULL,
  `postal_address` varchar(255) DEFAULT NULL,
  `contact_number` varchar(75) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`membership_number`),INDEX(full_names)
) ENGINE=InnoDB;

Execute o script SQL acima em MySQL ambiente de trabalho contra o “myflixdb”.

MySQL Criar índice

A atualização do myflixdb mostra a tabela recém-criada chamada member_indexed.

"Não te" A tabela member_indexed possui “full_names” no nó de índices.

À medida que a base de membros se expande e o número de registros aumenta, as consultas de pesquisa na tabela member_indexed que utilizam as cláusulas WHERE e ORDER BY serão muito mais rápidas em comparação com as realizadas na tabela de membros sem o índice definido.

Adicionar sintaxe básica de índice

O exemplo acima criou o índice ao definir a tabela do banco de dados. Suponha que já tenhamos uma tabela definida e as consultas de pesquisa nela sejam muito lentas. Eles demoram muito para retornar os resultados. Depois de investigar o problema, descobrimos que podemos melhorar bastante o desempenho do sistema criando INDEX na coluna mais usada na cláusula WHERE.

Podemos usar a seguinte consulta para adicionar índice

CREATE INDEX id_index ON table_name(column_name);

Suponhamos que as consultas de pesquisa na tabela de filmes sejam muito lentas e queremos usar um índice no “título do filme” para agilizar as consultas, podemos usar o seguinte script para conseguir isso.

CREATE INDEX `title_index` ON `movies`(`title`);

A execução da consulta acima cria um índice no campo de título na tabela de filmes.

Isso significa que todas as consultas de pesquisa na tabela de filmes usando o “título” serão mais rápidas.

As consultas de pesquisa em outros campos da tabela de filmes ainda serão mais lentas em comparação com aquelas baseadas no campo indexado.

Nota: Você pode criar índices em múltiplas colunas, se necessário, dependendo dos campos que pretende usar para seu mecanismo de pesquisa de banco de dados.

Se quiser visualizar os índices definidos em uma tabela específica, você pode usar o script a seguir para fazer isso.

SHOW INDEXES FROM table_name;

Vamos agora dar uma olhada em todos os índices definidos na tabela movies no myflixdb.

SHOW INDEXES FROM `movies`;

Executando o script acima em MySQL bancada contra o myflixdb nos dá resultados no índice criado.

Nota: As chaves primária e estrangeira na tabela já foram indexadas por MySQL. Cada índice tem seu próprio nome exclusivo e a coluna na qual ele está definido também é mostrada.

Sintaxe: índice de queda

O comando drop é usado para remover índices já definidos em uma tabela.

Pode haver momentos em que você já tenha definido um índice em uma tabela que é atualizada com frequência. Você pode querer remover os índices dessa tabela para melhorar o desempenho das consultas UPDATE e INSERT. A sintaxe básica usada para eliminar um índice em uma tabela é a seguinte.

DROP INDEX `index_id` ON `table_name`;

Vejamos agora um exemplo prático.

DROP INDEX ` full_names` ON `members_indexed`;

A execução do comando acima elimina o índice com id `full_names` da tabela member_indexed.

Resumo

  • Os índices são muito poderosos quando se trata de melhorar significativamente o desempenho de MySQL consultas de pesquisa.
  • Os índices podem ser definidos ao criar uma tabela ou adicionados posteriormente após a tabela já ter sido criada.
  • Você pode definir índices em mais de uma coluna de uma tabela.
  • O SHOW INDEX FROM table_name é usado para exibir os índices definidos em uma tabela.
  • O comando DROP é usado para remover um índice definido em uma determinada tabela.

Você pode gostar: