Ubuntu16.04のMySQL5.6で全文検索を使用してデータベース検索を改善する方法

getdocs

2年前

序章

全文検索（FTS）は、検索エンジンがデータベース内の結果を検索するために使用する手法です。これを使用して、ショップ、検索エンジン、新聞などのWebサイトの検索結果を強化できます。

具体的には、FTSは検索条件に完全に一致しないドキュメントを取得します。 Documents は、テキストデータを含むデータベースエンティティです。つまり、ユーザーが「猫と犬」を検索すると、たとえば、FTSに基づくアプリケーションは、単語を個別に含む結果（「猫」または「犬」のみ）を返し、異なる順序で単語を含めることができます。（「犬と猫」）、または単語の変形を含む（「猫」または「犬」）。これにより、アプリケーションは、ユーザーが何を意味するのかを推測し、より関連性の高い結果をより早く返すことができるという利点があります。

技術的に言えば、MySQLのようなデータベース管理システム（DBMS）は通常、 LIKE 条項。ただし、これらのリクエストは、大規模なデータセットではパフォーマンスが低下する傾向があります。また、ユーザーの入力と正確に一致するように制限されています。つまり、関連情報を含むドキュメントがある場合でも、クエリで結果が生成されない可能性があります。

FTSを使用すると、より高度なツールに余分な依存関係を導入することなく、より強力なテキスト検索エンジンを構築できます。このチュートリアルでは、MySQL 5.6を使用して全文検索を使用してデータベースにクエリを実行し、検索入力との関連性によって結果を定量化し、最適なもののみを表示します。

前提条件

このチュートリアルを開始する前に、次のものが必要です。

このUbuntu16.04 ガイドを使用した初期サーバーセットアップ（sudo非rootユーザーとファイアウォールを含む）に従ってセットアップされた1つのUbuntu16.04サーバー。
Ubuntu 16.04にMySQLをインストールする方法ガイドに従って、MySQL5.6以降をインストールします。

ステップ1—テストデータの作成

全文検索を試すには、いくつかのデータが必要です。このステップでは、というデータベースを作成します testdb と呼ばれるテーブルで news、架空のニュースアグリゲーターサイトの記事を表すいくつかのサンプルデータを入力します。

注：代わりに使用したいテキストデータを含む独自のテーブルがある場合は、手順2にスキップして、フォローしながら適切な置換を行うことができます。

まず、MySQLコンソールにアクセスします。 MySQLのインストール時に設定したrootパスワードを入力するように求められます。

mysql -u root -p

接続すると、プロンプトが次のように変わります mysql>.

次に、という新しいデータベースを作成します testdb. このデータベースには、テストデータが含まれます。

CREATE DATABASE testdb;

使用に切り替えます testdb データベースはデフォルトであるため、データベース内で作成または更新するためにデータベースの名前を指定する必要はありません。

USE testdb;

次に、データベースにというテーブルを作成します news ニュースアグリゲーターの記事の例の列があります。

CREATE TABLE news (
   id INT NOT NULL AUTO_INCREMENT,
   title TEXT NOT NULL,
   content TEXT NOT NULL,
   author TEXT NOT NULL,
   
   PRIMARY KEY (id)
);

このコマンドの機能を見ていきましょう。

CREATE TABLE 他の多くのデータベースと同様に、テーブルを作成するSQLコマンドです。
news テーブルの名前です。
title, content と author 長さが無制限のテキスト列です。
NOT NULL null値を持つことができない列をマークするために使用される宣言です（ただし、空の文字列が含まれている場合があります）。
id 特別なタイプのテーブルのプライマリインデックスです AUTO_INCREMENT、IDフィールドに次に使用可能なIDを自動的に入力します。

次に、いくつかのサンプルデータをテーブルに追加します。

INSERT INTO news (id, title, content, author) VALUES 
	(1, 'Pacific Northwest high-speed rail line', 'Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.', 'Greg'),
	(2, 'Hitting the beach was voted the best part of life in the region', 'Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.', 'Ethan'),
	(3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');

このコマンドの機能を見ていきましょう。

INSERT データを挿入します。
INTO データを挿入する場所を指定します。この場合、それは news テーブル。
(id, title, content, author) VALUES 各エントリのデータ値を格納する列を指定します。
最後の3行は、テーブルに追加する3行のデータです。それぞれに、ニュースWebサイトのサンプル記事が含まれています。 title、いくつか content、そしてその authorの名前。

各エントリにも固有のものがあります idデータベースインデックスに自動的に入力されるentifier。 データベースインデックスは、データ取得操作のパフォーマンスを向上させるデータ構造です。このインデックスは、メインデータとは別に保存されます。追加の書き込みと比較的少ないストレージスペースを犠牲にして、テーブルコンテンツの変更を更新します。サイズが小さく、データ構造が調整されているため、メインの表スペースを使用して照会を選択するよりも、索引をより効率的に操作できます。

データができたので、FTSを使用してそのデータを検索するクエリの作成を開始できます。

ステップ2—FTSインデックスの作成とFTS関数の使用

FTSを使用できるように、テキスト列のインデックスを作成しましょう。

これを行うには、MySQL専用のコマンドを使用します。 FULLTEXT. このコマンドは、FTSで検索できるようにするすべてのフィールドを内部インデックスに入れるようにMySQLに指示します。

ALTER TABLE news ADD FULLTEXT (title, content, author);

これは、すべてのテキスト列を組み合わせてサニタイズすることで機能します（例：句読点を削除し、大文字を小文字にします）。このインデックスが作成されたので、ソーステーブルの内容を変更するSQLクエリによって更新されます。

次に、関数を使用して「シアトルビーチ」の全文検索を実行してみます MATCH() AGAINST().

SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('Seattle beach' IN NATURAL LANGUAGE MODE)\G

The MATCH() コマンドの一部は、FTSを使用して索引付けされる列のセットを指定します。インデックスの作成に使用した列リストと一致する必要があります。 The AGAINST() 部分は、全文検索を実行している単語を指定します。この例では「シアトルビーチ」です。

IN NATURAL LANGUAGE MODE 検索ワードは、前処理なしでユーザー入力から直接提供されることを意味します。 MySQLはデフォルトで自然言語モードを想定しているため、明示的に指定する必要はありません。

注：自然言語モードと比較すると、単語ステミングは、インデックスストリップで単語の接辞を削除し、ルート部分のみを格納する、もう1つの便利なFTS手法です。たとえば、「fits」と「fitted」という単語は、単語の語幹を持つFTSを使用すると同じになります。

残念ながら、MySQLは単語のステミングをサポートしていません。ステミングはMySQLのワークログにありますが、実装してリリースする時間枠はまだありません。 FTSは、それよりもはるかに高速であるため、依然として有用です。 LIKE 条項。単語ステミングを使用したい場合は、スノーボールライブラリとの統合を検討できます。

The \G 上記のクエリの最後に、出力の各列が新しい行に出力されます。これにより、長い結果が少し読みやすくなります。上記のコマンドの出力は次のようになります。

Output*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
*************************** 2. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
2 rows in set (0.00 sec)

どのエントリにも「Seattlebeach」というフレーズは含まれていませんでしたが、全文検索を使用したため、「Seattle」という単語のみを含む最初の行と、「Seattlebeach」という単語のみを含む2番目の行の2つの結果が得られました。 “ビーチ”。キーワードを変更して結果を表示することにより、追加の検索を試すことができます。

SQLクエリでFTS関数を使用して、検索入力に関連する行を検索できるようになったので、これらの結果をより関連性の高いものにすることができます。

ステップ3—FTSの結果を改善する

全文検索結果の関連性を高めるのに役立つ2つの手法があります。 1つは結果の関連性スコアでフィルタリングし、もう1つは IN BOOLEAN 結果から特定の単語を除外し、検索語間の最大距離を指定します。

INBOOLEANの使用

手順2では、デフォルトのモードを使用しました IN NATURAL LANGUAGE クエリ用語を指定する場合。別のモードがあります、 IN BOOLEAN、検索から特定の単語を除外したり、入力内の単語が互いにどれだけ離れている必要があるかなどの範囲を定義できます。

クエリから用語を省略するには、マイナス演算子を次のように使用します。 IN BOOLEAN. 次のコマンドは、「traveling」という単語を含み、「Seattle」という単語を含まない結果を返します。

SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('traveling -Seattle' IN BOOLEAN MODE)\G

結果には行2のみが表示されます。

Output*************************** 1. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
1 row in set (0.01 sec)

これが機能するのは、マイナス演算子がDMSに、関連性スコアが0の除外された単語でドキュメントをマークするように指示しているためです。このモードでは、関連性スコアがゼロ以外の結果のみが表示されます。

使用することもできます IN BOOLEAN MODE 検索語間の最大距離を指定します。この距離は単語で測定され、重要なことに、検索語が含まれます。たとえば、「猫と犬」というフレーズの距離は3です。

次のコマンドは、「traveling」と「miles」という単語が2語以内で表示される結果を返します。

SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('"traveling miles" @4' IN BOOLEAN MODE)\G

一致した結果が1つ表示されます traveling the 140 miles 行2の content.

Output*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
1 row in set (0.00 sec)

変更した場合 @4 に @3 元のコマンドでは、結果は表示されません。

検索語間の距離によって検索結果を制限すると、語彙が多様な非常に大きなドキュメントを検索するときに役立ちます。クエリ用語間のギャップが小さいほど、結果はより正確になりますが、距離の微調整は、操作しているドキュメントのセットによって異なります。たとえば、一連の科学論文は3の小さな単語のギャップでうまく機能する可能性がありますが、フォーラムの投稿の検索は、結果をどの程度広くまたは狭くするかに応じて、8以上のギャップでより良いパフォーマンスを発揮する可能性があります。