Otimizando banco de dados

[André H. Ferreira 0]

Hoje venho posta sobre a maravilha dos índices em bancos de dados, e algumas dicas de performance. Coisas que não são levados em conta pelos desenvolvedores menos experientes em bancos, hoje serão comentadas neste post.

 

Até pouco mais de 3 meses eu não sabia de coisas muito avançadas de banco de dados, visto que nunca precisei, porém com o projeto em que trabalho atualmente, nosso banco de dados trabalha com milhões de registros, tabelas gigantes e temos que agilizar a pesquisa ao máximo, mesmo tendo tantos registros, agora vou passa algumas dicas no qual tive que aprender sozinho com muita pesquisa, juntamente com meu amigo e chefe Davi Pozzi.

 

Índices é a maravilha por trás dos bancos: Em vários testes, verifiquei que ao indexar campos com muitas requisições, o resultado apresentado em menos tempo(resultados consideráveis de redução), eu ainda não entendo o processo interno que os bancos fazem porém são eficientes.

 

Evite usar o comando LIKE: Evite ao máximo utilização do comando LIKE pois ao pesquisar várias palavras separadas por % o SELECT pode fica muito demorado. A melhor solução para pesquisas em linguagem natural de várias palavras é utilizar Full Text Search, descrito abaixo.

 

Nunca utilize * em consultas: Esta dica é extremamente valiosa, vou explicar por que. Os bancos de dados atuais utilizam um 'banco guias' para gerenciamento dos schemas(bancos de dados), no caso do MySQL todas as informações de databases, tabelas e campos são cadastrados em information_schema, quando você utilizar * o banco internamente precisa verificar no information quais os campos da tabela no qual deseja resultados, caso o SELECT tenha JOIN esse processo é feito para todas as tabelas, isso já diminui a performance, principalmente se o banco for compartilhar, onde deverá haver muitas informações no information_schema. Por isso fica a dica, é melhor declarar todos os campos que deseja retorna, NUNCA UTILIZE *.

 

Conferências de strings grandes, utilize MD5: Caso haja necessidade de fazer uma comparação de string em algum momento do desenvolvimento é aconselhável criar um campo contendo o MD5 da string que deseja pesquisar, associar ao campo um índice do tipo HASH, e fazer comparação. Este processo irá diminui muito o tempo de resposta da query. É claro que este tipo de pesquisa somente é válida para valores que devem ser exatamente iguais, para pesquisa por referencia é necessário usar LIKE ou Full Text Search.

 

Criar índices compostos em pesquisas com mais de um filtro: Caso haja verificações que utilizam mais de um parâmetro da tabela para filtro, é recomentado a criação de índices compostos pelos itens a serem filtrados, este processo melhora consideravelmente a pesquisa.

 

Evite uso de JOIN, DISTINCT e GROUP BY: Evite ao máximo utilizar estes processos pois eles gastam muito recurso do servidor para executar em tempo razoável, principalmente o DISTINCT.

 

Somente utilize índice UNIQUE em último caso: Os índices único são extremamente prejudiciais a performance da tabela visto que a cada INSERT o banco irá verificar todos os registros antes de inserir, por isso somente utilize-o em ultimo caso. NUNCA UTILIZE ESSE ÍNDICE EM TABELAS QUE SÃO MUITO PESQUISADAS.

 

Abuse das FUNCTIONS e STORED PROCEDURE: Todo processo que puder ser feito pelo banco deve ser feito nele, as funções e procedimentos são bons pois:

 

* São fáceis de alterar e não requer alteração no código fonte, que muitas vezes pode esta criptografado ou encodado.

* O tempo de resposta do processamento de uma função com dados de banco são incrivelmente mais rápidos que em linguagens como PHP, ASP, Ruby e Java, visto que há necessidade de transmitir os dados para a linguagem para que haja o processamento.

* Que as funções podem ser reaproveitadas em outras linguagens.

 

Neste caso a dica é: Sempre utilize stored procedure e functions quando for possível.

 

Tudo que exige muito tempo para processamento, utilize arquivo txt: Basicamente a dica é, tudo que requer mais de 2seg para executar, ou utilize processo distribuído, ou armazene a informações em TXT e crie Jobs para executar atualização deste arquivo, um exemplo deste tipo de abordagem é no site http://www.vigiadepreco.com.br, a quantidade de produtos cadastrados, e o total de lojas do sistema são armazenadas em arquivo texto e atualizada a cada 20min pelo Linux.

 

Otimização da tabela: Toda tabela que possui índice, e que frequentemente altera ou deleta registros devem ser otimizadas, pelo menos um vez por semana, este processo é demorado e recomendado a ser feito durante a madrugada, no MySQL o comando para otimizar a tabela é OPTIMIZE TABLE.

 

Full Text Search: Por ultimo a dica extraordinária do meu querido chefe(muito puxa saco neh kkk). Basicamente este item fala sobre o índice tipo FULLTEXT do MySQL, este tipo de índice possibilita pesquisas de alto nível e alta performance em linguagem natural para campos tipo CHAR, VARCAHR ou TEXT, com isso não é necessário utilização de vários LIKE dentro do query, para mais informações acesse http://dev.mysql.com/doc/refman/4.1/pt/fulltext-search.html.

 

Espero que tenha ajudado, Obrigado pela atenção

Para mais assuntos relacionados a performance, programação e SEO acesse http://vivendonaweb.blogspot.com

[Bruno Augusto 417]

Seria mais interessante a cada subtópico colocar um exemplo.

 

Bom artigo :thumbsup:

[3l3tr1c 1]

Boas as dicas. Obrigado.

[h4v3st 1]

André obrigado por contribuir com a comunidade iMasters. Continue se possível contribuindo com informações.

Estou movendo seu tópico para a área correta:

 

MySQL :seta: Artigos, tutoriais e matérias (MySQL).

[Jorge Seiki Teruya 15]

A escolha correta de hardware também é essencial para o desempenho do banco e a alteração do arquivo de configuração (my.cnf) é imprescindível também.