勉強日記

チラ裏

SQL Antipatterns ch17 Poor Man's Search Engine

SQL 勉強メモ

pragprog.com

Poor Man's Search Engine

You don't have to use SQL to solve every problem.

  • SQLで速くて正確な検索機能を実現することは、見かけによらず難しい

Objective: Full-Text Search

  • SQLの原則として、カラムはatomicである
    • 値と値の比較はできるが、値まるごとの比較である
    • 部分文字列の比較は効率や正確さを欠く運命にある

Antipattern: Pattern Matching Predicates

  • パターンマッチング
  • 欠点
    • パフォーマンス低い
      • インデックス効かない
    • 意図しないマッチ
      • %one%がlonelyにマッチしちゃったり
  • REGEXPなら単語境界を指定できたりする 
SELECT *
  FROM Bugs
 WHERE description
REGEXP '[[:<:]]one[[:>:]]';

How to Recognize the Antipattern

  • こういうのが聞こえてきたら注意:
    • 「LIKE式のワイルドカードの間に変数を挿入するにはどうすればいい？」
    • 「複数の単語を含んでいて、特定の語は含まず、所定の語の変形も含む文字列を検索するには？」
    • 「記事数増えたら遅くて使えなくなった」
      • DBの容量が増えると、本アンチパターンのスケーラビリティの乏しさが露見する

Legitimate Uses of the Antipattern

  • 滅多に発行しないクエリなら良い
    • インデックスによる利点が少ない
  • 単純な要件なら良い

Solution: Use the Right Tool for the Job

  • 標準SQLを使わない
  • 標準SQLを使うにしても、部分文字列のマッチングよりも概して効率的な方法がある

Vendor Extensions

  • 特定のDB製品依存で良いならば、高パフォーマンスを得るうえで最善の選択

Full-Text Index in MySQL

  • MyISAMストレージエンジン専用でFULLTEXT INDEXをサポート
    • 対象のデータ型
      • CHAR
      • VARCHAR
      • TEXT
    • MATCH関数で検索

Text Indexing in Oracle

  • CONTEXTインデックス
  • CTXCATインデックス
    • 短いテキスト用
    • CATSEARCH演算子で検索
  • CTXXPATHインデックス
  • CTXRULEインデックス
    • 類別用

Full-Text Search in Microsoft SQL Server

  • ストアドプロシージャでfull-textインデックス作成
  • CONTAINS演算子で検索

Text Search in PostgreSQL

  • TSVECTORデータ型
    • 検索用データ構造
  • さらに転値インデックス(GIN index: generalized inverted index)を作成する

Full-Text Search (FTS) in SQLite

  • SQLiteの標準的なテーブルは効率的な全文検索をサポートしない
  • 検索特化のvirtual table拡張を使う
    • FTS1
    • FTS2
    • FTS3

Third-Party Search Engines

Roll Your Own

英語

  • deceptively difficult
    • 見かけによらず難しい
  • gulf
    • 隔たり