MySQL8の自然言語全文検索で類似テキストを抽出してみる

はじめに

MySQL8 には IN NATURAL LANGUAGE MODEというものがあり、これを使うと文字列の自然言語処理を実行することができます。

今回の記事では、簡単に類似文字列の抽出やその類似スコアの出し方などをまとめていきたいと思います。

筆者の環境

• MySQL 8.0.30

テスト用のテーブルを作成する

まずはテスト用のテーブルを作成します。

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (title, body)
) ENGINE=InnoDB;

テーブルが作成されたか確認

mysql> show tables;
+---------------------+
| Tables_in_sample-db |
+---------------------+
| articles            |
+---------------------+
1 row in set (0.02 sec)

OK！

FULLTEXTって何？

CREATE TABLE文を見ると、 FULLTEXTというのがありますね。

これは、テキストベース（CHAR、VARCHAR、TEXTのデータ型）上に作成されるインデックスです。

このインデックスが張られたカラムの操作速度を上げることができます。

詳しくは公式のドキュメントを参照ください

→ https://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html

データを作成する

articlesテーブルに適当にデータを作成します。ここでは公式のドキュメントにあるデータを流し込んでみます。

Wikipedia等から適当なテキストを取ってきました。ちょっと見にくいかもですが、全部コピペするとデータ挿入に成功します。

INSERT INTO articles (title,body) VALUES
('MySQL Wikipedia','MySQL is free and open-source software under the terms of the GNU General Public License, and is also available under a variety of proprietary licenses. MySQL was owned and sponsored by the Swedish company MySQL AB, which was bought by Sun Microsystems (now Oracle Corporation)'),
('What is MySQL','MySQL is free and open-source software under the terms of the GNU General Public License, and is also available under a variety of proprietary licenses.'),
('History of Japan','Japan is the eleventh most populous country in the world, as well as one of the most densely populated and urbanized.'),
('About USA','The United States of America (U.S.A. or USA), commonly known as the United States (U.S. or US) or America, is a transcontinental country located primarily in North America.'),
('Oracle database','Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model[4] database management system produced and marketed by Oracle Corporation.'),
('Not about database, but apple','Apple Inc. is an American multinational technology company that specializes in consumer electronics, software and online services headquartered in Cupertino, California, United States.');

検索してみる

ではデータもできましたので、検索をやってみましょう！

クエリはこんな感じになります。

SELECT * FROM articles WHERE MATCH FULLTEXTインデックスを張ったカラム AGAINST ('検索文字列' IN NATURAL LANGUAGE MODE);

databaseという文字列で検索してみましょう。

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE)\G;

*************************** 1. row ***************************
   id: 5
title: Oracle database
 body: Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model[4] database management system produced and marketed by Oracle Corporation.
*************************** 2. row ***************************
   id: 6
title: Not about database, but apple
 body: Apple Inc. is an American multinational technology company that specializes in consumer electronics, software and online services headquartered in Cupertino, California, United States.

今のテーブルの状態では id: 5のOracleに関するテキストが一番類似性があったようです。

類似スコアを出してみる

では次にこの２つの類似スコアを出してみましょう。

クエリは以下のようにします。

mysql> SELECT id, MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE) AS score FROM articles\G;

*************************** 1. row ***************************
   id: 1
score: 0
*************************** 2. row ***************************
   id: 2
score: 0
*************************** 3. row ***************************
   id: 3
score: 0
*************************** 4. row ***************************
   id: 4
score: 0
*************************** 5. row ***************************
   id: 5
score: 0.9105787873268127
*************************** 6. row ***************************
   id: 6
score: 0.22764469683170319

id: 5（Oracleのデータ）がやっぱり一番数値が大きいですね。

でもこれだと見にくいので、数値が０より大きいものだけ出してみましょう。

mysql> SELECT id, MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH (title,body) AGAINST('database' IN NATURAL LANGUAGE MODE);
+----+---------------------+
| id | score               |
+----+---------------------+
|  5 |  0.9105787873268127 |
|  6 | 0.22764469683170319 |
+----+---------------------+

id: 5の方がスコアが上なんですね。

では、次に softという言葉で検索してみましょう。

mysql> SELECT id, MATCH (title,body) AGAINST ('soft' IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH (title,body) AGAINST('soft' IN NATURAL LANGUAGE MODE);
Empty set (0.02 sec)

なんと、結果は０でした。データをよく見ると、softwareという言葉があり、これがヒットしそうなのですが、ヒットしませんでした。

代わりに softwareで検索してみましょう。

mysql> SELECT id, MATCH (title,body) AGAINST ('software' IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH (title,body) AGAINST('software' IN NATURAL LANGUAGE MODE);
+----+--------------------+
| id | score              |
+----+--------------------+
|  1 | 0.0906190574169159 |
|  2 | 0.0906190574169159 |
|  6 | 0.0906190574169159 |
+----+--------------------+

３件が同率でヒットしました。これだとヒットするんですね。

MATCHが２回実行してるように見える

これまで、スコアを出力するクエリを見てみると、MATCHが２つあり、処理が大きくなっているような印象を受けます。

しかし、２回の MATCHの呼び出しが同じあるために MySQLオプティマイザが作用し全文検索が１回のみ実行され、追加のオーバーヘッドは存在しません。

最後に

今回の記事ではスーパー簡易的ですが、MySQL8の自然言語全文検索をやってみました。

これまで自然言語とかいうとPythonで難しい処理や特定のライブラリを使わないとできない、という印象でしたが、これがMySQLでできると色んなアプリケーションで簡単に自然言語処理ができるようになりそうですね。