はじめに

こんにちは。
GMOアドマーケティングのKONCEです。

新卒で入社し、数年経ちました。日々の業務で学ぶことは多いですが、今年度は技術の深堀りをテーマにやっていきたいと思っています。

今回は入社してDBやSQLに関しては業務内で学ぶことが多く、特別訓練をしていたわけではなかったのですが、「SQLアンチパターン」を用いて学びながら、改めて自分の現状を見つめ直していけたらと思います。

今回は学習を行う側面と自分自身のレベルについて見直していきたいので

知っていた → ○
部分的に知っていた → △
知らなかった → ×

を付けてみようと思います。

SQLアンチパターンについて
Ⅰ部データベース論理設計のアンチパターン
2-1. [○]1章ジェイウォーク（信号無視）
2-2. [×]2章ナイーブツリー（素朴な木）
2-3. [○]3章 IDリクワイアド（とりあえずID）
2-4. [×]4章キーレスエントリ（外部キー嫌い）
2-5. [×]5章 EAV（エンティティ・アトリビュート・バリュー）
2-6. [×]6章ポリモーフィック関連
2-7. [○]7章マルチカラムアトリビュート（複数列属性）
2-8. [×]8章メタデータトリブル（メタデータ大増殖）
Ⅱ部データベース物理設計のアンチパターン
3-1. [○]9章ラウンディングエラー（丸め誤差）
3-2. [△]10章サーティーワンのフレーバー（31のフレーバー）
3-3. [△]11章ファントムファイル（幻のファイル）
3-4. [△]12章インデックスショットガン（闇雲インデックス）
最後に

SQLアンチパターンについて

SQLアンチパターンは「べからず集」で先人たちの失敗などから学びを得ます。SQLを使うソフトウェア開発者向けで多くのアンチパターンへの回避方法が紹介されており、パフォーマンス等に関しては対象外です。

データベース論理設計のアンチパターン
データベース物理設計のアンチパターン
クエリのアンチパターン
アプリケーション開発のアンチパターン

で構成されています。今回は各章の細かい解説を記事に記載するというよりかは、学びを得たことや、今まで出来ていなかったことなど現時点での自分を見つめ直す意味で扱っていければと思います。

（引用元：O’Reilly Japan「SQLアンチパターン」https://www.oreilly.co.jp/books/9784873115894/）

Ⅰ部データベース論理設計のアンチパターン

[○]1章ジェイウォーク（信号無視）

交差テーブルを避ける（交差点を無視する＝信号無視）として紹介されています。カンマ区切りなどで複数の属性を一つの列に格納するケースです。
基本的には交差テーブルを用いて多対多で解決するので一つの列に複数格納する区切り文字や長さ制限などは回避できます。
パフォーマンス向上のために非正規化を行うケースではカンマ区切りフォーマットを使用してもいいとされています。僕のチームでも一部使用しており、馴染みがありました。

product_id	product_name	account_id
1	hoge	12,34

[×]2章ナイーブツリー（素朴な木）

読者がコメントやディスカッションをスレッド形式でできるような記事サイトで、親コメントに対して依存するケース（隣接リスト）をアンチパターンとして紹介されています。
経路列挙モデル、入子集合モデル、閉包テーブルモデルで代替することで解決することを解説しています。一つ一つの解説とその長所短所が参考になりました。

comment_id	parent_id	発言者	コメント
1	NULL	aさん	コメント1
2	1	bさん	コメント2
3	2	aさん	コメント3
4	1	cさん	コメント4
5	4	bさん	コメント5
6	5	cさん	コメント6

[○]3章 IDリクワイアド（とりあえずID）

主キーの規約を確立することを目的として、UNIQUE制約を貼ったサロゲートキーではなく複合キーを用いれば重複を回避できるケースをアンチパターンとして紹介されています。
idという名前は明確的でなく、より分かりやすい列名にすべきとも言及されています。フレームワークによっては規約により用いて良いとされています。
複合キーについてはやってしまったことがありますし、とりあえずidと名付けることに関しても見たことがあったので再認識の意味でも勉強になりました。

id	tag_id	article_id
11	327	1234
12	327	1234

[×]4章キーレスエントリ（外部キー嫌い）

外部キー制約を使用しないことをアンチパターンとして紹介されています。
子が参照している親の行を削除しようとすると子側から削除しなければならないと言ったような、複数テーブルが関連し合う列の更新に外部キー制約を邪魔と感じることがあるとされていますが、
外部キー制約を宣言したほうが効率的で、カスケード更新なども用いることが良いとされていました。

[×]5章 EAV（エンティティ・アトリビュート・バリュー）

下記表のようなBugとFeatureRequest（機能要望）が基底型であるIssue（問題）で共通の属性を共有していて、BugとFeatureRequestそれぞれ独自の項目を追加させたいようなケースを元に汎用的な属性テーブルを使用することをEAVと呼び、アンチパターンとして紹介されています。
属性の取得が冗長になることや、必須属性を「NOT NULL」で設定できない、日付型などのデータ型をうまく使用できないなどでデータの整合性を保ちづらい点などからRDBではEAVを使用するメリットはほとんどなさそうでした。

issue_id	attr_name	attr_value
1234	‘product’	‘1’
1234	‘date_reported’	‘2021-05-28’
1234	‘status’	‘NEW’
1234	‘description’	‘hoge’

[×]6章ポリモーフィック関連

複数の親テーブルを参照するときに二重目的の外部キーを使用するために関連付けたい複数の親テーブルの名前を文字列型の列として追加する「ポリモーフィック関連」をアンチパターンとして紹介されています。
参照を逆にすること、複数の親テーブルにそれぞれ対応した交差テーブルをUNIQUE制約を用いての作成などの単純化で回避することを解決方法としてあげられていました。
そもそも「ポリモーフィック関連」のような行動をしようってなったことがありませんでした。

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

複数の値を持つ属性を格納するという1章のジェイウォークと同じ目的ですが、以下表のように複数の列を定義することをアンチパターンとして紹介されています。
検索、追加、削除が不便で、列が足りなくなった時なども不都合です。
同じようなidが入るカラムでも意味合いが違えば問題ないとされていました。（下記表で言えばaccount_id1が報告者、account_id2が担当者と言ったようなケース）
問題ないとはいえid1みたいな名前は付けないと思います。

product_id	product_name	account_id1	account_id2
1	hoge	12	34

[△]8章メタデータトリブル（メタデータ大増殖）

この章は「データベースクエリはデータ容量が増えるにつれてパフォーマンスが低下するものですが、データの増加に合わせてクエリの実行速度を落とさないように設計すること」が目的で
「行数の多いテーブルを複数に分割すること」、「列を複数列に分割すること」をスタートレックに出てくる繁殖力の高いトリブルを用いたメタデータトリブルアンチパターンとして紹介されています。
テーブルが増えること、整合性の管理、一意性の保証など管理面で多く問題がありそうです。
解決方法はパーティショニングと正規化で、水平パーティショニングは僕の携わっているサービスでももちろん利用しています。垂直パーティショニングはやったことありませんでした。

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

[○]9章ラウンディングエラー（丸め誤差）

小数を使用すること、それを正確に計算を行うことを目的に、FLOAT型を使用することをアンチパターンとして紹介されています。
2進数と浮動小数点数の関係は一度は学ぶことですが、FLOAT型は楽ですし使いがちです。金融系のシステムでは大きく問題になりそうです。
僕の携わるサービスはNUMERIC(DECIMAL)を使うケースが多かったです。思ったよりアプリケーションの方は意識してないところがありますが。

[△]10章サーティーワンのフレーバー（31のフレーバー）

国によって違う「Mr」「Ms」などの敬称を例に、列を特定の値に限定することを目的にして、その中でCHECK制約やMySQLのENUMといった「限定する値を列定義で指定する」ことを
「３１のフレーバー」というキャッチコピーを用いて追加や変更の多かったサーティーワンのフレーバーの名前でアンチパターンとして紹介されています。ちなみに僕の好きなフレーバーはチョップドチョコレートです。
限定する値を列定義で指定することで追加、削除、移植が困難になります。
参照テーブルを用いて外部キー制約を宣言することで解決策を紹介していました。追加や変更がない場合は使用していいとされています。
僕はENUMに出会ったことはありますが、不変そうなものでした。

[△]11章ファントムファイル（幻のファイル）

画像などの大容量メディアファイルの格納のために「物理ファイルの使用を必須と思い込む」ことをアンチパターンとして紹介されています。
画像のバイナリデータを格納するか、画像ファイルパスを格納するかといったような問題で後者のリスクについて言及されています。
ただしこれにはデータ容量を減らすことなど正当な理由が多くあり、用いても良いとされています。必要に応じてどちらも検討する必要があるとのことでした。
僕が経験したのほとんどは後者です。

[△]12章インデックスショットガン（闇雲インデックス）

インデックスを効果的に使用することはDBのパフォーマンス改善に直結しますが、全く定義しない、少なすぎる、多すぎる、インデックスを活用しないクエリを実行するなどの「闇雲にインデックスを使用する」ことをアンチパターンとして紹介されています。
最適なインデックスの作成にはどのようなクエリを最適化しなければいけないかを把握している必要があり、Measure（測定）、Explain（解析）、Nominate（指定）、Test（テスト）、Optimize（最適化）、Rebuild（再構築）の頭文字を取った「MENTOR」に基づいて管理を行うべきとされていました。
開発を行う上でもでもこういった最適化を行うことはありましたが、「MENTOR」という言葉や詳細は知らずにやっていました。

最後に

長くなってしまったのでⅠ部、Ⅱ部を前編とし、Ⅲ部、Ⅳ部を後編として分割しようと思います。
ここまでの結果だけ見れば半数以上が○△なので、わりと業務内でもSQL力はつくと言って良さそうな気はしますが、後半はどうなるでしょうか？

GMOアドパートナーズ TECH BLOG byGMO

業務でどれだけSQL力がつくのか ~SQLアンチパターンを用いて確認~ 後編

はじめにこんにちは。GMOアドマーケティングのKONCEです。前回自分の現状を見つめ直していくために業務でどれだけSQL力がつくのか ~SQLアンチパターンを用いて確認~ 前編を公開いたしました。今回は後編です。今回も知っていた → ○ 部分的に知っていた → △ 知らなかった → ×を付けてみようと思います。目次SQLアンチパターンについてⅢ部クエリのアンチパターン2-1. 13章フェア・オブ・ジ・アンノウン（恐怖のunknown）2-2. 14章アンビギュアスグループ（曖昧なグループ）2-3. 15章ランダムセレクション2-4. 16章プアマ...

■エンジニア採用ページ　～福利厚生や各種制度のご案内はこちら～

https://note.gmo-ap.jp/n/n02cbeb6edb0d/

■noteページ　～ブログや採用、イベント情報を公開中！～

https://note.gmo-ap.jp/