Exemplo de desnormalização de banco de dados

Se você já trabalhou a mais de 2 meses em TI já ouviu os termos "normalização de banco de dados" e
"boas práticas". E provavelmente também ouviu a relação das duas coisas: é uma boa prática normalizar um banco de dados.

Não há o que se discutir sobre isso numa primeira instância. Sim, porque uma das técnicas avançadas de tuning é a desnormalização. Aqui vamos a um exemplo prático de como se fazer isso. Mas antes, as ressalvas:

- Aprenda a normalizar um banco de dados de forma decente, nada de fazer um serviço porco e querer dizer que é um padrão avançado inventado no Paquistão.

- Assim como para normalizar, estude o que pode ser desnormalizado. O processo é um método, racional e com suas próprias regras.

O exemplo.

Vamos supor que você cuide de um portal de artigos médicos, que são publicados sempre em 4 línguas (inglês, francês, alemão e espanhol).

Numa forma normalizada, você teria um banco de dados com estrutura semelhante a:

Tabela [autor]
- id
- nome

Tabela [idioma]
- id
- idioma

Tabela [artigo]
- id
- autor_id

Tabela [artigo_contents]
- id
- artigo_id
- idioma_id
- conteudo

Como vemos acima, temos uma tabela para idioma, uma para artigo com informações gerais, e uma tabela com o conteúdo dos artigos, indexada ao artigo e ao idioma. Essa estrutura facilita a organização dos conceitos uma vez que está normalizada.

Agora como saber se essa estrutura poderia ser otimizada e desnormalizada? Bem, poderíamos ter o conteúdo na própria tabela artigos. Antes de prosseguir com a idéia, precisamos responder a algumas perguntas:

- o número de idiomas vai/pode mudar? quais as chances disso acontecer?
- pode ser que um artigo seja publicado num número parcial de línguas, por exemplo só em inglês e alemão?

A resposta a estas perguntas é que determina se a atual estrutura do banco de dados pode ser desnormalizada ou não.

Se o número de línguas NÃO vai mudar ao longo do tempo, ou a chance disto é extremamente remota E os artigos serão todos sempre publicados em todas as línguas, então podemos desnormalizar o banco de dados, movendo o conteúdo para a tabela artigos.

A nova estrutura seria semelhante a:

Tabela [autor]
- id
- nome

Tabela [artigo]
- id
- autor_id
- conteudo_en
- conteudo_de
- conteudo_fr
- conteudo_es

Muito mais simples. A vantagem explícita desse tipo de operação é a simplicidade na hora de escrever e rodar as queries.

Antes deveria ser algo como:

SELECT c.conteudo, at.nome FROM artigo_conteudo c, autor a WHERE a.id = artigo.autor_id AND ac.artigo_id = artigo.id AND ac.idioma_id = '2' AND artigo.id = 27;

Agora se traduz em algo como:

SELECT a.conteudo_fr, at.nome FROM artigo a, autor at WHERE at.id = a.autor_id AND artigo.id = 27;


Reduzindo o número de joins e tudo o mais. Claro, vai ser necessário refatorar parte dos códigos, mas pode valer a pena. O ideal é desnormalizar ANTES de criar o código, na fase de design da solução. Assim o custo de refactor é 0.
----------- keepReading

Turbinando suas buscas internas

O problema

Quase sempre vemos um formulário de busca interna em sites que publicam conteúdos com frequencia. Este formulário, assim como a navegação por categorias ou por tags, ajuda muito os visitantes a encontrarem o que procuram. Mas desenvolvedores iniciantes - ou nem tão iniciantes assim - ainda implementam esse mecanismo de busca de uma forma "crua".

Geralmente é um simples formulário, com método GET, um campo de texto e um botão de OK. Até aí, sem problemas. No front end é isso mesmo. Mas no back end, geralmente a implementação que vemos é algo assim:


<?php
$terms = explode(' ', $_GET);
$query = "SELECT * FROM table WHERE conteudo LIKE ";

foreach ($terms as $term) {
$query .= "%" . $term . "% OR LIKE";
}
... // roda a query, pega resultados, mostra
?>


Pois bem, esta abordagem, apesar de funcional, não é elegante, nem tem a precisão desejável. Se você buscar por "um cone" pode encontrar "a coleção de ícones do Humberto". Isso sem falar da performance das queries, que é seriamente afetada quanto mais registros no banco e mais termos são procurados.

A solução? Um recurso que os principais RDBMS tem chamado Full-Text Search. Vou ensinar aqui como implementar e usar estes recursos na prática (no MySQL e no PostgreSQL).

MySQL

NOTA: o recurso de Full-Text Search só está disponível para tabelas MyISAM.
Vamos supor que você tenha uma tabela de posts ou artigos com a estrutura parecida com a seguinte:

id
titulo
intro
conteudo
autor
tags
publishdate

Em primeiro lugar vamos precisar adicionar um índice, que é composto pelos campos que desejamos que sejam buscados. Rode isso pelo seu programa de administração do MySQL (MySQL Admin, phpMyAdmin).

ALTER TABLE posts ADD FULLTEXT (titulo, intro, conteudo, tags);


Depois disso, basta reescrever a query no PHP:

SELECT * FROM posts
WHERE MATCH (titulo, intro, conteudo, tags)
AGAINST (". $_GET .");


A busca será mais rápida e os resultados com menos falsos positivos. Sem contar que o código fica mais limpo e elegante.

PostgreSQL

O PostgreSQL dá um pouco mais de trabalho para fazer a implementação.

Pelo seu programa de administração, rode as seguintes queries.

ALTER TABLE posts ADD COLUMN postsfts tsvector;

UPDATE posts SET postsfts =
setweight(to_tsvector('pg_catalog.portuguese', coalesce(titulo,'')), 'A') ||
setweight(to_tsvector('pg_catalog.portuguese', coalesce(intro,'')), 'B') ||
setweight(to_tsvector('pg_catalog.portuguese', coalesce(conteudo,'')), 'C');

CREATE INDEX postsfts_idx
ON posts
USING gin(postsfts);

CREATE TRIGGER postsfts_tg
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger('postsfts', 'pg_catalog.portuguese', 'titlulo', 'intro', 'conteudo');


Entendendo o que foi feito.
A primeira query adiciona um outro campo à tabela, onde o vetor de busca ficará armazenado (isso aumenta de forma significativa a performance).
A segunda query define quais campos farão parte do full-text search. Note que no PostgreSQL você pode definir o peso de cada campo - A, B, C ou D, sendo A o campo mais importante, D o menos importante. Ah, e claro definimos a linguagem para português.
Na terceira query, construímos um index sobre o campo que acabamos de criar.
Por último, criamos um trigger que vai atualizar o campo que criamos, sempre que o o conteúdo da tabela for atualizado.

Com isto feito, podemos transformar as queries no PHP para:

SELECT * FROM posts, to_tsquery(' . $_GET . ') query
WHERE query @@ postsfts;


No PostgreSQL o operador "@@" funciona como o MATCH AGAINST do MySQL. E precisamos converter o que vai ser buscado através da função to_tsquery().

Qual é o mais relevante?

Os recursos de full-text search permitem ir um pouco mais além e definir um ranking sobre quais registros são mais relevantes sobre os termos buscados. No PostgreSQL isso pode ser feito com uma query similar à seguinte:


SELECT *, ts_rank_cd(postsfts, query) AS rank
FROM posts, to_tsquery(' . $_GET . ') query
WHERE query @@ postsfts
ORDER BY rank DESC LIMIT 5;


Aqui temos a função ts_rank_cd que faz essa função do ranking (além de já termos setado os pesos dos campos antes). E vocês podem notar que estamos ordenando pelo ranking. Ou seja, essa query trás os 5 resultados mais relevantes.

Para obter o mesmo efeito no MySQL podemos fazer a query da seguinte forma:

SELECT *, MATCH (titulo, intro, conteudo, tags) AGAINST (". $_GET .") AS rank
FROM posts
WHERE MATCH (titulo, intro, conteudo, tags) AGAINST (". $_GET .")
ORDER BY rank DESC LIMIT 5;


Conclusão

Espero ter criado um guia rápido e prático para que vocês possam aplicar o full-text search em seus próximos trabalhos e assim obter melhores resultados. Vocês codificam mais rápido, o sistema ganha em performance, e os usuários agradecem os resultados mais precisos.

Abraços e keep readin'

----------- keepReading