Skip to content

Instantly share code, notes, and snippets.

@sawadyrr5

sawadyrr5/gist:39bf93f443dc861d47116e27fc8d5a68

Created March 26, 2020 13:59
Show Gist options
  • Save sawadyrr5/39bf93f443dc861d47116e27fc8d5a68 to your computer and use it in GitHub Desktop.
Save sawadyrr5/39bf93f443dc861d47116e27fc8d5a68 to your computer and use it in GitHub Desktop.
Download ZIP
APIファースト開発勉強会に参加してきた
Raw
gistfile1.md

11/5に @kantomi さん主催の勉強会に参加してきたので, 得られた知見などをメモしておきます. 個人的にはたいへん有意義な勉強会でありがとうございました. 機会があればまた参加したいと思います.

概要

  1. UI, APIから設計開発をスタートする"APIファースト開発"によってDB設計の確定を後回しにし, DB設計変更による手戻り(=炎上)を防ごう.
  2. ビジネスロジックはSQLで書こう. RDBMSはいろんなことを考慮して実行計画を考えてくれる. O/Rマッパー逝ってよし.
  3. SQLで書けるかどうかはまずExcelで書いてみて考えよう. ExcelでできればSQLでできる.

APIファースト開発のメリット

  • プロジェクトが炎上してデスマに陥るのは, ひとえに"DB設計"に手戻りが発生するからである.

  • 逆にUIとかの変更であれば, デザイナは大変だが炎上・デスマにはならない.

  • DB設計が変更されると影響範囲が大きいので, 修正要望→影響調査→修正適用→修正要望 というループに陥る. ここにドキュメンテーションが加わって死ぬ.

  • DB設計が甘い原因は要件定義が甘いからである.

  • 要件定義が甘い原因は, 最初はちゃんと考えて定義したつもりでも どうしても詰めきれない部分というのもそれなりにあるもので, それがウォーターフォール型開発だと目に見える完成形がユーザの手元に届くのが遅くなりがちであるため, いざ動くものが出来上がってから"こんなはずじゃなかった"になりがちなのである.(そういう意味では, アジャイルだろうが"こんなはずじゃなかった"率は大して変わらないのかもしれない. 要はそれが早くわかるか, 遅くわかるかという違いか.)

  • ならばDB層をスタブで柔軟に変更可能にしておきUI, APIから先に作ってしまおう. UIが出来ればユーザも操作イメージがつかみやすく, 隠れた要件が出てくることもある. そしたらAPIを決めよう. APIができたらそれに合うようにDB層を固めよう. というのがAPIファースト開発である.(というのが私の理解)

  • んで、スタブ作成用のSQLはExcelで書いた仕様書からダミーデータもろとも自動生成できるようにしておこう.

APIファースト開発のデメリット

  • DB層のバージョン管理が困難である.
  • SQLの教育コストがかかる.
  • ウォーターフォールっぽくないので既存のマネジメント(プレイングマネージャーにならないといけない)や経営層(工数が下がりすぎて商売しづらい)から抵抗を受ける. が、これってユーザーメリットじゃね？

O/Rマッパー逝ってよし

  • O/Rマッパーは手続き型言語の枠組みの中にSQLを取り込もうとするものである. が、そうすると"集合論的なデータ操作ができる"というSQLの特長も隠蔽されてしまう.
  • O/Rマッパーは結局最終的にはRDBMSに対してSQLを投げるので, そもそもSQLがわかるならO/Rマッパーなんて補助輪に頼らない方が話が早い.

ストアドプロシージャ, ファンクションの良いところ

  • ストアドプロシージャ, ファンクションはプリコンパイルされ, その際に依存オブジェクト関係が把握される. SQL Server だと以下のようなクエリで簡単に依存関係を調べることができる. テーブル修正の際などにほんと便利.(ほかのRDBMSでも調べられる.)
sys.dm_sql_referenced_entities

sys.dm_sql_referencing_entities
  • 逆に生のSQL文をそのまま投げていたり. SQL文を動的に生成しているような場合はプリコンパイルが効かなくてつらい.
  • ストアドプロシージャ原理主義者はすべてのSQLをストアドプロシージャで包む. 慈悲はない.

ストアドプロシージャ, ファンクション利用に対する反論への反論

  • 「DBサーバでストアドプロシージャを使うと遅くなる」 DBサーバの瞬間的な負荷については高まるものの, 負荷=使用率×時間とすれば, APサーバとの通信時間の短縮などのメリットはある. DBサーバからちょっとずつ読んでAPサーバで処理したり(APサーバの資源占有時間長期化)、どかんと読んでAPサーバでぐるぐる回したり(トラフィック増大)するよりは, DBサーバ上で答えを出してからAPサーバに渡したほうが 双方のサーバはもとよりネットワークトラフィックも抑えられる.
  • 「DBサーバは集合志向なので四則計算は慎むべき」 DBサーバのCPUが何百万MIPSだと思ってるの?四則計算くらい誤差.

ExcelVBA, SQLの小ネタなど

  • ExcelVBAでセル位置指定の際に以下のように書くのは重罪. 列位置変更で即死する.
' Bad example
Range("B" & 1)
  • 列位置変更に柔軟に対応するためには、列のヘッダーにあたるセルに名前を付け、データを格納するセルはヘッダーからのオフセットで指定するのがよい.
' Good example
Range(セル名).Offset(1,0)
  • SQLで任意(省略可能)なパラメータを含めて検索条件とするときには, 条件を以下のように書くとよい.
/* para1が必須でpara2, para3が省略可能とする */
WHERE
  col1 = para1
  AND ( col2 = para2 OR para2 IS NULL )
  AND ( col3 = para3 OR para3 IS NULL )
  • 実際にpara2がNULLだったとき、式は以下のような状態になる. このとき, ORの右辺はNULL IS NULLだからTrueである.
WHERE
  col1 = para1
  AND ( col2 = NULL OR NULL IS NULL )  --ORの右辺はTrue
  AND ( col3 = para3 OR para3 IS NULL )
  • OR演算ではいずれかがTrueであれば結果はTrueなので, 右辺がTrueと評価された瞬間にカッコ部分全体もまたTrueとなる. すると, para2に関する条件を指定している行自体がただのTrueと等価となり, 検索結果に影響を与えないこととなる. 実際には, RDBMSが構文解析時に上記と同様の解釈を行い, ユーザーが書いたクエリをそれと等価なものに書き換えて実行している.
WHERE
  col1 = para1
  AND ( col2 = NULL OR True )  --この行自体がTrueとなり演算結果に影響しない
  AND ( col3 = para3 OR para3 IS NULL )
  • WHERE句で条件抽出を行う際には、True=1, False=0, ANDは論理積(掛け算)、ORは論理和(足し算)として考えると良い. 当然, 論理演算においても掛け算は足し算に優先する.

RDBMS毎の違い

  • RDBMSはSQLを解釈し実行計画を作ってくれる賢い子だが, プロダクト毎に挙動には差がある. (筆者がSQL Server環境なのでそれしかメモってません.)

SQL Server

  • ストアドプロシージャを実行する際にOPTION RECOMPILEを指定すると, プリコンパイル結果をキャッシュしなくなる. 実行計画が変なパラメータで作られてしまったときの状態変化を治療するのに使う.

クエリオプティマイザの働き

  • 単純なSELECT文でも絞り込みの範囲によってインデックスを使うか, 使わないかをオプティマイザが決定している. つまり, スキャン範囲がテーブルのごく限られた部分であればインデックスによる絞り込みが効くと判断しインデックススキャンを行うし, テーブルを広範囲に舐めにいくような処理の場合はそもそもインデックスを使わずフルスキャンとして実行する.
  • インデックススキャンを行う場合, まずインデックスを見る->次にリーフにあるデータを読むというステップになるので, それなりのコストはかかるということ.
  • このあたりの最適化については, RDBMSが作成する統計情報に基づいて決定されている.

SQLの書き方

  • SQLの元はSEQUEL(Structured English Query Language)といい, つまり"構造化された英語の問い合わせ言語"という意味. 実際英語っぽいかというと微妙だが, 問い合わせるための言語として"何がほしいか"をメインに考えると良い.
  • 反対に, "どのように処理するか"という問題はすべてRDBMS(クエリオプティマイザ)がやってくれるので気にしなくて良い.
  • つまり, ゴール(最終的にどういうデータがほしいか)をイメージするのが重要であり, イメージ作業をしやすくするのがExcelである. Excelで表現できるようであればSQLにすることができる.

1行での計算になるか, 複数行にまたがる計算になるか

  • SQLにおいて, 基本的に算術計算は横につながるレコードの世界の中で行える. 必要なデータが1行の中に収まっていさえすれば, あとは四則演算とCASE WHENで何とかなるということである.
  • 逆に, 計算対象行以外の行も含めた計算結果を求めたい場合は, OLAP関数やサブクエリを使うことになる. 例えばSUM() OVER PARTITION BYなど.
  • パターンによって計算パラメータが変わるようなケースについても, 手続き型言語ではIF分を並べて表現していたところ, SQLでは横方向にパラメータさえ連結してしまえばSELECT文の中で計算してしまうことができる. 横方向にパラメータを連結するために, IF文の内容をパラメータテーブルに展開する.
  • ロジックが複雑になるようであればパラメータを引数とするファンクションに切り出してもいい. ファンクションはプリコンパイルされるので呼び出しまくっても問題ない.
  • なお, DBの最小I/Oサイズはブロック単位(16KB)なので, 16KB以下のテーブルだと常にフルスキャンになる.

DBチューニングのキモ

  • パフォーマンスチューニングのキモは"一筆書き"を目指すことである. すなわち, I/Oコストの掛かるテーブルスキャンを必要最小限の回数で終わらせるようにするのが目標である.
  • チューニングによって好ましい動作になっているかどうかは実行計画を見て確認することができる.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment