Performance para busca de estruturas com herança

Este post interessa à quem precisa armazenar estruturas com herança em banco de dados relacionais. Vou iniciar pelo problema e ao final mostro a solução, que minimiza o uso de junções (joins) para recuperar as informações na maioria dos casos.

1. O problema

1.1. Herança
É comum em sistemas de banco de dados, a figura a entidade Pessoa. Ela representa justamente registros que são pessoas no sistema. Entretanto, essa simplicidade é somente aparente. Na maioria (senão a totalidade dos casos) um registro de pessoa não adianta muito. As regras do sistema geralmente a tratam sob a perspectiva do contexto de negócio. Por exemplo, a pessoa para a área de Vendas da empresa é chamada de Cliente, para o Estoque, de o Fornecedor; para o RH é de Colaborador, Convênio, Conveniado, etc. Ou seja, o mesmo registro de pessoa (por exemplo, de código 123) é interpretado de várias formas, dependendo de onde ele é usado.

1.2. Mundo OO versus Mundo Relacional
Esse tipo de situação, geralmente demanda o uso de estruturas com herança para ser implementado. No mundo OO, isso seria relativamente fácil, pois o suporte a Polimorfismo e o (bom) uso de Interfaces e Classes Abstratas daria conta do recado. Com o advendo do Hibernate, a busca e a modificação dessas estruturas são muito fáceis.

Entretanto, é comum armazenar essas informações em banco de dados relacionais. E, nesse caso, uma série problemas acontece. O principal deles (ao meu ver) é a performance. Ou seja, por mais que tenhamos uma boa técnica de mapeamento objeto-relacional (o Hibernate e o JPA suportam de cara três alternativas - trato isso na minha palestra sobre o Hibernate), cada abordagem possui prós e contras. Na prática, ou ganhamos em momentos de pesquisa, ou ganhamos em otimização do uso das tabelas no banco, ou ganhamos nas operações de ou insert, ou update ou delete. Em palavras simples, nunca teremos 100% nas três dimensões.

1.3. Exclusões Lógicas
Não obstante esses problemas, é muito comum em sistemas profissionais a questão da Exclusão Lógica, onde uma operação de delete na verdade não apaga o registro no banco de dados, mas sim, simplesmente marca-o de forma que ele pareca não existir mais.

Esse comportamento é necessário para evitar que um registro seja usado novamente no sistema. Entretanto, ele não pode ser apagado porque podem existir dezenas (ou centenas) de outros registros que dependem dele. Assim, se ele fosse efetivamente removido do banco de dados, seria necessário atualizar ou remover esses registros dependentes. E isso pode ser um ciclo que - na prática - poderia acabar por remover em cascata todos os registros do banco de dados!

É muito comum efetuar exclusões lógicas nas estruturas de pessoas. Por exemplo, em uma loja poderia ser desejado excluir o cliente 123. Entretanto, essa pessoa é, também, um funcionário da loja. Assim, não é possível excluir o cliente sem perder o registro do funcionário. Daí entra a exclusão lógica, onde o registro 123 é marcado como logicamente excluído para o cliente - mas não para o funcionário.

A implementação da exclusão lógica é feita, na forma trivial, pela adição de um campo de flag na tabela Cliente, que indica se o registro está ativo ou não. É essa a abordagem clássica.

1.4. Os Problemas Juntos na Vida Real
Ao juntar as peças do quebra-cabeça acima, surgem situações que geram, no mínimo, graves problemas de performance no banco de dados. Vou dar um exemplo de um sistema que estou desenvolvendo. Nele, a figura pessoa possui cerca de 12 subtipos diferentes. Pelo esquema criado no banco (pelos analistas) existe uma tabela-mãe Pessoa e 12 tabelas-filha, digamos assim. Nessa implementação, os dados comuns à todas as pessoas são armazenados na tabela-mãe, ao passo que os dados específicos de cada um dos subtipos são armazenados na respectiva tabela-filha. A chave primária é compartilhada entre as tabelas, de forma que a pessoa 123 tem seu ID replicado em cada tabela-filha que ela exista.

Nesse cenário, para recuperar os dados completos do Colaborador número 123, por exemplo, é necessário uma junção entre a tabela Pessoa e a Colaborador buscando pelo registro 123. Isso é simples e é performático.

O problema surge quanto desejamos efetuar buscas como: Identificar todos os tipos da pessoa 123. Nessa busca, a solução clássica é ser feita de duas formas: (1) ou efetua-se uma junção entre a tabela-mãe e todas as tabelas-filha, (2) ou cria-se um campo extra na tabela-mãe que contém um identificador dizendo que, aquele registro, é do tipo colaborador, cliente, fornecedor, etc.

No início do sistema, os analistas propuseram a solução da junção. Entretanto, viram logo que isso seria demasiado custoso, uma vez que a grande maioria das pesquisas do sistema necessitaria efetuar essas junções. Logo depois, tentaram a solução de uma coluna String na tabela Pessoa, de forma a executar um famigerado "substring" nessa coluna para identificar os subtipos da pessoa. Logo perceberam que o substring era tão ruim quanto as junções da outra solução.

Não obstante a essa situação adversa, eles ainda não tinham colocado a variável da exclusão lógica nesse cenário - o que acresceria, no mínimo, um operador AND nos selects. Em palavras simples, estavam no mato sem cachorro.

Foi essa a bola que passaram para os projetistas resolverem...

2. Uma Solução Razoável (e matematicamente simples)

Por obra do destino, o projetista responsável pelo módulo que trata a estrutura de pessoas era justamente...eu :)

Do cenário deles, eu já estava a par. E também sabia as soluções que poderiam ser feitas no modo trivial, alá Hibernate ou JPA. Mas elas não seriam melhores do que as que os meus colegas já haviam tentado. Era preciso algo mais.

Não lembro como foi, mas o fato é que pensando na situação, me veio à cabeça o esquema de permissões de arquivos do Unix. Legal, né ?!

Bem, nesse esquema, é dado um número único para um recurso, que é capaz de identificar completamente a permissão que existe sobre ele. É o famoso CHMOD. Esse esquema funciona com base em um argumento matemático simples para números binários, onde um número de base 2 qualquer é sempre maior que a soma de todos os números que o precedem em uma unidade. Vou dar um exemplo:

0 = 0
1 = (1)
2 = (1) + 1
4 = (1) + 1 + 2
8 = (1) + 1 + 2 + 4
16 = (1) + 1 + 2 + 4 + 8
32 = (1) + 1 + 2 + 4 + 8 + 16
64 = (1) + 1 + 2 + 4 + 8 + 16 + 32
128 = (1) + 1 + 2 + 4 + 8 + 16 + 32 + 64
256 = (1) + 1 + 2 + 4 + 8 + 16 + 32 + 64 + 128
512 = (1) + 1 + 2 + 4 + 8 + 16 + 32 + 64 + 128 + 256

... e por aí vai.

Mas o que isso tem a ver com o caso de pessoa e seus subtipos? Explico:

Vamos supor que façamos isso:

Registros que são Pessoa tem valor 1; Colaborador tem valor 2; Funcionário, 4; Convênio, 8; Fornecedor, 16; Conveniado, 32. Páro aí nesse exemplo.

Então, se uma pessoa for Colaborador e Funcionário, ela vale 2 + 4 = 6. Se ela for Colaborador e Conveniado, ela vale 2 + 32 = 34. Se for Fornecedor e Convênio, ela vale 16 + 8 = 24.

Em outras palavras, cada vez que uma pessoa assume um novo perfil, ela tem seu valor somado. Se ela perde um perfil, ela tem seu valor subtraído.

Assim, o que temos? Temos um chamado número mágico, que é capaz de identificar todas os subtipos de uma pessoa através de uma decomposição matemática. Esse número é equivalente à solução da "substring", mas muito mais performático, uma vez que evita demorados cálculos sobre campos alfanuméricos. É esse campo que é salvo na famosa coluna Tipo na coluna Pessoa.

Assim, para eu efetuar uma pesquisa do tipo "O que o registro pessoa 123 é no sistema?" é fácil eu fazer um select sobre a tabela pessoa e, dentro da aplicação (usando uma estrutura de hash simples) mostrar quais são os tipos de uma pessoa. Da mesma forma, para uma pesquisa como "Me retorne todos os colaboradores" basta efetuar um select sobre a tabela Pessoa buscando os registros que resultem verdadeiro para a operação Pessoa.Tipo & Colaborador == Colaborador, algo simples e eficiente em qualquer BD ou linguagem de programação

E quanto aos ao registros excluídos logicamente? Nesse caso, eu faço a técnica da tentativa. Em outras palavras, quando o Colaborador é excluído logicamente ocorrem duas coisas. A primeira, é subtrair o valor de Colaborador do número mágico na tabela Pessoa. A segunda, é setar o flag de exclusão (no meu caso, um campo de timestamp) na tabela Colaborador. Então, quando a busca de colaboradores é feita, não é encontrado o registro 123 no retorno. Porém, quando alguém tentar incluir um colaborador com esse código, o sistema vai gerar um conflito, informando para o usuário que esse número já existe. Daí, o usuário podem optar por reativar o perfil Colaborador para esse registro, caso em que o número mágico é incrementado novamente.

3. Conclusões

A solução descrita acima está em uso e mostra-se como muito performática. Claro, quando a busca do precisa ter filtros que sejam colunas declaradas nas tabelas-filha não tem como tem fugir da junção.

Para isso, em conjunto com os analistas, temos um trabalho de "elevar" essas colunas para a tabela-mãe. São coisas clássicas, como nome, cpf, cnpj e outros campos identificadores comuns às tabelas-filha e que tenham boas chances (ou heurísticas) de serem usado frequentemente nas pesquisas. Certo, nesse caso abrimos espaço para os buracos com valores nulos, uma vez que numa linha de registro nem todos os campos estariam presentes. É a historinha das dimensões acima...

Bom, nesse post tentei dar uma idéia de uma solução interessante para busca de estruturas com herança armazenadas em bancos relacionais.

Antes de terminar, porém, exponho o comentário do meu amigo Corvalis (hic), que disse que a própria estrutura de pessoa que usamos nesse sistema não é compilante com com um bom mapeamento objeto-relacional e não poderia ser migrada para um Hibernate ou JPA. Disse à ele que nesse caso não tem problema, pois o arquiteto do projeto optou por não usar essas tecnologias e, portanto, não é prioridade nossa (ainda) se preocupar com isso.

Assim, dá-lhe CHMOD nas tabelas, hehehe.