SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

LIKE検索は使ったらダメな場合もある - 全文検索について

 SQLのLIKE検索は非常に便利です。しかし、データ量によっては使ってはいけません。

 例えば、
     WHERE
       備考 LIKE '%大阪%'

 とすれば備考欄に'大阪'が含まれているレコードをすべて取得することが可能ですが、当然、インデックスは使えません。必ずテーブルをフルスキャンすることになります。

 そもそも、LIKE検索をするような項目はデータ量が多く、数百Byteから数千Byteの領域を取っていることもあるでしょう。RDBMSのデータに対するアクセスは、カラム単位でもレコード単位でもなく、ブロック(ページ)単位になりますので、レコード数によっては非常に大量のアクセスが発生します。

 ブロック単位のアクセスはデータの入り方によって何とも言えないので、レコード長で考えるとすると、数百〜2000Byteのレコード長で1万件(数M〜20MByte)前後で、検索頻度が少ないのであればLIKE検索で十分でしょう。そういう条件で全文検索を作る、導入するのは少々オーバースペックです。

 それ以上のデータ量や検索頻度が高いとき、あるいは、同義語やあいまいな検索が必要な場合は、別途作る必要があります。

 まぁ、Googleなどの製品を買っても良いんですけどね。私が作ればスコアリングしないなら数人日でできるし、あいまいな検索も自由に作ることができるのでオリジナルで組んでしまう。

全文検索は主に2種類

 全文検索の方法は主に2種類あります。

 ■ 形態素解析分かち書き
   GoogleやYahooが採用している方式。
   辞書にヒットするキーワードをインデクシングしていく。
   辞書にない新語や造語は漏れる可能性がある。
   
   例)
     GoogleやYahooで「プラモ」と「プラモデル」で検索。
     本来であればプラモはプラモデルの中に含まれているので、
     プラモの方がヒットする件数が多くて当然ですが、
     プラモデルの方が多くヒットする。
     (型番などの検索に不向きです)

 ■ n-Gram
   OracleなどのDBが全文検索として提供している方式。
   文字をn字に切りインデクシングしていく。
   ゴミを拾いやすく、データ容量がたくさん要る。

   例)2-Gram
     今日はお天気ですね。
     → 2文字ずつ切ってインデクシングする
     今日
      日は
       はお
        お天
         天気
          気で
           です
            すね
             ね。

 どちらの方式でも、ハイブリッドにしても、インデクシングするのはトリガーで行えばリアルタイムにインデクシングできますし、SQLでもそこそこのスピードを出すことが可能です。SQLで行うメリットは他の並べ替えや抽出条件を簡単に追加することができる点です。

どの方式を選ぶか

   1万件までLIKE検索(ハードの能力と検索頻度にもよるけど)
     →
   数百万件までSQLで作り込み
     →
   数百万件を超えるとき、外付けのアプライアンス製品などを検討

 という順で検討すればよいと思います。

 というわけで、件(くだん)の岡崎市立図書館の検索機能は、最低限、n-Gramは使わないといけないパターンです。