Como otimizar um banco MySQL usando índices

Esta vídeo-aula mostra como podemos melhorar (muito) a performance do nosso mysql apenas criando corretamente os índices no banco de dados.

O vídeo é auto-explicativo, e aqui somente indicaremos os passos:

1) Você deverá configurar índices para os campos que usar no WHERE. O primeiro passo é fazer um planejamento e tentar encontrar os campos que estão tomando tempo e recursos do servidor.

2) Usando o phpMyAdmin, vá em Estrutura.

3) Desça um pouco a tela, e na tabela de índices, observe a cardinalidade de sua chave primária ou índice. Em nosso exemplo, temos uma tabela bastante grande, com uma chave primária de cardinalidade superior a 100 mil.

O que isso significa? Quando fizermos uma query nesta tabela, o mysql irá procurar em cada um dos 100 mil registros, um por um.

Com o índice criado corretamente, de um modo geral, a cardinalidade da chave precisa ficar entre 50 e 400. Tudo depende da sua aplicação, do volume de dados, etc.

Em nosso teste, a query demorou quase 1 minuto para ser executada (sem os índices configuradas).

4) No phpMyAdmin vá em “SQL” . Para criar o índice, digite:

CREATE INDEX <nome_do_indice> ON <tabela> ( <campo>(<tamanho>) )

no nosso exemplo:

CREATE INDEX nomedomeuindice ON contador ( prot(2) )

Mas o que significa de fato este “tamanho”?

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

Criando uma chave com 5 posições, o mysql organizará da seguinte forma:

Chave Referências
João João Vitor
João Guilherme
João Francisco
Paulo Paulo Fagundes
Paulo Roberto
Paulo Amorim
José José Cruz
José Silva

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!

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 !

Como faço para descobrir qual campo preciso indexar?

Basta executar suas queries no phpMyAdmin, colocando “desc” antes. 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

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

hhchat