はじめに
前回は RDBMS ごとに生成される SQL の違いを見ました。
今回は一覧画面の検索ボックスを扱います。
最初に驚きがひとつあります。
一覧の検索ボックスは、既定では全文検索を使っていません。
第3回でわざわざ RDBMS ごとの全文検索構文を読んだのに、
既定設定のままではそれが使われないのです。
なぜそうなっているのか、そして何を設定すれば全文検索に切り替わるのかを見ていきましょう。
バージョン 1.5.7.0 を対象にしています
サイト単位の検索方式は4種類
一覧の検索ボックスの挙動は、サイトの設定で決まります。定義はこの列挙体です。
public enum SearchTypes : int
{
FullText = 10,
PartialMatch = 15,
MatchInFrontOfTitle = 20,
BroadMatchOfTitle = 30,
}
そして既定値がここで決まります。
SearchType = SearchType ?? SearchTypes.PartialMatch;
既定は FullText ではなく PartialMatch です。
それぞれどんな SQL になるのかを表にまとめます。
| 値 | サイトの設定での表示 | 対象列 | 生成される条件 | 全文索引 |
|---|---|---|---|---|
FullText(10) |
全文検索 | Items.FullText |
RDBMS の全文検索構文 | 効く |
PartialMatch(15) |
部分一致(既定) | Items.FullText |
like '%...%' |
効かない |
MatchInFrontOfTitle(20) |
タイトル前方一致 | Items.Title |
like '...%' |
効く |
BroadMatchOfTitle(30) |
タイトル部分一致 | Items.Title |
like '%...%' |
効かない |
分岐しているのは SearchTextWhere() です。
switch (ss?.SearchType)
{
case SiteSettings.SearchTypes.FullText:
// 全文検索の条件を組み立てる
case SiteSettings.SearchTypes.MatchInFrontOfTitle:
return where.SqlWhereLike(
tableName: "Items",
name: "SearchText",
searchText: searchText,
clauseCollection: Rds.Items_Title_WhereLike(
factory: context,
forward: true).ToSingleList());
case SiteSettings.SearchTypes.BroadMatchOfTitle:
// タイトル部分一致
case SiteSettings.SearchTypes.PartialMatch:
default:
return where.SqlWhereLike(
tableName: "Items",
name: "SearchText",
searchText: searchText,
clauseCollection: Rds.Items_FullText_WhereLike(
factory: context,
forward: false).ToSingleList());
}
default が PartialMatch と同じ扱いになっているので、
設定していないサイトはすべて部分一致になります。
既定の PartialMatch が意味すること
PartialMatch は Items.FullText に対する like '%キーワード%' です。
前方にワイルドカードが付くため、全文索引はまったく使われません。
第2回で見たとおり、Items.FullText にはレコードのほぼ全項目とコメントと送信メールが
連結されています。かなり長い文字列です。
その長い文字列に対して、全レコード分の前方ワイルドカード LIKE を走らせているわけです。
既定設定のままでは、データが増えるほど一覧の検索が線形に遅くなります。
「最初は速かったのに最近重い」の主因はここにある可能性が高いです。
一方で PartialMatch には利点もあります。
RDBMS の全文検索機能に依存しないので、
索引が作られていない環境でも確実に動き、分割方式による取りこぼしもありません。
既定値として無難な選択、という位置づけだと理解できます。
検索文字列の分解ルール
一覧の検索ボックスに入力した文字列は、View.Search に入ります。
それを条件に変換しているのが SetSearchWhere() です。
private void SetSearchWhere(
Context context,
SiteSettings ss,
SqlWhereCollection where,
bool itemJoin)
{
var negative = UseNegativeFilters(
ss: ss,
name: "ViewFilters_Search") == true;
var collection = new SqlWhereCollection();
Search?
.Replace(" ", " ")
.Replace(" or ", "\n")
.Split('\n')
.Where(o => !o.IsNullOrEmpty())
.ForEach(search =>
collection.Add(and: new SqlWhereCollection().FullTextWhere(
context: context,
ss: ss,
searchText: search,
itemJoin: itemJoin,
negative: negative)));
if (collection.Any())
if (negative)
{
where.Add(and: collection);
}
else
{
where.Add(or: collection);
}
}
ここに、ドキュメントには書かれていない仕様が詰まっています。
or で区切ると OR になる
" or " を改行に置換してから分割し、分割された片同士を OR で結合しています。
そして各片の中のスペース区切りは、第3回で見た SearchIndexes() で分割されて AND になります。
| 入力 | 解釈 |
|---|---|
販売 東京 |
販売 AND 東京
|
販売 or 東京 |
販売 OR 東京
|
販売 東京 or 大阪 |
(販売 AND 東京) OR 大阪
|
置換しているのは " or " という前後にスペースを含む文字列です。したがって、
-
販売or東京は分割されません(orが語の一部として扱われます) -
販売 OR 東京も分割されません(大文字は対象外) -
orを含む英単語(colorなど)は、前後にスペースがないので影響を受けません
" or " の前後スペースが必須という点と、小文字限定という点は覚えておく価値があります。
否定検索では結合が AND に変わる
negative が真のとき、片同士の結合が OR ではなく AND になります。
これは論理的に正しい挙動です。
「A を含まない」かつ「B を含まない」が、否定検索で期待される結果だからです。
OR にしてしまうと「A を含まないか、B を含まない」になり、ほぼ全件が該当してしまいます。
negative フラグは、条件式の生成まで一貫して伝播します。
| RDBMS | 否定時の条件 |
|---|---|
| SQL Server | not contains(...) |
| PostgreSQL | not (coalesce(FullText,'') %> @p) |
| MySQL | not match(...) against(...) |
| LIKE 系 | not like |
itemJoin の有無で SQL の形が変わる
FullTextWhere() には itemJoin という引数があります。
public static SqlWhereCollection FullTextWhere(
this SqlWhereCollection where,
Context context,
SiteSettings ss,
string searchText,
bool itemJoin,
bool negative)
この値によって、対象テーブル名の決め方が変わります。
private static string ItemTableName(SiteSettings ss, bool itemJoin)
{
if (itemJoin)
{
return ss.ReferenceType + "_Items";
}
else
{
switch (ss.TableType)
{
case Sqls.TableTypes.Deleted:
return "Items_deleted";
default:
return "Items";
}
}
}
itemJoin が真なら Issues_Items のような結合済みの別名テーブルに直接条件を書きます。
偽なら相関サブクエリになります。
return $"exists(select * from \"{tableName}\" where \"{tableName}\".\"ReferenceId\"={ss.IdColumnBracket()} and {like})";
itemJoin |
生成される形 | 特徴 |
|---|---|---|
true |
Issues_Items.FullText like '%...%' |
結合済みなので条件が素直 |
false |
exists(select * from "Items" where ...) |
相関サブクエリ |
同じ検索でも呼び出し元によって形が変わるので、
実行計画を確認するときはどちらの経路で生成された SQL なのかを先に把握しておくと混乱しません。
生成された SQL を確認するには、SQL デバッガー機能が便利です。
ゴミ箱では全文検索が使われない
SearchTextWhere() と FullTextWhere() は、どちらも冒頭でテーブル種別を見ています。
if (ss != null && ss.TableType != Sqls.TableTypes.Normal)
{
return where.ItemWhereLike(
context: context,
ss: ss,
columnName: "FullText",
searchText: searchText,
name: name,
forward: false,
itemJoin: itemJoin,
negative: negative);
}
TableType が Normal 以外、つまりゴミ箱や履歴を見ているときは、
SearchType の設定にかかわらず強制的に LIKE になります。
Items_deleted テーブルには全文索引が張られていないので、これは妥当な実装です。
CreateFullText.sql を見ても、索引の対象は Items と Binaries だけです。
到達しない _deleted 分岐
ここで面白いコードを紹介します。
Indexes.cs には、テーブル種別からサフィックスを組み立てる分岐があります。
var suffix = string.Empty;
switch (tableType)
{
case Sqls.TableTypes.Deleted:
suffix = "_deleted";
break;
}
このメソッドは全文検索述語を組み立てるものです。
しかし呼び出し元をたどると、ss.TableType != Normal のときは
前段の分岐で LIKE に落ちているため、
suffix が _deleted になる経路には到達しません。
もし到達したら contains("Items_deleted"."FullText", @p) が生成され、
索引がないため実行時エラーになります。
現状は前段の分岐が守ってくれている、という構造です。
「常に検索条件を要求する」の判定式
サイトの設定に「常に検索条件を要求する」というオプションがあります。
条件を指定しないと全件表示されてしまうのを防ぐためのものです。
public bool RequestSearchCondition(Context context, SiteSettings ss)
{
var where = new SqlWhereCollection();
SetColumnsWhere(
context: context,
ss: ss,
where: where);
return (ss.AlwaysRequestSearchCondition == true)
&& (Incomplete != true
&& Own != true
&& NearCompletionTime != true
&& Delay != true
&& Overdue != true
&& where.Any() != true
&& Search.IsNullOrEmpty());
}
判定に使われる要素は7つあります。
| 要素 | 内容 |
|---|---|
Incomplete |
未完了フィルタ |
Own |
自分のレコードフィルタ |
NearCompletionTime |
完了間近フィルタ |
Delay |
遅延フィルタ |
Overdue |
期限超過フィルタ |
where.Any() |
列フィルタが1つでもあるか |
Search |
検索ボックスの入力 |
これらがすべて空のときだけ「検索条件を指定してください」と表示されます。
逆に言えば、絞り込みボタンをひとつ押しただけでも条件ありと判定されるので、
「全件に近い表示」を完全に防げるわけではありません。
項目ごとの絞り込み:Column.SearchTypes
一覧のフィルタ列での絞り込みは、別の列挙体で制御されています。
public enum SearchTypes : int
{
PartialMatch = 1,
ExactMatch = 2,
ForwardMatch = 3,
PartialMatchMultiple = 11,
ExactMatchMultiple = 12,
ForwardMatchMultiple = 13
}
Multiple が付いているものは、複数値を持つ項目(複数選択の分類項目など)向けです。
SiteSettings.SearchTypes と Column.SearchTypes は名前が同じで中身が別物です。
SiteSettings 側は検索ボックスの方式(4種類)、Column 側はフィルタ列の一致方式(6種類)です。
ソースを読むときに混同しやすいので注意してください。
さらに、ビューごとに列単位で上書きできる仕組みもあります。
public Dictionary<string, Column.SearchTypes> ColumnFilterSearchTypes;
条件を組み立てるときは、ビューの設定を優先して列の設定にフォールバックします。
var searchType = ColumnFilterSearchTypes?.ContainsKey(column.ColumnName) == true
? ColumnFilterSearchTypes.Get(column.ColumnName)
: column.SearchType;
同じ項目でも、ビューによって完全一致と部分一致を切り替えられるということです。
一覧の検索を全文検索に切り替えるときの注意
ここまでを踏まえると、一覧の検索を速くする方法は明確です。
サイトの設定で検索方式を「全文検索」に変えることです。
ただし第3回の内容から、切り替えると挙動も変わります。
| RDBMS | 切り替え後の変化 |
|---|---|
| SQL Server | 語ごとの AND。索引が効いて高速になる |
| PostgreSQL | 検索文字列全体の類似度判定。語数が増えるとヒットしなくなる |
| MySQL |
ngram_token_size(既定 2)より短い語が拾えなくなる |
「速くなるが、ヒットの仕方が変わる」という理解が正確です。
本番と同じ RDBMS の検証環境で、実際の検索パターンを試してから切り替えるのが安全です。
まとめ
今回の要点です。
- 一覧の検索方式は4種類で、既定は
PartialMatch(部分一致) です -
PartialMatchはItems.FullTextへの前方ワイルドカード LIKE なので、全文索引が使われません - 検索文字列は
" or "(前後スペースあり・小文字のみ)で OR、スペースで AND に分解されます - 否定検索のときは、片同士の結合が OR ではなく AND に変わります
-
itemJoinの有無で、結合済みテーブルへの条件か相関サブクエリかが変わります -
ゴミ箱や履歴では強制的に LIKE になります(
Items_deletedに索引がないため) -
SiteSettings.SearchTypesとColumn.SearchTypesは同名だが別物です - 全文検索に切り替えると速くなりますが、RDBMS ごとにヒットの仕方が変わります
次回は、ヘッダの検索ボックスから始まる横断検索の実装を読んでいきます。