/ Banco de dados

Utilizando Full Text Search para fazer consultas extremamente rápidas

O que é?

De forma resumida, podemos dizer que o "full text search" é um mecanismo de "índice invertido" que os SGBDs mais modernos suportam para otimizar consultas textuais.

QUE!? Índice invertido?

Sim! Nada mais é que uma "lista" onde as palavras de determinada coluna são separadas e armazenadas no índice juntamente com os locais onde a mesmas aparecerem.

Exemplo:
Imagine que temos uma tabela no banco de dados com duas colunas: id e descricao.
Nessa tabela temos 3 registros:

  • id: 1 | descricao: "tênis azul para corrida"
  • id: 2 | descricao: "tênis amarelo para caminhada"
  • id: 3 | descricao: "boné azul com amarelo"

Quando o índice invertido for criado para essa coluna, ele ficará da seguinte forma:

  • "tênis" : {1, 2}
  • "azul" : {1, 3}
  • "para" : {1, 2}
  • "corrida" : {1}
  • "amarelo" : {2, 3}
  • "caminhada" : {2}
  • "boné" : {3}

Como podemos perceber, no lado esquerdo é armazenada a palavra e no direito é informado onde ela aparece. Ou seja, assim o SGBD já sabe de uma forma muito mais rápida quem possui determinado valor.

Vantagens

A principal vantagem é o ganho de velocidade ao realizar consultas. Quanto mais registros existirem no banco de dados, mais perceptível será esse ganho.

No sistema SGPL, em uma base com 50 mil registros, onde cada um possuia uma coluna do tipo Varchar com tamanho máximo, o tempo de consulta foi reduzido de mais de um minuto para menos de um segundo.

Mas a velocidade não é a única vantagem! Ao instalar o full text search você ganha inúmeros recursos interessantes, como por exemplo:

Busca por plurais
Caso exista um registro na base com o valor "país" e o usuário buscar por "países", o SGBD sabe que o plural de "país" é "países" e mostrará aquele registro. Isso acontece porque o full text search possui um dicionário com as palavras de determinada linguagem (que pode ser configurada ao criar o índice).

Ingorar acentos
Caso o utilizador assim queira, é possível configurar de forma bem simples para ser ignorado os acentos nas consultas, ou seja, caso a pesquisa seja feita por "mão", trará os registros com o valor "mao".

Busca por palavras próximas
Outro recurso extremamente útil é a possibilidade de fazer pesquisas por palavras próximas, sendo possível dizer qual é a distância máxima entre as palavras.
Imagine que exista um registro com o valor "celular branco do João" e a consulta seja feita pela "celular" próxima de "joão" com a distância máxima de 2 palavras. Ao realizar essa consulta, o registro seria encontrado.

Além dos que foram citados aqui, o full text search possui uma infinidade de recursos interessantes que podem ser consultados lendo a documentação do mesmo.

Como instalar?

A grande maioria dos SGBDs suportam a instalação do full text search, nesse artigo daremos o exemplo de como instalar no SQL Server da Microsoft.

No Linux basta executar o seguinte comando no terminal:

export DEBIAN_FRONTEND=noninteractive && \
    apt-get update && \
    apt-get install -y curl  && \
    curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list | tee /etc/apt/sources.list.d/mssql-server.list && \
    apt-get update && \
    apt-get install -y mssql-server-fts

No Windows é necessário ir em "adicionar/remover programas" e alterar a instalação do SQL Server para adicionar o componente do full text search:
cdBNj

Em ambos os sistemas é necessário reiniciar o banco de dados após a instalação.

Como criar os índices?

Após a instalação, o último passo necessário para conseguir sair utilizando os recursos do full text é criar os índices para as tabelas escolhidas.

Veja o seguinte exemplo onde é criado um índice para a tabela TB_PROPOSICAO, na coluna PRP_RESUMO, configurado para ignorar acentos e utilizar o dicionário no idioma PT-BR (código 1046).

CREATE FULLTEXT CATALOG catalog_proposicao WITH ACCENT_SENSITIVITY=OFF;

CREATE UNIQUE INDEX ui_prop ON sgpl.TB_PROPOSICAO(PRP_ID);

CREATE FULLTEXT INDEX ON sgpl.TB_PROPOSICAO
(
  PRP_RESUMO
  Language 1046
)
KEY INDEX ui_prop ON catalog_proposicao WITH CHANGE_TRACKING AUTO

Fazendo consultas

Para ganhar os benefícios do full text search, ao realizar as consultas é necessário utilizar as funções que o recurso disponibiliza, como a função FREETEXT.

SELECT *  
FROM sgpl.TB_PROPOSICAO  
WHERE FREETEXT (PRP_RESUMO, 'informática')

Mas atenção! Caso a mesma consulta seja feita sem utilizar uma função específica do recurso, será realizada uma busca normal, como se o full text search nem estivesse instalado.

SELECT *  
FROM sgpl.TB_PROPOSICAO p  
WHERE p.PRP_RESUMO LIKE %informática%

Na consulta acima, nenhum recurso do full text search será utilizado, mesmo existindo um índice invertido criado para essa tabela e coluna.

Veja um exemplo utilizando o recurso para pesquisar por palavras próximas:

SELECT *  
FROM sgpl.TB_PROPOSICAO p  
WHERE CONTAINS(PRP_RESUMO, 'NEAR((az, informática), 2)')

A consulta acima irá trazer os registros que contém a palavra "az" perto de "informática" com uma distância máxima de 2 palavras entre elas.

Considerações

Tudo o que foi escrito aqui são conceitos e exemplos básicos da utilização do recurso para quem quer começar a entrar nesse mundo ou está apenas precisando resolver um problema de lentidão, o full text search vai muito além disso. Em uma próxima postagem será abordado a instalação e utilização do recurso no banco PostgreSQL.

Referências