SQL で全文検索を実装する
全文検索はテキスト内の単語にインデックスを付け、高速な検索を可能にします。FULLTEXT インデックス、tsvector、ランキングの使い方を学びましょう。
- 1
全文検索は、テキストを単語にトークン化し、語幹を抽出して、高速な検索のためにインデックス化します。MySQL では FULLTEXT インデックスと MATCH() AGAINST()、PostgreSQL では tsvector と tsquery と GIN インデックス、SQLite では FTS5 仮想テーブルを使います。機能：単語マッチ、フレーズマッチ、ランキング (関連度スコア)、語幹抽出、ストップワード、類義語です。
- 2 SQL フォーマッター →
全文検索クエリを整形します。MySQL なら SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database' IN NATURAL LANGUAGE MODE)、PostgreSQL なら SELECT * FROM articles WHERE to_tsvector(body) @@ to_tsquery('database') です。整形することで検索式が明確になります。
- 3 正規表現テスター →
全文検索インデックスを使わない単純なテキスト検索には、LIKE または正規表現を使います。LIKE '%word%' は低速 (インデックスが効かない) ですが、どこでも動作します。正規表現はより強力ですが、同じく低速です。本番環境の検索には、全文検索インデックスか専用検索エンジン (Elasticsearch、Meilisearch) を使ってください。
よくある質問
- 全文検索と LIKE はどう違いますか？
- LIKE '%word%' は全行を走査するため (インデックスが効かない)、大きなテーブルでは低速です。全文検索は転置インデックスを使うため高速で、ランキング、語幹抽出、フレーズマッチにも対応します。小さなテーブルでの単純な部分文字列マッチには LIKE を、大きなテーブルでテキスト内容を検索するには全文検索を使ってください。
- SQL の全文検索と Elasticsearch のどちらを使うべきですか？
- 1 つのデータベース内での単純な検索なら、SQL の全文検索で十分であり、シンプルです。複雑な検索 (ファセット、あいまい検索、複数エンティティ、関連度チューニング) には Elasticsearch や Meilisearch を使ってください。トレードオフは次のとおりです。SQL はシンプルですが機能が限られ、検索エンジンは強力ですがインフラが複雑になります。
このタスクを解決するワークフロー
関連ツール
SQL フォーマッター
Beautify or minify SQL queries online. Uppercase keywords, indent clauses, or compress to a single line。無料、プライベート、100%クライアントサイド。 ツール
正規表現テスター
Test regular expressions with match highlighting, capture groups, and replace preview。無料、プライベート、100%クライアントサイド。 ツール
正規表現置換ツール
Find and replace text using regular expressions。無料、プライベート、100%クライアントサイド。 ツール