はじめに
私はプロジェクトでシステムテスト、場合によっては本番稼働で起きる性能問題についてSQLチューニングを行ってきました。皆さんの中で、SQLチューニングは「専門的な知識」が必要とか「高レベルなスキル」を要求されると思っている方が居るかも？ っと思い、このネタを扱いました。
確かにそういった内容もある事はあります。
ただ、多く（体感８割くらい）の性能問題については本当に単純なもので解決できます。特に昨今のFutureでは脱Oracleを掲げており、安価なDB製品を使う事による性能問題は如実に出てきています。そのため開発者一人一人が意識して性能問題を駆逐することにより、生産性・品質・コストに貢献できると考えこのお題にしました。
テーブル設計の基本
なぜSQLの話でテーブル設計？
何故SQLの話でテーブル設計の話が出るかというと、テーブル設計でSQLの構成が決まるからです。そのため、SQLを考える前にまずテーブル設計を考えてください。
1POINT@これだけ意識して
「参照（SELECT）しやすい設計」
これが基本です。何故SELECTを単純にするかというと、「登録（INSERT）・更新（UPDATE）は性能問題が発生する事が非常に少ない」ためです。あと付加的な利点として、「登録・更新機能（画面が特に）は複雑なのでスキルが高い人が割り当てられる」といった事もあり、開発者のスキル別に担当を割り当て易いといったこともあります。
それにSQLの複雑性が登録・更新に寄ったとしても難度が劇的に上がる事はそうそうなく、参照の方が難度上がりやすいです。
画面設計の例では
- 単票画面 ＝ 1レコード
- 一覧画面の1行 ＝ 1レコード
です。結合先のテーブルを含めてです。
実際にこう言った単純設計すると、こういった単純な形にならない事（特に一覧で）がありますが、単純な形にするために設計頑張って下さい。
コーディング・テストといったフェーズの工数が削減されます。
SQLの基本
テーブル設計で完結できる？
以下SQLの考慮点を書いてみたのですが、上記テーブル設計を完璧にこなした場合SQLは単純になり、ここで語る考慮点は不要になります。ただ、データ正規化や業務要件による抽出・表示情報の複雑性により、そう簡単にはいかないのがシステムです。
そのため、上記テーブル設計をした上で対応できない領域に対してSQLをどう構築するかを考える必要があります。
1POINT@これだけ意識して
「件数の少ないテーブルを駆動にする」
探索する場合駆動件数が増えると計算量が多くなり、SQLの実行結果取得に時間がかかります。
これは良く言われていますし、意識している人も多いと思いますが以下で考慮漏れが多いです。
- 結合で件数が大きく膨れる
- 検索画面等の動的に抽出条件が変更される場合に網羅されていない。
上記1のケースですが、結合した際に件数が増えた場合に最後に集約や絞り込みをかけるのではなく、クエリの途中で件数が膨らんだ直後に絞り込みを行いましょう。
この辺りの組み換えやインラインビュー化はデータ量を考慮する必要があるので、設計者が一番詳しいはずなので設計者が対応してほしい所です。
上記2のケースですが、基本は最も時間がかかる条件において最短で終わるように結合順を考えましょう。
最も時間がかかる条件というのが必須検索項目のみでの検索ですので、性能面で問題とならないよう業務要件を満たす最も件数が絞れる検索条件を必須項目となる設計をすることが重要になってきます。このあたりを考慮して設計しないと後で性能問題となることが多いです。
終わりに
過去のこれは…と思った例は以下です。
とあるPJの帳票一覧画面の例
- 問題 ：一覧の1行に紐付くテーブルが存在しない
- 詳細 ：１行が複数の異なる帳票を束ねており、複数のテーブルのレコード有無とステータスを判定して、
行として表示するかと表示内容を決定している。 - 解決策：各テーブルの状態をサマリした状態管理用のテーブルを用意して、
帳票レコード作成・更新時に合わせて状態管理用のテーブルを更新する。
とあるPJの発注数初期値の例
- 問題 ：発注数の初期値は各属性毎に設定可能で優先度も決まっている。
優先度は「商品＞サブクラス＞クラス＞ライン＞部門」と言った順になっており、
優先度の高いレコードが無ければ次の優先度といった形で決定される。
ただ、商品の属性の組み換えは行われるので各属性値のみを結合条件としている。
上位の属性に値が入っていれば結合は１回で済むが属性値が１つのみしか設定されていないので、
複数の結合条件で抽出しなければならない。 - 解決策：全てのカラムに値を入れる。商品の属性が変わったらそのタイミングで洗い替える。
ここまで極端な例はそうそうないですが、、、
宣伝（社内の人向け）
社内の人だけが見れる社内動画サービスに動画をUpしています。
まだの人は是非ご覧ください！