目次:

• FULLTEXT概要
• 使用にあたっての制約
• 日本語 (マルチバイト文字) の対応状況
• 押さえておくべき挙動・仕様
  • 全レコードの50%以上が該当する検索語は除外される
  • 4文字未満の検索語は無視される
  • stop words リストにある検索語は無視される
  • ' とか + とかは無視される
  • FULLTEXTインデックスがあると INSERT/UPDATE 処理が遅くなる
  • UTF8ならば文字のゆらぎを吸収してくれる
• 全文検索 - 2種類の特殊モード
  • 1. QUERY EXPANSION MODE
  • 2. BOOLEAN MODE
• 日本語でFULLTEXT全文検索
  • 1. 事前に形態素解析
  • 2. Ngram を駆使する
• FULLTEXT以外の日本語全文検索ソリューション
  • 1. SennaをMySQLに組み込む
  • 2. MySQLとは切り離して、アプリ側で機能を担保する
• 関連情報

FULLTEXT概要

FULLTEXTとは、v3.23.23以降のMySQLに標準搭載されている全文検索インデックスのこと。デリミタ文字 (半角スペース等) で区切られた複数の単語から成るカラムに対してFULLTEXTインデックスを張っておき、

sql> alter table hoge add fulltext( myText );

sql> insert into hoge(myText) values('I love lucy.');

それを専用の MATCH ... AGAINST 構文を用いたSELECT句で検索する事により、マッチ率の高い順 (近似値順) にソートされたレコードセットを取得 することができる仕組み。つまり、MySQL単体で完結する全文検索の仕組み。

sql> select * from hoge where match( myText )
     against('lucy love');

→ lucy と love が含まれるレコード群が、良い感じに順位付けされて返って来る

▲RETURN TO TOP

使用にあたっての制約

• MyISAM型のデータベースでのみ使用可能。
• 対象文章は単語ごとにデリミタ文字 [ ,.] で区切られている必要あり。 つまり日本語はそのままでは検索できません (対策は後述)。

▲RETURN TO TOP

日本語 (マルチバイト文字) の対応状況

• MySQL 4.1.1 以降はマルチバイト文字も対応。
• ただし Unicode は utf8 のみ対応 （ucs2はNG）。

という事で v4.1.1 以上であれば、sjis/ujis/utf8 どれでもOKになったようです。なので、後は単語のデリミタ区切りさえどうにかすれば、日本語でもFULLTEXTが使える模様。

▲RETURN TO TOP

押さえておくべき挙動・仕様

全レコードの50%以上が該当する検索語は除外される

通常のLIKE検索と違い、FULLTEXT全文検索の場合はそんなルールが適用されます。文中に頻繁に登場する語句、例えば文末の 「です」「ます」 や、接続詞の 「が」「の」「を」 等をキーワードとして検索した際、全レコード中の半数以上に出現している場合はこれらキーワードは除外・無視されます。

ありがちな落とし穴） とりあえずFULLTEXTを試したくて、適当なテーブル作って、適当に 1-2 レコードを追加した上で MATCH ... AGAINST 検索を叩いてみたら・・・おいおい結果ゼロ件て何よ？ってケース。レコード数が少ないと、それだけ50%ルールが適応される率が高いので、挙動がおかしく見えてしまいます。※公式マニュアルにも、「テストする際は必ず3レコード以上で試してね」と記述アリ。

この50%ルールを外すことは通常はできません (ソースコードを修正すれば可) 。強いて言えば後述する BOOLEAN MODE に切り替えると、このルールは適応されなくなります (その代わり検索結果も大分変わるけど)。

▲RETURN TO TOP

4文字未満の検索語は無視される

デフォルト状態だと、"cat" "dog" "マウス" などの検索語はすべて、「4文字未満の単語」として、無視されてしまいます。公式マニュアルのコメント部分でも、英語圏の方々が「えぇー、それはおかしいっしょ？」と異議申し立てをしていますが、デフォルト仕様はそうなっています。

変更方法） root権限にて、/etc/my.cnf 等に対して下記の記述を追加することで任意の文字数に変更できるようです:

[mysqld]
ft_min_word_len=1

記述後、変更を有効にするためには mysqld を再起動した上で、FULLTEXTインデックスを再作成する必要があります。

※似たようなので「各検索語の最大文字数」を指定する ft_max_word_len ってのもある模様。デフォルト値はコマンドラインで 'myisamchk --help' と叩けば確認できます (v4.1.20だと84文字)。

▲RETURN TO TOP

stop words リストにある検索語は無視される

デフォルトの stop words リストはこちら。"the" や "some" 等、ここに含まれている語句はすべて「重みを持たない単語」として無視されます。デフォルトでは日本語系は何も追加されていませんが、my.cnf に ft_stopword_file 記述を足すことで好きにカスタマイズできるようです (詳細はマニュアルを参照の事）。

▲RETURN TO TOP

' とか + とかは無視される

ascii 文字セットに関しては、FULLTEXTが扱うのは letters [a-zA-Z], digits [0-9], underscores [_] のみだそうです。その他文字は検索語・検索対象テキストどちらの場合も除外した上で処理が進みます。例外として ' (single-quotation) だけは、単語中に1度だけ出現が認められているとのこと (例 that's / dad's)。詳しくは公式ドキュメントを参照の事。

▲RETURN TO TOP

FULLTEXTインデックスがあると INSERT/UPDATE 処理が遅くなる

FULLTEXTの判りやすい downside (代償) がこれかと。レコードに変更があるたびにインデックスにも変更を加えに行くんですが、これが結構重いです。しかもテーブルの全体レコード数が増えれば増えるほど、遅くなっていきます。なので頻繁に INSERT/UPDATE が走るようなテーブルに FULLTEXT は不向きかと。

バッチ処理などで、レコードを一括ロードする場合にはTIPSがあって、ロードする前に一旦FULLTEXTを削除した上で全件ロードを実行 → ロードが終わったら改めて FULLTEXT を張りなおすと、全体効率がかなり上がるとのこと。

sql> alter table hoge drop index myText;
sql> ... 全件ロード処理 ...
sql> alter table hoge add fulltext(myText);

▲RETURN TO TOP

UTF8ならば文字のゆらぎを吸収してくれる

MySQLの仕様として、検索の際に ひらがな <> カタカナ、全角 <> 半角 を同じ文字として認識してくれます。

sql> select * from hoge where match( myText )
     against( 'いんたーねっと' );

→カタナカ表記 "インターネット" を含むレコードも返す

sql> select * from hoge where match( myText )
     against( '１２３' );

→半角数字 "123" を含むレコードも返す

全文検索の仕組みとしては、嬉しい仕様かと。

この仕様を有効にするには、テーブルの create 文に以下の記述を追加します:

create table hoge (
  id int primary key,
  ...
) default charset utf8 collate utf8_unicode_ci;

逆に、このゆらぎ吸収の仕様をOFFにしたいならば、上記記述を create 文から消すだけでOK。

▲RETURN TO TOP

全文検索 - 2種類の特殊モード

FULLTEXTを使った全文検索には２つの特殊モードがあります:

1. QUERY EXPANSION MODE
2. BOOLEAN MODE

1. QUERY EXPANSION MODE

v4.1.1にて追加。検索語での結果に加えて、類似・関連していると思われるレコードも一緒に返してくれるモード。言い換えると、MySQLが関連した結果をレコメンドしてくれるモード。

sql> select * from hoge where match( myText )
     against( 'database' with query expansion );

→ 語句 "database" が含まれるレコードに加えて、 "MySQL" や
   "PostgreSQL" が含まれるレコードを返してくれる。

通常通り、検索語 "database" で全文検索した後、返ってきたレコードセットの最初の1-2個目から、関連語句をチョイスした上で、それらで再度全文検索を投げる... 合計2回のFULLTEXT検索が実行される模様。詳しくは公式マニュアルを参照の事。

▲RETURN TO TOP

2. BOOLEAN MODE

v4.0.1にて追加。マッチ率の高い順でソート... という概念を省いて、単純に、より機械的に「これら語句を必ず含むレコード」や、「この語句を含んで、この語句を含まないレコード」を検索できるモード。特徴は以下の通り：

• 50%制約が無い
  全レコードの50%に検索語が含まれていても、それらすべてを返します。
• 結果が近似値ソートされない
  マッチ率うんぬんという概念は無くなります。単にヒットした順にレコードが返ります。
• キーワードが必須 '+'、キーワードを含まない '-' の特殊指示が使える
  検索語の前に + や - をつける事で検索条件をコントロールできます。 このほかにも幾つかのキーワードが使えるようです。
• ワイルドカード * を使った前方一致検索が可能
  検索語の後ろに * をつけることで前方一致検索ができます。先頭に * をつけても後方一致検索にはならない点に注意。
• キーワードの最短・最長文字数制限は有効
• stop words リストも有効

使い方例） "MySQL" を含んで "database" を含まない

sql> select * from hoge where match( myText )
     against( '+MySQL -database' in boolean mode );

使い方例） ワイルドカード検索 "dataほにゃらら"

sql> select * from hoge where match( myText )
     against( 'data*' in boolean mode );

※あと、使いどころが判りませんが、FULLTEXTインデックスを張っていないカラムに対しても、この BOOLEAN MODE の match ... against 検索は使えるそうです。その代わり激遅ですよ、とのこと。

▲RETURN TO TOP

日本語でFULLTEXT全文検索

前述の通り、日本語文章そのままではFULLTEXT検索できません。対策としては、僕の理解範囲では2通りの方法があります。

1. 事前に形態素解析 (わかち書き)
2. Ngramを駆使する

1. 事前に形態素解析 (わかち書き)

あらかじめ文章を形態素解析にかけ、単語レベルまで分解、それらをスペース区切りのテキストとして格納しておけば、日本語でも問題なくFULLTEXTが活用できるようになります。形態素解析は MeCab や Chasen 等のライブラリを駆使することで実現できます。アプリ側でMySQLにデータを INSERT/UPDATE する際に、事前に形態素解析 → スペース区切りの文字列に整形 → SQL発行 という処理手順になります。

懸念点) ただでさえINSERT/UPDATEに伴うFULLTEXTの更新は遅いのに、加えて形態素解析の処理時間がそこに加算されること。

▲RETURN TO TOP

2. Ngram を駆使する

日本語文章をn文字ごとに規則的に分割しておきつつ (Ngram)、これらを BOOLEAN MODE で検索することで、LIKE検索に近い形の日本語全文検索を実現する手法。

例で示すと、「今日もお仕事」という文章を Ngram 変換したテキストがテーブルに格納されていたとして、

今日 日も もお お仕 仕事 事

これに対して「お仕事」という語句で検索する場合は、この検索語も Ngram 変換させた上で BOOLEAN MODE の全文検索にかけます:

sql> select * from hoge where match( myText )
     against( '+お仕 +仕事' in boolean mode );

するといい感じに、期待する「今日もお仕事」レコードがヒットする、といった具合。

Ngramだと (言語毎にロジックが異なる) 文章の形態素解析が不要なことから、グローバル企業の全文検索サービスでよく採用される手法だそうです。そのNgramと、MySQLの FULLTEXT Boolean Mode を組み合わせる事でイイ感じの全文検索が実現します。

特徴をまとめると以下のようになります：

• 形態素解析が不要 = よりお手軽
• データの INSERT/UPDATE 時に文章をNgram化させる必要あり
• 検索をかける際も、検索語をNgram化させる必要あり
• 検索結果が近似値ソートされない

※このNgram手法を導入した際に、どのくらいの精度・パフォーマンスになるのか、および、テキストをNgram化させる為のモジュールの紹介などについては後日別のエントリでまとめようと思います ≫MySQL FULLTEXT + Ngram : LIKE検索より数十倍高速な、お手軽 日本語全文検索 について

▲RETURN TO TOP

FULLTEXT以外の日本語全文検索ソリューション

ここまでずっとFULLTEXTについて説明してきましたが、FULLTEXT以外の選択肢もあります。確かにFULLTEXTはMySQL単体で完結していてお手軽に見えますが、手軽さよりも実行速度や検索精度にこだわるのであれば、迷わずこっちを選ぶべきでしょう。

1. SennaをMySQLに組み込む

有限会社未来検索ブラジルが公開してくれている組み込み型全文検索エンジン - SennaをMySQLに組み込む事で、FULLTEXTインデックスを利用せずとも、MATCH ... AGAINST 構文での日本語全文検索が使えるようになります。アプリ側の改修が不要なのと、「高速なインデックス更新」とうたっている辺りが素敵。 ≫Sennaについてもっと詳しく...

懸念点） いい事ずくめに見えますが、１点あるとすれば、SennaをMySQLに組み込むにはMySQL自体をソースからコンパイルする（しなおす）必要があること、です。

▲RETURN TO TOP

2. MySQLとは切り離して、アプリ側で機能を担保する

NamazuやSenna等の全文検索ソリューションをアプリケーション側に組み込むかたち。Perlでいうと Search::Namazu や Senna モジュール等のお世話になる感じです。レコードの INSERT/UPDATE をMySQLとこれらソリューションの双方に対して行うことになるので、整合性維持に気を使う必要が出てくるのが懸念点。

▲RETURN TO TOP

関連情報

• MySQL 4.1 日本語環境設定方法 (キャラクタセット設定方法) - iandeth.
• MySQL 4.1 日本語環境設定方法 (キャラクタセット設定方法) part.2 - iandeth.
• MySQL 4.1 日本語環境での使用時の注意点／関連情報まとめ - iandeth.

▲RETURN TO TOP