作って理解する RDBMSのしくみ

by ydah

Slide 1

Slide 1 text

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Confidential © 2024 ANDPAD All Rights Reserved. 6 WORLD TOUR 2024🤘 🇯🇵 2024.05.17 (Naha) RubyKaigi 2024 🇯🇵 2024.08.24 (Osaka) OsakaRubyKaigi04 (Organizing) 🌏 2024.08.31 (ONLINE) RubyKaigi 2024 followup 🇧🇦 2024.09.13 (Sarajevo) EuRuKo 2024 🇯🇵 2024.10.26 (Tokyo) Kaigi on Rails 2024 🇯🇵 2024.12.05 (Matsue) RubyWorld Conference2024

Slide 7

Slide 7 text

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Confidential © 2024 ANDPAD All Rights Reserved. Q 1 RDBMS のしくみについて話します RDBMS のしくみを理解してよりRDBMS を使いこなすためのヒントを持って帰っていただくのが本トークのゴールです。使いこなしていくぞ〜 Kaigi on Railsですが、RubyもRailsの話も全くないです作っているRDBMSのリポジトリは公開予定です Q 2 Q 3 実装もZigで実装しています。なので、RubyやRailsの話は全くありません！曰く、「今回最も議論を巻き起こしたプロポーザル」@amatsuda「トランプのジョーカー」公開も合わせてするぞ！と思っていたのですが、夏休みの宿題を最終日の深夜から始めるタイプの人間でして。スライドって終わらないですね.....はい..... 公開したらTwitter(現X)で呟くのでフォローしてお待ちください。 10 本日のトークについて

Slide 11

Slide 11 text

© 2024 ANDPAD All Rights Reserved. 11 本日のトークについて ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works ◯ 話すこと ●RDBMSの全体像について ●一般的なアーキテクチャ ●データ構造とアルゴリズム ●どのように動いているか × 話さないこと話すこと話さないこと ●RDBMS のつくりかたについて ●細かいコードや実装のはなし ●特定のRDBMSのはなし ●使い方や最適化のテクニック

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Slide 15

Slide 15 text

© 2024 ANDPAD All Rights Reserved. 15 構文解析器クエリを解析しAST(抽象構文木)に変換するクライアントから送られたクエリは、構文解析器(パーサー)が解析してAST(抽象構文木)を生成する。解析には字句解析と構文解析という2つの解析がある。 ●字句解析：バイト列を意味ある単位でトークンに変換する ●構文解析：字句解析から受け取ったトークン列が、　　　　　文法的に正しいかを検査してASTを作成する ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works

Slide 16

Slide 16 text

Slide 17

Slide 17 text

© 2024 ANDPAD All Rights Reserved. 17 字句解析バイト列を意味のある単位でトークンに変換する SELECT * FROM tbl1 WHERE id = 123 AND num > 456; ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works SELECT * FROM tbl1 ...

Slide 18

Slide 18 text

© 2024 ANDPAD All Rights Reserved. 18 構文解析字句解析から受け取ったトークン列が文法的に正しいかを検査してAST(抽象構文木)を作成する SELECT * FROM tbl1 WHERE id = 123 AND num > 456; ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works SELECT * FROM tbl1 ... SELECT * FROM WHERE tbl1 AND = id 123

Slide 19

Slide 19 text

Slide 20

Slide 20 text

© 2024 ANDPAD All Rights Reserved. 20 プランナ/オプティマイザクエリの実行計画を立てるプランナ/オプティマイザはAST(抽象構文木)を元に、最適なクエリの実行計画を立てる。アクセス経路や、インデックスの有無などのパラメーターから、コストの低いクエリを計画する。 ●実行計画：クエリを処理・実行する際の最適な手順を示した計画 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works

Slide 21

Slide 21 text

Slide 22

Slide 22 text

Slide 23

Slide 23 text

© 2024 ANDPAD All Rights Reserved. 23 クエリエクスキュータ実行計画通りにデータにアクセスするプランナやオプティマイザによって生成された実行計画を受け取って、その実行計画に従って必要な操作を実行する。ディスク上のデータ構造にアクセスするアルゴリズムに基づいて結果を返す。 ●実行計画通りにデータの検索・追加・削除などの操作を行う ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works

Slide 24

Slide 24 text

Slide 25

Slide 25 text

© 2024 ANDPAD All Rights Reserved. 25 バッファプールマネージャメモリ上でのキャッシュ管理とページ置換を行い、ディスクアクセスを減らす一般的にディスクI/Oが多いと遅くなる。データベースの主メモリ内にあるバッファプール（キャッシュ領域）を管理し、ディスクへの直接アクセスを減らすことで、ディスクI/Oを最適化する役割を担う。 ●メモリ内データキャッシュの管理を担う ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works

Slide 26

Slide 26 text

© 2024 ANDPAD All Rights Reserved. 26 LRU (Least Recently Used) キャッシュがいっぱいになった時に最も長い時間アクセスされていない要素を削除する戦略 LRU(Least Recently Used)は最低使用頻度の頭字語。記憶域を管理するための一般的な方法で、時間的局所性に基づいてキャッシュを更新するアルゴリズムです。 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works page3 page5 page4 page2 page6 page1 新しいサブリスト古いサブリスト右に行くほど使われていなくて古いページ

Slide 27

Slide 27 text

© 2024 ANDPAD All Rights Reserved. 27 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works page3 page5 page4 page2 page6 page1 右に行くほど使われていなくて古いページ最も昔にアクセスされたページ連結リストを使用して、アクセスの時系列を表現する LRU (Least Recently Used)

Slide 28

Slide 28 text

© 2024 ANDPAD All Rights Reserved. 28 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works page3 page5 page4 page2 page6 page1 右に行くほど使われていなくて古いページ最も昔にアクセスされたページ page6にアクセスが発生すると移動する LRU (Least Recently Used)

Slide 29

Slide 29 text

© 2024 ANDPAD All Rights Reserved. 29 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works page3 page5 page4 page2 page6 page1 右に行くほど使われていなくて古いページ最も昔にアクセスされたページ page2が最も昔にアクセスされたページになる LRU (Least Recently Used)

Slide 30

Slide 30 text

© 2024 ANDPAD All Rights Reserved. 30 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works page3 page5 page4 page2 page1 右に行くほど使われていなくて古いページ最も昔にアクセスされたページキャッシュに新たなページのpage7を追加すると page6 page7 LRU (Least Recently Used)

Slide 31

Slide 31 text

© 2024 ANDPAD All Rights Reserved. 31 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works page3 page5 page4 page2 page1 右に行くほど使われていなくて古いページ最も昔にアクセスされたページキャッシュがいっぱいなので、最も古いページのpage2を削除する page6 page7 LRU (Least Recently Used)

Slide 32

Slide 32 text

Slide 33

Slide 33 text

© 2024 ANDPAD All Rights Reserved. 33 ディスクマネージャディスク上のデータの物理的な読み書きを管理するバッファプールマネージャと連携してディスク上のデータの永続化と物理的なディスクI/Oの最適化を担当する。データの整合性と耐久性を保証する。 ●ディスクへのデータ操作を管理する ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works

Slide 34

Slide 34 text

Slide 35

Slide 35 text

© 2024 ANDPAD All Rights Reserved. 35 データファイルの構造データファイルの構造３種別 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works データがインデックス構造そのものに格納される。データがインデックスと一緒に管理され、検索が効率的です。頻繁な検索と主キーアクセスに適している。ヒープファイルデータが特定の順番にソートされることなく挿入順に格納される。検索は難しく、特定の基準で並び替えるインデックスを別途作成する必要がある。ハッシュファイルデータがハッシュ関数に基づいて格納される。特定のキーに対する高速なアクセスが求められる場合に適している。インデックス構成表 01 02 03

Slide 36

Slide 36 text

© 2024 ANDPAD All Rights Reserved. 36 データファイルの構造データファイルの構造３種別 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works Heap-Organized Tables データがハッシュ関数に基づいて格納される。特定のキーに対する高速なアクセスが求められる場合に適している。 02 02 インデックス構成表ヒープファイル 01 ハッシュファイル 03 データが特定の順番にソートされることなく挿入順に格納される。検索は難しく、特定の基準で並び替えるインデックスを別途作成する必要がある。データがインデックス構造そのものに格納される。データがインデックスと一緒に管理され、検索が効率的です。頻繁な検索と主キーアクセスに適している。

Slide 37

Slide 37 text

© 2024 ANDPAD All Rights Reserved. 37 インデックスがないとどうなるか？線形探索する必要があり計算量も O(n) ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 名前 Alice Dave Olivia Frank : Rupert Craig Ydah Michael Eve : 格納されたデータに順次アクセスをして探す必要がある

Slide 38

Slide 38 text

© 2024 ANDPAD All Rights Reserved. 38 インデックスがあるとどうなるか？ルールごとに並べ替えが可能になる ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works キーの値 Alice Craig Dave Eve : Olivia Rupert Ydah Frank Michael * * * * : * * * * * 名前 Alice Dave Olivia Frank : Rupert Craig Ydah Michael Eve 年齢 24 76 30 12 : 102 87 32 8 55 キーの値 8 12 24 30 : 76 87 102 32 55 * * * * : * * * * *

Slide 39

Slide 39 text

© 2024 ANDPAD All Rights Reserved. 39 インデックスの種類 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 01 B-tree 一般的な構造で、データを階層的なツリー構造で管理する。データが昇順または降順に格納される。等価検索や範囲検索に適しています。 Hash データをハッシュ関数を使用してマッピングする。主に長い文字列データの等価検索に最適で、高速な検索が可能。ただし、範囲検索には向いていない。 GIN 多対多の関係を持つデータや複数の要素を持つデータの検索に適している。配列や JSON、テキストの要素検索に対して優れたパフォーマンスを発揮する。 GiST / SP-GiST さまざまなデータ型や演算子に対応可能。テキスト検索や、範囲データ・空間データ（地理情報など）や類似検索に適している。 02 03 04

Slide 40

Slide 40 text

© 2024 ANDPAD All Rights Reserved. 40 インデックスの種類 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 01 B-tree Hash GIN GiST / SP-GiST 02 03 04 一般的な構造で、データを階層的なツリー構造で管理する。データが昇順または降順に格納される。等価検索や範囲検索に適しています。データをハッシュ関数を使用してマッピングする。主に長い文字列データの等価検索に最適で、高速な検索が可能。ただし、範囲検索には向いていない。多対多の関係を持つデータや複数の要素を持つデータの検索に適している。配列や JSON、テキストの要素検索に対して優れたパフォーマンスを発揮する。さまざまなデータ型や演算子に対応可能。テキスト検索や、範囲データ・空間データ（地理情報など）や類似検索に適している。

Slide 41

Slide 41 text

© 2024 ANDPAD All Rights Reserved. 41 木構造効率的なデータの挿入・削除を可能にする構造 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 木構造とは、一つの要素（ノード）が複数の子要素を持ち、子要素が複数の孫要素を持ち、という具合に階層が深くなるほど枝分かれしていく構造。

Slide 42

Slide 42 text

Slide 43

Slide 43 text

Slide 44

Slide 44 text

© 2024 ANDPAD All Rights Reserved. 44 二分探索木(Binary Search Tree, BST)とは何か ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 構造各ノードには最大で2つの子ノードがあり、左の子ノードは親ノードよりも小さく、右の子ノードは親ノードよりも大きいというルールで構成されている。操作の時間計算量探索、挿入、削除の操作は、木の高さに依存する。最悪の場合、木が片方に偏ると、操作の時間計算量はO(n)になる。よい点 / いまひとつな点シンプルな構造で、比較的容易に実装でき、検索や挿入、削除操作が平均して O(log n)の時間で実行できるが、平衡性が保証されていないため、データの挿入順によっては木が偏ってしまい、性能が劣化する可能性があります。二分探索木(Binary Search Tree, BST)

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Slide 47

Slide 47 text

Slide 48

Slide 48 text

© 2024 ANDPAD All Rights Reserved. 48 AVL木(Adelson-Velskii and Landis' tree) AVL木とは何か ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 構造二分探索木に平衡条件を追加したデータ構造。各ノードについて、その左部分木と右部分木の高さの差が1以下になるように常に保たれている。操作の時間計算量探索、挿入、削除の各操作の最悪時間計算量がO(log n)であり、バランスが崩れないように保たれるため、常に効率的。よい点 / いまひとつな点バランスが保証されるため、最悪の場合でも高速な検索や挿入、削除が可能。また、木の高さが常にlog(n)以下に抑えられるため、性能が安定しています。しかし、単純な二分探索木よりも挿入や削除にわずかに時間がかかる場合があります。

Slide 49

Slide 49 text

© 2024 ANDPAD All Rights Reserved. 49 AVL木(Adelson-Velskii and Landis' tree) ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 5 3 2 4 6 1 高さがズレた時に書いて回転して均す木高さ 0 高さ 2 このノードを検索する場合は遅い

Slide 50

Slide 50 text

Slide 51

Slide 51 text

© 2024 ANDPAD All Rights Reserved. 51 AVL木(Adelson-Velskii and Landis' tree) ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 3 2 1 4 6 5 ノードを付け替える高さ 1 高さ 1 このノードも他のノードと変わらない

Slide 52

Slide 52 text

Slide 53

Slide 53 text

© 2024 ANDPAD All Rights Reserved. 53 B-tree そこで現れた B-tree(B木) 1970年にRudolf BayerとEdward M. McCreightが発表した木構造のデータ構造。ブロック単位のランダムアクセスが可能なHDD上に木構造を構築するのに適した構造として知られる。 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works Bayer, R.; McCreight, E. (July 1970). "Organization and maintenance of large ordered indices" https://dl.acm.org/doi/10.1145/1734663.1734671

Slide 54

Slide 54 text

© 2024 ANDPAD All Rights Reserved. 54 B-tree B-treeとは何か ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 構造二分探索木を拡張した多分木。各ノードは複数のキーと子ノードを持ち、キーはソートされている。バランスの取れた木構造で、ルートからリーフまでの全てのパスの長さが同じになるように設計されている。操作の時間計算量 B-treeの高さはlogベースのt(n)に近似されるため、検索、挿入、削除の操作は最悪でもO(log n)で行われる。よい点 / いまひとつな点ノード内に複数のキーを持てるため、ディスクI/Oが効率的で、大規模データベースやファイルシステムに適している。しかし、木全体にわたってキーが分散しているため、範囲検索に対しては効率がやや劣る場合がある。

Slide 55

Slide 55 text

© 2024 ANDPAD All Rights Reserved. 55 B-tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works B-tree近影 031 008 004 001 006 007 013 026 046 073 099 036 109 024 040 081 104 068

Slide 56

Slide 56 text

© 2024 ANDPAD All Rights Reserved. 56 B-tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 1つのキーに含めるポインタを増やしてツリーの高さを抑える 031 008 004 001 006 007 013 026 046 073 099 036 109 024 040 081 104 068

Slide 57

Slide 57 text

© 2024 ANDPAD All Rights Reserved. 57 B-tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 範囲検索が苦手（001-013を検索する場合） 031 008 001 006 007 013 026 046 073 099 036 109 068 004 024 040 081 104

Slide 58

Slide 58 text

© 2024 ANDPAD All Rights Reserved. 58 B-tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 範囲検索が苦手（001-013を検索する場合） 031 001 006 007 013 026 046 073 099 036 109 068 008 004 040 024 081 104

Slide 59

Slide 59 text

© 2024 ANDPAD All Rights Reserved. 59 B-tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 範囲検索が苦手（001-013を検索する場合） 031 001 006 007 013 026 046 073 099 036 109 068 008 004 024 040 081 104

Slide 60

Slide 60 text

© 2024 ANDPAD All Rights Reserved. 60 B-tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 範囲検索が苦手（001-013を検索する場合） 031 001 006 007 013 026 046 073 099 036 109 068 008 004 024 040 081 104

Slide 61

Slide 61 text

© 2024 ANDPAD All Rights Reserved. 61 B-tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 範囲検索が苦手（001-013を検索する場合） 031 001 006 007 013 026 046 073 099 036 109 068 008 004 024 040 081 104

Slide 62

Slide 62 text

© 2024 ANDPAD All Rights Reserved. 62 B-tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 範囲検索が苦手（001-013を検索する場合） 031 001 006 007 013 026 046 073 099 036 109 068 008 004 024 040 081 104

Slide 63

Slide 63 text

© 2024 ANDPAD All Rights Reserved. 63 B-tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 範囲検索が苦手（001-013を検索する場合） 031 001 006 007 013 026 046 073 099 036 109 068 008 004 024 040 081 104

Slide 64

Slide 64 text

© 2024 ANDPAD All Rights Reserved. 64 B-tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 範囲検索が苦手（001-013を検索する場合） 031 001 006 007 013 026 046 073 099 036 109 068 004 024 008 040 081 104

Slide 65

Slide 65 text

© 2024 ANDPAD All Rights Reserved. 65 B+tree B+treeへの進化 B-treeの特徴を発展させ、データを全てリーフノードに格納し、リーフノード同士を連結リストで結ぶことで、範囲検索や順次アクセスを効率的に行うために改良された。1970〜1980年代にかけてのDBMSの進化とともに生まれた。 ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works Comer, Douglas (1979). "Ubiquitous B-Tree" https://dl.acm.org/doi/10.1145/356770.356776

Slide 66

Slide 66 text

© 2024 ANDPAD All Rights Reserved. 66 B+tree B+treeとは何か ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 構造データをすべてリーフノードに格納するB-treeの一種。内部ノードには索引だけが保存され、実際のデータはリーフノードに持たせている。また、リーフノードは連結リストでつながっており、範囲検索や順次アクセスが効率的に行える。操作の時間計算量 B-tree同様、バランス木なので操作はO(log n)の時間で行えますが、リーフノードの連結リストにより、範囲検索がさらに効率的。よい点 / いまひとつな点範囲検索や順序付きデータの処理が高速で、リーフノードを順次にたどることで効率的にデータを取得できる。しかし、挿入や削除の操作には、リーフノードと内部ノードの両方の管理が必要で、多少のオーバーヘッドがある。

Slide 67

Slide 67 text

© 2024 ANDPAD All Rights Reserved. 67 B+tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 070 024 030 050 010 020 050 060 090 100 110 120 030 040 150 130 140 B+tree近影 0090 0110 0130 150

Slide 68

Slide 68 text

© 2024 ANDPAD All Rights Reserved. 68 B+tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 070 024 030 050 010 020 050 060 090 100 0090 0110 0130 150 110 120 030 040 150 130 140 範囲検索も改善（010-060を検索する場合）

Slide 69

Slide 69 text

© 2024 ANDPAD All Rights Reserved. 69 B+tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 070 024 030 050 010 020 050 060 090 100 0090 0110 0130 150 110 120 030 040 150 130 140 範囲検索も改善（010-060を検索する場合）

Slide 70

Slide 70 text

© 2024 ANDPAD All Rights Reserved. 70 B+tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 070 024 030 050 010 020 050 060 090 100 0090 0110 0130 150 110 120 030 040 150 130 140 範囲検索も改善（010-060を検索する場合）

Slide 71

Slide 71 text

© 2024 ANDPAD All Rights Reserved. 71 B+tree ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works 070 024 030 050 010 020 050 060 090 100 110 120 030 040 150 130 140 範囲検索も改善（010-060を検索する場合） 0090 0110 0130 150

Slide 72

Slide 72 text

© 2024 ANDPAD All Rights Reserved. 72 B+treeの構造を理解したので構造や意図を理解した上で効果的なインデックスを要は構造に基づいた適切な絞り込みができれば早いおそい例：カーディナリティが低いカラムに対してインデックスを貼るのは遅い。複合インデックスを「first_name」と「last_name」に設定している場合に「last_name」での検索は遅い。 ●構造の理解が深まれば、何がよくて何がよくないかが分かる ydah | https://speakerdeck.com/ydah/makin g -and-learnin g -how-rdbms-works