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

・二分探索木 (binary search tree)
・AVL tree
・B-tree
・B+ tree

について順を追いながら説明。
流れを細かく書いたので、わかりやすいと思います。

Transcript

1. ͜ΕͰΘ͔Δ(?)
 B-tree ΞϧΰϦζϜ 2018/12/06 ࣾ಺ษڧձ ϋτωίΤ
2. ϋτωίΤ @nekonenene ࢲ͸ॳԻϛΫͰ͢ ͝ଘ஌͋ͷํ
3. • B໦ͱ΋ݺ͹ΕΔ • σʔλߏ଄ͷҰछͰ͋Δ໦ߏ଄ͷҰछ • ϧʔτ͔Βͷߴ͕͞Ұఆͷฏߧ໦ʢ΁͍͜͏͗ʣͷҰछ • Mongo DBͳͲͷσʔλϕʔεͷ΄͔ɺ
 WindowsͷϑΝΠϧγεςϜNTFS ΍
 MacͷϑΝΠϧγεςϜAPFS ͳͲͰ࢖ΘΕ͍ͯΔ B-tree
4. • MySQLͷσʔλϕʔεΤϯδϯ InnoDB Ͱ͸ɺ
 ॱʑʹΞΫηε͢ΔੑೳΛ্͛ͨ B+ tree ͕࢖ΘΕ͍ͯΔ • Oracle Database ΍
 MacͷϑΝΠϧγεςϜͩͬͨHFS+ Ͱ͸
 B* tree ͕࢖ΘΕ͍ͯΔʢࠓճ͸ѻ͍·ͤΜʣ B-tree ѥछ
5. Χϯλϯͳ΋ͷ͔Β
 ࢝Ί͍ͯ͜͏
6. • ΋ͬͱ΋୯७ͳ໦ߏ଄ • খ͍͞஋ΛࠨԼʹɺେ͖͍஋ΛӈԼʹ͚͍ͭͯ͘ ೋ෼୳ࡧ໦
7. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5
8. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 ਌ϊʔυ ࢠϊʔυ
9. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 3 2
10. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 3 2 8
11. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 3 2 8 4
12. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 3 2 1 8 4
13. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ ೋ෼୳ࡧ໦ɿྫ 10 5 3 2 1 8 4 6
14. • ໦ͷόϥϯε͕ۉ౳ʹ੔͍ͬͯΕ͹ɺ
 ཁૉ਺ n ʹରͯ͠୳ࡧʹ͔͔Δ࠷ѱܭࢉྔ͸ O (log n) ʹͰ͖Δ • ͭ·Γɺσʔλ͕૿͑ͯ΋ݕࡧͷ͕࣌ؒ૿͑ͳ͍ ೋ෼୳ࡧ໦ͷར఺ 10 5 2 8 16 12 20 ཁૉ਺ n ܭࢉྔ
15. • ࠨԼͷਤͷΑ͏ͳภͬͨ໦ߏ଄ͷ࠷ѱͷ৔߹Λߟ͑Δ • ཁૉ਺͕ଟ͍΄ͲൺֱΛ͓͜ͳ͏ͷͰ࠷ѱܭࢉྔ͸ O (n) ೋ෼୳ࡧ໦ͷܽ఺ ཁૉ਺ n ܭࢉྔ 10 5 3 2
16. ͦΕͳΒ੔ͬͨ
 ໦ߏ଄ʹ͠Α͏ʂ
17. • ߟҊऀͷ2ਓͷ໊લ͕༝དྷʢAdelson-Velskii and Landis' treeʣ • ࠨ෦෼໦ͷߴ͞ͱӈ෦෼໦ͷߴ͕͞1ΑΓେ͖͘ͳͬͨΒ
 ʮ໦ͷճసʯʹΑΓ࠶ߏ੒͠ɺฏߧΛอͭ AVL໦
18. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5
19. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 ʮ10ʯ͔Βݟͯ
 ࠨ͸ߴ͞ 2
 ӈ͸ߴ͞ 0
 ʹͳͬͯ͠·͏ ͦ͜Ͱɺ໦ͷճసʂ
20. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 த৺ʹ͍ͨ
 ʮ5ʯΛ਌ʹͯ͠
 ଞΛࢠʹ͢Δ
21. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2
22. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8
23. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4
24. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4 1
25. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4 1 ͭͳ͛ΒΕΔՕॴͷߴ͞ͷ͕ࠩ2
 ͜Ε͸໦ͷճస͕ى͜Δʁ ? ?
26. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4 1 ʮ5ʯ͔Βݟͯ
 ࠨͷߴ͞ 3
 ӈͷߴ͞ 2 ʮ3ʯ͔Βݟͯ
 ࠨͷߴ͞ 2
 ӈͷߴ͞ 1 ࠨ෦෼໦ͱӈ෦෼໦ͷߴ͞ͷࠩΛ
 ݟΔͷͰɺ·ͩ଱͑·͢
27. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4 1 6 ʮ10ʯ͔Βݟͯ
 ࠨͷߴ͞ 2
 ӈͷߴ͞ 0 ໦ͷճసνϟϯεʂ
28. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 10 5 3 2 8 4 1 6 த৺ʹ͍Δ
 ʮ8ʯΛ਌ʹ͠ɺ
 ଞ2ͭ͸ࢠʹ
29. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ AVL໦ɿྫ 8 5 3 2 6 4 1 10 ׬੒ʂ
30. • ࠷ѱܭࢉྔ͕େ͖͘ͳΒͳ͍Α͏ɺ
 ໦ͷճసΛ༻͍ɺͰ͖Δ͚ͩภΓͷͳ͍໦Λ࡞͍ͬͯͬͨ • ͜ͷΑ͏ͳฏୱʹۙ෇͚Δ໦ߏ଄Λ
 ฏߧ໦ʢ΁͍͜͏͗ʣͱݺͿ AVL໦ ͓͞Β͍
31. • ໦ͷߴ͕͞ߴ͘ͳΓ΍͍͢ • ݁Ռɺσʔλྔ͕ͱͯ΋ଟ͍৔߹ɺݕࡧʹ͕͔͔࣌ؒΔ • → ͦ͜Ͱ B-tree ʂ AVL໦ͷܽ఺
32. • ֤ϊʔυʹ࣋ͨͤΔ஋Λ1ͭͰͳ͘ෳ਺ʹ͠ɺ໦ͷߴ͞Λ཈͑Δ • ࠓ·Ͱͷϊʔυ͸஋XΑΓখ͍͔ͦ͞ΕҎ্͔ɺͷ
 2ຊͷࢬ͔͍࣋ͬͯ͠ͳ͔͕ͬͨɺB-treeͰ͸ΑΓଟ͘ͷࢬΛ࣋ͭ • ྫ͑͹ X ͱ Y ͷ 2 ͭͷ஋Λϊʔυʹ΋ͭͱܾΊͨ৔߹ɺ
 ஋Xະຬ / ஋XҎ্ͰYະຬ / ஋YҎ্
 ͷ3ຊͷࢬΛϊʔυ͸࠷େͰ࣋ͭʢΦʔμʔ3ʣ ͦ͜Ͱ B-tree
33. • ϊʔυ͕࠷େͰ࣋ͭࢬͷ਺ʹԠͯ͡ɺ
 ʮΦʔμʔ m ͷB-treeʯͱ͍͏ݴ͍ํΛ͢Δ • Φʔμʔ͸ 3 ʙ 5 ͕Α͋͘ΔΒ͍͠ • ࠓճ͸ɺΦʔμʔ 3 ͷ B-tree ʢͭ·Γϊʔυʹ2ͭͷ஋Λ࣋ͭʣ
 ʹ͍ͭͯྫΛݟ͍ͯ͘ɻ
 ࢠϊʔυΛ2ʙ3࣋ͭ͜ͱ͔Βɺ2-3 tree ͱ΋ݺ͹ΕΔ Φʔμʔ
34. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 10 ϊʔυʹ2ͭͷ
 ஋Λ΋ͭ
35. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 10 ϊʔυʹ
 3ͭ໨ͷ஋͕…… 3 த৺ͷ஋͸਌ϊʔυʹҠಈʂ
36. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 10 3
37. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 10 2 3
38. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 8 2 3 10
39. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 8 2 3 10 4
40. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 8 2 3 10 4 த৺ͷ஋͸
 ਌ϊʔυʹҠಈʂ
41. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 5 8 2 3 10 4 Զ͕਌ϊʔυͩʂ த৺ͷ஋͸
 ਌ϊʔυʹҠಈʂ
42. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 8 2 10 4 5
43. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 8 1 10 4 5 2
44. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 6 1 8 4 5 2 10 த৺ͷ஋͸
 ਌ϊʔυʹҠಈʂ
45. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 6 1 8 4 5 2 10 ͋Ε……͓अຐʁ
46. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 6 1 8 4 5 2 10 த৺ͷ஋͸
 ਌ϊʔυʹҠಈʂ
47. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B-treeɿྫ 3 6 1 8 4 5 2 10 ߴ͞Ξοϓʂ ˍ׬੒ʂ
48. • جຊͷΞϧΰϦζϜ͸ B-tree • ࠷Լ૚ͷϊʔυΛϙΠϯλͰͭͳ͗߹Θͤͨ͜ͱͰɺ
 খ͞ͳ஋͔Βେ͖ͳ஋΁ॱʑʹΞΫηε͍ͯ͘͠
 ͱ͍͏ڍಈͷύϑΥʔϚϯεΛ্͛Δ͜ͱʹ੒ޭ • ઌ΄Ͳಉ༷ɺΦʔμʔ 3 ʢϊʔυ͕࠷େͰ࣋ͭࢬ͕3ຊʣͷ
 B+ tree ʹ͍ͭͯݟ͍ͯ͘ B+ tree
49. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 10
50. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 10 ϊʔυʹ
 3ͭ໨ͷ஋͕…… 3 த৺ͷ஋͸਌ϊʔυʹҠಈʂ
51. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 3 10 ਌ϊʔυ΁Ҡಈͭͭ͠… ΋ͱͷ஋΋࢒͢ʂ
52. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 3 10 ࠷Լ૚ಉ࢜Λ
 ϙΠϯλͰͭͳ͙ʂ ͜Ε͕ B+ tree ͷ Ұ൪ͷಛ௃ʂ
53. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 3 10
54. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 2 10 3
55. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 2 8 3 10 த৺ͷ஋͸
 ਌ϊʔυʹҠಈʂ
56. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 2 8 3 10 8 ΋ͱͷ஋΋࢒͢
57. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 2 8 3 10 8 4 த৺ͷ஋͸
 ਌ϊʔυʹҠಈʂ
58. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 2 8 3 10 8 4 3 ΋ͱͷ஋΋࢒͢
59. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 3ͭʹͳͬͯ͠·ͬͨ…… 2
60. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 2 த৺ͷ஋͸
 ਌ϊʔυʹҠಈʂ 3ͭʹͳͬͯ͠·ͬͨ……
61. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 3 8 5 Θ͔Γ΍͍͢Α͏
 ্෦෼͚ͩߟ͑Δ ৽͍͠ύύͩΑ ΋ͱͷ஋΋࢒͢
62. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 3 8 5 Θ͔Γ΍͍͢Α͏
 ্෦෼͚ͩߟ͑Δ 5ະຬ 5Ҏ্
63. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 2 ͳͷͰಉ༷ʹ……
64. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 2 5 ͜͏ͳΔʂ
65. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 2 5 ʢ஫ҙʣ ͜͏͸ͳΒͳ͍ Φʔμʔ 3 ͷ
 B໦Ͱ͸
 ࠷௿ 2 ͭͷ
 ࢠϊʔυΛ࣋ͭ
66. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 2 5 ࿩Λ໭ͯ࣍͠΁…
67. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 1 5 2
68. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 1 5 2 6
69. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 1 5 2 6 ׬੒ʂ
70. • ࠷Լ૚ͷϊʔυ͕ϙΠϯλͰͭͳ͕͍ͬͯΔͨΊɺ
 খ͞ͳ஋͔Βେ͖ͳ஋΁ॱʑʹΞΫηε͍͖͍ͯͨ͠৔߹ʹɺ
 ຖճݕࡧͷखؒΛڬ·ͳͯ͘ࡁΉ • ͳ͓ɺ͜ͷ࠷Լ૚ͷϊʔυΛ
 ʮϦʔϑϊʔυʢleaf nodesʣʯͱݴ͍·͢ B+ tree ͓͞Β͍
71. • 10 → 5 → 3 → 2 → 8 → 4 → 1 → 6 ͷॱʹೖΕ͍͖ͯ·͢ B+ treeɿྫ 5 5 8 3 10 8 4 3 1 5 2 6 Ϧʔϑϊʔυ
72. • ΧϥϜʹΠϯσοΫεΛషΔ͜ͱͰɺ
 B+ tree ͷߏ଄͕࡞ΒΕɺॱʑʹͳ͍ͬͯΔͷͰ
 ൣғݕࡧ͕εϐʔυग़͠΍͍͢ • ͳ͓ɺInno DB Ͱ͸ϦʔϑϊʔυؒͷϙΠϯλ͕
 Ұํ޲Ͱ͸ͳ͘૒ํ޲ʹͳ͍ͬͯΔ Inno DB ʹ͓͍ͯ 5 8 10 4 3 1 2 6
73. • 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 ศརͳγϛϡϨʔλʔ