SQLアンチパターンNight part2に参加してきたまとめ #sqlap_night

公開日：2017/07/11 最終更新日：2017/07/12

オライリーから出ているSQLアンチパターンという本について語る夜第2弾が2年ぶりくらいに開催されたので、SQLアンチパターンNight part2に参加してきたまとめです。

2年前のまとめはSQLアンチパターンNightに参加してきたまとめ #sql_ap_nightへどうぞ。

State of SQL antipatterns in 2017

@t_wada様

スタンド名：ワイルドサバンナ
1週間前位で突然増刷の申告が来るらしいので本書に間違っている点があったら教えてください。
- 指摘点を教えてくれたら増刷版を無料贈呈

SQLアンチパターン形式のおさらい

本書の構成はすべて同じ構成になってます。
名前
- カタカナ表記でやれば異物をもった名前で輸入して目立たせている
- チームの会話でアンチパターンが飛び交うことで危険を察知しやすくなっている
アンチパターンの見つけ方として会話特集が入ります
アンチパターンを用いてもいい場合も記載されているのがパターン本と呼ばれる所以
ナイーブツリーの解決策について
- ジェイウォークでカンマ区切りは使うなというのに経路列挙で/で区切るのは何故いいんだというツッコミポイントもあります
- DBエンジンによっては経路列挙をDB側でサポートしている場合もあります

2017年におけるSQLアンチパターン

DB特化型の父と共にSQLアンチパターンを書きましたが、そんな父は70歳になります。

再掲

2017年より前、SQLの本は、入門書と超上級者向けの本しかなくて、上級者と初級者の間がない状況だった
最近ではSQLアンチパターンも含めて中級本も出てくるようになりました
ここ２〜３年は豊作で、ビッグデータ分析・活用のためのSQLレシピの本は解析系のSQLが学べておすすめです

<br />

プログラマのためのSQLグラフ言論はSQLでツリーを扱うというニッチな内容について言及した本。売れてるかどうかは不明。

<br />

アンチパターンのお焚き上げ

ここ数年でCommon Tableのようなアンチパターンでなくなったものもでたので紹介
アンチパターンは第1部が一番評価が高い
- SQLの書き方のアンチパターンと思われることが多いが、実は設計のアンチパターンになる
この本に書いてあるからといっているとそのうち古いことになってるかもしれない

Common Table Expression(SQL19999)

実装が終わればこのアンチパターンはほぼ用済みにはなる
再起SQLがあっても今後も使うかもしれない
参照が多い場合は、再起URLよりも、NestesdSetが有利。（更新には弱いが）
MySQL8.0でCTEが来ます！
- cf: MySQL 8.0 Lab版: MySQLの (再帰)共通テーブル式(CTE)
- MariaDBの10.2には既に入ってます
がっつりチューニングしないといけないときはNestedSetなどを使っていけばいい

IDリクワイアド（とりあえずID）

関係性の発生と考えよう
交差テーブルと考えるとIDいらないとなるが、よくよく設計すると、関係性が発生したのはシステムとしては重要なイベントになる
発生したイベントをもとにデータをたどる
- だんだんと関係性を意味するエンティティやトランザクションレポートに格上げされていく
- つまり関係が発生した
- 単なる交差テーブルでもなくなってくる

EAV(Entity Attribute Value)

すぐには時代遅れにはならないが、時代遅れの候補に上がっている
EAVは属性値と属性名をテーブルに格納してしまうというテーブル
本当に無限のものか、有限のものかを考えるべき
- アンケートは無限になるが、バグや機能要求などはちょっとずつ違うだけ。継承関係にマッピングできたりする
オブジェクト指向とマッピングを使っているのがシングルテーブル継承、具象テーブル継承、クラステーブル継承、シリアライズLOBとなる
共通部分でない無限の値があるのは構造が確定してないものとしているものがあると思うが、確定しているものは設計してしまう
なぜお焚きあげの対象か？
- SQL2016より、JSON関数群が増えて、標準化されてくるようになった。
- JSON型は定義されていなくて、アクセスの方法だけ定義されている

ファントムファイル

物理ファイルの仕様を必須と思い込む設計
- ファイルパスだけ格納して、画像は直接DBにはいれない
- トランザクションによる整合性がとれないのでアンチパターンになっている
- 99%の人はこの設計に反対する人が多い。しかし言いたいことは整合性や、ロックやトランザクションについて考えているかという事
今はS3にぶっこんでRDBにはS3のパスをいれればよい
- ただし、整合性やトランザクションはちゃんと考えないといけないのは変わらない。

アンギュアスグループ

MySQLやSQLiteに関して、曖昧なグループにしているとDBによっては期待値がとれない場合がある
MySQLはデフォルトだと許してしまう点が、kamipo traditionalの設定値を使うことで回避することができる
Listagg(SQL2016)
- group_concatのような雑なソリューションが定義されるようになった

SQLインジェクション

2017年にサニタイズが許される時代でない。
- 基本的にプリペアードステートメントやホワイトリストを使う
- 基本的にこの２つの方法でやる
本来のSQLでプリペアードステートメントが使えないところでSQLインジェクションが狙われたりするので、何を使っていいか、使っていけないかを意識して作りましょう

本書の意義について

整理された失敗を書かれているので、昔会社で踏んだ失敗談を知見の共有に寄与できるので、とても社内読書会に向いてます。
この本には順番がなく、独立しているので、どこから読める。
- 読書会は回によっては人が来れなかったりして人がだんだん減ってしまうが、いつ参加しても問題ないという本の構造はとても読書会に参加しやすい。
エッセン集やパターン集など、どこから参加してもOKという本を選んで読書会をしたほうが成功しやすい
アンチパターンを名前で読んで議論しましょう！

FAQ

時代の背景として削除もイベントとして捉えているが、削除フラグはアンチパターンと思っているがどうか?

私も思います。
過去に第26章のとりあえず削除フラグというスライドを作りました

SQLアンチパターン幻の第26章「とりあえず削除フラグ」 from Takuto Wada

削除フラグにした方がいいソリューションはあると思うが、全テーブルに削除フラグはよろしくない

再起SQLについてCTEを使ってるSQLがあるが、withじゃなくてwith recursiveじゃないか？

本文が間違っている。次の判で直します。

153pの5行目にMySQLの演算子のdistinct fromも間違っていないか？

次の判で直します

RDBでアンチパターンになっていても、フレームワークでEAVをサポートしていて使っているのは、アンチパターンになるか？

基本的にはcake phpなどのライブラリよりもDBのデータのほうが寿命が長い。
フレームワークやライブラリがサポートしているからといっていってしまうと、そのライブラリを使って繋いでるデータならいいが、管理用のシステムからとかのアクセスなどで、他のところでやりにくい形になってしまう。
データの整合性をつけるのが基本的には難しい
無限のバリエーションか、有限のバリエーションかを考える
国際化に関しては自分でコントロールできるので無限ではない
できるかぎりEAVに行くのは思いとどまりたい

ファントムファイルについて、画像をBLOBに入れる場合、事例としてsecureの画像をDBにいれたいなど、他の事例はあるか

トランザクションの要件なら必要
ファイルシステムにアクセスできないといった特殊な状況にはRDBにいれるしかない
基本的には2017年現在では特殊な事情がなければ避けたい

本書のポリモーフィックについて、参照を逆にする解決策について、本来あるべき関連が引っかかった。次の交差テーブルにつながるのか？

繋がっています。

25章目のパターンはどうか?

とてもよい。運用設計などを扱っているところなので、ちょうどこの本の足りないところを補ってくれたと思います。

**ここからはLT発表になります。**

やってはいけない空振りdelete

@nii_yan様

やってはいけない空振りDelete from Yu Yamada

idとnameがあるテーブルに対し、whereでid=2（データあり）とid=100（データ無し）と空振りするdmlでロックのかかり方が違う
存在しないidをdeleteしてる最中にデータを入れようとするとINSERTが行えなかったりする
MySQLだと存在しないindexに対してネクストキーロックという機能によりこの問題が発生する
中途半端なロックでデットロックも起きます
片方で存在しないidをdeleteし、おなじ存在しないidをdeleteした状態で、同じ値をinsertしようとするとデッドロックになりますので注意

みいつけた！SQLアンチパターン

@Kyabatalian様

余談ですが、銭湯にSQLアンチパターンがおいてありましたが、ビチャビチャになってました。
アンチパターンをブログに書いていました。
- 逃げ恥に例えてアンチパターンを書いてみました
Native Trees
- タグという機能があって、仕様上3階層までセーフだったが、階層が足りないので増やせといわれた
- with recursiveを使って解決！
Keyless Entry
- 外部キーは一切使ってません。頑張ります
EAV
- カテゴリと選択肢を管理者が作成できる機能がある
- 今後この機能は削除します
Index Shotgun
- 参照されるテーブルに20くらいインデックスがある
- pg_stat_user~を使ってどんどん消している
Spaghetti Query
- QueryBuilderやEntityFrameworkを使っていてよくわからない状態になっていたが、Dapperを使ったり、要件の精査やDBのリファクタリングを行っている
Implicit Columns
- サブクエリの中がselect *状態
- つらい
Diplomatic Immunity
- ER図などなくて、DBのリファクタリングをやらない
  - ユーザの可用性が厳しいのでリファクタリングしづらい
- 少しずつドキュメント化している
言い訳
- 最初はオフショアだったが、リニューアル無しできた。
- DBAいない
- 想定外に成長した
最近は丁寧に設計している

イミュータブルデータモデル（実践編）

@kawasima様

Step1エンティティの抽出
- T字型ER図の設計をしていたがとてもむずかしい
Step2エンティティの分類
- テーブルに対するupdateを少なくするというのがポイント
- エンティティをイベントリソースに分類する
  - イベントは属性として日時を持つかどうか
Step3イベントエンティティは１つの日時属性しか持たないようにする
- 縦長のテーブルに混ざっている日時の属性をばらしていく
Step4リソースに隠されたイベントを抽出する
Step5 非依存リレーションシップを交差エンティティにする

実践する上で厄介なお話

世代を持つデータ
- 商品の価格が日によって変わるものなど
- イベントは更新不可の性質を利用する。
- イベントの予実（実績）として別テーブルにもって、予定日が来たら実テーブルの値を反映させる
ワークフロー的なやつ
- イベントのつながりは業務フローが変わると変わるかもしれない
- 最新の状態を見つける必要がある
- スーパータイプを作ってそこでステータスを管理するようにする。そこに日時属性を持つ。
世代もったリソースとイベントをジョインしたい
- cf: 日付FROM-TOのデータを穴埋めする