Como usar pesquisas de texto completo no MySQL

A pesquisa de texto completo é uma técnica de banco de dados que recupera registros mesmo se eles não corresponderem exatamente aos seus critérios de pesquisa. Isso permite uma pesquisa rica em linguagem natural que parece mais familiar.

Procurando por “ mecanismo de banco de dados ” usar uma pesquisa de texto completo retornará resultados contendo as frases “ banco de dados, ” “ motor ” ou “ mecanismo de banco de dados ”. Isso resolve possíveis ambigüidades em seus critérios de pesquisa, portanto, linhas como “ Eu tenho um banco de dados e um mecanismo ” ainda aparecem.

Pesquisas simples no MySQL podem ser feitas usando o operador LIKE. Isso é ineficiente e tem funcionalidade limitada. O MySQL geralmente precisa realizar uma verificação de tabela completa para identificar os registros que correspondem à sua consulta.

As consultas de texto completo usam um índice criado especialmente para melhorar o desempenho. Isso também permite que o MySQL acompanhe as palavras em seu conjunto de dados, facilitando a pesquisa em linguagem natural.

Configurando a pesquisa de texto completo

Você só pode usar a pesquisa de texto completo com colunas que possuem um índice de texto completo. Use a cláusula FULLTEXT nas instruções CREATE TABLE para configurar isso ao propagar o esquema do banco de dados. Você também pode usá-lo com ALTER TABLE para adicionar um índice de pesquisa às colunas existentes.

CRIAR artigos & # 40; conteúdo TEXTO, FULLTEXT & # 40; conteúdo & # 41; & # 41 ;;   Artigos ALTERTABLE ADICIONE FULLTEXT & # 40; content & # 41 ;;

Com o índice no lugar, você está pronto para começar a consultar o seu banco de dados.

Usando pesquisas de texto completo

As pesquisas de texto completo começam com uma cláusula WHERE. Você usa MATCH AGAINST em vez de LIKE. Você precisa indicar as colunas indexadas a serem correspondidas, bem como a consulta a ser pesquisada.

SELECIONE * DOS artigos WHEREMATCH & # 40; conteúdo & # 41; CONTRA & # 40; 'mecanismo de banco de dados'INNATURALLANGUAGE MODE & # 41 ;;

Isso executará uma pesquisa de texto completo dos artigos usando o mecanismo de banco de dados de consulta. Ao especificar IN NATURAL LANGUAGE MODE, o MySQL é instruído a interpretar a consulta literalmente, sem processá-la de nenhuma forma. Este é o modo de pesquisa padrão se nenhum for especificado.

Ordenação por relevância do resultado

Uma das vantagens da pesquisa de texto completo é que ela permite ordenar os registros retornados por relevância. Isso não é possível com uma consulta LIKE normal. Você pode usar uma cláusula MATCH ... AGAINST como parte de um SQL SELECT. A coluna virtual retornada conterá uma pontuação de relevância, de 0 a 1, indicando o quão próximo o registro correspondeu à consulta de pesquisa.

SELECIONE o conteúdo, COMBINE & # 40; conteúdo & # 41; CONTRA & # 40; 'mecanismo de banco de dados' & # 41; COMO relevância DE artigos ORDERBY relevância DESC

Esta consulta retornaria os resultados mais relevantes primeiro. Isso ajuda seu aplicativo a atender às expectativas do usuário sobre como os sistemas de pesquisa em linguagem natural devem funcionar.

O MySQL calcula pontuações relevantes de pesquisa avaliando vários fatores diferentes. Isso inclui o número de registros que correspondem à consulta, bem como o número de vezes que a consulta ocorre em cada registro. Um resultado com várias correspondências exatas para a consulta terá uma classificação mais alta do que aquele que contém apenas parte da consulta.

Ao usar MATCH ... AGAINST em uma instrução SELECT, você não precisa repeti-la na cláusula WHERE. Você pode filtrar manualmente os resultados para incluir apenas registros com uma pontuação de relevância diferente de zero.

SELECIONE o conteúdo, COMBINE & # 40; conteúdo & # 41; CONTRA & # 40; 'mecanismo de banco de dados' & # 41; COMO relevância DE artigos ONDE relevância > 0ORDERBY relevância DESC

Modo de expansão da consulta

A linguagem natural não é o único modo de pesquisa de texto completo compatível. O modo de expansão da consulta é uma alternativa que ajuda a ampliar o alcance dos resultados da pesquisa. Ele pondera automaticamente a consulta de pesquisa em relação aos termos mais relevantes.

Uma pesquisa expandida começa encontrando os registros que contêm uma correspondência para a consulta. Esses registros são então verificados para identificar as palavras mais relevantes. O banco de dados então executa outra pesquisa, desta vez baseando-se nas palavras relevantes em vez da consulta original. Isso geralmente resulta em mais registros sendo retornados, mantendo um grau aceitável de relevância.

Veja como você ativa o modo de expansão de consulta:

SELECIONE * DE artigos WHEREMATCH & # 40; conteúdo & # 41; CONTRA & # 40; 'mecanismo de banco de dados' COM A EXPANSÃO DE CONSULTA & # 41;

Modo Booleano

O modo final de pesquisa de texto completo é o modo booleano. Isso permite incluir modificadores booleanos em sua consulta. Você pode usar este modo quando precisar de controle avançado sobre a lógica de correspondência.

Você pode exigir que uma palavra esteja presente em cada resultado, prefixando-a com +. Use - para excluir resultados que contenham a palavra. Outros operadores podem ser usados ​​para combinar partes de palavras, criar subexpressões e fazer com que as palavras reduzam a pontuação de relevância. O último pode ser útil ao mascarar o “ ruído ” termos. Se você não especificar um operador, fica implícito que a palavra será referenciada na consulta com OU.

SELECIONE * DE artigos WHEREMATCH & # 40; conteúdo & # 41; CONTRA & # 40; '+ data * engine -sqlite'INBOOLEAN MODE & # 41;

Esta consulta mostraria artigos com pelo menos uma palavra começando com dados onde sqlite não é mencionado. Cada resultado pode ou não incluir a palavra motor.

O modo booleano permite construir pesquisas poderosas usando sua própria lógica. Uma ressalva é que ele não oferece suporte para pontuação de relevância de pesquisa. Essa é a compensação em dar aos usuários a capacidade de fazer pesquisas booleanas. A classificação de resultados pode não estar de acordo com as expectativas humanas.

Configurando a pesquisa de texto completo

O MySQL vem com várias opções de configuração de texto completo que permitem que você ajuste como as pesquisas são conduzidas. Aqui estão algumas das mais importantes.

• innodb_ft_min_token_size – Define o comprimento mínimo da palavra para termos indexados. Palavras com menos caracteres do que esse valor não serão adicionadas ao índice, portanto, você não poderá pesquisá-las. O valor padrão é 3, que exclui palavras extremamente comuns, como a, an e I. Esta configuração se aplica a tabelas InnoDB; use ft_min_word_len para MyISAM.
• innodb_ft_max_token_size – Da mesma forma que innodb_ft_min_token_size, define o comprimento máximo das palavras indexadas. Palavras mais longas não serão pesquisáveis. Use ft_max_word_len para tabelas MyISAM.
• innodb_ft_enable_stopword – Esta configuração, ativada por padrão, permite que você controle se o MySQL deve filtrar “ palavras de interrupção ”. Palavras irrelevantes são palavras muito usadas que podem influenciar indevidamente os resultados da pesquisa. A lista de palavras irrelevantes padrão contém 36 frases frequentes.
• innodb_ft_user_stopword_table – Você pode definir este valor como o nome de uma tabela de banco de dados da qual o MySQL deve fornecer a lista de palavras de interrupção. Esta tabela deve ter uma única coluna VARCHAR chamada valor. Adicione suas palavras irrelevantes para excluir como registros nesta tabela.

Essas configurações geralmente são definidas no arquivo de configuração do servidor MySQL. A localização varia de acordo com a plataforma; ele geralmente pode ser encontrado em /etc/mysql/my. cnf. Você precisará reiniciar o serviço MySQL após alterar as configurações.

Depois que o servidor estiver de volta ao normal, reconstrua os índices de texto completo para sua tabela. Você deve fazer isso para que seus dados sejam reindexados usando a configuração atual. Caso contrário, os dados indexados anteriormente continuarão a ser usados.

Para reindexar uma tabela InnoDB, execute OPTIMIZE TABLE minha_tabela. Para tabelas MyISAM, use REPAIR TABLE my_table QUICK. Os índices de pesquisa serão reconstruídos para que as alterações na configuração tenham efeito.

Resumo

MySQL Full-Text Search é habilitado adicionando um índice FULLTEXT aos seus campos pesquisáveis. Você então usa MATCH ... AGAINST com um dos três modos de pesquisa para obter seus resultados. As consultas de linguagem natural retornam uma pontuação de relevância de pesquisa que você pode usar para classificar seus resultados.

A pesquisa de texto completo oferece um comportamento de pesquisa mais poderoso do que uma instrução LIKE. Também tem muito mais desempenho, especialmente em grandes conjuntos de dados, já que todo o texto é indexado antecipadamente.