Share
Embed
SQLAP#1
Presentation document for inside workshop for ikyu corp(www.ikyu.co.jp).
The theme is 2nd part of SQL Antipattern "Naive Tree".
kentana20
November 29, 2013
Transcript
57 slides
-
本日のアジェンダ • はじめに ◦ SQLアンチパターン本について • 本編 ◦ 2章:ナイーブツリー(素朴な木) ◦
3章:IDリクワイアド(とりあえずID) • ダイアログディスカッション • クロージング -
本日のアジェンダ • はじめに ◦ SQLアンチパターン本について • 本編 ◦ 2章:ナイーブツリー(素朴な木) ◦
3章:IDリクワイアド(とりあえずID) • ダイアログディスカッション • クロージング -
SQLアンチパターン本 • 訳書 ◦ 2013/01/26 ▪ Bill Karwin(著) ▪ 和田卓人(監訳)
和田省二(監訳) 児島 修(監訳) -
本日のアジェンダ • はじめに ◦ SQLアンチパターン本について • 本編 ◦ 2章:ナイーブツリー(素朴な木) ◦
3章:IDリクワイアド(とりあえずID) • ダイアログディスカッション • クロージング -
ナイーブツリー(素朴な木) • ナイーブツリー ◦ ツリー構造をDBで表すときのアンチパターン • 直近の親のみを参照するデータ構造 ◦ 隣接リスト ◦
ツリーに対する一般的な処理(SQL)で アンチパターンとなってしまう -
ナイーブツリー(素朴な木) • ツリー構造って何? ◦ 再帰的な関連を持つデータ構造 営業本部 bar営業部 foo営業部 fuga課 hoge課
piyo課 hogera係 ① ② ④ ③ -
ナイーブツリー(素朴な木) • ツリー構造のエントリ ◦ 各エントリ=ノード 営業本部 bar営業部 foo営業部 fuga課 hoge課
piyo課 hogera係 ノード -
ナイーブツリー(素朴な木) 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係 ルート •
ツリー構造のエントリ ◦ ルート(根) ◦ 非葉ノード ◦ リーフ(葉) 非葉ノード リーフ -
ナイーブツリー(素朴な木) • ツリー構造を用いる一般事例 ◦ 組織図 ◦ 掲示板のコメント(スレッド) • 一休での事例 ◦
宿泊 :エリアマスタ(特大 > 大 > 小) ◦ Nexus:投稿・コメント(投稿 > コメント) -
ナイーブツリー(素朴な木) • アンチパターン ◦ 自分の親のみを参照する構造(tbl_organization) section_id parent_id section_name 1 null
営業本部 2 1 foo営業部 3 1 bar営業部 4 2 hoge課 5 4 hogera係 -
ナイーブツリー(素朴な木) • アンチパターン ◦ 自分の親のみを参照する構造(tbl_organization) section_id parent_id section_name 1 null
営業本部 2 1 foo営業部 3 1 bar営業部 4 2 hoge課 5 4 hogera係 -
ナイーブツリー(素朴な木) • アンチパターン ◦ 自分の親のみを参照する構造(tbl_organization) section_id parent_id section_name 1 null
営業本部 2 1 foo営業部 3 1 bar営業部 4 2 hoge課 5 4 hogera係 ナイーブツリー -
ナイーブツリー(素朴な木) • 階層制限が無いとツラい件 ◦ あるノードを元にして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 営業部 -
ナイーブツリー(素朴な木) • 階層制限が無いとツラい件 ◦ あるノードを元にして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 営業部 -
ナイーブツリー(素朴な木) • 階層制限が無いとツラい件 ◦ あるノードを元にして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 営業部 -
ナイーブツリー(素朴な木) • 中間ノードの削除時の更新が大変な件 ◦ hoge課とhogera係が統合のため、hoge・ra課に! 営業本部 bar営業部 foo営業部 fuga課 piyo課
hoge・ra課 ※組織番号上、hogera係のIDを生かしたいという要望つき -
ナイーブツリー(素朴な木) • 中間ノードの削除時の更新が大変な件 ◦ hoge課とhogera係が統合のため、hoge・ra課に! section_id parent_id section_name 1 null
営業本部 2 1 foo営業部 3 1 bar営業部 4 2 hoge課 5 4 hogera係 ←いらない ←更新したい -
ナイーブツリー(素朴な木) • 中間ノードの削除時の更新が大変な件 ◦ 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課 ←いらない ←更新したい -
ナイーブツリー(素朴な木) • 中間ノードの削除時の更新が大変な件 ◦ 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’; -
ナイーブツリー(素朴な木) • アンチパターンの誘い(危険信号) ◦ 素朴故に安易に設計すると選んでしまいがちなパターン(3 階層までしかサポートしないけど、今の要件だったらとりあえ ずこれでも良いか的な・・・) ◦ 危険信号 ▪
「この機能、再帰は何回くらいありますかね?」 ▪ 「親の、親の、親の親の・・・ブツブツ」 ▪ 「先輩、このエリア、親エリアがなくないすか?」 -
ナイーブツリー(素朴な木) • 経路列挙型モデル ◦ ルートから自ノードまでのパスを保持する 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係 -
ナイーブツリー(素朴な木) • 経路列挙型モデル ◦ メリット ▪ 部分ノードの抽出が前方一致でラクにできる ▪ 各ノードの親子関係が比較的視認しやすい --
foo営業部配下の部、課を取得するための SQL SELECT * FROM tbl_organization WHERE path LIKE ‘/2/%’; -
ナイーブツリー(素朴な木) • 経路列挙型モデル ◦ デメリット ▪ 経路を文字列で保持しているため、パスの完全性を保証 できず、アプリケーションの実装に依存してしまう ▪ パス文字列(文字数)には上限が設定されるため階層数
には制限が生まれる ▪ 中間ノード(非葉ノード)の削除の手間は変わらない ‘ 追加するノードまでのパスを生成 Dim path path = getCurrentNodePath() & ‘/’ & newNode ‘ 若干危うい・・・ ‘ 追加するパスをprepare statement へセット cmd.Parameters.Add(‘@path’, path) -
ナイーブツリー(素朴な木) • 入れ子集合型モデル ◦ 親ではなく、子に関する情報(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係 -
ナイーブツリー(素朴な木) • 入れ子集合型モデル 営業本部 bar営業部 foo営業部 fuga課 hoge課 piyo課 hogera係
1 14 2 8 7 6 3 13 10 9 12 11 4 5 -
ナイーブツリー(素朴な木) • 入れ子集合型モデル ◦ 作り方 営業本部 bar営業部 foo営業部 fuga課 hoge課
piyo課 hogera係 1 2 3 4 6 5 -
ナイーブツリー(素朴な木) • 入れ子集合型モデル ◦ 作り方 営業本部 bar営業部 foo営業部 fuga課 hoge課
piyo課 hogera係 1 2 8 7 6 3 9 4 5 -
ナイーブツリー(素朴な木) • 入れ子集合型モデル ◦ 作り方 営業本部 bar営業部 foo営業部 fuga課 hoge課
piyo課 hogera係 1 2 8 7 6 3 9 4 5 13 10 12 11 -
ナイーブツリー(素朴な木) • 入れ子集合型モデル ◦ 作り方 営業本部 bar営業部 foo営業部 fuga課 hoge課
piyo課 hogera係 1 2 8 7 6 3 9 4 5 13 10 12 11 14 -
ナイーブツリー(素朴な木) • 入れ子集合型モデル ◦ メリット ▪ 中間ノード(非葉ノード)を削除した時の更新処理が不要 (該当ノードの削除のみで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 営業部; -
ナイーブツリー(素朴な木) • 入れ子集合型モデル ◦ デメリット ▪ 直近の親を抽出する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; -
ナイーブツリー(素朴な木) • 閉包テーブル型モデル ◦ 別テーブル(閉包テーブル)でパスを管理する ◦ 隣接ノードだけでなく、パス全体を管理 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 -
ナイーブツリー(素朴な木) • 閉包テーブル型モデル ◦ 自分と下位のノード情報を登録 営業本部 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 -
ナイーブツリー(素朴な木) • 閉包テーブル型モデル ◦ メリット ▪ 閉包テーブルを用いれば部分ノードや親へのパスの取 得が簡単(入れ子集合型より簡単) ▪ データの関連が直感的
▪ ノードの追加や削除もラク -- 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 営業部; -
本日のアジェンダ • はじめに ◦ SQLアンチパターン本について • 本編 ◦ 2章:ナイーブツリー(素朴な木) ◦
3章:IDリクワイアド(とりあえずID) • ダイアログディスカッション • クロージング -
本日のアジェンダ • はじめに ◦ SQLアンチパターン本について • 本編 ◦ 2章:ナイーブツリー(素朴な木) ◦
3章:IDリクワイアド(とりあえずID) • ダイアログディスカッション • クロージング -
ナイーブツリー(素朴な木) • まとめ ◦ アンチパターン ▪ 隣接ツリー型 • わかりやすいが、ナイーブ ◦
解決法 ▪ 経路列挙型 • 視認しやすいが、文字列ゆえの脆弱さ ▪ 入れ子集合型 • わかりづらっ ▪ 閉包テーブル型 • 管理しやすいが、データ量に難あり -
ナイーブツリー(素朴な木) • まとめ ◦ ツリー構造を採用する物理モデルはどのサービスにも頻出 する、「あるある」パターン ◦ どの方法もメリット、デメリットがある ◦ 設計時に”こういう解決策もあるんだよね”という心の引き出し
として装備しておくことが大事(適材適所の選択が大切) ◦ 要件がブレない、ブレさせない(未来も含めて)場合はアンチ パターンを選択する事も正解となる