SQLでbit演算を使うパターン

FROM句、WHERE句での論理演算

基本的に、AND、OR になり、bit毎の判断が行われるわけではない。

この理解のために、私は掛け算と足し算で説明しています。

bit演算式

あまり使うことはないけれど、SQLにもbit演算式があります。

SQLにおいて、FROM句、WHERE句での論理演算と、式としてのbit演算をごっちゃにすると混乱しますから、私は分けて説明するようにしています。

※ 他の言語で、bit演算をちゃんと理解していたら、WHERE句で詰まることはないと考えています。ですから、WHERE句で詰まる人には、bit演算を教えるより掛け算と足し算で教えるのが妥当と考えています。

bit演算式の例

例えば、ゲーム会社でお話したときには以下のようなお話をしました。(最後の方)

fbからの引用なので、インデントは全角ブランクにしていますw

 

■ 処理

30人のユーザが同時にボスキャラに対して攻撃を行う。ユーザアクションに対してダメージポイントはAPサーバ(またはクライアント)で計算し、ボスキャラのヒットポイントから除算し、ステータスを更新する。


■ テーブル

BossHitPoint
  ID
  BossID
  HitPoint
  Status
  ……

BattleLog
  ID
  UserID
  DamagePoint
  UseItemID
  ……

厳密な処理
(若干違うかもしれませんが……)

■ 選択処理
  SELECT
     BossID
    , BossHitPoint
    , Status
  FROM BossHitPoint
  WHERE
    ID = ?;

■ APサーバ(クライアント)の処理
  ボスのヒットポイント、ステータスを取得し、
  変数 @BossHitPoint、@Status に代入し
  ボスキャラの描画を行う。

  ユーザアクションから、ダメージポイント
  (@DamagePoint)と、@Status を計算する。

■ 更新処理

  トランザクション開始
  行ロックをして、最新のボスのヒットポイント
  (@BossHitPoint)を取得する。
    ※ 他のユーザが与えたダメージを取得するため

  UPDATE BossHitPoint
  SET
     BossHitPoint = @BossHitPoint - @DamagePoint
    , Status = @Status
  WHERE ID = ?;

  INSERT INTO BattleLog
  -- 省略

  コミットしてロックを外す。

  選択処理から始める。

30人のユーザがリアルタイムで行うにはこうなりますが、例えば、ボスキャラに毒のバフが掛かったり、治ったりするとするタイミングが APサーバ(クライアント)にリアルタイムで通知できないため、APサーバ(クライアント)でダメージポイントを計算するのは正確でない可能性があります。

この処理は非常に負荷が高いので、負荷を落とす方向でチューニングを行います。

■ チューニング

更新処理を以下のようにすることで、サーバの負荷を下げるようにします。
トランザクションを起こさず、オートコミットでも問題ありません。

  UPDATE BossHitPoint
  SET
     BossHitPoint = (@ResultBossHitPoint := BossHitPoint - @DamagePoint)
    , Status = (@ResultStatus := Status | @Status) -- 後で解説します。
  WHERE ID = ?;

  INSERT INTO BattleLog
  -- 省略
  
  SELECT @ResultBossHitPoint, @ResultStatus;
  
となります。
この場合、SELECT しなくても、UPDATE 時点で他のユーザのダメージを考慮した最新のデータを更新でき、更新後のデータをほぼゼロのコストで取得することができます。

また、私はゲーム屋さんではないので、どのような構造が一般的かは分かりかねますが、ステータスは以下のような考え方です。

ステータスに 「麻痺・スタン・毒・火傷」があり重ねることができるとすると、RDBの考え方ではステータスを正規化して 1:N の形になるのですが、この処理を実現するために私ならbitで持ちます。
以下のように、ビット毎にステータスが設定されているとすると
  
   1b(1) → 麻痺
   10b(2) → スタン
   100b(4) → 毒
  1000b(8) → 火傷

ユーザの処理で 毒 が付加されるとすると、既に 毒 のステータスがあるならばステータスの変更は要らない。
つまり、bit OR 演算をすればよく、SQLでもbit OR 演算子は "|" なので、上の通りになるわけです。

SQLをやる人には bit演算は馴染みがないので、一応、追加しておくと。

例えば、
  
  1010b(10) → スタン・火傷 に 毒を付加

    SELECT 10 | 4;
      → 14 (1110b つまり、スタン・火傷・毒)が返る。
  
  1100b(12) → 火傷・毒 に 毒を付加
  
    SELECT 12 | 4;
      → 12 (1100b つまり、火傷・毒 のまま)が返る。

となります。

第14・15回 SQL Server 2019勉強会より - はてなブログに引っ越し記念

Twitterは議論に向かない。

「第14・15回 SQL Server 2019勉強会」について、

イロイロとTwitterでご意見を頂戴しましたが、140字しか書けないTwitterは議論に向いていません。

ですから、勉強会では語り切れなかったことも含め、私の主張を改めてまとめたいと思います。

私の主張

SQLの方が息が長いからロジックをSQLへ寄せた方が良い

RDBMSSQL)が本格的に使われ出したのは1990年代の中頃でしょう。
ちょうど、私がこの業界に入った頃です。

時代の流れとして、
COBOLからの転換点
Windows 95の発売(PCが一般化)
・Webの利用が一般化
Y2Kの対応

という混乱要素がいくつも重なった時期でした。

その頃の言語の流行りは、VB4~6、DelphiC++Javaアプレット)と、今とはかなり変わっていてほとんど残っていません。しかし、言語が変わっても、「RDBMSを使う」というのは今もほとんど変わっていませんし、「RDBMS」が開発効率やパフォーマンスなどのボトルネックになるということも変わっていません。

30年以上、RDBMSについては、ずっと課題であり続けています。

しかし、現実的に一番息の長い言語がSQLです。また、テーブル構造とアプリケーションのどちらが寿命が長いかというと、テーブル構造です。

つまり、SQLにロジックを寄せた方が、システムとして長持ちします。

SQLが分かりにくいという常識とは?

私は CoderDojo のようなところで、子供たちにアドバイスをするメンターのようなこともやっていて、フローチャートがそのままプログラムになるというScratchなどを使っています。

Scratchは、将来ほとんどの言語に応用できる基礎的な考え方が学べます。

つまり、フローチャートはほとんどの言語の基礎の基礎に当たるわけですが、SQLは他の言語と違いフローチャートでは表現できません。

別のイメージが必要になるのですが、ほとんどの入門書でイメージではなく文法の解説になっています。これが最大の問題です。

文法の解説をされると、他の言語の経験がある人は、自然に、

 

フローチャート → 言語

 

という慣れ親しんだ考え方になってしまって、それが通用しないSQLのとき混乱するわけです。

 

新人の中には、フローチャートのイメージができてないまま、文法を覚えてプログラムを書こうとする人が出ます。そんなプログラムは読めません。

同様に、SQLではかなりのベテランの人も、(WordPressの例にある通り)ほとんどイメージできてないまま書いています。そんな人たちが長いSQLを書くと、私が見ても意味が分からないものになって、メンテもできません。

書いた本人が、イメージを持たずに書いているのですから、他人が(書いた本人ですら)理解できるはずがないのです。

 

ですから、今のままでロジックをSQLに寄せたら大混乱になります。

当然、私は、「今のままロジックをSQLに寄せるべきとは言っていません」

教育からやり直すべき

しかし、SQLの教育は簡単です。

私は相当高いレベルのSQLを書いていますが、その私と同じレベルのSQLコーダーを育てるコストは、ハンズオンで3日あれば十分です。あらゆる言語の中でこれほど教育コストが低い言語はありません。

その方法は、勉強会でお話した通り、ベン図やエクセルでSQLのイメージをつかむことです。ですから、プログラム未経験であっても、エクセルを使って業務をしている事務職の方は、エクセルで処理するイメージを既に持っているため、SQLをあっという間に理解できるようになります。

嫌いな人に関わらせない!

そして、嫌いな人に関わらせないように開発手法を見直すべきです。

開発効率を高めるため、SQLが苦手な人たちが考え出したORMなるものがあります。ORMは、「SQLが嫌いな人にもSQLを隠して関わらせよう」というものですが、俺俺フレームワークにあるORMも含めたら、いったい何種類のORMが存在するでしょう。

それだけの数を作っても解決に至っていない。
というのは、ORMは最適解ではないと断言してよいでしょう。

 

ORMはMVCの概念でインピーダンスミスマッチを解消するために作られました。

しかし、ORMはMVCが持つ「違う機能は疎結合にしよう」という最も重要な概念に矛盾しています。

f:id:Sikushima:20190225142257p:plain

f:id:Sikushima:20190225142303p:plain

f:id:Sikushima:20190225142308p:plain

この構造でおかしいのは、全く違う構造を持ったSQLを「オブジェクト指向言語で何とか扱おう」としていることです。SQLはAPサーバでORMによって生成されて、DBサーバで実行されます。

スタンドアローン向けに考え出されたMVCの概念をWeb向けに発展させていくとしたら、一番に疎結合にすべきSQLを、Modelに取り込んで実行時に生成するというという謎の構造になってしまいます。

f:id:Sikushima:20190225142312p:plain

上の図のようにSQLをDBサーバ側に保存(ストア)し疎結合にすれば、オブジェクト指向言語で開発する人と、SQLを扱う人を完全に分離することができます。

現状のMVCに基づく開発では、オブジェクト指向言語を扱う人はSQLの知識もいる。

しかし、全員に高いレベルを要求してもできないから、多くの技術者が(フローチャートにすらならず)違和感を持つSQLの技術レベルを下げるということが起きている訳です。

 

特定の技術のレベルを下げれば、そこがボトルネックになるのは当たり前です。

 

しかし、勉強会でお話した通り、SQL疎結合にすることができれば、

いずれも、活躍の場が生まれます。

そして、プロジェクト全体としては、特定の技術レベルを下げていないため高い品質を担保できるわけです。

議論にあたり

技術的な議論と、営業的な議論をごちゃ混ぜに意見するのは止めてください。

営業的な議論は、個々のプロジェクトによって違うことですから、公に議論しても意味がないことです。個々のプロジェクト内でやってください。

技術的な議論

技術者同士の議論について、「誰々が言ってる」、「みんなが言ってる」、「流行っているか」などを持ち出されても、それは何の論拠にはなりません。

「みんなが言ってる」が正しいのであれば、大地はいまだに平のままでしょう。

正しいか、正しくないか、自分の考えた理屈に従って、万人が何と言おうと、「それでも地球が回っている」と主張するのが技術者だろうと私は考えています。

 

営業的な議論

営業的には、「みんなが言ってる」、「流行っているか」などが意味を持つことがあります。(エクセル方眼紙のように)技術的に間違っていることでも選択することはあり得るのです。

 

10年ほど前に、「すべての変数をStaticで宣言したらしっくりくる」と言って大炎上した事件がありました。

すべての変数をStaticで宣言しても動くアプリケーションは作れ、ユーザに対する影響は軽微です。逆に、ORMを使うなど、SQLの技術を低くするということになり、ユーザに対する影響はもっと大きいです。

それなのに、なぜStaticオジサンは、大炎上させるのでしょう?

 

大炎上させるほどの技術者としての正義感が働く、その基準が私には理解できません。

 

「すべての変数をStaticで宣言」はひどい、と思うのであれば、「ORMを使うこと」はもっとひどいのです。ですから、営業的にORMを受け入れざるを得なかったとしても、「技術者としてあるまじきダブルスタンダードを受け入れたんだ」と理解して、次の機会に改善できないか考えていただければと思います。

 

消費税増税で景気を回復させる!

通常、不況は数年で収束するけれど、

バブル崩壊後、日本は全然立ち上がれない。それは……
バブルが急峻すぎたこと。バブル崩壊労働人口減少の始まりの時期が重なってしまったこと。なども、原因ではあるけれど、最大の原因は、日本は目の前の出血を極端に嫌うこと(だと思う)。目の前の出血を嫌ったあまり、

  1.不良債権の処理を極力遅らせた(しなかった)
  2.賃金を落としても雇用を守った。
  3.指名解雇をできるようにしなかった。
  4.非正規を安く使うようになった。

これが未だに尾を引いている。2〜4で、

  賃金上昇が起きない → 価格競争できる → デフレ

という流れが定着してしまった。

ここで、勘違いしないで欲しいのは、非正規が悪いのではなく、非正規の方が賃金が安いことが問題。指名解雇ができれば雇用の流動性が生まれ、労働賃金は市場原理によって動くようになる。だから、タレントや野球選手のように流動性が高い職種では、フリー(非正規)の方が高くなる。他に、流動性が高いIT技術者のような職種も、流動性が高いレベル(中小企業間では)フリーランスの方が高くなっている。しかし、流動性がない大手IT企業の下請けになると、下請け価格は市場原理とは違うところで決まる。というように、流動性が高ければ市場原理が効きやすく、流動性が低いと市場原理ではない価格決定がなされる。

アベノミクスについては、極端な円高を放置した民主党政権がひど過ぎたので金融緩和をする必要があった。戦力の逐次投入なんて意味がないので、黒田バズーカは悪いとは言わない。
しかし、リフレで経済問題が解決するはずがないから、ちゃんとした出口戦略として成長戦略が必要だし、財政再建も考えないと行けない。私は、上の通り成長戦略のキモは、解雇規制の撤廃と考えている。指名解雇ができるようになれば労働市場に市場原理が効くようになる。

  悪い上司が、滅多やたらにクビを切ったらどうするのか?
  そんな上司がいるところはさっさと辞めればよい。
  労働者不足でも会社は潰れるのですから、滅茶苦茶にクビに
  する会社なんて潰れるでしょう。貴方に給料分の能力がある
  なら、会社は貴方のクビを切ることはありません。

市場原理が働けば、同じ仕事をするなら不安定な非正規の方が高くなる。日本は労働人口が減少していくことは確定しているため、潜在成長率は基本的にゼロと考えるべき。今、少し上向いているのは、リーマンショック民主党政権の悪政というマイナスからの調整局面で、本質的に何も変わってない。それでも、バブル崩壊時まで戻ってないから、労働市場に市場原理を入れることによってそれぐらいには戻ると予想している。その先は、余程のイノベーションが起きたり、大きな新しい産業が生まれない限り、ゼロベースじゃないですかね。

ゼロベースで高齢化が進み社会保障費が掛かるようになるから、財政再建は非常に難しく、消費税増税でやるしかない。

ここで、どういう訳か現役世代でも消費税が嫌いな人が多いので、消費税の意味を考えると……

金を持っている老人から取るには、
    消費税
    贈与税(実際に払うのは貰ったおそらく現役世代)
    相続税(実際に払うのは貰ったおそらく現役世代)
しかない。ぶっちゃけ、消費税が一番手っ取り早い訳。
    直接税は、働いている人しか払わない税金。
    消費税は、老人からも取れる税金。
という意味をよく考える必要がある。

例えば、2014年に消費税は3%上がったが、2014年からでも社会保険と厚生年金は、合計で3%近く上がっている。消費税がなければ、さらに高率で社会保険料所得税などがジリジリ上がる。完全に現役世代から搾り取られているのに、なぜ不満の声が上がらないのか理解できない。

消費税を止めるべきという人は、「自分たちだけで老人を支えます」と言ってるのと同じで、その考え方は改めるべき。潜在成長率がゼロなのに、「景気回復させて所得税法人税で」という考え方も、あっという間に自分のクビを絞めることになる。バブル崩壊から回復できないのと同じように……。

つまり、財政再建(老人を支える原資)は消費税増税で行うべき。消費税を上げなければ、現役世代だけに掛かる直接税や、社会保障費が上がるだけ。そんな不公平をなくすには、消費税は20%ぐらい要ることは間違いない。

もちろん、一気に20%にはできないから、12年間、毎年1%ずつ上げ続ければよい。そうすれば、現金資産を持っている人にとって、12年後には確実に12%価値が目減りすることになる。

フロー(現金収入)ではなく、ストック(現金資産)に課税できる訳。

「できる限り今年中に物に変えよう」
今年を逃しても、来年になったら、11年後には11%目減りすると考えられるから、
「やっぱり早く現金を物に変えよう」
という消費に対するインセンティブを引き起こせる。

現金資産を持っている老人に、「使わないと損」言う状況を作り出す。金が回れば景気は回復する。更に、財政は確実に良くなり、積極的に社会保障の体制を作り直すことができる。

ここで、消費税増税をしたら、
年金生活者が」とか、
「失業者が」とか、
言う人が出るけれど、そう言う人はリフレ派だったりするのです。

リフレ派の言う通り、毎年2%のインフレが達成されるようになったとしましょう。
年金は-0.9で物価スライド。つまり、年金生活者は、毎年、0.9複利で生活が苦しくなる。失業保険も、かなり遅れて物価(賃金)でスライドするが、毎年複利になるインフレは、消費税増税とほぼ同じか、むしろ増税より厳しい。

更に、リフレ派のやり方では、景気回復分しか税収が伸びないばかりか、国債発行額は積み増しされて行くけれど、消費税増税なら、財政再建が進むので、本当に生活が苦しい人に社会保障を回す原資を作ることができる。

理論的には、リフレよりは上手く行きますが、

    解雇規制撤廃(指名解雇制)
    消費税を20%まで増税

どちらも、字面だけでも怖く、特にリフレ派の人なんて検討もしてないはず。しかし、20年以上も不況が続くやり方が正しい訳はないし、4年続けてマイナス金利までやっても、リフレ派の言う通りインフレは起きない!民主党政権がやった暗い雰囲気を戻すために、背中を押す程度のことでしかない。もう、間違いを認めた方が良い。

もちろん、消費税を考えるとき、「中小企業に云々」と言う人がでるが、そう言う人は消費税の仕組みを理解できてない。是非、図の内容は理解してから考えて欲しい。

元請には消費税が何%でも影響は全くないのに、元請に「消費税込みでと言われた」というのは、単純に値引き要求をされただけで、消費税は全く関係ない。営業マンの中には理解していない人もいるし、「夏祭りだから値引け」みたいにノリで言ってる可能性もある。何にしても値引き要求に勝てないのは、消費税のせいではない。

=============

私の言ってることは、マイナス金利よりは常識的な考えなんですけどね。
あり得ないと思っている側に答えがあるものです。
ちなみに、本業の方も、皆さんが目先で考える反対側の主張をしています。逆張りじゃなく、常識を取っ払って、ゼロベースで考えたらそうなるだけです。

「艦これ」についてのまとめ #艦これ

「艦これ」についてというには、遠いけれど、「艦これ」からソーシャル系のシステムについて考えた話についてまとめました。

一覧

「艦これ」から、ソーシャル系のサーバ構成を考える http://d.hatena.ne.jp/Sikushima/20130901/1378021345
お手製クラスター構成は良くないよというお話。

「艦これ」もう少し思い出して考えてみた http://d.hatena.ne.jp/Sikushima/20130904/1378261621
アニメーションの間、非同期で処理してる?というお話。

もうちょっと「艦これ」からSQLを考えてみる1 http://d.hatena.ne.jp/Sikushima/20131014/1381715465
SQLでするとこんな感じになるというお話。

もうちょっと「艦これ」からSQLを考えてみる2 http://d.hatena.ne.jp/Sikushima/20131104/1383554586
システム全体の流れから、SQLを考えてみる。

もうちょっと「艦これ」からSQLを考えてみる3 http://d.hatena.ne.jp/Sikushima/20131108/1383876997
もう少し、システム全体の流れを考えてみる。RDBMS を使う限り SQLを使わなければ、必ず非効率になるという話。

もうちょっと「艦これ」からSQLを考えてみる4 http://d.hatena.ne.jp/Sikushima/20131110/1384051720
ストアドファンクション(プロシージャ)を利用すればここまでキレイにできるという話。

とにかくやった方が良い

何にしても、RDBMSを使う限り、SQLを使うか、SQLをショートカットするAPIを使わない限り、システムの負荷も、パフォーマンスも悪くなる。負荷とパフォーマンスを犠牲にするのは、顧客に対する便益を犠牲にするということ。SQLができない人が多い、ということは技術者(システム会社)の都合でしかない。

できない技術者のために犠牲にしているのは、100倍(10000%)という単位のロス。

Javaや.Netなどで下手糞に作っても100倍という差は中々出ないけれど、数十%の差でも鬼の首を取った様に大騒ぎするが、「SQLはできなくても良い」というのは、技術者としてどうなのか?

日本で最も取得者が多い資格は、初級シスアドで、中学生から、OL、主婦まで取ってきました。それは、業界としての常識と言って差し障りはないでしょう。その初級シスアドの問題を見れば、技術者ばかりでなく、営業や経営者まで、全員ができて当たり前なんです。

できないと言うだけでなく、SQLを使いきらなければ負荷が下がらないということに、「炎上」とかいって絡んで来た馬鹿が大勢いました。彼らはいっぱしの技術者のつもりでしょうが、こんなシステムの単純なフロー http://d.hatena.ne.jp/Sikushima/20131108/1383876997 が頭の中になく RDBMSブラックボックスとしている証拠です。この程度のフローが分かってないのは技術者とは言えない。技術者どころか人間のクズでしょう。

人間のクズにならないように勉強した方が良い。工数の差は見せようがないのですけど、そら滅茶苦茶な差が付きますよ。

もうちょっと「艦これ」からSQLを考えてみる4

前回の続き

 DBサーバの処理を肩代わりして負荷を減らせるとしても、
    最後のソート(これは問題になるなら禁止すれば良い)
    IF文
    四則演算
だけです。

 「IF文、四則演算をAPサーバやクライアントで処理すべき」と考えられるのは、設計段階にSQLでどこまでできて、どんな記述をするか、全体の処理、負荷のバランスが完全に理解できてないと無理です。そういう技術者は少ないから、その様な設計をすることは難しい。
 全部を俯瞰できる優秀な技術者を集めて突き詰めて考えたとしても、IF文、四則演算をAPサーバなどで行うには、ほとんどの場合、転送量かSQLの発行回数が増えるため、減る負荷よりも遙かに大きな負荷が掛かります。つまり、「IF文、四則演算をAPサーバやクライアントで処理した方が良い」というパターンは本当に少ないので、全体を細かく考えられないなら「とにかくSQLでやる」と決める方がマシです。

 しかし、複雑な処理をSQLで行えば、ここにある様にとても醜いプログラムになる。

 その理由は、SQLに分岐がなく無理矢理 CASE式で行うからためです。しかし、こういう場合は、他の言語と同様に Funciton にすれば良い。

 求めている現在値は、
    前回更新時間
    前回の数
    増加数
    ブースト終了時間
    ブースト倍数
    最大値
があれば導出できることが理解できれば、ストアドプロシージャ(ファンクション)だからといってどうということはないでしょう。私もMySQLでは実務で使ったことはないけれど、肩肘張らずに他の言語の関数を作るのと同じように、機能と引数を考えれば、別に何も難しくないと分かるはずです。

計算するだけのストアドファンクション

delimiter //  -- ストアドプロシージャの中でも終端のデリミターが必要なので、デリミターを変更

DROP FUNCTION IF EXISTS fc現在値
//

CREATE FUNCTION fc現在値(
	前回更新時間 TIMESTAMP
	, 前回の数 INT
	, 増加数 INT
	, ブースト終了時間 TIMESTAMP
	, ブースト倍数 FLOAT
	, 最大値 INT) RETURNS INT
BEGIN
	DECLARE _ブースト分数 INT;
	DECLARE _通常分数 INT;
	DECLARE _ret INT;
	
	IF ブースト終了時間 > 前回更新時間 THEN -- ブースト中
		IF ブースト終了時間 > CURRENT_TIMESTAMP THEN 
			SET _ブースト分数 = TIMESTAMPDIFF(MINUTE, 前回更新時間, CURRENT_TIMESTAMP);
			SET _通常分数 = 0;
		ELSE 
			SET _ブースト分数 = TIMESTAMPDIFF(MINUTE, 前回更新時間, ブースト終了時間);
			SET _通常分数 = TIMESTAMPDIFF(MINUTE, ブースト終了時間, CURRENT_TIMESTAMP);
		END IF;
	ELSE -- ブーストなし
		SET _ブースト分数 = 0;
		SET _通常分数 = TIMESTAMPDIFF(MINUTE, 前回更新時間, CURRENT_TIMESTAMP);
	END IF;
	
	SET _ret = (増加数 * ブースト倍数 *_ブースト分数) + (増加数 * _通常分数);
	-- CASTは自動で行われる。嫌な人は明記すれば良い。
	
	IF _ret > 最大値 THEN
		RETURN 最大値; 
	ELSE
		RETURN _ret;
	END IF;

END;
//

delimiter ; -- デリミターを戻す。

SQLをラップするストアドプロシージャ

delimiter // 

DROP PROCEDURE IF EXISTS prユーザステータス
//

CREATE PROCEDURE prユーザステータス(IN p_id INT) -- INタイプのパラメータを指定
BEGIN

	SELECT 
		……
		, fc現在値(a.前回更新時間, a.弾薬数, b.弾薬増加数, a.ブースト終了時間, c.弾薬増加倍数, b.弾薬最大値) AS 弾薬数
		, fc現在値(a.前回更新時間, a.石油数, b.石油増加数, a.ブースト終了時間, c.石油増加倍数, b.石油最大値) AS 石油数
		……
	FROM 
		ユーザステータス AS a
		INNER JOIN 増加マスタ AS b
			ON a.ユーザレベル = b.ユーザレベル
		INNER JOIN ブーストマスタ AS c
			ON a.ブーストレベル = c.ブーストレベル
	WHERE 
		a.id = p_id;

END;
//

delimiter ; -- デリミターを戻す。

大した差はない

 ストアドファンクションは、CASE式をキレイに書くために切り出しただけ。他の言語でも同じように作るんじゃないの?

 ストアドプロシージャは単純にSELECT文をストアー(保存)するだけのもの。何も難しいことはしてないし、SQL文(SELECT文)のままでも悪いわけではないけれど、ストアドプロシージャを作れば、APサーバでは、SQL文の代わりに

	CALL prユーザステータス(?);

だけで良い。これでAPサーバ(オブジェクト指向言語)から見ても、異物として紛れ込むSQL文を最小限にすることができる。完全な疎結合になるので本当は一番キレイです。

 MySQLはストアドプロシージャにしてもプリコンパイルしない(らしい)ので、実行時のコンパイルを避けるということはできないようですけれど、SQL文をDBサーバで管理するのはメンテナンス面からも、本来はメリットがある。

 文法としては、代入するのにいちいち「SET 変数 = 値;」とするのは古典的で格好は良くない。しかし、ネットワークを挟んでループさせたり、大量のデータを転送して処理するという、設計上の大問題(私的にはバグ)をやってしまう方が遙かに格好悪い。

 ストアドプロシージャの文法はどうしようもないが、設計上の問題は担当する技術者の問題に他ならない。自分の担当分野の問題は平気なのに、「文法のダサさが気になる」というのは、完全に間違ってるのです。

もうちょっと「艦これ」からSQLを考えてみる3

 前回の続き

 前回のパターンであれば、全体を(パフォーマンス・サーバの負荷・開発工数・メンテナンス性)を鑑みて、IF文と四則演算は、DBサーバより、APサーバ。APサーバより、クライアントにある程度キャッシュさせて、処理もクライアントで行った方が良いです。

 そう判断するには、設計段階でSQLで処理したときどうなるか、SQLで処理しなかったときどうなるか。が正確に分かってないと判断できません。

 例えば、「艦これ」からは随分と離れますが、あるテストの男子と女子、全体の平均と最高点・最低点を求めたいとする。(面倒なので性別は非正規化されているとする)

テーブルは以下の通り

成績テーブル
 テストID
 生徒番号
 性別(1:男子、2:女子)
 得点

1.SQLで処理する

SELECT
	テストID
	, AVG(CASE WHEN 性別 = 1 THEN 得点 ELSE NULL END) AS 男子平均
	, AVG(CASE WHEN 性別 = 2 THEN 得点 ELSE NULL END) AS 女子平均
	, AVG(得点) AS 全体平均
	, MAX(CASE WHEN 性別 = 1 THEN 得点 ELSE NULL END) AS 男子最高点
	, MAX(CASE WHEN 性別 = 2 THEN 得点 ELSE NULL END) AS 女子最高点
	, MAX(得点) AS 全体最高点
	, MIN(CASE WHEN 性別 = 1 THEN 得点 ELSE NULL END) AS 男子最低点
	, MIN(CASE WHEN 性別 = 2 THEN 得点 ELSE NULL END) AS 女子最低点
	, MIN(得点) AS 全体最低点
FROM 成績テーブル
WHERE テストID = ?
GROUP BY テストID

実行計画をとれば分かりますが、図の様に全ての集計が1回のループ内で処理されます。

2.単純な集計のみSQLで行う

SELECT
	テストID
	, AVG(得点) AS 平均
	, MAX(得点) AS 最高点
	, MIN(得点) AS 最低点
FROM 成績テーブル
WHERE テストID = ? 
	AND (性別 = ? OR ? = 0)
GROUP BY テストID

上のSQLを0〜2までループするか、SQL文を動的生成し、全体を取得するときは

AND 性別 = ?

を付けない形もあるかと思いますが、こういうプログラムを書く人は多いでしょう。

 この処理は、DBサーバ、APサーバ共に余分な処理が増えているだけで、最終的な処理時間も長くなりますから、メモリーを掴んでいる時間も長くなります。

 1.のSQLが掛けない人に配慮する以外にメリットはあり得ません。

3.全件送信してAPサーバで計算

集計関数が分からないという人は、以下の様な処理を選ぶかも知れません。

SELECT
	テストID
	,性別
	, 得点
FROM 成績テーブル
WHERE テストID = ? 

 DBサーバの処理はIF文と四則演算が減っただけ(誤差)。その代わりに大量のデータ送信が行われます。当然、最終的な処理時間も長くなりますから、メモリーも長時間掴んだままになります。

 こちらも、1.のSQLが掛けない人に配慮する以外にメリットはあり得ません。

ソースのキレイさで選んではいけない

 APサーバ側のソースのキレイさで選んでしまう人にとっては、SQLを使いきる1.が一番違和感があって汚いと感じるでしょう。3.が一番キレイに感じるかも知れません。

 全体の処理フローから考えれば、2.3.の

   ネットワークを挟んでループする。
   無駄なデータを転送して、APサーバで四則演算する。

という処理はあり得ないほど非効率ですから、もちろん、2.3.で「DBサーバの負荷が下がる」と考えるのはプロと呼べないレベルです。馬鹿のカスのと言うけど、それでは足りないほどお粗末なレベルです。

 SQLでなくオブジェクト指向言語で書くプログラムをDBサーバに置くなら 2.3.の処理を書く馬鹿は少ないし、もし、いても設計担当からハズされるでしょう。

 2.3.はシステムが複雑になっただけで、依存関係が強くなり、言語のスパゲティ(SQLとOO言語)になっている。DBサーバの負荷も、APサーバの負荷も減らず、とてもプロが設計したシステムとは思えません。

 しかし、こんなお粗末なレベルのモノも現場で普通に存在します。それが平気なのは処理フローが全く見えてない、DBサーバをブラックボックスとして考えているのでしょう。ですから、SQLになれば突然、ほとんどの人が理解できなくなって、「2.3.の処理が正しい」と主張し出してしまう。SQLオブジェクト指向言語が等価と考えてない(何度も繰り返しますが、SQLコンパイルした後は等価)等価と考えてないということは、SQLを魔法か何かと勘違いしている訳です。

 全体の処理フローを突き詰めて考えていけば、APサーバでDBサーバの処理を肩代わりして負荷を下げることができるのは、最後のソートと、IF文、四則演算しかありません。

 ソート以外は誤差で、ほとんどの場合その誤差を減らすために返って処理量が増えますから、「RDBMSを使う以上、SQLで書くしかない」という結論になります。

 ※ ただ、SQLが苦手なシーケンシャルな処理は(ストアドプロシージャか)APサーバでやるべきですが。

分かってなくてもできるから良いか?

 結論的には、業務システムで今のサーバなら気にしないでもいいと思う。下手糞とは思うけど、下手糞しか居ないんだから仕方ない。ハードが良くなりすぎて、業務上トラブルになるほどの負荷、パフォーマンスになることはほとんどないでしょう。取りあえず下手糞が設計しても動きます。今のサーバは止めようと思っても、そう簡単には止めるほどヘボいプログラムは作れない(苦笑)

 まあ、スラスラSQLが書ければ工数も下がるのですが、できない馬鹿が多すぎてそうも言ってられないからね。

 しかし、ソーシャル系はユーザの増加は予測困難ですから、「取りあえず動く」は拙い。

 APサーバのプログラムとDBサーバのプログラム(SQL)を機能単位に同じ人に割り振っている現状の人員配置、開発手法では、全員にオブジェクト指向言語からSQLまで分かっていなければ、真っ当なものは出来ないということになります。

 ぶっちゃけ、それだけ人員を集めるのは難しいでしょう。

 ですから、APサーバのプログラムを担当する人、DBサーバのプログラムを担当する人を完全に分けるべき。ほとんどの人はできているつもりで、RDBMSSQL)をブラックぼっくとして中途半端に扱っているのですから、本当にブラックボックスにしてしまえば良いわけです。

 「馬鹿な考え休むに似たり」「馬鹿は考えるな」ってことです。

 そのためにはSQLはストアドプロシージャでラップするのが一番良いのです。後からどうとでもできます。普段から「1.しかあり得ない」という人が「ストアドプロシージャなんて」というなら聞く意味はありますが、「2.や3.で何が悪い!」っていう馬鹿が「ストアドプロシージャなんて」という意見には何の価値もない。基礎も分かってない人間が応用部分のメリットが理解できるわけがないからね。(ここが悩ましいところ)


 「艦これ」は、カタログスペックの1000分の1以下で止まっている訳ですから、どんな失敗が重なった設計なのかは私には見当もつかない。余程失敗が重ならないと、1000分の1が出せないってことはないでしょう。

 WEBサーバ、APサーバが何台で運用されているかは分かりませんから、その台数によってはシステムは簡単に落ちるでしょうけど、DBサーバが原因になるのは、設計に余程の問題を抱えている(いた)はずで、それはSQLの無理解から来ているはずです。

 ソーシャル系なら、NoSQLか、RDBMSを使うならSQLを完全に理解した人が設計すべきです。

もうちょっと「艦これ」からSQLを考えてみる2

 前回の続き
 「艦これ」で起きた障害は、カタログスペックの100分の1以下の処理量で発生ししていると思われる。その原因はDBサーバに対する無理解、SQLに対する無理解から起きているのでは?という考察です。
 もちろん、中身は知らないけれどね。

どこで処理するか?

 少し昔話から。コンピュータシステムはざっくりと、

   汎用システム
   クライアントサーバシステム
   Webシステム

 と発展してきました。
 汎用システムの時代は、端末に処理能力がありませんでしたから、あらゆる処理をホストコンピュータが担っていました。これがクライアントサーバシステムになったとき、どちらで処理を行うかを考える必要が出てきました。

 Webシステムになると、DBサーバ、APサーバ、クライアントのいずれもが処理能力を持ち、多くの処理をそれぞれが代替することができる。

 開発時はDBサーバ・APサーバ・クライアントが1台のPCで行われることも少なくなく、クライアントサーバシステムの頃、Exeファイルを置いただけのファイルサーバのことをAPサーバと呼んでいた馬鹿者も少なくなかったので、今でも分かってない人は多いのじゃないかと想像する。

 どのコンピュータで処理するかはシステム設計の根幹で、これを理解していたら、RDBMSを使う以上、「SQLを使いきるべき」と理解できるはずですが……。

前回のSQLはこういう流れになる。

 赤い部分は、APサーバでも、クライアントでも代替できる処理で、そこで何をやっているかというと、

   ブーストの時間内か判断(IF文)
   経過秒数と増加数、前回の保有量から現在値を算出(四則演算)
   最大容量を超えないか判断(IF文)

 数個のIF文と四則演算を、どのコンピュータにやらせるかによってシステムダウンするかどうかが変わる。それほどシビアなチューニングがなされたシステムであれば、カタログスペックに近い性能が出ているでしょう。

 赤い部分、つまりIF文と四則演算は、どこで処理してもシステムに与える負荷は誤差レベルです。

本来はこうすべき

 それぞれのサーバの負荷、パフォーマンス、コーディングを考えて設計すればこうあるべきでしょう。

SELECT 
	-- カラムの羅列(省略)
	, CURRENT_TIMESTAMP
FROM 
	ユーザステータス AS a
	INNER JOIN 増加マスタ AS b
		ON a.ユーザレベル = b.ユーザレベル
	INNER JOIN ブーストマスタ AS c
		ON a.ブーストレベル = c.ブーストレベル
WHERE 
		a.id = ?;

 DBサーバ、APサーバのどちらで処理を行っても、データの転送量も転送回数も大差はないため、「どこでやっても同じ」なら「キレイなコーディングになる方が良い」と判断できます。

 データはDBサーバにしか存在しませんから、これ以上、DBサーバの処理を減らすことはできません。つまり、JOINを避けるなどSQLを分割しても、DBサーバの処理は増えるだけで減ることはないのです。

   SQLでどこまでできるか?
   それはどんな流れになるか?
   コーディングはどちらが分かりやすいか?

 などが設計段階で見えてないと本来は設計できませんが、そういう人はほとんどいないでしょう。

 もちろん、SQLが苦手な人が設計してもこうなることはありますが、それは偶然に過ぎないのです。分かってないんですから……。

SQLを除けて考えれば理解できる

 データを保存しているデータ処理を、SQLではなくオブジェクト指向言語などで可能なコンピュータ(データサーバ)があったとします。
 データサーバがデータを読み込むときは、メモリー上に展開する必要があります。
 データサーバのメモリー上に展開したデータを、別のコンピュータに転送。
 別のコンピュータでも、当然、メモリー上に展開し、データ処理(IF文、四則演算など)を行う……。

 どう考えても無駄ですよね?

 ですから、データサーバが扱う言語がSQLではなくオブジェクト指向言語で処理が可能だった場合、ほとんどの人が、わざわざ別のコンピュータにデータを送って処理しようとはしないはずです。メモリー上にデータを展開しながら、データ処理(IF文、四則演算など)を行って、結果だけを別のコンピュータに転送するでしょう。

 しかし、「SQLは訳が分からない」「分かったつもりになっている」人達は、平気でそれをやろうとしますし、それが正しいと主張します。おかしいでしょう?

   「SQLで処理したら遅い」
   「SQLで処理したらDBサーバの負荷が高まる」

 などは、全く意味を分かってない発言なんです。

 SQLを魔法か何かと勘違いしているからでしょうが、SQLを受け取って、SQLコンパイルして処理するDBエンジン(オプティマイザ)のほとんどは C++ で書かれたプログラムです。つまり、SQLは仕様書にあたり、プログラムとは等価です。実行時には C++ で書かれたプログラムがデータにループしながらアクセスするわけですから、本来、設計する人は

   SQL → プログラム(オブジェクト指向言語
   プログラム(オブジェクト指向言語)→ SQL

 のどちらも自在にできないと、どこまでをSQLで処理するのが良いのか決めることはできません。

 SQLオブジェクト指向言語が一致しない人達は、SQLRDBMS)をブラックボックスとして考えて、何となく設計ができた気でいるだけです。もちろん、そんなやり方でも動く動かないで言えば動きます。オブジェクト指向言語で処理が可能なデータサーバがあったとして、どちらで処理をしても同じ結果が出るのは当たり前です。

 ただし、分からない人が適当にやってる設計ですから、巧くいくかは運次第です。場合によっては「カタログスペックの1000分の1も出せない」という結果になってしまいます。

コンパイルが通っただけ

 私に絡んで来るような人は、ヘボいとは思うけれど、それでも、業界的にはマシな部類でしょう。取りあえず、手続き型言語オブジェクト指向言語)はそこそこに使っているはずです。

 新人や後輩、部下の指導もしていることでしょう。

 例えば、新人を指導していて、「コンパイル(ビルド)は通りましたが、よく分かりません」と言ってくることはあるし、そんなことは言ってこなくても「コンパイルが通っているだけで、頭を抱えるスパゲッティコード」は多数存在します。

 何がしたいか分かってないからスパゲッティになるわけで、

 まあ、新人のうちは、コンパイルを通すだけでも必死になるのは仕方がないことですから、それなりの指導すればよいことですが、もちろん、「コンパイルが通っただけ」ではプログラミングができたとは言わない。それを理解して、「今はコンパイルを通すだけで必死だけれど、ソースの意味が分かって書けるように頑張ります!」と努力している人は、どんなに時間が掛かっても、バカともカスとも言わない。
 全力でサポートします。

 じゃあ、新人を指導していて、「コンパイルが通ってるんですから、何が悪いんですか!動くんですから何が悪いんですか!」と口答えしてきたらどう答える?

 そんな奴をプロとは呼べないでしょう。

 ……しかし、「INとEXISTSの違いが分かってない(MySQLはちょっと違うけど)」という人が、かなりの数います。そのレベルは、つまり、「コンパイルは通っているけれど意味が分かってない」状態な訳で、そのレベルのシステムを客に売ってるってことです。

 そのレベルの人間(技術者じゃない)がド偉そうに掛かって来るから「カスのバカの」と言うのです。まあ、意味が分かってないから掛かってこれるんでしょうけどね……。

 「分かってないことは拙い」
 「早く理解しないと」

と思っている人にはそんなこと言いませんが、分かった気でいっぱしのプロみたいなことを言われてもね……。

 サーバの性能が上がっているから、サーバが助けてくれるなら良いんじゃないの?と思うことも増えてきて、説得するのが面倒になって流されることも多くなってきましたが、まあ、理解できてないと「艦これ」程度のアクセスで止めてしまう。

 プロならできるべきですね。

 つづく