Taste of Tech Topics

Acroquest Technology株式会社のエンジニアが書く技術ブログ

お餅に飽きたのでDBやってみた

はじめまして。oogiです。

お正月も一週間以上過ぎました。
そろそろお餅を食べるのも飽きてしまったので
ちょっとDBの勉強をしてみました。

と言っても別段新しいことをやってみたわけではないので、
「何を今更…」と思う人もいるかもしれませんが
良ければお付き合いください。

DBの性能問題

さて、今私が携わっているプロジェクトではDBにOracle
使用していますが、規模も大きくて扱うデータも多くて複雑なため、
テーブル数がかなり多くなってしまっています。

そのため、ちょっと検索しようと思ったときに結合する必要のある
テーブルの数も多くなりがちです。

そうすると、気になるのが性能問題

当然、結合するテーブルが増えればSQLの実行にかかる時間も長くなってしまいます。

テーブルのレコード数が多いのでデータの処理自体にも時間がかかりますが、
テーブル数の増加に従ってオプティマイザがSQLを解析するのに時間が
かかってしまうのです。

SQLの解析

オプティマイザがSQLを解析して最適な実行計画を算出する際に、
結合するテーブルにどの順番でアクセスするか、全てのパターンに対して
解析を行い最適なものを算出します。

このときに解析するパターンの数は、結合するテーブルの数の階乗で表されます。

そうすると、テーブルの結合数に対するパターン数は次の表のようになります。


表1 結合パターン数

見ての通り、テーブル数が増えると飛躍的にパターン数が増えてしまいます。

そのため、結合パターン数の桁が増える5テーブル以上、
または7テーブル以上の結合を制限することが多いようです。

また、Oracle9iまでの場合は初期化パラメータ(デフォルト値2000)までの
パターン数しか解析しないため、7テーブル以上結合すると全パターンが
解析されず最適な実行計画が得られない場合もあるようです。

結合テーブル数を減らす

上記の通り、検索時に結合するテーブルの数が増えると実行にかかる時間が
飛躍的に増えてしまいます。

これを改善するために、あえて正規化を崩して結合すべきテーブル数を
減らすという工夫をすることがあります。

例えば、正規化された次のようなデータ構造のデータがあったとします。

図1 正規化されたデータ構造

このデータに対して、A情報テーブルのレコード単位で検索を行うとして、
取得する項目にはA1項目やA2項目の他に、C1項目やD1項目、G1項目が
含まれているとします。

このとき、検索SQLでは図1に示した7テーブルを結合しなければなりません。

そこで、以下のように非正規化してA情報テーブルにG1項目を持たせてみます。
※ここではA情報テーブルのレコードに関連付くG情報テーブルのレコードが
 1レコードだけであるものとします。


図2 非正規化されたデータ構造

これで検索時にG1項目をA情報テーブルから取得することによって
G情報テーブルを参照する必要がなくなったとすると、
検索SQLで結合するテーブルが6つに減らせることになります。

仮にE情報、F情報がG情報を結合するためだけに使用されていたとすると、
これらも結合する必要がなくなるため4テーブルですむことになります。

データの正規化はRDBMSを使用する際の基本ですが、非正規化によって
性能を改善できることもあるということですね。

注意点

非正規化によってDBの性能改善を図れる場合があることが分かりましたが、
非正規化すると当然同じデータが複数の箇所に存在することになります。
これらのデータが一致しているかどうかは十分な注意が必要です。

G情報テーブルのG1項目を更新したはずなのに検索すると変わっていない、
なんてことになってしまったら意味がありませんからね。

非正規化による性能改善について説明しましたが、データの整合性を
とる手間がかかることも考えると、非正規化は他の手を尽くした後の
最終手段であると考えるべきでしょう。

私の今のプロジェクトでも性能のために非正規化している所がありますが、
これ以上非正規化しなくてすむようにもっといろいろなチューニングの
テクニックを勉強したいと思います。

それでは。