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

#はじめに
以前に引き続き、社内で輪読会を実施しました。
※前回の記事はこちら

今回は、SQLアンチパターンを題材として取り上げました。
前回のGoF本もそうですが、各章が独立に書かれた本は、輪読会で取り上げやすいです。

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

今回は、アンチパターンについて焦点を当て、その問題点や解決策については記載しておりません。
アンチパターンが採用されないことが最も重要であり、そのためにアンチパターンをいつでも思い出すことができる状態にする、という方針です。
解決策を知りたい方は、ぜひ書籍をご購入ください。

#参考図書
SQLアンチパターン

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

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

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を指定するカラムを定義する。

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(購買)')。

ログ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テーブルに自身のテーブル名が格納されているかをチェックする。

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にする。

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

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

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

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

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

※別記事にまとめました
【実践】テーブル設計でFLOAT型を使用することの危険性について/Qiita

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

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

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→グループの最後の列の値が返される
・その他→エラーになる

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

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

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

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

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

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

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

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(コントローラ)上に、モデル(=アクティブレコード)を使用した複雑なロジックが書かれている。

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

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

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