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).

CRIANDO A TABELA

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';

Usando o Select Comum

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))

CRIANDO O ÍNDEX

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';

Efetuando o Select Otimizado

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

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

19 Responses

  1. Nunca usei nada além do primary. huahauuaa ( q horrível 😛 )
    Excelente artigo!!!

    Fale sobre as outras opções também(primary, unique e fulltext) a nível de programador. Pra sabermos como é o processo. Sua explicação tornou as coisas bem claras.

    Valew

  2. Na verdade, tá bem mal explicado isso aí. Vc citou trigger onde não tem nada a ver. A forma que o índice é visto pelo SGBD é bem diferente disso, pra entender esse conceito vc precisa entender o que é árvore. E não tou falando de árvore de jardim tá.

    1. Calma aê mano, ele fez esse artigo pra iniciantes, rsrs, mas como vc falou pra entender todo esse processo é necessário entender árvores, busca binária e outras coisitas mais. 😉

  3. ta bem mal explicadinho isso ai.

    na criação da tabela não tem nada especificando que o campo numero é unico!
    sendo assim,com a consulta que vc fez, o sgbd vai varrer todos os registros ta tabela, verificando onde existe o campo numero = 25, e vai adicionando no result set, e depois vai retornar todas as ocorrencias desta condição.

    mas quando vc cria o indice. vai ocorrer da primeira forma que vc descreveu! ou seja, quando o sgbd achar a condição que ta procurando vai retornar, pois ja que este campo possui um indice, ele é unico.

  4. Ao pesquisar com índices, o sgbd utiliza pesquisas binárias, onde a tabela de índices é dividida ao meio e são testados os números da extremidade com o número pesquisado. Caso não corresponda, todos os registros dessa metade são desprezados, a metade restante é dividida ao meio e o teste é reiniciado. No exemplo acima, a tabela seria divida no registro 15. os registros de 1 a 15 são desprezados e o número é encontrado na segunda metade, sendo realizadas no máximo duas consultas e não 25 como foi mostrado, por isso a pesquisa é mais rápida.

    1. Exatamente isso! E se bem analisado, veremos que é matemática básica pura, sem mistérios. Curiosamente, alguns falam que, para ser programador, não é necessário ser um expert em matemática, concordo, mas deve sim conhecer, no mínimo, o básico!

  5. Muito bom o seu artigo, foi a solução para mim, onde tenho uma tabela com mais de um milhão de registro, se não fosse este artigo esta dando nó em ponta de faca… Obrigado mesmo.

  6. Na verdade o MySQL trabalha com Árvores B, ou Hash para criação de índices, o que deixa a pesquisa mais otimizada!

  7. Realmente, pode até funcionar, mas achei mal explicado…

    CREATE INDEX index_numero ON tbl_numeros (numero(10))

    index_numero é um nome referencial qualquer, correto?

    apos o ON tbl_numero
    temos (numero(10))

    onde "numero" seria o campo dentro da tabela ON tbl_numero mas agora me explica o por que deste "(10)" ?

    Obrigado

    1. Elvis, creio que esse número seria o número máximo de linhas que o índice terá

      1. o '10' ele utilizará somente 10 caracteres… não entendi também o porque de utilizar isso num campo int.
        Sei que é útil com campos text. daí se você tem um campo muito grande tipo varchar 60
        vc pode fazer um índice de 30 por ex. daí o índice só irá funcionar nos 30 primeiros caracteres…
        Isso é útil para otimização de espaço, porque os índices, aumentam consideravelmente o tamanho da tabela;;
        quem trabalha com tabelas grandes sabe do que estou falando…

        1. Pelo que eu entendi seria o CHAR (10) o limite do campo assim como o Taito.

          Excelente materia deu pra entender direitinho como funciona vou usar ELE agora 🙂

        2. O campo 'numero' é char e não int….

          —– 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`)
          );

        3. Voce pode restringir a quantidade de caracteres a serem utilizados mesmo no campo numerico.

Leave a Reply to Israel Cancel reply

Your email address will not be published. Required fields are marked *


Como Criar Um Site, Blog - WebMaster.pt