50Mレコードの検索機能設計¶

原文¶

Your team needs to build a search feature for 50M records Pick one: A) SQL LIKE query with indexes...

要約¶

5000万件のレコードに対する検索機能をどう設計するかという設計問題。SQL LIKE、Elasticsearch、その他のアプローチを比較検討する。

回答¶

この規模では Elasticsearch（または類似の全文検索エンジン） が最も適切な選択肢。ただし、要件によってはハイブリッドアプローチが最適な場合もある。

解説¶

問題の本質¶

50M（5000万）件のレコードに対して検索機能を構築するとき、何が課題になるかを整理する。

検索機能で考慮すべき要素：
1. レスポンスタイム（ユーザーが待てるのは通常100ms〜500ms以内）
2. 検索の種類（完全一致？部分一致？あいまい検索？）
3. 検索対象のデータ構造（テキスト？構造化データ？）
4. リアルタイム性（データ追加後すぐ検索可能にする必要があるか）
5. スケーラビリティ（データ量が今後さらに増えるか）

選択肢A: SQL LIKE + インデックス¶

SELECT * FROM products WHERE name LIKE '%検索キーワード%';

仕組み： - RDB（MySQL、PostgreSQL等）のLIKE句で文字列パターンマッチング - B-Treeインデックスを作成して高速化を図る

問題点を詳しく解説：

前方一致のみインデックスが効く

-- これはインデックスが効く（前方一致）
WHERE name LIKE '検索キーワード%'

-- これはインデックスが効かない（中間一致・後方一致）
WHERE name LIKE '%検索キーワード%'
-- → フルテーブルスキャンになる！

ユーザーが期待する検索は大抵「部分一致」なので、LIKE '%keyword%' になりがち。50Mレコードのフルテーブルスキャンは致命的に遅い（数十秒〜数分かかり得る）。

あいまい検索ができない
タイポ（"iPhone" → "iPnone"）に対応できない
同義語（"laptop" と "notebook PC"）の扱いが困難
日本語の形態素解析（"走った" → "走る" のステミング）ができない
スコアリング（関連度順）ができない
LIKEはマッチするかしないかの2値。「どれだけ関連性が高いか」の順位付けができない
ユーザーは「最も関連性の高い結果」を期待する
50Mレコードでの性能
フルテーブルスキャン: 数十秒〜数分
前方一致（インデックスあり）: 数十ms（ただし用途が限定的）

結論: 50M件の汎用検索にSQL LIKEは不適切。ただし、IDやコードの完全一致・前方一致検索には今でも有効。

選択肢B: Elasticsearch¶

仕組み：

Elasticsearchは「転置インデックス（Inverted Index）」を使う。これは検索エンジンの核心技術。

通常のインデックス（B-Tree）：
  レコードID → 内容
  1 → "東京タワーは港区にあります"
  2 → "スカイツリーは墨田区にあります"
  3 → "東京駅は千代田区にあります"

転置インデックス：
  単語 → レコードID
  "東京"     → [1, 3]
  "タワー"   → [1]
  "港区"     → [1]
  "スカイツリー" → [2]
  "墨田区"   → [2]
  "駅"       → [3]
  "千代田区" → [3]

「東京」で検索すると、転置インデックスから即座にレコード1, 3が返る。50Mレコードあっても、インデックスルックアップは O(1) 〜 O(log n) で完了する。

Elasticsearchが50M件で優れる理由：

転置インデックスによる高速検索
部分一致、あいまい検索、全文検索がすべて高速（通常10ms〜100ms以内）
データ量が増えても検索速度が線形に劣化しない

分散アーキテクチャ

50Mレコードの分散例：
シャード1: レコード 1〜10M      ← Node A
シャード2: レコード 10M〜20M    ← Node B
シャード3: レコード 20M〜30M    ← Node C
シャード4: レコード 30M〜40M    ← Node A
シャード5: レコード 40M〜50M    ← Node B

検索時：全シャードに並列でクエリを送り、結果をマージ

各シャードが並列に検索するため、データ量が増えてもノードを追加すればスケールする。

高度な検索機能
あいまい検索: "iPnone" → "iPhone" を見つけられる（Levenshtein距離ベース）
形態素解析: 日本語なら kuromoji プラグインで「走った」→「走る」に正規化
同義語辞書: "ノートPC" で検索して "ラップトップ" もヒット
スコアリング（TF-IDF / BM25）: 関連度の高い順にランキング
リアルタイムに近い更新
ドキュメント追加後、デフォルト1秒後に検索可能（near real-time）
refreshInterval の設定で調整可能

Elasticsearchの注意点・コスト： - RDBとのデータ同期が必要（Change Data Capture、Debezium等を使う） - メモリを多く消費する（JVMヒープ + OSファイルキャッシュ） - 運用コストが高い（クラスタ管理、シャード設計、インデックス管理） - トランザクション保証がない（検索用途特化）

選択肢C: その他のアプローチ¶

PostgreSQL全文検索 (tsvector / tsquery) - PostgreSQLに組み込みの全文検索機能 - 転置インデックスを使うのでLIKEより格段に速い - 50M件程度なら実用的な性能が出る - Elasticsearchほどの柔軟性はないが、追加インフラ不要 - 日本語対応にはpg_bigmやpgroonga拡張が必要

Apache Solr - Elasticsearchと同じLuceneベース - より成熟しているが、Elasticsearchの方がエコシステムが充実

Meilisearch / Typesense - 軽量な全文検索エンジン。セットアップが簡単 - 50M件だとやや限界があるかもしれない

実務での推奨アーキテクチャ¶

               ┌─────────────────────┐
               │   クライアント        │
               └──────┬──────────────┘
                      │
               ┌──────▼──────────────┐
               │   APIサーバー        │
               └──┬─────────────┬────┘
                  │             │
    完全一致/ID検索│             │全文検索/あいまい検索
                  │             │
          ┌───────▼───┐  ┌─────▼──────────┐
          │  RDB      │  │ Elasticsearch  │
          │ (PostgreSQL)│  │ (検索用)       │
          │ (正のデータ) │  │                │
          └───────┬───┘  └────────────────┘
                  │            ↑
                  │  CDC/同期  │
                  └────────────┘

RDBを正（Source of Truth） として、Elasticsearchを検索用レプリカとして使う
データの書き込みはRDBに行い、CDC（Change Data Capture）でElasticsearchに同期
完全一致・ID検索はRDBへ、全文検索・あいまい検索はElasticsearchへルーティング

面接で差がつくポイント¶

「なぜSQL LIKEがダメか」をインデックスの仕組みから説明できる
「転置インデックス」の概念を簡潔に説明できる
トレードオフ（運用コスト、データ同期の複雑さ）に言及できる
ハイブリッドアーキテクチャを提案できる（正のデータはRDB、検索はES）

リンク¶

元ポスト