ハトネコエ
December 06, 2018
9.1k

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

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

について順を追いながら説明。

## ハトネコエ

December 06, 2018

## Transcript

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

΍  MacͷϑΝΠϧγεςϜAPFS ͳͲͰ࢖ΘΕ͍ͯΔ B-tree
4. ### • MySQLͷσʔλϕʔεΤϯδϯ InnoDB Ͱ͸ɺ  ॱʑʹΞΫηε͢ΔੑೳΛ্͛ͨ B+ tree ͕࢖ΘΕ͍ͯΔ • Oracle

Database ΍  MacͷϑΝΠϧγεςϜͩͬͨHFS+ Ͱ͸  B* tree ͕࢖ΘΕ͍ͯΔʢࠓճ͸ѻ͍·ͤΜʣ B-tree ѥछ

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 ܭࢉྔ

10 5 3 2

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 ׬੒ʂ

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 ׬੒ʂ

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