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

次の例は Books テーブルと Authors テーブルを JOIN するクエリです。

SELECT * FROM Books b INNER JOIN Authors a ON b.author_id = a.author_id;

この結果を PHP、はもう忘れたので、Perl で取り出そうとして、よく次のようなミスをします。

while (my $row = $sth->fetchrow_hashref) {
  print "Title: $row->{title}\n";
}

こんなのよく書きますよね？ しかし実際に試してみると、$row->{title} は全て NULL を返していました。

原因は、Books テーブルには title（本の題名）、Authors テーブルには title（肩書き、Mr., Mrs みたいな）、のように、同じ名前のカラムが存在したためです。肩書きを持つ人はそう多くないため、$row->{title} は全部空になってしまったのです。

この場合は、以下のようにクエリを改善することで解決します。

SELECT b.title, a.title AS salutation
FROM Books b INNER JOIN Authors a ON b.author_id = a.author_id;

要するに、こういうときに *（ワイルドカード）使っちゃダメってことです。今回の議題はこれです。

18.1 目的: タイプ数を減らす

とはいえ、ワイルドカード使わないと、次のような長大なクエリを使うことになります。これはプログラマの美徳である「怠慢」や DRY（Don't Repeat Yourself）原則にももとります。

SELECT bug_id, date_reported, summary, description, resolution,
  reported_by, assigned_to, verified_by, status, priority, hours
FROM Bugs;

SELECT * FROM Bugs;

すっきり！ INSERT 文でも同じです。

-- Before
INSERT INTO Accounts (account_id, account_name, first_name, last_name, email,
  password_hash, portrait_image, hourly_rate) VALUES (DEFAULT,
  'bkarwin', 'Bill', 'Karwin', '[e-mail]', SHA2('xyzzy', 256), NULL, 49.95);

-- After
INSERT INTO Accounts VALUES (DEFAULT,
  'bkarwin', 'Bill', 'Karwin', '[e-mail]', SHA2('xyzzy', 256), NULL, 49.95);

だいぶ短くなりました。

18.2 アンチパターン: ショートカットの罠に陥る

でも、この「怠慢」には大きな落とし穴があります。

18.2.1 リファクタリングにおける問題

先ほどの Bugs テーブルに date_due カラムを追加してみます。

ALTER TABLE Bugs ADD COLUMN date_due DATE;

すると、INSERT 文が失敗するようになりました。

INSERT INTO Bugs VALUES (DEFAULT, CURRENT_DATE,
    '新規バグ', 'テスト[T987]が失敗します...',
    NULL, 123, NULL, NULL, DEFAULT, 'MEDIUM', NULL);

-- SQLSTATE 21S01: Column count doesn't match value count at row 1

これも皆さん一度は経験があるでしょう。VALUES の列数が一致しないと、INSERT は失敗してしまいます。

列を削除するときも同じです。横着して、以下のようなコードを書いていたとしましょう。

my @row = $dbh->selectrow_array(<<SQL);
  SELECT * FROM Bugs WHERE bug_id = 1234
SQL
my $hours = $row->[10];

これは、「時刻」列がテーブルの 11 番目にあることを期待しています。しかし、その後の変更によってカラムが削除されたりすると……

ALTER TABLE Bugs DROP COLUMN verified_by;

困りますね。困らないかも知れませんが。（verified_by カラムが 12 番目以降の物だったら問題ありません） いずれにしろ、これは褒められたコードではありません。

18.2.2 隠れた代償

ワイルドカードはパフォーマンスにも悪影響を及ぼします。本当に、あなたは Books テーブルの全てのカラムが必要なんでしょうか？

SELECT * FROM Bugs;

必要だったらすみませんが、たいていはそうではありません。このテーブルが高々数千行しか持たないものだとしても、リクエストが毎秒 1000 回あるのだとしたら恐ろしい結果になります。うん。これもなんか身に覚えがありますね。

また、ORM（Perl なら DBIx::Class、Ruby なら ActiveRecord とか） 使ってると、こんなクエリが知らず知らずのうちに発生しています。でも、そこまで頭の悪い ORM は最近少ないかも……

と、本書にはあるんですが、手元の Rails アプリでクエリ見たら * クエリがものすごい発生してました。だめだこりゃ。（アプリが）

18.2.3 求めなければ得られない

これもよくある要望ですが、**特定のカラムだけ除いて表示したい！**場合があります。

SELECT （やたらと長い description 列を除いたもの以外全て） FROM Bugs;

残念ですが、**こんな書き方は SQL に用意されていません。**諦めて下さい。

18.3 アンチパターンの見つけ方

アプリケーションに障害が発生した。データベースの結果セットを古い列名で列を参照していたことが原因だ。コードをすべて修正したつもりだったが、修正漏れがあるかもしれない

あなたの同僚がこんなこと言い出したら赤信号です。まあたいていはその同僚のせいじゃないんですが。大昔の誰か（ひょっとしたら自分）が犯人です。経験上。

何日もかけて、ようやくネットワークのボトルネックを突き止めた。原因は、データベースサーバーへのトラフィックが多いことだった。統計値によれば、クエリは平均で2MB以上のデータをフェッチしているが、実際に表示しているのはその10分の1以下だった

これはコードの設計が悪いことにも寄りますが、先ほど例に挙げたように ORM が原因なこともあります。（頭の悪い）ORM が嫌われる原因の一つです。

18.4 アンチパターンを用いても良い場合

検証中に自分でちょっとだけ試してみるクエリならば、ワイルドカードの使用は問題ありません。たいていは、一度だけ書いて、二度と使わないものだからです。

でも、**本番サーバーで実行するときは気をつけて！**本番サーバーは開発サーバーよりもパフォーマンス影響が大きいです。世の中には数百カラムを超えるクソテーブルがあるものです。データ量が推測できない場合は安易にクエリを実行してはいけません。

多くの列名を入力するのには時間がかかります。実行時の効率よりも開発時の効率を重視するプログラマーがいます。（中略）開発時の効率、クエリの短さと可読性の方が重要であるなら、ワイルドカードを使いましょう。

と本書には書いてあるんですが、個人的には反対です。列名の入力がメンドイなら DDL をどっかからコピペしてくればいいだけです。レビュワーは大変かもしれませんが、* 使われた方がもっと大変です。（経験上）

コピペした後綺麗にインデントをそろえて見やすくするのが大変だって？ **そんな人は Vim を使いましょう！**Vim で正規表現使えば何でもできますし、さらに EasyAlign プラグインを使うとコードの整形もこんなに簡単に（省略されました。続きを読むにはワッフルワッフルと書き込んでも無駄です）

脱線しましたが、クエリが長くなるとネットワークトラフィックに悪影響がある場合も確かにあります。でもそれも、テーブル定義とコードの書き方のどっちかがおかしい場合がほとんどです。カラムが多すぎて困るテーブルは正規化することも考えるべきです。

18.5 解決策: 列名を明示的に指定する

ワイルドカードを使わず、明示的に列を書きましょう。上でもさんざん言ってますが。

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', '[e-mail]',
  SHA2('xyzzy', 256), NULL, 49.95);

もしくは、SQL ビルダー使った方がいいですね。

my $sql = SQL::Abstract->new;

{
  my ($stmt, @binds) = $sql->select('Bugs', [ ... ]);
}

{
  my ($stmt, @binds) = $sql->insert('Accounts', +{
    account_name  => 'bkarwin',
    password_hash => \"SHA2('xyzzy', 256)",
    ...
  });
}

Rails というか、ActiveRecord なら select メソッド使うのかなあ。でも毎回書くのはちょっと……

necessary_columns = %w[bug_id date_repo ...]
@some_bugs = Bugs.select necessary_columns

18.5.1 誤りの防止

本書では「ポカヨケ」という用語が用いられています。多少不便にしてでも、致命的な誤りを防ぐように設計する手法のことです。Wikipedia には中々恐ろしい例が記載されています。

切断機を稼働させるスイッチは本来は1つで十分であるが、片手で押している際に、ちょっとした不注意でもう片方の腕が切断される事故（ミス）が起きる可能性がある。そこで、スイッチを機械の左右両端にあえて設置し、両手で押さないと稼働しないようにすることで、ポカ（事故）を避ける仕組みが設けられる

ひどい SQL 書いたせいで腕が切断されるほどのことは起こりませんが、無用なトラブルは起こらない方がいいに決まっています。「解決策」にあるように、カラムをきちんと明記することで、テーブル定義が変更されても、

• SELECT の結果は変わらない。
• INSERT もきちんと動く。
• 必要なカラムが消えた場合はちゃんとエラーで落ちる。（原因が明確なので修正も簡単）

のように、欠点よりも利点の方が大きいです。

18.5.2 それは多分、必要ない (YAGNI: You Ain't Gonna Need It)

Wikipedia には YAGNI な例がいくつか挙げられています。

あとで使うだろうとの予測の元に作ったものは、実際には10%程度しか使われない。したがってそれに費やした時間の90%は無駄になる。

なんかマーフィーの法則っぽい。（古い）

いつも * を使って全カラムを取得することに慣れていると、いざパフォーマンスの問題が発生したときに原因の究明が難しくなります。

また、常にカラムを明記する癖を付けておくと、使うかどうかわからないようなカラムはテーブルを定義する際、最初から作らないようになるでしょう。結果的に、それが一番帯域を節約することになるのです。

18.5.3 ワイルドカードを使えない局面はいずれ訪れる

最初は * での取得で問題なくても、いずれかならず、個別に列を選択する必要が生じます。一番初めの例に出た例がまさにそれですね。

SELECT b.title, a.title AS salutation
FROM Books b INNER JOIN Authors a ON b.author_id = a.author_id;

最初からカラムを明記しておけば、title の重複にも気づけた筈です。

というわけで、何度も言いましたが、結論。必要な列だけを指定するようにしましょう。