Posted inデータベース

SQLの「LIKE」はもう古い：Meilisearchで応答速度50msのインクリメンタルサーチを構築する

Database tutorial - IT technology blog
Database tutorial - IT technology blog

午前2時の呼び出し：SQLの「LIKE %query%」が本番環境を壊すとき

午前2時、私の電話が鳴り響きました。ECサイトを運営するクライアントのコンバージョン率が急落しており、ログを確認すると原因は一つ、検索バーでした。ユーザーが商品名を入力しても、データベースが追いついていなかったのです。平均レスポンス時間は3.5秒。現代のWebにおいて、3秒は永遠にも等しい時間です。ユーザーはキーを叩いた瞬間に結果が表示されること、つまり「インクリメンタルサーチ（Search-as-you-type）」を期待しています。

バックエンドを調査すると問題が判明しました。以前のエンジニアが、3つのテーブルにまたがる標準的なSQLのLIKE演算子を使って検索を実装していたのです。商品数が1,000点なら問題ありませんでしたが、カタログは50万点にまで膨れ上がり、バリエーションも多岐にわたっていました。データベースは、1回のキーストロークごとにフルテーブルスキャンを実行していたのです。大文字小文字を区別しないILIKEを追加したことで、CPU使用率はさらに上昇しました。私たちはスケーラビリティの壁にぶつかっただけでなく、全速力で激突していたのです。

なぜリレーショナルデータベースは検索に弱いのか

単なる行数だけが原因ではありません。PostgreSQLやMySQLのようなリレーショナルデータベースは、ACID特性や複雑なリレーションシップの維持を優先しており、高速なテキストランキング（順位付け）のために設計されているわけではありません。モダンな検索体験を無理やりSQLで実現しようとすると、主に3つのボトルネックが発生します。

  • タイポ耐性ゼロ： ユーザーが「iphone」ではなく「ipone」と入力すると、LIKEは何も返しません。たった1文字のミスで、売上の機会を逃してしまいます。
  • ランキングの関連性の低さ： SQLはどの結果が「より優れているか」をネイティブに理解しません。文字列が存在するかどうかを判定するだけです。タイトルの完全一致を説明文の部分一致より優先させるといった処理は、簡単にはできません。
  • ファセット検索のオーバーヘッド： カテゴリ、ブランド、価格帯（ファセット）ごとの件数を計算するには、複雑なGROUP BYクエリが必要です。これらはデータセットが大きくなるにつれて、指数関数的に遅くなります。

比較検討：Elasticsearch vs Algolia vs Meilisearch

この問題を解決するために、3つの選択肢を評価しました：

  1. Elasticsearch： 強力な定番ツールですが、リソースを大量に消費します。動作させるだけで4GBのヒープメモリを必要とすることが多く、複雑なJVMチューニングも不可欠です。今回のプロジェクトには過剰でした。
  2. Algolia： プレミアムなSaaS製品。驚異的に速く、メンテナンスも不要です。しかし、検索ボリュームに応じてコストが急激に上昇します。また、クライアントは厳格なプライバシーコンプライアンスのため、データをオンプレミスで保持する必要がありました。
  3. Meilisearch： Rust製のオープンソースエンジンで、エンドユーザー向けの検索に特化して設計されています。軽量で、デフォルトでタイポ耐性を備えており、ファセット検索もネイティブに処理できます。

私は**Meilisearch**を選びました。インフラを軽量に保ちつつ、最高のパフォーマンス対設定比を実現できるからです。

実装：SQLからMeilisearchへ

1. エンジンの起動

MeilisearchをDockerでデプロイしました。これが、ローカルライブラリの競合を気にせず、本番環境のインスタンスを確実に実行する最も信頼できる方法です。

docker run -it --rm \
  -p 7700:7700 \
  -v $(pwd)/meili_data:/meili_data \
  getmeili/meilisearch:latest \
  meilisearch --master-key="MY_SECRET_MASTER_KEY"

2. データの変換とインポート

Meilisearchは構造化された JSONを利用します。クライアントのデータは巨大なレガシーCSVファイルに閉じ込められていました。使い捨てのPythonスクリプトを書くのを避けるため、toolcraft.app/ja/tools/data/csv-to-jsonを使ってファイルを変換しました。ブラウザ内で動作するため、データがマシン外に出ることはありません。これにより、正規表現によるクリーンアップ作業を約20分短縮できました。

`products.json`の準備ができたら、cURLを使ってインデックスにデータをプッシュします：

curl -X POST 'http://localhost:7700/indexes/products/documents' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer MY_SECRET_MASTER_KEY' \
  --data-binary @products.json

3. ファセットとソートの設定

ファセット検索により、ユーザーはブランドや評価でフィルタリングできるようになります。Meilisearchでは、インデックスを最適化するために、これらの属性を明示的に定義する必要があります。これは、高性能なフィルタリングを実現するための必須ステップです。

curl -X PATCH 'http://localhost:7700/indexes/products/settings' \
  -H 'Authorization: Bearer MY_SECRET_MASTER_KEY' \
  -H 'Content-Type: application/json' \
  --data-binary '{
    "filterableAttributes": ["category", "brand", "rating"],
    "sortableAttributes": ["price", "rating"]
  }'

4. フロントエンド：即時フィードバックループ

「即時」の感覚を実現するために、`meilisearch-js` SDKを使用しました。フロントエンドはメインAPIをバイパスして、Meilisearchに直接クエリを投げます。このエンジンは読み取り負荷の高いワークロードに最適化されているため、数百の同時リクエストを50ms未満のレイテンシで処理できます。

import { MeiliSearch } from 'meilisearch'

const client = new MeiliSearch({
  host: 'http://localhost:7700',
  apiKey: 'SEARCH_ONLY_PUBLIC_KEY', // クライアントサイドでは必ず制限付きのキーを使用してください！
})

const index = client.index('products')

async function searchProducts(query) {
  const results = await index.search(query, {
    facets: ['category', 'brand'],
    attributesToHighlight: ['title'],
  });
  return results;
}

成果：レスポンス時間12ms

効果はすぐに現れました。デプロイ後、平均検索レイテンシは3,500msからわずか12msに短縮されました。検索は非常にスムーズになりました。ユーザーが「samung」と入力しても、組み込みのレーベンシュタイン距離アルゴリズムのおかげで、エンジンは正しく「Samsung」を特定しました。

サイドバーのフィルターも大幅に改善されました。以前はカテゴリをクリックするたびに重いSQLのJOINが必要でしたが、今ではそれらのファセットが瞬時に更新されます。検索処理をメインデータベースから切り離したことで、DB全体のCPU使用率が40%削減されました。これにより、アプリケーション環境全体が安定しました。

まとめ

メインデータベースにすべてを任せるのはやめましょう。SQLはトランザクションやデータの整合性には優れていますが、専用の検索エンジンはスピードと関連性のために作られています。検索が遅いと感じたら、SQLテーブルにインデックスを追加するだけで済ませず、Meilisearchのような専用ツールへの移行を検討してください。それはユーザー体験を完全に変え、そして何より、午前2時の呼び出しからあなたを解放してくれるはずです。

Share:
Tags: