Criamos um tutorial em vídeo sobre “Como Otimizar um Banco MySQL usando índices”. Confira no vídeo abaixo, publicado no YouTube:
Esta vídeo-aula mostra como podemos melhorar de forma significativa a performance do nosso MySQL apenas criando corretamente os índices no banco de dados.
Portanto, como o vídeo é auto-explicativo, neste artigo estaremos apenas indicando os os passos.
Passos de como otimizar um banco MySQL usando Índices
1) Inicialmente, você deverá configurar índices para os campos que usar no WHERE. Dessa forma, o primeiro passo é fazer um planejamento e tentar encontrar os campos que estão tomando tempo e recursos do servidor.
2) Posteriormente, usando o phpMyAdmin, localize e selecione a opção “Estrutura“.
3) Então, desça um pouco a tela, e na tabela de índices, observe a cardinalidade de sua chave primária ou índice MySQL. Em nosso exemplo, temos uma tabela grande, com uma chave primária de cardinalidade superior a 100 mil.
Mas, o que isso significa? Isso significa que, quando fizermos uma query nesta tabela, o MySQL irá procurar em cada um dos 100 mil registros, um por um.
Porém, com um índice criado corretamente, de um modo geral, a cardinalidade da chave precisa ficar entre 50 e 400. Mas, tudo dependerá da sua aplicação, do volume de dados, etc.
Dessa forma, ao configurarmos os índices, conseguiremos então diminuir nossa busca para um volume muito menor de dados. Portanto, o desempenho do nosso banco de dados será otimizado significativamente, principalmente em exemplos como o nosso, contendo um volume muito grande de dados.
Em nosso teste, sem configurar os índices, a query demorou quase 1 minuto para ser executada.
Enfim, vamos então prosseguir para a crição dos índices:
4) No phpMyAdmin vá em “SQL” . Para criar o índice, digite:
CREATE INDEX <nome_do_indice> ON <tabela> ( <campo>(<tamanho>) )
Em nosso exemplo utilizamos o seguinte comando:
CREATE INDEX nomedomeuindice ON contador ( prot(2) )
Mas o que significa de fato este “tamanho” dos Índices MySQL?
Considere que estejamos criando um índice de tamanho (5). Caso os registros do nosso banco sejam:
- João Vitor
- Paulo Fagundes
- José Cruz
- João Guilherme
- José Silva
- Paulo Roberto
- João Francisco
- Paulo Amorim
Sem criarmos índices e chaves, ao realizar uma pesquisa, nosso Banco de Dados iria passar por todos os 8 nomes acima. Porém, ao criarmos uma chave com 5 posições, o MySQL organizará da seguinte forma:
Chave | Referências |
João | João Vitor |
Paulo | Paulo Fagundes |
José | José Cruz |
Ou seja, quando fizermos uma query: SELECT * FROM cadastro WHERE nome = “João Vitor” , ao invés do MySQL percorrer todos os 8 registros da nossa tabela-exemplo, ele irá verificar apenas os 3 índices que criamos.
Conforme mostrado em nosso vídeo, esta otimização dentro de uma tabela com muitos registros, irá lhe economizar bastante tempo e processamento!
Dessa forma, com a aplicação de índices no nosso banco de dados com mais de 100 mil registros, reduziu o tempo da query de quase 1 minuto para menos de 0,01 segundos! Portanto, fica claro o quanto esse método consegue otimizar muito o desempenho do nosso Banco de Dados apenas utilizando os Índices no MySQL.
Como faço para descobrir qual campo preciso indexar?
Para descobrir qual campo você precisa Indexar, basta executar suas queries no phpMyAdmin, colocando “desc” antes. Dessa forma, o MySQL irá lhe mostrar passo a passo o caminho que ele está percorrendo para lhe fornecer um resultado.
Exemplo:
desc SELECT * FROM `contador` WHERE prot='03554d6b'
Resultado:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE contador ref nomedomeuindice nomedomeuindice 4 const 462 Using wher
Portanto, você poderá criar seus Índices do MySQL com base nessa pesquisa.
