0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@pmc-ko(K Ohtsuka)
in

プリザンターの検索機能を詳しくみてみる[第3回：RDBMS別の全文検索SQL編]

0
Posted at

はじめに

前回までで、検索の土台となる Items.FullText の作られ方を確認しました。

今回は検索する側です。同じキーワードを入れても、
SQL Server・PostgreSQL・MySQL で生成される SQL がまったく違います。
しかも「違う SQL が出る」だけでなく、ヒットの仕方そのものが変わります。

「開発環境の PostgreSQL では思ったように検索できたのに、
本番の SQL Server では挙動が違う」という話が起きるのはこのためです。

検索語がパラメータに変換されるまでの流れから追っていきましょう。

バージョン 1.5.7.0 を対象にしています

検索語がパラメータになるまで

入力された文字列は、4段階の加工を経て SQL のパラメータになります。

第1段階：SearchIndexes()

まず全角スペースを半角に変換して分割します。

Implem.Pleasanter/Libraries/Extensions/SearchIndexExtensions.cs
public static List<string> SearchIndexes(this string self)
{
    return self?.Replace("　", " ")
        .Split(' ')
        .Select(o => o.Trim())
        .Where(o => o != string.Empty)
        .Distinct()
        .ToList()
            ?? new List<string>();
}

ここでも Distinct() が入っているので、同じ語を2回入れても1回として扱われます。

第2段階：Words()

分割した語を、SQL パラメータの辞書に変換します。

Implem.Pleasanter/Libraries/Search/Indexes.cs
private static Dictionary<string, string> Words(string searchText)
{
    return searchText?
        .Replace("　", " ")
        .Replace("\"", " ")
        .Replace("'", "’")
        .Trim()
        .Split(' ')
        .Where(o => o != string.Empty)
        .Distinct()
        .Select(o => FullTextClause(o))
        .ToDictionary(o => Strings.NewGuid(), o => o);
}

ここで2つの文字が特別扱いされています。

文字 処理 理由
"（半角ダブルクォート） スペースに置換 後段でフレーズを囲むのに使うため
'（半角シングルクォート） ’（全角）に置換 SQL リテラルの区切りと衝突するため

つまり検索語にダブルクォートを入れても引用符としては機能しません。単に区切りとして消えます。

パラメータ名には Strings.NewGuid() が使われています。
検索語ごとにランダムなパラメータ名が振られるので、
SQL デバッガーで生成された SQL を見ると @a1b2c3... のような名前が並びます。

第3段階：FullTextClause()

ここが日本語対応の核心部分です。

Implem.Pleasanter/Libraries/Search/Indexes.cs
private static string FullTextClause(string word)
{
    var data = new List<string> { word };
    var katakana = CSharp.Japanese.Kanaxs.KanaEx.ToKatakana(word);
    var hiragana = CSharp.Japanese.Kanaxs.KanaEx.ToHiragana(word);
    if (word != katakana) data.Add(katakana);
    if (word != hiragana) data.Add(hiragana);
    return "(" + data
        .SelectMany(part => new List<string>
        {
            part,
            ForwardMatchSearch(part: part)
        })
        .Where(o => o != null)
        .Distinct()
        .Select(o => "\"" + o + "\"")
        .Join(" or ") + ")";
}

やっていることは2つです。

  1. KanaEx でひらがなとカタカナの相互変換を行い、表記ゆれを吸収する
  2. それぞれについて前方一致用の * 付きも作り、すべてを or で連結する

たとえば はんばい で検索すると、こういう文字列が組み立てられます。

("はんばい" or "はんばい*" or "ハンバイ" or "ハンバイ*")

漢字が含まれる場合はかな変換で変化しないので、素直な形になります。

("販売" or "販売*")

SearchIndexes() と Words() に加えてここでも Distinct() が効いているので、
変換結果が元の語と同じなら重複しません。

第4段階：ForwardMatchSearch() の地味な仕様

前方一致用の * を付けるかどうかを決めているのが、このメソッドです。

Implem.Pleasanter/Libraries/Search/Indexes.cs
private static string ForwardMatchSearch(string part)
{
    var separators = "!#$%&()*+,-./:<=>?@[\\]^_`{|}~";
    foreach (var separator in separators)
    {
        if (part.Split(separator).Any(o => o.RegexExists("^[0-9]$")))
        {
            return null;
        }
    }
    return part + "*";
}

記号で分割した断片のなかに1桁の数字だけの要素があると、null を返して前方一致をあきらめます。

これが効いてくるのは型番やバージョン表記です。

検索語 記号で分割 1桁数字を含むか 前方一致
販売 販売 含まない 付く
A-1 A / 1 含む 付かない
v1.0 v1 / 0 含む 付かない
AB-12 AB / 12 含まない 付く

A-1 で検索すると前方一致が付かないので、A-1234 のようなレコードは引っかかりません。
一方で AB-12 なら前方一致が付くため AB-1234 も拾えます。

「品番の一部で検索したのに出てこない」という現象の理由がここにあります。
* を付けると検索コストが跳ね上がるケースを避けるための保険だと思われますが、
挙動としては覚えておく価値があります。

RDBMS ごとの実装は ISqlCommandText で分岐する

ここから先は RDBMS 別です。切り替えは以下のインタフェースで行われています。

Rds/Implem.IRds/ISqlCommandText.cs
string CreateFullTextWhereItem(string itemsTableName, string paramName, bool negative);
string CreateFullTextWhereBinary(string itemsTableName, string paramName, bool negative);
Dictionary<string,string> CreateSearchTextWords(Dictionary<string,string> words, string searchText);

この3メソッドの実装差が、そのまま検索の挙動差になります。

SQL Server

生成される条件式

Rds/Implem.SqlServer/SqlServerCommandText.cs
public string CreateFullTextWhereItem(
    string itemsTableName,
    string paramName,
    bool negative)
{
    return (negative
        ? $"(not contains(\"{itemsTableName}\".\"FullText\", @{paramName}#CommandCount#))"
        : $"(contains(\"{itemsTableName}\".\"FullText\", @{paramName}#CommandCount#))");
}

contains() 述語をそのまま使います。#CommandCount# は、
1回の実行で複数の SQL を投げるときにパラメータ名が衝突しないよう連番を差し込むプレースホルダです。

検索語の前処理はしない

Rds/Implem.SqlServer/SqlServerCommandText.cs
public Dictionary<string,string> CreateSearchTextWords(
    Dictionary<string,string> words,
    string searchText)
{
    return words;
}

受け取った辞書をそのまま返します。語ごとに1パラメータになるので、
呼び出し側で語ごとに条件が追加され、結果としてスペース区切りが AND として効きます。

インデックスの DDL

App_Data/Definitions/Sqls/SQLServer/CreateFullText.sql
CREATE FULLTEXT CATALOG ftx
WITH ACCENT_SENSITIVITY = OFF;

CREATE FULLTEXT INDEX ON [Items]
    ([FullText] Language 'Japanese')
    KEY INDEX #PKItems#
    ON ftx;

ポイントは3つです。

  • カタログ名は ftx 固定で、ACCENT_SENSITIVITY = OFF
  • Language 'Japanese' を指定しているので、日本語のワードブレーカーが必要
  • #PKItems# は実行時に SelectPkName.sql で主キー名を引いて差し替えられる

日本語のワードブレーカーが単語を切ってくれるので、
Items.FullText に日本語の文章がそのまま入っていても単語単位で検索できます。

PostgreSQL

生成される条件式

Rds/Implem.PostgreSql/PostgreSqlCommandText.cs
public string CreateFullTextWhereItem(
    string itemsTableName,
    string paramName,
    bool negative)
{
    return (negative
        ? $"(not (coalesce(\"{itemsTableName}\".\"FullText\",'') %> @{paramName}#CommandCount#))"
        : $"(\"{itemsTableName}\".\"FullText\" %> @{paramName}#CommandCount#)");
}

%> は pg_trgm 拡張が提供する単語類似度演算子です。
SQL Server の contains() のような全文検索述語ではなく、トライグラムによる類似判定です。

否定側だけ coalesce() で NULL を空文字に落としているところも実装の細かい配慮です。

インデックスと拡張

App_Data/Definitions/Sqls/PostgreSQL/CreateFullText.sql
create index if not exists "ftx" on "Items" using gin ("FullText" gin_trgm_ops);

pg_trgm 拡張そのものはスキーマ作成時に入ります。

App_Data/Definitions/Sqls/PostgreSQL/CreateSchema.sql
create extension if not exists pg_trgm;

ここだけ実装が違う：CreateSearchTextWords()

PostgreSQL 版だけ、このメソッドの中身がまったく別物です。

Rds/Implem.PostgreSql/PostgreSqlCommandText.cs
public Dictionary<string,string> CreateSearchTextWords(
    Dictionary<string,string> words,
    string searchText)
{
    if (searchText.IsNullOrWhiteSpace()) return new();
    return new Dictionary<string, string> { [Strings.NewGuid()] = searchText };
}

受け取った words を捨てて、検索文字列を丸ごと1パラメータにしています。

これが何を意味するか整理しましょう。

SQL Server / MySQL PostgreSQL
パラメータ数 語の数だけ 常に1つ
語同士の関係 AND なし（1つの文字列として扱う）
FullTextClause() の成果物 使われる 使われない
判定方法 全文検索述語 文字列全体の類似度

つまり PostgreSQL では、
第3段階で丁寧に作ったかな変換や前方一致の展開が結果的に捨てられます。
%> に渡されるのは、ユーザが入力した生の文字列です。

さらに %> は pg_trgm.word_similarity_threshold（既定 0.6）を閾値として判定します。
検索文字列が長くなるほど類似度は下がりやすくなるため、
語数を増やすとヒットしなくなるという挙動になります。

PostgreSQL では「単語を足して絞り込む」という使い方が期待どおりに動きません。
SQL Server が AND で絞り込むのに対し、PostgreSQL は文字列全体の類似度で判定するためです。

MySQL

生成される条件式

Rds/Implem.MySql/MySqlCommandText.cs
public string CreateFullTextWhereItem(
    string itemsTableName,
    string paramName,
    bool negative)
{
    return (negative
        ? $@"not match(""{itemsTableName}"".""FullText"") against (@{paramName}#CommandCount# in boolean mode)"
        : $@"match(""{itemsTableName}"".""FullText"") against (@{paramName}#CommandCount# in boolean mode)");
}

MATCH ... AGAINST を BOOLEAN MODE で使います。
CreateSearchTextWords() は SQL Server と同じく素通しなので、語ごとに1パラメータです。

インデックスの DDL

App_Data/Definitions/Sqls/MySQL/CreateFullText.sql
create fulltext index "ftx" on "Items"("FullText") with parser "ngram";

ngram パーサを使っているので日本語も分割されますが、
ngram_token_size（既定 2）より短い語は索引に載りません。
1文字での検索は効かないことになります。

BOOLEAN MODE では * が前方一致、" がフレーズ指定として解釈されます。
FullTextClause() が生成する ("販売" or "販売*") という文字列は、
BOOLEAN MODE の構文としても意味を持ってしまうため、
実際の挙動は環境で検証してから判断するのが安全です。

3つの RDBMS を並べて比較する

項目 SQL Server PostgreSQL MySQL
条件式 contains(FullText, @p) FullText %> @p match(FullText) against (@p in boolean mode)
索引の種類 Full-Text Catalog GIN + pg_trgm FULLTEXT + ngram
日本語の分割 ワードブレーカー トライグラム N-gram
検索語の前処理 素通し 1パラメータに結合 素通し
スペース区切りの意味 AND 意味を持たない AND
かな変換の反映 される されない される
短い語 分割に依存 3文字未満は不利 2文字未満は不可
閾値の概念 なし word_similarity_threshold なし

「開発は PostgreSQL、本番は SQL Server」という構成だと、
検索の挙動テストが本番環境の代わりにならないことが分かります。

インデックスが作られる条件も RDBMS ごとに違う

もうひとつ大事な差があります。インデックスをいつ作るかです。
CodeDefiner の TablesConfigurator を見てみましょう。

SQL Server

Implem.CodeDefiner/Functions/Rds/TablesConfigurator.cs
Def.SqlIoBySa(factory: factory, initialCatalog: Environments.ServiceName)
    .ExecuteNonQuery(
        factory: factory,
        dbTransaction: null,
        dbConnection: null,
        commandText: Def.Sql.CreateFullText
            .Replace("#PKItems#", pkItems)
            .Replace("#PKBinaries#", pkBinaries));

毎回実行されます。索引の有無は SQL 側の IF NOT EXISTS で判定しています。
注目したいのは Def.SqlIoBySa() の部分で、sa 権限で実行されるという点です。

PostgreSQL

Implem.CodeDefiner/Functions/Rds/TablesConfigurator.cs
private static void ConfigureFullTextIndexPostgreSql(ISqlObjectFactory factory)
{
    try
    {
        if (!factory.SqlDefinitionSetting.IsCreatingDb)
        {
            return;
        }
        Def.SqlIoByAdmin(factory: factory)
            .ExecuteNonQuery(
                factory: factory,
                dbTransaction: null,
                dbConnection: null,
                commandText: Def.Sql.CreateFullText);
    }

IsCreatingDb が真のとき、つまりデータベースを新規作成するときだけ実行されます。

PostgreSQL では、既存のデータベースに対して後から ftx インデックスが作られません。
索引がない状態でも %> は動くため、遅いだけで気づきにくいという厄介な状態になります。

MySQL

Implem.CodeDefiner/Functions/Rds/TablesConfigurator.cs
bool Exists()
{
    return Def.SqlIoByAdmin(factory: factory)
        .ExecuteTable(
            factory: factory,
            commandText: Def.Sql.ExistsFullText
                .Replace("#InitialCatalog#", Environments.ServiceName))
        .Rows.Count == 1;
}
if (!Exists())
{
    // CreateFullText を実行
}

存在チェックの SQL は Items だけを見ています。

App_Data/Definitions/Sqls/MySQL/ExistsFullText.sql
select "index_name"
from "information_schema"."statistics"
where "table_schema" = '#InitialCatalog#'
and "table_name" = 'Items'
and "index_name" = 'ftx';

索引の作成に失敗しても CodeDefiner は正常終了する

ここは知っておくと事故を防げます。SQL Server 版の例外処理を見てください。

Implem.CodeDefiner/Functions/Rds/TablesConfigurator.cs
catch (Microsoft.Data.SqlClient.SqlException e)
{
    Consoles.Write($"[{e.Number}] [{nameof(ConfigureFullTextIndexSqlServer)}]: {e}", Consoles.Types.Error);
}

例外はコンソールに1行書かれるだけで、処理は続行されます。
以下のいずれかに当てはまると、黙って索引なしの状態になります。

  • sa の接続文字列が未設定、または権限が足りない
  • SQL Server に Full-Text Search 機能が入っていない
  • Language 'Japanese' の言語リソースが使えない

CodeDefiner は最後まで走って正常終了するので、気づけるのはコンソールの出力だけです。
この状態でサイトの検索方式を「全文検索」にすると、実行時に contains() が失敗します。

診断してみましょう

索引が実在するかを確認する SQL です。

-- SQL Server
select
    t.name as table_name,
    c.name as column_name
from sys.fulltext_index_columns fic
inner join sys.tables t on fic.object_id = t.object_id
inner join sys.columns c
    on fic.object_id = c.object_id
    and fic.column_id = c.column_id;

-- PostgreSQL
select indexname, indexdef
from pg_indexes
where tablename = 'Items' and indexname = 'ftx';

-- MySQL
select index_name, index_type
from information_schema.statistics
where table_name = 'Items' and index_name = 'ftx';

PostgreSQL で結果が返らない場合は、
IsCreatingDb の条件に引っかかって索引が作られていない可能性があります。

まとめ

今回の要点です。

  • 検索語は SearchIndexes() → Words() → FullTextClause() → CreateSearchTextWords() の4段階で加工されます
  • FullTextClause() はひらがなとカタカナの相互変換で表記ゆれを吸収します
  • ForwardMatchSearch() は記号で区切った断片に1桁数字があると前方一致をあきらめます（A-1 など）
  • SQL Server は contains()、PostgreSQL は pg_trgm の %>、MySQL は match ... against を使います
  • PostgreSQL だけ CreateSearchTextWords() の実装が違い、検索文字列を1パラメータに結合します。
    そのため語を足して絞り込む使い方ができず、かな変換も反映されません
  • 索引の作成条件も RDBMS ごとに異なり、PostgreSQL は新規作成時のみです
  • 索引の作成に失敗しても CodeDefiner は正常終了するため、コンソール出力を確認する必要があります

次回は、一覧画面の検索ボックスを扱います。
既定では全文検索ではないという話から始めます。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?