これでわかるB-treeアルゴリズム / B-tree algorithm

by ハトネコエ

Slide 1

Slide 1 text

͜ΕͰΘ͔Δ(?)  B-tree ΞϧΰϦζϜ 2018/12/06 ࣾ಺ษڧձ ϋτωίΤ

Slide 2

Slide 2 text

ϋτωίΤ @nekonenene ࢲ͸ॳԻϛΫͰ͢ ͝ଘ஌͋ͷํ

Slide 3

Slide 3 text

• B໦ͱ΋ݺ͹ΕΔ • σʔλߏ଄ͷҰछͰ͋Δ໦ߏ଄ͷҰछ • ϧʔτ͔Βͷߴ͕͞Ұఆͷฏߧ໦ʢ΁͍͜͏͗ʣͷҰछ • Mongo DBͳͲͷσʔλϕʔεͷ΄͔ɺ  WindowsͷϑΝΠϧγεςϜNTFS ΍  MacͷϑΝΠϧγεςϜAPFS ͳͲͰ࢖ΘΕ͍ͯΔ B-tree

Slide 4

Slide 4 text

• MySQLͷσʔλϕʔεΤϯδϯ InnoDB Ͱ͸ɺ  ॱʑʹΞΫηε͢ΔੑೳΛ্͛ͨ B+ tree ͕࢖ΘΕ͍ͯΔ • Oracle Database ΍  MacͷϑΝΠϧγεςϜͩͬͨHFS+ Ͱ͸  B* tree ͕࢖ΘΕ͍ͯΔʢࠓճ͸ѻ͍·ͤΜʣ B-tree ѥछ

Slide 5

Slide 5 text

Χϯλϯͳ΋ͷ͔Β  ࢝Ί͍ͯ͜͏

Slide 6

Slide 6 text

• ΋ͬͱ΋୯७ͳ໦ߏ଄ • খ͍͞஋ΛࠨԼʹɺେ͖͍஋ΛӈԼʹ͚͍ͭͯ͘ ೋ෼୳ࡧ໦

Slide 7

Slide 7 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5

Slide 8

Slide 8 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 ਌ϊʔυ ࢠϊʔυ

Slide 9

Slide 9 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 3 2

Slide 10

Slide 10 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 3 2 8

Slide 11

Slide 11 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 3 2 8 4

Slide 12

Slide 12 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 3 2 1 8 4

Slide 13

Slide 13 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 3 2 1 8 4 6

Slide 14

Slide 14 text

• ໦ͷόϥϯε͕ۉ౳ʹ੔͍ͬͯΕ͹ɺ  ཁૉ਺ n ʹରͯ͠୳ࡧʹ͔͔Δ࠷ѱܭࢉྔ͸ O (log n) ʹͰ͖Δ • ͭ·Γɺσʔλ͕૿͑ͯ΋ݕࡧͷ͕࣌ؒ૿͑ͳ͍ ೋ෼୳ࡧ໦ͷར఺ 10 5 2 8 16 12 20 ཁૉ਺ n ܭࢉྔ

Slide 15

Slide 15 text

• ࠨԼͷਤͷΑ͏ͳภͬͨ໦ߏ଄ͷ࠷ѱͷ৔߹Λߟ͑Δ • ཁૉ਺͕ଟ͍΄ͲൺֱΛ͓͜ͳ͏ͷͰ࠷ѱܭࢉྔ͸ O (n) ೋ෼୳ࡧ໦ͷܽ఺ ཁૉ਺ n ܭࢉྔ 10 5 3 2

Slide 16

Slide 16 text

ͦΕͳΒ੔ͬͨ  ໦ߏ଄ʹ͠Α͏ʂ

Slide 17

Slide 17 text

• ߟҊऀͷ2ਓͷ໊લ͕༝དྷʢAdelson-Velskii and Landis' treeʣ • ࠨ෦෼໦ͷߴ͞ͱӈ෦෼໦ͷߴ͕͞1ΑΓେ͖͘ͳͬͨΒ  ʮ໦ͷճసʯʹΑΓ࠶ߏ੒͠ɺฏߧΛอͭ AVL໦

Slide 18

Slide 18 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5

Slide 19

Slide 19 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 ʮ10ʯ͔Βݟͯ  ࠨ͸ߴ͞ 2  ӈ͸ߴ͞ 0  ʹͳͬͯ͠·͏ ͦ͜Ͱɺ໦ͷճసʂ

Slide 20

Slide 20 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 த৺ʹ͍ͨ  ʮ5ʯΛ਌ʹͯ͠  ଞΛࢠʹ͢Δ

Slide 21

Slide 21 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2

Slide 22

Slide 22 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8

Slide 23

Slide 23 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4

Slide 24

Slide 24 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4 1

Slide 25

Slide 25 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4 1 ͭͳ͛ΒΕΔՕॴͷߴ͞ͷ͕ࠩ2  ͜Ε͸໦ͷճస͕ى͜Δʁ ? ?

Slide 26

Slide 26 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4 1 ʮ5ʯ͔Βݟͯ  ࠨͷߴ͞ 3  ӈͷߴ͞ 2 ʮ3ʯ͔Βݟͯ  ࠨͷߴ͞ 2  ӈͷߴ͞ 1 ࠨ෦෼໦ͱӈ෦෼໦ͷߴ͞ͷࠩΛ  ݟΔͷͰɺ·ͩ଱͑·͢

Slide 27

Slide 27 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4 1 6 ʮ10ʯ͔Βݟͯ  ࠨͷߴ͞ 2  ӈͷߴ͞ 0 ໦ͷճసνϟϯεʂ

Slide 28

Slide 28 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4 1 6 த৺ʹ͍Δ  ʮ8ʯΛ਌ʹ͠ɺ  ଞ2ͭ͸ࢠʹ

Slide 29

Slide 29 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 8 5 3 2 6 4 1 10 ׬੒ʂ

Slide 30

Slide 30 text

• ࠷ѱܭࢉྔ͕େ͖͘ͳΒͳ͍Α͏ɺ  ໦ͷճసΛ༻͍ɺͰ͖Δ͚ͩภΓͷͳ͍໦Λ࡞͍ͬͯͬͨ • ͜ͷΑ͏ͳฏୱʹۙ෇͚Δ໦ߏ଄Λ  ฏߧ໦ʢ΁͍͜͏͗ʣͱݺͿ AVL໦ ͓͞Β͍

Slide 31

Slide 31 text

• ໦ͷߴ͕͞ߴ͘ͳΓ΍͍͢ • ݁Ռɺσʔλྔ͕ͱͯ΋ଟ͍৔߹ɺݕࡧʹ͕͔͔࣌ؒΔ • → ͦ͜Ͱ B-tree ʂ AVL໦ͷܽ఺

Slide 32

Slide 32 text

• ֤ϊʔυʹ࣋ͨͤΔ஋Λ1ͭͰͳ͘ෳ਺ʹ͠ɺ໦ͷߴ͞Λ཈͑Δ • ࠓ·Ͱͷϊʔυ͸஋XΑΓখ͍͔ͦ͞ΕҎ্͔ɺͷ  2ຊͷࢬ͔͍࣋ͬͯ͠ͳ͔͕ͬͨɺB-treeͰ͸ΑΓଟ͘ͷࢬΛ࣋ͭ • ྫ͑͹ X ͱ Y ͷ 2 ͭͷ஋Λϊʔυʹ΋ͭͱܾΊͨ৔߹ɺ  ஋Xະຬ / ஋XҎ্ͰYະຬ / ஋YҎ্  ͷ3ຊͷࢬΛϊʔυ͸࠷େͰ࣋ͭʢΦʔμʔ3ʣ ͦ͜Ͱ B-tree

Slide 33

Slide 33 text

• ϊʔυ͕࠷େͰ࣋ͭࢬͷ਺ʹԠͯ͡ɺ  ʮΦʔμʔ m ͷB-treeʯͱ͍͏ݴ͍ํΛ͢Δ • Φʔμʔ͸ 3 ʙ 5 ͕Α͋͘ΔΒ͍͠ • ࠓճ͸ɺΦʔμʔ 3 ͷ B-tree ʢͭ·Γϊʔυʹ2ͭͷ஋Λ࣋ͭʣ  ʹ͍ͭͯྫΛݟ͍ͯ͘ɻ  ࢠϊʔυΛ2ʙ3࣋ͭ͜ͱ͔Βɺ2-3 tree ͱ΋ݺ͹ΕΔ Φʔμʔ

Slide 34

Slide 34 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 10 ϊʔυʹ2ͭͷ  ஋Λ΋ͭ

Slide 35

Slide 35 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 10 ϊʔυʹ  3ͭ໨ͷ஋͕…… 3 த৺ͷ஋͸਌ϊʔυʹҠಈʂ

Slide 36

Slide 36 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 10 3

Slide 37

Slide 37 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 10 2 3

Slide 38

Slide 38 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 8 2 3 10

Slide 39

Slide 39 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 8 2 3 10 4

Slide 40

Slide 40 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 8 2 3 10 4 த৺ͷ஋͸  ਌ϊʔυʹҠಈʂ

Slide 41

Slide 41 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 8 2 3 10 4 Զ͕਌ϊʔυͩʂ த৺ͷ஋͸  ਌ϊʔυʹҠಈʂ

Slide 42

Slide 42 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 8 2 10 4 5

Slide 43

Slide 43 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 8 1 10 4 5 2

Slide 44

Slide 44 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 6 1 8 4 5 2 10 த৺ͷ஋͸  ਌ϊʔυʹҠಈʂ

Slide 45

Slide 45 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 6 1 8 4 5 2 10 ͋Ε……͓अຐʁ

Slide 46

Slide 46 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 6 1 8 4 5 2 10 த৺ͷ஋͸  ਌ϊʔυʹҠಈʂ

Slide 47

Slide 47 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 6 1 8 4 5 2 10 ߴ͞Ξοϓʂ ˍ׬੒ʂ

Slide 48

Slide 48 text

• جຊͷΞϧΰϦζϜ͸ B-tree • ࠷Լ૚ͷϊʔυΛϙΠϯλͰͭͳ͗߹Θͤͨ͜ͱͰɺ  খ͞ͳ஋͔Βେ͖ͳ஋΁ॱʑʹΞΫηε͍ͯ͘͠  ͱ͍͏ڍಈͷύϑΥʔϚϯεΛ্͛Δ͜ͱʹ੒ޭ • ઌ΄Ͳಉ༷ɺΦʔμʔ 3 ʢϊʔυ͕࠷େͰ࣋ͭࢬ͕3ຊʣͷ  B+ tree ʹ͍ͭͯݟ͍ͯ͘ B+ tree

Slide 49

Slide 49 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 10

Slide 50

Slide 50 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 10 ϊʔυʹ  3ͭ໨ͷ஋͕…… 3 த৺ͷ஋͸਌ϊʔυʹҠಈʂ

Slide 51

Slide 51 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 3 10 ਌ϊʔυ΁Ҡಈͭͭ͠… ΋ͱͷ஋΋࢒͢ʂ

Slide 52

Slide 52 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 3 10 ࠷Լ૚ಉ࢜Λ  ϙΠϯλͰͭͳ͙ʂ ͜Ε͕ B+ tree ͷ Ұ൪ͷಛ௃ʂ

Slide 53

Slide 53 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 3 10

Slide 54

Slide 54 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 2 10 3

Slide 55

Slide 55 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 2 8 3 10 த৺ͷ஋͸  ਌ϊʔυʹҠಈʂ

Slide 56

Slide 56 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 2 8 3 10 8 ΋ͱͷ஋΋࢒͢

Slide 57

Slide 57 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 2 8 3 10 8 4 த৺ͷ஋͸  ਌ϊʔυʹҠಈʂ

Slide 58

Slide 58 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 2 8 3 10 8 4 3 ΋ͱͷ஋΋࢒͢

Slide 59

Slide 59 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 3ͭʹͳͬͯ͠·ͬͨ…… 2

Slide 60

Slide 60 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 2 த৺ͷ஋͸  ਌ϊʔυʹҠಈʂ 3ͭʹͳͬͯ͠·ͬͨ……

Slide 61

Slide 61 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 3 8 5 Θ͔Γ΍͍͢Α͏  ্෦෼͚ͩߟ͑Δ ৽͍͠ύύͩΑ ΋ͱͷ஋΋࢒͢

Slide 62

Slide 62 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 3 8 5 Θ͔Γ΍͍͢Α͏  ্෦෼͚ͩߟ͑Δ 5ະຬ 5Ҏ্

Slide 63

Slide 63 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 2 ͳͷͰಉ༷ʹ……

Slide 64

Slide 64 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 2 5 ͜͏ͳΔʂ

Slide 65

Slide 65 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 2 5 ʢ஫ҙʣ ͜͏͸ͳΒͳ͍ Φʔμʔ 3 ͷ  B໦Ͱ͸  ࠷௿ 2 ͭͷ  ࢠϊʔυΛ࣋ͭ

Slide 66

Slide 66 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 2 5 ࿩Λ໭ͯ࣍͠΁…

Slide 67

Slide 67 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 1 5 2

Slide 68

Slide 68 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 1 5 2 6

Slide 69

Slide 69 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 1 5 2 6 ׬੒ʂ

Slide 70

Slide 70 text

• ࠷Լ૚ͷϊʔυ͕ϙΠϯλͰͭͳ͕͍ͬͯΔͨΊɺ  খ͞ͳ஋͔Βେ͖ͳ஋΁ॱʑʹΞΫηε͍͖͍ͯͨ͠৔߹ʹɺ  ຖճݕࡧͷखؒΛڬ·ͳͯ͘ࡁΉ • ͳ͓ɺ͜ͷ࠷Լ૚ͷϊʔυΛ  ʮϦʔϑϊʔυʢleaf nodesʣʯͱݴ͍·͢ B+ tree ͓͞Β͍

Slide 71

Slide 71 text

• 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 1 5 2 6 Ϧʔϑϊʔυ

Slide 72

Slide 72 text

• ΧϥϜʹΠϯσοΫεΛషΔ͜ͱͰɺ  B+ tree ͷߏ଄͕࡞ΒΕɺॱʑʹͳ͍ͬͯΔͷͰ  ൣғݕࡧ͕εϐʔυग़͠΍͍͢ • ͳ͓ɺInno DB Ͱ͸ϦʔϑϊʔυؒͷϙΠϯλ͕  Ұํ޲Ͱ͸ͳ͘૒ํ޲ʹͳ͍ͬͯΔ Inno DB ʹ͓͍ͯ 5 8 10 4 3 1 2 6

Slide 73

Slide 73 text

• AVL໦  https://www.cs.usfca.edu/~galles/visualization/AVLtree.html • B-tree  https://www.cs.usfca.edu/~galles/visualization/BTree.html • B+ tree  https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html • ͦͷଞ  https://www.cs.usfca.edu/~galles/visualization/Algorithms.html ศརͳγϛϡϨʔλʔ