Hatena::ブログ(Diary)

SQLer 生島勘富 の日記 このページをアンテナに追加 RSSフィード Twitter

2011-08-09

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

 あまりに気になったので「山本大@クロノスの日記」にチャチャを入れてみる。

    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を受け取った回数
データブロックを読んだ回数
ソート回数
メモリー使用量×使用時間
データ転送量

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


 5つの要素を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ユーザで、シングルポイントのデータベースサーバが早くパンクするわけ。

 完全な同時実行なんて希ですが、処理が長ければ長いほど輻輳する可能性は高くなる。

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

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

 しかし、巧く作るのはハード上の理論値まで。

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

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

iad_otomamayiad_otomamay 2011/08/09 16:05 トラックバックありがとうございました。
おっしゃってることもっともですが、こちらもいろいろと書ききれないこともあったので補足したいと思います。

下手糞がいなければ、もっと大胆な策がとれたのは確かです。しかしながら5年、10年と使われるはずのサービスを、大勢が参画して構築している状況を考えると低レベルなエンジニアのむち無茶苦茶な実装を制限するポリシーは必要悪だと感じます。
また逃げるではなく分けるという意味を、役割分担と解釈させてもらうと、ずっとハイスキルな人間がアサインできるわけではなく、またプロジェクトの横展開に対応できないのでした。そういう意味で、もっともスケールしないのはハイスキルなエンジニアかもしれません。

Join禁止については書くとややこしくなるので、詳細にはかいてませんが、アーキテクトに対して申請すればOKとしていました。これによりなにか起こしそうなSQLを全部管理する方法をとりました。

また実はJoinをやってほしくないもう一つの理由があって。(あんなことを書いておきながら)RDBを分散できるように設計したのでした。ビジネスドメインごとにスキーマを分けておいてユーザがあまりにも増加した場合にスキーマごと分割できるようにしていました。だから余計にJoin禁止なんです。あと1ユーザに紐づくデータが大半なので、非正規化の効果が高いDBでした。原則Join禁止としても1クエリで取ってこれるデータが多かったです。

そして、ソートはもちろん慎重に扱うのは前提で、それももちろん理解しつつなんですが、しかしながら、集計関数も遅さを招きます。とはいえ、それには条件があって「数値ではない項目を集計関数に入れると遅い」です。CHAR項目をSUMして+するなどすると、暗黙の型変換によって非常に重くなるのでした。だから固定長DBという前提で言えば集計関数を使うことは遅いといえました。

DELETE、INSERTでの対策をフレームワークで吸収する案は、そののち実装しましたがアーカイブログ領域の増大化という別問題を引き起こしたことを補足しておきます。

SikushimaSikushima 2011/08/09 17:12 コメントありがとうございます。

長くなるので、次のエントリーで。

おおふじおおふじ 2011/08/10 17:34 お久しぶりです。あれから1年程になりますね。
いずれにせよお元気そうで何よりです。

JOIN禁止についてですが、
http://el.jibun.atmarkit.co.jp/g1sys/2010/05/post-2d1b.html
のコラムで議論したときは『SQLはオブジェクト指向言語の数十倍の効率』といわれてその例としてJOINを出されたかと思います。
ここのJOIN禁止の記事は私が自身のコラム(http://www.ohfuji.name/?m=201005)で指摘した内容そのものですが、そのことについて少しでも私に謝辞なんぞをいただけますとうれしかったりします。

どうも余計な話でお騒がせいたしました。

SikushimaSikushima 2011/08/11 00:46 おおふじさん、どうも。

ブログ拝見しましたが、元記事にもあって、RDBMSにも搭載されている
オプティマイザの機能がどこにも実装されておらず、人間がやっている
様に思えるのですが?

私の思い違いかも知れません。
あなたのすばらしい実装のどこで実行前にハッシュジョインが
良いのか、ネスティッドループが良いのか判断しているのでしょう。

優秀な方でしょうから、ついうっかりの実装漏れでしょうか。
是非、実装し直してからパフォーマンスを測って、公開して下さい。

それでも、もしSQLより速いモノが作れるなら私もRDBMSを
二度と使いません。

出来ないなら文盲と判断しますので、二度と掛かってこないで。
時間の無駄です。

おおふじおおふじ 2011/08/11 09:29 残念ですね。

本当のエンジニアというのは、少なくとも技術面では真摯であるべきです。
貴方が、過去に
『SQLはオブジェクト指向言語の数十倍の効率』
という考えを持っていて、それに反する事実を見たとき、それを素直に受け入れるのがエンジニアというものです。
(例えばxerenさんのようにです)。

技術的な論争を行ったとき、その内容は素人には判断できないので、適当に言いがかりを付ければ議論が成立しているように見えるでしょう。ただそれは本来やってはいけないことであると思いますが?

>それでも、もしSQLより速いモノが作れるなら私もRDBMSを二度と使いません。
私はこれからもRDBMSを使います。道具は適材適所で使えばよいのです。なぜこのような見苦しい言いがかりをいうのでしょうか?

>出来ないなら文盲と判断しますので、二度と掛かってこないで。時間の無駄です。

こういう話をされるということは、
『SQLはオブジェクト指向言語の数十倍の効率』
は間違いであったとお認めになったと受け取ります。

おお、追加で記事を書かれていますね。

記事の内容的には面倒くさいのでコメントしませんが、
『SQLはオブジェクト指向言語の数十倍の効率』
という話は出てきていないようですので、これについては不正確であるとお認めになったと受け取ります。

こちらでのコメントはこれで最後にします。

SikushimaSikushima 2011/08/11 09:37 文盲のサル相手に何を書いても意味がないけれど、あなたのブログにある1は
あなたがハッシュジョインを選択するように狙って作ったデータを受けて、
オプティマイザがハッシュジョインを選択している。

SQLはオプティマイザが実行計画を作るところからがプログラムで、
同じモノは書けないって何度も書いている。

私の質問に答えろ。
オマエのクソコードのどこにヒストグラムを測ってアクセスパスを決定して
いる部分があるのか?


SQLは全体処理の最適化のために大きなオーバーヘッドを書けることになる。
差が付く処理を探してきて、速い部分だけ切り出しても全く意味がない。

サルに何を言っても意味がないので、次に来ても消します。