SQL最適化と実行計画の確認（SQL Server・PostgreSQL・MySQL）
プリザンターのSQLを最適化するときは、遅い画面から対象SQLを特定し、同じSQL・パラメータ・データで実行計画と実測値を取ってから変更します。索引を増やすだけでは、件数取得、リンク先の検索、型変換、統計情報のずれなどを見落とします。
このページは、SQLを取得するところから、計画の読み方、改善候補、改善前後の比較までをつなぐ調査手順です。プリザンター固有の構造は1.5.8.1のソースと元記事、DBMSの計測方法はMicrosoft Learn・PostgreSQL 17・MySQL 8.4の公式文書で確認しています。取得・計測のSQLは説明用のSELECTです。後半の統計更新・索引作成はDBを変更する操作として示しています。実際のプリザンターのSQLを取得したら、そのSQL全体と実際のパラメータで調べてください。
図を読み込み中…
1. 画面の遅さをSQL単位に分ける
まず、遅い操作のサイト、保存ビュー、フィルタ、並べ替え、ページ番号、利用者の権限を記録します。一覧を開く処理でも、ページ本体の取得、件数、集計、リンク選択肢の取得が別々に動きます。ページのSELECTだけが速くなっても、件数や選択肢の取得が支配的なら画面全体は速くなりません。
プリザンターのレコードは、サイトごとに別の物理テーブルを作る方式ではなく、Results・Issuesなどを共有してSiteIdで区別します。タイトル表示にはItemsのJOIN、リンク項目にはリンク先のJOINや数値変換が入ることがあります。実際のSQLと計測例はDBが遅くなる理由とインデックス設計、組み立ての実装は内部で動くSQL文にあります。
| 記録するもの | 調査で使う理由 |
|---|---|
| SQL全文とパラメータの値・型 | 同じ列でも型変換や値の偏りで処理が変わるため |
| サイト・利用者・ビュー・ページ番号 | 権限条件、検索条件、並べ替え、ページ送りを再現するため |
| DBMSの版・索引・統計の状態 | 計画を決める前提を揃えるため |
| 1回の時間・実行回数・合計時間 | 1回が遅いSQLと、頻繁に動くSQLを区別するため |
| 画面全体の時間 | SQL以外の処理や、別のSQLが残っていないか確認するため |
2. 発行SQLを取得する
開発環境ではSQLデバッガーを使う
SQLデバッガーはApp_Data/Logs/CommandTextForDebugging_1.sql〜3.sqlへSQLを出力します。WriteSqlLogにはConditional("DEBUG")が付いており、通常のReleaseビルドでは呼び出されません。設定ファイルを変えるだけで有効になるログではありません（SqlDebugs.cs#L15-L28）。詳しい使い方はOperations ToolsとSQLデバッガーを参照してください。
出力時の既定値は、SQL Serverでは変数宣言と値の設定、PostgreSQL・MySQLではSQL本文への値の置換です（SqlDebugs.cs#L49-L65）。文字列の置換を含むため、コピーしたログを実行する前に、引用符・NULL・日付・パラメータ型を確認します。SQL Serverの変数宣言による再実行と、アプリケーションのパラメータ付き実行では、計画が一致するとは限りません。
MySQL接続では、本体がセッションのsql_modeをansi_quotes,pipes_as_concatに設定します（MySqlCommandText.cs#L11-L14）。DBクライアントで生成SQLを再現するときも、二重引用符と||の解釈を揃えます。このページのMySQL例は、識別子をバッククォートで囲んでいます。
Release環境ではDB側の実行統計から対象を選ぶ
既に収集されている統計から、時間と回数の大きいSQLを探せます。以下はいずれも読み取りです。参照権限と統計収集の設定が必要で、統計の開始・リセット時刻にも注意します。集約されたSQLだけでは実際のパラメータが分からないことがあるので、操作時刻やアプリ側の記録と照合します。
SQL Server：キャッシュに残るSQLの実行統計
SELECT TOP (20)
qs.execution_count,
qs.total_elapsed_time / 1000.0 AS total_elapsed_ms,
qs.total_elapsed_time / NULLIF(qs.execution_count, 0) / 1000.0 AS avg_elapsed_ms,
qs.total_logical_reads,
SUBSTRING(st.text,
qs.statement_start_offset / 2 + 1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1
) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
WHERE pa.attribute = 'dbid' AND pa.value = CONVERT(sql_variant, DB_ID())
ORDER BY qs.total_elapsed_time DESC;対象DBへ接続して実行します。計画のdbidで絞っています（sys.dm_exec_plan_attributes）。値は計画がキャッシュにある間の集計です。再起動やキャッシュからの削除後も維持される履歴ではありません。単位と権限の要件はsys.dm_exec_query_statsで確認します。
PostgreSQL：pg_stat_statementsが有効な環境
SELECT queryid, calls, total_exec_time, mean_exec_time,
rows, shared_blks_hit, shared_blks_read, query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;これは拡張が有効な環境で使う例です。有効化にはshared_preload_librariesなどの準備が必要です。時間はミリ秒で、集約されたSQLのパラメータ値はこの一覧から復元できません（pg_stat_statements）。
MySQL：Performance SchemaのSQLダイジェスト
SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR,
SUM_TIMER_WAIT / 1000000000000.0 AS total_seconds,
AVG_TIMER_WAIT / 1000000000000.0 AS avg_seconds,
SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;対象DBを選択してから実行します。ダイジェスト集約が有効であることが前提です。時間はピコ秒から秒へ換算しています。平均時間に加え、検査した行数と返した行数を比べ、少量の結果のために大量の行を調べるSQLを探します（Statement Summary Tables、Event Timing）。
3. 推定計画と実際の計画を使い分ける
| DBMS | 実行せず計画を確認 | 実行して行数・時間も確認 |
|---|---|---|
| SQL Server | SSMSの「推定実行プランの表示」 | 「実際の実行プランを含める」とSET STATISTICS IO/TIME |
| PostgreSQL | EXPLAIN | EXPLAIN (ANALYZE, BUFFERS) |
| MySQL | EXPLAIN・EXPLAIN FORMAT=JSON | EXPLAIN ANALYZE FORMAT=TREE |
推定計画は、オプティマイザーが見積もった処理です。実際の計画では、見積もりと実際の行数、繰り返し回数などを比較できます。実測を伴う方法は対象SQLを実行します。このページの例はSELECTに限定しています。取得したログにUPDATE・DELETEなどが含まれていたら、バッチ全体をそのまま実行しないでください。SELECTでも計測自体の負荷は発生するため、重い処理は検証用DBで確認します（SQL Serverの実行計画、PostgreSQL EXPLAIN、MySQL EXPLAIN）。
SQL Server：実際の計画とIO・時間を一緒に取る
SSMSで対象DBへ接続し、「クエリ」から**「実際の実行プランを含める」**を有効にして実行します。実行計画タブとメッセージタブの両方を保存します（実際の実行プランの表示）。
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXEC sys.sp_executesql
N'SELECT ResultId, Title, UpdatedTime
FROM dbo.Results
WHERE SiteId = @SiteId AND Status = @Status
ORDER BY UpdatedTime DESC, ResultId ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;',
N'@SiteId bigint, @Status int',
@SiteId = 123, @Status = 100;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;これはパラメータ付きの説明用SQLです。実際のSQLを調べるときは、JOIN・アクセス制御・並べ替えを削らず、元の型と値を使います。logical readsはキャッシュから読んだページ数で、返却件数ではありません。CPU時間と経過時間も記録します（STATISTICS IO、STATISTICS TIME）。
| 計画で見るもの | 次に確認すること |
|---|---|
| Estimated / Actual Number of Rows | 条件の偏りや統計の状態。実際に通る行数とのずれ |
| Seek Predicates / Predicate | 索引で絞る条件と、読んだ後に除外する条件 |
| Scan・Key Lookup・Sort | 何行に対して動き、どれだけ読み取りを増やすか |
| 実行回数・警告 | JOINの内側の反復、暗黙の型変換、ソート等のメモリ不足 |
演算子の意味はShowplanの演算子リファレンスで確認します。演算子の名前だけで採否を決めず、入出力の行数と実際の仕事量を合わせて読みます。
不足索引の提案は、既存索引との重複や更新負荷を確認するための候補として扱います。提案を全部作ることが最適化ではありません（不足索引の提案による調整）。
PostgreSQL：行数・loops・Buffersを読む
-- 推定計画
EXPLAIN
SELECT "ResultId", "Title", "UpdatedTime"
FROM "Results"
WHERE "SiteId" = 123 AND "Status" = 100
ORDER BY "UpdatedTime" DESC, "ResultId" ASC
LIMIT 20;
-- 実際に実行して計測
EXPLAIN (ANALYZE, BUFFERS)
SELECT "ResultId", "Title", "UpdatedTime"
FROM "Results"
WHERE "SiteId" = 123 AND "Status" = 100
ORDER BY "UpdatedTime" DESC, "ResultId" ASC
LIMIT 20;costはミリ秒ではなく、プランナーのコスト単位です。actual timeはミリ秒で、loopsが複数のノードは1回当たりの時間・行数が表示されます。反復を含む仕事量を見るにはrows × loopsも確認します。親ノードの時間やBuffersは子の処理を含むので、全ノードを単純に足して合計時間や読み取り量にはしません（Using EXPLAIN）。
Buffers: shared hitは共有バッファからの取得、shared readは共有バッファへ読み込んだブロックです。readだけで物理ディスクからの読み取りを断定せず、OSキャッシュの影響も考慮します。Rows Removed by Filterやソートのディスク使用が大きい場合は、読んで捨てている行や並べ替える件数を調べます（EXPLAINのオプション）。
MySQL：通常のEXPLAINと実測のTREEを分ける
-- 推定計画：key・rows・filtered・Extraを確認
EXPLAIN
SELECT `ResultId`, `Title`, `UpdatedTime`
FROM `Results`
WHERE `SiteId` = 123 AND `Status` = 100
ORDER BY `UpdatedTime` DESC, `ResultId` ASC
LIMIT 20;
-- 実際に実行して計測
EXPLAIN ANALYZE FORMAT=TREE
SELECT `ResultId`, `Title`, `UpdatedTime`
FROM `Results`
WHERE `SiteId` = 123 AND `Status` = 100
ORDER BY `UpdatedTime` DESC, `ResultId` ASC
LIMIT 20;EXPLAIN ANALYZEはMySQL 8.0.18で追加されました。この例は8.4の文書に合わせ、実測の出力形式をTREEと明示しています。通常のEXPLAINのrowsは推定値であり、実際に調べた件数ではありません。TREEのactual time・rows・loopsで実際の処理を読みます（8.0の導入版、8.4のEXPLAIN）。
| 表示 | 読み方 |
|---|---|
possible_keys / key | 候補になった索引 / 選択された索引 |
typeとrows・filtered | アクセス方法と、調べる行数・条件通過率の見積もり |
Using index | 索引だけで必要な情報を取得。索引を使用する全ケースの表示ではない |
Using filesort | 索引順だけでは完了しない並べ替え。表示だけでディスク使用を断定しない |
TREEのactual time・rows・loops | 最初の行と全行の取得時間、返す行数、実行回数。反復時は1回当たりの値も見る |
通常の出力はEXPLAIN Output Format、実測値の意味はEXPLAIN ANALYZEで確認できます。
4. 計画のどこから改善するか
Scan・Seq Scan・Table scanが存在することだけで失敗と判断しません。少ない行やテーブルの大部分を返す条件では、索引を経由するより全体を読む計画が選ばれることがあります。まず、実際に重いSQLのどの処理が行数・読み取り・反復を増やしているかを見ます（PostgreSQLの計画例、SQL Serverの索引設計）。
| 観測したこと | プリザンターで確認する箇所 | 改善を検討する方向 |
|---|---|---|
| 推定行数と実際の行数が大きく異なる | サイトごとの件数、条件値の偏り、大量データ投入後の統計 | まず統計の状態を確認し、同じ条件で再計測 |
| サイト内の多くの行を読んで少量を返す | 保存ビューのフィルタと、索引で絞れている条件 | 条件の意味を保ったSQLの書き方・追加索引 |
| 多くの行をSortして20件返す | ORDER BYの列・方向・型変換 | 絞り込みと並べ替えに合う索引、必要な表示順の見直し |
| JOINの内側が大量に反復する | リンク先、親子の向き、数値変換、相関した条件 | 内側で読む件数と索引を確認。サイト設計の見直しも検討 |
| 一覧のSELECTは速いがCOUNTが遅い | 同じフィルタの件数取得・集計 | SELECTとCOUNTを別々に計測し、双方に効く変更か確認 |
| SQLの時間が減っても画面は遅い | その他のSQL・セッション・サーバー処理 | 同時アクセスとセッションも調査 |
統計が古い場合
統計情報は行数の見積もりに使われます。大量投入後などで見積もりがずれ、統計の状態に根拠がある場合に更新を検討します。更新処理自体にも負荷があるので、一律に毎回実行する手順にはしません。
UPDATE STATISTICS dbo.Results;ANALYZE "Results";ANALYZE TABLE `Results`;実施条件と既定の管理方法はUPDATE STATISTICS、PostgreSQL ANALYZE、MySQLのEXPLAINを使った最適化を確認します。統計更新だけで必ず速くなるとはせず、更新後の計画と実測値で判断します。
SQLと索引を見直す場合
説明例の条件はSiteIdとStatusの等価比較、並べ替えはUpdatedTime DESC, ResultId ASCです。このSQL向けの候補として、等価条件の列の後へ並べ替えの列を置いた索引を検討できます。列順と有効な条件はSQL Serverの索引設計、PostgreSQLの複合索引、MySQLの複合索引を参照してください。
以下は検証用DBで試す候補です。既存索引で足りるか確認してから使います。プリザンター全体に共通の推奨索引ではなく、実際のSQLで効果を確認して採否を決めます。
CREATE INDEX IX_ManualTest_Results_Site_Status_Updated
ON dbo.Results (SiteId, Status, UpdatedTime DESC, ResultId ASC);CREATE INDEX "IX_ManualTest_Results_Site_Status_Updated"
ON "Results" ("SiteId", "Status", "UpdatedTime" DESC, "ResultId" ASC);CREATE INDEX `IX_ManualTest_Results_Site_Status_Updated`
ON `Results` (`SiteId`, `Status`, `UpdatedTime` DESC, `ResultId` ASC);実際の分類列を使う場合は、同じDDLの列名だけをClassAに置き換えてはいけません。分類列はSQL Serverでnvarchar(1024)、MySQLでtextなど、DBMSによって定義が異なります。キー長・prefix・変換式の条件を含めた設計はDBが遅くなる理由とインデックス設計にあります。部分一致を完全一致に変えるなど、検索結果の意味が変わる変更も採用しません。
拡張SQLでは、列への関数やCASTを伴う条件を、同じ意味を保てる範囲条件へ書き換えられるか検討します。標準画面では、発行SQLに合わせてフィルタ・表示列・並べ替え・リンク設定を調べます。元のJOINを削って速くなっただけのSQLを、標準画面の改善結果にはしません。
たとえばSQL Serverの拡張SQLで、DBに保存された日付の1日分を取る条件なら、列を日付へ変換する書き方と、列をそのまま比較する半開区間を比較できます。列への関数適用と索引設計の関係は索引設計ガイドで説明されています。
DECLARE @Day date = '2026-10-01';
-- 書き換え前
SELECT ResultId
FROM dbo.Results
WHERE SiteId = 123 AND CONVERT(date, UpdatedTime) = @Day;
-- 書き換え候補
SELECT ResultId
FROM dbo.Results
WHERE SiteId = 123
AND UpdatedTime >= @Day
AND UpdatedTime < DATEADD(day, 1, @Day);この例はDBの値の暦日を比較するものです。利用者のタイムゾーンに変換した日付を探す処理では、元の意味に合う境界値を別途求めます。オプティマイザーが元の条件を処理できる場合もあるため、書き換えだけで速くなると断定せず、返却IDの一致・Seek条件・読み取り量を比較します。
5. IndexCreatorの計画とDBの実行計画を区別する
IndexCreatorのplanは、サイト設定と既存索引から計算した索引の作成・維持の計画です。DBがSQLをどう実行するかを示す実行計画ではありません。実行時間や実際に使われた索引を測った結果でもありません。
dotnet VehicleVision.PleasanterTools.IndexCreator.dll planまず候補と未対応条件を確認し、必要な候補を検証用DBで適用して、対象SQLの実際の計画を取り直します。ツールは任意の拡張SQLや一時的な画面条件を解析するものではありません。対象条件と適用・整理の手順はIndexCreatorによる索引の計画と運用にあります。
追加索引には保存時の更新負荷と容量が発生します。プリザンターのテーブル再構築時の扱い、手動索引とツール管理索引の管理方法も決めておきます（追加索引の運用、IndexCreatorの適用と整理）。
6. 改善前後を同じ条件で比較する
一度に変える点を絞り、同じデータ・パラメータ・権限・ページ番号で測ります。キャッシュや同時負荷の条件を記録し、複数回の値を保存します。計測のために本番全体のキャッシュを消す必要はありません。
| 比較項目 | 改善前 | 改善後 |
|---|---|---|
| SQL全文・パラメータ・DBMSの版 | 記録 | 同じ条件で比較 |
| 返却件数・レコードID・並び順 | 保存 | 一致することを確認 |
| 経過時間・取得できるCPU時間・実行回数 | 計測 | 同じ条件で再計測 |
| 読み取り量 | SQL Serverのlogical reads、PostgreSQLのBuffersなど | 同じDBMSの同じ指標で比較 |
| 推定行数と実際の行数・loops | 計画を保存 | 計画を保存 |
| COUNT・リンク選択肢・他のビュー | 計測 | 悪化していないか確認 |
| 保存・一括更新の時間と索引容量 | 記録 | 追加索引の負担を確認 |
| 画面全体の応答時間 | 計測 | 利用者の操作が改善したか確認 |
SQL Serverのページ数、PostgreSQLのブロック数、MySQLの行数は、そのまま横並びの性能比較には使いません。同じDBMS・同じ条件の変更前後で評価します。計測時の出力、変更したDDL・サイト設定、戻す方法を残し、効果が確認できた変更だけを運用へ反映します。