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

投稿日: 2019-11-08 2022-04-20
タグ: Databases, mysql, Ubuntu 16.04

序章

全文検索（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行のデータです。それぞれに、title、いくつかのcontent、およびauthorの名前が付いたニュースWebサイトのサンプル記事が含まれています。

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

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

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

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

これを行うには、FULLTEXTというMySQL専用コマンドを使用します。このコマンドは、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

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

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

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

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

上記のクエリの最後にある\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

行2のcontentのtraveling the 140 milesと一致する1つの結果が表示されます。

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以上のギャップでより良いパフォーマンスを発揮する可能性があります。

結論

このガイドでは、MySQLの全文検索機能を使用しました。ドキュメント駆動型データベースのデータベーススキーマを構築するときにインデックスを作成し、それに対してクエリを実行するときに、特別な演算子を使用して最も関連性の高い結果を見つけました。

MySQLのFTS機能をさらに詳しく調べたい場合は、MySQL5.6の全文検索に関する公式ドキュメントを読むことができます。

getdocs

13036

序章