MySQL の Full-Text Search (全文検索) で自分のツイートを検索してみた

MySQL で文字列の検索をすると以下のような問題に躓いた事はありませんか？

• 部分一致しようとしたらフルスキャンが走った
• 先頭の 255 文字しか index が効かなかった

私も以下のように諦める事が多かったです。

• まだこのレコード数ならフルスキャンで耐えられるから…
• varchar(255) に変更し、前方一致で勘弁してもらう

そこで Elasticsearch ですよ！ と言いたいところですが、サーバ代金は掛かるし、Elasticsearch ができる人がいない…

そこで MySQL の Full-Text Search (全文検索) が使い物になるのか試してみました。

環境

• mecab 環境構築済みの MySQL 8.0.11(gkmr/mysql-mecab:8.0)
• 私のツイート約 4.5 万件

テーブルの作成とテストデータの挿入

docker を使います。

docker-compose.yml

version: "3"
services:
  db:
    platform: linux/x86_64
    image: gkmr/mysql-mecab:8.0
    environment:
      MYSQL_ROOT_PASSWORD: password
    ports:
      - "3306:3306"
    command: --default-authentication-plugin=mysql_native_password
    volumes:
      - ./docker/mysql:/var/lib/mysql
      - ./data/:/data/

起動して接続します。あとで LOAD DATA LOCAL INFILE したいので docker に入って接続しています。

docker compose up -d
docker exec -it mysql_full_text-db-1 /bin/bash
mysql --local-infile -uroot -ppassword

今回はシンプルに tweet テーブルに PK と tweet を入れます。
tweet には FULLTEXT index を付加します。

CREATE DATABASE tweet;
use tweet;
CREATE TABLE tweet (
    id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    tweet TEXT NOT NULL,
    FULLTEXT idx (tweet)
) ENGINE=InnoDB;

Twitter から全ツイートをダウンロードします。
1 日掛かるのでお急ぎの方は私の人工無能 bot のツイートでお試しください。
https://twitter.com/settings/download_your_data

Twtter からダウンロードした tweet.js は JavaScript の変数なので以下のように CSV にしました。
素直に Node.js で読んでしまうほうが楽だったかも…

# brew install gsed jq
# ゴミ掃除。変数宣言を削除してjsonにする。
gsed -i -e "s/^window.YTD.tweet.part0 =//" tweet.js

# json lines (1行1jsonの形式) にします
jq -c '.[]' tweet.js > tweet_jsonlines.json

# CSVにします
cat tweet_jsonlines.json | jq -r ".tweet | [.id, .full_text] | @csv" > tweet.csv

# 確認
head -n5 tweet.csv

このような CSV です。

"64536042","ガムガムガムガムガムガムガムガム！"
"60248152","@xxx http://www.apple.com/jp/macosx/bootcamp/ xxxさんはこっちだよね^^"
"60113702","「どうだい？腹筋が火を噴いてきただろう？」"
"58664402","@xxx ごめん　こなきじじいじゃなくて、それ僕です"
"58654132","手羽先の炒め物、たこ刺し、オクラゆで"

Docker にマウントしておいた data/tweet.csv に配置して、MySQL に import します。

SET GLOBAL local_infile=on;

LOAD DATA LOCAL INFILE "/data/tweet.csv"
INTO TABLE tweet_ja
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';

/* 確認 */
select * from tweet limit 10;

like で検索する

fulltext index を指定するだけでは恩恵は受けられないことに注意。
遅かった like は修正する必要があります。

select * from tweet where tweet like "%京都府%";

134614452468326402	@waketi 東京都府中市
241717869631660032	@xxx mecab 東京都府中市に行きたい
560053819707371520	@xxx 東京都府中市
1133919296239726592	よく使われる英文らしい。日本でいう東京都府中市的なやつだろうか？ #ElasticONTokyo https://t.co/HAdCzfaPJf
1264804295435644928	RT @xxx: 京都府 舞鶴産 本マグロの  白子入荷‼️白子ポン酢   天ぷらで召し上がれ‼️ https://t.co/81IyhKgO9a
1310491950450003968	検索の例に「東京都府中市」をよく使いますが、「超電磁砲」もなかなか良いと思うんですよね。

60〜600ms 程度かかりました。4.5 万件ならこれでもいいか、という判断も無きにしもあらず。

全文検索する

標準のパーサで検索

select * from tweet where match(tweet) against ("京都府");

1264804295435644928	RT @xxx: 京都府 舞鶴産 本マグロの  白子入荷‼️白子ポン酢   天ぷらで召し上がれ‼️ https://t.co/81IyhKgO9a

1.6ms で 1 件しました。この速さなら問題なさそうです。
たまたまこれだと良い結果だったのですが、京、 京都 で 0 件になりました。

これは標準の全文検索パーサが space やカンマで単語を分割して検索するからです。

The built-in FULLTEXT parser determines where words start and end by looking for certain delimiter characters; for example, (space), , (comma), and . (period).

英語だとそれで良さそうなのですが…。日本語は難しいですね…。

一応、後述のブール全文検索を使えば回避できます。

select * from tweet where match(tweet) AGAINST ('*京都*' IN BOOLEAN MODE);

765348738541953025	ruby kaigi 9/7 17:00会社出発、18:30東京発、京都から宿までは町を見ながら4kmほど歩く、で行こうと思います。今夜予約する。
774524584057982976	京都またね！
1264804295435644928	RT @xxx: 京都府 舞鶴産 本マグロの  白子入荷‼️白子ポン酢   天ぷらで召し上がれ‼️ https://t.co/81IyhKgO9a
1268713493668098048	最近10代の夢をよく見る。↵友達と名古屋〜京都を旅した。↵違和感なく10代に戻れる。つまりそれは…?
448258236638978048	電車で等間隔に座る自分達を見て、京都の鴨川を思い出す。

ngram と mecab

MySQL では ngram と mecab のパーサを使えます。

ngram は単語を一定数の間隔で区切るパーサです。デフォルトでは 2 で bigram と呼ばれますね。東京, 京都, 都府, 府中, 中市 のように分割されます。

mecab パーサは環境構築が必要なので注意です。AWS の RDS でこのような環境構築はできず、mecab は使えません。

以下のテーブルを作成し、先程と同じくデータを挿入します。

CREATE TABLE tweet_ngram (
    id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    tweet TEXT not null,
    FULLTEXT idx (tweet) WITH PARSER ngram
) ENGINE=InnoDB;

CREATE TABLE tweet_ja (
    id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    tweet TEXT not null,
    FULLTEXT idx (tweet) WITH PARSER mecab
) ENGINE=InnoDB;

ngram

select * from tweet_ngram where match(tweet) against ("京都府");

2.7ms でたくさんヒットしました。とにかく部分一致させたい場合はこれがいいですね。
今回は悪意がある例ですが、未知語に対応できるを検索できる、などのメリットもあります。
例えば「ドウシマキョウ」という未知語に「ドウシマ」でヒットさせることができます。

560053819707371520	@xxx 東京都府中市
1133919296239726592	よく使われる英文らしい。日本でいう東京都府中市的なやつだろうか？ #ElasticONTokyo https://t.co/HAdCzfaPJf
1264804295435644928	RT @yoshiike_group: 京都府 舞鶴産 本マグロの  白子入荷‼️白子ポン酢   天ぷらで召し上がれ‼️ https://t.co/81IyhKgO9a
省略

ブール全文検索

Not やワイルドカードなどで検索できます。

select * from tweet_ngram where match(tweet) AGAINST ('+京都 -東京' IN BOOLEAN MODE);

774524584057982976	京都またね！
1264804295435644928	RT @xxx: 京都府 舞鶴産 本マグロの  白子入荷‼️白子ポン酢   天ぷらで召し上がれ‼️ https://t.co/81IyhKgO9a
1268713493668098048	最近10代の夢をよく見る。↵友達と名古屋〜京都を旅した。↵違和感なく10代に戻れる。つまりそれは…?
224452089676431361	迷ったらコレ！ JR京都駅構内で買えるテッパンの京みやげ5選 : ライフハッカー［日本版］ http://t.co/gTMnhrsi 全部食べたい。全部だ！
448258236638978048	電車で等間隔に座る自分達を見て、京都の鴨川を思い出す。

mecab パーサ

select * from tweet_ja where match(tweet) against ("京都府");

1264804295435644928	RT @xxx: 京都府 舞鶴産 本マグロの  白子入荷‼️白子ポン酢   天ぷらで召し上がれ‼️ https://t.co/81IyhKgO9a
586819795603091456	I'm at 大阪国際空港 (ITM/RJOO) in 豊中市, 大阪府 https://t.co/7P73icAHnf
765348738541953025	ruby kaigi 9/7 17:00会社出発、18:30東京発、京都から宿までは町を見ながら4kmほど歩く、で行こうと思います。今夜予約する。
774524584057982976	京都またね！
1268713493668098048	最近10代の夢をよく見る。↵友達と名古屋〜京都を旅した。↵違和感なく10代に戻れる。つまりそれは…?
224452089676431361	迷ったらコレ！ JR京都駅構内で買えるテッパンの京みやげ5選 : ライフハッカー［日本版］ http://t.co/gTMnhrsi 全部食べたい。全部だ！
448258236638978048	電車で等間隔に座る自分達を見て、京都の鴨川を思い出す。

「京都」「府」に分割されるので京都がヒットしますが、「府」で「大阪府」がヒットしてますね。
速度も他と変わらず 2ms 程度でした。

スコアを見ることができます。「府」が強い。

select *, match(tweet) against ("京都府") as score
from tweet_ja where match(tweet) against ("京都府")
order by score desc;

1264804295435644928	RT @yoshiike_group: 京都府 舞鶴産 本マグロの  白子入荷‼️白子ポン酢   天ぷらで召し上がれ‼️ https://t.co/81IyhKgO9a	34.11714553833008
586819795603091456	I'm at 大阪国際空港 (ITM/RJOO) in 豊中市, 大阪府 https://t.co/7P73icAHnf	19.300804138183594
765348738541953025	ruby kaigi 9/7 17:00会社出発、18:30東京発、京都から宿までは町を見ながら4kmほど歩く、で行こうと思います。今夜予約する。	14.816340446472168
774524584057982976	京都またね！	14.816340446472168
1268713493668098048	最近10代の夢をよく見る。↵友達と名古屋〜京都を旅した。↵違和感なく10代に戻れる。つまりそれは…?	14.816340446472168
224452089676431361	迷ったらコレ！ JR京都駅構内で買えるテッパンの京みやげ5選 : ライフハッカー［日本版］ http://t.co/gTMnhrsi 全部食べたい。全部だ！	14.816340446472168
447690110222344194	@nyajika むむ、京都？	14.816340446472168
448258236638978048	電車で等間隔に座る自分達を見て、京都の鴨川を思い出す。	14.816340446472168

まとめ

• 標準のパーサは日本語だと使いづらい
• 他のパーサは ngram と mecab がある
• 部分一致を提供したい場合は ngram が良さそう
• mecab パーサを使うと「東京都府中市問題」(京都で東京都府中市がヒットする問題)が起きない
• AWS RDS などのクラウドベンダーでは mecab パーサは使えない事が多そう

Elasticsearch などの全文検索エンジンを導入するのは色々大変なこともあります(例: できる人がいない。サーバコスト増)。
選択肢の一つとしてありですが、自分たちのやりたいことが実現できているのか考えたい。

Elasticsearch は難しくないし、これで解決できる問題がたくさんあるはずです！
そんなあなたに、検索だけじゃない Elasticsearch 入門+ という本を書きましたので良かったらどうぞ！(PR)