SQL Antipatterns ch17 Poor Man's Search Engine

SQL勉強メモ

出典: 


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

  • パターンマッチング

    • LIKE
    • REGEXP
  • 欠点

    • パフォーマンス低い

      • インデックス効かない
    • 意図しないマッチ

      • %one%lonelyにマッチしちゃったり
  • REGEXPなら単語境界を指定できたりする

    • MySQL
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インデックス

    • CONTAINS演算子で検索
  • CTXCATインデックス

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

    • XML検索特化
    • existsNode演算子で検索
  • 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

  • Sphinx Search
  • Apache Lucene

    • Solrサブプロジェクト
  • 【補】Elasticsearch

Roll Your Own

  • 自前で転値インデックスを作るという選択肢

    • プロプライエタリな(独占的な)検索機能を使いたくない場合
    • SQLと別途検索エンジン製品をインストールしたくない場合

英語

  • deceptively difficult

    • 見かけによらず難しい
  • gulf

    • 隔たり