Posted inデータベース

PostgreSQLとMySQLにおけるベトナム語検索の高度な設定：Collation、Unaccent、インデックス最適化

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

ベトナム語テキスト検索の課題

ベトナム語は複数の母音形式に適用される6つの異なる声調を使用し、60を超えるユニークなアクセント付き文字を生み出します。この複雑さが具体的な問題を引き起こします。標準的な SELECT * FROM table WHERE name LIKE '%nguyen%' では「Nguyễn」がマッチしません。人間の目には関連して見える文字列も、データベースエンジンにとっては完全に異なるバイト列なのです。

私はMySQLとPostgreSQLで20万件以上のユーザーレコードを処理する本番システムでこれらの設定を6ヶ月間チューニングしてきました。このガイドでは、実際に機能するものと、大規模運用において静かにパフォーマンスを破壊するものを記録します。

クイックスタート：5分で解決

理論を学ぶ時間がない？データベースを選んで以下のコマンドを実行してください。

MySQL：正しいCollationを使用する

MySQLはCollationによってアクセント感度を制御します。カラムを utf8mb4_unicode_ci または新しい utf8mb4_0900_ai_ci に変更するだけで、アプリケーションの変更なしにアクセント不問の検索が機能します。

-- 既存テーブルのCollationを変更する
ALTER TABLE users 
MODIFY COLUMN full_name VARCHAR(255) 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

これ以降、'nguyen' で検索すると 'Nguyễn' が自動的にマッチします。

PostgreSQL：Unaccent拡張機能

PostgreSQLは異なるアプローチを取ります。Collationレベルでの除去ではなく、クエリ実行時に発音区別符号を除去する拡張機能を使用します。

-- 拡張機能を有効化（スーパーユーザー権限が必要）
CREATE EXTENSION IF NOT EXISTS unaccent;

-- テスト
SELECT unaccent('Tiếng Việt có dấu'); 
-- 結果: 'Tieng Viet co dau'

詳細解説：MySQLでのベトナム語検索

MySQLはシンプルです。Collation名のサフィックスがすべてを示しています。_ci はCase Insensitive（大文字小文字不問）、_ai はAccent Insensitive（アクセント不問）を意味します。

なぜ utf8mb4_0900_ai_ci なのか？

MySQL 8.0以降では、utf8mb4_0900_ai_ci が最善の選択肢です。Unicode照合アルゴリズム（UCA）9.0.0を実装しており、エンジンは ‘a’、’á’、’ạ’ を並べ替えと検索の両方において同じ基本文字として正しく扱います。

ただし注意点があります。どのCollationを使っても LIKE '%keyword%' は全表スキャンを引き起こします。50万行のテーブルでは、クエリごとに数秒かかることを意味します。代わりに全文インデックスを使用してください。

-- 全文インデックスを追加
ALTER TABLE articles ADD FULLTEXT(title, content);

-- ブールモードで検索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('nguyen' IN BOOLEAN MODE);

詳細解説：PostgreSQLでのベトナム語検索

PostgreSQLはより多くの制御を提供します。トレードオフもより大きいです。unaccent 関数は強力ですが、誤って使用するとパフォーマンス上の大惨事になります。

パフォーマンスの罠

数千行を超えるテーブルではこれを絶対に実行しないでください：

-- 大規模テーブルでこれを実行してはいけない
SELECT * FROM users 
WHERE unaccent(full_name) ILIKE unaccent('%Nguyễn%');

PostgreSQLは比較前にすべての行で unaccent() を評価します。20万行のusersテーブルでは、私のテストで4.2秒かかりました。検索ボックスとしては完全に使い物になりません。解決策は関数インデックスです。

関数インデックスによる最適化

生のカラム値ではなく、関数の結果をインデックス化します。その後、まったく同じ式を使ってクエリを実行します。

-- アクセント除去済みデータにインデックスを作成
CREATE INDEX idx_users_unaccent_name 
ON users (unaccent(lower(full_name)));

-- まったく同じ式を使ってクエリを実行
SELECT * FROM users 
WHERE unaccent(lower(full_name)) LIKE unaccent(lower('Nguyễn%'));

同じクエリが4.2秒から10ミリ秒未満に短縮されました。重要な点が1つ：インデックスは WHERE 式が完全に一致する場合のみ有効になります——わずかな違いがあるだけで完全にバイパスされます。

高度な使い方：全文検索（FTS）

単一カラムの LIKE クエリはほとんどのユースケースをカバーします。複数カラムの検索や、関連性でランク付けする必要がある場合には、全文検索が必要です。

PostgreSQL GINインデックス

GIN（汎用転置インデックス）は複数語クエリと関連性ランキングを効率的に処理します。unaccent と組み合わせることで、ベトナム語の発音区別符号もカバーできます。

-- 検索ベクターカラムを作成（任意だが速度向上のため推奨）
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- アクセント除去済みコンテンツで更新
UPDATE articles SET search_vector = 
  to_tsvector('simple', unaccent(title || ' ' || content));

-- インデックスを作成
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

'simple' 辞書はステミングを完全にスキップします。これは意図的なものです。ベトナム語の文法は英語のようにステミングしないため、汎用ステミングを適用するとクエリを修正するより多くのクエリを壊してしまいます。

本番環境での6ヶ月から得た実践的なヒント

1. ストレージとCPUのトレードオフ

クエリ実行時に unaccent() を計算することは、5万行未満のテーブルでは問題ありません。それを超えるとクエリ時間が増加し始め、シーケンシャルスキャンで私たちのハードウェアでは約300msかかります。私の解決策：前処理済みの値（小文字、アクセントなし）を保存する専用の name_search カラムを追加することです。確かに追加のディスクスペースを消費しますが、クエリは実行時に関数呼び出しがない単純なインデックス参照になります。

2. ‘đ’ 文字への対処

これは本番環境で私たちを驚かせました。’đ’ と ‘Đ’ は分解不可能な文字です——結合発音区別符号を持たず、独自のUnicodeコードポイントです。古いunaccent辞書はこれらを完全にスキップします。PostgreSQLでの解決策はカスタム変換ルールです：

-- カスタムunaccentルール（unaccent.rulesファイル）
'đ' 'd'
'Đ' 'D'

本番稼働前にテストしてください。’d’ と ‘đ’ は一見似ているため、見落としやすいです。

3. ベトナム語のTSearch処理

PostgreSQLには英語、フランス語、ドイツ語など約30言語の辞書が付属しています。ベトナム語はその中にありません。simple 辞書と unaccent の組み合わせは、ほとんどのアプリケーションで信頼性高く機能します。vntoolkit のような外部ステミングライブラリも存在しますが、追加の複雑さはほとんど割に合いません。

4. クライアントサイドの前処理

アプリケーションコードはデータベースインデックスが行うことを反映しなければなりません。インデックスが unaccent(lower(column)) 上に構築されている場合、検索クエリはデータベースに送る前に同じ変換を適用する必要があります。そうしなければインデックスが完全にバイパスされます。’đ’ を正しく処理するPythonの例：

import unicodedata

def normalize_search(text):
    # 結合発音区別符号を除去し、đ/Đを個別に処理する
    nfd = unicodedata.normalize('NFD', text)
    stripped = ''.join(c for c in nfd if unicodedata.category(c) != 'Mn')
    return stripped.replace('đ', 'd').replace('Đ', 'D').lower()

比較表：MySQL vs. PostgreSQL

機能 MySQL PostgreSQL
使いやすさ 高い（Collationベース） 中程度（拡張機能ベース）
パフォーマンス 全文インデックスで良好 GIN/関数インデックスで優秀
柔軟性 制限あり 非常に高い

選択は既存のスタックに依存します。すでにMySQLを使用している場合は、8.0にアップグレードして utf8mb4_0900_ai_ci に切り替えてください——最小限の影響で即座に結果が得られます。新規スタートや検索ヘビーなアプリケーションを構築する場合は、ベトナム語テキストに対してPostgreSQLの関数インデックスとGIN検索は急な設定の手間に見合う価値があります。

Share:
Tags: