MySQL の全文検索機能

はじめに

この実験では、MySQL の全文検索機能を探索します。まず、データベースと id、title、content の列を持つ articles という名前のテーブルを作成し、サンプルデータを挿入します。

次に、ALTER TABLE 文を使用して、articles テーブルの title 列と content 列に article_index という名前の全文インデックスを追加します。最後に、SHOW INDEXES コマンドを使用してインデックスの作成を確認し、Index_type が FULLTEXT であることを確認します。この設定により、MySQL データベース内で効率的なテキスト検索が可能になります。

Skills Graph

テーブルに全文インデックスを追加する

このステップでは、MySQL データベースのテーブルに全文インデックスを追加します。全文インデックスは、文字ベースのデータに対して効率的なテキスト検索を行うために使用されます。

始める前に、作業対象となる articles という名前のシンプルなテーブルを作成しましょう。ターミナルを開き、以下のコマンドを使用して MySQL サーバーに接続します。

mysql -u root -p

root パスワードの入力を求められます。入力して接続します。

次に、search_db という名前のデータベースを作成し、それに切り替えましょう。

CREATE DATABASE IF NOT EXISTS search_db;
USE search_db;

次に、id、title、content の列を持つ articles テーブルを作成します。

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

では、articles テーブルにいくつかのサンプルデータを挿入しましょう。

INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search', 'This article explains how to use full-text search in MySQL.'),
('Indexing in MySQL', 'Learn about different types of indexes in MySQL, including full-text indexes.'),
('Optimizing MySQL Queries', 'Tips and tricks for optimizing your MySQL queries for better performance.');

articles テーブルに全文インデックスを追加するには、ALTER TABLE 文を使用します。title 列と content 列に全文インデックスを作成します。以下の SQL コマンドを実行します。

ALTER TABLE articles ADD FULLTEXT INDEX article_index (title, content);

このコマンドは、title 列と content 列を含む article_index という名前の全文インデックスを作成します。これにより、MySQL は全文検索機能を使用してこれらの列を効率的に検索できるようになります。

SHOW INDEXES コマンドを使用して、インデックスが作成されたことを確認できます。

SHOW INDEXES FROM articles;

出力には、article_index 全文インデックスの行が含まれるはずです。このインデックスの Index_type 列には FULLTEXT が表示されます。

+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| articles |          0 | PRIMARY        |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| articles |          1 | article_index  |            1 | title       | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
| articles |          1 | article_index  |            2 | content     | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

これで、テーブルに全文インデックスを正常に追加できました。次のステップでは、このインデックスを使用して全文検索を行う方法を学びます。

MATCH AGAINST を使用した検索

このステップでは、前のステップで作成した articles テーブルに対して、MATCH AGAINST 句を使用して全文検索を行います。

まず、MySQL サーバーに接続し、search_db データベースを使用していることを確認します。接続が閉じている場合は、以下のコマンドを使用して再接続します。

mysql -u root -p

パスワードの入力を求められたら、root パスワードを入力します。その後、データベースを選択します。

USE search_db;

MATCH AGAINST 句は、SELECT 文の WHERE 句で使用され、全文検索を行います。基本的な構文は次の通りです。

SELECT * FROM table_name WHERE MATCH (column1, column2, ...) AGAINST ('search_term');

ここで、column1、column2 などは全文インデックスの一部である列で、'search_term' は検索したいテキストです。

「MySQL」という単語を含む記事を簡単に検索してみましょう。

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('MySQL');

このクエリは、title 列または content 列に「MySQL」という単語が含まれる articles テーブルのすべての行を返します。

以下のような出力が表示されるはずです。

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

すべての記事がタイトルまたは本文に「MySQL」という単語を含んでいるため、3 つの記事がすべて返されていることに注意してください。

MATCH AGAINST 句は、関連性に基づいて結果をランク付けするためにも使用できます。MySQL は、各行が検索語句とどれだけ一致するかに基づいて関連性スコアを割り当てます。このスコアは、SELECT リストで MATCH AGAINST 句を使用して取得できます。

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles;

このクエリは、各記事の id、title、content および relevance スコアを返します。関連性スコアが高いほど、一致度が高いことを意味します。

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

次に、関連性に基づいて結果を並べ替え、最も一致度の高い結果を最初に表示することができます。

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles ORDER BY relevance DESC;

このクエリは、前と同じ結果を返しますが、relevance スコアの降順で並べ替えられます。

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

このステップでは、MATCH AGAINST 句を使用して基本的な全文検索を行う方法と、関連性に基づいて結果をランク付けする方法を学びました。次のステップでは、より高度な検索機能を実現するためのブールモードの使用方法を学びます。

高度な検索のためにブールモードを使用する

このステップでは、MATCH AGAINST 句のブールモードを探索します。このモードを使用すると、より正確で柔軟な全文検索が可能になります。ブールモードでは、検索クエリ内で必須、禁止、および任意の単語を指定するための演算子が提供されます。

まず、MySQL サーバーに接続し、search_db データベースを使用していることを確認します。接続が閉じている場合は、以下のコマンドを使用して再接続します。

mysql -u root -p

パスワードの入力を求められたら、root パスワードを入力します。その後、データベースを選択します。

USE search_db;

ブールモードを使用するには、MATCH AGAINST 文に IN BOOLEAN MODE 句を追加します。構文は次の通りです。

SELECT * FROM table_name WHERE MATCH (column1, column2, ...) AGAINST ('search_term' IN BOOLEAN MODE);

ブールモードで使用できる演算子の一部を以下に示します。

• +: この演算子が付いた単語は、返されるすべての行に含まれていなければなりません。
• -: この演算子が付いた単語は、返されるどの行にも含まれてはいけません。
• >: この演算子が付いた単語は、行の関連性に正の影響を与えます。
• <: この演算子が付いた単語は、行の関連性を低下させます。
• ~: この演算子が付いた単語は、行の関連性に負の影響を与えます。
• *: ワイルドカード演算子です。
• "": フレーズを定義します。

簡単な例から始めましょう。「MySQL」という単語を 必ず 含み、「optimizing」という単語を 含まない 記事を検索したいとします。以下のクエリを使用できます。

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -optimizing' IN BOOLEAN MODE);

このクエリは、「MySQL」を含み、「optimizing」を含まない行を返します。

以下のような出力が表示されるはずです。

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

「Optimizing MySQL Queries」というタイトルの記事は、「optimizing」という単語を含んでいるため、返されないことに注意してください。

次に、「MySQL」を必須とし、「indexing」を優先するクエリを試してみましょう。

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL indexing' IN BOOLEAN MODE);

ブールモードでは、演算子がない場合、単語は任意として扱われます。このクエリは、「MySQL」を必須とし、「indexing」を任意で含みます。

出力は次のようになります。

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

すべての記事が「MySQL」を含んでいるため、3 つの記事がすべて返されます。「indexing」の存在は任意です。

> 演算子を使用して、「indexing」を含む行の関連性を高めてみましょう。

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE);

このクエリは依然として「MySQL」を必須としますが、「indexing」を含む行はより高いランクになります（ただし、この例では関連性で並べ替えていません）。出力は同じですが、内部的な関連性スコアは影響を受けます。

最後に、二重引用符を使用してフレーズを検索してみましょう。

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('"full-text search"' IN BOOLEAN MODE);

このクエリは、「full-text search」という正確なフレーズを検索します。

出力は次のようになります。

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

このステップでは、MATCH AGAINST 句とブールモードを使用して、+、-、>、<、"" などの演算子を用いた、より高度で正確な全文検索を行う方法を学びました。次のステップでは、全文クエリをテストし、改良します。

全文クエリ結果のテスト

このステップでは、全文クエリをテストし、改良して、目的の結果が返されることを確認します。様々な検索語句とブール演算子を試して、可能な限り高い検索精度を達成します。

まず、MySQL サーバーに接続し、search_db データベースを使用していることを確認します。接続が閉じている場合は、以下のコマンドを使用して再接続します。

mysql -u root -p

パスワードの入力を求められたら、root パスワードを入力します。その後、データベースを選択します。

USE search_db;

まずは、articles テーブルのデータを確認してみましょう。

SELECT * FROM articles;

これにより、テーブルの現在の内容が表示されます。

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

次に、もう少し複雑なブールクエリを試してみましょう。「MySQL」という単語を含み、かつ「indexing」または「optimizing」を含む記事を検索したいとします。これは、括弧を使用して任意の用語をグループ化することで実現できます。

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL +(indexing optimizing)' IN BOOLEAN MODE);

このクエリは、「MySQL」を必須とし、さらに「indexing」または「optimizing」のいずれかを含むことを要求します。

出力は次のようになります。

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

記事 1 は、「MySQL」のみを含み、「indexing」または「optimizing」を含まないため除外されます。

別のシナリオを試してみましょう。「MySQL」に関する記事で、特に「queries」に関してでは ない 記事を検索したいとします。

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -queries' IN BOOLEAN MODE);

このクエリは、「MySQL」を必須とし、「queries」を含む記事を除外します。

出力は次のようになります。

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

「MySQL Queries」に関する記事 3 は除外されます。

次に、結果のランク付けを試してみましょう。> と < 演算子を使用して、関連性スコアに影響を与えることができます。「MySQL」に関する記事を検索し、「indexing」も言及している記事を優先したいとします。

SELECT id, title, content, MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) ORDER BY relevance DESC;

このクエリは、「MySQL」を必須とし、「indexing」を含む記事の関連性を高めます。結果は関連性の降順で並べ替えられます。

出力は次のようになります。

+----+--------------------------+---------------------------------------------------------------------+-----------+
| id | title                    | content                                                             | relevance |
+----+--------------------------+---------------------------------------------------------------------+-----------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |  1.6931  |
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |  0.3068  |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |  0.3068  |
+----+--------------------------+---------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

「Indexing in MySQL」の記事が最初に表示されることに注意してください。これは、>indexing の条件により、関連性スコアが高くなっているためです。

様々な検索語句とブール演算子を試すことで、全文クエリを微調整して、目的の結果を得ることができます。常にクエリを十分にテストして、期待どおりに動作することを確認するようにしてください。

これで、MySQL での全文検索に関する実験は終了です。全文インデックスを追加する方法、MATCH AGAINST を使用して基本的な検索を行う方法、高度な検索のためにブールモードを使用する方法、およびクエリ結果をテストする方法を学びました。

まとめ

この実験では、まず search_db という名前のデータベースと、id、title、content の各列を持つ articles テーブルを作成しました。その後、サンプルデータをテーブルに挿入して、記事を格納しました。

このステップの核心は、ALTER TABLE 文を使用して、articles テーブルの title 列と content 列に article_index という名前の全文インデックスを追加することでした。これにより、効率的なテキスト検索が可能になります。最後に、SHOW INDEXES コマンドを使用してインデックスの作成を確認し、Index_type が FULLTEXT であることを確認しました。