MySQL – Otimização Criando Índices

As pessoas costumam dizer que quem trabalha com programação e banco de dados aprende algo novo a cada dia!

Posso dizer que isso é uma verdade, já que a informática nos surpreende a cada dia.

Existem vários recursos que ajudam os Programadores a terem soluções mais rápidas e poderosas. Uma delas é a criação de índices em suas tabelas.

Trabalhando Com Índices

Imagine uma base de dados pequena e com poucos registros. Seu desempenho é ótimo, correto?

Vamos ser mais claros?

Digamos que você tenha uma tabela de clientes / usuários e nesta tabela existam de 5 a 10 registros.

Ao efetuar uma busca, as informações virão rapidamente em tela!

Agora pense… Você já imaginou fazer uma busca na mesma base de dados tendo ela uma grande quantidade de registros?

Bem… o problema não é a quantidade de registros e sim a forma que os Programadores efetuam suas consultas!

Abaixo vamos simular alguns exemplos onde você poderá conhecer melhor sobre este assunto.

Criando As Tabelas Na Base De Dados

Antes de partirmos para a programação, abra o ficheiro script-sql.txt (disponível em anexo) e cole seu conteúdo no Programa utilizado para gerenciamento da base de dados.

Em nossas matérias de MySQL utilizamos como padrão o MySQLFront. Caso você queira utilizar este programa, clique em SQL Editor e cole o conteúdo do script (conforme imagem abaixo).

Veja abaixo o código a ser inserido:

----- CRIA A TABELA TBL_NUMEROS -----



CREATE TABLE `tbl_numeros` (

  `id_numero` int(11) NOT NULL auto_increment,

  `numero` char(10) default NULL,

  PRIMARY KEY  (`id_numero`)

);



----- INSERE CONTEÚDO NA TBL_NUMEROS -----



INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (1,1);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (2,2);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (3,3);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (4,4);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (5,5);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (6,6);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (7,7);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (8,8);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (9,9);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (10,10);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (11,11);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (12,12);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (13,13);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (14,14);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (15,15);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (16,16);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (17,17);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (18,18);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (19,19);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (20,20);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (21,21);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (22,22);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (23,23);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (24,24);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (25,25);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (26,26);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (27,27);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (28,28);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (29,29);

INSERT INTO `tbl_numeros` (`id_numero`,`numero`) VALUES (30,30);

Após criar a tabela e inserir as informações na base de dados, vamos partir para os testes!

Usando O Select Comum

Suponhamos que você tenha uma tabela com números que vão de 1 a 30. Você podería usar a consulta a seguir (veja o código e imagem a seguir):

SELECT numero FROM tbl_numeros WHERE numero = '25';

Se você verificar bem, verá que a resposta do “Execution Time” foi de “0.09″!

Isso acontece porque até encontrar o número “25″ (conforme o select acima) o MySQL usará a lógica a seguir:

• POSIÇÃO 1: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 2: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 3: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 4: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 5: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 6: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 7: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 8: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 9: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 10: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 11: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 12: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 13: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 14: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 15: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 16: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 17: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 18: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 19: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 20: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 21: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 22: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 23: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 24: o campo numero é igual a 25? NÃO então vamos ao próximos registro;
• POSIÇÃO 25: o campo numero é igual a 25? SIM – Então retorne em tela as informações solicitadas.

É ruim ler registro por registro, não? Se o MySQL pudesse responder, diría a mesma coisa Vamos conhecer agora uma forma mais prática de se trabalhar?

Criando O Índice

No exemplo abaixo, vamos usar um índice para o campo número em “tbl_numeros”.

Antes de usar a solução, vamos criar junto ao MySQL este índice!

Siga a sintaxe abaixo descrita (conforme código e imagem a seguir):

----- CRIANDO UMA INDEX PARA O CAMPO NUMERO EM TBL_NUMEROS -----



CREATE INDEX index_numero ON tbl_numeros (numero(10))

Quem trabalha com SQL não terá dificuldades, já que no MSSQL trabalhamos com triggers (que usam estrutura bem próximas). Em todo caso, não há dificuldade… É só seguir a estrutura acima!

Efetuando O Select Otimizado

Abaixo efetuaremos um select otimizado e veremos também seu tempo de execução (conforme código e imagem a seguir):

----- SELECIONANDO A INDEX DA TABELA TBL_NUMEROS -----



SELECT numero FROM tbl_numeros USE INDEX (index_numero) WHERE numero = '25';

Observe que em “Execution Time” diferente da primeira opção, o tempo é de “0.02″.

Como ele fuciona?

Na prática o sistema fará uma média entre o primeiro e último registro e trará em tela as informações solicitadas!

Claro, não é exatamente desta forma que funciona, mas… como programador, “imagino” que ele busque as informações pulando registros.

Isso dá uma vantagem muito maior, já que ele trás os dados em tela em tempo record (menos da metade do tempo).

Bem… ficamos por aqui com mais esta matéria.

Caso você queira conhecer mais sobre esta matéria, poste suas dúvidas e observações!

Estaremos falando mais sobre este e outros assuntos nas próximas edições.

ficheiros.zip