SQL Serverでテーブルパーティション化して実行計画を前後比較してみた

はじめに

僕が開発リーダーをしているプロダクトでは、大きいテーブルで4億レコードほど登録した状態でのオンライン処理(検索処理)や、

300万レコードを3~4時間以内に集計するバッチ処理を行います。

それにも関わらず、、、

ファイル設計など行わずに現在primary１ファイルに全部データが入っていたり、大きいテーブルのパーティショニング化の検討等もできないまま半年間運用してきました。

現状、インデックスの貼り方を工夫したり、オンラインの検索仕様をシンプルにすることででなんとか性能担保していますが、

今後取り扱うデータ量はまだまだ増えそうなのと、どれくらい増加するのか予測が難しいです。

そこで、データ量が増加した際の対策として実行計画を分析しながらいくつか検証してみました。

いくつか検証した中の１つとして、テーブルパーティショニング化の検証を行い、ある程度の改善が見込めたので結果をまとめました。

SQL Serverでテーブルパーティション化を行った前後比較結果

この記事ではパーティショニングテーブルとは？？メリットは？？などについての説明は省略しますが、

この辺りが参考になるので、興味のある方は参照ください。

＞＞SQL Server のデータパーティションについて

＞＞パーティション テーブルとパーティション インデックス

ここから本題ですが、

まず、今回はこんなイメージで数億規模のトランザクションテーブルをパーティション化しました。

上のようにパーティション化し、

このテーブルに同じクエリを実行した際の比較結果はこのようになりました。

パーティション化する前からクエリのチューニングをいろいろとした後なので、

クエリタイムだけ見るとそんなに改善されていないようにみえますが、、、そこは気にしないでください。

ちなみにチューニングしてSQLの実行計画を比較する際は上のようにSSMSの比較機能を使うのがオススメです。

クエリプランを開いて右クリック＞プラン表示の比較

SQL Serverでテーブルパーティション化を行った前後の比較結果からわかること

Index SeekオペレーションでのCPUの推定コスト、I/O推定コストが半分になったことから、

同じクエリを実行して同じIndexを利用して同じデータを特定するまでのデータアクセス経路が最適化されたことが分かります。

こんなイメージです。

元々１つの大きなB-Treeインデックスを辿ってデータを特定していたのを・・・

パーティション化されたB-Treeインデックスの該当パーティションを辿るだけになりました。

そのため、探索するツリーが分割されて深さが浅くなっているため探索のコストが減りました。

また、

最後のSELECTオペレーションでのサブツリーの推定コストが半分になったことから

Index Seekオペレーションだけでなくクエリ全体で見てもクエリ実行にかかるコストが半分になったことが分かります。

SQL Serverでテーブルパーティション化を行った前後の実行計画からわかること

実行計画のコストを見ると・・・

Index Seekのコストはクエリ全体で見たときに3%です。本来ならここをいくら最適化したところで性能改善にはならないです。。。。

(今回はパーティション化の検証が目的ですが、本来はこの実行計画を見たらコストの高いsortオペレーションを最適化すべきです。)

また、

Index Seekオペレーションの「述語」があります。

これは、Index Seekした後にIndex Scanしているということです。

こんなイメージ。

もしこれがIndex Seekだけで辿れるようにできるようになればさらに性能がさらに改善されるかもしれません。

ちょっと反省

ちなみに、、

これはプロダクトの画面仕様において性能観点での工夫が足りていなかったところですが、

このIndex Scanのkeyになっている項目は画面で検索対象の金額のレンジを設定するものです。

「0円から9999999円の取引データを検索対象に絞り込む。」みたいなイメージ

しかも、画面仕様として初期値で金額スパン0円から9999999円に設定されます

(つまり全金額スパンを検索対象とするだけなのにwhereに金額スパンを条件指定することになる)。

それによって、

ユーザは実際にスパンを絞り込む必要がなくても無意識にこの設定で検索していることがほとんど。。。

なので、初期値は金額スパン設定なしで、絞り込みたい時だけ入力させるような画面仕様にしておけば、

インデックスのkeyからこの項目はそもそも外せて、Index Seekの後にIndex Scanするようなこともなかったと反省しています。

前提：よく使われる検索条件だけクエリの性能を最適化させ、オンラインアプリに必要なインデックス作成を最低限にすることで、夜間大量データバッチ処理での登録更新処理性能を優先させる方針

まとめ

SQL Serverでパーティション化しても今回はクエリを爆速にすることはできませんでしたが、

今後システムを運用していってデータが溜まってきて今まで通りIndex Seekしても遅くなった場合でも以下対応で改善の余地があることが分かりました。

１点だけ注意点としては、

パーティション化することでパーティションを跨ぐような検索にはオーバーヘッドかかってくるので、

分割列の選定は

該当テーブル検索時、良く条件指定に利用され、なおかつパーティションを跨がないで済むような列を選定することに注意してください。

またパーティション化のメリットに軽く触れておくと、

テーブルロックやインデックスのリビルドのメンテナンスをパーティション単位でかけれるなどもあります。

大量データを保持するテーブルを作成する際に、この記事が参考になれば幸いです。

2022/03/21追記

現場で必要なデータベース周りの知識に関するまとめ記事を作ったので

是非そちらも参照ください！！

現場で必要なデータベースの基礎知識まとめ｜正規化、ACID特性、SQLチューニングなど