Posted inMySQL

MySQLにおけるベトナム語検索の極意：低速なLIKE句から最適化されたFull-Text Searchへ

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

「ベトナム語検索」という名の悩み

ベトナム人向けのアプリケーションを開発する際、「アクセント記号なしで入力しても, 記号ありの結果を出す」という機能は、単なるおまけではなく必須要件です。ユーザーは常に ‘dien thoai’ と入力して ‘điện thoại’（電話）がヒットすることを期待します。もし LIKE %keyword% だけを使っているなら、データ量が増えるにつれてシステムはすぐに限界を迎えるでしょう。

私はかつて、1,000万件以上のレコード（約50GB）を保持するMySQL 8.0のプロダクションデータベースを管理していました。当初、検索クエリのレスポンスに2〜3秒かかり、サーバーのCPU使用率は危険水域の80〜90%に達していました。しかし、以下に挙げる最適化手法を適用した結果、クエリ時間は150ms以下に短縮され、CPU使用率も10%前後で安定するようになりました。

一般的な手法の比較

1. LIKE句と照合順序（Collation）の利用（手動アプローチ）

これは最も手軽な解決策です。通常、Collationに utf8mb4_unicode_ci や utf8mb4_vietnamese_ci を選択します。しかし、MySQLは非常に複雑な設定をしない限り、’d’ と ‘đ’ を同一視することはできません。

  • 弱点： フルテーブルスキャン（Full Table Scan）を発生させます。データベースが1行ずつスキャンするため、リソースを極端に消費します。
  • 現実的な用途： 1,000行未満のマスターテーブルなどでの使用に限定すべきです。

2. デフォルトのMySQL Full-Text Search (FTS)

FTSは専用のインデックスメカニデックスにより、LIKE よりも何倍も高速です。それでも、デフォルト設定のままでは、ベトナム語特有のアクセント記号を持つ単語の扱いに苦労することがあります。

3. Shadow Column（シャドウカラム）技法 — パフォーマンスの王道

これは私が大規模プロジェクトで常に優先して採用する手法です。考え方は非常にシンプルです。アクセント記号を取り除いた内容を保存するための補助カラム（例：search_vector）を追加します。ユーザーが検索する際は、この「クリーンな」カラムに対してスキャンを行うだけです。

Shadow ColumnとFull-Text Searchの構築

ステップ1：テーブル構造の再定義

アクセント記号なしのテキストを保存するための専用カラムを追加します。このカラムには必ずFull-Textインデックスを貼ってください。

ALTER TABLE products ADD COLUMN content_search_no_sign TEXT;
CREATE FULLTEXT INDEX idx_fts_vietnamese ON products(content_search_no_sign);

ステップ2：アプリケーション層でのデータ正規化

MySQL内部でアクセントを除去する関数を作成する人もいますが、これはデータベースサーバーに余計な計算ロジックを強いることになります。この処理はバックエンド側（NodeJS、Python、PHPなど）で行いましょう。

例えばNodeJSなら、unidecode ライブラリを使用して「Tiếng Việt có dấu」を「Tieng Viet co dau」に変換してからDBに保存します。これにより、データベースの負荷を最小限に抑えられます。

ステップ3：トリガーによる自動化

メインのカラムと補助カラムのデータを常に同期させるには、トリガー（Trigger）が最適です。title が更新されるたびに、content_search_no_sign も自動的に更新されるようにします。

CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    -- 定義済みのアクセント除去関数を呼び出す
    SET NEW.content_search_no_sign = remove_accents(NEW.title);
END;

MySQLがベトナム語をより正確に認識するための設定

ベトナム語には “áo”（服）、”xe”（車）、”tủ”（棚）のように、非常に短い単語が多いという特徴があります。デフォルトでは、MySQLは3文字未満の単語を無視するため、検索結果に重大な漏れが生じます。

最小単語長の調整

<a href="https://itfromzero.com/ja/database/mysql-ja-database/mysql-8%e3%81%aemy-cnf%e6%9c%80%e9%81%a9%e5%8c%96%ef%bc%9a%e3%83%87%e3%83%95%e3%82%a9%e3%83%ab%e3%83%88%e8%a8%ad%e5%ae%9a%e3%81%a7%e3%82%b5%e3%83%bc%e3%83%90%e3%83%bc%e3%82%92%e3%80%8c%e7%aa%92.html">my.cnf</a> (Linux) または my.ini (Windows) の設定ファイルを編集する必要があります。

[mysqld]
innodb_ft_min_token_size = 2
ft_min_word_len = 2

修正後、サービスを再起動し、OPTIMIZE TABLE products; コマンドを実行してください。このステップは、新しいルールに基づいてMySQLがインデックスを再構築するために極めて重要です。

Boolean Modeの活用

通常の検索だけでなく、IN BOOLEAN MODE を活用して結果をより正確に絞り込みましょう。

-- "dien" と "thoai" の両方が必須の商品を検索
SELECT * FROM products 
WHERE MATCH(content_search_no_sign) AGAINST('+dien +thoai' IN BOOLEAN MODE);

実戦経験から得たTips

システムが数百万ユーザー規模に達すると、アクセントなし版のみを保存する方法では精度が落ちることがあります。私がよく使うテクニックは、アクセントありとアクセントなしの両方をスペース区切りで同じカラムに保存することです。例："điện thoại dien thoai"。

こうすることで、ユーザーがどのような形式で入力しても、MySQLはヒットさせることができます。特に、アクセントまで完全に一致する結果はスコア（relevance score）が高くなり、上位に表示されます。これが大規模システムにおけるユーザー体験最適化の定石です。

おわりに

プロジェクトが小規模であれば LIKE でも十分でしょう。しかし、長期的かつプロフェッショナルな運用を目指すなら、「Shadow Column」「バックエンドでの前処理」「min_token_sizeの設定」の3本柱を忘れないでください。皆さんのクエリが稲妻のように速くなることを願っています！

Share:
Tags: