Slide 1

Slide 1 text

SQLアンチパターン勉強会 #1 宿泊事業本部システム開発部 田中 健介(@kentana20)

Slide 2

Slide 2 text

本日のアジェンダ ● はじめに ○ SQLアンチパターン本について ● 本編 ○ 2章:ナイーブツリー(素朴な木) ○ 3章:IDリクワイアド(とりあえずID) ● ダイアログディスカッション ● クロージング

Slide 3

Slide 3 text

本日のアジェンダ ● はじめに ○ SQLアンチパターン本について ● 本編 ○ 2章:ナイーブツリー(素朴な木) ○ 3章:IDリクワイアド(とりあえずID) ● ダイアログディスカッション ● クロージング

Slide 4

Slide 4 text

はじめに ● 社内で勉強会(読書会)開催できて嬉しいです (感謝御礼) ● 定期イベントとして、マインドアップの機会として 根付かせていきましょう(切望)

Slide 5

Slide 5 text

SQLアンチパターン本 ● 訳書 ○ 2013/01/26 ■ Bill Karwin(著) ■ 和田卓人(監訳) 和田省二(監訳) 児島 修(監訳)

Slide 6

Slide 6 text

SQLアンチパターン本 ● 原書 ○ 2010/06/17 ○ PragmaticBookshelf社 (達人プログラマーとか)

Slide 7

Slide 7 text

本日のアジェンダ ● はじめに ○ SQLアンチパターン本について ● 本編 ○ 2章:ナイーブツリー(素朴な木) ○ 3章:IDリクワイアド(とりあえずID) ● ダイアログディスカッション ● クロージング

Slide 8

Slide 8 text

ナイーブツリー(素朴な木) ● ナイーブツリー ○ ツリー構造をDBで表すときのアンチパターン ● 直近の親のみを参照するデータ構造 ○ 隣接リスト ○ ツリーに対する一般的な処理(SQL)で アンチパターンとなってしまう

Slide 9

Slide 9 text

ナイーブツリー(素朴な木) ● ツリー構造って何? ○ 再帰的な関連を持つデータ構造 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係

Slide 10

Slide 10 text

ナイーブツリー(素朴な木) ● ツリー構造って何? ○ 再帰的な関連を持つデータ構造 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係 ① ② ④ ③

Slide 11

Slide 11 text

ナイーブツリー(素朴な木) ● ツリー構造のエントリ ○ 各エントリ=ノード 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係 ノード

Slide 12

Slide 12 text

ナイーブツリー(素朴な木) 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係 ルート ● ツリー構造のエントリ ○ ルート(根) ○ 非葉ノード ○ リーフ(葉) 非葉ノード リーフ

Slide 13

Slide 13 text

ナイーブツリー(素朴な木) ● ツリー構造を用いる一般事例 ○ 組織図 ○ 掲示板のコメント(スレッド) ● 一休での事例 ○ 宿泊 :エリアマスタ(特大 > 大 > 小) ○ Nexus:投稿・コメント(投稿 > コメント)

Slide 14

Slide 14 text

ナイーブツリー(素朴な木) ● アンチパターン ○ 自分の親のみを参照する構造(tbl_organization) section_id parent_id section_name 1 null 営業本部 2 1 foo営業部 3 1 bar営業部 4 2 hoge課 5 4 hogera係

Slide 15

Slide 15 text

ナイーブツリー(素朴な木) ● アンチパターン ○ 自分の親のみを参照する構造(tbl_organization) section_id parent_id section_name 1 null 営業本部 2 1 foo営業部 3 1 bar営業部 4 2 hoge課 5 4 hogera係

Slide 16

Slide 16 text

ナイーブツリー(素朴な木) ● アンチパターン ○ 自分の親のみを参照する構造(tbl_organization) section_id parent_id section_name 1 null 営業本部 2 1 foo営業部 3 1 bar営業部 4 2 hoge課 5 4 hogera係 ナイーブツリー

Slide 17

Slide 17 text

ナイーブツリー(素朴な木) ● なぜアンチパターンか ○ 階層制限が無いツリーをSQLで書くのは大変 ○ 集約関数を使えない ○ 中間ノードの削除時の更新が大変

Slide 18

Slide 18 text

ナイーブツリー(素朴な木) ● 階層制限が無いとツラい件 ○ あるノードを元にして2階層を取得するSQL -- foo営業部が所属している本部を求める SQL SELECT ORG2.section_id, ORG2.section_name FROM tbl_organization AS ORG1 -- 子 LEFT OUTER JOIN tbl_organization AS ORG2 -- 親 ON ORG1.parent_id = ORG2.section_id WHERE C1.section_id = ‘ 2’ -- 2:foo 営業部

Slide 19

Slide 19 text

ナイーブツリー(素朴な木) ● 階層制限があるとツラい件 ○ 一見良さそうに見える

Slide 20

Slide 20 text

ナイーブツリー(素朴な木) ● 階層制限が無いとツラい件 ○ あるノードを元にして2階層を取得するSQL -- foo営業部が所属している本部を求める SQL SELECT ORG2.section_id, ORG2.section_name FROM tbl_organization AS ORG1 -- 子 LEFT OUTER JOIN tbl_organization AS ORG2 -- 親 ON ORG1.parent_id = ORG2.section_id WHERE C1.section_id = ‘ 2’ -- 2:foo 営業部

Slide 21

Slide 21 text

ナイーブツリー(素朴な木) ● 階層制限が無いとツラい件 ○ あるノードを元にして3階層を取得するSQL -- hoge課が所属する部と本部を求める SQL SELECT ORG3.section_id, ORG3.section_name , ORG2.section_id, ORG2.section_name FROM tbl_organization AS ORG1 -- 子 LEFT OUTER JOIN tbl_organization AS ORG2 -- 親 ON ORG1.parent_id = ORG2.section_id LEFT OUTER JOIN tbl_organization AS ORG3 -- 親の親 ON ORG2.parent_id = ORG3.section_id WHERE C1.section_id = ‘ 2’ -- 2:foo 営業部

Slide 22

Slide 22 text

ナイーブツリー(素朴な木) ● 階層制限があるとツラい件 ○ 階層が増える度に際限なくテーブルをJOINしていかなけれ ばならない ○ 際限ない再帰をプログラムで動的に書くのはしんどい

Slide 23

Slide 23 text

ナイーブツリー(素朴な木) ● 中間ノードの削除時の更新が大変な件 ○ hoge課とhogera係が統合のため、hoge・ra課に! 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係

Slide 24

Slide 24 text

ナイーブツリー(素朴な木) ● 中間ノードの削除時の更新が大変な件 ○ hoge課とhogera係が統合のため、hoge・ra課に! 営業本部 bar営業部 foo営業部 fuga課 piyo課 hoge・ra課 ※組織番号上、hogera係のIDを生かしたいという要望つき

Slide 25

Slide 25 text

ナイーブツリー(素朴な木) ● 中間ノードの削除時の更新が大変な件 ○ hoge課とhogera係が統合のため、hoge・ra課に! section_id parent_id section_name 1 null 営業本部 2 1 foo営業部 3 1 bar営業部 4 2 hoge課 5 4 hogera係 ←いらない ←更新したい

Slide 26

Slide 26 text

ナイーブツリー(素朴な木) ● 中間ノードの削除時の更新が大変な件 ○ hoge課とhogera係が統合のため、hoge・ra課に! section_id parent_id section_name 1 null 営業本部 2 1 foo営業部 3 1 bar営業部 4 2 hoge課 5 2 hoge・ra課 ←いらない ←更新したい

Slide 27

Slide 27 text

ナイーブツリー(素朴な木) ● 中間ノードの削除時の更新が大変な件 ○ hoge課とhogera係が統合のため、hoge・ra課に! -- hoge・ra課誕生のためのSQL -- 1.hogera係レコードの更新 UPDATE tbl_organization SET parent_id = ‘2’ , section_name = ‘hoge ・ra課’ WHERE section_id = ‘5’; -- 2. hoge課レコードの削除 DELETE FROM tbl_organization WHERE section_id = ‘4’;

Slide 28

Slide 28 text

ナイーブツリー(素朴な木) ● なぜアンチパターンか ○ 階層数が決まっていない、ノードの変更が頻繁にあるような 拡張性を求められるツリーだと不向き

Slide 29

Slide 29 text

ナイーブツリー(素朴な木) ● アンチパターンの誘い(危険信号) ○ 素朴故に安易に設計すると選んでしまいがちなパターン(3 階層までしかサポートしないけど、今の要件だったらとりあえ ずこれでも良いか的な・・・) ○ 危険信号 ■ 「この機能、再帰は何回くらいありますかね?」 ■ 「親の、親の、親の親の・・・ブツブツ」 ■ 「先輩、このエリア、親エリアがなくないすか?」

Slide 30

Slide 30 text

ナイーブツリー(素朴な木) ● アンチパターンを使っても良いケース ○ 階層の(数が少ない数で)予め決まっていて、サービス拡大 に伴う変更が無い場合 ○ 直近の親子関係のみを参照できればサービスの仕様を満た せる場合 ○ 再帰クエリが使用できるRDBMSを採用している場合

Slide 31

Slide 31 text

ナイーブツリー(素朴な木) ● では、どうすればよいか

Slide 32

Slide 32 text

ナイーブツリー(素朴な木) ● では、どうすればよいか ○ “代替ツリーモデル”を使う ■ 経路列挙型 ■ 入れ子集合型 ■ 閉包テーブル型

Slide 33

Slide 33 text

ナイーブツリー(素朴な木) 経路列挙型

Slide 34

Slide 34 text

ナイーブツリー(素朴な木) ● 経路列挙型モデル ○ ルートから自ノードまでのパスを保持する section_id path section_name 1 1/ 営業本部 2 1/2/ foo営業部 3 1/3/ bar営業部 4 1/2/4 hoge課 5 1/2/4/5/ hogera係

Slide 35

Slide 35 text

ナイーブツリー(素朴な木) ● 経路列挙型モデル ○ メリット ■ 部分ノードの抽出が前方一致でラクにできる ■ 各ノードの親子関係が比較的視認しやすい -- foo営業部配下の部、課を取得するための SQL SELECT * FROM tbl_organization WHERE path LIKE ‘/2/%’;

Slide 36

Slide 36 text

ナイーブツリー(素朴な木) ● 経路列挙型モデル ○ デメリット ■ 経路を文字列で保持しているため、パスの完全性を保証 できず、アプリケーションの実装に依存してしまう ■ パス文字列(文字数)には上限が設定されるため階層数 には制限が生まれる ■ 中間ノード(非葉ノード)の削除の手間は変わらない ‘ 追加するノードまでのパスを生成 Dim path path = getCurrentNodePath() & ‘/’ & newNode ‘ 若干危うい・・・ ‘ 追加するパスをprepare statement へセット cmd.Parameters.Add(‘@path’, path)

Slide 37

Slide 37 text

ナイーブツリー(素朴な木) 入れ子集合型

Slide 38

Slide 38 text

ナイーブツリー(素朴な木) ● 入れ子集合型モデル ○ 親ではなく、子に関する情報(2個)をテーブルに保存 ■ 自ノードのすべての子ノードの値よりも小さい値 ■ 自ノードのすべての子ノードの値よりも大きい値 section_id ns_left ns_right section_na me 1 1 14 営業本部 2 2 9 foo営業部 3 10 12 bar営業部 4 3 6 hoge課 5 4 5 hogera係

Slide 39

Slide 39 text

ナイーブツリー(素朴な木) ● 入れ子集合型モデル 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係 1 14 2 8 7 6 3 13 10 9 12 11 4 5

Slide 40

Slide 40 text

ナイーブツリー(素朴な木) ● 入れ子集合型モデル ○ 作り方 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係 1 2 3 4 6 5

Slide 41

Slide 41 text

ナイーブツリー(素朴な木) ● 入れ子集合型モデル ○ 作り方 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係 1 2 8 7 6 3 9 4 5

Slide 42

Slide 42 text

ナイーブツリー(素朴な木) ● 入れ子集合型モデル ○ 作り方 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係 1 2 8 7 6 3 9 4 5 13 10 12 11

Slide 43

Slide 43 text

ナイーブツリー(素朴な木) ● 入れ子集合型モデル ○ 作り方 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係 1 2 8 7 6 3 9 4 5 13 10 12 11 14

Slide 44

Slide 44 text

ナイーブツリー(素朴な木) ● 入れ子集合型モデル ○ メリット ■ 中間ノード(非葉ノード)を削除した時の更新処理が不要 (該当ノードの削除のみでOK) ■ 部分ノードの取得も簡単 -- foo営業部配下の部、課を取得するための SQL SELECT ORG2.* FROM tbl_organization AS ORG1 INNER JOIN tbl_organization AS ORG2 ON ORG2.ns_left BETWEEN ORG1.ns_left AND ORG2.ns_right WHERE ORG1.section_id = ‘2’ -- 2:foo 営業部;

Slide 45

Slide 45 text

ナイーブツリー(素朴な木) ● 入れ子集合型モデル ○ デメリット ■ 直近の親を抽出するSQLが若干複雑になる -- hoge課が所属する部を取得するための SQL SELECT ORG_PARENT.* FROM tbl_organization AS ORG1 INNER JOIN tbl_organization AS ORG_PARENT ON ORG_PARENT.ns_left < ORG1.ns_left -- hoge 課のns_leftより小さい AND ORG1.ns_left < ORG_PARENT.ns_right -- hoge 課のns_leftより大きい LEFT OUTER JOIN tbl_organization AS ORG_BETWEEN ON ORG_BETWEEN.ns_left < ORG1.ns_left -- hoge 課のns_leftより小さい AND ORG1.ns_left < ORG_BETWEEN.ns_right -- hoge 課のns_leftより大きい AND ORG_PARENT.ns_left < ORG_BETWEEN.ns_left -- 中間のns_leftより大きい AND ORG_BETWEEN.ns_left < ORG_PARENT.ns_right -- 中間のns_leftより大きい WHERE ORG1.section_id = ‘4’ -- 4:hoge 課 AND ORG_BETWEEN.section_id IS NULL;

Slide 46

Slide 46 text

ナイーブツリー(素朴な木) 閉包テーブル型

Slide 47

Slide 47 text

ナイーブツリー(素朴な木) ● 閉包テーブル型モデル ○ 別テーブル(閉包テーブル)でパスを管理する ○ 隣接ノードだけでなく、パス全体を管理 section_id section_name 1 営業本部 2 foo営業部 3 bar営業部 4 hoge課 5 hogera係 parent_id child_id 1 1 1 2 1 3 1 4 1 5

Slide 48

Slide 48 text

ナイーブツリー(素朴な木) ● 閉包テーブル型モデル ○ 自分と下位のノード情報を登録 営業本部 3:bar営業部 2:foo営業部 5:fuga課 6:piyo課 4:hoge課 parent_id child_id 2 2 2 4 2 5 4 4 5 5 3 3 3 6 6 6

Slide 49

Slide 49 text

ナイーブツリー(素朴な木) ● 閉包テーブル型モデル ○ メリット ■ 閉包テーブルを用いれば部分ノードや親へのパスの取 得が簡単(入れ子集合型より簡単) ■ データの関連が直感的 ■ ノードの追加や削除もラク -- foo営業部配下の部、課を取得するための SQL SELECT ORG.* FROM tbl_organization AS ORG INNER JOIN tbl_closure AS CLS ON ORG.section_id = CLS.child_id WHERE CLS.parent_id = ‘2’ -- 2:foo 営業部;

Slide 50

Slide 50 text

ナイーブツリー(素朴な木) ● 閉包テーブル型モデル ○ デメリット ■ 階層が増えるたびに閉包テーブルのデータ量がかなり 増える

Slide 51

Slide 51 text

本日のアジェンダ ● はじめに ○ SQLアンチパターン本について ● 本編 ○ 2章:ナイーブツリー(素朴な木) ○ 3章:IDリクワイアド(とりあえずID) ● ダイアログディスカッション ● クロージング

Slide 52

Slide 52 text

ダイアログディスカッション http://www.flickr.com/photos/23679420@N00/545653437/in/photolist-QdBKT-25kuiF-2hA7TJ-2kkgtJ- 397CKE-3fRHGF-3ZCRwZ-4a5hEa-4o8sXH-4FuTGy-4Qokri-4VHDKq-4Zxr8T-55rj3n-5f26Fg-5hfLxM- 5mv4k4-5BrV7A-5HP6iM-5QxJZa-5YhgUK-67cRkw-6a8ueD-6bgtY7-6fvXzC-6F5kSW-6J8UVE- 6NczWM-6WeVqv-6Y6rU3-7i5ikq-7jjmJk-7q4zYp-7rPFiR-7rTBt7-7t1L7Z-7t1Laz-7t5P7S-8qUpxV-8y2Ti5- bZd2uU-85Rk9G-9oGwvq-bN9EVX-bMQ5zV-abAqox-8A4V5n-7KvWQ3-bVprFr-bxrRUy-8yzJ6a

Slide 53

Slide 53 text

本日のアジェンダ ● はじめに ○ SQLアンチパターン本について ● 本編 ○ 2章:ナイーブツリー(素朴な木) ○ 3章:IDリクワイアド(とりあえずID) ● ダイアログディスカッション ● クロージング

Slide 54

Slide 54 text

ナイーブツリー(素朴な木) ● まとめ ○ アンチパターン ■ 隣接ツリー型 ● わかりやすいが、ナイーブ ○ 解決法 ■ 経路列挙型 ● 視認しやすいが、文字列ゆえの脆弱さ ■ 入れ子集合型 ● わかりづらっ ■ 閉包テーブル型 ● 管理しやすいが、データ量に難あり

Slide 55

Slide 55 text

ナイーブツリー(素朴な木) ● まとめ ○ ツリー構造を採用する物理モデルはどのサービスにも頻出 する、「あるある」パターン ○ どの方法もメリット、デメリットがある ○ 設計時に”こういう解決策もあるんだよね”という心の引き出し として装備しておくことが大事(適材適所の選択が大切) ○ 要件がブレない、ブレさせない(未来も含めて)場合はアンチ パターンを選択する事も正解となる

Slide 56

Slide 56 text

ナイーブツリー(素朴な木) ● まとめ ○ 今後のサービス設計時に誤った選択をしないようにみんなで 気をつけていきましょう ○ 設計時、設計レビュー時に「いや、待てよ…。これだとナイー ブツリーじゃないか!?」と気づいたら、その選択が正しいもの かを立ち止まって考えましょう

Slide 57

Slide 57 text

http://www.flickr.com/photos/56477159@N06/5524676855/in/photolist-9qcpXe-8PERMP-9ezKQc- dx4VGU-h5kaer-anbEQ7-7N36e4-aizzsb-dF7CWR-gDwCxR-fK9BTB-96c8ij-9JhcNV-dmBbo9-aEXCSV- bmAUti-digmWb-9ZSPKh-9TMDem-boBPvR-7SBJU5-dErN9j-aNPLhK-chDRW5-dCPBjy-c3LFwh- 9W1LPN-bzTkX7-dFRwb6-gjQjeN-ayhzXx-atTbzf-dC46am-7NzeeY-d5BTjm-8FSHwF-7JNJcx-aE57RY- h5V12t-bV6aNh-dCPqQB-9gK4MR-djSXo1-azzavD-9NzzMs-8DWScM-fHkFuP-aTSRkR-7XShC6- 9cSWzw-8Drgd5