前回記事
https://qiita.com/sw_ah/items/de584377d497aaf0c78d
の続きで、
- STEP2 実行計画の見方
- STEP3 効果的なインデックスの貼り方
の部分を簡単にまとめていきます。
STEP2. 実行計画の見方
STEP1では「SQLが遅くなる本質的な理由」を整理しました。
次におさえておきたいことは、実行計画で見るべきポイントです。
実行計画はどのような方法でテーブルにアクセスしようとしているのかを見える化してくれるので、実行したSQLのどこがボトルネックになっているかを把握し、どのようなアプローチでチューニングするかの指標が見えてきます。
実行計画とは何か？
実行計画とは、DBがSQLを実行する際に選択した
- アクセス方法（Table Scan / Index Scan など）
- 結合順序・結合方法
- フィルタやソートのタイミング
を実行ステップごとに可視化したものです。
基本的な見方は参考に置いたYOUTUBEを参考に勉強してください。
まず最初に見るべきポイント
① カーディナリティ(Rows)
まず見るべきポイントは、カーディナリティ(Rows)です。
Oracle公式リファレンスでは、カーディナリティを次のように定義しています。
カーディナリティは、実行計画で各操作によって戻される行数です。
なぜ重要か？
データベースのオプティマイザ（最適化機能）は、「何件返ってくるか」を予測し、最も効率的なアクセスパスや、結合方法を選択するからです。
想定よりRowsが多い場合は、
- 絞り込みが弱い
- 統計情報が古い
- 関数や型変換で条件が効いていない
という問題が存在していることになります。
同じ環境であれば、costを参考にもできるが絶対値ではないため、Rowsに注目してチューニングすることが正攻法です。
② 実行計画はツリー構造で理解する
実行計画は 表形式で出力されますが、実体はツリー構造（木構造）です。
上から下に向かって処理が進む形になっています。
SELECT STATEMENT (最上位ノード)
└─ JOIN / SCAN / SORT
├─ TABLE ACCESS
└─ TABLE ACCESS
ポイント：
上位ノードほど結果を“まとめる処理”
下位ノードほど“データの取得元”
これを押さえるだけで、計画全体の流れがつかみやすくなります。
③ 実行計画を見るべき本質ポイント
参考資料
では性能劣化の原因として注目すべき項目を明示してあります。
特に以下に着目します👇
① アクセス方法
- Full Table Scan
- Index Range Scan
- Index Unique Scan
- TABLE ACCESS BY INDEX ROWID
→ Index Range Scan が多いほど高速検索の期待値が高いです。
フルスキャンが多い場合、インデックスや条件式の見直しが必要と判断します。
② 結合方法
- Nested Loops
- Hash Join
- Merge Join
→ データ量や絞り込みの特性によって最適な結合方式が異なります。
例えば Nested Loops は少ない行数のテーブル同士で効果的ですが、
大規模テーブル x 大規模テーブル では Hash Join の方が良いことが多いです。
③ ソート / 集約処理
ソートや GROUP BY は 事前に絞られていないと重くなる処理です。
Top-N を取得する場合は、インデックス順でソートできるかを検討します。
具体例：実行計画のどこが問題なのかを読み解く
上記のポイントを
以下の実行計画（パフォーマンスが悪い）の具体例を見ながら解説していきます。
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 101K | 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
| 2 | VIEW | VW_TMP_1 | 59 | 1652 | 101K | 00:00:04 |
| 3 | HASH GROUP BY | | 59 | 11800 | 101K | 00:00:04 |
| 4 | NESTED LOOPS OUTER | | 59 | 11800 | 101K | 00:00:04 |
|* 5 | FILTER | | | | | |
|* 6 | HASH JOIN OUTER | | 18 | 3258 | 101K | 00:00:04 |
| 7 | JOIN FILTER CREATE | :BF0000 | 18 | 2304 | 50779 | 00:00:02 |
|* 8 | HASH JOIN | | 18 | 2304 | 50779 | 00:00:02 |
|* 9 | TABLE ACCESS BY INDEX ROWID | ORDER_HEADER | 1998 | 119K | 19522 | 00:00:01 |
|*10 | INDEX RANGE SCAN | IDX_ORDER_DATE_CATEGORY |18843 | | 2746 | 00:00:01 |
| 11 | NESTED LOOPS | |14822 | 969K | 31257 | 00:00:02 |
|*12 | TABLE ACCESS FULL | FORM_INFO |14822 | 202K | 1592 | 00:00:01 |
|*13 | INDEX UNIQUE SCAN | PK_FORM_INFO | 1 | | 1 | 00:00:01 |
|*14 | TABLE ACCESS BY INDEX ROWID | FORM_DETAIL | 1 | 53 | 2 | 00:00:01 |
| 15 | JOIN FILTER USE | :BF0000 |12261 | 634K | 50998 | 00:00:02 |
|*16 | TABLE ACCESS FULL | FORM_DETAIL |12261 | 634K | 50998 | 00:00:02 |
|*17 | TABLE ACCESS BY INDEX ROWID | DOCUMENT | 3 | 57 | 5 | 00:00:01 |
|*18 | INDEX RANGE SCAN | IDX_DOC_KEY | 3 | | 2 | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information:
----------------------
6 - filter(
TO_CHAR(REPORT_DATE, 'YYYY-MM-DD HH24:MI:SS')
> TO_CHAR(BASE_DATE, 'YYYY-MM-DD HH24:MI:SS')
)
10 - access(
ORDER_DATE >= :from_date
AND ORDER_DATE < :to_date
)
12 - filter(STATUS = 1)
16 - filter(SYS_OP_BLOOM_FILTER(:BF0000, KEY_COLUMN))
では、先ほどの実行計画を STEP2で押さえた観点 から見ていきます。
① Rows を上から順に追う
まず最上位の Rows に注目します。
SELECT STATEMENT Rows = 1
最終的に返したい結果は 1 行です。
しかし、その直下を見ていくと👇
INDEX RANGE SCAN Rows = 18843
TABLE ACCESS BY INDEX Rows = 1998
TABLE ACCESS FULL Rows = 14822
TABLE ACCESS FULL Rows = 12261
最終結果は1行なのに、途中で1万件以上のデータを何度も処理している
👉 この時点で、絞り込みが適切ではないと判断できます。
② TABLE ACCESS FULL が連鎖している
TABLE ACCESS FULL FORM_INFO Rows = 14822
TABLE ACCESS FULL FORM_DETAIL Rows = 12261
これらは結合の途中で フルスキャン されています。
この時点で DB は、
- 先に絞れない
- 件数が多い
- 仕方なく全件読む
という判断をしています。
特に、
- HASH JOIN
- NESTED LOOPS
- HASH GROUP BY
- SORT AGGREGATE
が連続している点から、
大量データを結合 → 集約 → ソート
という 最も重い処理パターン に入っていることが分かります。
③この実行計画から導ける改善方針
フルスキャンされているテーブルを疑う
- 本当に全件必要か？
- JOIN の順序は適切か？
- 事前に絞れる条件はないか？
STEP2のまとめ
- まずカーディナリティに注目する
- 重いアクセス方法や結合方法が採用されていないかを確認
- 数値よりも「なぜそうなったか」を考える
次のSTEPでは、
では、どうすれば意図した実行計画に誘導できるのか？
という視点で インデックス設計 に進みます。
STEP3. 効果的なインデックスの貼り方
SQLチューニングというと、
「とりあえずインデックスを貼る」ことを最初に思い浮かべがちです。
私は、やみくもにインデックスを作成して、とにかく実行計画を確認するという非効率的な作業をしていました。
効率的なインデックスを作る手法があったら知っておきたかった
という自責の念から以下を要点をまとめておきます。
インデックスを貼る前に考えるべきこと
① WHERE句で本当に使われているか
- 条件に関数がかかっていないか
- 型変換が発生していないか
- LIKE '%xxx%' になっていないか
貼っただけでは使われません。
SQLとセットで成立して初めて意味を持ちます。
インデックスはどのカラムに貼るのか
基本的には、WHERE/JOIN/ORDER BY が頻繁に使われるカラムに対して貼ると使用されます。
※ただし、主キー・外部キーには自動でインデックスが付与されます
② 単一インデックスか複合インデックスか
複数条件がある場合、
- 単一インデックスを複数貼る
- 複合インデックスを1つ貼る
のどちらが適切かを考えます。
基本的な考え方は以下です。
- 絞り込み件数が大きい列を先頭にする（より絞り込みができるカラムを先頭に）
- 等価条件（=）を優先
- 範囲条件（BETWEEN / < / >）は後ろに置く
③ 並び替え（ORDER BY）を意識する
ORDER BY に使われる列順とインデックスの列順が一致している場合、
- ソート処理が省略される
デフォルトは昇順ですが、高頻度で降順を使用する場合は降順索引を作成しておくとソート処理をスキップすることができ高速化する可能性があります。
検索＋並び替えはセットで考える のがコツです。
関数インデックスという選択肢
どうしても関数を使わざるを得ない場合、
- Function-Based Index（関数インデックス）
を検討します。
CREATE INDEX IDX_USERS_DATE
ON USERS (TO_CHAR(CREATE_DATE, 'YYYY-MM-DD'));
ただし、
- SQLが固定されやすい
- 保守コストが高い
というデメリットもあるため、
最後の手段 として考えるのが無難です。
インデックス設計でよくある失敗
- 使われないインデックスが増える
- 更新性能が劣化する
- どのSQL向けのインデックスかわからなくなる
インデックスは資産ではなく負債にもなり得る
STEP3のまとめ
- インデックスは「SQLありき」
- 実行計画で使われ方を確認する
- 貼る数より、意図を明確にする
全体まとめ
- 遅い理由を分類する（STEP1）
- 実行計画で事実を確認する（STEP2）
- 意図した動きになるよう設計する（STEP3）
この順番を守るだけで、
SQLチューニングは再現性のある作業になります。
参考