O que é um índice?
Os índices no MySQL classificam os dados de maneira sequencial organizada. Eles são criados na (s) coluna (s) que serão usadas para filtrar os dados. Pense em um índice como uma lista classificada em ordem alfabética. É mais fácil pesquisar nomes que foram classificados em ordem alfabética do que aqueles que não foram classificados.
Usar um índice em tabelas que são freqüentemente atualizadas pode resultar em baixo desempenho. Isso ocorre porque o MySQL cria um novo bloco de índice toda vez que os 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 extrema 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 sejam mais rápidas. Mas há um limite para os investimentos em hardware que uma empresa pode fazer. Otimizar seu banco de dados é uma solução mais barata e melhor.
A lentidão no tempo de resposta geralmente se deve ao fato de os 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, os índices são usados para classificar os dados para facilitar a pesquisa.
Sintaxe: Criar Índice
Os índices podem ser definidos de 2 maneiras
- No momento da criação da mesa
- Depois que a tabela foi criada
Para nosso myflixdb, esperamos muitas pesquisas no banco de dados por nome completo.
Vamos adicionar a coluna "full_names" ao índice 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 no ambiente de trabalho MySQL no "myflixdb".
Atualizar o myflixdb mostra a tabela recém-criada chamada members_indexed.
"Nota" a tabela members_indexed tem "full_names" no nó de índices.
Conforme a base de membros se expande e o número de registros aumenta, as consultas de pesquisa na tabela members_indexed que usam 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 sobre ela sejam muito lentas. Eles demoram muito para retornar os resultados. Depois de investigar o problema, descobrimos que podemos melhorar muito o desempenho do sistema criando INDEX na coluna mais comumente usada na cláusula WHERE.
Podemos usar a consulta a seguir para adicionar o índice
CREATE INDEX id_index ON table_name(column_name);
Vamos supor que as consultas de pesquisa na tabela de filmes são muito lentas e queremos usar um índice no "título do filme" para acelerar as consultas, podemos usar o seguinte script para isso.
CREATE INDEX `title_index` ON `movies`(`title`);
Executar a 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 na tabela de filmes, no entanto, ainda são mais lentas em comparação com aquelas baseadas no campo indexado.
Nota: Você pode criar índices em várias colunas, se necessário, dependendo dos campos que pretende usar para seu mecanismo de pesquisa de banco de dados.
Se quiser ver os índices definidos em uma tabela específica, você pode usar o seguinte script para fazer isso.
SHOW INDEXES FROM table_name;
Vamos agora dar uma olhada em todos os índices definidos na tabela de filmes no myflixdb.
SHOW INDEXES FROM `movies`;
Executar o script acima no ambiente de trabalho MySQL no myflixdb nos dá resultados no índice criado.
Nota: As chaves primária e estrangeira na tabela já foram indexadas pelo MySQL. Cada índice tem seu próprio nome exclusivo e a coluna na qual ele é definido também é exibida.
Sintaxe: Drop index
O comando drop é usado para remover índices já definidos em uma tabela.
Pode haver momentos em que você já definiu um índice em uma tabela que é atualizada com freqüência. Você pode querer remover os índices de tal tabela para melhorar o desempenho das consultas UPDATE e INSERT. A sintaxe básica usada para descartar 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`;
Executar o comando acima remove o índice com id `full_names` da tabela members_indexed.
Resumo
- Os índices são muito poderosos quando se trata de melhorar muito o desempenho das consultas de pesquisa do MySQL.
- 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 em uma tabela.
- O SHOW INDEX FROM nome_tabela é usado para exibir os índices definidos em uma tabela.
- O comando DROP é usado para remover um índice definido em uma determinada tabela.