SQLアンチパターン　一言で表現してみた ～輪読会まとめ～

#はじめに
以前に引き続き、社内で輪読会を実施しました。
※前回の記事は[こちら](https://qiita.com/payawoooow/items/1d653c4e23a7a262d2a6)

今回は、[SQLアンチパターン](https://www.amazon.co.jp/SQL%E3%82%A2%E3%83%B3%E3%83%81%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3-Bill-Karwin/dp/4873115892/ref=sr_1_1?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&dchild=1&keywords=SQL%E3%82%A2%E3%83%B3%E3%83%81%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3&qid=1630757794&sr=8-1)を題材として取り上げました。
前回の[GoF本](https://www.amazon.co.jp/%E5%A2%97%E8%A3%9C%E6%94%B9%E8%A8%82%E7%89%88-Java%E8%A8%80%E8%AA%9E%E3%81%A7%E5%AD%A6%E3%81%B6%E3%83%87%E3%82%B6%E3%82%A4%E3%83%B3%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3%E5%85%A5%E9%96%80-%E7%B5%90%E5%9F%8E-%E6%B5%A9-ebook/dp/B00I8ATHGW/ref=sr_1_1?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&dchild=1&keywords=%E3%83%87%E3%82%B6%E3%82%A4%E3%83%B3%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3+JAVA&qid=1630757902&sr=8-1)もそうですが、各章が独立に書かれた本は、輪読会で取り上げやすいです。

以前と同様に、**各SQLアンチパターンを一言で表現してみる**、という取り組みをしてみました。
狙いとしては
・「一言で表現する」という要約の過程で、さらに理解を深めることができる
・一言であれば後から思い出しやすい
という感じです。

今回は、アンチパターンについて焦点を当て、その問題点や解決策については記載しておりません。
**アンチパターンが採用されないことが最も重要であり、そのためにアンチパターンをいつでも思い出すことができる状態にする**、という方針です。
解決策を知りたい方は、ぜひ[書籍](https://www.amazon.co.jp/SQL%E3%82%A2%E3%83%B3%E3%83%81%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3-Bill-Karwin/dp/4873115892/ref=sr_1_1?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&dchild=1&keywords=SQL%E3%82%A2%E3%83%B3%E3%83%81%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3&qid=1630757794&sr=8-1)をご購入ください。

#参考図書
[SQLアンチパターン](https://www.amazon.co.jp/SQL%E3%82%A2%E3%83%B3%E3%83%81%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3-Bill-Karwin/dp/4873115892/ref=sr_1_1?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&crid=Q8OQNO8LS3IT&dchild=1&keywords=sql%E3%82%A2%E3%83%B3%E3%83%81%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3&qid=1632392289&sprefix=SQL%E3%82%A2%E3%83%B3%E3%83%81%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3%2Caps%2C269&sr=8-1)

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1665928/ab0a2f82-3824-de95-bc5b-428147821326.png" width=40%>

超オススメです。ただし、中級者向けなので、基本的なSQL構文やRDBについて勉強してから取り組むのがよいと思います。

#SQLアンチパターン　一言で表現してみた
##データベース論理設計のアンチパターン##
####１．ジェイウォーク(信号無視)　＜カンマ区切りでデータを格納する＞####
一つの属性の複数の値を格納したいとき、
VARCHARで文字数長めのカラムを用意する。その中に複数のデータをカンマ区切りで格納する。

```SQL
CREATE TABLE Products ( 
 product_id SERIAL PRIMARY KEY, 
 product_name VARCHAR(1000), 
 account_id VARCHAR(100), -- カンマ区切りのリスト
```

|製品ID(product_id)|製品名(product_name)|アカウントID(account_id)|
|:---|:---:|--:|
|0001|Phone TypeA|AAA,BBB,CCC|
|0002|Phone TypeB|BBB,DDD,EEE,FFF|

####２．ナイーブツリー(素朴な木) ＜親のみに依存する木構造＞####
木構造を表現したいとき、
同テーブル内の、自身の親のIDを指定するカラムを定義する。

```SQL
CREATE TABLE Departments ( 
 department_id VARCHAR(4) PRIMARY KEY, 
 department_name VARCHAR(10), 
 upper_department_id VARCHAR(4), ---同テーブルのdepartment_idを参照する
 FORREIGN KEY (upper_department_id) REFERENCES Departments(department_id)
```

|部門ID(department_id)|部門名(department_name)|上位部門ID(upper_department_id)|
|:---|:---:|--:|
|1111|営業１課|1110|
|1112|営業２課|1110|
|1110|営業部||

####３．IDリクワイアド(とりあえずID) <とりあえず疑似キーを付ける>####
行ごとに、データを識別するIDをつけたいとき、
とりあえず疑似キーを用意する(レコードごとにユニークなIDを用意するため)。
※疑似キーとは、データに対して後付けされた、意味を持たないID。「id」という列名の、16~32bitの整数値であることが多い。

|疑似ID(id)|部門名(department_name)|部員数(member_count)|
|:---|:---:|--:|
|1|営業１課|40|
|2|営業２課|45|


####４．キーレスエントリ(外部キー嫌い) <外部キーを設定しない>####
外部キーを設定しない。

####５．EAV(エンティティ・アトリビュート・バリュー) <サブタイプ属性を設定する>####
"サブタイプ名"という名前のカラムを用意し、その中にサブタイプ名を保管する。サブタイプ名によって、同じ行の"データ"の意味を変える。
新しい属性を追加したい場合に、列を新規追加しなくてよくなるのが狙い。

|社員ID(id)|サブタイプ名(attribute_name)|データ(attribute_value)|
|:---|:---:|--:|
|1234|name|田中一郎|
|1234|department|営業部|
|1234|year_of_employment|2010|
|1234|class|総合職|

####６．ポリモーフィック関連 <参照するテーブルを指定するカラムを作成する>####
行によって、外部参照するテーブルを変えたいとき、
参照するテーブル名を指定するカラムを作成する。

例えば、以下のように、'どのシステムに対するログか'によって、log_idを参照するテーブルが異なるとき(今回は'Sales(営業)'or'Purchase(購買)')。

<!--
```SQL
CREATE TABLE Logs ( 
  log_id SERIAL PRIMARY KEY, 
  system_type VARCHAR(20), -- 'Sales' または 'Purchase' が格納される
  system_id BIGINT UNSIGNED NOT NULL,   --SalesテーブルかPurchaseテーブルにidがある(外部キーは設定されない)
  log TEXT
```
-->

ログID(log_id)|システムタイプ(system_type)|ログ(log)|
|:---|:---:|:---:|--:|
|1|Sales|21/10/05 新規契約|
|2|Purchase|21/10/06/購入手続き|
|3|Sales|21/10/06 新規契約|
|4|Purchase|21/10/06/購入手続き|

以下のように、ログ検索のときに、Logsテーブルに自身のテーブル名が格納されているかをチェックする。

```SQL
SELECT * FROM Sales AS a 
  INNER JOIN Logs AS b 
  ON a.log_id = b.log_id AND b.system_type = 'Sales'
 WHERE b.log_id = '1';
```

####７．マルチカラムアトリビュート(複数列属性) <同一属性のカラムを複数用意する>####
一つのカラムに複数の値を格納したい(ジェイウォークと同じ)とき、
同じ属性のカラムを複数用意する。

例えば、記事に対して、それがどういう記事かを表すタグをつけたいとき。
付けることができるタグの数を最大3とし、タグを格納する属性を3つ定義する。タグが3つ無い場合は、NULLにする。

```SQL
CREATE TABLE Issues ( 
 issue_id VARCHAR(4) PRIMARY KEY, 
 tag1 VARCHAR(20), 
 tag2 VARCHAR(20), 
 tag3 VARCHAR(20), 
```

|記事ID(issue_id)|タグ名1(tag1)|タグ名2(tag2)|タグ名3(tag3)|
|:---|:---:|:---:|--:|
|1111|SQL|データベース|SQL好きさんと繋がりたい|
|2222|JAVA|デザインパターン|NULL|
|3333|NULL|NULL|NULL|

####８．メタデータドリブル(メタデータ大増殖) <同一属性を持つテーブルを複数用意する>####
テーブルが大きくなりすぎることを防ぐために、テーブルの分割を試みる。
例えば、入室履歴を記録するテーブルを、年度ごとに用意する。

```SQL
CREATE TABLE Entry_2008 ( . . . ); 
CREATE TABLE Entry_2009 ( . . . ); 
CREATE TABLE Entry_2010 ( . . . );
```

##データベース物理設計のアンチパターン##

####９．ラウンディングエラー(丸め誤差) <小数値をFLOAT型で利用する>####
テーブルに小数値(FLOAT型)を格納して、計算に利用する。

※別記事にまとめました
[【実践】テーブル設計でFLOAT型を使用することの危険性について/Qiita](https://qiita.com/payawoooow/items/f06e89d613ebe1f3c5a3)

####１０．サーティーワンフレーバー(31のフレーバー) <列定義上で、使用する値を限定する>####
イレギュラーな値が入力されるのを防ぐために、列定義上で入力可能な値を制限する。
例えば、CHECK制約やENUMなどを使用する。

```SQL
CREATE TABLE Recipes (
 -- 他の列 . . .
 meat_type VARCHAR(20) CHECK (status IN ('Beef', 'Pork', 'Chicken')) );
```

```SQL
CREATE TABLE Recipes (
 -- 他の列 . . .
 meat_type ENUM('Beef', 'Pork', 'Chicken') );
```
※ENUMはMySQL固有の機能です。

####１１．ファントムファイル(幻のファイル) <画像ファイルのファイルパスをDBに記録する>####
画像などのファイルをDBに直接保存せず、ファイルサーバーに保存する。
そのパスを、DBに保存する。

|ファイルID(issue_id)|ファイルパス(path)|
|:---|:---|
|1111|/home/user/hoge/hoge.png|
|2222|/home/user/moge/moge.png|

####１２．インデックスショットガン(闇雲インデックス) ＜インデックスが効果的に使用できていない＞####
以下の三つ
１．インデックスをまったく定義しない
２．インデックスを多く定義しすぎる、もしくは役に立たないインデックスを定義してしまう
３．インデックスを活用しないクエリを実行してしまう

##クエリのアンチパターン##

####１３．フィア・オブ・ジ・アンノウン(恐怖のunknown) ＜NULL値を一般的な値として使用する＞####
NULLはSQLでは、不明(unknown)という特別な意味を持つ値であるが、その性質を十分に理解しないままに使用すること
・NULLを一般的な値と区別せずに使用する(例えば、四則演算など)
・NULLを嫌い、NULLを一切使用しないテーブル設計にする(例えば、未入力を表現する値として、NULLの代わりに'-1'を使用するなど)

####１４．アンビギュアスグループ(曖昧なグループ) ＜GROUPしていないカラムを選択する＞####
データをグループ化して表示したいとき、GROUP BYで指定していないカラムをSELECTで指定する。

この場合(下記例ではorder_id)の振る舞いは、RDBMSによって異なる。
・MySQL→グループの最初の列の値が返される
・SQLite→グループの最後の列の値が返される
・その他→エラーになる

```SQL
SELECT product_id, MAX(arrival_date) AS latest, order_id FROM Products INNER JOIN Purchase USING (order_id) GROUP BY product_id;
```

####１５．ランダムセレクション<インデックスが活用できないランダムサンプリング>####
データをランダムに取得したい場合には、SQLのRAND()を使用することになるが、使用方法を間違えるとインデックスが活用されなくなる。

具体的には、下記のように、ランダムにソートを行うを行うと、インデックスが適用されないソート処理が発生するので、パフォーマンスが悪化する。

```SQL
SELECT recipe_id, recipe_name FROM Recipes ORDER BY RAND() LIMIT 1;
```

####１６．プアマンズ・サーチエンジン(賢者のサーチエンジン)<キーワード検索にパターンマッチ述語を使用する>####
キーワード検索をする際に、正規表現やLike述語などを使用する。

####１７．スパゲッティクエリ＜別目的のクエリをまとめて一つにする＞####
様々な値の検索を一つのクエリでまとめて実行しようとすること。

####１８．インプリシットカラム(暗黙の列)＜全列指定(アスタリスク)を使用する＞####
全列指定(アスタリスク)を使用する。

```SQL
SELECT * FROM Recipes;
```

##アプリケーション開発のアンチパターン##

####１９．リーダブルパスワード(読み取り可能パスワード)＜パスワードを平文でテーブルに格納する＞####
パスワードをテーブルに平文で格納する。
平文のパスワードを通信したり、認証に使用したりする。

####２０．SQLインジェクション＜未認証の値を組み立て、クエリを構築する＞####
認証されていない値を使ってクエリを組み立てる場合、悪意を持つ値がクエリ内部に潜り込むことで、意図しないテーブル操作がされてしまうリスクがある(SQLインジェクション)。

####２１．シュードキー・ニートフリーク(疑似キー潔癖症)＜疑似キーの欠番を許さない＞####
データ削除などで疑似キーに欠番ができた際に、新データの登録やキーの振り直しによって、欠番を埋めようとする。

**〇キー振り直し前**

|キー(id)|果物名(fruit)|
|:---|:---|
|1|Apple|
|2|Orange|
|4|Grape|

**〇キー振り直し後**

|キー(id)|果物名(fruit)|
|:---|:---|
|1|Apple|
|2|Orange|
|3|Grape|

####２２．シー・ノー・エビル(臭いものに蓋)＜クエリ実行時のエラー処理を書かない＞####
コードを短くするためなどの理由で、クエリ実行時のエラー処理を書かないこと。
・クエリ実行時のAPIの戻り値を取得しない
・実際に構築されたクエリを、後から把握できない
など。

####２３．ディプロマティック・イミュニティ(外交特権)＜SQLを管理対象から外す＞####
DBに関して、文書化やバージョン管理、テスト対象から外すこと。

####２４．マジックビーンズ(魔法の豆)<モデルとDAOが一体化している>####
MVCのM(モデル)が、アクティブレコード(ORMをサポートするパターン)そのものである。
C(コントローラ)上に、モデル(=アクティブレコード)を使用した複雑なロジックが書かれている。

####２５．砂の城<運用後の姿を想定していない>####
サービス運用前の想定が甘い。
・データサイズ
・マシンの性能
・バックアップ計画
・インシデントへの対応
などなど

#まとめ
本を読み進めていく中で、「自身が過去に実施したテーブル設計やクエリ設計が実はアンチパターンだった」みたいなことが何度かあり、冷や汗が止まりませんでした・・
これも勉強ということで、今後は気を付けることにしましょう。

色々な気付きを与えてくれる、とても良い輪読会でした。