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

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 ศརͳγϛϡϨʔλʔ