#はじめに
本エントリーは某社内で実施するSQL勉強会向けの資料となります。
本エントリーで書籍「SQL アンチパターン」をベースに学習を進めます。書籍上でのサンプルコードはMySQLですが、本エントリーでのサンプルコードはSQL Serverに置き換えて解説します。
また本章以外のエントリーおよび利用するSQLリソースなどは、以下のGitHubを参照ください。
#インプリシットカラムとは
インプリシットカラムとは、暗黙的な列指定を不適切に使用したために問題が発生するアンチパターンです。
例えば、SQLクエリでSELECT * (ワイルドカード)を用いて、カラム名を省略することなどである。
■SELECT文
- カラム名を指定
SELECT bug_id,date_reported,summary,description,resolution,reported_by,assigned_to,verified_by,status,priority,hours
FROM Bugs;
- ワイルドカード機能使用 (アンチパターン)
SELECT * FROM Bugs
ワイルドカード記号のアスタリスク(*)はすべての列を意味するため、カラム名のリストは明示的ではなく暗黙的に指定される。
よってワイルドカードを用いることでクエリを簡潔に書くことが出来る。
■INSERT文
INSERT文を書く際にも、デフォルト順の恩恵を受けるのがスマートな方法に見える
VALUEに並べた値は、すべての列にテーブルで定義された順番で適用される。
- カラム名を指定
INSERT INTO Accounts(account_id,account_name,first_name,last_name,email,password_hash,portrait_image,hourly_rate)
VALUES (DEFAULT,'bkarwin','Bill','karwin','bill@example.com',SHA2('xyzzy',256),NULL,49.95);
- 指定なし
INSERT INTO Accounts VALUES (DEFAULT,'bkarwin','Bill','karwin','bill@example.com',SHA2('xyzzy',256),NULL,49.95);
こちらも列を明示的に指定せずに書くことで、ステートメントは短くなる。
#メリット
- タイプ数を減らすことで開発効率が上がる
- ステートメントが短くなり、可読性が上がる
#デメリット
- リファクタリングにおける問題
- パフォーマンスとスケーラビリティ（拡張性）の低下
■リファクタリングに置ける問題
↑Bugsテーブル (現在列は11個)
例 Bugテーブルにdate_due列を加える。よってBugsテーブルの列数は12個になる。
ALTER TABLE Bugs ADD date_due DATE;
以下のINSERTクエリがエラーになる。
→テーブルには12の列があるのにクエリには11列しか値を指定していない為である。
INSERT Into Bugs VALUES (DEFAULT,CURRENT_DATE,'新規バグ','テストが失敗します',NULL,123,NULL,NULL,DEFAULT,'MEDIUM',NULL);
暗黙的な列を使ったINSERTステートメントでは、テーブルに列が定義されている順番と同じ順番で、すべての列に値を与えなければならない。
列定義に変更があった場合には、エラーになる、または誤った列に値が格納されてしまうことが懸念される。
また、誰かが列の追加、削除、名前変更などを行うと、別の列の値を参照してしまうこともある。
その際ワイルドカードを使用していると、コードを辿って原因を解明するのは大変である。
■パフォーマンスとスケーラビリティの低下
ワイルドカードを使用すると、クエリが多くの列をフェッチするようになるため、多くのデータがアプリケーションとデータベースサーバの間を行き来しなければならなくなる。本番稼働中のアプリケーションでは多くのクエリを同時に実行することがあり、これらは同じネットワーク帯域幅を利用する。数千行を返すクエリを100個も同時に実行すれば、帯域幅がいっぱいになってしまう恐れがある。
#アンチパターンの見つけ方
プロジェクトが以下のような状況になった場合は、暗黙的な列指定を不適切に使用したために問題が発生するインプリシットカラムアンチパターンに陥っている可能性がある。
-
「アプリケーションに障害が発生した。データベースの結果セットを古い列名で列を参照していたことが原因だ。コードをすべて修正したつもりだったが、修正漏れがあるかもしれない」
→ 列の追加、削除、名前変更、順番変更などによってテーブル定義を変更したものの、テーブルを参照するアプリケーションコードを修正していなかったことが原因である。参照しているアプリケーションコードをすべて修正するのは、大変な手間がかかってしまう。 -
「何日もかけてネットワークのボトルネックを突き止めた。原因はデータベースサーバへのトラフィックが多いことだった。統計値によれば、クエリは平均で2MB以上のデータをフェッチしているが、実際に表示しているのはその10分の1以下だった」
→不要なデータをフェッチしすぎている
#アンチパターンを用いても良い場合
-
アドホックなSQLなどを素早く書きたい場合
ワイルドカードの使用は、アドホック(その場限りで使い捨て)なSQLなどを素早く書きたい場合には便利である。 -
実行時の効率よりも開発時の効率を重視する場合
やはり、多くの列名を入力するのには時間がかかってしまう。ワイルドカードを使うことでキー入力は減り、クエリも短くなる。
また、SQLクエリが長くなることで、アプリケーションとデータベース間のネットワークトラフィックが増えてしまうというプログラマーもいる。理論上、クエリが長ければトラフィックが増える可能性がありますが、一般的にはクエリが多くの行を返すことによって増加するデータの方がはるかに多くの帯域幅を使用するので適切な判断が必要である。開発時の効率、クエリの短さと可読性のほうが大事と考えるのであればワイルドカードの使用が最適かもしれないが、前述しているリスクの対処を検討しておくべきである。
#解決策. 列名を明示的に指定する
ワイルドカードや暗黙的な列指定を使わずに、必要な列名は明示的に指定するようにする
列名をすべて入力するのは手間がかかるが、下記のメリットがある。
SELECT bug_id,date_reported,summary,description,resolution,reported_by,assigned_to,verified_by,status,priority,hours FROM Bugs;
INSERT INTO Accounts (account_name,first_name,last_name,email,password_hash,portrait_image,hourly_rate)
VALUES ('bkarwin','Bill','Karwin','bill@example.com','xyxxy',NULL,49.45);
- テーブル定義の列の順番が変更された場合でも、クエリ結果の列の位置は変わらない
- テーブルに列が加えられた場合でも、クエリ結果に影響はない
- テーブルから列が削除された場合、クエリはエラーを返してくれ、修正すべきコードを特定することが出来る。原因究明のためにコード全体を探し回る必要がなくなる。
INSERTステートメントでも列を明示的に指定すると、同じようなメリットを得ることが出来る。明示的に指定した列順はテーブル定義の列順よりも優先されるため、値は必ず意図した列に割り当てられる。新たに追加した列がステートメントで指定されていない場合は、値がデフォルト値またはNULLになります。削除された列が指定されているステートメントはエラーを返しますが、「できるだけ早い段階で失敗すべし」という原則に従っている良い例と言うことが出来る。
YAGNI(ヤグニ)の考え方
You ain’t gonna need it
機能は必要になるまで実装しないほうが良い。という考え方
今回のアンチパターンもこの考え方に含まれる。
#まとめ
Select文を作成するときはでワイルドカード（*）を使用することが妥当か検討するクセをつけ、
なるべくは必要な列だけを指定するようにしましょう