SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

JOIN禁止と固定長カラムについて

SQL中級・上級 システム設計

　あまりに気になったので「山本大＠クロノスの日記」にチャチャを入れてみる。
　　　　http://d.hatena.ne.jp/iad_otomamay/20110808/1312805917
　　　　http://d.hatena.ne.jp/iad_otomamay/20100906/1283786846

　まあ、政治的にはどのみち勝てなかったでしょう。私も同じ条件であれば、一応は説得を試みるけれど返り討ちに遭うと思う。いずれにしても結果は同じですが、教えられたと思っているなら間違いです。

　結論はいつものとおり「下手糞が居るから」に行き着くのですが……。

JOIN禁止について

　「JOIN禁止」が正しい場合がある。
　それはJOINされるデータがアプリケーションサーバにキャッシュされていて、その一貫性が何らかの形で保証されている場合。
　そもそも、せっかくキャッシュしているのに、それを使わずにJOINして取り直す行為は馬鹿げている。キャッシュを使うのであれば、JOIN禁止は当たり前で誰も異論はないだろう。

　それ以外は、JOINを禁止すれば逆にDBサーバの負荷を高めることになる。

　例えば、受注明細に商品マスタを連結し商品名を付けて返すとする。（オプティマイザが必ず同じ処理を選択するとは限らないが）


　データベースエンジンは

　　　　受注明細を抽出しメモリー上に展開する。
　　　　商品コードと一致する商品データを抽出する。
　　　　受注明細に商品名を付けてクライアントに返す。

　アプリケーションサーバでJOINと同じ処理をすると


　データベースエンジンは

　　　　受注明細を抽出しメモリー上に展開する。
　　　　受注明細データをアプリケーションサーバに返す。
　　　　（全部返しきれなかったらメモリー上に維持する）
　　　　以下を繰り返す。
　　　　　　商品を抽出するSQLを受け取る。
　　　　　　商品データを抽出する。
　　　　　　商品データをアプリケーションサーバに返す。
　　　　繰り返し終わり。

　データベースサーバへの負荷は、もちろん、ボトルネックになる場所は違うが、

SQLを受け取った回数
データブロックを読んだ回数
ソート回数
メモリー使用量×使用時間
データ転送量

　上の５つの要素でほぼ決まる。


　５つの要素をJOINした場合と、しない場合を比べると、それぞれ、

SQLを受け取った回数 → JOINしない方が多い
データブロックを読んだ回数 → 同じ
ソート回数 → 同じ
メモリー使用量×使用時間 → JOINしない方が多い
データ転送量 → JOINしない方が通信のオーバーヘッド分多い

　JOINを避けてサーバにとって負荷が減っている要素は何一つない。
　処理のピークは低くなっているけれど、JOINしない方が処理が遅く、データベースサーバへの負荷を掛けていることになる。

　ただし、「下手糞がいる」というときは違う。インデックスを外したJOINをされると予想がつかない結果が起きてしまう。

集計関数が遅いというのはウソ

　集計関数が遅いというのはウソ！集計する前のソートが遅いのです。
　これが分かってない人が、非常に、非常に、非常に多い。

　SQLが分からなくても、Javaなどが分かっていれば体感的に分かっていると思うが、ループの中にいくつか変数を入れ、四則演算を増やしても処理時間の差を体感することはできないが、ループの元になる配列（やコレクションなど）をソートし直したら、件数によって体感できるぐらい遅いでしょう。

　SQLで確認するなら、SUMだけと、AVGのあるときと、パフォーマンスが変わるか試してみれば分かる。

　　　　SELECT SUM(xxxx) --, AVG(xxxx)
　　　　FROM TABLE
　　　　GROUP BY xxx

　「集計関数が遅いと勘違いして」集計関数を禁止しながら、ORDER BYを禁止してないとして、JOINの場合と同じく負荷を考えると

SQLを受け取った回数 → 同じ
データブロックを読んだ回数 → 同じ
ソート回数 → 同じ
メモリー使用量×使用時間 → 集計関数を使わない方が多い
データ転送量 → 集計関数を使わない方が多い

　つまり、ORDER BYを使っていたら、集計関数を使わない方がデータベースの負荷は高い。集計関数を禁止するなら、ORDER BYを禁止しないと意味がないわけだが、ORDER BY禁止は、私の経験では見たことがない（笑）。むしろ、共通仕様で「ORDER BYを必ず付けること」の方が多いんじゃないか？

　ただし、「下手糞がいる」というときは違う。
　頭を抱えるようなサブクエリーの中でGROUP BYをされていたら、予想がつかない結果が起きてしまう。

固定長カラムについて

　固定長カラムを使うメリットは、行移行（行連鎖）を避けるだけしかない。

　それは、本来的には PCTFREE や PCTUSED を使ってデータブロックの空きを作っておくことで自動調整が可能ですが、これをテーブル毎に一々調整をしてられないため、固定長の方が良いと考える人が居るかも知れない。

　しかし、細密充填をした方が処理毎のシステム全体のデータブロックの読み取り回数が少なくなるからデータベースサーバに対する負荷は少ない。

　特に、OracleのNumber型のものを文字で持つと約倍の保存領域が必要になって、処理のたびに型変換が必要になるため、極力避けるべきでしょう。

　行移行がそれほどまでにイヤなら、UPDATEを禁止して、DELETE、INSERT にする方が効率的でこれはフレームワーク側で吸収できる。

　数十分の夜間バッチなどが可能であれば、アナライズして行移行（行連鎖）が起きている行だけ削除してインサートし直す手もある。

　http://otndnld.oracle.co.jp/skillup/oracle9i/6_1/index.html の第四章を参照。

　また、アナライズは全レコードが調査対象となるため巨大テーブルでは時間が掛かりすぎるということであれば、タイムスタンプ列を利用して、前回、修正した時間より後にUPDATEされたレコードに対して、一旦、ワークに待避させ、DELETE、INSERTをすればいい。

　PCTFREE を僅かばかり大きくしておけば、行移行は、余程、頻繁に更新があるシステム以外ほとんど起きない。特にレコード数が大きな巨大データベースでは、マスターのUPDATEはあっても、トランザクションでは、INSERTが多くUPDATEを頻繁に行うことは希なので、どうしても気になるなら、アナライズや、DELETE、INSERTを利用して、遅延で修正すれば良いだろう。

　余談ですが、固定列でつくって PCTFREE が 0％じゃなく、デフォルト値のままなんてのもなくはない。

データベースサーバの負荷は、ピークではなく時間の掛け算で考える

　例えば、JOINを使ったときのリソース使用率が100％で0.1秒、JOINを使わないとき、20％で1秒といった感じになる。

　たまたま、5人が同時に同じ処理をリクエストしたとしましょう。
　　　　JOINを使ったときは、0.1〜0.5秒で終わる。
　　　　JOINを使わないときは、全員がほぼ1秒で終わる。← これを重要視しがち

　しかし、10人が同時に同じ処理をリクエストしたとしましょう。
　　　　JOINを使ったときは、0.1〜1.0秒で終わる。
　　　　JOINを使わないときは、1〜2秒で終わる。← 要件を満たせない≒パンク

　データベースサーバでJOINで処理していれば、1秒間に対応できるのは10ユーザ、JOINしていなければ5ユーザで、シングルポイントのデータベースサーバが早くパンクするわけ。
　完全な同時実行なんて希ですが、処理が長ければ長いほど輻輳する可能性は高くなる。

　結論としては、アプリケーションサーバでソートを肩代わりするか、マスターなどをキャッシュする以外は、下手糞がいなければデータベースサーバで処理した方がデータベースサーバの負荷は減る。

　巨大だからとか、見当違いも甚だしく全く関係ない。

　しかし、巧く作るのはハード上の理論値まで。
　下手糞は無限に下手糞に作れる、つまり、最悪できないこともあるので、まあ、無限大のリスクを避けようとするのはあながち間違っているとは言えないけれど、プロが言うなよ。

　何度もいってるが、全く逆のスキルだからこそ、逃げるのではなく分けるべきなのだけれど。