実行計画を読んで遅いクエリを改善する

目標

実行計画を読み、インデックスの有無・カラム順序・関数条件・カバリングの有無が計画をどのように変えるかを自分で確認しながら、N+1を結合で改善し、チューニングレポートを残せるようになります。

なぜ重要なのか

実行計画を読むことは、ノード名を眺めることではなく、オプティマイザーの予測と実際の結果のずれを探すことです。そしてcostは時間ではなく相対スコアなので、「costが10000を超えたら危険だ」のような基準には根拠がありません。もう1つ重要な姿勢があります。フルスキャンが常に悪いわけではありません。ほとんどの行を読む必要があるクエリではスキャンが最善で、「スキャンが見えるからインデックスを作ろう」という反射的な反応は、インデックスを増やすだけで、書き込みを遅くします。このラボは、その判断を計画を見ながら自分でやってみるものです。

ステップ

1. /root/db/tune.dbを作成し、/opt/lab/fixtures/dbo/gen.sqlを実行してSALESテーブルを作成してください。200,000行である必要があります。 カラム: SALE_ID、CUST_ID、SALE_DT(文字列YYYYMMDD)、PROD_CD、AMT
2. 次のクエリ(Q1)の実行計画を/root/db/plan1.txtに保存してください。

   SELECT SALE_ID, SALE_DT, AMT FROM SALES
    WHERE CUST_ID = 'C000123' AND SALE_DT BETWEEN '20260101' AND '20260630';
   

   計画にSCANが出てくる必要があります。 (インデックスを作る前に取得する必要があります。作ったあとに取得すると計画が変わります。)
3. Q1のためのインデックスIX_SALES_01を作成し、同じクエリの計画を/root/db/plan2.txtに保存してください。計画にIX_SALES_01が出てくる必要があります。
4. インデックスIX_SALES_BADをカラム順序を逆にして作成し、/root/db/composite.txtを作成してください。3行です。

   good=<Q1 에 적합한 인덱스의 컬럼 순서, 쉼표 구분>
   bad=<부적합한 순서>
   reason=<한 줄 근거>
   

   (山括弧の中の韓国語はプレースホルダーで、順に、Q1に適したインデックスのカラム順序(カンマ区切り)、不適切な順序、1行の根拠です。)
5. 次のクエリ(Q2)を、インデックスが使える形に書き直して/root/db/rewrite.sqlに保存してください。

   SELECT COUNT(*) FROM SALES WHERE substr(SALE_DT,1,6) = '202603';
   

   書き直したクエリの結果が元と同じで、実行計画を/root/db/plan3.txtに保存したときにインデックスを使っている必要があります。 (必要なら、インデックスを追加で作成してください。)
6. 次のクエリ(Q3)がカバリングインデックスだけで処理されるようにしてください。

   SELECT SALE_DT, AMT FROM SALES WHERE CUST_ID = 'C000123';
   

   実行計画を/root/db/covering.txtに保存し、計画にCOVERING INDEXが出てくる必要があります。
7. /opt/lab/fixtures/dbo/gen.sqlが一緒に作成したCUSTOMER_Mテーブルを使って、「顧客別の総売上上位10人(顧客名を含む)」を求めるクエリを/root/db/join.sqlに書き、結果を/root/db/join-result.txtに保存してください。 結果はCUST_ID,CUST_NM,TOTALの3カラムで、TOTALの降順の10行です。 クエリは結合1回で書く必要があります(サブクエリの繰り返しは禁止)。
8. /root/db/tuning.mdを作成してください。 ## 대상、## 증상、## 원인、## 조치、## 결과(韓国語の見出しで、順に対象、症状、原因、措置、結果を意味します)の5つのh2見出しが必要で、本文に次の3点が含まれている必要があります。
   • ステップ2とステップ3の計画の違い(SCAN → インデックス)
   • ANALYZEが行うことと、移行直後になぜ必要か
   • インデックスがINSERTを遅くする理由

参考

• 実行計画: EXPLAIN QUERY PLAN <쿼리>;(プレースホルダーはクエリです)
• 統計の更新: ANALYZE;
• 時間の測定: .timer on
• よくあるミス1: インデックスを作ってANALYZEをしなかったため、計画が変わらないことです。
• よくあるミス2: 書き直したクエリの結果が元と変わってしまうことです。 substr(SALE_DT,1,6)='202603'は3月1日から3月31日までです。
• よくあるミス3: カバリングを作ると言って、インデックスにカラムをすべて入れることです。 インデックスがテーブルと同じ大きさになると、利点が消えます。

大容量テーブルを作成する

/root/db/tune.dbを作成し、/opt/lab/fixtures/dbo/gen.sqlを実行してSALESテーブルを作成してください。200,000行である必要があります。 カラム: SALE_ID、CUST_ID、SALE_DT(文字列YYYYMMDD)、PROD_CD、AMT

再帰CTEで大量の行を作れます。作成後に件数を確認してください。

インデックスなしの実行計画

次のクエリ(Q1)の実行計画を/root/db/plan1.txtに保存してください。

SELECT SALE_ID, SALE_DT, AMT FROM SALES
 WHERE CUST_ID = 'C000123' AND SALE_DT BETWEEN '20260101' AND '20260630';

計画にSCANが出てくる必要があります。 (インデックスを作る前に取得する必要があります。作ったあとに取得すると計画が変わります。)

チューニングの出発点は、改善前の状態を記録しておくことです。計画にどんな単語が出てくるかに注目してください。

インデックス作成後の比較

Q1のためのインデックスIX_SALES_01を作成し、同じクエリの計画を/root/db/plan2.txtに保存してください。計画にIX_SALES_01が出てくる必要があります。

同じクエリの計画がどう変わるかを見ます。インデックス名が計画に出てくるかを確認してください。

複合インデックスのカラム順序

インデックスIX_SALES_BADをカラム順序を逆にして作成し、/root/db/composite.txtを作成してください。3行です。

good=<Q1 에 적합한 인덱스의 컬럼 순서, 쉼표 구분>
bad=<부적합한 순서>
reason=<한 줄 근거>

(山括弧の中の韓国語はプレースホルダーで、順に、Q1に適したインデックスのカラム順序(カンマ区切り)、不適切な順序、1行の根拠です。)

等価条件のカラムが前、範囲条件のカラムが後ろです。2つの順序をどちらも作って計画を比べると、違いがはっきりします。

関数条件を取り除く

次のクエリ(Q2)を、インデックスが使える形に書き直して/root/db/rewrite.sqlに保存してください。

SELECT COUNT(*) FROM SALES WHERE substr(SALE_DT,1,6) = '202603';

書き直したクエリの結果が元と同じで、実行計画を/root/db/plan3.txtに保存したときにインデックスを使っている必要があります。 (必要なら、インデックスを追加で作成してください。)

カラムに関数を適用すると、インデックスの並び順を使えません。同じ結果を返す範囲条件に変えてみてください。

カバリングインデックス

次のクエリ(Q3)がカバリングインデックスだけで処理されるようにしてください。

SELECT SALE_DT, AMT FROM SALES WHERE CUST_ID = 'C000123';

実行計画を/root/db/covering.txtに保存し、計画にCOVERING INDEXが出てくる必要があります。

クエリが必要とするカラムがすべてインデックスにあれば、テーブルを読みません。計画に特定の単語が追加で出てきます。

N+1を結合に

/opt/lab/fixtures/dbo/gen.sqlが一緒に作成したCUSTOMER_Mテーブルを使って、「顧客別の総売上上位10人(顧客名を含む)」を求めるクエリを/root/db/join.sqlに書き、結果を/root/db/join-result.txtに保存してください。 結果はCUST_ID,CUST_NM,TOTALの3カラムで、TOTALの降順の10行です。 クエリは結合1回で書く必要があります(サブクエリの繰り返しは禁止)。

繰り返しの照会を1回の結合に変えます。結果セットが元と同じであって初めて、改善といえます。

チューニングレポート

/root/db/tuning.mdを作成してください。 ## 대상、## 증상、## 원인、## 조치、## 결과(韓国語の見出しで、順に対象、症状、原因、措置、結果を意味します)の5つのh2見出しが必要で、本文に次の3点が含まれている必要があります。

• ステップ2とステップ3の計画の違い(SCAN → インデックス)
• ANALYZEが行うことと、移行直後になぜ必要か
• インデックスがINSERTを遅くする理由

6か月後に誰かがこのインデックスを削除しようとするときに必要な文書です。症状・原因・措置・結果を、数値とともに残してください。