ページ187-196p
17章 スパゲッティクエリ
17章のまとめ
ある事柄を説明するために、必要以上に多くの実体を仮定するべきではない
- 集計・グルーピングなどを駆使した複雑なクエリはスパゲッティになりがち。
- スパゲッティはメンテが大変だったり、バグを見つけにくかったりするから、一つにまとめず、シンプルなクエリに分割しましょう。
例えば、以下の情報が欲しいとする.
- 取り扱っている製品の数
- バグを修正した開発者の数
- 開発者一人あたりの平均バグ修正数
- 修正したバグの中で顧客から報告されたバグの数
以下のクエリは誤った情報を返す.
SELECT COUNT(bp.product_id) AS how_many_products,
COUNT(dev.account_id) AS how_many_developers,
COUNT(b.bug_id)/COUNT(dev.account_id) AS avg_bugs_per_developer,
COUNT(cust.account_id) AS how_many_customers
FROM Bugs b INNER JOIN BugsProducts bp ON b.bug_id = bp.bug_id
INNER JOIN Accounts dev ON b.assigned_to = dev.account_id
INNER JOIN Accounts cust ON b.reported_by = cust.account_id
WHERE cust.email = NOT LIKE '%example.com'
GROUP BY bp.product_id
17.1 目的: SQLクエリの数を減らす
短く書いたら優雅だし、ネットワーク間のやりとりが減るというメリットがある.
17.2 アンチパターン: 複雑な問題をワンステップで解決しようとする
一つのクエリでまとめることは必ずしもいいことではない。他のプログラミング言語でもそうでしょう。
17.2.1 意図に反した結果
一つのクエリで全てを処理しようとすると、 デカルト積(Cartesian Product) が生じることがある。デカルト積とは、クエリで指定する二つのテーブルがリレーションシップを制限する条件を持たないときに生じる。この制限がないと、二つのテーブルを結合することで一つのテーブルの各行が、もう一つのテーブルの全ての行とペアになってしまう。
以下例。バグデータベースに、製品別の修正済みバグ数と未修正済みバグ数を問い合わせたいとする。
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;
実際は、修正済みのバグ数が11件、未修正のバグが7件あるとすると、
クエリの結果は、以下のようになる。
- product_id: 1
- count_fixed: 77
- count_open: 77
BugsProductsテーブルがBugsテーブルの二つの部分集合と結合され、結果としてこれらの2つの部分集合のデカルト積が生じた。
このクエリで示される唯一のリレーションシップは、BugsProductsテーブルと、Bugsテーブルの各部分集合の間にある。全ての修正済み(FIXED)バグと全ての未修正(OPEN)バグの組み合わせを制限する条件がないので、11x7の行が生じた。
このように一つのクエリで二つのタスクを実現使用とすると、意図しないデカルト積が頻繁に生まれる。
17.2.2 さらなる弊害
- 上記のように意図しない結果が導かれる
- クエリの記述や修正、デバッグが困難になる
- 実行時のコスト(結合や相関サブクエリなどの多い手の込んだSQLクエリはシンプルなクエリに比べて最適化処理や高速な実行が難しくなる.実行するSQLクエリの数を減らせばパフォーマンスが上がるとは限らない.)
17.3 アンチパターンの見つけ方
こんなセリフをきいたら「スパゲッティクエリ」アンチパターンに注意せよ
「SUM関数やCOUNT関数の結果がありえないくらいに大きくなっているのはなぜ？」
意図しないデカルト積かも
「このお化けみたいに複雑なSQLクエリを書くのに丸一日もかかったよ！」
そんなに複雑なクエリを書くな
「このレポート出力には、もう何も追加できない。このSQLクエリを書き直すのには手間がかかりすぎる」
メンテできないものを書くな
「このクエリに、もう一つDISTINCTを追加してみよう」
クエリや集約関数の修飾子にDISTINCTキーワードを使って、行の重複を防ごうとすると、DBはソートと重複ハイjのためだけに内部で中間結果を生成しなくてはいけなくなってしまう。
17.4 アンチパターンを用いてもいい場合
- BI
- Reporting tools
- データのソートなどDBの得意領域の処理を噛ませたいとき
17.5 解決策: 分割統治を行う
まったく同じ予測をする二つの競合する理論があるときは単純な方が優れている。
17.5.1 ワンステップずつ
意図しないデカルト積が生じているテーブル間に論理的な結合条件が見つからない場合、単に条件がそもそも存在しない可能性もある。デカルト積を避けるには、幾つかのクエリに分割する必要がある。
SELECT p.product_id, COUNT(f.bug_id) AS count_fixed
FROM BugsProducts p
LEFT OUTER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'FIXED'
WHERE p.product_id = 1
GROUP BY p.product_id;
SELECT p.product_id, COUNT(o.bug_id) AS count_open
FROM BugsProducts p
LEFT OUTER JOIN Bugs o ON p.bug_id = o.bug_id AND o.status = 'OPEN'
WHERE p.product_id = 1
GROUP BY p.product_id;
- 分割によりデカルト積を防げる
- メンテをしやすい
- 実行コストが低い
- 可読性が高い
17.5.2 UNIONを用いる
複数のクエリの結果は、UNIONによって一つの結果セットにまとめることができる。
(SELECT p.product_id, 'FIXED' AS status, COUNT(f.bug_id) AS bug_count
FROM BugsProducts p
LEFT OUTER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'FIXED'
WHERE p.product_id = 1
GROUP BY p.product_id)
UNION ALL
(SELECT p.product_id, 'OPEN' AS status, COUNT(o.bug_id) AS bug_count
FROM BugsProducts p
LEFT OUTER JOIN Bugs o ON p.bug_id = o.bug_id AND o.status = 'OPEN'
WHERE p.product_id = 1
GROUP BY p.product_id)
ORDER BY bug_count DESC;
各サブクエリの結果を合わせたものが、クエリの結果として出力される.
UNIONは、両方のサブクエリの列に互換性があるときにのみ使用できる.
17.5.3 CASE式とSUM関数を組み合わせる
条件ごとの集約を一つのクエリでシンプルに行うために、CASE式とSUM関数を組み合わせる方法がよく使われる.
SELECT p.product_id,
SUM(CASE b.status WHEN 'FIXED' THEN 1 ELSE 0 END) AS count_fixed,
SUM(CASE b.status WHEN 'OPEN' THEN 1 ELSE 0 END) AS count_open
FROM BugsProducts p
INNER JOIN Bugs b USING (bug_id)
WHERE p.product_id = 1
GROUP BY p.product_id;
17.5.4 上司の問題を解決する
- 取り扱っている製品の数
- バグを修正した開発者の数
- 開発者一人あたりの平均バグ修正数
- 修正したバグの中で顧客から報告されたバグの数
最善の解決策は、これらのタスクを分割して処理すること
課が取り扱っている製品の数
SELECT COUNT(*) AS how_many_products
FROM Products;
バグを修正した開発者の数
SELECT COUNT(DISTINCT assigned_to) as how_many_developers
FROM Bugs
WHERE status = 'FIXED'
開発者一人あたりの平均バグ修正数
SELECT AVG(bugs_per_developer) AS average_bugs_per_developer
FROM (SELECT dev.account_id, COUNT(*) AS bugs_per_developer
FROM Bugs b INNER JOIN Accounts dev
ON b.assigned_to = dev.account_id
WHERE b.status = 'FIXED'
GROUP BY dev.account_id) t;
※ SQLあんまり知らない人(自分)向けメモ
SELECT dev.account_id, COUNT(*) AS bugs_per_developer
FROM Bugs b INNER JOIN Accounts dev
ON b.assigned_to = dev.account_id
WHERE b.status = 'FIXED'
GROUP BY dev.account_id
ここは返り値として、assigned_idとbugs_per_developer(値はcountが入る)を持つテーブルが返される。そしてそれをtという文字でエイリアスしてる。
FROM Bugs b INNER JOIN Accounts dev
ON b.assigned_to = dev.account_id
WHERE b.status = 'FIXED'
ここでは、INNER JOINした上でFIXEDのものに限定している.
GROUP BY
dev.account_id でgroup_byしているため、上のリンクにあるように、account_id以外に紐づくそれぞれの情報は集約されてしまう。それらの集約された情報を取り出すには、集計関数を利用する必要がある。COUNT()や、AVG()は集計関数である。
こうして、この部分のクエリでは、account_idとそれに集約されたbugの個数が返り値として返っている。さらにそうしてできたテーブル tにたいして、AVG()を用いて計算している。
修正したバグの中で顧客から報告されたバグの数
SELECT COUNT(*) AS how_many_customer_bugs
FROM Bugs b INNER JOIN Accounts cust ON b.reported_by = cust.account_id
WHERE b.status = 'FIXED' AND cust.email NOT LIKE '%@example.com';
17.5.5 SQLを用いたSQLの自動的な記述
複雑なSQLクエリを分割すると、データの値によってわずかに異なる、似たようなクエリをいくつも作成することがある.
コード生成 (コンパイルや実行が可能なコードをコードから出力する技法) を用いれば、そういった繰り返しを避けることができる.
例えば、以下のクエリは複数のUPDATEステートメントを出力する.
SELECT CONCAT('UPDATE Inventory',
' SET last_used = ''', MAX(u.usage_date), '''',
' WHERE inventory_id = ', u.inventory_id, ';') AS update_statement
FROM ComputerUsage u
GROUP BY u.inventory_id;
17章のまとめ
ある事柄を説明するために、必要以上に多くの実体を仮定するべきではない
- 集計・グルーピングなどを駆使した複雑なクエリはスパゲッティになりがち。
- スパゲッティはメンテが大変だったり、バグを見つけにくかったりするから、一つにまとめず、シンプルなクエリに分割しましょう。