プアマンズ・サーチエンジン
キーワード検索を高速で正確に行うことは簡単ではない。
SQLにおいて文字列の部分一致による比較は非効率性や不正確さにつながる。
アンチパターン：パターンマッチ述語を使用する
SQLには文字列比較のためのパターンマッチがある。
キーワード検索等で一般的に使用されているLIKE演算子である。
LIKE演算子は0個以上の文字列と一致するワイルドカードを扱うことができる。
前後に％をつけることで文字列中に部分一致するワードとマッチすることができる。
パターンマッチはキーワード検索等で一般的だが問題点がある。
- パターンマッチはインデックスのメリットを受けることができない
- LIKE演算子を用いたパターンマッチでは意図しないマッチが生じる可能性がある
インデックスのメリットを受けることができない
文字列に対するパターンマッチはテーブルスキャンが行われる。
それによってパターンマッチでのデータ取得はパフォーマンスの低下につながる。
意図しないマッチが生じる
以下のようなクエリを発行し「円」という文字をパターンマッチをした際に表の中の文字列が全てヒットする。
SELECT * FROM Keyword WHERE keyword LIKE '%円%'
| keyword |
|---|
| 高円寺 |
| 日本円 |
| 円高 |
| 円安 |
| 三百円 |
| 円周率 |
| 円円円円円 |
上記のようなパターンマッチは極端でこのような登録はしないとは思うが、このような関係のないワードどうしが同じテーブルに格納されている場合、意図しないワードまで取得される可能性がある。
アンチパターンを用いても良い場合
・用途がシンプルな場合（検索したいケースが単純）
・パフォーマンスを考慮しなくても良いケース
解決策：適切なツールを使用する
- SQLの代わりに専用の全文検索エンジンを使用する
- SQL標準に準拠しつつも部分文字列マッチングより効果的な転置インデックスを使用する
ベンダー拡張
主要なデータベース製品は、全文検索の要件に対する独自の解決策を用意している。
これらの独自機能は標準化されておらず、ベンダー間での互換性もない。
使用するデータベース製品が１つである場合にはSQLクエリと親和性が高く、高パフォーマンスなテキスト検索を行うための最善策とも言える。
MySQLのフルインデックス
MySQLではMyISAMストレージエンジンのみがサポートするフルテキストインデックスが提供されている。
フルテキストインデックスを定義できるのはCHAR、VARCHAR、TEXT型の列。
※MySQL5.7までは日本語文書には使用できない
詳細についてはMySQL 5.6 リファレンスマニュアルを参照してください。
Oracleでのテキストインデックス
CONTEXT
- 単一のテキストの列に対してインデックスを作成
- インデックスを用いた検索にはCONTAINSを使用
- このインデックスはデータ変更に対する一貫性が維持されない
- PARAMETER(’’SYNC(ON COMMIT)') を追加すると自動で内容を同期
CTXCAT
- 短いテキストに特化したインデックス
- 同じテーブルの他の列と組み合わせて構造化
- インデックス化対象のデータがトランザクショによって更新されてもインデックスの一貫性は維持
CTXXPATH
- XMLドキュメントをexistsNode関数を用いて検索する用途に特化
CTXRULE
- CTXRULE型のインデックスは大量の文書解析ルールを設計し、分類結果を確認できる
詳細についてはOracle Text 概要を参照してください。
Microsoft SQL Serverでの全文検索
- SQL Server2000以降では全文検索がサポートされている
- 言語、シソーラス、データ変更時の同期など複雑な構成オプションが使用可能
詳細についてはSQL Server フルテキスト検索を参照してください。
PostgreSQLでのテキスト検索
- パフォーマンスを最適化するにはテキスト検索可能なTSVECTORを用いてコンテンツを格納
- PostgreSQLのテキスト検索演算子を用いてフルテキストインデックスを用い全文検索が可能
詳細についてはテキスト検索に関する型を参照してください。
SQLiteでの全文検索
- SQLiteの標準テーブルは効率的な全文検索をサポートしていない
- SQLiteの拡張機能のFTSを用いることで効率的な全文検索が利用できる
詳細についてはSQLite FTS5 Extensionを参照してください。
サードパーティーのサーチエンジン
データベースに依存せずにテキストの全文検索をしたい場合はデータベースから独立して動作する検索エンジンが必要
まとめ
問題を解決するために、必ずしもSQLを使う必要はありません
参考文献
