sexta-feira, 13 de fevereiro de 2015

Dica rápida... como um banco relacional usa índices

Olá pessoal, tudo bem?

Um banco de dados relacional utiliza os índices como meio de acelerar as consultas.
A intenção é simples.. pegue um livro que você gosta, por exemplo a Bíblia.. digamos que você não seja um cristão fervoroso que conheça várias passagens (eu mesmo conheço poucas).. Em que livro, página e linhas fica a passagem das bodas de Caná, onde Jessus transformou Água em Vinho?

Como não sei de cabeça, fui no índice da bíblia e procurei.. evangelho de João 2, 1 - 11.

Caso não houvesse o índice, eu teria que percorrer todos os evangelhos, até encontrar minha passagem favorita da Bíblia.

O banco de dados também faz a mesma coisa, porém quem cria os índices do banco? O próprio banco? Nós os desenvolvedores. Nós somos os criadores da informação e é nosso dever facilitar as coisas.

Quando inserimos ou atualizamos um registro em uma tabela, o banco relacional avalia o novo registro e atualiza os índices daquela tabela. Se temos portando um índice para um campo de data, o banco de dados é capaz de selecionar rapidamente os registros de vendas feitos no dia 13/02/2015 (sexta-feira TREZE!).

Não é muito bom sair criando índices para TODOS os campos da tabela, porque isso fará com que o tamanho da tabela cresça muito. Índice também ocupa espaço. Voltando a comparação com a Bíblia.. imagine se metade da Bíblia fossem índices e o resto os livros sagrados? Seria prático?

Crie portanto índices necessários às chaves estrangeiras e a suas buscas.

Voltemos ao exemplo das vendas..
Queremos criar uma tabela hipotética Vendas, que armazene o código próprio, o código do cliente, a data da venda e valor. Nosso cliente quer um relatório em que seja possível ver o valor vendido por período ou dia e um detalhamento com as maiores vendas do dia.

Para atender ao nosso cliente, criamos um índice (aqui chamei de W1 em homenagem ao nosso ERP na Móveis Simonetti), com os campos data e valor.
INDEX `w1` (`data` ASC, `valor` DESC))
Dessa forma, com um único índice, atendemos a 2 ou mais buscas do sistema. Cliente feliz, sistema rápido e mais tempo para fazermos novos recursos pro sistema.
Mais informações sobre índices e seu uso no Mysql podem ser encontrados no seguinte link: http://dev.mysql.com/doc/refman/5.6/en/optimization-indexes.html

Nos vemos logo!

Abração!

2 comentários:

  1. Olá Marcelo, tdo bem? Comecei acompanhar seu blog, obrigado pelo material, dicas e afins que estão disponíveis no blog!

    Um certo dia li em algum lugar que não é recomentado criar no mesmo Indice duas colunas com declaração de "Order" diferentes:
    INDEX `w1` (`data` ASC, `valor` DESC))
    sendo o recomendado que tanto DATA como VALOR sejam da mesma ordem, ASC ou DESC:
    INDEX `w1` (`data` ASC, `valor` ASC)) ou INDEX `w1` (`data` DESC, `valor` DESC)).

    Isso procede? Qual o impacto par o otimizador?

    []'s


    ResponderExcluir
    Respostas
    1. Boa tarde Gigu.

      Perguntas como as suas são ótimas.. Não me lembro agora de alguma documentação sobre sua dúvida. Então farei uma pesquisa, testes e se necessário abrirei um chamado no MySQL AB se ainda tivermos dúvidas.

      Assim teremos a verdadeira informação.

      O arquivo de índices de uma tabela é um arquivo a parte dos dados. Então o Optimizer busca baseado na cláusula WHERE ou JOIN os dados nesse arquivo e então vai buscar os dados. Se o índice não for suficiente, ele termina a query fazendo varredura em disco (que é o que não queremos que ocorra). Então vou estudar a construção do índice e o caso que você perguntou e postarei a resposta aqui. Quem sabe até como um novo Post.

      Abração.

      Excluir