はじめに
こんにちは。小川です。
以前から気になっていた「SQLアンチパターン」という本を読みました。
とても良い本で今まで気づかずにやっていたことがSQLアンチパターンなんだと知ることができました。
勉強になったので、学んだことを言語化して量が多いのでいくつかの記事に分けて紹介していこうと思います。
今回は第Ⅲ部のクエリのアンチパターンについて記載します。
この記事は「SQLアンチパターン」の気づき（DB物理設計2）の続きになります。
13章.フィア・オブ・ジ・アンノウン（恐怖のunknown）
アンチパターン
NULLを一般値として使う、または一般値をNULLとして使うアンチパターンです。
-
式でNULLを扱う
以下のクエリでhoursがNULLの場合、NULLは0にはならないのでNULL+10の結果としてNULLが返されます。SELECT hours + 10 FROM Bugs; -
NULLを許容する列の検索
以下のクエリではBugsテーブルからassigned_to列に123を持つ行のみ返します。
assigned_to列に他の値またはNULLを持つ行は返されない。SELECT * FROM Bugs WHERE assigned_to = 123;以下のクエリではBugsテーブルからassigned_to列に123を持たない行のみ返します。
SELECT * FROM Bugs WHERE NOT assigned_to = 123;しかし、このクエリもassigned_to列にNULLが割り当てられた列を返さない。
WHERE句の条件式はTRUEの場合のみ該当の行を返すので、NULLを用いた比較ではTRUE、もしくはFALSEではなく不明（unknown）を返すため、以下のクエリのようにNULLを検索しても行を取得することはできない。
SELECT * FROM Bugs WHERE assigned_to = NULL; -
プリペアードステートメントでNULLを使用する
プリペアードステートメントでパラメータ化したSQLでNULLを使用することは困難。
以下のクエリでパラメータに整数値を使用した場合該当の結果を取得するので、NULLを使用することができない。SELECT * FROM Bugs WHERE assigned_to = ?;
解決策：NULLを一意な値として使う
-
スカラー式でのNULL
NULLの問題のほとんどは真偽値で使用する場合にNULLはどのような挙動をするか知らないことです。
以下の式では予想した結果と実際の結果での差異です。式 予想した結果 実際の結果 NULL = 0 TRUE NULL NULL = 12345 FALSE NULL NULL <> 12345 TRUE NULL NULL + 12345 12345 NULL NULL = NULL TRUE NULL NULL <> NULL TRUE NULL -
論理式でのNULL
上記に続き論理式でも間違えやすくなっています。
以下の式では予想した結果と実際の結果での差異です。式 予想した結果 実際の結果 NULL AND TRUE FALSE NULL NULL AND FALSE FALSE FALSE NULL OR FALSE FALSE NULL NULL OR TRUE TRUE TRUE NOT(NULL) TRUE TRUE -
NULLの検索
NULLを検索するときは等式(=)では取得できないためIS NULLを使う必要があります。SELECT * FROM Bugs WHERE assigned_to IS NULL; SELECT * FROM Bugs WHERE assigned_to IS NOT NULL; -
NOT NULL制約を宣言する
NULLがアプリケーションのポリシーに反する場合や、その列においてNULLが意味をなさない場合には、列にNOT NULL制約を宣言するのが良い。
14章.アンビギュアスグループ（曖昧なグループ）
アンチパターン
アンビギュアスグループとは、グループ内で最大値（または最小値）を持つ行を取得する際、その行のグループ化されていない列を取得する際に発生するアンチパターンです。
--例）実行可能
SELECT
product_id,
Max(date_reported) AS latest
FROM
Bugs
INNER JOIN
BugsProducts
USING (bug_id)
GROUP BY
product_id
-- 例）bug_idを追加すると、クエリエラー
SELECT
product_id,
Max(date_reported) AS latest,
Bugs.bug_id
FROM
Bugs
INNER JOIN
BugsProducts
USING (bug_id)
GROUP BY
product_id
単一値の原則(Single-Value Rule)
単一値の原則とは、「SELECT句で列挙した列について、行グループごとに単一の値でなければならない」という規則です。
SELECT
product_id,
Max(date_reported) AS latest,
Bugs.bug_id
FROM
Bugs
INNER JOIN
BugsProducts
USING (bug_id)
GROUP BY
product_id
この例では、追加したbug_id列が問題となり、product_idとbug_idは一対多の関係にあります。そのため、product_idをGROUP BY句で集約した際、対応する複数のbug_idの全ての値を表すことができません。
解決策：曖昧でない列を使用する
-
関数従属属性のある列のみにクエリを実行する
SELECT product_id, Max(date_reported) AS latest FROM Bugs INNER JOIN BugsProducts USING (bug_id) GROUP BY product_idこのクエリは、製品ごとの最新の日付を返します。最新のバグに対応するbug_idは返されませんが、それで十分な場合もあります。
-
相関サブクエリを使用する
SELECT bp1.product_id, b1.date_reported AS latest, b1.bug_id FROM Bugs b1 INNER JOIN BugsProducts bp1 USING (bug_id) WHERE NOT EXISTS ( SELECT * FROM Bugs b2 INNER JOIN BugsProducts bp2 ON b2.bug_id = bp2.bug_id WHERE bp1.product_id = bp2.product_id --date_reportedが最新でないデータを抽出し、NOT EXISTで除外 AND b1.date_reported < b2.date_reported )読みやすく、コードの記述も簡単な解決策です。
ただし、相関サブクエリが外部クエリの各行に対してそれぞれ実行されるため、最善のパフォーマンスは得られない。 -
導出テーブルを使用する
SELECT m.product_id, m.latest, b1.bug_id FROM Bugs b1 INNER JOIN BugsProducts bp1 ON b1.bug_id = bp1.bug_id INNER JOIN ( --導出テーブル SELECT bp2.product_id, MAX(b2.date_reported) AS latest FROM Bugs b2 INNER JOIN BugsProducts bp2 ON b2.bug_id = bp2.bug_id GROUP BY bp2.product_id ) AS m ON bp1.product_id = m.product_id AND b1.date_reported = m.latest導出テーブルによる解決策は、相関サブクエリに対する、よりパフォーマンスの高い選択肢として使用されます。
-
他の列に対しても集約関数を使用する
SELECT product_id, Max(date_reported) AS latest, Max(Bugs.bug_id) FROM Bugs INNER JOIN BugsProducts ON Bugs.bug_id = BugsProducts.bug_id GROUP BY product_iddate_reportedの最大値とbug_idの最大値が必ず一致することが確約されている場合のみ、上記のクエリにより目的の値を取得できます。
まとめ
今回は第Ⅲ部のクエリのアンチパターンについて一部まとめてみました。
この記事はこの本を読んで、私がこういう理解をしましたということを言語化してみました。
とても勉強になる本なので皆様もぜひ読んでみてください。
以上、小川でした。