はじめに
こんにちは。
GMOアドマーケティングのKONCEです。
前回自分の現状を見つめ直していくために
業務でどれだけSQL力がつくのか ~SQLアンチパターンを用いて確認~ 前編
を公開いたしました。
今回は後編です。
今回も
- 知っていた → ○
- 部分的に知っていた → △
- 知らなかった → ×
を付けてみようと思います。
目次
- SQLアンチパターンについて
- Ⅲ部 クエリのアンチパターン
2-1. [△]13章 フェア・オブ・ジ・アンノウン（恐怖のunknown）
2-2. [△]14章 アンビギュアスグループ（曖昧なグループ）
2-3. [△]15章 ランダムセレクション
2-4. [△]16章 プアマンズ・サーチエンジン（貧者のサーチエンジン）
2-5. [○]17章 スパゲッティクエリ
2-6. [○]18章 インプリシットカラム（暗黙の列） - Ⅳ部 アプリケーション開発のアンチパターン
3-1. [△]19章 リーダブルパスワード（読み取り可能パスワード）
3-2. [○]20章 SQLインジェクション
3-3. [△]21章 シュードキー・ニートフリーク（擬似キー潔癖症）
3-4. [○]22章 シー・ノー・エビル（臭い物に蓋）
3-5. [△]23章 ディプロマティック・イミュニティ（外交特権）
3-6. [×]24章 マジックビーンズ（魔法の豆）
3-7. [×]25章 砂の城 - 結論
- 最後に
SQLアンチパターンについて
概要に関しては前編で紹介させていただきましたが、今回も書籍を紹介させていただきます。
（引用元：O’Reilly Japan「SQLアンチパターン」https://www.oreilly.co.jp/books/9784873115894/）
Ⅲ部 クエリのアンチパターン
[△]13章 フェア・オブ・ジ・アンノウン（恐怖のunknown）
first_nameとlast_nameの間にNULLABLEなmiddle_initialを追加し、フルネームを取得しようとしたがミドルネームを登録したものしか表示されなかったことを例に、
欠けている値を区別することを目的に、NULLを一般値として使う、または一般値をNULLとして使うことをアンチパターンとして紹介されています。
NULLの特性を解説し、状況に応じてNOT NULLやデフォルトを使用するのがいいとされていました。
改めて勉強になる点も多かったです。
SELECT first_name || '' || middle_initial || '' || last_name AS full_name FROM Accounts;
[△]14章 アンビギュアスグループ（曖昧なグループ）
GROUP BYでグループ内で最大値を持つ行とその行の他の属性も取得するクエリを実行することを目的としていて、非グループ化列を参照することをアンチパターンとして紹介されていました。
ありがちなケースで、クエリから曖昧な列を排除することや相関サブクエリを使用すること、JOINの利用など多くの解決方法が紹介されており、使用したことがあるものないものどちらも参考になりました。
[△]15章 ランダムセレクション
ランダムに結果を返すSQLクエリが必要になる（サンプル行をフェッチする）ケースを目的としますが、SQLでランダムソートすることはアンチパターンとして紹介されていました。
データセットが小さければ問題ないとされていますが、SQLでは非効率で、1と最大値の間の数のランダムキーを使用したり、全部取ってアプリケーション側で対応することが解決方法として紹介されていました。
広告においてはこういったケースは多いですが、僕はアプリケーションで解決する方法でしか対応したことがありませんでした。
[△]16章 プアマンズ・サーチエンジン（貧者のサーチエンジン）
検索エンジンなど全文検索を行うことを目的とし、その中でのLIKEとワイルドカード%を用いる、または正規表現を用いるパターンマッチ述語を使用するケースをアンチパターンとして紹介されていました。
インデックスの効果を得られないパフォーマンスの観点や意図しない語へのマッチなど問題がありそうです。
別にパフォーマンス気にする必要ないよといったクエリでの利用は問題ないとされていました。
解決方法に全文検索のフルテキストインデックスについて記載されていました。
チームメイトがそういったブログを過去に記載していましたのでこれについては知ってはいました。業務で使用したことはありません。
[○]17章 スパゲッティクエリ
1つのクエリで多くのことを実現しようとしたいケースは多くありますが、複雑な問題をワンステップで解決しようとすることはアンチパターンとして紹介されていました。
1つのクエリで解決しようとしている際、二つのテーブルが関係を制限する条件を持たない場合にデカルト積が生じるケースがあると紹介されていました。
例として以下のクエリで、
SELECT p.product_id,
COUNT(f.bug_id) AS count_fixed,
COUNT(o.bug_id) AS count_open
FROM BugsProducts p
INNER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'fixed'
INNER JOIN BugsProducts p2 USING(product_id)
INNER JOIN Bugs o ON p2.bug_id = o.bug_id AND o.status = 'open'
WHERE p.product_id = 1
GROUP BY p.product_id;
意図した結果はfixedが11、openが7ですが、関係を制限する条件を持っていないため11×7のデカルト積77で出ているケースを紹介されていました。
| product_id | count_fixed | count_open |
| 1 | 77 | 77 |
1つのクエリで複数の目的を叶えようとするとデバッグやメンテナンス性の悪さなどに関しても言及されていました。
BIツール、レポートツールなどの複雑なタスクを1つのクエリで実現しなければならない場合などは用いてもいいとされています。
単にステップを分けることやUNION、CASEとSUMの組み合わせなど解決方法が紹介されていました。
業務上で経験（失敗も）したことが多い内容でした。
[○]18章 インプリシットカラム（暗黙の列）
JOINした2つのテーブルに同じ名前のカラムがあり、*で取得した際うまく動かなかったことを例に、タイプ数を省略することを目的とするとショートカットの罠に陥るアンチパターンとして紹介されていました。
リファクタリングで問題になり得ること、パフォーマンスとスケーラビリティに悪影響を及ぼす可能性があることにも言及し、16章のLIKEのように雑に書きたい場合などは特に問題ないですが、ワイルドカードではなくカラムを明示的に指定すべきとされています。
チームのルールなどで決まっているケースが多いのではと思いますがどうでしょうか。
Ⅳ部 アプリケーション開発のアンチパターン
[△]19章 リーダブルパスワード（読み取り可能パスワード）
パスワードを平文で格納することを重大なセキュリティ欠陥であるアンチパターンとして紹介されていました。
平文で格納する場合は暗号化を用いたり、ハッシュの利用での暗号化、ハッシュへのソルトの追加、アプリケーションでハッシュを用意しクエリではハッシュのみの利用することなどでアンチパターンの解決をし、一時パスワードの利用でパスワードのリカバリーでなくリセットすることで不正アクセスなどを防ぐべきとされていました。
一部知ってる内容ではありましたが改めて学びになりました。
[○]20章 SQLインジェクション
ここではSQLインジェクションに関して深く触れませんが、動的SQLを記述しようとするときに未検証の入力をコードとして実行することをアンチパターンとして紹介されていました。
エスケープ、プリペアドステートメントなどだけでなく、「誰も信用するな」といった感じで入力のフィルタリング、バリデーションを行ったり、ユーザーの入力をコードから隔離したり、コードレビューを行ったりして徹底的に潰すべきとされていました。
SQLインジェクションに関しては別で学んだこともあり、日々努力すべき内容でした。
[△]21章 シュードキー・ニートフリーク（擬似キー潔癖症）
行が連番になっていないケースはありますが、隙間を埋めることをアンチパターンとして紹介されていました。
欠番の割り当てや番号の振り直しはコストが高いですし、キーの再利用はしてはならないとしています。
解決策としてもそもそも擬似キーの欠番は埋めないとし、「そういうもの」として捉えることが重要そうです。
営業の人などに「そういうもの」って伝えるのってコスト高いですよね。そちらについても言及されていて学びになりました。
コストの高さが解決したわけではありませんが、僕はわりと得意です。
[○]22章 シー・ノー・エビル（臭い物に蓋）
コード量の少ないクールなコードを書こうとすることが開発者には多くありますが、データベースAPIの戻り値を無視することとアプリケーションコード内のSQLしか読まないことでエラーに気づかず肝心な部分を見逃すアンチパターンとして紹介されていました。
SQL文字列を組み立てるアプリケーション側からのデバッグは悪習とされ、いかにミスから回復するかを徹底するのが解決策とされています。
try catchを用いたり、ログファイルに組み立て後のSQLを出力したりしてデバッグ作業を整えることが良いとされていました。
開発の流れを教えていただいた時からのルーティーンで自然と行なっている内容でした。
[△]23章 ディプロマティック・イミュニティ（外交特権）
技術的負債を抱えないためにもベストプラクティスに従って開発しようとすることの中で、SQLを特別扱いすることをアンチパターンとして紹介されています。
データベース管理者と役割が区別されているケースがあると紹介されています。僕のチームではそういった区別はないのでスッと入ってきませんでしたが、データベース開発においてもベストプラクティスに従って品質保証すべきとありました。
ER図やテーブルなどの説明、関連性などの文書化、問題が発生した時のためのバージョン管理、テストコードなどによるテスティングで品質の保証をデータベースに対しても取り組むべきで、僕自身は一部できているところ、まだできていないところなどあり理解が深まりました。
[×]24章 マジックビーンズ（魔法の豆）
MVCのM（モデル）を単にデータアクセスオブジェクトとしてみて、モデルがアクティブレコードそのものとすることをアンチパターンとして紹介されています。
アクティブレコード自体に問題があるわけではなく、全てのモデルクラスをアクティブレコードの規定クラスを継承する規約がアンチパターンとして挙げられています。
解決策としてモデルがアクティブレコードを「持つ」ようにすることが挙げられています。モデルを理解し、ドメインモデルを使用して疎結合化させシンプルにしていくこと。
[×]25章 砂の城
サービスの安定稼働のために「想定不足」をアンチパターンとして紹介されています。
むやみやたらに対策をしていくということではなく、どのようなトラブルが起こりうるか可能な限り想定しておくことが重要で、そのためにベンチマーク、テスト環境の用意、例外処理、バックアップ、マシンの冗長化など考慮し、問題をなるべく防ぐ重要性について言及されています。
自分へ言い聞かせる意味でも×とします。
結論
「SQLアンチパターン」を用いての学習の面が大きかったですが、業務でどれだけSQL力がつくのかを（僕基準ですが）○×△の結果から見ると「かなりつく」と言えるのではないでしょうか。
僕はわりと勉学先行というより「やって身につけるタイプ」と思っていましたが、それが証明された気もするのでやってみてよかったです。
最後に
長くなりましたが、SQLアンチパターンを用いて自分の現状を見つめ直すことができました。
アンチパターン、アンチパターンの見つけ方、アンチパターンを使用してもいい場合、解決法の流れで学ぶことができ、楽しむことができました。
今年度は自分の能力向上にも力を入れていきたいので、また別のものでこういったブログを書ければと思います。
■エンジニア採用ページ ～福利厚生や各種制度のご案内はこちら～
https://note.gmo-ap.jp/n/n02cbeb6edb0d
■noteページ ～ブログや採用、イベント情報を公開中！～
