Save 37% off PRO during our Black Friday Sale! »

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

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

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

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

959960084f2086d7a875a30d2a428682?s=128

ハトネコエ

December 06, 2018
Tweet

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