Índices MySQL – Aumentando a velocidade das suas consultas em até 100x

É isso mesmo!!! Você não leu errado. Com índices no banco de dados posso aumentar a velocidade das minhas consultas em 100 vezes e em alguns casos muito mais do que isso.

Já soube de um sistema que levava cerca de 30 minutos pra rodar uma determinada rotina e que depois do trabalho de um DBA, passou a levar alguns segundos. Boa parte dessa performance devemos aos índices.

Bom, o intuito desse artigo não é só falar o que é um índice e deixá-los na vontade. Vamos por a mão na massa e ver essa maravilha com nossos próprios olhos.

Entendendo o experimento

Para testar a funcionalidade dos índices vamos explicar o que vamos fazer aqui. Primeiro temos uma tabela agenda, com os campos ID, ddd e número, todos do tipo integer. Essa tabela é populada com 280 mil registros (o que é pouco, mas para um teste rápido já podemos perceber alguma diferença de performance). Em seguida duplicamos essa tabela, criamos índices em uma delas e na outra não. Logo após realizamos algumas consultas e comparamos a performance. Fácil né? Então…

Mãos à obra

Agora é a hora que todo mundo gosta… a hora de por a mão no código. Em primeiro lugar vamos criar duas tabelas idênticas no mysql:

create table agenda(
id int not null auto_increment primary key,
ddd int(3) not null,
numero int(8) not null
) ENGINE=MyISAM;

create table agenda_indexada(
id int not null auto_increment primary key,
ddd int(3) not null,
numero int(8) not null
) ENGINE=MyISAM;

CREATE INDEX numero on agenda_indexada(numero(4));

No script acima criamos um índice para o campo número, onde esse índice indexa de acordo com os 4 primeiros números, ou seja, o prefixo do telefone. Em nosso caso, como os números foram gerados aleatoriamente, o resultado pode ser até mais lento do que numa agenda real, onde os prefixos costumam repetir bastante.
Agora, vamos usar um script PHP para popular dados nelas, segue o código:

$cn = mysql_connect("localhost","usuario","senha");
for($i = 0; $i < 280000; $i++){
	$ddd = rand(100,999);
	$numero = rand(1000,9999)."".rand(1000,9999);
	mysql_query("INSERT INTO agenda(ddd,numero) VALUES(".$ddd.", ".$numero.")");
	mysql_query("INSERT INTO agenda_indexada(ddd,numero) VALUES(".$ddd.", ".$numero.")");
}
echo "script terminado!!!";

Agora temos duas tabelas com 280 mil registros cada, vamos aos testes. Em primeiro lugar, vamos buscar pelo número e ddd juntos:

select SQL_NO_CACHE * from agenda where ddd=426 AND numero=43117459;

Ao testar isso em meu ambiente de produção obtive os seguintes resultados:

tabela agenda: 0.3857
tabela agenda_indexada: 0.0294

Nessa situação o índice aumentou a performance da sql em 13.11 vezes. Isso já faz uma diferença razoável em determinados sistemas. Mas ainda não é tudo. Vamos fazer agora uma consulta apenas pelo número:

select SQL_NO_CACHE * from agenda where numero=43117459 limit 5;

Quando a consulta é apenas em campos indexados o resultado é bem mais visível. Veja só:

Tabela agenda: 0.4568
Tabela agenda_indexada: 0.0019

Nesse caso a consulta na tabela indexada foi 240.42 vezes mais rápido. Supimpa, né?!

Então é isso galera! Espero que tenham gostado do meu teste. Experimentem usar índices a partir de agora e não se esqueçam de comentar seus resultados e experiências.

Fonte: EstiloFacil.com

EstiloFacil

Mais artigos deste autor »

Tecnólogo, com experiência em desenvolvimento de sistemas web profissionalmente há mais de dois anos pela empresa WebCorpore. Especialidades: CodeIgniter, Doctrine, Magento e WordPress. Curto vídeos engraçados, MMORPGs e afins. Ah e a propósito, casado, futuro pai e atual servo do Deus vivo.


11 Comentários

Diego
2

Opa,

Excelente artigo, sem dúvida é essencial que desenvolvedores conheçam e utilizem os índices de maneira correta, afinal o ganho de velocidade em consultas nas tabelas com grande quantidades de registros é impressionante.

Sucesso!

Rodrigo Martins
4

Falando em índices, o que sempre me deixa na dúvida é como criar os índices. Por exemplo, se tenho pesquisas ordenando com os campos DATA e ID, crio um índice com os dois (em qual ordem?) ou crio dois índices (um com cada campo) pensando que posso ter pesquisas ordenando apenas com o ID?

Anderson
8

Não não alessandro, índice é uma tarefa pesada, vc não pode sair fazendo a torto e a direito, senão sua busca vai é ficar mais lenta.
Ali eu indexei só o número pq imaginei que num sistema de busca nessa tabela, seria o campo mais procurado, indexei apenas os 4 primeiros algarismos pq imaginei um autocomplete que dá um select a kda numero digitado a partir do quarto.. saca?

Rodrigo
10

indiferentemente se eu criei o indice apenas para o campo numero a tabela sempre vai ser mais rapida ou tenho que criar o indice para todos os campos q eu chamo em um select?

exemplo
tenho a tabela com os campos
codigo, departamento, titulo,texto,descricao
terei que criar indice para todos os campos ou apenas para o campo codigo?

Deixe seu comentário

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


oito + 9 =

Você pode usar estas tags e atributos de HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>