階層構造を表現するデータ構造とリファクタリング 〜1年で10倍成長したプロダクトの変化と課題〜

Kaigi on Rails 2025 資料です。
https://kaigionrails.org/2025/talks/Yuhi-Sato/#day2

Transcript

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