はじめに
業務で本格的にSQLに携わりましたが、人から聞いただけの知識で理解が曖昧な内容があったため、自分で検証して理解を深めその備忘録として当記事を残します。
1. パーティション化
1-1.パーティション化とは
データのパーティション化とは、データの格納場所を分割する機能です。
これに伴い、データアクセス速度の向上につながります。
カレンダ年月という項目に対してパーティションを設定した例を説明します。
すべての期間のデータを保持した全体のテーブルを1つの箱としたとき、パーティションを設定することでカレンダ年月ごとの引き出しが作られます。
カレンダ年月を条件に絞り込みをしたときに、箱の中を全て見ていくわけではなく、対象の引き出しのみを開けてデータを取り出すイメージです。
上記の例で考えても、アクセス速度が向上することがわかると思います。
参考：https://qiita.com/fuk101/items/603ddb445070aebbfcfa
1-2.パーティション化したテーブルへのアクセス速度の検証
パーティション化による影響を調査するため、以下条件のクエリを実行する。
1．INSERT INTO [パーティションなし挿入先テーブル](項目～) SELECT (項目～) FROM [パーティションなし元テーブル]
2．INSERT INTO [パーティションあり挿入先テーブル](項目～) SELECT (項目～) FROM [パーティションあり元テーブル]
※パーティションは「カレンダ年月」の項目に設定
1．の実行計画
| StmtText | TotalSubtreeCost | TotalSubtreeCost(行程ごと) |
|---|---|---|
| INSERT INTO [パーティションなし挿入先テーブル] (項目～) SELECT (項目～) FROM [パーティションなし元テーブル] | 49 | 98 (INSERT) |
| |--Clustered Index Insert(Object: ([パーティションあり挿入先テーブル].[PKのインデックス]), SET (項目～)) | 49 | ↑ |
| |--Compute Scalar | 28 | 106 (SELECT) |
| |--Sort(ORDER BY:(項目～) | 28 | ↑ |
| |--Compute Scalar | 25 | ↑ |
| |--Clustered Index Scan(OBJECT:(Object: ([パーティションあり元テーブル].[PKのインデックス]), WHERE:([CALMONTH]の条件式) | 25 | ↑ |
2．の実行計画
| StmtText | TotalSubtreeCost | TotalSubtreeCost(行程ごと) |
|---|---|---|
| INSERT INTO [パーティションあり挿入先テーブル] (項目～) SELECT (項目～) FROM [パーティションあり元テーブル] | 6028 | 12056 (INSERT) |
| |--Clustered Index Insert(Object: ([パーティションあり挿入先テーブル].[PKのインデックス]), SET (項目～)) | 6028 | ↑ |
| |--Compute Scalar | 3 | 9 (SELECT) |
| |--Compute Scalar | 3 | ↑ |
| |--Clustered Index Scan(OBJECT:(Object: ([パーティションあり元テーブル].[PKのインデックス]), SEEK:([PtnId1003]>=RangePartitonNew([@CalMonthFrom],(0),(201110.),(201111.),(~パーティションが作成された他のカレンダ年月 ...)) | 3 | ↑ |
上記クエリの実行結果を比較し、パフォーマンスへの影響について確認します。
元テーブルへのSELECT句については、パーティション有のテーブルのほうがコストが小さいことがわかります。
どちらのクエリもClustered Index ScanでPKのインデックスを参照していますが、データの絞り込み方法がWHEREとSEEKで異なり、インデックス有の方はパーティション毎にデータを探索しており、パフォーマンスの向上につながっております。
逆に、挿入先テーブルへのINSERT句については、パーティションなしのテーブルのほうがコストが格段に小さいことがわかります。
1-3.パーティションの使い場所
INSERT・SELECTそれぞれの時間を計測しましたが、パーティションの有無によって大きな違いが生まれました。
もし、INSERT・SELECTそれぞれにリアルタイム性を持たせる必要がある場合は、本番想定のデータ量でクエリを実行した結果によって、パーティション化によるパフォーマンスへの影響が良いか悪いかを総合的に判断する必要があります。
ただし、INSERT処理は夜間ジョブなどで実施し、SELECTにのみリアルタイム性が必要な場合などには、パーティション化がパフォーマンスへ好影響をもたらす可能性が高いと感じました。
2. インデックスとパーティション化の違い（未検証）
表題の通りですが、インデックスを設定したとしてもパーティションを設定したとしても、設定した項目を条件にした時にアクセス速度が向上するという点は同じのため違いはあるのかということを疑問に思ってしまいました。
参考(インデックス)：https://use-the-index-luke.com/ja/sql/anatomy/the-leaf-nodes
しかし、現時点では検証できませんでした。。。
パーティション化ありのテーブルとインデックスありのテーブルで比較しましたが、私の環境ではテストデータの性質上インデックスを参照してデータを取得できなかったため、検証用の専用データを作成して来期に持ち越して検証予定です。
おわりに
今回、初めて自分で実行計画を見ながらパフォーマンスを確認しましたが、想像以上にチューニングが難しかったです。
基本的は、テーブルへの参照やWHERE句の条件などが影響することが多いと思っていましたが、SELECTで指定している列やデータ量も関係しているようで、想像していた結果が返ってきませんでした。
今回の期間の検証では十分な成果が得られなかったので、検証用のデータベースを構築して来期にリベンジしたいです。