MySQLで全文検索が使いたい場面が
もちろんLIKE検索もインデックスを張れば速いらしいですが、冒頭にワイルドカードがあると遅くなるらしいです。（例 : LIKE '%ワン%' ）
そこで思い切って MySQL + Mroonga を使って全文検索を試してみました。導入までは少し大変だったけど、検索速度はLIKEとは比較にならない速さです。
▼ ちなみに当方の開発環境について
- XAMPP（Windows版）
- MariaDB10.4.12
環境はそれぞれで異なるのは当然です。
それゆえ環境依存の所は各自置き換えてください。
このページの目次
１．Mroonga付属のmaridbをインストールする
▼ まずは各環境に以下をインストール
mariadb-x.x.xx-with-mroonga-xx.xx
▼ 公式ダウンロードページ
ここから各自の環境（Windows・MacOS・Linux...）にあわせてインストールしてください。
僕の環境はWindows+XAMPPなので mariadb-10.4.12-with-mroonga-9.12-winx64.zip をインストールしてXAMPP元々のMySQL（mariadb）と置き換えしました。
ちなみにXAMPPのMroonga導入は以下記事が役にたちます。
デフォルトのXAMPP構成だとMroongaは使えません。必ず C:\xampp\mysql をdataディレクトリなどを除いて置換する必要があります。
２．XAMPPのみ : データベースの復元が必要
XAMPP限定での話ですが・・・
大前提としてXAMPPではデータベースをdataディレクトリに格納しています。（例えば C:\xampp\mysql\data である場合がほとんど）
もし C:\xampp\mysql を丸々mariadb+Mroongaに置き返したとすると、dataディレクトリは空の状態になっています。
そのためもしデータベースを復元するなら、
- 置き換え前にDBをzipなりでバックアップ
- 置き換え後にzipからデータベース復元
みたいな方法を取るのがいいかもしれません。
もし「既存データベースが動かない...」みたいなトラブルが起きたなら、上記の方法を試してみてください。
３．テーブルにFULLTEXTインデックスを張る
それでは早速Mroongaで全文検索を体験！
手始めに次のテーブルを作成してみました。
▼ テーブル作成のSQL
|
1 2 3 4 5 6 7 |
CREATE TABLE IF NOT EXISTS books ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(1000) NOT NULL, creator VARCHAR(1000) NOT NULL, FULLTEXT INDEX(name) COMMENT 'parser = \"TokenMecab\"', PRIMARY KEY(id) ) engine = mroonga default charset utf8; |
このように FULLTEXT INDEX(name) のように書けば、nameカラムに対して全文検索のインデックスを張ることができます。
あと地味に大事なのがコメントから COMMENT 'parser = \"TokenMecab\"' のようにパーサーを指定することですね。これは恐らく必須
そして当然ながらエンジンには engine = mroonga default charset utf8; のようにmrgoonaを指定してください。文字コードはUTF8を指定します。
既存テーブルにFULLTEXTインデックスを張るには
テーブル作成後でもインデックスは晴れます。
▼ こういうSQLを実行すればＯＫ
|
1 |
ALTER TABLE books FULLTEXT INDEX idx_name_fulltext (name) COMMENT 'parser = \"TokenMecab\"' |
ただしエンジンにMroongaを使うことが前提です。
４．MATCH～AGAINST構文で高速検索できる
そしたら最後は実際に検索してみました。
※ レコード挿入とかは面倒なので省略
以下のようなテーブルで試すことにします。
- レコード数は10755件
- テーブル圧縮はしていない
具体的には次の２つの検索SQLで比較しました。
▼ 従来のLIKE検索を使った検索SQL
|
1 |
SELECT * FROM `books` WHERE name LIKE '%ワン%'; |
▼ 全文検索のMATCH～AGAINSTによる検索SQL
|
1 |
SELECT * FROM `books` WHERE MATCH(name) AGAINST('ワン') IN BOOLEAN MODE); |
このSQLのように MATCH(name) AGAINST('ワン') を使うとnameカラム内を渡したキーワードで検索できます。あとモードを渡すことも可能（モードについては後述）
上記SQLにより両方とも34件のレコードが返ってきました。
驚いたのはその検索速度です。
▼ LIKE検索の5回の検索速度
| 1回目 | 0.0080 秒 |
| 2回目 | 0.0088 秒 |
| 3回目 | 0.0087 秒 |
| 4回目 | 0.0091 秒 |
| 5回目 | 0.0081 秒 |
▼ 全文検索の5回の検索速度
| 1回目 | 0.0011 秒 |
| 2回目 | 0.0010 秒 |
| 3回目 | 0.0011 秒 |
| 4回目 | 0.0011 秒 |
| 5回目 | 0.0012 秒 |
どうですかこれ？
比較するまでもなく全文検索の圧勝です！
全文検索にはBOOLEAN MODEを使うのがベスト
もう一度全文検索のSQLを載せておきます。
|
1 |
SELECT * FROM `books` WHERE MATCH(name) AGAINST('ワン') IN BOOLEAN MODE); |
ここで IN BOOLEAN MODE を使っているのは、そちらの方がgoogle検索などの挙動に近くなるからです。Mroonga公式でもこのように説明されています。
▼ 公式でのモードの解説
通常、デフォルトの IN NATURAL LANGUAGE MODE よりも IN BOOLEAN MODE の方が適切です。なぜなら IN BOOLEAN MODE はWeb検索エンジンのクエリーと似ているからです。多くのユーザーはWeb検索エンジンのクエリーに慣れています。
デフォルトだと自然言語モードである模様
これとIN BOOLEAN MODEがどう違うのかの詳細は不明ですが、一般的な検索エンジンと近づけるにはそっちを使った方がベストみたいですね。
実際使ってみると IN BOOLEAN MODE では検索語と一致したものだけ表示できます。一方の自然言語モードだと無関係なのもヒットして精度が低くなる模様です。
高速検索にはMroongaなどが必須（かもしれない）
もし何万・何百万レコードもある場合・・・
そこから「○○」という単語で検索したいという場面があって、そこで LIKE '%○○%' なんてやったら永遠に検索が終わりません。だからMroongaなどのエンジンがあるわけですね。
まだMroonga含めて全文検索は分からないことだからなので、なにか気づいたこと・新しい知見があったら追記してこうと思います。ではまた(@^^)/~~~
