はじめに
プリザンターのレコード数が増え、一覧の絞り込みや並べ替えに時間がかかるようになったとき、確認したいものの一つがデータベースのインデックスです。検索する列と並べ替える列に合った索引があると、DB が読み取る行や並べ替えの負担を減らせる場合があります。
ただ、サイトの分類項目や保存ビューが増えるたびに、追加索引を手作業で管理するのは大変です。サイト構成を変えたら不要な索引を整理し、CodeDefiner によるテーブル再構成で追加索引がなくなったら作り直す必要もあります。
そこで、サイト設定から追加索引を計画・作成・更新する VehicleVision.PleasanterTools.IndexCreator を作りました。SQL Server・PostgreSQL・MySQL に対応した .NET 10 のコンソールアプリで、AGPL v3 で公開しています。
この記事は IndexCreator v0.1.0 を対象にしています。プリザンター本体の設定は 1.5.8.1 のソースで確認しています。掲載する架空のサイト JSON は配布版で計画を実行して確認しています。実運用データでの性能向上を測定した記事ではありません。
何を自動化するのか
IndexCreator はプリザンターの DB を直接参照し、サイトごとのレコード数と SiteSettings、既存の索引を比較します。本体のソースコードやテーブル定義を変更せず、追加索引を管理します。
標準索引や他のツールが作った索引は削除対象にしません。管理する名前は次の形式です。
IX_vvplic_{ReferenceType}_{SiteId}_{用途}_{定義ハッシュ16桁}
索引の列・並び順などから定義ハッシュを作ります。同じ物理テーブルに同じ候補があれば複数サイトで共有し、再実行しても同じ索引を増やしません。
保存ビューから索引の候補を作る
例えば、サイトで「状態が受付の案件を、金額の降順で見る」という保存ビューを使っているとします。分類の ClassA を完全一致で絞り込み、Nullable を有効にした数値の NumA で並べ替える設定なら、次の列順が候補になります。
SiteId → ClassA → NumA DESC → UpdatedTime DESC → ResultId DESC
先頭の SiteId は対象サイト、次の ClassA は完全一致の条件です。続いて並べ替えの列を置き、同値の場合の順序を決める列を追加します。
ただし、画面で選択肢を設定していても、フィルタの検索方法が「部分一致」のままなら、この候補にはなりません。プリザンターが部分一致で検索する条件を、選択肢だからという理由で完全一致として扱うことはしていません。
同じく、数値の Nullable が無効で、本体が ISNULL・coalesce・ifnull を使って並べ替える条件も通常の索引では対応しません。診断を確認し、業務上必要な検索の意味を保ったまま検討してください。
まず DB に接続せず試してみる
.NET Runtime 10 を用意し、Release の Assets から OS に合う ZIP と .sha256 を取得します。Windows x64 の場合は IndexCreator-win-x64.zip です。ハッシュを確認して展開し、IndexCreator.dll があるフォルダーで実行します。
次の JSON は動きを試すための架空の例です。プリザンターから取得する実データとは別に、計画入力用の形式で用意しています。
[
{
"SiteId": 100,
"Title": "案件",
"ReferenceType": "Results",
"RecordCount": 20000,
"SiteSettings": {
"GridColumns": ["ResultId", "ClassA", "NumA", "UpdatedTime"],
"Columns": [
{
"ColumnName": "ClassA",
"LabelText": "状態",
"ControlType": "ChoicesText",
"ChoicesText": "10,受付,受\n20,対応中,中\n30,完了,完",
"SearchType": "ExactMatch"
},
{
"ColumnName": "NumA",
"LabelText": "金額",
"Nullable": true
}
],
"Views": [
{
"Id": 1,
"ColumnFilterHash": { "ClassA": "[\"10\"]" },
"ColumnSorterHash": { "NumA": "desc" }
}
]
}
}
]
dotnet IndexCreator.dll plan /sites sites.json /dbms PostgreSQL /output indexes.sql
この例では、上で説明した列順の候補が一つ生成されます。SQL Server・MySQL では既定の並べ替え用の候補も加わります。PostgreSQL は標準の主キーで既定の並べ替えに対応できるため、その追加索引は作りません。
/sites は DB に接続しない計画専用です。既存索引との比較や、実 DB の列の確認は行いません。本体の設定がない状態では DisableIndexChangeDetection の警告が出ますが、計画は作成できます。出力した SQL をそのまま実環境へ流さず、接続した状態でも計画を確認してください。
プリザンターの環境に配置する
配布フォルダーは、本体と CodeDefiner の隣に置きます。
pleasanter/
├── Implem.Pleasanter/
├── Implem.CodeDefiner/
└── IndexCreator/
└── IndexCreator.dll
本体の App_Data/Parameters/Rds.json などから、DBMS と owner 接続を読みます。資格情報をコマンドラインへ渡す必要はありません。
追加索引を適用する前に、Rds.json の DisableIndexChangeDetection を true にしてください。IndexCreator はこの設定を自動変更せず、未設定または false の状態では索引の適用を停止します。既存の接続設定を残して、この項目を変更します。
計画を確認して適用する
dotnet IndexCreator.dll plan /p "C:\web\pleasanter\Implem.Pleasanter"
dotnet IndexCreator.dll _rds /p "C:\web\pleasanter\Implem.Pleasanter"
plan は DB を変更しません。_rds は apply の別名で、計画を表示してから yes の入力を求めます。CodeDefiner を呼び出したり、DB を新規作成したりするコマンドではありません。
| 表示 | 意味 |
|---|---|
Create |
不足する索引を作成 |
Keep |
既存索引で対応できる |
Repair |
無効な索引などを再作成 |
Drop |
明示的な整理対象 |
WARNING |
未対応条件やキー長などの確認事項 |
既定の対象は １万件以上のサイトです。小さな検証用サイトなら、計画と適用の両方に /min-records 0 を付けて試せます。初版には候補を一つずつ選ぶ機能がないため、候補全体を確認してから適用します。
サイト構成を変えたら再実行する
不足する索引を補うだけなら _rds を再実行します。古い管理索引も整理する場合は /prune を付けます。
dotnet IndexCreator.dll plan /prune
dotnet IndexCreator.dll _rds /prune
dotnet IndexCreator.dll plan /prune
必要な索引の新規作成と確認が成功してから、不要な管理索引を削除します。CodeDefiner がテーブルを作り直した後も、再実行して不足分を補えます。自動実行時の確認省略は /y です。
SQL View も作れる
索引とは別に、サイトの一覧列から SQL View（SELECT に名前を付けた参照用の仕組み） を生成できます。
dotnet IndexCreator.dll views
dotnet IndexCreator.dll _views
GridColumns の順序と項目の表示名を反映します。連携処理で物理的な列名を使いたい場合は、計画と適用の両方に /names column を付けます。
固定選択肢を Value・Text・TextMini の３列で取得するマスタ View も生成できます。
dotnet IndexCreator.dll choice-lists
dotnet IndexCreator.dll _choice-lists
作成後の固定選択肢の編集は、View を作り直さなくても反映されます。ユーザーなどの動的選択肢や、表をリンク先にした選択肢は対象外です。
SQL View はプリザンターのユーザー・レコード権限を判定しません。画面の絞り込み・並べ替え・表示書式も再現しません。DB を直接読む連携先には、DB 側で参照権限を設定してください。
適用前に確認したいこと
候補ができることと、業務の処理が速くなることは別です。代表的な一覧の応答時間と実行計画を適用前後で比較し、登録・更新への影響も確認してください。
- 部分一致、複数選択、OR、否定、リンク先条件、式を使う並べ替えなどは対象外です。一時的な画面条件・API 条件・拡張 SQL も、サイト設定にないため解析しません。
- 長い分類値は DB の索引キーサイズに抵触する場合があります。SQL Server・PostgreSQL では索引作成だけでなく、その後の登録・更新も確認します。MySQL の分類列は先頭部分の索引で、既定は100文字です。
- 通常はオンラインで索引を作成します。ただし、短いロックや負荷の発生までなくなるわけではありません。SQL Server の対応しないエディションでは停止するため、保守時間帯で
/offlineを使うか判断します。 - PostgreSQL の
CONCURRENTLYは既存トランザクションの終了を待つため、/lock-timeoutで実行時間全体が５秒に収まるわけではありません。 - サイト編集や CodeDefiner と同時に適用しません。途中で失敗した場合、完了した変更は残るため、再度
planで確認します。
まとめ
- IndexCreator はサイト設定と既存索引から、不足する追加索引を計画・管理します。
-
planで確認し、_rdsで適用します。不要な管理索引の削除は/pruneを指定したときだけです。 - サイト一覧と固定選択肢の SQL View は、索引とは別のコマンドで作成できます。
- 効果は実際のデータとクエリ次第です。適用前後の性能と登録・更新を確認して使います。
まずは架空の sites.json で候補を確認し、その後、検証環境のサイトを使って試してみてください。