アナログCPU：5108843109

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

('ω') ＜ ｲｻﾞﾕｹｴﾝｼﾞﾆｬｰ

MySQLでフリーワード検索

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 or MyISAM)
    • ユーザID(PK)、名前、職業、趣味、自己紹介、… 、検索用単語集(FULLTEXT INDEX)

検索用単語集フィールドにはFULLTEXTインデックスを張っています。
元々MyISAMでしか利用できないようでしたが、MySQL5.6以降であればInnoDBでもOKです。

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

MySQLの設定変更

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

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

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

検索用データをINSERTする部分

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

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

  • 検索対象フィールドの文字列を半角スペース区切りですべてつなげた文字列を作る
  • 形態素解析して配列化
  • 必要に応じて加工する（以下は例）
    • 記号を削除
    • 重複した単語を削除
    • かな変換を行ったものを追加
  • 加工後の単語群を半角スペース区切りの文字列に変換
  • 出来上がった文字列を検索用テーブルに通常通りの方法でINSERT

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

データをSELECTする部分

いよいよ検索部分です。

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

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

  • 形態素解析する
  • 必要に応じて加工
    • 例えば「晴」という単語でも「晴れ」を含むレコードをヒットさせたい場合、末尾にワイルドカードを付け「晴*」にする
  • 全単語の頭に「+」付加（AND検索のため。+何も付けない場合はOR検索になる）
  • 半角スペース区切りの文字列に変換（つまり、「+単語1 +単語2 +単語3」というような文字列になる）
  • MATCH～AGAINST構文を用いて検索
SELECT   *
FROM     `検索用テーブル`
WHERE    MATCH(`検索用単語集カラム`) AGAINST('加工した検索語句' IN BOOLEAN MODE)
ORDER BY `ユーザID` DESC

「IN BOOLEAN MODE」指定をしない場合は、AND検索やNOT検索（単語の頭に-を付加）などができなくなり、
代わりに結果が自動的にマッチ率でソートされるようになります。

SELECT   *
FROM     `検索用テーブル`
WHERE    MATCH(`検索用単語集カラム`) AGAINST('加工した検索語句')
-- ORDERは指定不要

おしまい

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

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