こんにちは@s_tsukaです。今回は珍しくDBネタです。

SQLアンチパターンという本があります。これがなかなか良い本らしく読んでみました。

[tmkm-amazon]4873115892[/tmkm-amazon]

結論としてはかなり勉強になりました。DBやSQLを扱う人は絶対に１度は読んだ方が良いでしょう。折角なので軽くまとめておきたいと思います。

概要

アンチパターンの本なので、パターンが列挙されています。ただそのパターンは良いパターンではなく悪いパターンです。

これがなかなか役に立って、読んでる最中何度も「あーこの設計やった（あるいは考えた）ことあるwwwwww」となりました。勿論解決策付きで解説されているため、ためになります。

以下、アンチパターンと解決策を軽くまとめていきます。ほとんど備忘録用なので、詳細は本を買って読みましょう。

アンチパターン

以下に忘れないようにアンチパターンをまとめておきます。

アンチパターンなので、みなさん真似しちゃダメですよ！（絶対ではない）

1. ジェイウォーク（信号無視）

カンマ区切りの値を入れちゃうやつです。

BOOK_ID	NAME	TAGS
1	Spring MVC	Java,Spring,SpringMVC
2	はじめてのScala	Scala,入門

こういうテーブルがあったときにTAGSの部分にカンマ区切りで値を入れているのがNGです。交差テーブルを作って解決しましょう。

2. ナイーブツリー（素朴な木）

階層構造を表現しちゃうやつです。

COMMENT_ID	COMMENT	PARENT_ID
1	良い記事ですね！	NULL
2	良い記事って。。ただのまとめでしょ？	1
3	いや、役に立つんじゃないかな！	2

PARENT_IDによって引用を表現していますね。スレッディングと言えば良いのかな？こういうテーブルは確かにデータストアとして正しいですが、SQL使うシーンを考えるとかなり面倒です。なので、閉包テーブルを使う等代替案を考えましょう。

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

なんでもかんでもIDをつけるし、AUTO INCREMENTだし、しかも列名が”ID”だったりするアレです。（AUTO INCREMENTが悪、とかではないです）

ID	ARTICLE_ID	TAG_ID
10001	2941	10
10002	2941	23
10003	2942	8

これは交差テーブルと呼ばれるもので、ARTICLEとTAG、つまり記事と（複数の）タグを結びつけるテーブルです。ARTICLE_IDとTAG_IDの複合主キーで良いのに、IDという余計な主キー列を作っています。これがNGです。冗長だったり、重複レコードできちゃったり。“ID”というのも微妙でJOIN書くときにID列だらけだとUSINGが使えなかったり、視認し辛かったり。

主キーはちゃんと考えましょう。

4. キーレスエントリ（外部キー嫌い）

外部キー付けない人多いよねっていうアレです。

個人的には外部キーはきっちり付けたい派です。データ構造とデータはなるべく（制約で）守りたい派。

外部キーいらない派とバトルするときはこの章を引用しましょうw

5. EAV（エンティティ・アトリビュート・バリュー）

普通DBのテーブルを作るときに「何を格納するか」を考えて「列」を作るわけですが、柔軟性を重視してこれを行でやろうとするアレです。

FILE_ID	ATTRIBUTE_NAME	ATTRIBUTE_VALUE
1	extension	png
1	size	3441238
1	created_by	s_tsuka
1	created_at	2015-03-21 20:57:00
1	locked	false

想像できるかと思いますが、上記の各データは列じゃなくて行なので、かなり使い勝手が悪いです。制約が使えなかったり、そもそも数値、文字列、真偽値が混ぜこぜでしかも数値は実は’3441238’というように文字列で表現しなくちゃいけなかったり・・・。そして多分これをアプリ側で使うときも泣きを見ます。アプリ側では配列として使いたいのではなく何らかのentityとして使いたいはずで変換が必要という・・・

解決方法は継承っぽいことをしたりするんですが、この辺りの経緯はちょっと上手く説明できないので本を読みましょう。

6. ポリモーフィック関連

データによって見る親テーブルが変わるやつです。

COMMENT_ID	TYPE	COMMENT	PARENT_ID
1	blog	良いblogですね！	101
2	article	この記事は勉強になった！	4210
3	article	やっほー	4211
4	blog	記事偏ってんなー	101

BLOGテーブルとARTICLEテーブルがあったとして、システムではそのどちらに対してもコメントができるとします。そして、PARENT_IDが指すテーブルはデータによって異なります(BLOGかARTICLEか)。

上記のケースだとポリモーフィックな構造ではないので、微妙ですが・・・こういうケースはNGで、解決策として共通の親テーブルとかが提案されています。まあとにかく詳細は本を読みましょう。

7. マルチカラムアトリビュート（複数列属性）

ジェイウォークと同じだけど列を複数作ったやつです。正規化されていないテーブルのことです。

ARTICLE_ID	TAG1	TAG2	TAG3	TAG4
1	Scala	入門	NULL	NULL
2	入門	Java	NULL	Java8

もうこれだけでBad smellしかしませんね。検索とか考えると頭が痛いです。重複考慮した更新や削除とかも同じくきついですね。

従属テーブル作って対応しましょう。

8. メタデータトリブル（メタデータ大増殖）

全く同じテーブルなのに年や月で分かれていたり、同じような列が連続しているやつです。

CREATE TABLE LOG_2013 ( ... );
CREATE TABLE LOG_2014 ( ... );
CREATE TABLE LOG_2015 ( ... );

データが多すぎるから分けた、というケースが多いと思います。古いデータは使わないし、かといってテーブル１つにするとクエリが遅くなるし、みたいな。

上記のようなケースならOKだし、実際そういう経験はありますが。LOG_2014テーブルの2014/12/31のデータが間違ってたので、UPDATEして2015年のデータにしよう！ってときはUPDATEじゃなくてDELETE INSERTが必要だったり、まあ色々NGですね。

DBのパーティショニングの機能を使いましょう。

9. ラウンディングエラー（丸め誤差）

FLOATとかDOUBLEの列を使って誤差で後で困るやつです。

誤差はITエンジニアの基礎知識かというと確かにそうなんですが、実はそうではなくて。計算機科学を学んでいないエンジニアは結構いるので、意外とハマったりします。

浮動小数点数と誤差を知らない人はそれを学ぶと良いですね。その上で開発するアプリケーションやデータの要件を考慮してNUMBER、FLOAT、DOUBLE、NUMERIC、DECIMALのどれを使うか考慮すべきでしょう。

10. サーティワンフレーバー（３１のフレーバー）

いわゆるEnumというか、特定の値をテーブルに保存するやつです。

CREATE TABLE Article (
  status VARCHAR(20) CHECK (status IN ('Draft', 'Private Open', 'Open'))
);

上記の場合はCHECKですが、ENUMでも同じです。これだと値の候補はCHECKとして表現されているので、（簡単には）その候補一覧を取れないし、値の候補が追加されたとき面倒だったりと色々きついです。

こういうときは例えばArticleStatusというようなテーブルを別に作り、そこに候補値を挿入して、後は外部キーで使うようにしましょう。

11. ファントムファイル（幻のファイル）

DBにファイルを保存したくないので、DBにはファイルのパスを入れつつ、ファイルはファイルシステムに保存する・・というやつです。

確かにDBにファイル入れたくない気持ちも分かりますが、個人的には入れる派です。

ファイルシステムに入れないとトランザクションとか整合性とか色々ありますしね。解決策としては当然ですが、BLOGとかMEDIUMBLOGとかです。

12. インデックスショットガン（闇雲ショットガン）

インデックスをまったくつけなかったり、つけまくってしまったりするやつです。

インデックスが効くクエリと効かないクエリを理解していないとか。複合インデックスのことを分かっていないとか。

MENTORの原則に基づいて効果的なインデックス管理をしましょう。

13. フィア・オブ・ジ・アンノウン（恐怖のunknown）

|| 演算子でNULLと文字列を結合しても結果はNULLです。とかまあNULL関連色々です。

NULLは何してもunkownになるのでちゃんとIS NULL, IS NOT NULLしましょう。

COALESCE, NVL, ISNULLなどの関数も覚えておきましょう。

14. アンビギュアスグループ（曖昧なグループ）

GROUP BY + MAXとかでひっかけた「値が存在する行全体」を取得するケースなどのやつです。

以下は書籍の例をそのまま引用させて頂きます。

SELECT product_id, MAX(date_reported) AS latest, bug_id
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;

Bill Karwin (2013). SQLアンチパターン 株式会社オライリー・ジャパン

上記のダメなクエリは、「製品IDごとにグルーピングして、最新のバグ報告日付と一緒にそのバグのIDも出したい！」というクエリです。つまり各製品の最も最近のバグが知りたい訳です。

ですが、これは残念ながら多くのDBではエラーになるし、MySQLでは実行できても意図通りの値になるとは限りません。bug_idはグルーピングおよび集計の対象でないので、何を出せば良いのかDBが分からないのです。

対策は色々あるので詳しいことは本を読みましょう。ちょっと難しいJOINとかで対応します。

 15. ランダムセレクション

特定のテーブルからランダムに１行取得するというクエリを発行するやつです。

SELECT * FROM Article ORDER BY RAND() LIMIT 1;

テーブルのデータが増えるほど遅くなって行きます。

アプリ側でランダムなIDを生成して、それを指定しても良いですが、SQLでやりたい場合、クエリを工夫します。1〜最大値の間のランダムな値とか。

16. プアマンズ・サーチエンジン（貧者のサーチエンジン）

全文検索をしたくてLIKE ‘%foo%‘;とやってしまうやつです。

データが少ないなら問題はないですが、データが多いならば%foo%ではインデックスが効かないのでかなり遅くなってしまいます。

なので、DBに付属している、あるいはDB以外の全文検索エンジンを使いましょう。例えばMySQL 5.6.4では全文検索機能が搭載されたけど日本語対応していないとかあったりします。

使っているDBに日本語対応全文検索エンジンがあるならそれを、なければApache LuceneやElasticsearchを使う感じでしょうか。

17. スパゲッティクエリ

集計・グルーピングなどを駆使して複雑なクエリを作るやつです。

複雑なものを作れば一発で希望する結果を取れますが、メンテが大変だったりするので、分割しましょうよという話です。

18. インプリシットカラム（暗黙の列）

SELECT * というようにワイルドカードを使うやつです。

ワイルドカードは楽だし、100%悪、というわけではないですが、これを使うよりは多少面倒でも列を列挙した方が安全だし、確実だよね、という話です。

19. リーダブルパスワード（読み取り可能パスワード）

USERテーブルとかのPASSWORD列に平文の（生の）パスワードを保存してしまうやつです。絶対辞めましょう。

なぜ生のパスワードを保存してはいけないのかは以下の本が参考になります。ITエンジニアでセキュリティに疎いならば読んでおきましょう。

[tmkm-amazon]4797361190[/tmkm-amazon]

DBのハッシュ関数使えばOKという人も要注意です。アプリとDB間のセキュリティ的な意味で。

簡単に言うと解決策は SSL + アプリ側でのハッシュ化 + 個別ソルト + ストレッチングです。

20. SQLインジェクション

これも上記の本に載っています。

解決策は一言で言うとPrepared statementです。

21. シュードキー・ニートフリーク（疑似キー潔癖性）

AUTOINCREMENTALなID列を作ったは良いけど、欠けているIDがあるので、無理に埋めようとするやつです。

無理に埋めようとしてはいけません。欠番が気持ち悪くても我慢しましょう。IDが足りなくなるのが怖いなら64bit整数の型にしておきましょう。Twitterのツイートでさえまだ53だか54bitです。

シー・ノー・エビル（臭いものに盖）

SQLを多分何らかのClient(例えばJDBCとか)から実行すると思いますが、そのときにレスポンスコードを無視したり、何か問題が発生しているときにソース上のSQLしか読まないというやつです。

レスポンスコードはちゃんと見ましょう。

ソース上のSQL構築が間違っているケースとかあるので、SQLログ見たりprepared statementのSQL見たり、とにかくコード眺める以外もしましょう。

23. ディプロマティック・イミュニティ（外交特権）

これはなんかDBというよりは一般的な話でした。

文書化・バージョン管理・テストなど。

24. マジックビーンズ（魔法の豆）

MVCのMが単純化されすぎていたり、ActiveRecordオブジェクトそのものだけだったりするやつです。

MVCのよくある勘違いとして「MはModelでModelはただのデータ構造」というものがあると思います。（・・・ですよね？）

これは間違っていてModelはビジネスロジックそのものなので、データ構造もそうだけど、もっと重い色々処理、責務を持っています。

なのでM = ActiveRecordだけ、とかは間違っていてあくまでActiveRecordをhas aで持っていると考えましょう。

（というような話です）

砂の城

インシデント対応の話です。SQLアンチパターンというか開発プロジェクトアンチパターンな感じの話なので省略します。

まとめ

かなり省略してしまいましたが、簡単にまとめてみました。

SQLアンチパターン、かなりの良書なので、DBAやDBを利用しているITエンジニア、そしてこれから世に出る学生、みなさんぜひとも読んでおきましょう。

[tmkm-amazon]4873115892[/tmkm-amazon]