Slide 1
Slide 1 text
階層構造を表現するデータ構造と
リファクタリング
〜1年で10倍成長したプロダクトの変化と課題〜
Kaigi on Rails 2025
yuhi
Slide 2
Slide 2 text
yuhi
株式会社プレックス / 24卒
yuhi_junior Yuhi-Sato
自己紹介
● 建設系SaaS サクミル 開発
● 24th Dev, Wakate.rb主催
● Rails歴 2年
● Kaigi on Rails初参加🙌
Slide 3
Slide 3 text
皆さんは階層構造を持つサービスを
使ったことがありますか？
Slide 4
Slide 4 text
具体例
Slide 5
Slide 5 text
階層構造を持つサービス例 1
クラウドストレージサービスにおけるフォルダは階層構造
id: 3
parent_id: 2
Kaigi on Rails
id: 3
parent_id: 2
2023
id: 3
parent_id: 2
2024
id: 3
parent_id: 2
2025
id: 3
parent_id: 2
1日目
id: 3
parent_id: 2
2日目
Slide 6
Slide 6 text
階層構造を持つサービス例 2
レシピサイトにおける材料タグは階層構造
Slide 7
Slide 7 text
階層化機能のよくある要件
● 階層構造の読み込み
○ 親ノードを取得
○ ⼦ノードを取得
○ 祖先ノードを取得
○ ⼦孫ノードを取得
● 階層構造の書き込み
○ ⼦ノードを作成
○ ノードを移動
○ ノードとその全ての⼦孫ノードを削除
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
ノード
Slide 8
Slide 8 text
RDBで階層化機能をどのように
表現するんだろう？
Slide 9
Slide 9 text
なんとなくで表現すると
大変なことになるかも ...
Slide 10
Slide 10 text
本日話すこと
● 階層構造を表現するデータ構造とそのリファクタリング
● 急成⻑サービスにおける変化と課題
本日話さないこと
● 下記のデータ構造について
○ Path Enumeration（経路列挙） , Nested Sets（⼊れ⼦集合）
● 階層構造を表現する特定のgemについて
○ gemの裏側で使われているデータ構造の話をします
はじめに
Slide 11
Slide 11 text
はじめに
ゴール
1. 階層構造を表現する2つのデータ構造を理解する（SQLクイズを出題！）
○ Adjacency List（隣接リスト）
○ Closure Table（閉包テーブル）
2. Recursive CTE（再帰CTE）の概要を理解する
3. 階層化機能において適切な⼿法を選択ができるようになる
Slide 12
Slide 12 text
本発表では階層構造の中でも
ツリー構造（= 親を⼀つだけ持つ）
のみを扱います
はじめに
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 13
Slide 13 text
Adjacency List（隣接リスト）
Slide 14
Slide 14 text
Adjacency List（隣接リスト）とは
● 各レコードが⾃⾝の親レコードへの参照を持っている
● parent_idが親レコードへの参照
Slide 15
Slide 15 text
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
Adjacency Listの例
id parent_id
1 null
2 1
3 2
4 3
5 3
6 1
7 6
foldersテーブル
Slide 16
Slide 16 text
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
Adjacency Listの例
id parent_id
1 null
2 1
3 2
4 3
5 3
6 1
7 6
foldersテーブル
Slide 17
Slide 17 text
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
Adjacency Listの例
id parent_id
1 null
2 1
3 2
4 3
5 3
6 1
7 6
foldersテーブル
Slide 18
Slide 18 text
SQLで階層構造を操作してみよう
Slide 19
Slide 19 text
親ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 20
Slide 20 text
親ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
parent_id=3という情報から
id=3のノードを取得する
Slide 21
Slide 21 text
親ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
parent_id=3という情報から
id=3のノードを取得する
Slide 22
Slide 22 text
問題1: 子ノードを取得する SQLを答えよ
Slide 23
Slide 23 text
子ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 24
Slide 24 text
子ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
？
空欄に⼊るのは？
1. parent_id = 3
2. id = 3
Slide 25
Slide 25 text
子ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
空欄に⼊るのは？
1. parent_id = 3
2. id = 3
親のidが3のノードを取得する
Slide 26
Slide 26 text
Adjacency Listのメリット
● シンプル
● 書き込み操作が容易
○ ⼦ノードを作成
○ ノードを移動
Slide 27
Slide 27 text
子ノードを作成（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 8
parent_id: 7
Slide 28
Slide 28 text
子ノードを作成（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 8
parent_id: 7
親のidを7にする
Slide 29
Slide 29 text
子ノードを作成（ Adjacency List）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 8
parent_id: 7
親のidを7にする
Slide 30
Slide 30 text
ノードを移動（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
Slide 31
Slide 31 text
ノードを移動（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
Slide 32
Slide 32 text
ノードを移動（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
親のidを7に変更する
Slide 33
Slide 33 text
ノードを移動（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
親のidを7に変更する
Slide 34
Slide 34 text
Adjacency Listのデメリット
● 親⼦関係を超えた読み込み操作が困難
○ 祖先ノード取得
○ ⼦孫ノード取得
Slide 35
Slide 35 text
祖先ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 36
Slide 36 text
祖先ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 37
Slide 37 text
祖先ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 38
Slide 38 text
祖先ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 39
Slide 39 text
全ての祖先ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
クエリ実⾏回数: 3
Slide 40
Slide 40 text
子孫ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 41
Slide 41 text
子孫ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 42
Slide 42 text
子孫ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 43
Slide 43 text
子孫ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
Slide 44
Slide 44 text
子孫ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
Slide 45
Slide 45 text
子孫ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
Slide 46
Slide 46 text
子孫ノードを取得（ Adjacency List）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
Slide 47
Slide 47 text
子孫ノードの取得（ Adjacency List）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
クエリ実⾏回数: 7
Slide 48
Slide 48 text
Folderモデル（Adjacency List）
Slide 49
Slide 49 text
親への関連
⼦への関連
Folderモデル（Adjacency List）
Slide 50
Slide 50 text
全ての祖先を配列で取得
全ての⼦孫を配列で取得
Folderモデル（Adjacency List）
Slide 51
Slide 51 text
祖先ノードを取得（ Adjacency List）
Slide 52
Slide 52 text
祖先ノードを取得（ Adjacency List）
配列の結合
Slide 53
Slide 53 text
祖先ノードを取得（ Adjacency List）
親の配列
Slide 54
Slide 54 text
祖先ノードを取得（ Adjacency List）
親の祖先
Slide 55
Slide 55 text
祖先ノードを取得（ Adjacency List）
再帰処理
Slide 56
Slide 56 text
祖先ノードを取得（ Adjacency List）
親が存在しなくなるまで繰り返す
Slide 57
Slide 57 text
子孫ノードを取得（ Adjacency List）
Slide 58
Slide 58 text
子孫ノードを取得（ Adjacency List）
配列の結合
Slide 59
Slide 59 text
子孫ノードを取得（ Adjacency List）
⼦の配列
Slide 60
Slide 60 text
子孫ノードを取得（ Adjacency List）
それぞれの⼦の⼦孫
Slide 61
Slide 61 text
子孫ノードを取得（ Adjacency List）
再帰処理
Slide 62
Slide 62 text
Adjacency List まとめ
● メリット
○ 実装が容易
■ 直感的なFolderモデルで実装できる
○ 書き込みが容易
■ SQL問い合わせ1回
● デメリット
○ 親⼦関係を超えた読み込み操作が困難
■ SQL問い合わせを複数回⾏う必要がある
Slide 63
Slide 63 text
フォルダ階層化機能の開発（ 1年前）
Slide 64
Slide 64 text
フォルダ階層化機能とは
● サクミルではフォルダに階層構造を持たせてファイルを管理することができる
● フォルダの閲覧‧作成‧移動‧削除ができる
Slide 65
Slide 65 text
開発当時のプロダクト状況
● 導⼊社数が100社とユーザー数が少ない
○ パフォーマンスが問題になりにくい
● プロダクト全体の機能が不⼗分
○ 新機能を素早くリリースして機能を充実させたい
Slide 66
Slide 66 text
開発当時のプロダクト状況
● 導⼊社数が100社とユーザー数が少ない
○ パフォーマンスが問題になりにくい
● プロダクト全体の機能が不⼗分
○ 新機能を素早くリリースして機能を充実させたい
実装が容易なAdjacency Listでフォルダ階層化機能を実装
Slide 67
Slide 67 text
プロダクト状況の変化と課題（ 1年後）
Slide 68
Slide 68 text
プロダクト状況の変化
● 1年で導⼊社数が1,000社へと10倍成⻑した
● プロダクト全体の機能も充実してきた
● プロダクトを使い込んでいただけるお客様も増えた
Slide 69
Slide 69 text
プロダクト状況の変化
● 1年で導⼊社数が1,000社へと10倍成⻑した
● プロダクト全体の機能も充実してきた
● プロダクトを使い込んでいただけるお客様も増えた
顧客体験がより重要視されるフェーズに
Slide 70
Slide 70 text
フォルダ階層化機能の課題
● 巨⼤な階層構造が作成されるケースが出てきたことにより
⼤量のN+1問題が発⽣
Slide 71
Slide 71 text
大量のN+1が発生する例 : 子孫ノードを探索（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1000
parent_id: xxx
Slide 72
Slide 72 text
大量のN+1が発生する例 : 子孫ノードを探索（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 1
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1000
parent_id: xxx
Slide 73
Slide 73 text
大量のN+1が発生する例 : 子孫ノードを探索（ Adjacency List）
id: 3
parent_id: 2
id: 3
parent_id: 1
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1000
parent_id: xxx
Slide 74
Slide 74 text
大量のN+1が発生する例 : 子孫ノードを探索（ Adjacency List）
id: 1
parent_id: null
id: 6
parent_id: 1
id: 700
parent_id: xxx
id: 2
parent_id: 1
id: 1,000
parent_id: xxx
Slide 75
Slide 75 text
子孫ノードを探索（ Adjacency List）
id: 1
parent_id: null
id: 6
parent_id: 1
id: 700
parent_id: xxx
id: 2
parent_id: 1
id: 1,000
parent_id: xxx
クエリ実⾏回数: 1,000
N+1問題が⾟すぎる😭
Slide 76
Slide 76 text
子孫ノードを探索（ Adjacency List）
id: 1
parent_id: null
id: 6
parent_id: 1
id: 700
parent_id: xxx
id: 2
parent_id: 1
id: 1,000
parent_id: xxx
リファクタリングでN+1問題を解消しよう
Slide 77
Slide 77 text
● 検討した⼿法は2つ
○ Closure Table（閉包テーブル）
○ Adjacency List + Recursive CTE（再帰CTE）
リファクタリング
Slide 78
Slide 78 text
Closure Table（閉包テーブル）
Slide 79
Slide 79 text
Closure Table（閉包テーブル）とは
● 階層構造における全ての経路を保持しているテーブル
● Adjacency Listへの関連を持っている
● ancestor_idは祖先のid、descendant_idは⼦孫のid
● 祖先‧⼦孫の関係によって経路を表現（= 経路の始点‧終点の関係）
● depthは経路⻑
Slide 80
Slide 80 text
Closure Tableの例（始点が id=1の経路のみ）
ancestor_id descendant_id depth
1 1 0
1 2 1
1 3 2
1 4 3
1 5 4
1 6 5
1 7 6
folder_pathsテーブル
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
Slide 81
Slide 81 text
Closure Tableの例（始点が id=1の経路のみ）
ancestor_id descendant_id depth
1 1 0
1 2 1
1 3 2
1 4 3
1 5 4
1 6 5
1 7 6
folder_pathsテーブル
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
Slide 82
Slide 82 text
Closure Tableの例（始点が id=1の経路のみ）
ancestor_id descendant_id depth
1 1 0
1 2 1
1 3 2
1 4 3
1 5 4
1 6 5
1 7 6
folder_pathsテーブル
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
Slide 83
Slide 83 text
Closure Tableの例（始点が id=2の経路のみ）
folder_pathsテーブル
ancestor_id descendant_id depth
2 2 0
2 3 1
2 4 2
2 5 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
Slide 84
Slide 84 text
Closure Tableの例（始点が id=2の経路のみ）
folder_pathsテーブル
ancestor_id descendant_id depth
2 2 0
2 3 1
2 4 2
2 5 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
Slide 85
Slide 85 text
Closure Tableの例（始点が id=2の経路のみ）
folder_pathsテーブル
ancestor_id descendant_id depth
2 2 0
2 3 1
2 4 2
2 5 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
Slide 86
Slide 86 text
Closure Tableのメリット
● 親⼦関係を超えた読み込みが容易
○ 祖先ノードを取得
○ ⼦孫ノードを取得
Slide 87
Slide 87 text
祖先ノードを取得（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 88
Slide 88 text
祖先ノードを取得（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 89
Slide 89 text
祖先ノードを取得（ Closure Table）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
foldersとfolder_pathsを結合
Slide 90
Slide 90 text
祖先ノードを取得（ Closure Table）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
終点がid=4である経路に絞る
Slide 91
Slide 91 text
問題2: 子孫ノードを取得する SQLを答えよ
Slide 92
Slide 92 text
子孫(descendant)ノードを取得（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 93
Slide 93 text
子孫(descendant)ノードを取得（ Closure Table）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
？
空欄に⼊るのは？
1. descendant_id = 1
2. ancestor_id = 1
Slide 94
Slide 94 text
子孫(descendant)ノードを取得（ Closure Table）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
？
空欄に⼊るのは？
1. descendant_id = 1
2. ancestor_id = 1
始点がid=1である経路に絞る
Slide 95
Slide 95 text
子孫(descendant)ノードを取得（ Closure Table）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
空欄に⼊るのは？
1. descendant_id = 1
2. ancestor_id = 1
始点がid=1である経路に絞る
Slide 96
Slide 96 text
子孫(descendant)ノードを取得（ Closure Table）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
始点がid=1である経路に絞る
空欄に⼊るのは？
1. descendant_id = 1
2. ancestor_id = 1
Slide 97
Slide 97 text
FolderPathモデル
Slide 98
Slide 98 text
Folderモデル（Closure Table）
Slide 99
Slide 99 text
Folderモデル（Closure Table）
祖先経路への関連
⼦孫経路への関連
Slide 100
Slide 100 text
Folderモデル（Closure Table）
全ての祖先を取得
全ての⼦孫を取得
Slide 101
Slide 101 text
Closure Tableのデメリット
● ストレージコスト
● 書き込みの操作が困難（経路を管理する必要がある）
○ ⼦ノードを作成
○ ノードを移動
○ ノードとその全ての⼦孫ノードを削除
Slide 102
Slide 102 text
子ノードを作成（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 8
parent_id: 7
Slide 103
Slide 103 text
子ノードを作成（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 8
parent_id: 7
Slide 104
Slide 104 text
子ノードを作成（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 8
parent_id: 7
ノードを挿⼊して
Slide 105
Slide 105 text
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 8
parent_id: 7
子ノードを作成（ Closure Table）
親が終点の経路を取得し
Slide 106
Slide 106 text
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 8
parent_id: 7
子ノードを作成（ Closure Table）
親の経路を使って
⾃⾝が終点の経路を挿⼊
Slide 107
Slide 107 text
Folder作成時にFolderPathを作成
id: 3
parent_id: 2
id: 8
parent_id: 7
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 108
Slide 108 text
Folder作成時にFolderPathを作成
ノード作成時にコールバックを実⾏
id: 3
parent_id: 2
id: 8
parent_id: 7
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 109
Slide 109 text
Folder作成時にFolderPathを作成
経路をバルクインサートするための配列を定義
id: 3
parent_id: 2
id: 8
parent_id: 7
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
バルクインサート
Slide 110
Slide 110 text
Folder作成時にFolderPathを作成
⾃⾝への経路を配列に追加
id: 3
parent_id: 2
id: 8
parent_id: 7
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 111
Slide 111 text
Folder作成時にFolderPathを作成
親ノードが終点の経路を取得
id: 3
parent_id: 2
id: 8
parent_id: 7
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 112
Slide 112 text
Folder作成時にFolderPathを作成
⾃⾝が終点の経路作成
id: 3
parent_id: 2
id: 8
parent_id: 7
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 113
Slide 113 text
Folder作成時にFolderPathを作成
id: 3
parent_id: 2
id: 8
parent_id: 7
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 7
parent_id: 6
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
バルクインサート
Slide 114
Slide 114 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
Slide 115
Slide 115 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
Slide 116
Slide 116 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
親を更新して
Slide 117
Slide 117 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⼦孫を取得して
Slide 118
Slide 118 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⾃⾝と⼦孫が終点である
経路を削除
Slide 119
Slide 119 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⾃⾝と⼦孫が終点である
経路を削除
Slide 120
Slide 120 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⾃⾝と⼦孫が終点である
経路を削除
Slide 121
Slide 121 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⾃⾝と⼦孫が終点である
経路を削除
Slide 122
Slide 122 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⾃⾝と⼦孫が終点である
経路を削除
Slide 123
Slide 123 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⾃⾝と⼦孫が終点である
経路を削除
Slide 124
Slide 124 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⾃⾝と⼦孫が終点である
経路を削除
Slide 125
Slide 125 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⾃⾝が終点の経路を作成
Slide 126
Slide 126 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⼦孫が終点の経路を作成
Slide 127
Slide 127 text
ノードを移動（ Closure Table）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 7
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
⼦孫が終点の経路を作成
Slide 128
Slide 128 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 129
Slide 129 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
親の変更時にコールバックを実⾏
Slide 130
Slide 130 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫のノードを取得
Slide 131
Slide 131 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫の経路を削除
Slide 132
Slide 132 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫の経路を削除
Slide 133
Slide 133 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫の経路を削除
Slide 134
Slide 134 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫の経路を削除
Slide 135
Slide 135 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫の経路を削除
Slide 136
Slide 136 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫の経路を削除
Slide 137
Slide 137 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫の経路を削除
Slide 138
Slide 138 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫の経路を作成
Slide 139
Slide 139 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫の経路を作成
Slide 140
Slide 140 text
Folder移動時にFolderPathを削除・作成
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾃⾝と⼦孫の経路を作成
Slide 141
Slide 141 text
Closure Table まとめ
● メリット
○ 親⼦関係を超えた読み込みが容易
■ JOINを伴うSQL問い合わせ1回
● デメリット
○ ストレージコスト
○ 書き込み操作が困難
■ 経路を正しい状態に維持する必要がある
○ 実装が複雑
Slide 142
Slide 142 text
Adjacency List +
Recursive CTE（再帰CTE）
Slide 143
Slide 143 text
● SQLにおけるCTE (Common Table Expression) の⼀種
○ WITH句のやつ
● 再帰的にクエリを実⾏できる
● 主要なRDBMSでサポートされている
○ MySQL 8.0
○ PostgreSQL 8.4
○ SQLite 3.8.3
● Rails 7.2.0からQueryMethods#with_recursiveが登場
Recursive CTE（再帰CTE）とは
Slide 144
Slide 144 text
Recursive CTEの基本構造
Slide 145
Slide 145 text
Recursive CTEの基本構造
WITH句でCTEを定義
利⽤
Slide 146
Slide 146 text
Recursive CTEの基本構造
⾮再帰項:
初回だけ実⾏される
Slide 147
Slide 147 text
Recursive CTEの基本構造
再帰項: 繰り返し実⾏される
Slide 148
Slide 148 text
Recursive CTEの基本構造
1つ前の実⾏結果を参照
Slide 149
Slide 149 text
Recursive CTEの基本構造
新たなレコードが⽣じなくなるまで繰り返す
Slide 150
Slide 150 text
Recursive CTEの基本構造
レコードの重複の扱い
UNION ALL: 重複削除なし
UNION: 重複削除
Slide 151
Slide 151 text
Recursive CTEの手順
n
nums
Slide 152
Slide 152 text
Recursive CTEの手順
n
1
nums
⾮再帰項を実⾏
Slide 153
Slide 153 text
Recursive CTEの手順
n
1
2
nums
Slide 154
Slide 154 text
Recursive CTEの手順
n
1
2
3
nums
新たなレコードが⽣じなくなるまで繰り返す
Slide 155
Slide 155 text
Recursive CTEの手順
n
1
2
3
4
nums
新たなレコードが⽣じなくなるまで繰り返す
Slide 156
Slide 156 text
Recursive CTEの手順
n
1
2
3
4
5
nums
新たなレコードが⽣じなくなるまで繰り返す
Slide 157
Slide 157 text
Recursive CTEの手順
n
1
2
3
4
5
nums
numsを取得
Slide 158
Slide 158 text
祖先ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 159
Slide 159 text
祖先ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
CTEを定義
Slide 160
Slide 160 text
祖先ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
⾮再帰項:
初回だけ実⾏される
Slide 161
Slide 161 text
祖先ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
再帰項:
繰り返し実⾏される
Slide 162
Slide 162 text
祖先ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
再帰項:
繰り返し実⾏される
Slide 163
Slide 163 text
祖先ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 3
parent_id: 2
CTEを利⽤
Slide 164
Slide 164 text
Folderモデル 祖先ノードを取得（ Adjacency List + Recursive CTE）
Slide 165
Slide 165 text
子孫ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
Slide 166
Slide 166 text
子孫ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
CTEを定義
Slide 167
Slide 167 text
子孫ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
⾮再帰項:
初回だけ実⾏される
Slide 168
Slide 168 text
子孫ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 7
parent_id: 6
再帰項:
繰り返し実⾏される
Slide 169
Slide 169 text
子孫ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
再帰項:
繰り返し実⾏される
Slide 170
Slide 170 text
子孫ノードを取得（ Adjacency List + Recrusive CTE）
id: 3
parent_id: 2
id: 6
parent_id: 1
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 2
parent_id: 1
id: 3
parent_id: 2
id: 4
parent_id: 3
id: 3
parent_id: 2
id: 5
parent_id: 3
id: 3
parent_id: 2
id: 7
parent_id: 6
CTEを利⽤
Slide 171
Slide 171 text
Folderモデル 子孫ノードを取得（ Adjacency List + Recursive CTE）
Slide 172
Slide 172 text
Adjacency List + Recursive CTE 概要
● メリット
○ Adjacency Listからデータ構造を変える必要がない
■ 問い合わせ⽅法が変わるのみ
○ 親⼦関係を超えた読み込みをクエリ1回で取得できる
○ Adjacency Listのままなので書き込みが容易
● デメリット
○ DB内で再帰処理を繰り返す必要があるため深い階層だと低速になる
Slide 173
Slide 173 text
リファクタリング手法の決定
Slide 174
Slide 174 text
リファクタリング手法の決定
Closure Table
読み込み 非常に高速
書き込み 低速
データ構造 閉包テーブルを追加
実装 複雑
Recursive CTE
高速
高速（追加操作なし）
変更なし
容易
● Adjacency Listからの移⾏についてClosure TableとRecursive CTEを⽐較
Slide 175
Slide 175 text
● フォルダ階層化機能を整理
○ 読み込みが多いワークロード
■ ⼀度作成されたフォルダは継続的に閲覧される
○ 階層制限がない
リファクタリング手法の決定
Slide 176
Slide 176 text
● フォルダ階層化機能を整理
○ 読み込みが多いワークロード
■ ⼀度作成されたフォルダは継続的に閲覧される
○ 階層制限がない
リファクタリング手法の決定
読み取りが⾼速なClosure Tableを選択
Slide 177
Slide 177 text
パフォーマンス実験
Slide 178
Slide 178 text
実験:子孫ノードを探索
● ActiveRecord上でトップレベルの
ノードから葉ノード探索処理の
実⾏時間を計測
● 3つの⼿法(Adjacency List, Closure Table,
Adjacency List + Recursive CTE)の
パフォーマンスを⽐較
● 階層構造は完全2分⽊とする
● 深さを変えて実験
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 1
parent_id: null
id: 3
parent_id: 2
id: 3
parent_id: 2
id: 512
parent_id: xxx
id: 3
parent_id: 2
Slide 179
Slide 179 text
実験条件
● ローカルPCのDocker環境（PostgreSQL + Ruby）
● PostgreSQL バージョン: 17.5
● Ruby バージョン: 3.4.3
● Rails バージョン: 8.0.2.1
Slide 180
Slide 180 text
実験条件
● DBスキーマ
Slide 181
Slide 181 text
実験コード
サンプルコード
● 3つの⼿法それぞれについて別プロセスでベンチマークを実⾏する
Slide 182
Slide 182 text
実験結果
深さ6
(127ノード)
深さ9
(1,023ノード)
深さ13
(16,383ノード)
Adjacency List
(real [ms])
38.050 244.622 3749.056
Closure Table
(real [ms])
4.228 4.020 4.024
Recursive CTE
(real [ms])
10.869 10.918 14.111
● Adjacency Listと⽐較して、Closure Tableでは⼤幅に⾼速化を達成
● Closure Tableは横ばい、Recursive CTEは深くなるほど低速
Slide 183
Slide 183 text
深さ6
(127ノード)
深さ9
(1,023ノード)
深さ13
(16,383ノード)
Adjacency List
(real [ms])
38.050 244.622 3749.056
Closure Table
(real [ms])
4.228 4.020 4.024
Recursive CTE
(real [ms])
10.869 10.918 14.111
実験結果
● Adjacency Listと⽐較して、Closure Tableでは⼤幅に⾼速化を達成
● Closure Tableは横ばい、Recursive CTEは深くなるほど低速
x 8 x 60 x 931
Slide 184
Slide 184 text
実験結果
● Adjacency Listと⽐較して、Closure Tableでは⼤幅に⾼速化を達成
● Closure Tableは横ばい、Recursive CTEは深くなるほど低速
深さ6
(127ノード)
深さ9
(1,023ノード)
深さ13
(16,383ノード)
Adjacency List
(real [ms])
38.050 244.622 3749.056
Closure Table
(real [ms])
4.228 4.020 4.024
Recursive CTE
(real [ms])
10.869 10.918 14.111
Slide 185
Slide 185 text
実験結果
● Adjacency Listと⽐較して、Closure Tableでは大幅に高速化を達成
● Closure Tableは横ばい、Closure Tableは深くなるほど低速
深さ6
(127ノード)
深さ9
(1,023ノード)
深さ13
(16,383ノード)
Adjacency List
(real [ms])
38.050 244.622 3749.056
Closure Table
(real [ms])
4.228 4.020 4.024
Recursive CTE
(real [ms])
10.869 10.918 14.111
Closure TableとRecursive CTEについて
SQLクエリの実⾏時間を⾒てみよう
Slide 186
Slide 186 text
深さ6
(127ノード)
深さ9
(1,023ノード)
深さ13
(16,383ノード)
Closure Table
(Execution Time [ms])
0.037 0.031 0.023
Recursive CTE
(Execution Time [ms])
0.056 0.274 4.185
クエリの実行時間（ Closure Table vs Recursive CTE）
● EXPLAIN ANALYZE で実⾏時間の測定
● 結果、クエリ実⾏⾃体はClosure Tableの⽅が⾼速
○ ActiveRecord上ではクエリ実⾏以外にボトルネックがあり、
パフォーマンス差がでていなかった可能性
Slide 187
Slide 187 text
まとめ
Slide 188
Slide 188 text
まとめ
ゴール
1. ✅ 階層構造を表現する2つのデータ構造を理解する
○ Adjacency List（隣接リスト）
○ Closure Table（閉包テーブル）
2. ✅ Recursive CTE（再帰CTE）の概要を理解する
3. 階層化機能において適切な⼿法を選択ができるようになる
Slide 189
Slide 189 text
まとめ
● 階層化機能（親⼦関係を超えた読み込みを伴う場合）のおすすめ⼿法
おすすめ手法
開発初期 Adjacency List
小規模の階層（100ノード） Adjacency List + Recursive CTE
中規模の階層（1,000ノード）
読み込みが多い → Closure Table
書き込みが多い → Adjacency List + Recursive CTE
大規模の階層（10,000ノード） 頻繁に書き込みがなければ Closure Table
Slide 190
Slide 190 text
まとめ
ゴール
1. ✅ 階層構造を表現する2つのデータ構造を理解する
a. Adjacency List（隣接リスト）
b. Closure Table（閉包テーブル）
2. ✅ Recursive CTE（再帰CTE）の概要を理解する
3. ✅ 階層化機能において適切な⼿法を選択ができるようになる
Slide 191
Slide 191 text
参考文献
● Models for hierarchical data with SQL and PHP
○ https://www.slideshare.net/slideshow/models-for-hierarchical-data/4179181
● Bill Karwin「SQLアンチパターン 第⼆版 」
○ https://www.oreilly.co.jp/books/9784814400744
● Add support for recursive CTEs in ActiveRecord
○ https://github.com/rails/rails/pull/51601
● ancestory
○ https://github.com/stefankroes/ancestry
● closure_tree
○ https://github.com/ClosureTree/closure_tree
● PostgreSQL Wiki CTEReadme
○ https://wiki.postgresql.org/wiki/CTEReadme
● MySQL 8.0 レファレンスマニュアル 13.2.15 WITH (共通テーブル式)
○ https://dev.mysql.com/doc/refman/8.0/ja/with.html
Slide 192
Slide 192 text
補足
Slide 193
Slide 193 text
1. folder_pathsテーブルを作成
2. FolderPathモデルを作成
3. Folderモデルに
build_pathメソッドを実装
4. 右のスクリプトを実⾏する
○ トップフォルダから順に
build_pathメソッドを叩いて
経路を作成している
Adjacency List から Closure Table への移行手順
Slide 194
Slide 194 text
非循環のバリデーション
● 階層構造には循環がないことが前提なので、⾮循環のバリデーションを⾏う