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

投稿者: | 2021年6月23日

はじめに

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

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

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

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

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

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


目次

  1. SQLアンチパターンについて
  2. Ⅰ部 データベース論理設計のアンチパターン
    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. Ⅱ部 データベース物理設計のアンチパターン
    3-1. [○]9章 ラウンディングエラー(丸め誤差)
    3-2. [△]10章 サーティーワンのフレーバー(31のフレーバー)
    3-3. [△]11章 ファントムファイル(幻のファイル)
    3-4. [△]12章 インデックスショットガン(闇雲インデックス)
  4. 最後に

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といった「限定する値を列定義で指定する」ことを
「31のフレーバー」というキャッチコピーを用いて追加や変更の多かったサーティーワンのフレーバーの名前でアンチパターンとして紹介されています。ちなみに僕の好きなフレーバーはチョップドチョコレートです。
限定する値を列定義で指定することで追加、削除、移植が困難になります。
参照テーブルを用いて外部キー制約を宣言することで解決策を紹介していました。追加や変更がない場合は使用していいとされています。
僕はENUMに出会ったことはありますが、不変そうなものでした。

[△]11章 ファントムファイル(幻のファイル)

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

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

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


最後に

長くなってしまったのでⅠ部、Ⅱ部を前編とし、Ⅲ部、Ⅳ部を後編として分割しようと思います。
ここまでの結果だけ見れば半数以上が○△なので、わりと業務内でも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/