Presentation document for inside workshop for ikyu corp(www.ikyu.co.jp). The theme is 2nd part of SQL Antipattern "Naive Tree".
SQLアンチパターン勉強会#1宿泊事業本部システム開発部田中 健介（@kentana20）
View Slide
本日のアジェンダ● はじめに○ SQLアンチパターン本について● 本編○ ２章：ナイーブツリー（素朴な木）○ ３章：IDリクワイアド（とりあえずID）● ダイアログディスカッション● クロージング
はじめに● 社内で勉強会（読書会）開催できて嬉しいです（感謝御礼）● 定期イベントとして、マインドアップの機会として根付かせていきましょう（切望）
SQLアンチパターン本● 訳書○ 2013/01/26■ Bill Karwin(著)■ 和田卓人(監訳)和田省二(監訳)児島 修(監訳)
SQLアンチパターン本● 原書○ 2010/06/17○ PragmaticBookshelf社（達人プログラマーとか）
ナイーブツリー（素朴な木）● ナイーブツリー○ ツリー構造をDBで表すときのアンチパターン● 直近の親のみを参照するデータ構造○ 隣接リスト○ ツリーに対する一般的な処理(SQL)でアンチパターンとなってしまう
ナイーブツリー（素朴な木）● ツリー構造って何？○ 再帰的な関連を持つデータ構造営業本部bar営業部foo営業部fuga課hoge課 piyo課hogera係
ナイーブツリー（素朴な木）● ツリー構造って何？○ 再帰的な関連を持つデータ構造営業本部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_name1 null 営業本部2 1 foo営業部3 1 bar営業部4 2 hoge課5 4 hogera係
ナイーブツリー（素朴な木）● アンチパターン○ 自分の親のみを参照する構造（tbl_organization）section_id parent_id section_name1 null 営業本部2 1 foo営業部3 1 bar営業部4 2 hoge課5 4 hogera係ナイーブツリー
ナイーブツリー（素朴な木）● なぜアンチパターンか○ 階層制限が無いツリーをSQLで書くのは大変○ 集約関数を使えない○ 中間ノードの削除時の更新が大変
ナイーブツリー（素朴な木）● 階層制限が無いとツラい件○ あるノードを元にして2階層を取得するSQL-- foo営業部が所属している本部を求める SQLSELECTORG2.section_id, ORG2.section_nameFROMtbl_organization AS ORG1 -- 子LEFT OUTER JOIN tbl_organization AS ORG2 -- 親ON ORG1.parent_id = ORG2.section_idWHEREC1.section_id = ‘ 2’ -- 2:foo 営業部
ナイーブツリー（素朴な木）● 階層制限があるとツラい件○ 一見良さそうに見える
ナイーブツリー（素朴な木）● 階層制限が無いとツラい件○ あるノードを元にして3階層を取得するSQL-- hoge課が所属する部と本部を求める SQLSELECTORG3.section_id, ORG3.section_name , ORG2.section_id, ORG2.section_nameFROMtbl_organization AS ORG1 -- 子LEFT OUTER JOIN tbl_organization AS ORG2 -- 親ON ORG1.parent_id = ORG2.section_idLEFT OUTER JOIN tbl_organization AS ORG3 -- 親の親ON ORG2.parent_id = ORG3.section_idWHEREC1.section_id = ‘ 2’ -- 2:foo 営業部
ナイーブツリー（素朴な木）● 階層制限があるとツラい件○ 階層が増える度に際限なくテーブルをJOINしていかなければならない○ 際限ない再帰をプログラムで動的に書くのはしんどい
ナイーブツリー（素朴な木）● 中間ノードの削除時の更新が大変な件○ hoge課とhogera係が統合のため、hoge・ra課に！営業本部bar営業部foo営業部fuga課hoge課 piyo課hogera係
ナイーブツリー（素朴な木）● 中間ノードの削除時の更新が大変な件○ hoge課とhogera係が統合のため、hoge・ra課に！営業本部bar営業部foo営業部fuga課 piyo課hoge・ra課※組織番号上、hogera係のIDを生かしたいという要望つき
ナイーブツリー（素朴な木）● 中間ノードの削除時の更新が大変な件○ hoge課とhogera係が統合のため、hoge・ra課に！section_id parent_id section_name1 null 営業本部2 1 foo営業部3 1 bar営業部4 2 hoge課5 4 hogera係←いらない←更新したい
ナイーブツリー（素朴な木）● 中間ノードの削除時の更新が大変な件○ hoge課とhogera係が統合のため、hoge・ra課に！section_id parent_id section_name1 null 営業本部2 1 foo営業部3 1 bar営業部4 2 hoge課5 2 hoge・ra課←いらない←更新したい
ナイーブツリー（素朴な木）● 中間ノードの削除時の更新が大変な件○ hoge課とhogera係が統合のため、hoge・ra課に！-- hoge・ra課誕生のためのSQL-- 1.hogera係レコードの更新UPDATEtbl_organizationSETparent_id = ‘2’, section_name = ‘hoge ・ra課’WHEREsection_id = ‘5’;-- 2. hoge課レコードの削除DELETE FROM tbl_organizationWHEREsection_id = ‘4’;
ナイーブツリー（素朴な木）● なぜアンチパターンか○ 階層数が決まっていない、ノードの変更が頻繁にあるような拡張性を求められるツリーだと不向き
ナイーブツリー（素朴な木）● アンチパターンの誘い（危険信号）○ 素朴故に安易に設計すると選んでしまいがちなパターン（3階層までしかサポートしないけど、今の要件だったらとりあえずこれでも良いか的な・・・）○ 危険信号■ 「この機能、再帰は何回くらいありますかね？」■ 「親の、親の、親の親の・・・ブツブツ」■ 「先輩、このエリア、親エリアがなくないすか？」
ナイーブツリー（素朴な木）● アンチパターンを使っても良いケース○ 階層の（数が少ない数で）予め決まっていて、サービス拡大に伴う変更が無い場合○ 直近の親子関係のみを参照できればサービスの仕様を満たせる場合○ 再帰クエリが使用できるRDBMSを採用している場合
ナイーブツリー（素朴な木）● では、どうすればよいか
ナイーブツリー（素朴な木）● では、どうすればよいか○ “代替ツリーモデル”を使う■ 経路列挙型■ 入れ子集合型■ 閉包テーブル型
ナイーブツリー（素朴な木）経路列挙型
ナイーブツリー（素朴な木）● 経路列挙型モデル○ ルートから自ノードまでのパスを保持するsection_id path section_name1 1/ 営業本部2 1/2/ foo営業部3 1/3/ bar営業部4 1/2/4 hoge課5 1/2/4/5/ hogera係
ナイーブツリー（素朴な木）● 経路列挙型モデル○ メリット■ 部分ノードの抽出が前方一致でラクにできる■ 各ノードの親子関係が比較的視認しやすい-- foo営業部配下の部、課を取得するための SQLSELECT*FROMtbl_organizationWHEREpath LIKE ‘/2/%’;
ナイーブツリー（素朴な木）● 経路列挙型モデル○ デメリット■ 経路を文字列で保持しているため、パスの完全性を保証できず、アプリケーションの実装に依存してしまう■ パス文字列（文字数）には上限が設定されるため階層数には制限が生まれる■ 中間ノード（非葉ノード）の削除の手間は変わらない‘ 追加するノードまでのパスを生成Dim pathpath = getCurrentNodePath() & ‘/’ & newNode ‘ 若干危うい・・・‘ 追加するパスをprepare statement へセットcmd.Parameters.Add(‘@path’, path)
ナイーブツリー（素朴な木）入れ子集合型
ナイーブツリー（素朴な木）● 入れ子集合型モデル○ 親ではなく、子に関する情報(2個）をテーブルに保存■ 自ノードのすべての子ノードの値よりも小さい値■ 自ノードのすべての子ノードの値よりも大きい値section_id ns_left ns_rightsection_name1 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 14287631310912114 5
ナイーブツリー（素朴な木）● 入れ子集合型モデル○ 作り方営業本部bar営業部foo営業部fuga課hoge課 piyo課hogera係123465
ナイーブツリー（素朴な木）● 入れ子集合型モデル○ 作り方営業本部bar営業部foo営業部fuga課hoge課 piyo課hogera係12876394 5
ナイーブツリー（素朴な木）● 入れ子集合型モデル○ 作り方営業本部bar営業部foo営業部fuga課hoge課 piyo課hogera係12876394 513101211
ナイーブツリー（素朴な木）● 入れ子集合型モデル○ 作り方営業本部bar営業部foo営業部fuga課hoge課 piyo課hogera係12876394 51310121114
ナイーブツリー（素朴な木）● 入れ子集合型モデル○ メリット■ 中間ノード（非葉ノード）を削除した時の更新処理が不要（該当ノードの削除のみでOK）■ 部分ノードの取得も簡単-- foo営業部配下の部、課を取得するための SQLSELECTORG2.*FROMtbl_organization AS ORG1INNER JOIN tbl_organization AS ORG2ON ORG2.ns_left BETWEEN ORG1.ns_left AND ORG2.ns_rightWHEREORG1.section_id = ‘2’ -- 2:foo 営業部;
ナイーブツリー（素朴な木）● 入れ子集合型モデル○ デメリット■ 直近の親を抽出するSQLが若干複雑になる-- hoge課が所属する部を取得するための SQLSELECTORG_PARENT.*FROMtbl_organization AS ORG1INNER JOIN tbl_organization AS ORG_PARENTON 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_BETWEENON 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より大きいWHEREORG1.section_id = ‘4’ -- 4:hoge 課AND ORG_BETWEEN.section_id IS NULL;
ナイーブツリー（素朴な木）閉包テーブル型
ナイーブツリー（素朴な木）● 閉包テーブル型モデル○ 別テーブル（閉包テーブル）でパスを管理する○ 隣接ノードだけでなく、パス全体を管理section_id section_name1 営業本部2 foo営業部3 bar営業部4 hoge課5 hogera係parent_id child_id1 11 21 31 41 5
ナイーブツリー（素朴な木）● 閉包テーブル型モデル○ 自分と下位のノード情報を登録営業本部3:bar営業部2:foo営業部5:fuga課 6:piyo課4:hoge課parent_id child_id2 22 42 54 45 53 33 66 6
ナイーブツリー（素朴な木）● 閉包テーブル型モデル○ メリット■ 閉包テーブルを用いれば部分ノードや親へのパスの取得が簡単（入れ子集合型より簡単）■ データの関連が直感的■ ノードの追加や削除もラク-- foo営業部配下の部、課を取得するための SQLSELECTORG.*FROMtbl_organization AS ORGINNER JOIN tbl_closure AS CLSON ORG.section_id = CLS.child_idWHERECLS.parent_id = ‘2’ -- 2:foo 営業部;
ナイーブツリー（素朴な木）● 閉包テーブル型モデル○ デメリット■ 階層が増えるたびに閉包テーブルのデータ量がかなり増える
ダイアログディスカッション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
ナイーブツリー（素朴な木）● まとめ○ アンチパターン■ 隣接ツリー型● わかりやすいが、ナイーブ○ 解決法■ 経路列挙型● 視認しやすいが、文字列ゆえの脆弱さ■ 入れ子集合型● わかりづらっ■ 閉包テーブル型● 管理しやすいが、データ量に難あり
ナイーブツリー（素朴な木）● まとめ○ ツリー構造を採用する物理モデルはどのサービスにも頻出する、「あるある」パターン○ どの方法もメリット、デメリットがある○ 設計時に”こういう解決策もあるんだよね”という心の引き出しとして装備しておくことが大事（適材適所の選択が大切）○ 要件がブレない、ブレさせない（未来も含めて）場合はアンチパターンを選択する事も正解となる
ナイーブツリー（素朴な木）● まとめ○ 今後のサービス設計時に誤った選択をしないようにみんなで気をつけていきましょう○ 設計時、設計レビュー時に「いや、待てよ…。これだとナイーブツリーじゃないか!?」と気づいたら、その選択が正しいものかを立ち止まって考えましょう
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
kensuketanaka
hschwentner
cocoeyes02
suzukihoge
ogiwarat
twada
manfredsteyer
hsawaji
ivargrimstad
dunglas
schroneko
pluu
tkhs0604
deanohume
danielanewman
chriscoyier
frogandcode
chrislema
notwaldorf
bermonpainter
jacobian
ufuk
philhawksworth
jasonvnalue
holman