SQLer 生島勘富 のブログ

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

MVCがなぜ間違っているか？

WebシステムにMVCを適用するのは間違っています（正確にはインターフェースが足りません）。

paperface.hatenablog.com

このように思考停止したエンジニアにはわからないかもしれませんが、もう一度解説してみましょう。

目次

そもそもMVCとは？

MVCができた頃

MVCは、スタンドアロー向けのプログラムがあまりにぐちゃぐちゃだったので、「違う機能は疎結合にしよう！」という考えで作り出されました。 1980年代の頃のお話です。 それ自体は正しいです。

歴史を振り返ってみましょう

Webシステムを「MVCに適用させよう」と言われ出したのは2000年頃からです。

この辺りを理解するには、少し歴史を知る必要があります。

1995年頃（私の新人時代）にJavaが生まれましたが、当時はJavaをWebで使うのはAppletでした（はっきり言って使い物になりませんでした）。 1995～2000年にかけて、Y2K（2000年問題）が起きました。 COBOLでは、西暦を2桁で保存することが一般的だったため、「2000年以降システムが動かなくなる」という問題で、システムを改修するか、クラサバで作り直すか、いずれかが選択され、クラサバシステムがたくさん作られることになりました。 このときRDBMSがメジャーになり、人材が足りませんからCOBOLerが大量に流入してきました。

イベントドリブンが分からないCOBOLerが、仕様を決めたり、コードレビューをするような上の立場にいるわけです。 本当に混沌とした時代でした。

このときCOBOLer達は、「RDBMSはストレージ、SQLはファイルの読み書きをする方言」とやり過ごしました。

イベントドリブンが分からない上に、「俺が分からんから配列は禁止」とか言い出す人がたくさんいましたから、私たち分かっている世代は、まずはSQL以外の不毛なバトルを繰り広げる必要があり、SQLまで手が回らなかったのです。

結果、「RDBMSはストレージ、SQLはファイルの読み書きをする方言」というのが文化として残ってしまったのです。

2000年以前は、ブラウザがCSSに対応していませんでした。 HTML自体がデザインを分離できていませんでしたから、WebシステムでMVCを唱えることも事実上無理でした。

2000年になりドットコムバブルの中で、Servlet、JSP、CSS（が使えるブラウザ）などが生まれます。

Viewを分離することができるようになって、「MVCにしようぜ」という流れができるのです。

この流れ自体は間違いではありませんが、残念ながら、「RDBMSはストレージ、SQLはファイルの読み書きをする方言」というCOBOLerの遺伝子は残ってしまいました。

インピーダンスミスマッチとORMの登場！

MVCを適用していく中で、「インピーダンスミスマッチ」ということが表面化されていきます。

インピーダンスミスマッチ

これを解決するために、ORMなるものが開発されました。

ORMができた

さて、この図をしっかり見てみましょう。Viewには、CSS、JavaScriptなど、Webサーバ（ブラウザ）が管轄するものと、APサーバが管轄するものがあります。

Controlは、APサーバに限定されます。

Modelは、APサーバと、DBサーバに分かれますが、なぜかSQLはAPサーバの管轄になっています。 PHP（Eloquent）Ruby（ActiveRecord）などで覆っても、私から見れば、オブジェクト指向言語的には「異物」以外に表現しようがありません。 ORMを使ったソースは、オブジェクト指向言語的にものすごく汚いソースにしか見えないのですが……。 それに、ORMの機能をViewに当てはめて考えれば、「PHPで書けばJavaScript、CSSを自動生成してくれる」ぐらい奇妙な構造になっています。

これを奇妙と思わない人は、「RDBMSはストレージ、SQLはファイルの読み書きをする方言」というCOBOLerが作った文化を引き継いでいるからです。

こうあるべきでしょう？

こうあるべき（APIが必要）

まったく逆のアーキテクチャであるSQLをAPサーバで実行されるModelに入れるということに無理がある。 オブジェクト指向を正しく理解していれば、さらに、「違った機能は疎結合にする」というMVCの理念を理解していれば、DBサーバにインターフェースを置く必要があると分かるはずです。

DBサーバにインターフェースを置くとして、RDBMSのメモリー空間でインターフェースとして扱えるのはストアドプロシージャになります。 つまり、「APIとしてストアドプロシージャを使うべき」という結論になる訳です。

そうならないのは自分のスキルで、「できる・できない」からスタートしているからですよね？

ストアドプロシージャにすることに問題はないの？

もちろん、問題はあります。 すべてストアドプロシージャにするには、現状の一般的なエンジニアが持っているSQLを書くスキルでは全く足りません。

ほとんどのエンジニアは、SQLにおいてStaticおじさんよりひどいからです。

sikushima.hatenablog.com

この問題はSQLのスキルを端的に測れます。 基本構文の問題ですから、これを間違う人でRDBMSに係ってプロとしてお金を貰うのは詐欺と言っても良いけれど、いろんなところでやってもらいましたが正解率は5％ぐらいです。Staticおじさんを批判している本人が、Staticおじさんよりはるかに問題の「LEFT JOIN決め打ち」をやっています。

sikushima.hatenablog.com

世界10億サイト（ページじゃないよ）で使われているWordPressの変換を見ても分かる通り、日本だけではなく世界的に分かっていません。

Fetch してグルグルしない

スキルが足りない人にとっては、ストアドプロシージャにすると言うと、

「ストアドプロシージャみたいな完成度の低い言語（めっちゃ低い）で Fetch してグルグルしたらめっちゃ大変……」

と考えるのでしょう。

確かにスキルが足りない人が、「Fetch してグルグルする」ようなことをしたらシステムは一瞬で破綻します。

弊社で基幹システムをすべてストアドプロシージャで製造すると、もちろん、まったくFetchしないわけではありませんが、Fetchするのは1％ぐらいです。 SQLのスキルがあれば、Fetchしないといけない処理はほとんどありません。

例えば、基幹システムでFetchする機能には、請求書を作る機能があります。 と言っても、得意先コードを指定して請求書を作る処理はFetchする必要はありません。 しかし、締め日を指定して一括で請求書を作る場合、指定された締め日の得意先の一覧を取得し、先ほどの得意先コードを指定して請求書を作るストアドプロシージャにFetchして渡します。

そういうものを数えても1％ぐらいです。

「得意先コードを指定して、1件分の請求書を作るだけならFetchしないでSQLで処理できる」

というスキルが必要です。 そのスキルが猛烈に高いかというと、現状では高いでしょう。 しかし、弊社のセミナーでは、プログラミング未経験の事務員さんが3日もあればできるようになります。

文字列連結は要らない

「ストアドプロシージャみたいな完成度の低い言語（めっちゃ低い）で文字列連結してSQLを構築するなんて……」

と思う人もいるのでしょう。 先ほどの請求書を作成するSQLなど、とても複雑な処理をしていますが、文字列連結でSQLを構築することは弊社ではほぼありません。 こちらに詳しく書きました。

sikushima.hatenablog.com

どちらが極端ですか？

「すべてストアドプロシージャにするべき」というと、極端だと言われますが、どちらが極端ですか？

まったく真逆のアーキテクチャーであるSQLを、オブジェクト指向言語の中で動的生成する。 冷静に考えれば、そんな極端なことはありません。

ほとんどのRDBMSは、オブジェクト指向言語のC++で書かれています。 CPUはSQLなんて理解できませんから、オブジェクト指向言語で書かれたRDBMSが翻訳をしているのです。

翻訳された結果は、「実行計画」として見ることができます。

同じSQLでも、実行計画が変わるSQLの例。

実行計画１
実行計画２

実行計画をオブジェクト指向言語に直すとこんな感じになります。

Javaにすると

SQL → 実行計画 → オブジェクト指向言語

という変換ができるようになれば（というか、それができない人がエンジニアで良いのか？）

オブジェクト指向言語 → SQL（自動生成）→ オブジェクト指向言語（自動生成）

という、実行時に「車輪の再開発」が起きていても「便利だ」と極端なことを考えるはずがないです。 そんなことを考えてしまうのは、「SQLのスキルが圧倒的に足りない」ことを証明しているだけの話です。

分離開発するべき

SQLとオブジェクト指向言語は、真逆のアーキテクチャです。 私はどちらも同じにできますが、それでも同時に考えるのは困難です。

これだけSQLができないエンジニアがいるのですから、SQLを担当するエンジニアと、オブジェクト指向言語を担当するエンジニアを分けた方がよい。 ストアドプロシージャにすればそれが可能になるのです。

SQLと、オブジェクト指向言語を担当するエンジニアが分かれれば、オブジェクト指向言語を担当するエンジニアはSQLを知る必要もない。

現状では、全員がSQLを理解している必要がある。 そんな極端なことはやめた方が良いでしょう。

プロローグ版「SQLの苦手を克服する」オンラインセミナー

以前より行っておりました「SQLの苦手を克服するセミナー」のオンライン版を作りました。

このセミナーは4時間以上かかるため土日の開催になるのですが、前半の内容をプロローグ版として9月30日（水曜日）19時より1時間30分～2時間程度で開催いたします。

オンラインセミナーになりますから、遠方の方もご参加ください。

よろしくお願いいたします。

api-first.connpass.com

SQLで消費税の処理

こちらで書いた記事のご意見が気になったので少し。

目次

ご意見

gihyo.jp


> 99%は同意するが一つ言わせてほしい。SQLの最大の弱点は時間の扱いに弱いことで、RDBの理論的基盤の数学が時間を考慮してないからしょうがないとはいえ消費税みたいな時間で変化するマスタへの配慮が無さすぎる。

99%は同意するが一つ言わせてほしい。SQLの最大の弱点は時間の扱いに弱いことで、RDBの理論的基盤の数学が時間を考慮してないからしょうがないとはいえ消費税みたいな時間で変化するマスタへの配慮が無さすぎる。 - turanukimaru のブックマーク / はてなブックマーク

とのことですが、Window関数以前は、前後のレコードの処理が考慮されていませんでした。現在は、MySQLでもWindow関数が実装されたので、ほとんどの問題は解決されたと思います。

しかし、消費税率の変更程度であれば、通常はテーブルとデータを整備することで処理が可能です。

テーブル設計における消費税率について

消費税で考慮すべき点は、税率は、3種類（通常、軽減税率、非課税品目）あり、顧客（販売相手）に2種類（課税対象、課税対象外）あることにあります。

税率については、商品のカテゴリーに対して税率種別を付けるべきでしょう。

マスタ類の設計例

マスタ類の設計例

トランザクション類の設計例

トランザクション類の設計例

消費税率の登録の仕方

消費税率の登録の仕方

非課税品目や、消費税がなかった時代のデータまで税率をゼロで登録しておくことがミソです！

SQLで処理するとこうなる

上のようなテーブル設計にして、消費税率を登録しておくと、SQLで処理するときは以下のようになります。

SELECT 
    -- 中略
    , ss.単価 * ss.個数 * tm.税率 AS 消費税額
    -- 中略
FROM 
    売上テーブル sm
    INNER JOIN 売上明細テーブル ss
        ON sm.ID = ss.売上ID
    INNER JOIN 顧客マスタ cm
        ON sm.顧客ID = cm.ID
    INNER JOIN 商品マスタ pm
        ON ss.商品ID = pm.ID
    INNER JOIN 商品分類マスタ pc
        ON pm.商品分類ID = pc.ID
    INNER JOIN 消費税率マスタ tm
        ON sm.売上日 >= tm.適応開始日
        AND sm.売上日 <= tm.適応終了日
        AND pc.消費税区分ID = tm.消費税区分ID
WHERE
    -- 以下略
;

CASE式すら入らないということに注目してください。

ゼロにするときには、ゼロを掛ければ良い

このブログでも、何度か書いていますが、

・ゼロを得たいときにゼロを掛ける
・変化なしを得たいときにゼロを足す、あるいは1を掛ける

などの処理は、自然言語では行われない表現ですが、数式にするときに工夫すべきです。
つまり、仕様を決めるとき、

「顧客が課税対象のときは税率区分の税率を適用し、課税対象外（海外企業など）のときは消費税額をゼロとする」

などという会話がなされます。それをそのままプログラムにすると、とても複雑な処理が必要になります。
消費税課税区分（名前は課税係数の方が良いかも）を0と1にしておけば、

単価 * 個数 * 税率 * 消費税課税区分

という単純な数式で、すべての組合せの処理が可能になります。

トランザクションに税率を入れるか？

税率は通常は導出項目になります。
しかし、税率が変更になる前後で、特別に前の税率で処理して欲しいなどというイレギュラーなことを言い出す可能性があるときには、トランザクションに非正規化しておいた方が良いでしょう。
（売上日を前日にすべきですけどね）

MySQLでサブクエリがどうしても遅いときの対処法

他のRDBMSでも起こりますが、特にMySQLでは、サブクエリを使うとどうしても遅いときがあります。
そんなときの対処法は主に2点あります。

目次

テンポラリーテーブルを使う方法

DROP TEMPORARY TABLE IF EXISTS tmp1;

CREATE TEMPORARY TABLE tmp1
-- (PRIMARY KEY(id)) 必要に応じて主キー 
-- (INDEX(id))       インデックスを生成する 
AS
SELECT *
FROM customer; -- 使いたかったサブクエリ

-- 遅かったクエリのサブクエリを tmp1 にして書き換える。

DROP TEMPORARY TABLE IF EXISTS tmp1;

文字列連結でSQLを生成する方法

例えば、以下のようなとき、col1のインデックスを使った方が早いときでも、ヒントを入れても使ってくれないことがある。

SELECT * 
FROM table1
WHERE col1 IN (SELECT colx FROM table2 WHERE xxx)
;

対処法

SELECT colx FROM table2 WHERE xxx
;

を先に実行し、結果から

SELECT * 
FROM table1
WHERE col1 IN (1, 3, 5)
;
SELECT * 
FROM table1
WHERE col1 IN ('JPN', 'USA')
;

などのSQL文を生成する。

配列が使えるDBでも、配列を使ったらインデックスを使わなかったり（古いバージョンの記憶なので現在はどうか？）するときにも有効です。

ゲームを題材に学ぶ 内部構造から理解するMySQL

以前、Software Design の特集記事を執筆しましたが、

sikushima.hatenablog.com

技術評論社のページですべて無料で公開されています。

gihyo.jp

ぜひ、ご覧ください。

「SQLの苦手を克服する」セミナー

api-first.connpass.com

お時間が合いましたらご参加ください。

拙著『SQLの苦手を克服する本』のご案内

SQLの苦手を克服する本
SQLの苦手を克服する本

https://www.amazon.co.jp/gp/product/4297107171

ブログで書いている内容を、もっと分かりやすくしたものです。

例えば、以下の記事の内容も、分かりやすく解説しています。

「SQLを文字列連結して作る必要はない」

sikushima.hatenablog.com

私はどうしても、「自分が理解している範囲をエンジニアなら理解しているだろう」という想いで文章を書いてしまいます。 しかし、それでは難解で理解できないエンジニアが多いということで、開米瑞浩さんに第三者にもわかりやすくリライトしていただいています。

ideacraft.jp

是非、ご購入いただければ幸いです。