NoSQL と SQL について

かなり古いネタですが、DeNA の松信氏のブログから考えてみましょう。

www.publickey1.jp元記事はこちら。

yoshinorimatsunobu.blogspot.com

DeNA の松信氏がやった実験

以下のような SQL

"select user_name,..
from test.user where user_id=?"

 1Gbps の NIC を 4枚挿ししたサーバに対し1千万回投げて、1秒間に処理できた件数を測ったというもの。

結果は以下のようになっています。

f:id:Sikushima:20190419185229p:plain


意外に思う人が多いとは思いますが、SQL が一番 CPU の利用率が低いです。これは私たち DB屋さんの「CPUを使い切る方が難しいよね」という感覚が実験結果でも再現されていると言えます。CPUを使い切るとしたら、よっぽど下手くそな SQL を書くか、GIS など内部的な計算が異常に多くなる処理をしなければ 100%に張り付くなんてことは起きないのです。

一番遅いのはネットワーク

全てのデータがメモリ上にキャッシュされている(松信氏の実験)ことを前提とすると、一番遅いのはネットワークで、この実験の場合、理論値でも1Gbpsしか出ていません。

他の条件として、memcached も、SQLを使わないでデータにアクセスしていますが、Handler Socket は、MySQLAPI を利用して SQL を使わないでデータにアクセスしています。つまり、データにアクセスする部分については、SQLを使ったときと同じなのです。

そこから、結果をイメージ化すると図のようになります。

f:id:Sikushima:20190419190018p:plain

松信氏のブログから、リクエスト、レスポンスもネットワークの負荷が小さくなるように工夫したと書いてあるので、そのようにして、4倍差、7倍差をつけてみました。

memcashed のデータ処理の速度が MySQL と同じではないでしょうが、大差はないと仮定して作っています。いずれにしても、SQLのオーバーヘッドと、ネットワークの処理時間が大きくなければ、4倍(memcashed)7倍(Handler Socket)という速度差は成り立ちません。

SQLは滅茶苦茶重いが、速くもできる!

RDBSQL の概念)が発表されたのは1969年。50年前です。

ネットワークは今とは桁違いに遅い

端末に処理能力がない

という条件のもとに考え出された概念ですから、サーバで一括処理して端末に結果だけを送る。という前提で作られています。

つまり、

"select user_name,..
from test.user where user_id=?" 

というような SQL を「1千万回も送る」というような処理は、そもそも想定されていないのです。

それぞれを限界までチューニングするという風にして条件をそろえるなら、

"select user_name,..
from test.user where user_id between 1 AND 2500000" 

という SQL × 4(ポートの数)と、memcached や、Handler Socket を比べることになるでしょう。そうすれば、100% SQLの方が速いと断言できます。

処理にもよりますが、通常、数回~数十回の SQL を、1回の SQL まとめることができた時点で逆転します。

 f:id:Sikushima:20190419190303p:plain

ユーザアクションに対して SQL は極力1回にする

つまり、ユーザアクションに対して SQL は極力1回に抑えるべきです。

逆に、1回のユーザアクションに対して、1件のレコードしか処理しないという要件が多いときは、RDBMS を使うべきではないのです。

「1回のユーザアクションに対して、1件のレコードしか処理しない」というようなことが比較的多いシステム(例えばゲーム)のときは、Handler Socket や、NoSQL API のようなものを使い、複数のレコードを処理できるときは SQL を使うという併用が良いでしょう。

 

NoSQL を使うべきとき

SQLRDBMS)は完璧ではありませんから、NoSQLを選択すべきときは以下のようなシステムのときになります。

  • 極端に否定形のデータを扱う(ドキュメント型)
  • レコード単位の処理がない(列指向)
  • レコード単位の処理しかない(KVS)

レコード単位の処理も、複数のレコード単位の処理もある。というときは、Handler Socket や、NoSQL API のような SQL の解析をスキップできるものと、SQLの併用。

 

レコード単位の処理が少ないときは、SQL になるでしょう。

 

SQLと手続き型言語 - JOIN を手続き型で書いてみる

SQL手続き型言語は最終的には同じです。

今回は、JOIN を手続き型言語Javaっぽく)書いてみます。

オプティマイズについて

SQL を実行する前に手続き型のソースに変換されます。この処理をオプティマイズと言い、その処理を行う機能はオプティマイザと呼ばれています。

単純に SQL と手続き型に1対1で変換することをルールベースオプティマイズと言います。

SQL を変換するときテーブルのレコード数やインデックスを考慮し、どのインデックスを使うか、あるいは、何を外部表とするかまで統計情報を使って判断してから変換する方式を、コストベースオプティマイズと言います。

1990年代は、「将来、コストベースになります」というアナウンスはなされていましたが、ルールベースが主流でした。現在はコストベースが主流になっています。

もちろん、設定でルールベースに切り替えることはできますが、現在もルールベースで利用されているのは SQLite ぐらいじゃないかと思います。

2つの表をJOINしたSQL

以下の SQL を代表的な4つのアルゴリズムJavaっぽく書いています。

SELECT *

FROM 外部表

    INNER JOIN 内部表

        ON句の条件

WHERE

        外部表のWHERE句の条件

        AND 内部表のWHERE句の条件

;

単純なネスティッドループ(NESTED LOOP)

JOIN にインデックスを使えないときに選択されます。

HASH JOIN のアルゴリズムを持っている RDBMS であればこのアルゴリズムが選択されることはまずなく、ハッシュジョインが選択されるはずです。

HASH JOIN がない MySQL ではよく見られます。「MySQL は JOIN が遅い」と言われる理由でもある。

foreach ( row外部 : 外部表 ) {
    if ( ! 外部表のWHERE句の条件 ) { // ネストを減らすため否定形
        continue; // WHERE句の条件に合わなかったら飛ばす。
    }

    foreach ( row内部 : 内部表 ) {

        if ( ON句の条件内部表のWHERE句の条件 ) {

             result.Add(row外部, row内部);

        }

     }

}

インデックスを使ったネスティッドループ(NESTED LOOP)

オーソドックスな JOIN です。

外部表のWHERE句の条件」で十分に絞り込める。とオプティマイザが判断したときによく選択されます。

foreach( row外部 : 外部表 )

     if( ! 外部表のWHERE句の条件 ) {
         continue; // WHERE句の条件に合わなかったら飛ばす。
     }
     wrkIndexs = 内部表.IndexRangeScan(row外部.内部のキー)
     foreach ( wrkIndex : wrkIndexs ) {
         if ( インデックス以外のON句の条件 ) {
             resultRow = 内部表.GetRowByIndex(wrkIndex);
             if( resultRow を使ったWHEREの条件 ){
                 result.Add(row外部, resultRow );
             }
         }
     }
}

ハッシュジョイン(HASH JOIN)

内部表(マスタ類など)を一旦、メモリー上のハッシュテーブルに入れて処理する形です。「外部表のWHERE句の条件」で絞り込んだ結果が多くなると判断されたとき、選択されることが多いアルゴリズムです。

 

foreach( row内部 : 内部表){ // 内部表を全件読んでいる

    hash内部.put(row内部);

}

foreach(row外部 : 外部表){

    if ( ! 外部表のWHERE句の条件 ) { // ネストを減らすため否定形

        continue; // WHERE句の条件に合わなかったら飛ばす。

    }

    row内部s = hash内部.get(row外部.キー); // 1件とは限らない

    foreach(row内部 : row内部s){

        if( ON句の条件内部表のWHERE 条件 ){

            result.Add(row外部, row内部);

        }

    }

}

ソートマージジョイン(SORT MERGE JOIN)

HASH JOIN の方が速くなるため、現在では選択されることはほとんどありませんが、両方のキーにインデックスがあるとき、あるいは、FULL OUTER JOINなどのときに選択されることがあります。

外部表.Sort(キー);    // これが遅いからあまり選択されない

内部表.Sort(キー);    // これが遅いからあまり選択されない

i = 0;

j = 0;

 

while ( i < 外部表.Count ){

    if ( ! 外部表のWHERE句の条件 ) { // ネストを減らすため否定形

        continue; // WHERE句の条件に合わなかったら飛ばす。

    }

    while ( j < 内部表.Count ){

        if ( 外部表(i).キー =  内部表(j).キー ) {

            if ( 外部表のWHERE句の条件 ) {

                result.Add(外部表(i), 内部表(j));

            }

        } else if ( 外部表(i).キー < 内部表(j).キー ) {

            break;

        } // else なしを嫌う人もいるけど

        j++;

    }

    i++;

}

少なくとも4種類考えられる

2つの表を JOIN しただけの単純な SQL でも上のように少なくとも4種類は考えられ、それぞれに、「外部表のWHERE句の条件」、「内部表のWHERE句の条件」について、インデックスを使った方が良いかの判断が行われ、使われるケースと使われないケースがあります。

それぞれのパターンを考えると、昨今、「センサーの状況で少し分岐した処理をする」という機能を持った家電に「AI搭載(似非AI)」と謳っている広告が多いですが、それ以上のレベルの機能は30年ほど前には確立され、20年前には十分に実用レベルになっていた訳です。

ちなみに、私も手続き型言語オブジェクト指向言語)からキャリアをスタートさせていますから、SQL を考えるとき先に手続き型で考えています。

上に書いたことの逆の思考をしているのです。

なぜ ORM を目の敵にするか

ORM からは、RDBMS の内部にある統計情報を見る手段がありません。

よしんば見れたとしても、大量のデータキャッシュを保持している RDBMS のインプロセスで処理した方が圧倒的に効率的です。

統計情報が見れないのであれば、理論上、ORM は RDBMS が備えるオプティマイザを超えない訳です。

そもそも、ORM は、「SQLを書きたくない。手続き型で全部処理したい」という欲求から生まれています。

「その欲求がそもそも勘違いである」

というのが私の主張で、

手続き型(ORM) → 非手続き型(SQL)→ 手続き型(実行計画)

という流れは、典型的な車輪の再開発です。

通常、批判される車輪の再開発は開発期間だけの無駄なのですが、この流れは実行時にも毎回同じことが繰り返されることが重要です。あり得ないレベルでの車輪の再開発なのです。

車輪の再開発が起きるのは、車輪の機能を正しく理解してないからです。

つまり、ORM を作ったり、使ったりしてしまうのは、それだけで SQLRDBMS の理解が足りてないと証明されてしまいます。

もちろん、正しく理解されてない時代が長いため、最新の言語と比べると SQL の不便さが際立ってしまっているところもあります。しかし、ここ20年間に大量のリソースを「車輪の再開発」に費やしてきました。

その間に、

 

「ORM が良い」

「否、SQL を書くべき」

 

という議論は、何度も起きては消えしてきました。

その間に、消えて行った ORM はいくつもありますが、「消える」と言われた SQL は消えていません。理論上、ORM は、SQL を超えないからです。

私は、「SQL を正しく理解して SQL の不便さを解消する」という方向に向かうべきで、今からでも遅くないと考えています。

 

次回は、NoSQL と SQL について書きます。

ほとんどのエンジニアは「Staticオジサン」よりひどい3

前回の続き。

正解はこうなる

f:id:Sikushima:20190417105234p:plain

※ 私は左辺、右辺を揃えますが、揃えなくてもOK。

削除フラグで絞り込みたい訳ではないので、「削除フラグ = 0」という条件は、抽出条件ではなく、結合条件なのです。

 

イメージはこうなる

f:id:Sikushima:20190417105649p:plain

削除フラグで抽出してないことに注目してください。

 

つまりこれはバグ!!

f:id:Sikushima:20190417110803p:plain

LEFT OUTER JOIN のとき、右に置いたテーブルがWHERE句に入ると、外部結合を打消してしまいます。

ですから、外部結合を期待しているのか、INNER JOIN との使い分けが面倒で決め打ちしているのか、ソースだけでは意図が分からない。

いずれにしても矛盾したバグなのです。

「Staticオジサン」はStatic決め打ち

「Staticオジサン」は、Static決め打ちだったのですが、LEFT JOIN 決め打ちはいくらでもあります。

例えば、WordPress のようなユーザ数が多い OSS でも不用意に決め打ちしている LEFT JOIN はあります。

f:id:Sikushima:20190417112313p:plain

WordPress のような LEFT JOIN 決め打ちは、会社、プロジェクト単位の共通仕様に、「JOIN は LEFTにすること」と書かれていることもしばしばあります。

「間違った使い方」という意味では、Static 決め打ちも、LEFT JOIN 決め打ちも、どちらも同じです。後から調査してメンテナンスが大変なのも、どちらも同じです。

しかし、Staticは実害はありませんが、LEFT JOIN の決め打ちは実害のあるバグなのです。

間違いに気づいてない、エンジニアが「Staticオジサン」を笑う資格はまったくありません。

※ 「Staticオジサン」がSQLをちゃんと理解していたかは知りませんが。

ちなみにWordPressはこんな変換をしています

f:id:Sikushima:20190417112329p:plain

間違っていることの意図を予想して解説をするのはとても難しいですが、なぜ、最後に LEFT JOIN に変換をしている理由はおそらく以下のスライドの通りでしょう。

f:id:Sikushima:20190417113138p:plain

世界的にひどい状態です。

 

 

 

ほとんどのエンジニアは「Staticオジサン」よりひどい2

前回の続きです。

まずは間違いの例

たぶん、何も考えてない人は以下のようになったと思います。

f:id:Sikushima:20190417102129p:plain

基本構文を分かってない。どうしようもないですね。

もう少しマシな人の例

f:id:Sikushima:20190417102346p:plain

工夫はしたけれど、残念!

 

正解は次回。

ほとんどのエンジニアは「Staticオジサン」よりひどい1

「Staticオジサン」が炎上して10年ほど経ったけれど、私が指摘した通り、ほとんどのエンジニアは「Staticオジサン」よりひどく、10年経っても改善してないどころか、更にひどくなっているのではないかと思います。

自分はできると思う人は例題にチャレンジ

「自分はできる」と思う人も、思わない人も、下の単純な問題にチャレンジしてみてください。

f:id:Sikushima:20190417101503p:plain

f:id:Sikushima:20190417101601p:plain

回答は次回。

実行計画のキャッシュについて2 - 各RDBMSについて

SQL文の動的生成を避けるには、実行計画のキャッシュを意識しなければなりません。

そこで、RDBMS毎にキャッシュのやり方が違いますからその違いと、「本当はこういう構造にすれば良いのに」という提案について書きます。

 

RDBMSの実行計画のキャッシュ

MySQL

実行計画はキャッシュしない。

SQL結果をキャッシュする。

PostgreSQL

実行計画のキャッシュはセッション単位。プリペアードステートメントにするとキャッシュする。

SQLServer

基本的に実行計画をキャッシュする。

RECOMPILE オプションでキャッシュをキャンセルできる。

Oracle

基本的に実行計画をキャッシュする。

BIND PEEK(最初に実行されたバインド変数で実行計画を作りキャッシュする)

ADAPTIVE CURSOR SHARING を有効にしていれば(嫌う人が多く有効にされていないことが多いが)、最初に設定されたバインド変数と外れた値が設定され、保存されている実行計画が不適切だと判断すると、同じSQLに対して、変数の範囲で複数の実行計画をキャッシュし使い分けます。

qiita.com

Oracleはよくできているが逆!

ADAPTIVE CURSOR SHARING は非常によくできた仕組みです。

しかし、考え方が逆でしょう。

 

Oracleの処理の概要

Oracleの処理はかなり端折っていますが、以下のように処理されます。

f:id:Sikushima:20190411163217p:plain

 

前回より遅いというのがどれぐらいで判断するのかはっきりしないが、同じSQLを実行された回数の10%ぐらいという実験結果もあるようです。

いずれにしても、少なくとも1度は不適切な実行計画が実行される訳です。

 

私が欲しい処理

Oracle の逆の処理を行うのです。遅いか判断するのではなく、実行計画が同じか判断すればよいでしょう。

バインド変数は範囲で持っているはずなので、作成した実行計画が同じならバインド変数の範囲を拡張していけば最終的には、最適化されたキャッシュになります。

1回目に実行計画を作ったときは、ユニークキーに対して等号(=)で判断しているバインド変数は、範囲をLowValからHighValまでとしておけば良い。NULL値かどうかは別途持つ。

 

f:id:Sikushima:20190411163523p:plain

まとめ

前回に書いたようなやり方では、RDBMSが持つ実行計画のキャッシュが使えません。

sikushima.hatenablog.com

これは実にもったいなく、Oracle の ADAPTIVE CURSOR SHARING はよくできているけれど、必ず不適切な実行計画を実行しなければいけない時点でダメでしょう。

現実に、ADAPTIVE CURSOR SHARING を無効にして運用しているプロジェクトの方が多いようです。

実行計画をキャッシュしつつ、不適切な実行計画を使うことをできる限り避けるには、Oracle の逆の考え方にするのがベストと私は考えています。

そうならなかったのは、前回書いたような当たり前のコーディング(私は20年以上前からそうしている)が、想定されていないからです。

想定していたら、実行計画のキャッシュは必ず問題になるし、O/Rマッパーなるものは生まれなかったでしょう。

「O/Rマッパー」という呼び名がなかった新人の頃に私も企画し、「工夫したら分岐は避けられる」と気づいて、企画を放棄したのですから。

実行計画のキャッシュについて1 - 分岐を避けるSQL

分岐(IF文)か計算式か

私はプログラマ(設計者)の資質に「言い換えが巧いか」ということを重視しています。

例えば、消費税率が8%で非課税品があったとき、商品マスタの税率項目に 0、課税品には 0.08 を登録しておけば、単純な計算で問題ありません。

しかし、慣れていないと日本語で仕様書を書くときに

非課税品フラグが1のときは、消費税を課税しない(消費税をゼロにする) 

 のように書き、これをそのまま分岐(IF文)で処理してしまう人が出てきてしまう。

日本語に限らず、

  • 0を得たいときに、0 を掛ける
  • 変化なしを得たいとき、1 を掛ける
  • 変化なしを得たいとき、0 を足す

という処理は、自然言語としては、「暗黙の変換が起きる」ため、「その逆の暗黙の変換」が巧くできるか、というのはIT技術者にとって、非常に重要な資質だと考えている訳です。

論理演算について

sikushima.hatenablog.comでも書きましたが、基本的に

  • AND(論理積)は掛け算
  • OR(論理和)は足し算
  • True は 1(0以外)
  • False は 0

と置き換えて考えて問題ありません。

展開、因数分解も数式と同じになります。

例えば数式で、展開した式と、因数分解した式は、

 (a × b) + (a × c)

  = a × (b + c) 

 掛け算を AND 、足し算を OR に置き換えた論理式も同様になります。

(a AND b) OR (a AND c) 

  = a AND (b OR c)           

 日本語の例としては、

男性で日本人、または、男性でアメリカ人

男性で、日本人、または、アメリカ人

日本語に限らず、自然言語(特に日本語は)を因数分解すると、最初の「男性で、」が、どこまでに掛かっているのか分かりにくく間違いのもとになりますが、理屈は同じです。

これが、(思考の上、自然言語ソースコード)スラスラできていない人のソースコードは、ネストが深くなり、くどくて非常に読みにくくなってしまいます。

分岐を避けるSQL

もちろん、SQLでも同じですが、SQLには分岐がないため、この応用が特に重要になります。

ちゃんと理解していれば、スライドの通り、分岐をしてWHERE句を動的生成する必要はありません。 

f:id:Sikushima:20190410111656p:plain



 

f:id:Sikushima:20190410111755p:plain

f:id:Sikushima:20190410111837p:plain

f:id:Sikushima:20190410111910p:plain

f:id:Sikushima:20190410111924p:plain

f:id:Sikushima:20190410111937p:plain

f:id:Sikushima:20190410111956p:plain

パースが必要(実行計画のキャッシュが使えない)

一つのSQLで複数の実行計画になるということは、実行計画のキャッシュが使えないということになります。

商用サーバでは実行計画をキャッシュしていますので、それが使えないのはいただけないですね。

次回は、実行計画のキャッシュの違いと、提案について書きます。