アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

MySQLでフリーワード検索

おしごとで、フリーワード検索機能を組み込む機会があったのでメモ。
というか当時のメモが見つかったのでリファクタリング

「最終的に何をどう導入したか」しか書いてないので、もうちょっと詳しく知りたい方は以下参考サイトをどうぞ。

参考:MySQL全文検索 - FULLTEXTインデックスの基礎知識
http://www.tatamilab.jp/rnd/archives/000389.html
参考:MySQL5.6でInnoDBのFULLTEXT INDEXで全文検索する
http://www.petitec.com/2013/04/mysql5-6-fulltext-index/


ここでの例として…
求められる仕様は、↓のような感じでした。

  • ユーザプロフィールを任意の語句で検索
    • 検索対象フィールドは複数あり、いずれかに語句が含まれていればヒット
      • ユーザ名、職業、趣味、自己紹介
    • 語句は複数指定可(ただし、すべて含まれているものを抽出したい)
    • ユーザIDの降順で表示
  • 尚、検索対象フィールドの更新頻度は1日100回未満を想定
  • 検索対象フィールドには日本語が入る想定

案としては、以下の3つがありました。
①DB構造そのまま、LIKE検索
②NoSQLを導入し、検索専用のデータベースを作る
③FULLTEXTインデックス+MATCH~AGAINST構文

①は従来と何も変えなくていいので導入の手間などはありませんが、検索速度に難があることが容易に想像できます。
②は、検索速度をかなり上げられる可能性がありますが、導入やMySQLとの連携などで手間が大きすぎます。

そこで目を付けたのが③です。
②と比べると導入の手間はかなり少なく済み、検索速度も問題ありません。

これはどういうものかというと、
例えば「東京 都 中央 区 ほげほげ」というレコードに対して、
「東京」→ヒットする
「京都」→ヒットしない
「中央」→ヒットする
中央区」→ヒットしない
「ほげ」→ヒットしない
という挙動をします。
(日本語で見ると使いづれぇ!って思いますが…単語ごとに半角スペースなどで区切られる言語であれば自然な動きをする、ということです)

この方法で実装するために、以下のような手順で導入しました。

データベース設計

元々のユーザプロフィールのテーブルとは別に、検索用のテーブルを作成します。

  • ユーザプロフィール(InnoDB)
    • ユーザID(PK)、名前、メールアドレス、パスワード、職業、趣味、自己紹介、…
  • ユーザ単語検索用テーブル(MyISAM)
    • ユーザID(PK)、検索用単語集(FULLTEXT INDEX)

プロフィールテーブルを含め、基本的にすべてInnoDBにしていますが、
検索テーブルはMyISAMにして、検索用フィールドにFULLTEXTインデックスを張ります。

<追記 20161020>
調べた時はFULLTEXTインデックスはMyISAMのみ使用可能、というのばかり見かけたのですが、
MySQL5.6以降であればInnoDBでも使用可能です。
</追記>

あ、テーブルやフィールドの照合順序も必要に応じて。
表記ゆれには対応したかったので「utf8_unicode_ci」にしました。

MySQLの設定変更

デフォルト状態では3文字以下の語が無視されてしまうので、以下設定しました。
権限の問題で、わたしがやったわけじゃないので詳しくないです。

  • /etc/my.cnf の設定変更
[mysqld]
#MyISAMの場合
ft_min_word_len=1
#InnoDBの場合
innodb_ft_min_token_size=1

尚、既にテーブル作成済みの場合は、この設定をしたあとにインデックスを張り直す必要があります。

データをINSERTする部分

アプリケーション側ですね。
冒頭で書いたとおり、日本語は単語ごとに半角スペースで区切ってやる必要があります。
今回はmecabを使用しての形態素解析にしました。
また、記号での検索は無視されるようなので、保存しても仕方がありません。節約のため消してしまいます。

ということで、ユーザデータの追加更新が発生したタイミングで、以下処理を行うようにしました。

  • 検索対象フィールドの文字列を半角スペース区切りですべてつなげた文字列を作る
  • 記号を半角スペースに置換
  • 形態素解析する
  • 重複した単語を削除
  • 出来上がった文字列を検索用テーブルにINSERT

今回のケースでは対象テーブルの更新頻度がそんなに高くないのでこれで行きましたが、
本来FULLTEXTインデックスのあるテーブルの更新はちょっと遅めなので、場合によっては工夫が必要かもしれません。

データをSELECTする部分

いよいよ検索部分です。

検索語句に以下処理を加えてクエリを投げます。

  • 形態素解析する
  • 全単語の頭に「+」付与(AND検索のため)

今回は「指定語をすべて含むものを抽出」「ID降順でソート」という仕様なので、
「IN BOOLEAN MODE」指定が必要になります。

SELECT   *
FROM     `検索用テーブル`
WHERE    MATCH(`検索用単語集カラム`) AGAINST('加工した検索語句' IN BOOLEAN MODE)
ORDER BY `ユーザID` DESC

おしまい

大体こんな感じです。
検索精度の調整のためもうちょっと加工したりもしていますが、まあそこは仕様や使用感に応じて適当に。

OR検索、NOT検索、完全一致へも対応できますし、それなりに使い回しは利くと思います。