SQLアンチパタ`ンi氏 仝スパゲッティクエリ々
?
3 likes?3,666 views
2014/04/03 (直) SQLアンチパタ`ンi氏 仝スパゲッティクエリ々 http://sqlap.doorkeeper.jp/events/10206
SQLアンチパタ`ンi氏 仝スパゲッティクエリ々
- 3. 3 17.1 朕議 SQLクエリの方をpらす SQLプログラマ`が恷も謹く岷中する} どのようにして朕の念の碧並を１つのクエリでgFするか 盾Q貨はシンプルにしたい タスクを１つのクエリで盾Qすることで、クエリを仝囘に々 仝紳糞弔法くことを朕議とする。
- 4. 4 17.2 アンチパタ`ン SQLは掲械に燕F薦にれた冱Z??? ? １つのクエリやステ`トメントで謹くのことをgFできる ? だからといって、１つのクエリですべてのタスクをI尖 することを崙するものではない }jな}をワンステップで盾Qしようとする}jな}をワンステップで盾Qしようとする
- 5. 5 17.2.1 吭蹐坊瓦靴申Y惚 (1/5) ¢ デカルトe Cartesian product ? クエリで峺協する２つのテ`ブルがvB┘螢讒`ション シップを崙泙垢詭周を隔たないときに伏まれる。 ? この訳周がないと、２つのテ`ブルをY栽することに よって、１つのテ`ブルの光佩が、もう１つのテ`ブルの すべてのテ`ブルのすべての佩とペアになってしまう。 ? いわゆる、岷e鹿栽 (Cross Join)。
- 6. 6 17.2.1 吭蹐坊瓦靴申Y惚 (2/5) 揖じテ`ブル２つにし、 prodcut_idをキ`にJOIN??? ? 岷e(デカルトe)がk伏 そのu瞳の俐屎gみのバグ方が11周、 隆俐屁のバグが7周であるはずなのに、 I圭とも 77周 ┌ 11周 〜 7周となっている。 ＊ Spaghetti-Query/anti/cartesian.sql
- 7. 7 17.2.1 吭蹐坊瓦靴申Y惚 (3/5) 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; FROM BugsProducts p INNER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'FIXED' INNER JOIN Bugs o ON p2.bug_id = o.bug_id AND o.status = 'OPEN' INNER JOIN BugsProducts p2 ¢BugsProducts p ¢Bugs f ? bug_idでY栽 ? statusが'FIXED' ¢BugsProducts p2 ¢Bugs o ? bug_idでY栽 ? statusが'OPEN' ＊ Spaghetti-Query/anti/cartesian.sql FIXEDとOPEN のバグのMみ栽 わせを崙泙垢 訳周なし。。。
- 9. 9 ＊ cartesian-no-group.sql の Select鞘を Select * に筝したY惚 17.2.1 吭蹐坊瓦靴申Y惚 (5/5)
- 10. 10 17.2.2 さらなる益墾 ¢ １つのクエリで}方のタスクを佩おうとすると??? ? 吭蹐靴覆そY惚がГれる ? クエリの峰や俐屎、デバッグがyしくなる ? g佩rのコストが貧がる 返のzんだSQLは恷m晒I尖などがyしくなる
- 11. 11 17.3 アンチパタ`ンのつけ圭 ¢ SUMv方やCOUNTv方のY惚があり誼ないくら いに寄きくなってるのはなぜ ¢ このお晒けみたいに}jSQLクエリをくのに、来 1晩かかったよ ¢ このレポ`ト竃薦には、もう採も弖紗できない。この SQLクエリをき岷すのは返gがかかりすぎる ¢ このクエリに、もう１つDISTINCTを弖紗してみよう ¢ g佩rgがLすぎる。
- 12. 12 17.4 アンチパタ`ンを喘いてもよい栽 ¢ g匯のクエリをデ`タソ`スにYび原けてアプリケ`ション にデ`タを燕幣するような、プログラミングフレ`ムワ`ク やビジュアルコンポ`ネントライブラリ、レポ`トツ`ルを 聞っている栽 ? ただ、レポ`トの勣周が１つのSQLクエリでgFするにはあ まりにも}jな栽は、レポ`トを}方恬撹した圭がよいか もしれない ¢ }方のY惚を１つのソ`トで燕幣させるために、１つの クエリから}jなY惚を誼たい栽 ? SQLクエリでは、ソ`トをgに峺協できる ? }方のクエリY惚をソ`トするするには、アプリケ`ション コ`ドをくよりもDBでソ`トした圭が紳糞弔栽が謹い
- 13. 13 17.5 盾Q貨嵯峺扈y嵶を佩う ¢ sの圻tthe law of parsimony ? まったく揖じ嚠yをする２つの栽する尖があるとき は、gな圭がれている。 ¢ この圻tをSQLに輝てはめると??? ? まったく揖じY惚セットを伏む２つのクエリをxkできる 栽は、gなクエリをxぶべき。
- 14. 14 17.5.1 ワンステップずつ (1/2) 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 p2.product_id, COUNT(o.bug_id) AS count_open FROM BugsProducts p2 LEFT OUTER JOIN Bugs o ON p2.bug_id = o.bug_id AND o.status = 'OPEN' WHERE p2.product_id = 1 GROUP BY p2.product_id; FROM BugsProducts p INNER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'FIXED' INNER JOIN Bugs o ON p2.bug_id = o.bug_id AND o.status = 'OPEN' INNER JOIN BugsProducts p2 SELECT p.product_id, COUNT(f.bug_id) AS count_fixed, COUNT(o.bug_id) AS count_open WHERE p.product_id = 1 GROUP BY p.product_id; USING (product_id) デカルトeを閲けるために、クエリを蛍護デカルトeを閲けるために、クエリを蛍護 Spaghetti-Query/anti/cartesian.sql Spaghetti-Query/soln/split-query.sql
- 15. 15 17.5.1 ワンステップずつ (2/2) ¢ クエリ蛍護は？なメリットをもたらす ? デカルトeが伏じない。 ? 仟たな勣周が弖紗された栽、すでに}jなクエリをさ らに}jにするより、gなクエリを仟たにく圭がは るかにg。 ? 匯違議に、SQLエンジンは}jなクエリよりもgなク エリの圭がスム`ズかつ_gにg佩できる。 ? コ`ドレビュ`などでは、シンプルな}方のクエリをh苧 する圭が、１つの}jなクエリをh苧するよりg。
- 18. 18 17.5.4 貧望の}を盾Qする 恷念の盾Q貨は、貧望から箔められたタスクを蛍護してI尖すること。 ＊lかが函りQっているu瞳の方 SELECT COUNT(*) AS how_many_products FROM Products; ＊バグを俐屎した_k宀の方 SELECT COUNT(DISTINCT assigned_to) AS how_many_developers FROM Bugs WHERE status = 'FIXED'; ＊_k宀１繁あたりの峠譲バグ俐屎方 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; ＊俐屎したバグの嶄で人から鷂罎気譴織丱阿諒 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';
- 19. 19 17.5.5 SQLを喘いたSQLの徭啜弔篇峰 ＊ コラム此｝}方のUPDATEステ`トメント伏撹 ‐箭／ 双 last_used の､髻光コンピュ`タが聞喘された恷仟の晩原にO協する 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; ¢ }jなSQLクエリを蛍護すると、デ`タの､砲茲 てわずかになる、貌たようなクエリをいくつも伏 撹することがある。 ? それはわしいので、仝コ`ド伏撹々を佩いましょう。 ? 仝コ`ド伏撹々は、仟しいコ`ドを返でくには掲械に 薦がかかるような中で森議。