SQL実践入門第３章を読んだ

この本は章末にその章のポイントがまとめられているので、そのまとめを自分なりにわかりやすく噛み砕いて説明、要約することが内容の理解にすごく繋がっている気がした。なので、今回は章末のまとめに対してそれを補完する形でブログを書いてみた。

初心者がやりがちなUNIONを使った条件分岐のアンチパターン

条件分岐の記述に、わかりやすいUNIONを使いがちだが、UNIONは本来条件分岐のための道具ではない。 WHERE句だけが異なる複数のSELECT文をマージすることによって複数の条件に合致する1つの結果セットを得る方法は、パフォーマンス面で大きな欠陥を抱えている。なぜなら、SQL文の実行回数こそ１回だが、内部的には複数のSELECT文を実行する実行計画として解釈されることが多いためテーブルへのアクセス回数が増え、I/Oコストが大きく膨らむ傾向があるからである。

SQLのパフォーマンスは低速なストレージへのI/Oをどれだけ減らせるかが鍵

なので、極力UNIONを使わずにCASE式によるSQL本流の考え方を身につける

UNIONで条件分岐を表現したくなったら、冗長性症候群にかかっていないか疑おう

簡単にレコード集合をマージできるという点でUNIONは非常に便利な道具だが、これを安易に条件分岐のためのツールとして使おうとするのは危険である。なぜなら、安易にSELECT文全体を連ねて冗長なコードを記述してしまうと、その分だけ無駄なテーブルへのアクセスが発生してSQLのパフォーマンスが劣化し、また、物理リソース（ストレージのI/Oコスト）も無駄に消費してしまう。このように、安易にUNIONで条件分岐を使ってしまう傾向をこの本では冗長性症候群と呼んでいる。

INやCASE式で条件分岐を表現できれば、テーブルへのスキャンを大幅に減らせる可能性がある

格言「条件分岐をWHERE句で行うのは素人のやること。プロはSELECT句で分岐させる」 CASE式による条件分岐をうまく使うことによって、UNIONを削減し、それによってテーブルへのアクセスを削減することが可能になる。 CASE式はSQLを使いこなす上で生命線となる道具であるが、その理由は表現力の高さだけでなく、パフォーマンス改善にも大きな力を発揮するからである。

効率の良いSQLを書くために、文から式へのパラダイムシフトを習得しよう

UNIONによる条件分岐は、SELECT「文」の単位で分岐させている。「文」を基本単位として思考という点で、手続き型の発想に囚われた解だと言える。対して、CASE式による分岐は文字通り「式」をベースにした思考である。このように、手続き型の世界から宣言型の世界へのパラダイムシフトを成功させることはSQLをマスターする1つの鍵である。この思考を身につけるためのコツは、「この問題を手続き型言語で解いたら」と考えた時、IF文を使う箇所があれば、それをSQLに翻訳したらCASE式を使う、というルールを持っておくこと。

CASE式とUNIONでそれぞれ条件分岐を書いたSQLの実行計画メモ

// CASE式による条件分岐
// 実行計画から、データベースへのアクセスが１度の順次読み込みで完結していることがわかる。

example-db=> EXPLAIN SELECT emp_name,
       CASE WHEN COUNT(*) = 1 THEN MAX(team)
       WHEN COUNT(*) = 2 THEN '2つ以上を兼務'
       WHEN COUNT(*) >= 3 THEN '3つ以上を兼務'
       END AS team
FROM Employees
GROUP BY emp_name;
                            QUERY PLAN
-------------------------------------------------------------------
 HashAggregate  (cost=28.23..31.73 rows=200 width=64)
   Group Key: emp_name
   ->  Seq Scan on employees  (cost=0.00..18.10 rows=810 width=64)
(3 rows)


// UNIONによる条件分岐（アンチパターン）
// SELECT文の数だけ順次読み込みが走っていることがわかる
// また、GROUP BYのハッシュ演算も3回走ってしまっている

example-db=> EXPLAIN SELECT emp_name,
       MAX(team) AS team
FROM Employees
GROUP BY emp_name
HAVING COUNT(*) = 1
UNION
SELECT emp_name,
       '2つを兼務' AS team
FROM Employees
GROUP BY emp_name
HAVING COUNT(*) = 2
UNION
SELECT emp_name,
       '3つ以上を兼務' AS team
FROM Employees
GROUP BY emp_name
HAVING COUNT(*) >= 3;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 HashAggregate  (cost=77.35..78.04 rows=69 width=64)
   Group Key: employees.emp_name, (max(employees.team))
   ->  Append  (cost=24.18..77.01 rows=69 width=64)
         ->  HashAggregate  (cost=24.18..26.68 rows=1 width=64)
               Group Key: employees.emp_name
               Filter: (count(*) = 1)
               ->  Seq Scan on employees  (cost=0.00..18.10 rows=810 width=64)
         ->  HashAggregate  (cost=22.15..24.65 rows=1 width=64)
               Group Key: employees_1.emp_name
               Filter: (count(*) = 2)
               ->  Seq Scan on employees employees_1  (cost=0.00..18.10 rows=810 width=32)
         ->  HashAggregate  (cost=22.15..24.65 rows=67 width=64)
               Group Key: employees_2.emp_name
               Filter: (count(*) >= 3)
               ->  Seq Scan on employees employees_2  (cost=0.00..18.10 rows=810 width=32)
(15 rows)