Posted inMySQL

MySQL Full-Text Search: アプリケーション向け超高速・高精度検索の秘訣

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

実問題: アプリケーションにおけるLIKE句による検索の遅延

プログラマーの皆さんならきっと、何らかのアプリケーションで検索機能を構築した経験があるでしょう。小規模なプロジェクトでデータが少ない場合、SELECT * FROM posts WHERE title LIKE '%検索%' OR content LIKE '%検索%'のようなクエリは問題なく機能するように見えます。シンプルで分かりやすく、すぐに実行できます。

しかし、プロジェクトが成長しデータ量が大幅に増加すると（例えば、私が運用している本番環境のMySQL 8.0データベースには約50GBのデータがあります）、LIKE '%キーワード%'というクエリは悲劇を招く可能性があります。一般的なキーワード一つだけで、応答時間は急増し、サーバーは重くなり、ユーザーは1秒ごとに待たされることに苛立ちを感じるでしょう。

さらに、LIKEメソッドには他にも多くの制限があります。

  • 関連性ランキング (Relevance Ranking) の欠如: 返される結果は、キーワードがあるかないかだけです。どのドキュメントがユーザーの検索語句に最も適しているかを知ることはできません。
  • パフォーマンスの低さ: %キーワード%の場合、MySQLは検索カラムのインデックスを効率的に使用できません。これにより、テーブル全体のフルスキャンが発生し、特に大規模なデータテーブルでは非常に多くのリソースを消費し、非常に時間がかかります。
  • ベトナム語における困難: ベトナム語の記号、同義語の処理、またはサジェスト検索は、アプリケーション側で非常に複雑な処理ロジックを必要としない限り、LIKEではほとんど不可能です。

原因分析: なぜLIKE句は全文検索に適さないのか？

LIKE '%キーワード%'の根本的な問題は、MySQLの処理方法にあります。検索語句の先頭にワイルドカード文字 (%) を配置すると、MySQLは通常のインデックス（B-Treeインデックスなど）を効率的に利用できません。その代わりに、各行を一つずつスキャンし、カラムの内容を検索パターンと比較せざるを得なくなります。想像してみてください、あなたのデータベースに数百万の記事があるとしたら、これは数百万冊の本の各ページを読んで一つのキーワードを探すのと何ら変わりません！

B-Treeインデックスは、順序に基づいた検索のために設計されています。例えば、「キーワード」で始まる値 ('キーワード%') や正確な値を検索する場合に非常に効率的です。しかし、「キーワード」を含む値 ('%キーワード%') を検索したい場合、B-Treeの構造はもはや役に立ちません。

さらに、LIKEは単純な文字列マッチングのみを実行します。文脈を理解せず、単語の概念や自然言語処理の機能も持ちません。したがって、LIKEにとって「 điện thoại 」（電話）と「 smartphone 」（スマートフォン）の検索は、ユーザーが同様の結果を期待するかもしれないにもかかわらず、まったく異なる2つのクエリとなります。

検索問題に対するその他の解決策

今日の主要な解決策に深く入り込む前に、皆さんが既に知っているかもしれない、あるいは遭遇したかもしれないいくつかの他のアプローチを概説します。

1. LIKE句を使用するが、しかし…

LIKE 'キーワード%'（先頭に%なし）を使用してインデックスを利用することを考える人もいるかもしれません。この方法は、ユーザーが常にプレフィックスで検索する場合により高速になる可能性があります。しかし、これは明らかに全文検索ではありません。ユーザーは通常、テキスト内の任意の場所にあるキーワードを検索したいと考えています。

もう一つの方法は、独自のキーワードテーブルを構築することです。記事の内容をキーワードに分析し、このテーブルに保存し、その後キーワードテーブルを検索します。しかし、この方法は、特に元のコンテンツが頻繁に変更される場合、実装とメンテナンスに多大な労力を要します。

2. 外部検索エンジン (Elasticsearch, Solr) の利用

複雑な検索要件と優れたパフォーマンスが求められる超大規模システムの場合、ElasticsearchやApache Solrのような専用ツールが最良の選択肢となります。これらは検索に特化して構築されており、強力な拡張性、自然言語分析、ファジー検索、ファセット検索などをサポートしています。ただし、これらのシステムを統合して管理することはかなり複雑です。追加のサーバー、深い専門知識、そしてかなりの運用コストが必要となります。

3. MySQL Full-Text Search: 理想的なバランスの解決策

これこそが、今日私が紹介したいソリューションです。MySQLは、データベース内で直接、効率的な全文検索を実行できるFull-Text Search (FTS) 機能を内蔵しています。

FTSは、LIKEのシンプルさと専用検索エンジンの強力さの間で素晴らしいバランスを提供します。私が運用している50GBのMySQL 8.0本番データベースでは、これらの最適化によりクエリ速度が劇的に向上し（例: 数十秒から1秒未満へ）、サーバーの負荷が大幅に軽減され、ユーザーエクスペリエンスが向上しました。

最良の方法: MySQL Full-Text Search の使い方

MySQL Full-Text Searchは、テキストカラムに特別なインデックス（Full-Text Indexes）を作成することで機能します。検索を実行すると、MySQLはこれらのインデックスを使用してキーワードを含むドキュメントを迅速に特定します。さらに、各結果の関連性も計算できます。

1. Full-Text パーサーの種類

MySQL FTSは、主に3つのクエリモードをサポートしています。

  • IN NATURAL LANGUAGE MODE: デフォルトモードで、自然言語形式で指定されたキーワードを検索し、関連性の降順で結果を返します。
  • IN BOOLEAN MODE: ブール演算子（例: +, -, ""）を使用して、必須の単語、禁止される単語、または正確な語句を指定することで、クエリをより詳細に制御できます。
  • WITH QUERY EXPANSION: 関連性の高いドキュメントから関連語句を含めることで、元のクエリを拡張し、追加の潜在的な結果を検索します。

2. Full-Text インデックスの作成

FTSを使用するには、まず検索したいカラムにFULLTEXTインデックスを作成する必要があります。以下にpostsテーブルの例を示します。


CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

FULLTEXTインデックスを追加するには、次のように実行できます。


ALTER TABLE posts ADD FULLTEXT(title, content);

または、テーブル定義時に直接作成することもできます。


CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT(title, content)
);

重要な注意点: FULLTEXTインデックスは、CHAR、VARCHAR、またはTEXT型のカラムでのみ機能します。

3. Full-Text Search を用いた検索の実行

3.1. 自然言語モードでの検索 (IN NATURAL LANGUAGE MODE)

これは最もシンプルで一般的なモードです。MySQLは自動的に返される結果の関連性を評価し、ランク付けします。


SELECT id, title, content,
       MATCH(title, content) AGAINST ('mysql の使い方' IN NATURAL LANGUAGE MODE) AS relevance_score
FROM posts
WHERE MATCH(title, content) AGAINST ('mysql の使い方' IN NATURAL LANGUAGE MODE)
ORDER BY relevance_score DESC;

上記のコマンドでは、以下の点に注意する必要があります。

  • MATCH(title, content): 検索したいFULLTEXTインデックスを持つカラムを指定します。
  • AGAINST ('mysql の使い方' IN NATURAL LANGUAGE MODE): 検索語句と検索モードを提供します。
  • AS relevance_score: MySQLは、各結果の関連度を示す数値「関連性スコア」を返します。値が高いほど、結果は検索キーワードに密接に関連しています。

3.2. ブールモードでの検索 (IN BOOLEAN MODE)

このモードでは、特殊な演算子を使用することで、クエリをより正確に制御できます。

  • +: この単語は結果に*必須*です。
  • -: この単語は結果に*含めてはなりません*。
  • < および >: その単語の関連性への貢献度を変更します（例: >database <sqlは’sql’よりも’database’を優先します）。
  • "キーワード": 正確な語句を検索します。
  • *: 単語の末尾のワイルドカード演算子（例: mysql*はmysql、mysql_dbなどを検索します）。

-- 'mysql'を含むが'replication'を含まない記事を検索
SELECT id, title FROM posts
WHERE MATCH(title, content) AGAINST ('+mysql -replication' IN BOOLEAN MODE);

-- 'mysql のガイド'という正確な語句を検索
SELECT id, title FROM posts
WHERE MATCH(title, content) AGAINST ('"mysql のガイド"' IN BOOLEAN MODE);

-- 'database'と'sql'を検索し、'database'を優先
SELECT id, title FROM posts
WHERE MATCH(title, content) AGAINST ('>database <sql' IN BOOLEAN MODE);

3.3. クエリ拡張による検索 (WITH QUERY EXPANSION)

このモードは、検索すべき正確なキーワードが不明な場合に特に役立ちます。MySQLは最初の検索を実行し、その後、最も関連性の高いドキュメントから関連キーワードを使用して2回目の検索を実行し、結果の範囲を広げます。


SELECT id, title FROM posts
WHERE MATCH(title, content) AGAINST ('sql' WITH QUERY EXPANSION);

4. Full-Text Search の設定と最適化

FTSを最も効率的に機能させるには、特にベトナム語の場合、以下のいくつかの重要な設定に注意する必要があります。

  • ft_min_word_len: これは、MySQLがFTSインデックスに含める単語の最小長です。デフォルトは4です。ベトナム語では、「 ăn 」（食べる）、「 ở 」（いる）、「 đi 」（行く）のように4文字未満の単語が多くあります。この値をmy.cnfまたはmy.iniファイルで減らすことができます。

[mysqld]
ft_min_word_len = 2

変更後、MySQLを再起動し、変更を有効にするためにはFTSインデックスを再構築する（インデックスをDROPしてADDし直す）ことが必須です。

  • ft_stopword_file: MySQLには、インデックス作成時や検索時に無視されるデフォルトのストップワードリスト（例: 英語の「a」、「the」、「is」）があります。ベトナム語の場合、「 là 」（である）、「 và 」（と）、「 có 」（ある）、「 được 」（〜できる）のような一般的なベトナム語のストップワードを含む別のファイルを作成し、それらをインデックスから除外することをお勧めします。これにより、検索結果がより正確かつ高速になります。

[mysqld]
ft_stopword_file = /path/to/your/vietnamese_stopwords.txt

同様に、変更後にはMySQLを再起動し、FTSインデックスを再構築する必要があります。

  • 文字セット (Character Set) と照合順序 (Collation): テーブルとカラムがutf8mb4文字セットとutf8mb4_unicode_ci照合順序（または、利用可能であればutf8mb4_vietnamese_ci）を使用していることを確認してください。この設定により、MySQLはベトナム語のアクセント記号付き文字を適切に処理し、正確な検索結果を保証します。

私が運用している50GBの本番データベースにこれらの最適化を適用したことで、検索速度が著しく改善され、ユーザーによりスムーズな体験を提供できるようになりました。

5. 制限と利用すべき場面

MySQL Full-Text Searchは、一般的なウェブアプリケーション、ブログ、または中程度の内部検索要件を持つシステムにとって優れた選択肢です。展開と管理が簡単です。しかし、MySQLのFTSにはいくつかの制限があります。

  • 専用ツールには及ばない: ファジー検索、スマートなキーワードサジェスト、または多様な非構造化データに対する検索といった非常に複雑な要件の場合、ElasticsearchやSolrの方が依然として適切で最適化された選択肢となります。
  • 特定のデータ型のみをサポート: FULLTEXTインデックスはCHAR、VARCHAR、TEXT型のみに使用できます。
  • 設定変更時にインデックスの再構築が必要: ft_min_word_lenやft_stopword_fileなどの設定パラメーターを変更するたびに、FTSインデックスを再構築する必要があります。このプロセスは、大規模なデータテーブルではかなりの時間がかかる可能性があります。

結論

要約すると、MySQL Full-Text Searchは、多くのアプリケーションで高速かつ効率的な検索機能を実装するための強力で非常に便利な機能です。それはLIKEの低いパフォーマンスの問題を完全に解決し、複雑な外部検索システムを必要とせずに関連性ランキング機能を提供します。いくつかの小さな設定調整と適切なクエリモードを使用するだけで、ユーザーの検索エクスペリエンスを大幅に向上させることができます。ぜひ試してみて、その違いを感じてください！

Share:
Tags: