Parceiros
[X] Fechar

Seu nome:

Seu email:

Nome do amigo:

Email do amigo:

A importância dos índices em um banco de dados

Telegram Whats Twitter Email Mapa de imagens. Clique em cada uma das imagens
domingo, 4 de julho de 2010 - 01:47
Escolhi este assunto de índices de tabelas para ser o primeiro post da área de informática do site porque quando era desenvolvedor de sistemas desktop para poucos clientes (acessos concorrentes) não dava muita importância para este tema, ou então porque nas organizações onde trabalhava havia uma área específica de DBA (Database Adminstrator) que cuidava dessa parte de "tunning" de banco de dados.

Porém, quando passei a "trabalhar" (fazemos por hobby e por amor ao Vasco, sem ganhar nada com isso) no SuperVasco, ficamos de cabelos brancos com isso, porque quando havia aquelas notícias bombásticas, chegamos a ter (ainda temos até hoje) 1.000 usuários por minuto acessando o site. Resultado: Eram constantes as quedas de performance do site, quando não acontecia o maldito "too many connections".

Para ilustrar a importância que se deve dar ao tema, criei um bdteste no MySQL, com duas tabelas: Produtos e Fornecedores. Para efeito de ilustração, suponha que cada produto só pode ser vendido por um fornecedor.

A estrutura das tabelas é simples: Codigo e Nome do produto e Codigo e Nome do fornecedor. Para se recuperar todos os produtos vendidos por um fornecedor, o seguinte comando é executado:

s.elect p.strNomeproduto, f.strNomeFornecedor from produtos p join fornecedores f on f.lngCodFornecedor = p.lngCodFornecedor where f.lngCodFornecedor = 2


Executar o explain (comando do MySQL que informa como o o mecanismo do banco irá executar consultas) nos mostra o seguinte resultado:

ids.elect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEfconstPRIMARYPRIMARY4const1(null)
1SIMPLEpALL    4Using where


Explicando o explain

Três indicativos de que algo não vai bem:
  • O valor ALLna coluna type
  • Os campos Possible keys e keys estarem com valor nulo

Isso indica que não será usado nenhum índice para varrer a tabela p (produtos). Ou seja, independente do que você quiser executar, o MySQL vai varrer a tabela toda. Nesse exemplo, o estrago parece não ter sido muito grande, porque foram varridos só quatro registros, multiplicadas as duas tabelas. Mas há que se considerar que a tabela p tinha quatro registros, ou seja, foram varridos todos os registros dessa tabela.

Imaginaram o estrago se essa tabela tivesse 10.000 itens? Como a quantidade de registros é produto cartesiano entre as tabelas, o valor total de registros é a multiplicação das linhas desse explain. Ou seja, se houvesse 50 fornecedores, ele teria varrido 50 vezes 10.000 registros.

Ok, mas como resolver isso?

Criando uma foreign key para a tabela de fornecedor na tabela de produtos . Assim, independente do número de produtos, o motor do banco de dados varrerá, na tabela de produtos, apenas o fornecor cujo código satisfaça à condição colocada no "where".

Vejam como ficou a tabela, após a criação da foreign key:

ids.elect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEfconstPRIMARYPRIMARY4const1(null)
1SIMPLEprefidxFornecedoridxFornecedor4const2(null)


Notem que os campos possible_keys e key (chave que o MySQL entendeu por bem usar) estão preenchidos e que o número de registros varridos caiu de 4 para 2.

Para finalizar, as dicas que dou para o tema são as seguinte: Estude bem o seu modelo relacional e teste a performance das suas consultas antes de sentar no ASP ou no PhP. Depois, pode ser como trocar um pneu de carro com ele em movimento... mrgreen

Outra coisa: O exemplo dado é para MySQL, mas o mesmo princípio vale para qualquer banco de dados, porque isso é baseado em teoria de conjuntos, base para todos os bancos de dados.

Espero que essa dica tenha sido útil!

Abraços!

Fonte: Coelho de Programa

Leia mais sobre: índice, banco de dados, mysql, explain, foreign key

Comentários

Quer comentar? Clique aqui para o login.
Copyright © Marcelo Coelho