目次
はじめに
僕が開発リーダーをしているプロダクトでは、大きいテーブルで4億レコードほど登録した状態でのオンライン処理(検索処理)や、
300万レコードを3~4時間以内に集計するバッチ処理を行います。
それにも関わらず、、、
ファイル設計など行わずに現在primary1ファイルに全部データが入っていたり、大きいテーブルのパーティショニング化の検討等もできないまま半年間運用してきました。
現状、インデックスの貼り方を工夫したり、オンラインの検索仕様をシンプルにすることででなんとか性能担保していますが、
今後取り扱うデータ量はまだまだ増えそうなのと、どれくらい増加するのか予測が難しいです。
そこで、データ量が増加した際の対策として実行計画を分析しながらいくつか検証してみました。
いくつか検証した中の1つとして、テーブルパーティショニング化の検証を行い、ある程度の改善が見込めたので結果をまとめました。
SQL Serverでテーブルパーティション化を行った前後比較結果
この記事ではパーティショニングテーブルとは??メリットは??などについての説明は省略しますが、
この辺りが参考になるので、興味のある方は参照ください。
ここから本題ですが、
まず、今回はこんなイメージで数億規模のトランザクションテーブルをパーティション化しました。
上のようにパーティション化し、
このテーブルに同じクエリを実行した際の比較結果はこのようになりました。
項目名 | パーティション化前 | パーティション化後 |
CPUの推定コスト | 2.33 | 1.15 |
I/Oの推定コスト | 189.1 | 93.8 |
サブツリーの推定コスト | 6217.0 | 3087.4 |
クエリタイム | 7s | 3s |
パーティション化する前からクエリのチューニングをいろいろとした後なので、
クエリタイムだけ見るとそんなに改善されていないようにみえますが、、、そこは気にしないでください。
本来なら、数秒しか改善されないのに既存テーブルをパーティション化し、
移行の労力の割に合わないようなことはしたくないし、すべきではありません。
あくまで今回は、システムを数年間運用して性能劣化することを想定した検証の位置づけで、実際この段階で本番適用する予定はありません。
ちなみにチューニングしてSQLの実行計画を比較する際は上のようにSSMSの比較機能を使うのがオススメです。
クエリプランを開いて右クリック>プラン表示の比較
SQL Serverでテーブルパーティション化を行った前後の比較結果からわかること
Index SeekオペレーションでのCPUの推定コスト、I/O推定コストが半分になったことから、
同じクエリを実行して同じIndexを利用して同じデータを特定するまでのデータアクセス経路が最適化されたことが分かります。
こんなイメージです。
元々1つの大きな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しても遅くなった場合でも以下対応で改善の余地があることが分かりました。
- テーブルをパーティション化することでIndex Seekにかかるコストを半分くらいまで減らせる。
- 画面仕様を工夫(金額スパン常時指定外す)することで性能改善ができそう。
1点だけ注意点としては、
パーティション化することでパーティションを跨ぐような検索にはオーバーヘッドかかってくるので、
分割列の選定は
該当テーブル検索時、良く条件指定に利用され、なおかつパーティションを跨がないで済むような列を選定することに注意してください。
またパーティション化のメリットに軽く触れておくと、
テーブルロックやインデックスのリビルドのメンテナンスをパーティション単位でかけれるなどもあります。
大量データを保持するテーブルを作成する際に、この記事が参考になれば幸いです。
2022/03/21追記
現場で必要なデータベース周りの知識に関するまとめ記事を作ったので
是非そちらも参照ください!!
