Introduction to Tree Representations in RDB 2024

Slide 1

Slide 1 text

RDBでのツリー表現入門2024 #NextbeatTechBar 1

Slide 2

Slide 2 text

のプロダクトエンジニア主要技術スタック: / + PostgreSQL 関数型言語/関数型プログラミングが好き仕事や趣味で , , などに長く触れてきた RDB/SQLが好きプログラミングを始めて最初に好きになった言語がSQLだった lagénorhynque 🐬カマイルカ株式会社スマートラウンド Kotlin Ktor Clojure Scala Haskell 2

Slide 3

Slide 3 text

0. 事前準備 1. 隣接リスト(adjacency list) 2. 経路列挙(path enumeration) 3. 入れ子集合(nested sets) 4. 閉包テーブル(closure table) 3

Slide 4

Slide 4 text

0. 事前準備 4

Slide 5

Slide 5 text

サンプルコードローカルDB: MySQL 8.4 lagenorhynque/tree-representations-in-rdb # 実験環境の準備 docker compose up -d make migrate compose.yaml 5

Slide 6

Slide 6 text

ツリーの本体データ用テーブル department CREATE TABLE `department` ( `department_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`department_id`) ); 6

Slide 7

Slide 7 text

department の初期データ 7

Slide 8

Slide 8 text

表現したいツリー 8

Slide 9

Slide 9 text

1. 隣接リスト(adjacency list) 9

Slide 10

Slide 10 text

「隣接リスト」モデル 10

Slide 11

Slide 11 text

ツリー管理用のテーブル parent_id: 親ノード本体データ用テーブルに含めても良い CREATE TABLE `department_adjacency_list` ( `department_id` int(10) unsigned NOT NULL, `parent_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`department_id`), FOREIGN KEY (`department_id`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT, FOREIGN KEY (`parent_id`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT ); 11

Slide 12

Slide 12 text

department_adjacency_list の初期データ 12

Slide 13

Slide 13 text

すべてのノードとその階層情報の取得 distance: ルートノードからの距離 cf. WITH RECURSIVE department_tree AS ( SELECT d.*, dal.parent_id, 0 distance FROM department d JOIN department_adjacency_list dal USING (department_id) WHERE dal.parent_id IS NULL UNION ALL SELECT d.*, dal.parent_id, dt.distance + 1 FROM department d JOIN department_adjacency_list dal USING (department_id) JOIN department_tree dt ON dal.parent_id = dt.department_id ) SELECT * FROM department_tree; MySQL :: MySQL 8.4 Reference Manual :: 15.2.20 WITH (Common Table Expressions) 13

Slide 14

Slide 14 text

Slide 15

Slide 15 text

特定のノードからの子孫ノードの取得 distance: ノード 10 からの距離 WITH RECURSIVE department_tree AS ( SELECT d.*, dal.parent_id, 0 distance FROM department d JOIN department_adjacency_list dal USING (department_id) WHERE d.department_id = 10 UNION ALL SELECT d.*, dal.parent_id, dt.distance + 1 FROM department d JOIN department_adjacency_list dal USING (department_id) JOIN department_tree dt ON dal.parent_id = dt.department_id ) SELECT * FROM department_tree; 15

Slide 16

Slide 16 text

Slide 17

Slide 17 text

特定のノードの子ノードの取得 SELECT d.* FROM department d JOIN department_adjacency_list dal USING (department_id) WHERE dal.parent_id = 10; 17

Slide 18

Slide 18 text

Slide 19

Slide 19 text

特定のノードの親ノードの取得 SELECT d.* FROM department d JOIN department_adjacency_list dal ON d.department_id = dal.parent_id WHERE dal.department_id = 10; 19

Slide 20

Slide 20 text

Slide 21

Slide 21 text

PROS モデルとして単純(良くも悪くもナイーブ) 直近の親/子ノードに対するアクセスが容易挿入操作が容易参照整合性が保証できる 21

Slide 22

Slide 22 text

CONS 再帰CTE (recursive common table expressions)が使えないと任意階層に対するクエリが困難削除操作が煩雑 parent_id で NULL が現れてしまうルートノード管理用のテーブルを用意することで回避可能 22

Slide 23

Slide 23 text

2. 経路列挙(path enumeration) a.k.a. 経路実体化(materialized path) 23

Slide 24

Slide 24 text

「経路列挙」モデル 24

Slide 25

Slide 25 text

ツリー管理用のテーブル path: 先祖からそのノードまでの経路(パス) 本体データ用テーブルに含めても良い CREATE TABLE `department_path_enumeration` ( `department_id` int(10) unsigned NOT NULL, `path` varchar(1000) NOT NULL, PRIMARY KEY (`department_id`), FOREIGN KEY (`department_id`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT ); 25

Slide 26

Slide 26 text

department_path_enumeration の初期データ 26

Slide 27

Slide 27 text

すべてのノードとその階層情報の取得 depth: ルートノードからの距離 SELECT d.*, dpe.path, CHAR_LENGTH(dpe.path) - CHAR_LENGTH(REPLACE(dpe.path, '/', '')) - 1 depth FROM department d JOIN department_path_enumeration dpe USING (department_id); 27

Slide 28

Slide 28 text

Slide 29

Slide 29 text

特定のノードからの子孫ノードの取得 depth: ルートノードからの距離 SELECT d.*, dpe.path, CHAR_LENGTH(dpe.path) - CHAR_LENGTH(REPLACE(dpe.path, '/', '')) - 1 depth FROM department d JOIN department_path_enumeration dpe USING (department_id) WHERE dpe.path LIKE CONCAT(( SELECT path FROM department_path_enumeration WHERE department_id = 10 ), '%'); 29

Slide 30

Slide 30 text

Slide 31

Slide 31 text

特定のノードの子ノードの取得 SELECT d.* FROM department d JOIN department_path_enumeration dpe USING (department_id) WHERE dpe.path REGEXP CONCAT(( SELECT path FROM department_path_enumeration WHERE department_id = 10 ), '\\d+/$'); 31

Slide 32

Slide 32 text

Slide 33

Slide 33 text

特定のノードの親ノードの取得 SELECT d.* FROM department d JOIN department_path_enumeration dpe USING (department_id) WHERE dpe.path = ( SELECT CASE WHEN path = CONCAT(department_id, '/') THEN NULL ELSE REGEXP_REPLACE(path, '^(.+/)\\d+/$', '$1') END FROM department_path_enumeration WHERE department_id = 10 ); 33

Slide 34

Slide 34 text

Slide 35

Slide 35 text

PROS 子孫/先祖ノードに対するアクセスが容易更新系操作が容易 35

Slide 36

Slide 36 text

CONS パス文字列をアプリケーションコードで管理しなければならない正規化されていない(第1正規形でさえない) 参照整合性が保証できない 36

Slide 37

Slide 37 text

3. 入れ子集合(nested sets) cf. 入れ子区間(nested intervals) 37

Slide 38

Slide 38 text

「入れ子集合」モデル 38

Slide 39

Slide 39 text

ツリー管理用のテーブル left, right: 数直線上の左右の点を表す depth: ルートノードからの距離(必須ではない) 本体データ用テーブルに含めても良い CREATE TABLE `department_nested_sets` ( `department_id` int(10) unsigned NOT NULL, `left` int(11) NOT NULL, `right` int(11) NOT NULL, `depth` int(10) unsigned NOT NULL, PRIMARY KEY (`department_id`), FOREIGN KEY (`department_id`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT ); 39

Slide 40

Slide 40 text

department_nested_sets の初期データ 40

Slide 41

Slide 41 text

すべてのノードとその階層情報の取得 SELECT d.*, dns.left, dns.right, dns.depth FROM department d JOIN department_nested_sets dns USING (department_id); 41

Slide 42

Slide 42 text

Slide 43

Slide 43 text

特定のノードからの子孫ノードの取得 SELECT d.*, dns.left, dns.right, dns.depth FROM department d JOIN department_nested_sets dns USING (department_id) JOIN department_nested_sets dns2 ON dns.left BETWEEN dns2.left AND dns2.right WHERE dns2.department_id = 10; 43

Slide 44

Slide 44 text

Slide 45

Slide 45 text

特定のノードの子ノードの取得 SELECT d.* FROM department d JOIN department_nested_sets dns USING (department_id) JOIN department_nested_sets dns2 ON dns.left BETWEEN dns2.left AND dns2.right WHERE dns2.department_id = 10 AND dns.depth = ( SELECT depth FROM department_nested_sets WHERE department_id = 10 ) + 1; 45

Slide 46

Slide 46 text

Slide 47

Slide 47 text

特定のノードの親ノードの取得 SELECT d.* FROM department d JOIN department_nested_sets dns USING (department_id) JOIN department_nested_sets dns2 ON dns2.left BETWEEN dns.left AND dns.right WHERE dns2.department_id = 10 AND dns.depth + 1 = ( SELECT depth FROM department_nested_sets WHERE department_id = 10 ); 47

Slide 48

Slide 48 text

Slide 49

Slide 49 text

PROS 子孫/先祖ノードに対するアクセスが容易削除操作が容易 49

Slide 50

Slide 50 text

CONS 左右の点の値をアプリケーションコードで管理しなければならない正規化されていない(第1正規形でさえない) 挿入操作が非常に煩雑でコストも高い「入れ子区間」では改善される参照整合性が保証できない 50

Slide 51

Slide 51 text

4. 閉包テーブル(closure table) 51

Slide 52

Slide 52 text

「閉包テーブル」モデル 52

Slide 53

Slide 53 text

ツリー管理用のテーブル ancestor, descendant: 先祖/子孫関係にあるノードの組 path_length: ancestor から descendant までの距離(必須ではない) CREATE TABLE `department_closure_table` ( àncestor` int(10) unsigned NOT NULL, `descendant` int(10) unsigned NOT NULL, `path_length` int(10) unsigned NOT NULL, PRIMARY KEY (àncestor`, `descendant`), FOREIGN KEY (àncestor`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT, FOREIGN KEY (`descendant`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE RESTRICT ); 53

Slide 54

Slide 54 text

department_closure_table の初期データ 54

Slide 55

Slide 55 text

すべてのノードとその階層情報の取得 depth: ルートノードからの距離 MAX(dct.path_length) でも求められる SELECT d.*, COUNT(*) - 1 depth FROM department d JOIN department_closure_table dct ON d.department_id = dct.descendant GROUP BY dct.descendant; 55

Slide 56

Slide 56 text

Slide 57

Slide 57 text

特定のノードからの子孫ノードの取得 distance: ノード 10 からの距離 depth: ルートノードからの距離 SELECT d.*, dct.path_length distance, dct2.depth FROM department d JOIN department_closure_table dct ON d.department_id = dct.descendant JOIN ( SELECT descendant, COUNT(*) - 1 depth FROM department_closure_table GROUP BY descendant ) dct2 ON d.department_id = dct2.descendant WHERE dct.ancestor = 10; 57

Slide 58

Slide 58 text

Slide 59

Slide 59 text

特定のノードの子ノードの取得 SELECT d.* FROM department d JOIN department_closure_table dct ON d.department_id = dct.descendant WHERE dct.ancestor = 10 AND dct.path_length = 1; 59

Slide 60

Slide 60 text

Slide 61

Slide 61 text

特定のノードの親ノードの取得 SELECT d.* FROM department d JOIN department_closure_table dct ON d.department_id = dct.ancestor WHERE dct.descendant = 10 AND dct.path_length = 1; 61

Slide 62

Slide 62 text

Slide 63

Slide 63 text

PROS 子孫/先祖ノードに対するアクセスが容易更新系操作が容易参照整合性が保証できる 63

Slide 64

Slide 64 text

CONS 他のモデルよりも保持すべきデータが多くなる 64

Slide 65

Slide 65 text

まとめ「閉包テーブル」は総合的に優れている「隣接リスト」は最も素朴(ナイーブ)な設計だがデメリットもいくつかある「経路列挙」「入れ子集合」は参照整合性が保証できない(RDBの強みが犠牲になる) 65

Slide 66

Slide 66 text

Further Reading 2章　ナイーブツリー（素朴な木）第10章　グラフに立ち向かう 10.4　ツリー（木）第10章　階層的なデータ構造『SQLアンチパターン』『理論から学ぶデータベース実践入門』『Effective SQL』 66

Slide 67

Slide 67 text

第9章　一歩進んだ論理設計～SQLで木構造を扱う『達人に学ぶDB設計徹底指南書』『プログラマのためのSQLグラフ原論』 What are the options for storing hierarchical data in a relational database? - Stack Overflow 67