2010年01月24日

SQLiteの全文検索を使ってみる

FTS3を使って全文検索してみた。

SQLiteの全文検索というと、昔はFTS1とか2とかのモジュールを使用する方式だったみたいだけど、現在はFTS3なるものがsqlite3.exeとかに含まれているので、得に下準備をすることなく使用できるっぽい。

公式の説明
http://www.sqlite.org/fts3.html

参考にしたサイト
http://cast-a-spell.at.webry.info/200806/article_15.html

フルテキストサーチする為にやることは以下の3つ。

・CREATE VIRTUAL TABLE テーブル名 USING fts3 でテーブルを生成する
・INSERTする時に、フルテキスト検索に使いたいカラムに、ヒットさせたいワードを半角スペースで区切りつつ登録
・MATCHを使って検索する

検索方法とかそれなりに用意されているので、そこそこの件数を処理するだけならSQLiteで十分かもしれない。



1. CREATEする

とりあえず、sentenceに文章を、wordsに検索用の単語を入れるようなテーブルを作ってみる
CREATE VIRTUAL TABLE tbl USING fts3(sentence, words);



2. INSERTする

1つ目のカラムに適当な文章を、2つ目のカラムに分かち書き(半角スペース区切り)した文章を入れる。
INSERT INTO tbl VALUES( '今日は良い天気だ', '今日 は 良い 天気 だ' );

この半角スペースで区切った単語たちに対して検索がかけられる。



3. 普通にSELECTする

WHERE句でMATCHを使って検索すると引っかかる
SELECT sentence FROM tbl WHERE words MATCH '今日';
  → 今日は良い天気だ

「今日」では引っかかるけど、「今」では引っかからない
SELECT sentence FROM tbl WHERE words MATCH '今';
  → empty set

複数の検索ワードを使うことも可能
SELECT sentence FROM tbl WHERE words MATCH '今日 天気';
  → 今日は良い天気だ



4. 前方一致でSELECTする

アスタリスクを使えば前方一致で引っかけれる
SELECT sentence FROM tbl WHERE words MATCH '今*';
  → 今日は良い天気だ

前方一致だけで、後方一致は無理
SELECT sentence FROM tbl WHERE words MATCH '*日';
  → empty set

複数の検索ワードを使うことも可能
SELECT sentence FROM tbl WHERE words MATCH '今* 天*';
  → 今日は良い天気だ



5. フレーズ検索でSELECTする

ダブルコーテーションでくくれば、フレーズ検索
SELECT sentence FROM tbl WHERE words MATCH '"今日 は 良い"';
  → 今日は良い天気だ

下記のような繋がってない検索ワードだと引っかからない
SELECT sentence FROM tbl WHERE words MATCH '"今日 天気"';
  → empty set



6. 距離を指定してSELECTする

NEARを使うと、指定された距離内に単語がいないと検索に引っかからない

下記の例だと、2個隣までは引っかかる
SELECT sentence FROM tbl WHERE words MATCH '今日 NEAR/1 良い';
  → 今日は良い天気だ

3個隣の「今日」と「天気」を指定すると引っかからない
SELECT sentence FROM tbl WHERE words MATCH '今日 NEAR/1 天気';
  → empty set



7. 複合条件でSELECTする

ORを使うと、どっちかが含まれていれば検索にヒットする
SELECT sentence FROM tbl WHERE words MATCH '今日 OR 存在しない言葉';
  → 今日は良い天気だ

NOTを使うと、存在しないことみたいな指定になる
と思ったけど、気のせいだったみたいだ
SELECT sentence FROM tbl WHERE words MATCH '今日 NOT 存在しない言葉';
  → empty set

単語の前にマイナス符号を付けると、その言葉が存在しない場合にヒットする
SELECT sentence FROM tbl WHERE words MATCH '今日 -存在しない言葉';
  → 今日は良い天気だ

存在する言葉でマイナスを付ければ、ヒットしない
SELECT sentence FROM tbl WHERE words MATCH '今日 -天気';
  → empty set



8. 性能を見てみる

とりあえずランダムな文字列を生成して、10万件ほどテーブルに突っ込む。
ちなみにSQLiteはトランザクション使わないと大量INSERTがもの凄く遅いので(10分待っても終わらなかったので、途中で一度切ったさ)、入れる時はBEGINとCOMMITを忘れない。

というわけで、10万と1件入った状態で計測してみる。
SELECT count(*) FROM tbl;
  → 100001

この程度のサイズなら、2ミリ秒程度で処理可能らしい
SELECT sentence FROM tbl WHERE words MATCH '今日';
  → 今日は良い天気だ
  → 1.873ミリ秒

前方一致も速度は変わらず
SELECT sentence FROM tbl WHERE sentence MATCH '今日*'
  → 今日は良い天気だ
  → 2.028ミリ秒

LIKEの前方一致検索だと、こんな速度
SELECT sentence FROM tbl WHERE sentence like '今日%'
  → 今日は良い天気だ
  → 194.502ミリ秒

並べて見るとこんな感じ。

1.873
2.028
194.502

というわけで、LIKEと比べると100倍速い。用意したテストデータは、sentenceに20文字、wordsはスペースが6個杯って26文字しか入ってない軽量のもの。これが10万件くらいであればパフォーマンス的には全く問題ないようで。

あと、条件を複数いれると、こんな感じ
SELECT sentence FROM tbl WHERE words MATCH '今* 天気 良*';
  → 今日は良い天気だ
  → 2.955ミリ秒



以上、SQLiteの全文検索を使用してみた際のメモでした。

例文を「今日は良い天気だ」にしたけど、「明日は晴れるかなぁ?」にしておけば良かったと少し後悔した。そっちの方が天和とか出そうだから。

2の2の核分裂!!!