DB が遅くなる理由とインデックス設計
プリザンターは全サイトのレコードを Results・Issues の 1 つのテーブルに入れ、拡張項目（ClassA など）には索引を張りません。そのため、1 サイトのレコードが数十万件になると、一覧を開くたびにサイト内の全レコードを読んで並べ替えたり、数え直したりするようになります。
- 遅さの主な原因は 並べ替え（SQL Server・MySQL では既定の並べ替えでも全件を並べ替える）、拡張項目での絞り込み、件数の数え直し、リンク（親から子の項目を表示する、リンク先の項目で絞り込む、リンク項目を含むタイトルを親の更新で書き換える）です。
- サイトの作り方（選択肢と完全一致、数値の「NULL許容」、リンク先の値のコピー）で避けられるものと、索引を足さないと避けられないものがあります。
- 索引は CodeDefiner の管理外に作ることになるため、先に
Rds.jsonのDisableIndexChangeDetectionをtrueにし、テーブルの作り直しのたびに作り直します。ページの最後に、SiteSettings を読んで必要な索引を割り出し、運用を止めずに作る SQL を出すスクリプトを載せています。
検証した環境
プリザンター 1.5.8.1 の公式 Docker イメージ（implem/pleasanter:1.5.8.1）に、SQL Server 2025（Developer）・PostgreSQL 15・MySQL 8.4 をつないで確かめました。記録テーブルを 2 つ作り、「顧客」に 2 万件、「案件」に 50 万件を入れています（案件の ClassA が顧客へのリンク項目）。時間は同じマシンで比べた値で、絶対値は環境によって変わります。読み取りページ数（reads / blocks）は環境の差を受けにくいので併記しています。
テーブルの作りと標準の索引
レコードは、テーブルの種類ごとに 1 つのテーブルにまとまっています（記録テーブルは Results、期限付きテーブルは Issues）。どのサイトのレコードかは SiteId 列で区別し、タイトルや全文検索用の文字列は Items テーブルに別に持ちます。テーブルの一覧は データベースのテーブル構成 を参照してください。
CodeDefiner が作る索引は次のとおりです（1.5.8.1。SQL Server の実 DB で確認）。
| テーブル | 索引 | キー列 |
|---|---|---|
Results | 主キー（SQL Server はクラスター化） | SiteId, UpdatedTime DESC, ResultId |
Results | Ix1 | ResultId, SiteId, UpdatedTime DESC |
Results | Ix2（一意） | ResultId |
Results | Ix3 | SiteId, Locked, ResultId |
Results | Ix4 | SiteId |
Issues | 主キー・Ix1〜Ix3 | Results と同じ形（IssueId） |
Issues | Ix4 | SiteId, Status, CompletionTime |
Items | 主キー | ReferenceId |
Items | Ix1〜Ix5 | ReferenceType+ReferenceId、SiteId+ReferenceId、SearchIndexCreatedTime+UpdatedTime+ReferenceId、SiteId+ReferenceType+UpdatedTime、SiteId+ReferenceType |
主キーの UpdatedTime DESC は _BaseItems_UpdatedTime.json の "PkOrderBy": "desc" から来ています（_BaseItems_UpdatedTime.json）。PostgreSQL では主キーを作るときに並び順を付けないので（#PkColumnsWithoutOrderType#、CreatePk.sql）、SiteId, UpdatedTime, ResultId がすべて昇順です。MySQL（InnoDB）の主キーは SQL Server と同じ SiteId, UpdatedTime DESC, ResultId ASC で、テーブルの行はこの順に並びます。
拡張項目（ClassA〜、NumA〜、DateA〜 など）には索引がありません。分類項目は SQL Server が nvarchar(1024)、PostgreSQL が varchar(1024)、MySQL が text です（MySQL の実 DB で確認。長さ 1,024 以上の文字列を text にする処理は MySqlColumnSize.cs）。
一覧を 1 回開くと流れる SQL
「案件」の一覧（リンク項目 ClassA を表示）を開いたときに SQL Server で実行された SQL を、sys.dm_exec_query_stats から取り出しました。一覧の 1 ページ目では、次の 4 つが重い処理です。
図を読み込み中…
①② 1 ページ分の SELECT と件数（実際の SQL、SQL Server）
sql
select "Results"."ResultId","Results"."UpdatedTime","Results"."Title",
"Results_Items"."Title" as "ItemTitle","Results"."ClassA", …,
"ClassA~1"."Title" as "ClassA~1,Title","ClassA~1_Items"."Title" as "ClassA~1,ItemTitle", …
from "Results" as "Results"
left outer join "Items" as "ClassA~1_Items"
on try_cast("Results"."ClassA" as bigint)="ClassA~1_Items"."ReferenceId"
and "ClassA~1_Items"."SiteId"=1
left outer join "Results" as "ClassA~1"
on "ClassA~1_Items"."ReferenceId"="ClassA~1"."ResultId"
inner join "Items" as "Results_Items"
on "Results"."ResultId"="Results_Items"."ReferenceId"
and "Results"."SiteId"="Results_Items"."SiteId"
where "Results"."SiteId"=2
order by "Results"."UpdatedTime" desc,"Results"."ResultId" desc
offset @_Offset1 rows fetch next @_PageSize1 rows only;
select count(*) as "Count" from "Results" as "Results"
left outer join "Items" as "ClassA~1_Items" on …（同じ JOIN）
inner join "Items" as "Results_Items" on …
where "Results"."SiteId"=2;Itemsへの INNER JOIN は、表示する列にかかわらず必ず付きます（ItemUtilities.cs#L46-L76）。件数の SELECT にも同じ JOIN が付きます（GridData.cs#L117-L124）。- リンク項目を一覧に出すと、リンク先のタイトルを取るために
try_castの LEFT JOIN が足されます（SiteSettings.cs#L5657-L5666。仕組みは リンク先の列（チルダ構文）と JOIN の生成）。 - ③ は一覧の上に出る集計の件数です。1 ページ目（
offset == 0）のたびに、同じ条件で数え直します（Aggregations.cs#L18-L44）。 - ④ はリンク項目の選択肢です。リンク先サイトのレコードを
Title順にDropDownSearchPageSize（既定 500）件読みます（SiteSettings.cs#L4928-L4983、General.json#L86）。 SiteIdは SQL にリテラルで埋め込まれ、絞り込みの値はパラメータ（文字列）か、数値・日付ではリテラルで渡ります。
一覧の SQL の組み立て全体は 内部で動く SQL 文 にまとめています。
何が遅いのか
既定の並べ替えが主キーの順序と合わない（SQL Server・MySQL）
並べ替えを設定していなくても、ORDER BY の末尾には必ず UpdatedTime DESC, <ID> DESC が付きます（View.cs#L3365-L3378）。SQL Server の主キー（クラスター化索引）は SiteId, UpdatedTime DESC, ResultId ASC なので、ResultId DESC まで含めた順序では読めません。実行計画を見ると、既定の一覧でもサイトの全レコードを並べ替えてから 20 件を返しています。
| 条件（案件 50 万件、1 ページ 20 件） | 実行計画 | 読み取り | 時間（CPU） |
|---|---|---|---|
| 追加索引なし | Items の全件スキャン → ハッシュ結合 → 50 万件の Sort | 64,209 ページ | 105 ms（955 ms） |
(SiteId, UpdatedTime DESC, ResultId DESC) を追加 | 索引を先頭から 20 件 | 189 ページ | 0.3 ms |
| リンク項目を表示（追加索引なし） | 上に加えて try_cast の JOIN | 163,981 ページ | 783 ms（2,559 ms） |
| リンク項目を表示（同じ索引を追加） | 索引を先頭から 20 件 → 20 件分だけ JOIN | 474 ページ | 0.3 ms |
一覧の画面全体（HTTP の応答時間、中央値）では、リンク項目を表示した既定の一覧が 1,064 ms → 202 ms になりました。
MySQL も主キーが同じ形なので、同じく全件を並べ替えていました（EXPLAIN ANALYZE で 459 ms → 同じ索引を追加して 0.11 ms）。PostgreSQL は主キーを後ろから読むことで UpdatedTime DESC, ResultId DESC の順に読めるため、追加索引なしでも 0.3 ms でした。
拡張項目での絞り込みに索引がない
ビューのフィルタで ClassB = '新規' と絞り込むと、SiteId で範囲を決めたあと、そのサイトの全レコードを 1 件ずつ調べます。
| 絞り込み（案件 50 万件） | 追加索引なし | 索引を追加 |
|---|---|---|
ClassB（選択肢・完全一致、12.5 万件が該当）の件数 | 57,775 ページ、45 ms | 2,072 ページ、11 ms |
ClassA（リンク項目・完全一致、約 25 件が該当）の 1 ページ | 56,373 ページ、38 ms | 102 ページ、0.1 ms |
ClassA の前方一致（LIKE '1000%'） | 57,098 ページ、43 ms | 1,289 ページ、1.0 ms |
ClassA の部分一致（LIKE '%100%'） | 57,255 ページ、60 ms | 6,731 ページ、3.2 ms（索引の全件スキャン） |
絞り込みの SQL は項目の型と「検索方法」で決まります（View.cs）。
| 項目 | 生成される条件 | 索引 |
|---|---|---|
| 分類（選択肢あり・単一選択） | 既定は完全一致 "ClassA" = @p（複数値は or でつなぐ） | 効く |
| 分類（選択肢なし）・タイトル | 既定は部分一致 like '%' + @p + '%' | 効かない |
| 分類で検索方法を前方一致にしたもの | like @p + '%' | SQL Server・MySQL は効く。PostgreSQL は照合順序が C 以外なら varchar_pattern_ops の索引が要る |
| 分類（複数選択） | JSON 文字列に対する like | 効かない |
| 数値・状況・管理者・担当者 | =、in (…)、between | 効く |
| 日付 | between '…' and '…' | 効く |
リンク先の項目（ClassA~1,ClassB） | リンク先テーブルを try_cast で JOIN した先の条件 | 効かない |
検索方法の既定は、選択肢があって複数選択でない分類項目だけが完全一致で、それ以外は部分一致です（Column.cs#L302-L307）。PostgreSQL では、部分一致にも pg_trgm の GIN 索引が使えます（プリザンターは Items.FullText にこの索引を作っています）。検証では ClassA の部分一致が 24 ms → 15 ms になりましたが、該当件数が多い条件では大きくは速くなりません。MySQL の分類項目は text なので、索引は先頭の文字数を決めた前方部分の索引（ClassA(100)）になります。完全一致の絞り込みは 357 ms → 0.15 ms になりました。
並べ替えの列が ISNULL で包まれる
一覧を項目で並べ替えると、NULL を許す列は ISNULL(列, 既定値)（PostgreSQL は coalesce）で包まれます（Column.cs#L1215-L1231、SqlOrderBy.cs#L85-L87）。関数で包んだ列の順序は索引から読めないので、SQL Server ではその列に索引を作っても全件を並べ替えます。実際に発行された ORDER BY は次のとおりです。
| 並べ替える項目 | 発行された ORDER BY | SQL Server で索引が効くか |
|---|---|---|
日付（DateA）・作成日時・更新日時 | "Results"."DateA" desc | 効く |
数値（NumA） | isnull("Results"."NumA", 0) desc | 効かない |
| 数値で「NULL許容」をオン | "Results"."NumA" desc | 効く |
分類（ClassB） | isnull("Results"."ClassB", '') asc | 効かない |
| 状況・管理者（記録テーブル） | isnull("Results"."Status", 0) asc | 効かない |
| タイトル | isnull("Results_Items"."Title", '') asc | 効かない |
リンク項目（ClassA） | リンク先 Items の Title を引くサブクエリ | 効かない |
包まれるかどうかは、列の定義（Definition_Column と ExtendedColumnDefinitions）の Nullable で決まります（SiteSettings.cs#L2251）。期限付きテーブルの Status や Creator・Updator は NULL を許さない定義なので、包まれません。
| 並べ替え（案件 50 万件、SQL Server） | 追加索引なし | 索引を追加 |
|---|---|---|
isnull(NumA, 0) desc | 64,209 ページ、108 ms | 59,075 ページ、97 ms（効かない） |
NumA desc（NULL許容） | 64,209 ページ、107 ms | 181 ページ、0.1 ms |
DateA desc | 64,209 ページ、100 ms | 181 ページ、0.1 ms |
PostgreSQL では式の索引（("SiteId", coalesce("NumA", 0) DESC, …)）を作ると、coalesce で包まれた並べ替えも索引で読めます（224 ms → 0.35 ms）。MySQL の ifnull("Results"."NumA", 0) も、関数索引（(ifnull(NumA, 0)) DESC）で 573 ms → 0.15 ms になりました。ただし MySQL は text の式に索引を作れないので、分類項目の並べ替えは索引で速くできません。SQL Server で同じことをするには計算列が要りますが、列を足すと CodeDefiner がテーブルを作り直すので使えません（後述）。
件数を毎回数え直す
1 ページ目を表示するたびに、条件に合うレコードを 2 回数えます（一覧の件数と集計の件数）。件数は条件に合う行をすべて数えるので、条件に合う件数に比例して遅く なり、索引で速くなるのは条件で件数が絞れるときだけです。
| 件数（案件 50 万件、絞り込みなし） | SQL Server | PostgreSQL | MySQL |
|---|---|---|---|
SELECT COUNT(*)（Items との JOIN 込み） | 3,492 ページ、19 ms（CPU 266 ms） | 18,435 ブロック、118 ms | 545 ms |
索引ではこれ以上速くならないので、既定のビューで絞り込むか、1 サイトのレコード数を抑えるしかありません。
リンク先の項目で絞り込む
一覧のフィルタで、リンク先の項目（例: 顧客の「都道府県」ClassA~1,ClassB）を条件にすると、自サイトの全レコードについて try_cast("Results"."ClassA" as bigint) でリンク先を JOIN してから絞り込みます。結合の条件が関数なので、索引で先に絞れません。
| 方法（案件 50 万件、金額の降順、SQL Server、画面全体） | 追加索引なし | 索引を追加 |
|---|---|---|
リンク先の項目 ClassA~1,ClassB で絞り込み | 715 ms | 939 ms（速くならない） |
都道府県を案件側の項目 ClassC にコピーして絞り込み | 327 ms | 166 ms |
索引を追加した列は、下の 列の並べ方 に従ったものです（コピーした列では (SiteId, ClassC, NumA DESC, UpdatedTime DESC, ResultId DESC)）。リンク先の値を自サイトの項目にコピーするには、リンクの「項目の連携」（Lookups）が使えます。
リンクの向きで重さが変わる（子から親、親から子）
A ← B ← C のようにリンクを重ねたとき、B の一覧に A の項目を出す（子から親、ClassA~<A のサイト ID>）のと、B の一覧に C の項目を出す（親から子、ClassA~~<C のサイト ID>）のとでは、JOIN の形が違います（SiteSettings.cs#L5642-L5666）。
sql
-- 子から親（B の一覧に A の項目）: 自分の ClassA を変換して、親の主キーで引く
left outer join "Items" as "ClassA~1_Items"
on try_cast("Results"."ClassA" as bigint) = "ClassA~1_Items"."ReferenceId" and "ClassA~1_Items"."SiteId" = 1
-- 親から子（A の一覧に B の項目）: 子の ClassA を変換して、自分の ID と比べる
left outer join "Results" as "ClassA~~2"
on "Results"."ResultId" = try_cast("ClassA~~2"."ClassA" as bigint) and "ClassA~~2"."SiteId" = 2- 子から親は、変換するのが自分の列で、相手は
Itemsの主キーなので、表示する 1 ページ分だけ親を引けば済みます（既定の並べ替えの索引があれば、リンク項目を表示した一覧でも 474 ページ・0.3 ms）。 - 親から子は、変換するのが 子の列 なので、子の
ClassAに索引があっても使えません。子サイトの全レコードを読んで突き合わせます。 - 件数の SELECT にも同じ JOIN が付くため、親から子の項目を出した一覧の件数は 親の件数ではなく JOIN した行の数 になります。検証では、顧客 2 万件の一覧に案件（50 万件）の項目を出すと、件数の SELECT の結果は 500,000 でした。
顧客（2 万件）の一覧に、子である案件（50 万件）の項目 ClassA~~2,Title・ClassA~~2,NumA を足したときの画面全体の応答時間です。
| 顧客の一覧（2 万件） | SQL Server | PostgreSQL | MySQL |
|---|---|---|---|
| 子の項目なし | 104 ms | 78 ms | 222 ms |
| 子の項目あり | 388 ms | 1,396 ms | 300 秒で打ち切り（件数の SELECT が終わらない） |
| 子の項目あり＋子側に式の索引（PostgreSQL） | — | 230 ms | — |
MySQL は親の 1 行ごとに子を全件読む入れ子のループになり、件数の SELECT（2 万 × 50 万）が終わりませんでした。PostgreSQL は、プリザンターが出す変換式と同じ式の索引を子のテーブルに作ると、親から子の JOIN も索引で引けます（1 ページ分の SELECT が 468 ms → 0.3 ms）。
sql
CREATE INDEX CONCURRENTLY "ixc_Results_ClassA_child" ON "Implem.Pleasanter"."Results"
("SiteId", (CASE WHEN "ClassA"~E'^\\d+$' THEN "ClassA"::bigint ELSE null END));変換式は CreateTryCast() が作ります（PostgreSqlCommandText.cs#L57-L62）。SQL Server の try_cast は計算列なしでは索引にできず、MySQL の cast(… as signed) の関数索引は、テーブルに数値でない値（別のサイトのコード値など）が 1 件でもあると作成に失敗しました（Data truncated for functional index）。大きな子サイトの項目を親の一覧に出さない のが確実です。子の一覧を見るなら、編集画面のリンクの一覧（Links テーブルの主キーで引く）を使います。
リンク項目を含むタイトルを親の更新で書き換える
子サイトのタイトルにリンク項目を含めている（「タイトル」に ClassA を選んでいる）と、親のタイトルを変えたときに、子の Items.Title を書き換えます（ItemUtilities.cs#L78-L120）。
- 子 1 件ごとに
UPDATE "Items"を 1 本ずつ流します。子が 30 件の親を更新すると、SQL が 77 本（うちUPDATE "Items"が 30 本）、460 ms でした。 - 子が 100 件を超えると、子サイトの全レコード を対象にします。
GetResults()が 100 件以下ならResultId IN (…)で絞り、超えるとSiteIdだけで読むためです（ItemUtilities.cs#L278-L300）。さらに、各レコードのタイトルを作り直すときにリンク先のタイトルを親ごとに 1 本ずつ引きます（Column.cs#L755-L797）。
検証で子が 174 件の顧客のタイトルを API で更新すると、案件 50 万件すべてについて、顧客ごとのタイトルの SELECT（約 2 万本）と 1 件ずつの UPDATE "Items" が始まり、47 分たっても 41 万件目 でした（そこでプリザンターを再起動して止めました）。途中で見た SQL Server のプランキャッシュは、ID をリテラルで埋め込んだ SELECT が 1 本ずつ別のプランになるため、4,588 件・808 MB に膨らんでいました。
大きな子サイトでは、リンク項目をタイトルに含めないでください。親の名前を子の一覧に出したいだけなら、一覧にリンク先の項目（ClassA~1,Title）を出せば、親のタイトルを変えても子は書き換わりません。
項目のアクセス制御
項目のアクセス制御（テーブルの管理 > 項目のアクセス制御）は、SQL を増やしません。一般ユーザーで一覧を開いたときの SQL は、読み取り・更新の制御（「レコードの管理者」などのレコード単位の条件）を付けても付けなくても 71 本で同じでした。
判定はメモリ上で行いますが、レコード単位の条件は行ごとに判定し直します。一覧の各行でレコードを作るたびに判定結果のキャッシュを消すためです（HtmlGrids.cs#L651-L664、SiteSettings.cs#L3627-L3637）。API で 200 件を取得すると、制御なしの 181 ms が、分類と数値の 2 項目に読み取り制御・日付に更新制御を付けて 220 ms になりました。行数に比例するアプリケーション側の負荷で、DB の索引では変わりません。
1 回の操作で流れる SQL の本数
ログイン済みのユーザーが一覧を 1 回開くと 32 本、編集画面を 1 回開くと 36 本の SQL が流れました（SQL Server、sys.dm_exec_query_stats の実行回数の合計）。一覧・レコードの取得のほかに、セッションの読み書き（Sessions を 4 回読んで更新）、テナント情報（4 回）、ユーザー（2 回）、システムログの記録（2 回）などが毎回流れます。セッションの負荷は 同時アクセスが遅くなる理由 を参照してください。
1 本ずつは軽くても、ループの中で SQL を流す処理（上のタイトルの書き換え、サマリの集計先ごとの更新、リンク付きのコピーでの子の作成）は、件数がそのまま SQL の本数になります。
書き込みのたびに更新される索引
レコードを 1 件更新すると、少なくとも次の SQL が流れます。
- 版を上げる場合は
Results_historyへのコピー Resultsの UPDATE。UpdatedTimeは必ず現在時刻に更新される（SqlUpdate.cs#L64）Itemsの UPDATE（タイトル・全文検索用の文字列）Linksの DELETE と INSERT。リンク項目の値が変わっていなくても毎回行う（ResultModel.cs#L2116-L2120）
UpdatedTime は SQL Server のクラスター化索引のキーなので、更新のたびに行の位置が変わります。SQL Server の非クラスター化索引はクラスター化索引のキーを持っているため、追加した索引はすべて、どの列を更新しても書き換わります。
| 1 件の UPDATE（1,000 回の平均） | 追加索引なし | 追加索引 5 本 |
|---|---|---|
| SQL Server | 読み取り 47 ページ、書き込み 0.6 ページ、CPU 87 µs | 読み取り 131 ページ、書き込み 10.5 ページ、CPU 275 µs |
| PostgreSQL | 22 ブロック（うち変更 1.8）、0.048 ms | 37.5 ブロック（うち変更 2.9）、0.047 ms |
1 件あたりではミリ秒未満ですが、インポートや一括更新、サマリの再計算（集計先のレコードごとに更新処理が走る）では件数分だけ効いてきます。
サイトの設計で避ける
索引を足す前に、サイトの作りで次のことができます。
| 対策 | 効くもの |
|---|---|
| よく絞り込む分類項目は選択肢にする。自由入力のコード類は「検索方法」を完全一致か前方一致にする | 絞り込み（部分一致のままでは索引が効かない） |
| 並べ替えに使う数値項目は「NULL許容」をオンにする | 並べ替え（SQL Server で ISNULL が外れる） |
| 並べ替えは日付・作成日時・更新日時か、NULL許容の数値にする | 並べ替え（分類・状況・管理者・タイトル・リンク項目は SQL Server で索引が効かない） |
| リンク先の値で絞り込むなら、項目の連携で自サイトの項目にコピーする | リンク先を経由した絞り込み |
| 既定のビューに絞り込みを入れる。「常に検索条件を要求する」を使う | 件数の数え直し |
| 年度などでサイトを分け、1 サイトのレコード数を抑える | 件数の数え直し・並べ替え（索引の先頭が SiteId なので、サイトが小さいほど読む範囲が狭い） |
大きな子サイトの項目（ClassA~~<子>）を親の一覧に出さない。子は編集画面のリンクの一覧で見る | 親から子の JOIN（子の全件を読み、件数も JOIN した行数になる） |
| 大きな子サイトでは、リンク項目をタイトルに含めない | 親の更新による子のタイトルの書き換え（子が 100 件を超えると子サイト全件） |
「常に検索条件を要求する」（AlwaysRequestSearchCondition）をオンにすると、絞り込みも検索もないときは WHERE に (0=1) を足して、レコードを読みません（View.cs#L3494-L3509）。
インデックスを追加する
列の並べ方
プリザンターの一覧の SQL に合わせるには、次の順に列を並べます。
図を読み込み中…
- 先頭は必ず
SiteIdです。すべての SQL が"SiteId"=<サイト ID>で絞ります。 - 並べ替えの列の後ろには、プリザンターが必ず足す
UpdatedTime DESC, ResultId DESC（期限付きテーブルはIssueId DESC）まで入れます。SQL Server では、(SiteId, DateA DESC)だけではORDER BY DateA DESC, UpdatedTime DESC, ResultId DESCの順に読めず、全件を並べ替えました（64,209 → 59,075 ページで、ほぼ変わらない）。PostgreSQL は途中まで並んだ索引から残りを並べ替える（Incremental Sort）ので、(SiteId, DateA)だけでも 3.6 ms になりました。 - 範囲の絞り込み（数値・日付の範囲、未完了
Status < 900、前方一致）があるときは、それを完全一致の列の直後に置き、並べ替えの列は入れません。件数の SELECT が同じ条件で毎回流れるので、該当する行だけを読めることを優先します。
後述のスクリプトが出した索引（7 本）をまとめて作ったときの、画面全体の応答時間です（SQL Server、案件 50 万件、7 回の中央値。ほかのコンテナも動いている環境で、同じ条件でも ±30% ほどばらつきます）。
| ビュー | 効いた索引 | 追加前 | 追加後 |
|---|---|---|---|
| 既定（リンク項目を表示） | (SiteId, UpdatedTime DESC, ResultId DESC) | 1,064 ms | 202 ms |
| 種別 = 新規、受注予定日の降順 | (SiteId, ClassB, DateA DESC, UpdatedTime DESC, ResultId DESC) | 356 ms | 117 ms |
| 顧客（リンク項目）で絞り込み | (SiteId, ClassA, UpdatedTime DESC, ResultId DESC) | 244 ms | 108 ms |
| 金額の降順（NULL許容） | (SiteId, NumA DESC, UpdatedTime DESC, ResultId DESC) | 1,036 ms | 212 ms |
| 作成日時の降順 | (SiteId, CreatedTime DESC, UpdatedTime DESC, ResultId DESC) | 994 ms | 324 ms |
| 状況・金額の範囲・日付の範囲・タイトルの部分一致 | (SiteId, Status, NumA) | 244 ms | 192 ms |
| リンク先の項目で絞り込み | なし（効く索引がない） | 715 ms | 939 ms |
作る手順
App_Data/Parameters/Rds.jsonに"DisableIndexChangeDetection": trueがあることを確かめる（ない・falseなら設定する）。これがないと、CodeDefiner が追加した索引を差分とみなしてテーブルを作り直し、索引を消す（下記の「CodeDefiner が索引を消す・テーブルを作り直す」）。- 後述のスクリプトで候補を出し、SQL を確認する。スクリプトに
-RdsJsonPathを渡すと 1. の設定も確かめる。 - 出した SQL を DB に流す。既定で、運用を止めずに作る書き方になっている（運用を止めずに作る）。
- よく使うビューの応答を、作る前と後で比べる。
- CodeDefiner（バージョンアップ時の
_rdsなど）を実行したら、同じ SQL をもう一度流す。すでにある索引は作らない書き方なので、そのまま流せる。
作るときの注意
CodeDefiner が索引を消す・テーブルを作り直す
CodeDefiner（_rds）は、定義にない索引を知りません。
Rds.jsonのDisableIndexChangeDetectionがfalseだと、DB の索引の名前の一覧と定義を比べ（Indexes.cs#L253-L278）、違えばテーブルを作り直します（TablesConfigurator.cs#L243-L263）。作り直しは、新しいテーブルを作って全件をコピーし、元のテーブルを_Migrated_<日時>_Resultsに改名する処理です（Tables.cs#L41-L100、MigrateTable.sql）。検証環境でfalseにして_rdsを実行すると、ResultsとItemsが作り直され、追加した索引は新しいテーブルからなくなり、元の 52 万件が_Migrated_…のテーブルに残りました。- 1.5.8.1 の
Rds.jsonは"DisableIndexChangeDetection": trueです。SQL Server・PostgreSQL・MySQL とも、この設定のまま_rdsを実行しても、追加した索引（式の索引・関数索引を含む）は残りました（Rds.json#L11）。Rds.jsonにこの行がないとfalseになる（boolの既定値）ので、索引を足す前に確認してください。 trueでも、列の数や型が変わるとき（バージョンアップで列が増えるときなど）はテーブルが作り直され、追加した索引はなくなります。CodeDefiner を実行したあとは、索引を作る SQL をもう一度流します。_Migrated_…のテーブルは自動では消えません。
SQL Server の索引キーは 1,700 バイトまで
分類項目は nvarchar(1024)（最大 2,048 バイト）なので、分類項目を含む索引は作成時に次の警告が出ます。
text
Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'ixc_Results_ClassA_65b94875' has maximum length of 2072 bytes. For some combination of large values, the insert/update operation will fail.索引は作れますが、キーの合計が 1,700 バイトを超える値を入れると、その登録・更新は失敗します。検証環境で (SiteId, ClassA, UpdatedTime DESC, ResultId DESC) を作り、ClassA に 900 文字を API で入れると更新が失敗し、SysLogs に次のエラーが残りました（830 文字は成功）。
text
SqlException: Operation failed. The index entry of length 1824 bytes for the index 'ixc_Results_ClassA_65b94875' exceeds the maximum length of 1700 bytes for nonclustered indexes.選択肢・コード・リンク項目のように値が短い項目に限って索引を作り、既存データの最大長（MAX(DATALENGTH(ClassA))）も確かめます。PostgreSQL の B-tree も 1 行が約 2,700 バイトまでです。MySQL は text 列の先頭の文字数を決めて索引にする（スクリプトの既定は 100 文字）ので、長い値でも登録は失敗しませんが、その列の順序で並べ替えることはできません。
索引で逆に遅くなることがある
Items (SiteId, Title) を作ると、リンク項目の選択肢（④）は 7,880 → 1,605 ページ、8.5 → 0.8 ms になりました。一方、タイトルの部分一致を含むビューでは、SQL Server がこの索引を使う並列でないプランを選び、画面全体の応答が 320 ms → 828 ms に悪化しました（この索引を外すと 180 ms）。部分一致の条件がある一覧は索引では速くならないうえ、索引を足すとプランが変わることがあります。追加前後で、よく使うビューの応答を測って比べてください。
運用を止めずに作る
通常の CREATE INDEX は、作成中にテーブルへの書き込みを待たせます。スクリプトが出す SQL は、既定で次の書き方にしています（-Offline で通常の書き方になります）。
| DBMS | 書き方 | 検証結果・注意 |
|---|---|---|
| SQL Server | WITH (ONLINE = ON)。SERVERPROPERTY('EngineEdition') が 3（Enterprise・Developer）、5（Azure SQL Database）、8（Azure SQL Managed Instance）のときだけ使い、それ以外はオンラインでない作成に切り替える | ONLINE = ON はエディションが対応していないと、実行しない分岐に書いてもバッチのコンパイルで失敗しました（Express で Msg 1712 Online index operations can only be performed in Enterprise edition）。そのため EXEC (N'…') で実行しています。Standard・Express では作成中の書き込みが待たされます |
| PostgreSQL | CREATE INDEX CONCURRENTLY IF NOT EXISTS | トランザクションの中では実行できないので、psql -f などで 1 文ずつ流します。失敗すると INVALID の索引が残り、IF NOT EXISTS では作り直されません。スクリプトはこれを Invalid として報告し、削除して作り直す SQL を出します |
| MySQL | ALTER TABLE … ADD INDEX …, ALGORITHM=INPLACE, LOCK=NONE | 関数索引（ifnull の並べ替え）は LOCK=NONE にできず（ERROR 1846 … Try LOCK=SHARED）、LOCK=SHARED（作成中の書き込みは待たされる）になります |
SiteSettings から必要なインデックスを割り出す
どの索引が要るかは、各サイトの設定（Sites.SiteSettings の JSON）にほぼ書かれています。次のスクリプトは、レコード数が多いサイトの SiteSettings を読み、上の並べ方のルールで索引の候補を出します。DB は変更しません。
読んでいる設定
| SiteSettings の場所 | 使い方 |
|---|---|
Views[].ColumnFilterHash | 保存したビューの絞り込み。項目の型と検索方法（Views[].ColumnFilterSearchTypes → Columns[].SearchType → 既定）で、完全一致・範囲・前方一致・索引が効かないものに分ける |
Views[].ColumnFilterNegatives | 否定条件の項目は候補にしない |
Views[].ColumnSorterHash | 並べ替えの列と順序。ISNULL で包まれる列は、SQL Server では「索引が効かない」として報告し、PostgreSQL では coalesce の式の索引にする |
Views[].Incomplete | 未完了（Status < 900）を範囲の絞り込みとして扱う |
Columns[] | ChoicesText（選択肢・リンク）、MultipleSelections、SearchType、数値の Nullable |
Links[]、ChoicesText の [[サイト ID]] | リンク項目。親レコードでの絞り込み用に (SiteId, リンク項目) を出す |
Summaries[] | サマリの集計（WHERE SiteId = … AND リンク項目 IN (…) GROUP BY リンク項目、Summaries.cs#L1063-L1075）用に、集計元サイトの (SiteId, リンク項目) を出す |
FilterColumns | -IncludeFilterColumns を付けたときだけ。フィルタ欄に置いた、完全一致で絞れる項目 |
GridColumns、Views[].GridColumns | 親から子の項目（ClassA~~300,Title）。PostgreSQL では子のテーブルに変換式の索引を出し、SQL Server・MySQL では「索引が効かない」として報告する |
ほかに、SQL Server と MySQL では既定の並べ替え用の (SiteId, UpdatedTime DESC, ID DESC) を、対象サイトがあるテーブルに 1 つ出します。候補のうち、別の候補の先頭部分と同じものはまとめます。DB に接続したときは、同じ名前か、同じ列で始まる既存の索引があるものを Existing にします。
DBMS ごとに、SQL の形を変えています。
| DBMS | 違い |
|---|---|
| SQL Server | ISNULL で包まれる並べ替えは候補にしない。分類項目を含む索引には 1,700 バイトの注意を付ける |
| PostgreSQL | coalesce の並べ替えは式の索引、前方一致は varchar_pattern_ops、親から子は変換式の索引にする。既定の並べ替えの索引は出さない |
| MySQL | text 列は ClassA(100) のように先頭の文字数を決める（-MySqlPrefixLength）。数値の ifnull の並べ替えは関数索引にし、分類の並べ替えとタイトル順の選択肢は候補にしない |
使い方
SQL Server には直接接続して読みます。Windows 認証が既定で、SQL 認証は -Credential で渡します。
powershell
./Get-PleasanterIndexAdvice.ps1 -Server sql.example.local -Database Implem.Pleasanter `
-RdsJsonPath 'C:\web\pleasanter\Implem.Pleasanter\App_Data\Parameters\Rds.json' `
-OutputSqlPath ./indexes.sql |
Format-Table Status, Table, Keys, Reason -WrapPostgreSQL・MySQL は、psql・mysql コマンドを使って読みます（-ClientPath で場所を指定できます）。パスワードは子プロセスの環境変数（PGPASSWORD・MYSQL_PWD）で渡し、コマンドラインには出しません。
powershell
./Get-PleasanterIndexAdvice.ps1 -Dbms PostgreSQL -Server db.example.local -Database Implem.Pleasanter `
-Credential (Get-Credential) -OutputSqlPath ./indexes.sql
./Get-PleasanterIndexAdvice.ps1 -Dbms MySQL -Server db.example.local -Database Implem.Pleasanter `
-Credential (Get-Credential) -OutputSqlPath ./indexes.sqlサーバーに接続できない場所で調べるときは、次の SQL の結果を JSON ファイルに書き出して -SitesJsonPath で渡します（既存の索引との比較はしません）。
sql
SELECT (
SELECT s.SiteId, s.Title, s.ReferenceType, s.SiteSettings,
CASE s.ReferenceType
WHEN N'Results' THEN (SELECT COUNT_BIG(*) FROM dbo.Results r WHERE r.SiteId = s.SiteId)
WHEN N'Issues' THEN (SELECT COUNT_BIG(*) FROM dbo.Issues i WHERE i.SiteId = s.SiteId)
END AS RecordCount
FROM dbo.Sites s
WHERE s.ReferenceType IN (N'Results', N'Issues')
FOR JSON PATH) AS Sites;sql
SELECT json_agg(t)
FROM (
SELECT s."SiteId", s."Title", s."ReferenceType", s."SiteSettings",
CASE s."ReferenceType"
WHEN 'Results' THEN (SELECT count(*) FROM "Implem.Pleasanter"."Results" r WHERE r."SiteId" = s."SiteId")
WHEN 'Issues' THEN (SELECT count(*) FROM "Implem.Pleasanter"."Issues" i WHERE i."SiteId" = s."SiteId")
END AS "RecordCount"
FROM "Implem.Pleasanter"."Sites" s
WHERE s."ReferenceType" IN ('Results', 'Issues')
) t;sql
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'SiteId', s.SiteId, 'Title', s.Title, 'ReferenceType', s.ReferenceType, 'SiteSettings', s.SiteSettings,
'RecordCount', CASE s.ReferenceType
WHEN 'Results' THEN (SELECT COUNT(*) FROM Results r WHERE r.SiteId = s.SiteId)
WHEN 'Issues' THEN (SELECT COUNT(*) FROM Issues i WHERE i.SiteId = s.SiteId) END))
FROM Sites s
WHERE s.ReferenceType IN ('Results', 'Issues');書き出しは、SQL Server は sqlcmd -y 0、PostgreSQL は psql -At、MySQL は mysql --batch --raw --skip-column-names のように、整形しない出力にします。
powershell
./Get-PleasanterIndexAdvice.ps1 -SitesJsonPath ./sites.json -Dbms PostgreSQL -OutputSqlPath ./indexes.sql| パラメータ | 意味 |
|---|---|
-Dbms | SQLServer（既定）・PostgreSQL・MySQL |
-RdsJsonPath | Rds.json の DisableIndexChangeDetection が true かを確かめる。true でなければ警告し、SQL の先頭にも書く |
-MinRecords | これより少ないサイトは対象外（既定 10000） |
-IncludeFilterColumns | フィルタ欄の項目も候補にする |
-IncludeItemsTitle | リンク項目の選択肢用の Items (SiteId, Title) も候補にする（逆に遅くなることがある ため既定では出さない） |
-MySqlPrefixLength | MySQL の text 列を索引にする文字数（既定 100） |
-Offline | 運用を止めずに作る書き方（ONLINE = ON・CONCURRENTLY・LOCK=NONE）にしない |
-TrustServerCertificate | SQL Server のサーバー証明書を検証しない（検証環境用） |
-Port・-ClientPath | ポートと、PostgreSQL・MySQL の psql・mysql の場所 |
出力の例
検証環境（案件サイトにビューを 12 個作ったもの）に SQL Server で実行した結果です。Status が Proposed のものが -OutputSqlPath の SQL になります。
text
Status Table Keys Reason
------ ----- ---- ------
Proposed Results SiteId ASC, UpdatedTime DESC, ResultId DESC 既定の並べ替え（UpdatedTime DESC, ID DESC）
Proposed Results SiteId ASC, ClassB ASC, DateA DESC, UpdatedTime DESC, ResultId DESC 絞り込み（ClassB） + 並べ替え（DateA）
Proposed Results SiteId ASC, ClassA ASC, UpdatedTime DESC, ResultId DESC 絞り込み（ClassA） + 既定の並べ替え（UpdatedTime DESC, ID DESC） / リンク項目 ClassA（…）
Proposed Results SiteId ASC, NumA DESC, UpdatedTime DESC, ResultId DESC 並べ替え（NumA）
Proposed Results SiteId ASC, CreatedTime DESC, UpdatedTime DESC, ResultId DESC 並べ替え（CreatedTime）
Proposed Results SiteId ASC, Status ASC, NumA ASC 絞り込み（Status） + 範囲の絞り込み（NumA）
Proposed Results SiteId ASC, ClassC ASC, NumA DESC, UpdatedTime DESC, ResultId DESC 絞り込み（ClassC） + 並べ替え（NumA）
NotIndexable Results リンク先の項目 ClassA~1,ClassB での絞り込みはリンク先を型変換で JOIN した先の条件になり、…
NotIndexable Results ClassB の並べ替えは ISNULL(ClassB, …) になり SQL Server では索引の順序を使えない
NotIndexable Results Status の並べ替えは ISNULL(Status, …) になり SQL Server では索引の順序を使えない
NotIndexable Results Manager の並べ替えは ISNULL(Manager, …) になり SQL Server では索引の順序を使えない
NotIndexable Results タイトルの並べ替えは ISNULL(Items.Title) になる
NotIndexable Results リンク項目 ClassA の並べ替えは Items のタイトルを引くサブクエリになる
NotIndexable Results Title の検索方法が部分一致（LIKE '%値%'）で B-tree 索引を使えない
Optional Items リンク項目の選択肢（リンク先を Title 順に 500 件読む）には Items (SiteId, Title) が効くが、…出力される SQL は次の形です（1 候補分）。何度流しても重複して作らないので、CodeDefiner の実行後にそのまま流し直せます。索引の名前は ixc_<テーブル>_<列>_<キー列から計算した 8 桁> で、同じ候補からは同じ名前になります。
sql
-- Results: 並べ替え（CreatedTime）
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Results]') AND name = N'ixc_Results_CreatedTime_19346072')
BEGIN
IF CAST(SERVERPROPERTY('EngineEdition') AS int) IN (3, 5, 8)
EXEC (N'CREATE NONCLUSTERED INDEX [ixc_Results_CreatedTime_19346072] ON [dbo].[Results] ([SiteId] ASC, [CreatedTime] DESC, [UpdatedTime] DESC, [ResultId] DESC) WITH (ONLINE = ON)');
ELSE
BEGIN
PRINT N'ONLINE = ON is not available in this edition; building ixc_Results_CreatedTime_19346072 offline (writes to Results wait until it finishes).';
EXEC (N'CREATE NONCLUSTERED INDEX [ixc_Results_CreatedTime_19346072] ON [dbo].[Results] ([SiteId] ASC, [CreatedTime] DESC, [UpdatedTime] DESC, [ResultId] DESC)');
END
END;
-- DROP INDEX [ixc_Results_CreatedTime_19346072] ON [dbo].[Results];sql
-- Results: 子の項目 ClassA~~2,Title（子のリンク項目を bigint に変換して親と JOIN する式）
-- 対象: 1:顧客 / 一覧
CREATE INDEX CONCURRENTLY IF NOT EXISTS "ixc_Results_ClassA_7a81890f" ON "Implem.Pleasanter"."Results" ("SiteId" ASC, (CASE WHEN "ClassA"~E'^\\d+$' THEN "ClassA"::bigint ELSE null END) ASC);
-- DROP INDEX CONCURRENTLY "Implem.Pleasanter"."ixc_Results_ClassA_7a81890f";sql
-- Results: 絞り込み（ClassA） + 既定の並べ替え（UpdatedTime DESC, ID DESC） / リンク項目 ClassA（親レコードでの絞り込み・リンク付きコピー）
-- 注意: TEXT 列は先頭 100 文字だけを索引にする（並べ替えには使えない）
SET @ixc_ddl = IF((SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'Results' AND INDEX_NAME = 'ixc_Results_ClassA_65b94875') = 0,
'ALTER TABLE `Results` ADD INDEX `ixc_Results_ClassA_65b94875` (`SiteId` ASC, `ClassA`(100) ASC, `UpdatedTime` DESC, `ResultId` DESC), ALGORITHM=INPLACE, LOCK=NONE', 'DO 0');
PREPARE ixc_stmt FROM @ixc_ddl; EXECUTE ixc_stmt; DEALLOCATE PREPARE ixc_stmt;
-- ALTER TABLE `Results` DROP INDEX `ixc_Results_ClassA_65b94875`;スクリプトが見ていないもの
画面で一時的に指定した絞り込み・並べ替え（ビューに保存していないもの）、検索欄、サーバースクリプトや拡張 SQL で足した条件、API からの取得条件は SiteSettings に残らないので、候補に出ません。候補は出発点として使い、実際に遅い画面の SQL（SQL Server の sys.dm_exec_query_stats、PostgreSQL の pg_stat_statements）と見比べてください。
スクリプト全文
Get-PleasanterIndexAdvice.ps1 をダウンロード
PowerShell 7.3 以降で動きます。SQL Server への接続には System.Data.SqlClient、PostgreSQL・MySQL には psql・mysql コマンドを使います。3 つの DBMS とも、検証環境の実 DB で候補の出力と、出した SQL の実行（2 回流して重複しないこと）を確かめました。
Get-PleasanterIndexAdvice.ps1
powershell
#Requires -Version 7.3
<#
.SYNOPSIS
Reads Pleasanter site settings (SiteSettings) and proposes additional indexes
for the Results / Issues / Items tables (SQL Server, PostgreSQL, MySQL).
.DESCRIPTION
Analyzes the saved views (filters, sorts, grid columns), summaries and link
columns of each site whose record count is at least MinRecords, and derives the
index key columns that let the grid / count / summary queries generated by
Pleasanter 1.5.8.1 use an index instead of scanning or sorting every record of
the site.
Input modes:
- Server/Database: reads Sites, record counts and existing indexes directly.
SQL Server uses System.Data.SqlClient (encrypted connection).
PostgreSQL / MySQL run the psql / mysql command-line client (ClientPath).
The password is passed to the client through PGPASSWORD / MYSQL_PWD of the
child process only, never on the command line.
- SitesJsonPath: reads a JSON array exported by the query shown in the manual.
Nothing is changed in the database. The script returns one object per proposal
(Status = Proposed / Existing / Invalid / Optional / NotIndexable) and, with
OutputSqlPath, writes idempotent CREATE INDEX statements to a file.
The generated statements build the indexes online by default:
- SQL Server: WITH (ONLINE = ON) on Enterprise / Developer / Azure SQL
(EngineEdition 3, 5, 8). Other editions fall back to an offline build,
which blocks writes to the table while it runs.
- PostgreSQL: CREATE INDEX CONCURRENTLY (run it outside a transaction).
A failed concurrent build leaves an INVALID index; the script reports it.
- MySQL: ALGORITHM=INPLACE, LOCK=NONE. Functional key parts (IFNULL sorts)
need LOCK=SHARED, which blocks writes while the index is built.
Use -Offline to generate plain (blocking) statements.
Before running the statements:
- Set "DisableIndexChangeDetection": true in Rds.json (the 1.5.8.1 default).
When it is false, CodeDefiner treats the extra indexes as a difference and
rebuilds the table. Pass RdsJsonPath to have the script check it.
- Indexes created outside CodeDefiner are lost whenever CodeDefiner rebuilds a
table (for example when a version upgrade adds columns). Run the generated
script again after every CodeDefiner run.
- On SQL Server, Class columns are nvarchar(1024) (2048 bytes). A nonclustered
index key over 1700 bytes makes inserts/updates of long values fail.
.PARAMETER Server
Database server host (SQL Server instance name/address), at most 128 characters.
.PARAMETER Database
Exact database name, at most 128 characters.
.PARAMETER Port
TCP port for PostgreSQL / MySQL. Defaults to the client's default.
.PARAMETER Credential
SQL Server: SQL authentication (omit for integrated authentication).
PostgreSQL / MySQL: user name and password for the client.
.PARAMETER TrustServerCertificate
SQL Server only. Accept the server certificate without validation (test environments).
.PARAMETER ClientPath
PostgreSQL / MySQL only. Path of psql / mysql. Defaults to the one on PATH.
.PARAMETER SitesJsonPath
Path of a JSON array of { SiteId, Title, ReferenceType, SiteSettings, RecordCount }.
.PARAMETER Dbms
SQLServer (default), PostgreSQL or MySQL.
.PARAMETER Schema
Schema of the Pleasanter tables. Defaults to dbo (SQL Server) or Implem.Pleasanter
(PostgreSQL). Not used for MySQL (the current database is used).
.PARAMETER RdsJsonPath
Path of App_Data/Parameters/Rds.json. Warns when DisableIndexChangeDetection is not true.
.PARAMETER MinRecords
Sites with fewer records are ignored. Defaults to 10000.
.PARAMETER MySqlPrefixLength
MySQL only. Prefix length (characters) for TEXT columns such as ClassA. Defaults to 100.
.PARAMETER IncludeFilterColumns
Also propose indexes for columns placed in the filter area (FilterColumns) that can
be filtered by equality, not only for filters saved in views.
.PARAMETER IncludeItemsTitle
Also propose Items (SiteId, Title) for link-column choice lists and exact / forward
title filters. Off by default: with this index, SQL Server may choose a serial plan
for partial-match title filters on large sites and become slower.
.PARAMETER Offline
Generate statements without ONLINE / CONCURRENTLY / LOCK=NONE.
.PARAMETER OutputSqlPath
Writes the statements for Proposed / Invalid indexes to this file (UTF-8).
.EXAMPLE
./Get-PleasanterIndexAdvice.ps1 -Server localhost -Database Implem.Pleasanter -OutputSqlPath ./indexes.sql |
Format-Table Status, Table, Keys, Reason -Wrap
.EXAMPLE
./Get-PleasanterIndexAdvice.ps1 -Dbms PostgreSQL -Server db.example.local -Database Implem.Pleasanter `
-Credential (Get-Credential) -OutputSqlPath ./indexes.sql
.EXAMPLE
./Get-PleasanterIndexAdvice.ps1 -SitesJsonPath ./sites.json -Dbms MySQL -OutputSqlPath ./indexes.sql
#>
[CmdletBinding(DefaultParameterSetName = 'Server')]
param(
[Parameter(Mandatory, ParameterSetName = 'Server')]
[ValidateScript({ -not [string]::IsNullOrWhiteSpace($_) -and $_.Length -le 128 })]
[string]$Server,
[Parameter(Mandatory, ParameterSetName = 'Server')]
[ValidateScript({ -not [string]::IsNullOrWhiteSpace($_) -and $_.Length -le 128 })]
[string]$Database,
[Parameter(ParameterSetName = 'Server')]
[ValidateRange(1, 65535)]
[int]$Port,
[Parameter(ParameterSetName = 'Server')]
[System.Management.Automation.PSCredential]$Credential,
[Parameter(ParameterSetName = 'Server')]
[switch]$TrustServerCertificate,
[Parameter(ParameterSetName = 'Server')]
[string]$ClientPath,
[Parameter(Mandatory, ParameterSetName = 'Json')]
[ValidateScript({ Test-Path -LiteralPath $_ -PathType Leaf })]
[string]$SitesJsonPath,
[ValidateSet('SQLServer', 'PostgreSQL', 'MySQL')]
[string]$Dbms = 'SQLServer',
[ValidateScript({ -not [string]::IsNullOrWhiteSpace($_) -and $_.Length -le 128 })]
[string]$Schema,
[ValidateScript({ Test-Path -LiteralPath $_ -PathType Leaf })]
[string]$RdsJsonPath,
[ValidateRange(0, [int]::MaxValue)]
[int]$MinRecords = 10000,
[ValidateRange(1, 768)]
[int]$MySqlPrefixLength = 100,
[switch]$IncludeFilterColumns,
[switch]$IncludeItemsTitle,
[switch]$Offline,
[string]$OutputSqlPath
)
Set-StrictMode -Version 3.0
$ErrorActionPreference = 'Stop'
if (-not $Schema) { $Schema = switch ($Dbms) { 'SQLServer' { 'dbo' } default { 'Implem.Pleasanter' } } }
# ---------------------------------------------------------------------------
# Column metadata (Pleasanter 1.5.8.1 definitions)
# Kind : String / Numeric / Date / Bool / Text (max, not indexable)
# DbNull : the column definition is Nullable (sorting wraps it in ISNULL)
# OnItems : the grid reads the value from the Items table (Title)
# Bytes : declared key size on SQL Server
# ---------------------------------------------------------------------------
function Get-ColumnMeta([string]$table, [string]$name) {
switch -Regex ($name) {
'^Class(\d{3}|[A-Z])$' { return @{ Kind = 'String'; DbNull = $true; Bytes = 2048 } }
'^Num(\d{3}|[A-Z])$' { return @{ Kind = 'Numeric'; DbNull = $true; Bytes = 9 } }
'^Date(\d{3}|[A-Z])$' { return @{ Kind = 'Date'; DbNull = $true; Bytes = 8 } }
'^Check(\d{3}|[A-Z])$' { return @{ Kind = 'Bool'; DbNull = $true; Bytes = 1 } }
'^(Description|Attachments)(\d{3}|[A-Z])$' { return @{ Kind = 'Text' } }
'^(Body|Comments)$' { return @{ Kind = 'Text' } }
'^Title$' { return @{ Kind = 'String'; DbNull = $true; Bytes = 2048; OnItems = $true } }
'^Status$' { return @{ Kind = 'Numeric'; DbNull = ($table -eq 'Results'); Bytes = 4 } }
'^(Manager|Owner)$' { return @{ Kind = 'Numeric'; DbNull = $true; Bytes = 4 } }
'^(Creator|Updator|Ver)$' { return @{ Kind = 'Numeric'; DbNull = $false; Bytes = 4 } }
'^(ResultId|IssueId)$' { return @{ Kind = 'Numeric'; DbNull = $false; Bytes = 8 } }
'^(CreatedTime|UpdatedTime|CompletionTime)$' { return @{ Kind = 'Date'; DbNull = $false; Bytes = 8 } }
'^StartTime$' { return @{ Kind = 'Date'; DbNull = $true; Bytes = 8 } }
'^(WorkValue|ProgressRate|RemainingWorkValue)$' { return @{ Kind = 'Numeric'; DbNull = $true; Bytes = 9 } }
'^Locked$' { return @{ Kind = 'Bool'; DbNull = $true; Bytes = 1 } }
}
return $null
}
function Get-Prop($object, [string]$name) {
if ($null -eq $object) { return $null }
if ($object -is [System.Collections.IDictionary]) {
if ($object.Contains($name)) { return $object[$name] }
return $null
}
return $null
}
function ConvertTo-Array($value) {
if ($null -eq $value) { return @() }
if ($value -is [string]) { return @($value) }
return @($value)
}
function Get-ColumnSetting($ss, [string]$name) {
foreach ($column in (ConvertTo-Array (Get-Prop $ss 'Columns'))) {
if ((Get-Prop $column 'ColumnName') -eq $name) { return $column }
}
return $null
}
function Get-LinkSiteIds($ss) {
$result = @{}
foreach ($link in (ConvertTo-Array (Get-Prop $ss 'Links'))) {
$columnName = Get-Prop $link 'ColumnName'
$siteId = [long](Get-Prop $link 'SiteId')
if ($columnName -and $siteId -gt 0) {
if (-not $result.Contains($columnName)) { $result[$columnName] = [System.Collections.Generic.List[long]]::new() }
if (-not $result[$columnName].Contains($siteId)) { $result[$columnName].Add($siteId) }
}
}
foreach ($column in (ConvertTo-Array (Get-Prop $ss 'Columns'))) {
$choices = [string](Get-Prop $column 'ChoicesText')
foreach ($m in [regex]::Matches($choices, '^\s*\[\[(\d+)[^\]]*\]\]\s*$', 'Multiline')) {
$name = Get-Prop $column 'ColumnName'
$siteId = [long]$m.Groups[1].Value
if (-not $result.Contains($name)) { $result[$name] = [System.Collections.Generic.List[long]]::new() }
if (-not $result[$name].Contains($siteId)) { $result[$name].Add($siteId) }
}
}
return $result
}
function Test-HasChoices($setting) {
if ($null -eq $setting) { return $false }
$controlType = Get-Prop $setting 'ControlType'
$choices = [string](Get-Prop $setting 'ChoicesText')
return ((-not $controlType) -or $controlType -eq 'ChoicesText') -and -not [string]::IsNullOrWhiteSpace($choices)
}
$searchTypeNames = @{ 1 = 'PartialMatch'; 2 = 'ExactMatch'; 3 = 'ForwardMatch'; 11 = 'PartialMatchMultiple'; 12 = 'ExactMatchMultiple'; 13 = 'ForwardMatchMultiple' }
function Get-SearchType($view, $setting, [string]$name) {
$raw = Get-Prop (Get-Prop $view 'ColumnFilterSearchTypes') $name
if ($null -eq $raw) { $raw = Get-Prop $setting 'SearchType' }
if ($null -ne $raw) {
if ($raw -is [string] -and $raw -notmatch '^\d+$') { return $raw }
return $searchTypeNames[[int]$raw]
}
# Column.SearchTypeDefault(): single-select choices -> ExactMatch, otherwise PartialMatch
if ((Test-HasChoices $setting) -and (Get-Prop $setting 'MultipleSelections') -ne $true) { return 'ExactMatch' }
return 'PartialMatch'
}
function Test-RangeValue($value) {
# Numeric filters are a JSON array; an element "from,to" is a range, other elements are IN values
$items = @()
try { $items = @([string]$value | ConvertFrom-Json) } catch { $items = @([string]$value) }
return [bool]($items | Where-Object { [string]$_ -match ',' })
}
# ---------------------------------------------------------------------------
# Classify one filter entry: Equality / Range / Prefix / NotIndexable
# ---------------------------------------------------------------------------
function Get-FilterUse($site, $view, [string]$name, $value) {
$ss = $site.SiteSettings
$table = $site.ReferenceType
if ($name.Contains('~')) {
return @{ Use = 'NotIndexable'; Why = "リンク先の項目 $name での絞り込みはリンク先を型変換で JOIN した先の条件になり、索引で絞れない（リンク先の値を自サイトの項目にコピーして絞り込む）" }
}
$meta = Get-ColumnMeta $table $name
if ($null -eq $meta) { return $null }
if ($meta.Kind -eq 'Text') { return @{ Use = 'NotIndexable'; Why = "$name は長いテキスト（max）で索引を作れない" } }
$setting = Get-ColumnSetting $ss $name
switch ($meta.Kind) {
'Date' { return @{ Use = 'Range'; Meta = $meta } }
'Bool' { return @{ Use = 'Equality'; Meta = $meta } }
'Numeric' {
if (Test-RangeValue $value) { return @{ Use = 'Range'; Meta = $meta } }
return @{ Use = 'Equality'; Meta = $meta }
}
'String' {
if ((Get-Prop $setting 'MultipleSelections') -eq $true) {
return @{ Use = 'NotIndexable'; Why = "$name は複数選択（JSON 文字列を LIKE で検索する）" }
}
$searchType = Get-SearchType $view $setting $name
switch -Wildcard ($searchType) {
'Exact*' { return @{ Use = 'Equality'; Meta = $meta } }
'Forward*' { return @{ Use = 'Prefix'; Meta = $meta } }
default { return @{ Use = 'NotIndexable'; Why = "$name の検索方法が部分一致（LIKE '%値%'）で B-tree 索引を使えない" } }
}
}
}
return $null
}
# ---------------------------------------------------------------------------
# Classify one sort key: returns the key, or NotIndexable
# ---------------------------------------------------------------------------
function Get-SortKey($site, [string]$name, [string]$direction, $linkColumns) {
$table = $site.ReferenceType
$dir = if ($direction -eq 'desc') { 'DESC' } else { 'ASC' }
if ($name.Contains('~')) { return @{ Use = 'NotIndexable'; Why = "リンク先の項目 $name での並べ替え" } }
if ($linkColumns.Contains($name)) { return @{ Use = 'NotIndexable'; Why = "リンク項目 $name の並べ替えは Items のタイトルを引くサブクエリになる" } }
$meta = Get-ColumnMeta $table $name
if ($null -eq $meta -or $meta.Kind -eq 'Text') { return @{ Use = 'NotIndexable'; Why = "$name では並べ替えに索引を使えない" } }
if ($meta.ContainsKey('OnItems')) { return @{ Use = 'NotIndexable'; Why = 'タイトルの並べ替えは ISNULL(Items.Title) になる' } }
$setting = Get-ColumnSetting $site.SiteSettings $name
$wrapped = $meta.DbNull -and $meta.Kind -ne 'Date'
if ($wrapped -and $meta.Kind -eq 'Numeric' -and (Get-Prop $setting 'Nullable') -eq $true) { $wrapped = $false }
if (-not $wrapped) { $key = New-Key $name $dir $meta; $key.Use = 'Key'; return $key }
$hint = if ($meta.Kind -eq 'Numeric' -and $name -match '^Num') { '（数値項目は「NULL許容」を有効にすると ISNULL が外れる）' } else { '' }
switch ($Dbms) {
'PostgreSQL' {
$isNullValue = switch ($meta.Kind) { 'String' { "''" } 'Numeric' { '0' } 'Bool' { 'false' } }
$key = New-Key $name $dir $meta "coalesce(""$name"", $isNullValue)"; $key.Use = 'Key'; return $key
}
'MySQL' {
# Functional key parts cannot be TEXT, so IFNULL(ClassA, '') cannot be indexed
if ($meta.Kind -eq 'String') { return @{ Use = 'NotIndexable'; Why = "$name の並べ替えは IFNULL($name, '') になり、TEXT 列の式には索引を作れない" } }
$key = New-Key $name $dir $meta "ifnull(``$name``, 0)"; $key.Use = 'Key'; $key.Functional = $true; return $key
}
}
return @{ Use = 'NotIndexable'; Why = "$name の並べ替えは ISNULL($name, …) になり SQL Server では索引の順序を使えない$hint" }
}
# ---------------------------------------------------------------------------
# Proposal collection
# ---------------------------------------------------------------------------
$proposals = [ordered]@{}
$notIndexable = [System.Collections.Generic.List[object]]::new()
function New-Key([string]$column, [string]$dir = 'ASC', $meta = $null, [string]$expression = $null) {
@{ Column = $column; Dir = $dir; Meta = $meta; Expression = $expression; Use = $null; Functional = $false }
}
function Get-KeyText($key) {
if ($key.Expression -like '*pattern_ops') { return $key.Expression }
if ($key.Expression) { return "($($key.Expression)) $($key.Dir)" }
return "$($key.Column) $($key.Dir)"
}
function Add-Proposal([string]$table, [object[]]$keys, [string]$reason, [string]$source) {
$keyText = ($keys | ForEach-Object { Get-KeyText $_ }) -join ', '
$id = "$table|$keyText"
if (-not $proposals.Contains($id)) {
$proposals[$id] = [pscustomobject]@{
Table = $table
KeyList = $keys
Keys = $keyText
Reasons = [System.Collections.Generic.List[string]]::new()
Sources = [System.Collections.Generic.List[string]]::new()
}
}
if (-not $proposals[$id].Reasons.Contains($reason)) { $proposals[$id].Reasons.Add($reason) }
if (-not $proposals[$id].Sources.Contains($source)) { $proposals[$id].Sources.Add($source) }
}
function Add-NotIndexable([string]$table, [string]$source, [string]$why, [string]$status = 'NotIndexable') {
if ($notIndexable | Where-Object { $_.Table -eq $table -and $_.Source -eq $source -and $_.Why -eq $why }) { return }
$notIndexable.Add([pscustomobject]@{ Status = $status; Table = $table; Source = $source; Why = $why })
}
function Add-ItemsTitle([string]$reason, [string]$source) {
if ($Dbms -eq 'MySQL') {
Add-NotIndexable 'Items' $source "${reason}: MySQL の Title は TEXT 列で、前方の文字数だけの索引では Title 順に読めない"
return
}
if (-not $IncludeItemsTitle) {
Add-NotIndexable 'Items' $source "${reason}には Items (SiteId, Title) が効くが、部分一致のタイトル絞り込みが遅くなることがあるため既定では出さない（-IncludeItemsTitle で出す）" 'Optional'
return
}
Add-Proposal 'Items' @((New-Key 'SiteId'), (New-Key 'Title' 'ASC' @{ Kind = 'String'; Bytes = 2048 })) $reason $source
}
function Get-TieBreakers([string]$table) {
$id = if ($table -eq 'Issues') { 'IssueId' } else { 'ResultId' }
@((New-Key 'UpdatedTime' 'DESC' (Get-ColumnMeta $table 'UpdatedTime')), (New-Key $id 'DESC' (Get-ColumnMeta $table $id)))
}
function Get-FilterEntries($hash) {
# Flattens and_ groups; or_ groups and special keys are skipped
$entries = [System.Collections.Generic.List[object]]::new()
if ($null -eq $hash) { return $entries }
foreach ($key in $hash.Keys) {
$value = $hash[$key]
if ($key -like 'and_*') {
try { $inner = $value | ConvertFrom-Json -AsHashtable } catch { continue }
foreach ($e in (Get-FilterEntries $inner)) { $entries.Add($e) }
continue
}
if ($key -like 'or_*' -or $key -like 'eq_*' -or $key -like 'notEq_*' -or $key -in @('Groups', 'GroupMembers', 'OnSelectingWhere', 'SiteTitle')) {
$entries.Add(@{ Name = $key; Value = $value; Skip = $true })
continue
}
$entries.Add(@{ Name = $key; Value = $value; Skip = $false })
}
return $entries
}
function Get-ViewIndex($site, $view, [string]$viewLabel, $linkColumns) {
$table = $site.ReferenceType
$source = "$($site.SiteId):$($site.Title) / $viewLabel"
$equality = [System.Collections.Generic.List[object]]::new()
$range = $null
$negatives = @(ConvertTo-Array (Get-Prop $view 'ColumnFilterNegatives'))
foreach ($entry in (Get-FilterEntries (Get-Prop $view 'ColumnFilterHash'))) {
if ($entry.Skip) { Add-NotIndexable $table $source "$($entry.Name) の条件（OR・列どうしの比較など）は索引の候補にしない"; continue }
if ($entry.Name -in $negatives) { Add-NotIndexable $table $source "$($entry.Name) は否定条件"; continue }
$use = Get-FilterUse $site $view $entry.Name $entry.Value
if ($null -eq $use) { continue }
switch ($use.Use) {
'NotIndexable' { Add-NotIndexable $table $source $use.Why }
'Equality' {
if ($use.Meta.ContainsKey('OnItems')) { Add-ItemsTitle 'タイトルの完全一致の絞り込み' $source }
else { $equality.Add((New-Key $entry.Name 'ASC' $use.Meta)) }
}
default {
if ($use.Meta.ContainsKey('OnItems')) { Add-ItemsTitle 'タイトルの前方一致の絞り込み' $source; continue }
if ($null -eq $range) {
$range = New-Key $entry.Name 'ASC' $use.Meta
$range.Use = $use.Use
# PostgreSQL: LIKE 'x%' uses a B-tree only with varchar_pattern_ops (or the C collation)
if ($use.Use -eq 'Prefix' -and $Dbms -eq 'PostgreSQL') { $range.Expression = """$($entry.Name)"" varchar_pattern_ops" }
}
}
}
}
# Incomplete (未完了) -> Status < CompletionCode
if ((Get-Prop $view 'Incomplete') -eq $true -and $null -eq $range) {
$range = New-Key 'Status' 'ASC' (Get-ColumnMeta $table 'Status'); $range.Use = 'Range'
}
if ((Get-Prop $view 'Own') -eq $true) { Add-NotIndexable $table $source '「自分」の絞り込みは Manager と Owner の OR になるため候補にしない' }
if (-not [string]::IsNullOrWhiteSpace([string](Get-Prop $view 'Search'))) { Add-NotIndexable $table $source '検索欄の条件は Items.FullText の LIKE（PostgreSQL は既定の pg_trgm 索引を使う）' }
# Sort keys (ColumnSorterHash keeps the order of the keys)
$sortKeys = [System.Collections.Generic.List[object]]::new()
$sortUsable = $true
$sorter = Get-Prop $view 'ColumnSorterHash'
if ($null -ne $sorter -and $sorter.Count -gt 0) {
foreach ($key in $sorter.Keys) {
$direction = [string]$sorter[$key]
if ($direction -match '^\d+$') { $direction = if ([int]$direction -eq 1) { 'desc' } else { 'asc' } }
$sortKey = Get-SortKey $site $key $direction $linkColumns
if ($sortKey.Use -eq 'NotIndexable') {
Add-NotIndexable $table $source $sortKey.Why
$sortUsable = $false
break
}
if ($sortKey.Column -in @('UpdatedTime', 'ResultId', 'IssueId')) { continue }
$sortKeys.Add($sortKey)
}
}
$keys = [System.Collections.Generic.List[object]]::new()
$keys.Add((New-Key 'SiteId'))
$equality | Sort-Object { $_.Column } | ForEach-Object { $keys.Add($_) }
$reasonParts = [System.Collections.Generic.List[string]]::new()
if ($equality.Count) { $reasonParts.Add('絞り込み（' + (($equality | ForEach-Object Column) -join ', ') + '）') }
if ($null -ne $range) {
# The COUNT(*) query runs with the same WHERE on every first page, so a range /
# prefix filter is placed right after the equality columns (the sort is not covered).
$keys.Add($range)
$label = if ($range.Use -eq 'Prefix') { '前方一致' } else { '範囲' }
$reasonParts.Add("${label}の絞り込み（$($range.Column)）")
}
elseif ($sortUsable) {
foreach ($k in $sortKeys) { $keys.Add($k) }
foreach ($k in (Get-TieBreakers $table)) { $keys.Add($k) }
if ($sortKeys.Count) { $reasonParts.Add('並べ替え（' + (($sortKeys | ForEach-Object Column) -join ', ') + '）') }
else { $reasonParts.Add('既定の並べ替え（UpdatedTime DESC, ID DESC）') }
}
if ($equality.Count -eq 0 -and $null -eq $range -and ($sortKeys.Count -eq 0 -or -not $sortUsable)) { return }
if ($keys.Count -le 1) { return }
Add-Proposal $table $keys.ToArray() ($reasonParts -join ' + ') $source
}
# Child-direction columns (ClassA~~300,Title): the JOIN converts the CHILD's link
# column (try_cast / CASE / cast), so an ordinary index on it cannot be used.
function Add-ChildJoin($site, [string]$gridColumn, [string]$source) {
$path = ($gridColumn -split ',')[0]
foreach ($hop in ($path -split '-')) {
if ($hop -notmatch '^(\w+)~~(\d+)$') { continue }
$column = $Matches[1]; $childSiteId = [long]$Matches[2]
$child = $sites | Where-Object { $_.SiteId -eq $childSiteId } | Select-Object -First 1
$childTable = if ($child) { $child.ReferenceType } else { 'Results' }
switch ($Dbms) {
'PostgreSQL' {
$key = New-Key $column 'ASC' (Get-ColumnMeta $childTable $column) "CASE WHEN ""$column""~E'^\\d+`$' THEN ""$column""::bigint ELSE null END"
Add-Proposal $childTable @((New-Key 'SiteId'), $key) "子の項目 $gridColumn（子のリンク項目を bigint に変換して親と JOIN する式）" $source
}
'MySQL' {
Add-NotIndexable $childTable $source "子の項目 $gridColumn は cast(子の $column as signed) で JOIN する。関数索引は、テーブル内に数値でない $column が 1 件でもあると作れない"
}
default {
Add-NotIndexable $childTable $source "子の項目 $gridColumn は try_cast(子の $column as bigint) で JOIN するため、子サイトの全レコードを読む（SQL Server では式の索引を作れない）"
}
}
}
}
# ---------------------------------------------------------------------------
# Input
# ---------------------------------------------------------------------------
$sites = [System.Collections.Generic.List[object]]::new()
$existingIndexes = @{}
$exportSql = @{
'PostgreSQL' = @'
SELECT json_agg(t) FROM (
SELECT s."SiteId", s."Title", s."ReferenceType", s."SiteSettings",
CASE s."ReferenceType"
WHEN 'Results' THEN (SELECT count(*) FROM "{0}"."Results" r WHERE r."SiteId" = s."SiteId")
WHEN 'Issues' THEN (SELECT count(*) FROM "{0}"."Issues" i WHERE i."SiteId" = s."SiteId")
END AS "RecordCount"
FROM "{0}"."Sites" s
WHERE s."ReferenceType" IN ('Results', 'Issues')
) t;
'@
'MySQL' = @'
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'SiteId', s.SiteId, 'Title', s.Title, 'ReferenceType', s.ReferenceType, 'SiteSettings', s.SiteSettings,
'RecordCount', CASE s.ReferenceType
WHEN 'Results' THEN (SELECT COUNT(*) FROM Results r WHERE r.SiteId = s.SiteId)
WHEN 'Issues' THEN (SELECT COUNT(*) FROM Issues i WHERE i.SiteId = s.SiteId) END))
FROM Sites s
WHERE s.ReferenceType IN ('Results', 'Issues');
'@
}
$indexSql = @{
'PostgreSQL' = @'
SELECT json_agg(t) FROM (
SELECT c.relname AS "TableName", i.relname AS "IndexName", x.indisvalid AS "IsValid",
string_agg(CASE WHEN k.attnum = 0 THEN '(expr)' ELSE a.attname END
|| CASE WHEN (x.indoption[k.ord - 1] & 1) = 1 THEN ' DESC' ELSE ' ASC' END, ', ' ORDER BY k.ord) AS "KeyText"
FROM pg_index x
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL unnest(x.indkey::int2[]) WITH ORDINALITY AS k(attnum, ord)
LEFT JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = k.attnum
WHERE n.nspname = '{0}' AND c.relname IN ('Results', 'Issues', 'Items') AND k.ord <= x.indnkeyatts
GROUP BY c.relname, i.relname, x.indisvalid
) t;
'@
'MySQL' = @'
SELECT JSON_ARRAYAGG(JSON_OBJECT('TableName', TABLE_NAME, 'IndexName', INDEX_NAME, 'IsValid', TRUE, 'KeyText', k)) FROM (
SELECT TABLE_NAME, INDEX_NAME,
GROUP_CONCAT(CONCAT(COALESCE(COLUMN_NAME, '(expr)'), IF(COLLATION = 'D', ' DESC', ' ASC')) ORDER BY SEQ_IN_INDEX SEPARATOR ', ') AS k
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('Results', 'Issues', 'Items')
GROUP BY TABLE_NAME, INDEX_NAME
) t;
'@
}
function Add-ExistingIndex([string]$table, [string]$name, [string]$keys, [bool]$isValid = $true) {
if (-not $existingIndexes.Contains($table)) { $existingIndexes[$table] = [System.Collections.Generic.List[object]]::new() }
$existingIndexes[$table].Add([pscustomobject]@{ Name = $name; Keys = $keys; IsValid = $isValid })
}
function Invoke-DbClient([string]$sql) {
# Runs psql / mysql and returns the single value printed (a JSON document)
$exe = if ($ClientPath) { $ClientPath } elseif ($Dbms -eq 'PostgreSQL') { 'psql' } else { 'mysql' }
if (-not (Get-Command $exe -ErrorAction SilentlyContinue)) { throw "$exe was not found. Install the client or pass -ClientPath." }
$sqlFile = [System.IO.Path]::GetTempFileName()
$envName = if ($Dbms -eq 'PostgreSQL') { 'PGPASSWORD' } else { 'MYSQL_PWD' }
$previous = [Environment]::GetEnvironmentVariable($envName)
try {
Set-Content -LiteralPath $sqlFile -Value $sql -Encoding utf8NoBOM
$arguments = [System.Collections.Generic.List[string]]::new()
if ($Dbms -eq 'PostgreSQL') {
$arguments.AddRange([string[]]@('-h', $Server, '-d', $Database, '-X', '-q', '-A', '-t', '-v', 'ON_ERROR_STOP=1', '-f', $sqlFile))
if ($Port) { $arguments.AddRange([string[]]@('-p', "$Port")) }
if ($Credential) { $arguments.AddRange([string[]]@('-U', $Credential.UserName)) }
}
else {
$arguments.AddRange([string[]]@("--host=$Server", "--database=$Database", '--batch', '--raw', '--skip-column-names', '--default-character-set=utf8mb4', "--execute=source $($sqlFile.Replace('\', '/'))"))
if ($Port) { $arguments.Add("--port=$Port") }
if ($Credential) { $arguments.Add("--user=$($Credential.UserName)") }
}
if ($Credential) { [Environment]::SetEnvironmentVariable($envName, $Credential.GetNetworkCredential().Password) }
$outputLines = & $exe @arguments 2>&1
if ($LASTEXITCODE -ne 0) { throw "$exe failed (exit code $LASTEXITCODE): $(($outputLines | Select-Object -First 3) -join ' ')" }
$text = (($outputLines | Where-Object { $_ -is [string] -and $_ -notmatch '^mysql: \[Warning\]' }) -join "`n").Trim()
if ([string]::IsNullOrWhiteSpace($text) -or $text -eq 'NULL') { return '[]' }
return $text
}
finally {
[Environment]::SetEnvironmentVariable($envName, $previous)
Remove-Item -LiteralPath $sqlFile -ErrorAction SilentlyContinue
}
}
function Add-SiteRows($rows) {
if ($rows -is [System.Collections.IDictionary]) { $rows = @($rows) }
foreach ($row in $rows) {
$sites.Add([pscustomobject]@{
SiteId = [long]$row['SiteId']
Title = [string]$row['Title']
ReferenceType = [string]$row['ReferenceType']
SiteSettings = $row['SiteSettings']
RecordCount = [long]$row['RecordCount']
})
}
}
if ($PSCmdlet.ParameterSetName -eq 'Server' -and $Dbms -eq 'SQLServer') {
try { $builder = [System.Data.SqlClient.SqlConnectionStringBuilder]::new() }
catch { throw 'System.Data.SqlClient is required but unavailable in this PowerShell installation.' }
$builder['Data Source'] = if ($Port) { "$Server,$Port" } else { $Server }
$builder['Initial Catalog'] = $Database
$builder['Encrypt'] = $true
$builder['TrustServerCertificate'] = [bool]$TrustServerCertificate
$builder['Connect Timeout'] = 15
$builder['Persist Security Info'] = $false
$builder['Application Name'] = 'Get-PleasanterIndexAdvice'
$builder['Integrated Security'] = $null -eq $Credential
$connection = $null
$password = $null
try {
$connection = [System.Data.SqlClient.SqlConnection]::new($builder.ConnectionString)
if ($null -ne $Credential) {
$password = $Credential.Password.Copy()
$password.MakeReadOnly()
$connection.Credential = [System.Data.SqlClient.SqlCredential]::new($Credential.UserName, $password)
}
try { $connection.Open() }
catch { throw 'SQL Server connection failed. Check connectivity, authentication, and the server certificate trust/name; connection details are not logged.' }
$command = $connection.CreateCommand()
$command.CommandTimeout = 120
[void]$command.Parameters.Add('@schema', [System.Data.SqlDbType]::NVarChar, 128)
$command.Parameters['@schema'].Value = $Schema
$command.CommandText = @'
SELECT s.SiteId, s.Title, s.ReferenceType, s.SiteSettings,
COALESCE(r.Cnt, i.Cnt, 0) AS RecordCount
FROM [Sites] AS s
LEFT JOIN (SELECT SiteId, COUNT_BIG(*) AS Cnt FROM [Results] GROUP BY SiteId) AS r
ON r.SiteId = s.SiteId AND s.ReferenceType = N'Results'
LEFT JOIN (SELECT SiteId, COUNT_BIG(*) AS Cnt FROM [Issues] GROUP BY SiteId) AS i
ON i.SiteId = s.SiteId AND s.ReferenceType = N'Issues'
WHERE s.ReferenceType IN (N'Results', N'Issues');
'@.Replace('[Sites]', "[$Schema].[Sites]").Replace('[Results]', "[$Schema].[Results]").Replace('[Issues]', "[$Schema].[Issues]")
$reader = $command.ExecuteReader()
try {
while ($reader.Read()) {
$sites.Add([pscustomobject]@{
SiteId = $reader.GetInt64(0)
Title = if ($reader.IsDBNull(1)) { '' } else { $reader.GetString(1) }
ReferenceType = $reader.GetString(2)
SiteSettings = if ($reader.IsDBNull(3)) { '{}' } else { $reader.GetString(3) }
RecordCount = [long]$reader.GetValue(4)
})
}
}
finally { $reader.Dispose() }
$command.CommandText = @'
SELECT t.name AS TableName, i.name AS IndexName,
STRING_AGG(CAST(c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END AS nvarchar(max)), ', ')
WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyText
FROM sys.indexes AS i
JOIN sys.tables AS t ON t.object_id = i.object_id
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE t.name IN (N'Results', N'Issues', N'Items') AND SCHEMA_NAME(t.schema_id) = @schema
GROUP BY t.name, i.name;
'@
$reader = $command.ExecuteReader()
try {
while ($reader.Read()) { Add-ExistingIndex $reader.GetString(0) $reader.GetString(1) $reader.GetString(2) }
}
finally { $reader.Dispose() }
}
finally {
if ($null -ne $connection) { $connection.Dispose() }
if ($null -ne $password) { $password.Dispose() }
}
}
elseif ($PSCmdlet.ParameterSetName -eq 'Server') {
$schemaLiteral = $Schema.Replace("'", "''").Replace('"', '""')
Add-SiteRows ((Invoke-DbClient ($exportSql[$Dbms].Replace('{0}', $schemaLiteral))) | ConvertFrom-Json -AsHashtable)
foreach ($row in @((Invoke-DbClient ($indexSql[$Dbms].Replace('{0}', $schemaLiteral))) | ConvertFrom-Json -AsHashtable)) {
if ($null -eq $row) { continue }
Add-ExistingIndex ([string]$row['TableName']) ([string]$row['IndexName']) ([string]$row['KeyText']) ([bool]$row['IsValid'])
}
}
else {
Add-SiteRows ((Get-Content -LiteralPath $SitesJsonPath -Raw -Encoding utf8) | ConvertFrom-Json -AsHashtable)
}
foreach ($site in $sites) {
$settings = $site.SiteSettings
if ($settings -is [string]) {
try { $settings = $settings | ConvertFrom-Json -AsHashtable }
catch { Write-Warning "SiteId $($site.SiteId): SiteSettings could not be parsed; skipped."; $settings = $null }
}
$site.SiteSettings = $settings
}
$rdsNote = 'Rds.json の DisableIndexChangeDetection を true にしておく（false だと索引の差分でテーブルが作り直され、追加した索引が消える）。'
if ($RdsJsonPath) {
$rds = Get-Content -LiteralPath $RdsJsonPath -Raw -Encoding utf8 | ConvertFrom-Json -AsHashtable
if ($rds['DisableIndexChangeDetection'] -eq $true) { $rdsNote = 'Rds.json の DisableIndexChangeDetection は true（確認済み）。' }
else {
$rdsNote = '注意: Rds.json の DisableIndexChangeDetection が true ではない。索引を作る前に "DisableIndexChangeDetection": true を設定する。'
Write-Warning 'Rds.json: DisableIndexChangeDetection is not true. CodeDefiner will rebuild the tables and drop the added indexes. Set it to true before creating the indexes.'
}
}
# ---------------------------------------------------------------------------
# Analysis
# ---------------------------------------------------------------------------
$largeSites = @($sites | Where-Object { $null -ne $_.SiteSettings -and $_.RecordCount -ge $MinRecords })
$recordCounts = @{}
foreach ($site in $sites) { $recordCounts[$site.SiteId] = $site.RecordCount }
foreach ($site in $largeSites) {
$ss = $site.SiteSettings
$table = $site.ReferenceType
$linkColumns = Get-LinkSiteIds $ss
# Default sort: the clustered PK (SiteId, UpdatedTime DESC, Id ASC) of SQL Server / MySQL
# cannot be read in the order UpdatedTime DESC, Id DESC, so the whole site is sorted.
if ($Dbms -in @('SQLServer', 'MySQL')) {
Add-Proposal $table (@((New-Key 'SiteId')) + (Get-TieBreakers $table)) '既定の並べ替え（UpdatedTime DESC, ID DESC）' "$($site.SiteId):$($site.Title)"
}
$views = @(ConvertTo-Array (Get-Prop $ss 'Views'))
foreach ($view in $views) {
$label = "ビュー $((Get-Prop $view 'Id')):$((Get-Prop $view 'Name'))"
Get-ViewIndex $site $view $label $linkColumns
}
# Child-direction columns shown in the grid
$gridSources = @(@{ Label = "$($site.SiteId):$($site.Title) / 一覧"; Columns = (Get-Prop $ss 'GridColumns') })
foreach ($view in $views) { $gridSources += @{ Label = "$($site.SiteId):$($site.Title) / ビュー $((Get-Prop $view 'Id'))"; Columns = (Get-Prop $view 'GridColumns') } }
foreach ($g in $gridSources) {
foreach ($gridColumn in (ConvertTo-Array $g.Columns)) { if ([string]$gridColumn -match '~~') { Add-ChildJoin $site ([string]$gridColumn) $g.Label } }
}
# Summaries defined on this (source) site: WHERE SiteId = @src AND LinkColumn IN (...) GROUP BY LinkColumn
foreach ($summary in (ConvertTo-Array (Get-Prop $ss 'Summaries'))) {
$linkColumn = Get-Prop $summary 'LinkColumn'
if ($linkColumn -and (Get-ColumnMeta $table $linkColumn)) {
Add-Proposal $table @((New-Key 'SiteId'), (New-Key $linkColumn 'ASC' (Get-ColumnMeta $table $linkColumn))) "サマリの集計（リンク項目 $linkColumn でグループ化）" "$($site.SiteId):$($site.Title) / サマリ $((Get-Prop $summary 'Id'))"
}
}
# Link columns: filtering children by the parent ID (CopyWithLinks, link filters)
foreach ($linkColumn in $linkColumns.Keys) {
if (Get-ColumnMeta $table $linkColumn) {
Add-Proposal $table @((New-Key 'SiteId'), (New-Key $linkColumn 'ASC' (Get-ColumnMeta $table $linkColumn))) "リンク項目 $linkColumn（親レコードでの絞り込み・リンク付きコピー）" "$($site.SiteId):$($site.Title)"
}
}
if ($IncludeFilterColumns) {
$filterColumns = [System.Collections.Generic.HashSet[string]]::new()
foreach ($name in (ConvertTo-Array (Get-Prop $ss 'FilterColumns'))) { [void]$filterColumns.Add($name) }
foreach ($view in $views) { foreach ($name in (ConvertTo-Array (Get-Prop $view 'FilterColumns'))) { [void]$filterColumns.Add($name) } }
foreach ($name in $filterColumns) {
$use = Get-FilterUse $site @{} $name ''
if ($null -eq $use -or $use.Use -ne 'Equality' -or $use.Meta.ContainsKey('OnItems')) { continue }
Add-Proposal $table (@((New-Key 'SiteId'), (New-Key $name 'ASC' $use.Meta)) + (Get-TieBreakers $table)) "フィルタ欄の項目 $name" "$($site.SiteId):$($site.Title) / フィルタ欄"
}
}
}
# Link choices: SELECT TOP 500 ... FROM Items WHERE SiteId = @target ORDER BY Title
foreach ($site in $sites | Where-Object { $null -ne $_.SiteSettings }) {
$links = Get-LinkSiteIds $site.SiteSettings
foreach ($columnName in $links.Keys) {
foreach ($targetId in $links[$columnName]) {
if ($recordCounts.Contains($targetId) -and $recordCounts[$targetId] -ge $MinRecords) {
Add-ItemsTitle 'リンク項目の選択肢（リンク先を Title 順に 500 件読む）' "$($site.SiteId):$($site.Title) → $targetId"
}
}
}
}
# ---------------------------------------------------------------------------
# Covering / existing checks and output
# ---------------------------------------------------------------------------
function Test-IsPrefix([string]$shorter, [string]$longer) {
return $longer -eq $shorter -or $longer.StartsWith($shorter + ', ')
}
$list = @($proposals.Values)
$results = [System.Collections.Generic.List[object]]::new()
foreach ($p in $list) {
$coveredBy = $list | Where-Object { $_ -ne $p -and $_.Table -eq $p.Table -and (Test-IsPrefix $p.Keys $_.Keys) } | Select-Object -First 1
if ($coveredBy) {
foreach ($r in $p.Reasons) { if (-not $coveredBy.Reasons.Contains($r)) { $coveredBy.Reasons.Add($r) } }
foreach ($s in $p.Sources) { if (-not $coveredBy.Sources.Contains($s)) { $coveredBy.Sources.Add($s) } }
continue
}
$results.Add($p)
}
function Get-KeyBytes($key) {
if ($Dbms -eq 'MySQL' -and $key.Meta -and $key.Meta.Kind -eq 'String' -and -not $key.Expression) { return $MySqlPrefixLength * 4 }
if ($key.Meta -and $key.Meta.ContainsKey('Bytes')) { return $key.Meta.Bytes }
return 8
}
function Get-DdlColumn($key) {
switch ($Dbms) {
'SQLServer' { return "[$($key.Column)] $($key.Dir)" }
'PostgreSQL' {
if ($key.Expression -like '*pattern_ops') { return $key.Expression }
if ($key.Expression) { return "($($key.Expression)) $($key.Dir)" }
return """$($key.Column)"" $($key.Dir)"
}
'MySQL' {
if ($key.Expression) { return "($($key.Expression)) $($key.Dir)" }
if ($key.Meta -and $key.Meta.Kind -eq 'String') { return "``$($key.Column)``($MySqlPrefixLength) $($key.Dir)" }
return "``$($key.Column)`` $($key.Dir)"
}
}
}
function Get-Statements([string]$table, [string]$name, [object[]]$keyList, [bool]$recreate) {
$cols = ($keyList | ForEach-Object { Get-DdlColumn $_ }) -join ', '
$functional = [bool]($keyList | Where-Object { $_.Functional })
switch ($Dbms) {
'SQLServer' {
$target = "[$Schema].[$table]"
$create = "CREATE NONCLUSTERED INDEX [$name] ON $target ($cols)"
if ($Offline) {
$statement = "IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'$target') AND name = N'$name')`n $create;"
}
else {
# EngineEdition 3 = Enterprise / Developer / Evaluation, 5 = Azure SQL Database, 8 = Azure SQL Managed Instance.
# ONLINE = ON is rejected when the batch is compiled on other editions, so it is run through EXEC.
$createLiteral = $create.Replace("'", "''")
$statement = @(
"IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'$target') AND name = N'$name')"
'BEGIN'
" IF CAST(SERVERPROPERTY('EngineEdition') AS int) IN (3, 5, 8)"
" EXEC (N'$createLiteral WITH (ONLINE = ON)');"
' ELSE'
' BEGIN'
" PRINT N'ONLINE = ON is not available in this edition; building $name offline (writes to $table wait until it finishes).';"
" EXEC (N'$createLiteral');"
' END'
'END;'
) -join "`n"
}
return @{ Create = $statement; Drop = "-- DROP INDEX [$name] ON $target;" }
}
'PostgreSQL' {
$concurrently = if ($Offline) { '' } else { ' CONCURRENTLY' }
$create = "CREATE INDEX$concurrently IF NOT EXISTS ""$name"" ON ""$Schema"".""$table"" ($cols);"
if ($recreate) { $create = "DROP INDEX$concurrently IF EXISTS ""$Schema"".""$name"";`n$create" }
return @{ Create = $create; Drop = "-- DROP INDEX$concurrently ""$Schema"".""$name"";" }
}
'MySQL' {
$options = if ($Offline) { '' } elseif ($functional) { ', ALGORITHM=INPLACE, LOCK=SHARED' } else { ', ALGORITHM=INPLACE, LOCK=NONE' }
$alter = "ALTER TABLE ``$table`` ADD INDEX ``$name`` ($cols)$options"
$statement = @(
"SET @ixc_ddl = IF((SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '$table' AND INDEX_NAME = '$name') = 0,"
" '$($alter.Replace("'", "''"))', 'DO 0');"
'PREPARE ixc_stmt FROM @ixc_ddl; EXECUTE ixc_stmt; DEALLOCATE PREPARE ixc_stmt;'
) -join "`n"
return @{ Create = $statement; Drop = "-- ALTER TABLE ``$table`` DROP INDEX ``$name``;" }
}
}
}
$ddl = [System.Collections.Generic.List[string]]::new()
$output = [System.Collections.Generic.List[object]]::new()
foreach ($p in $results) {
$readable = (($p.KeyList | Where-Object { $_.Column -notin @('SiteId', 'UpdatedTime', 'ResultId', 'IssueId') -or $_.Expression } | ForEach-Object Column) -join '_')
if (-not $readable) { $readable = 'Sort' }
$hash = [System.Security.Cryptography.SHA256]::HashData([System.Text.Encoding]::UTF8.GetBytes("$($p.Table)|$($p.Keys)"))
$suffix = ([System.Convert]::ToHexString($hash)).Substring(0, 8).ToLowerInvariant()
$prefix = "ixc_$($p.Table)_$readable"
if ($prefix.Length -gt 50) { $prefix = $prefix.Substring(0, 50) }
$name = "${prefix}_$suffix"
$existing = $null
if ($existingIndexes.Contains($p.Table)) {
$existing = $existingIndexes[$p.Table] | Where-Object { $_.Name -eq $name } | Select-Object -First 1
if (-not $existing) { $existing = $existingIndexes[$p.Table] | Where-Object { $_.IsValid -and (Test-IsPrefix $p.Keys $_.Keys) } | Select-Object -First 1 }
}
$status = if (-not $existing) { 'Proposed' } elseif ($existing.IsValid) { 'Existing' } else { 'Invalid' }
$keyBytes = ($p.KeyList | ForEach-Object { Get-KeyBytes $_ } | Measure-Object -Sum).Sum
$notes = [System.Collections.Generic.List[string]]::new()
if ($Dbms -eq 'SQLServer' -and $keyBytes -gt 1700) { $notes.Add("宣言上のキー長 $keyBytes バイトが上限 1700 バイトを超える。値の長いレコードは登録・更新に失敗する") }
if ($Dbms -eq 'PostgreSQL' -and ($p.KeyList | Where-Object { $_.Meta -and $_.Meta.Kind -eq 'String' -and -not ($_.Expression -like 'CASE*') })) { $notes.Add('B-tree の 1 行は約 2,700 バイトまで。長い値があると登録・更新に失敗する') }
if ($Dbms -eq 'MySQL' -and ($p.KeyList | Where-Object { $_.Meta -and $_.Meta.Kind -eq 'String' })) { $notes.Add("TEXT 列は先頭 $MySqlPrefixLength 文字だけを索引にする（並べ替えには使えない）") }
if ($Dbms -eq 'MySQL' -and -not $Offline -and ($p.KeyList | Where-Object { $_.Functional })) { $notes.Add('関数索引は LOCK=SHARED で作る（作成中は書き込みが待たされる）') }
if ($status -eq 'Invalid') { $notes.Add('同名の索引が INVALID（CONCURRENTLY の作成に失敗した残り）。削除して作り直す') }
$sql = Get-Statements $p.Table $name $p.KeyList ($status -eq 'Invalid')
$output.Add([pscustomobject]@{
Status = $status
Table = $p.Table
Keys = $p.Keys
Name = if ($existing) { $existing.Name } else { $name }
KeyBytes = $keyBytes
Reason = $p.Reasons -join ' / '
Sources = $p.Sources -join ' / '
Notes = $notes -join ' / '
Sql = if ($status -eq 'Existing') { $null } else { $sql.Create }
})
if ($status -ne 'Existing') {
$ddl.Add("-- $($p.Table): $($p.Reasons -join ' / ')")
$ddl.Add("-- 対象: $($p.Sources -join ' / ')")
foreach ($n in $notes) { $ddl.Add("-- 注意: $n") }
$ddl.Add($sql.Create)
$ddl.Add($sql.Drop)
$ddl.Add('')
}
}
foreach ($n in $notIndexable) {
$output.Add([pscustomobject]@{
Status = $n.Status; Table = $n.Table; Keys = $null; Name = $null; KeyBytes = $null
Reason = $n.Why; Sources = $n.Source; Notes = $null; Sql = $null
})
}
if ($OutputSqlPath) {
$onlineNote = switch ($Dbms) {
'SQLServer' { if ($Offline) { '-- 索引の作成中はテーブルへの書き込みが待たされる（-Offline）。' } else { '-- ONLINE = ON で作る（Enterprise / Developer / Azure SQL）。それ以外のエディションはオフラインで作り、作成中は書き込みが待たされる。' } }
'PostgreSQL' { if ($Offline) { '-- 索引の作成中はテーブルへの書き込みが待たされる（-Offline）。' } else { '-- CONCURRENTLY で作る。トランザクションの外で（psql -f などで 1 文ずつ）実行する。失敗すると INVALID の索引が残るので、もう一度このスクリプトで確かめる。' } }
'MySQL' { if ($Offline) { '-- 索引の作成中はテーブルへの書き込みが待たされる（-Offline）。' } else { '-- ALGORITHM=INPLACE, LOCK=NONE で作る（関数索引だけ LOCK=SHARED）。Pleasanter の DB に接続して実行する。' } }
}
$header = @(
"-- Get-PleasanterIndexAdvice ($Dbms) $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"
"-- 対象サイト: レコード数 $MinRecords 件以上 $($largeSites.Count) サイト"
"-- $rdsNote"
'-- CodeDefiner がテーブルを作り直すと、ここで作った索引は消える。CodeDefiner の実行後にもう一度流す。'
$onlineNote
''
)
Set-Content -LiteralPath $OutputSqlPath -Value ($header + $ddl) -Encoding utf8
}
$output関連ページ
- 内部で動く SQL 文 — 一覧・編集画面の SQL の組み立て
- リンク先の列（チルダ構文）と JOIN の生成 —
ClassA~123,Titleの JOIN - 検索機能の内部実装 — 検索欄の SQL と全文検索の索引
- データベースのテーブル構成 —
Results・Items・_historyの関係 - 同時アクセスが遅くなる理由（セッション・Redis） — DB 以外の遅さ
- SQL Server の行サイズの見積もりと現状診断 — 拡張項目を増やすときの行サイズ
- DB アクセスの性能改善案 — 本体を改修する場合の案