MySQLのInnoDBの日本語全文検索インデックスを試す（上から順にSQLはるだけ） #MySQL

GMOさんのこちらの記事を試した上で、もう少しデータを増やしたものです。
https://techblog.gmo-ap.jp/2020/01/06/mysql-innodb-fulltext-search-tuto/

全文検索とは

長い文章に含まれる単語ごとの出現回数リストを用意することで、SQLのlike文より柔軟にほしい文書を早く検索する機能です。現実の用途としてはGoogleなど検索エンジンが代表的なもので、Amazonで何を検索しても商品名から解説文までマッチして一瞬で結果一覧が出るのも、Macのファイルやアプリを探すSpotlight検索やそのCLI版であるmdfind命令も似たような仕組みで動いていると思われます。

以前はMySQLに専用製品を組み合わせたり、ElasticSearchなどの別製品で扱われることが多かったのですが、2015年のMySQL5.7から標準で日本語の全文検索がつきました。

しくみ

文章を分解して、単語ごとの出現回数リスト(Index=もくじ)を作り、本文でなくそのインデックスに問い合わせることで、通常のlike文などの直接全テキストを探す方法よりも、ほしい文書を早く柔軟に見つけ重要な順に表示します。

登録時に時間をかけてインデックス化して整理することで、問い合わせの応答を早くするという仕組みなので、あまり更新されない大量のデータ(50年分の新聞記事、100年分の裁判例)や、更新されるけれど問い合わせの方がはるかに多いサービス（数億サイトを扱うWeb検索エンジン、マッチングサイト200万人のプロフィール検索）に向いています。

日本語と英語

単語出現のリストを作るにあたって、英語などの単語を空白で区切る言語ではリスト作成が簡単で、日本語や中国語は単語の区切りがないぶん工夫が必要で、機械的に数文字ごとにリスト化するN-gramや、単語辞書と文法を用意した上でリスト化する形態素解析（文章を最小要素に分解すること）などの手法があります。

歴史

もともと2000年ごろにGoogleなど検索エンジン周辺の必要からオープンソースで開発が進み、それが事業化する形でElastic社のElasticSearchなどが出てきて、MySQLも日本語全文検索できるよう改造したものが売られたり（ひろゆきさんの未来検索ブラジルという会社）しましたが、2015年のMySQL5.7で公式に日本語全文検索がつきました。

InnoDBのテーブル作る

MySQLで日本語全文検索をつけられるストレージエンジンはInnoDBです。
TEXT型のbody（本文）という列に日本語全文検索をつけます。
全文検索の仕組みはインデックスなので列のデータ型はVARCHARでもTEXTでも構いません。

CREATE TABLE full_text_test (
  id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
  body TEXT
) ENGINE=InnoDB CHARACTER SET utf8mb4;

データつくる

東京や大阪や京都といった単語が一回あるいは大量に使われた文章を登録します。

INSERT INTO full_text_test (body) VALUES
  ('東京都で遊ぶ'),
  ('京都で遊ぶ'),
  ('大阪で遊ぶ'),
  ('京都と大阪で遊ぶ'),
  ('東京都で働く'),
  ('京都で働く'),
  ('大阪で働く'),
  ('京都と大阪で働く'),
  ('京都と大阪で遊ぶけど、やっぱり京都が好きで5回京都に行ったし京都に住んだこともあるし、働いたこともある'),
  ('東京都で遊ぶといえば、東京駅から初めて東京都庁をめぐり・・・。'),
 ('大阪大好き！ 大阪大阪大阪大阪大阪大阪大阪大阪！！ 大阪大阪大阪大阪大阪大阪大阪大阪！！'),
 ('東京大好き！ 東京東京東京東京東京東京東京！！ 東京東京東京東京東京東京東京！！'),
 ('東京大好き！ 東京東京東京東京東京東京東京！！ 遊びまくる、遊ぶ遊ぶ遊ぶ遊ぶ遊ぶ！'),
 ('東京大好き！ 東京東京東京東京東京東京東京！！ 働きまくる！ 働く働く働く働く働く働く！');

フルテキスト（全文検索）インデックスをはる

n-gram方式のインデックスをbody列につけます。

ALTER TABLE full_text_test 
ADD FULLTEXT INDEX ngram_idx (body) WITH PARSER ngram;

取り出してみる boolean mode

SELECT * FROM full_text_test WHERE MATCH (body) 
AGAINST ('東京' IN BOOLEAN MODE); // 東京と関係が深い順

SELECT * FROM full_text_test WHERE MATCH (body) 
AGAINST ('大阪' IN BOOLEAN MODE); // 大阪と関係が深い順

SELECT * FROM full_text_test WHERE MATCH (body) 
AGAINST ('京都' IN BOOLEAN MODE); // 京都と関係が深い順
SELECT * FROM full_text_test WHERE MATCH (body) 
AGAINST ('労働' IN BOOLEAN MODE); // 労働と関係が深い順
SELECT * FROM full_text_test WHERE MATCH (body) 
AGAINST ('+東京 都庁 '  IN BOOLEAN MODE); // 東京があって、都庁もあればなお良い

SELECT * FROM full_text_test WHERE MATCH (body) 
AGAINST ('+京都 -東京' IN BOOLEAN MODE); // 京都があって東京がないものを重要度順に
SELECT * FROM full_text_test WHERE MATCH (body) 
AGAINST ('+京都 +大阪 -東京' IN BOOLEAN MODE);
SELECT * FROM full_text_test WHERE MATCH (body) 
AGAINST ('+京都 大阪 -東京' IN BOOLEAN MODE);