Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PostgreSQLで学ぶリレーショナルデータモデルとその実装

 PostgreSQLで学ぶリレーショナルデータモデルとその実装

中国DB勉強会でのトーク資料です。

Akira Suenami

May 01, 2016
Tweet

More Decks by Akira Suenami

Other Decks in Technology

Transcript

  1. PostgreSQLͰֶͿ
    ϦϨʔγϣφϧσʔλϞσϧͱͦͷ࣮૷
    Twitter: @a_suenami
    Github: a-suenami
    id: a_suenami

    View full-size slide

  2. ໔੹ࣄ߲
    • ຊൃදͷ಺༰͸ࢲݸਓͷݟղͰ͋Γɺॴଐ͢Δ૊৫ɾஂମͷݟղΛ
    ඞͣ͠΋൓өͨ͠΋ͷͰ͸͋Γ·ͤΜɻྃ͝ঝ͍ͩ͘͞ɻ

    View full-size slide

  3. ࣗݾ঺հ
    • ຤ฒ ߊʢ͑͢ͳΈ ͖͋Βʣ
    • @a_suenami
    • ޷͖ͳ΋ͷ͸ RDB ͱ͔ΦϒδΣΫτࢦ޲ͱ͔ TDD
    ͱ͔ɻ
    • ࠷ۙ͸౶࣭ܯ࡯ͱͯ͠׆ಈதɻ

    View full-size slide

  4. ࢓ࣄ
    ঁͷࢠͷ͔Θ͍͍Λ࡞ͬͯ·͢ɻ

    View full-size slide

  5. ຊൃදͷ໨త
    • PostgreSQL Λ͸͡Ίͱ͢ΔϦϨʔγϣφϧσʔλϕʔεʢ RDB ʣ
    ͷࠜװΛ੒͢ϦϨʔγϣφϧσʔλϞσϧʹ͍ͭͯཧղͯ͠΋Β͏ɻ
    • RDB ΍ͦΕʹର͢Δ໰͍߹Θͤݴޠͱͯ͠ͷ SQL ͸ϦϨʔγϣφϧ
    σʔλϞσϧΛϕʔεʹ͠ͳ͕Β΋ɺͦΕҎ্ʹศརʹ࢖͏ͨΊʹଟ
    ͘ͷ௥Ճ࣮૷͕͋Δ͜ͱΛཧղͯ͠΋Β͏ɻ
    • PostgreSQL ΛྫʹͱͬͯɺϦϨʔγϣφϧσʔλϞσϧ͓ΑͼͦΕ
    Ҏ֎ͷ࣮૷ʹ͍ͭͯ஌ͬͯ΋Β͏͜ͱɻ

    View full-size slide

  6. ·ͣ͸ࠓ೔͜ͷձ৔ʹ
    Ͳ͏͍͏ਓ͕͍Δͷ͔஌Γ͍ͨͰ͢

    View full-size slide

  7. ֶੜͷਓ ϊϊϊ

    View full-size slide

  8. ʢݚڀ΍ۀ຿Ͱʣ
    গͳ͘ͱ΋ͻͱͭҎ্ͷRDBMS ੡඼Λ
    ࢖ͬͨܦݧ͕͋Δਓ ϊϊϊ

    View full-size slide

  9. ϦϨʔγϣφϧσʔλϞσϧͬͯ
    Կ͔஌ͬͯΔਓ ϊϊϊ

    View full-size slide

  10. ͋Γ͕ͱ͏͍͟͝·͢ɻ
    ࢀߟʹ͠·͢ɻ

    View full-size slide

  11. ϦϨʔγϣφϧσʔλϞσϧ

    View full-size slide

  12. ϦϨʔγϣφϧσʔλϞσϧͷجૅ
    • ͋Δ෺ࣄʹؔ͢Δࣄ࣮ͷू߹ʢ=ϦϨʔγϣϯʣΛѻ͏σʔλϞσ
    ϧɻ
    • ϦϨʔγϣϯ ≠ ϦϨʔγϣϯγοϓ
    • ϦϨʔγϣϯʢؔ܎ʣͱ͸ɺRDB ʹ͓͚Δςʔϒϧؒͷؔ܎ʢࢀ
    রɺ֎෦ΩʔʣͰ͸ͳ͘ɺςʔϒϧ಺ͷΧϥϜؒͷؔ܎ɺͭ·Γςʔ
    ϒϧͦͷ΋ͷͷ͜ͱͰ͋Δɻ

    View full-size slide

  13. ೋ߲ؔ܎ͱड़ޠ࿦ཧͱϦϨʔγϣϯ
    • ͋Δ 2 ͭͷू߹ͷཁૉ x, y ͷ૊Έ߹Θ͕ͤ͋Δ৚݅ R Λຬͨ͢৔߹ɺʮx ͱ y
    ͸ R ͷؔ܎ʹ͋Δʯͱ͍͍ɺ͜ͷ૊Έ߹Θͤͷू߹Λೋ߲ؔ܎ͱ͍͏ɻ
    • ྫ: x + y = 3 ͱͳΔΑ͏ͳࣗવ਺ͷू߹
    • (0, 3), (1, 2), (2, 1), (3, 0)
    • ϦϨʔγϣφϧσʔλϞσϧ͸͜ΕΛ n ߲ʹ֦ுͨ͠΋ͷͰ͋Δɻ
    • ͕ͨͬͯ͠ɺϦϨʔγϣϯͷ֤ཁૉ͸ n ࣍ݩ্ۭؒͷ͋Δ࠲ඪΛද͢͜ͱʹ
    ͳΔɻ
    • ॏෳ͸ͳ͍ɻ
    • ॱং΋ͳ͍ɻ

    View full-size slide

  14. ؔ܎ԋࢉ
    • ੍ݶʢ Restrict ʣ
    • ࣹӨʢ Project ʣ
    • ࿨ʢ Union ʣ
    • ੵʢ Intersect ʣ
    • ௚ੵʢ Product ʣ
    • ݁߹ʢ Join ʣ

    View full-size slide

  15. ੍ݶ
    ձһ൪߸ ੏ ໊ ೥ྸ
    ຤ฒ ߊ
    ࢁా ଠ࿠
    ాத Ֆࢠ
    ࠤ౻ ୓໵
    ձһ൪߸ ੏ ໊ ೥ྸ
    ຤ฒ ߊ
    ࠤ౻ ୓໵

    View full-size slide

  16. ࣹӨ
    ձһ൪߸ ੏ ໊ ೥ྸ
    ຤ฒ ߊ
    ࢁా ଠ࿠
    ాத Ֆࢠ
    ࠤ౻ ୓໵
    ձһ൪߸ ੏ ໊
    ຤ฒ ߊ
    ࢁా ଠ࿠
    ాத Ֆࢠ
    ࠤ౻ ୓໵

    View full-size slide


  17. ձһ൪߸ ੏ ໊ ೥ྸ
    ຤ฒ ߊ
    ࢁా ଠ࿠
    ాத Ֆࢠ
    ࠤ౻ ୓໵
    ձһ൪߸ ੏ ໊ ೥ྸ
    ླ໦ Ұ࿠
    ߴڮ ࿨ࢠ
    ձһ൪߸ ੏ ໊ ೥ྸ
    ຤ฒ ߊ
    ࢁా ଠ࿠
    ాத Ֆࢠ
    ࠤ౻ ୓໵
    ླ໦ Ұ࿠
    ߴڮ ࿨ࢠ

    View full-size slide

  18. ݁߹
    ձһ൪߸ ੏ ໊ ೥ྸ ౎ಓ෎ݝ
    ຤ฒ ߊ େ෼
    ࢁా ଠ࿠ ࢁޱ
    ాத Ֆࢠ ࢁޱ
    ࠤ౻ ୓໵ ޿ౡ
    ౎ಓ෎ݝ ݝிॴࡏ஍
    ๺ւಓ ࡳຈ

    ࢁޱ ࢁޱ

    େ෼ େ෼

    ձһ൪߸ ੏ ໊ ೥ྸ ౎ಓ෎ݝ ݝிॴࡏ஍
    ຤ฒ ߊ େ෼ େ෼
    ࢁా ଠ࿠ ࢁޱ ࢁޱ
    ాத Ֆࢠ ࢁޱ ࢁޱ
    ࠤ౻ ୓໵ ޿ౡ ޿ౡ

    View full-size slide


  19. • ݁߹ͷಛघͳέʔεɻ
    • ͢΂ͯͷଐੑ͕׬શʹҰக͢Δ৔߹ɻ
    ੏ ໊
    ຤ฒ ߊ
    ࢁా ଠ࿠
    ాத Ֆࢠ
    ࠤ౻ ୓໵
    ੏ ໊
    ຤ฒ ߊ
    ాத Ֆࢠ
    ੏ ໊
    ຤ฒ ߊ
    ాத Ֆࢠ

    View full-size slide

  20. ௚ੵ
    • ݁߹ͷಛघͳέʔεɻ
    • Ұக͢Δଐੑ͕ͻͱͭ΋ͳ͍৔߹ɻ
    ੏ ໊
    ຤ฒ ߊ
    ࢁా ଠ࿠
    εΩϧ
    1PTUHSF42-
    .Z42-
    ੏ ໊ εΩϧ
    ຤ฒ ߊ 1PTUHSF42-
    ຤ฒ ߊ .Z42-
    ࢁా ଠ࿠ 1PTUHSF42-
    ࢁా ଠ࿠ .Z42-

    View full-size slide

  21. SQL ͱͷରԠ
    ؔ܎ԋࢉ 42-
    ੍ݶ 8)&3&
    ࣹӨ 4&-&$5
    ࿨ 6/*0/
    ݁߹ +0*/

    View full-size slide

  22. ϦϨʔγϣφϧσʔλϞσϧͱ
    ਖ਼نԽཧ࿦

    View full-size slide

  23. Կނਖ਼نԽ͕ඞཁ͔
    • σʔλ͕ϦϨʔγϣϯͰදݱ͞Ε͍ͯΕ͹ؔ܎ԋࢉΛߦͳ͏͜ͱ͸
    ՄೳͰ͋Δɻ
    • ͨͩ͠ɺͦΕ͸σʔλͷ੔߹ੑΛอূͯ͘͠ΕΔ͜ͱΛҙຯ͢ΔΘ
    ͚Ͱ͸ͳ͍ɻ
    • ʮԋࢉ͕ՄೳͰ͋Δʯ͜ͱͱʮԋࢉ݁Ռ͕ଥ౰Ͱ͋Δ͜ͱ͕อূ͞
    ΕΔʯ͜ͱ͸ҟͳΔɻ
    • ਖ਼نԽ͸σʔλͷ੔߹ੑΛҡ࣋͢Δ্Ͱඞཁͳཧ࿦Ͱ͋Δɻ

    View full-size slide

  24. ਖ਼نԽͷछྨ
    • ୈ̍ਖ਼نܗ
    • ୈ̎ਖ਼نܗ
    • ୈ̏ਖ਼نܗ
    • ϘΠείουਖ਼نܗ
    • ୈ̐ਖ਼نܗ
    • ୈ̑ਖ਼نܗ
    • ୈ̒ਖ਼نܗ

    View full-size slide

  25. ༻ޠ঺հ
    • ީิΩʔ
    • ϦϨʔγϣϯͷཁૉΛҰҙʹಛఆͰ͖Δଐੑͷू߹ͰɺͦΕҎ্ཁૉΛݮΒ͢͜ͱ͕Ͱ
    ͖ͳ͍΋ͷɻ
    • εʔύʔΩʔ
    • ީิΩʔͷεʔύʔηοτɻ
    • ͢΂ͯͷଐੑΛؚΉू߹͸ৗʹεʔύʔΩʔͰ͋Δɻ
    • ؔ਺ैଐੑ
    • ϦϨʔγϣϯͷཁૉͷ͋Δଐੑ a ͕ఆ·ͬͨΒผͷଐੑ b ΋ఆ·Δ৔߹ɺb ͸ a ʹؔ਺
    ैଐ͢Δͱ͍͏ɻ
    • ݁߹ैଐੑ
    • ςʔϒϧΛ݁߹ͨ͠৔߹ʹݩʹ໭ΔΑ͏ʹແଛࣦ෼ղͰ͖Δ͜ͱɻ

    View full-size slide

  26. ୈ̍ਖ਼نܗ
    • σʔλ͕ϦϨʔγϣϯͰදݱ͞Ε͍ͯΔ͜ͱɻ
    • ٯʹݴ͏ͱඇਖ਼نܗͷσʔλ͸ͦ΋ͦ΋ϦϨʔγϣφϧσʔλϞσ
    ϧʹैͬͯͳ͍ͱ͍͏͜ͱʹͳΔɻ
    • ΧϯϚ۠੾Γจࣈྻɺ഑ྻɺJSON ౳ͷσʔλ͸ؔ܎ԋࢉͰѻ͑ͳ
    ͍ͷͰ͜͏͍ͬͨσʔλ͕ഉআ͞Εͨঢ়ଶ͕ୈ1ਖ਼نܗͰ͋Δɻ

    View full-size slide

  27. ୈ̎ਖ਼نܗ
    • ީิΩʔͷҰ෦͔ΒඇΩʔଐੑ΁ͷؔ਺ैଐ͕ഉআ͞Εͨঢ়ଶɻ
    • ୯ҰଐੑͷީิΩʔ͔͠ଘࡏ͠ͳ͍৔߹ʹ͸ࣗಈతʹୈ2ਖ਼نܗͱ
    ͳΔɻ
    ੏ ໊ ೥ྸ ॻ੶
    ຤ฒ ߊ σʔλϕʔε࣮ફೖ໳
    ຤ฒ ߊ 42-Ξϯνύλʔϯ
    ຤ฒ ߊ υϝΠϯۦಈઃܭ
    ࢁా ଠ࿠ σʔλϕʔε࣮ફೖ໳
    ੏ ໊ ೥ྸ
    ຤ฒ ߊ
    ࢁా ଠ࿠
    ੏ ໊ ॻ੶
    ຤ฒ ߊ σʔλϕʔε࣮ફೖ໳
    ຤ฒ ߊ 42-Ξϯνύλʔϯ
    ຤ฒ ߊ υϝΠϯۦಈઃܭ
    ࢁా ଠ࿠ σʔλϕʔε࣮ફೖ໳

    View full-size slide

  28. ୈ̏ਖ਼نܗ
    • ඇΩʔଐੑͷਪҠؔ਺ैଐ͕ഉআ͞Εͨঢ়ଶɻ
    ੏ ໊ ೥ྸ ౎ಓ෎ݝ ݝிॴࡏ஍
    ຤ฒ ߊ େ෼ େ෼
    ࢁా ଠ࿠ ࢁޱ ࢁޱ
    ాத Ֆࢠ ࢁޱ ࢁޱ
    ࠤ౻ ୓໵ ޿ౡ ޿ౡ
    ੏ ໊ ೥ྸ ౎ಓ෎ݝ
    ຤ฒ ߊ େ෼
    ࢁా ଠ࿠ ࢁޱ
    ాத Ֆࢠ ࢁޱ
    ࠤ౻ ୓໵ ޿ౡ
    ౎ಓ෎ݝ ݝிॴࡏ஍
    ๺ւಓ ࡳຈ

    ࢁޱ ࢁޱ

    େ෼ େ෼

    View full-size slide

  29. ϘΠείουਖ਼نܗ
    • ඇΩʔଐੑ͔ΒީิΩʔʹର͢Δؔ਺ैଐੑΛഉআͨ͠΋ͷɻ
    • ΄ͱΜͲͷ৔߹ʹ͓͍ͯୈ̏ਖ਼نܗͱಉ͡ʹͳΔ͕ɺީิΩʔ͕ෳ
    ਺ଘࡏ͢Δ৔߹ʹ͸ୈ̏ਖ਼نܗͰ͸͋Δ͕ϘΠείουਖ਼نܗͰ͸
    ͳ͍͜ͱ͕ى͜Γ͏Δɻ
    ੏ ໊ ϓϩδΣΫτ Ϛωʔδϟʔ
    ຤ฒ ߊ 999 ҏ౻
    ຤ฒ ߊ ::: தଜ
    ࢁా ଠ࿠ 999 খྛ
    ࢁా ଠ࿠ ;;; ࢁຊ
    Ϛωʔδϟʔ ϓϩδΣΫτ
    ҏ౻ 999
    தଜ :::
    খྛ 999
    ࢁຊ ;;;
    ੏ ໊ Ϛωʔδϟʔ
    ຤ฒ ߊ ҏ౻
    ຤ฒ ߊ தଜ
    ࢁా ଠ࿠ খྛ
    ࢁా ଠ࿠ ࢁຊ

    View full-size slide

  30. ୈ4ਖ਼نܗ
    • Ұ෦ͷ݁߹ैଐੑʢଟ஋ैଐੑʣΛഉআͨ͠ঢ়ଶɻ
    • ୈ̐ਖ਼نܗͱୈ̑ਖ਼نܗ͸ީิΩʔͷΈͰߏ੒͞ΕΔϦϨʔγϣϯ
    ͷΈ͕ਖ਼نԽͷର৅ͱͳΔɻ
    ঎඼໊ ৭ αΠζ
    ϫϯϐʔε ന 4
    ϫϯϐʔε ന .
    ϫϯϐʔε ϐϯΫ 4
    ϫϯϐʔε ϐϯΫ .
    εΧʔτ ࠇ 4
    εΧʔτ ࠇ .
    εΧʔτ ࠇ -
    εΧʔτ ന 4
    εΧʔτ ന .
    εΧʔτ ന -
    ๧ࢠ ੨ .
    ঎඼໊ αΠζ
    ϫϯϐʔε 4
    ϫϯϐʔε .
    εΧʔτ 4
    εΧʔτ .
    εΧʔτ -
    ๧ࢠ .
    ঎඼໊ ৭
    ϫϯϐʔε ന
    ϫϯϐʔε ϐϯΫ
    εΧʔτ ࠇ
    εΧʔτ ന
    ๧ࢠ ੨

    View full-size slide

  31. ୈ5ਖ਼نܗ
    • ͢΂ͯͷ݁߹ैଐੑΛഉআͨ͠ঢ়ଶɻ
    • ࣄ্࣮ɺਖ਼نܗͷ࠷ऴஈ֊Ͱ͋Δɻ
    • ୈ̒ਖ਼نܗ΋ଘࡏ͸͢Δ͕࣮ࡍʹ͸͋·Γҙຯ͕ͳ͍ɻ

    View full-size slide

  32. ୈ4ਖ਼نܗ / ୈ5ਖ਼نܗ
    • ݁߹ैଐੑΛഉআͨ͠ঢ়ଶɻ
    • ީิΩʔͷΈͰߏ੒͞ΕΔϦϨʔγϣϯͷ৔߹ʹ͸͜ΕΒͷਖ਼نԽ
    Λͨ͠΄͏͕Α͍৔߹΋͋Δɻ

    View full-size slide

  33. ਖ਼نԽ͠ͳ͍ͱ͍͏બ୒ࢶ
    • ͦ΋ͦ΋ϦϨʔγϣφϧσʔλϞσϧͱ૬ੑ͕ѱ͍σʔλͷ৔߹͸ඞ
    ͣ͠΋ਖ਼نԽΛ͢Δඞཁ͸ͳ͍ɻ
    • ৔߹ʹΑͬͯ͸ͦ΋ͦ΋ҧ͏ΞʔΩςΫνϟΛݕ౼ͨ͠΄͏͕Α͍ɻ
    • ཤྺσʔλ
    • ໦ߏ଄σʔλ
    • άϥϑσʔλ
    • ෳࡶͳॱং෇͖Ϧετ

    View full-size slide

  34. PostgreSQL ʹ͓͚Δ࣮૷

    View full-size slide

  35. ϦϨʔγϣφϧσʔλϞσϧʹ͸ͳ͍͕SQLʹ͋Δ΋ͷ
    • NULL
    • ॱংɾฒͼସ͑
    • τϥϯβΫγϣϯ
    • ΠϯσοΫε

    View full-size slide

  36. PostgreSQL ͷ࣮૷
    • JOIN
    • ΠϯσοΫε
    • τϥϯβΫγϣϯ

    View full-size slide

  37. ݁߹ͷ࣮૷
    • ωεςουϧʔϓ݁߹
    • Ϛʔδ݁߹
    • ϋογϡ݁߹

    View full-size slide

  38. ωεςουϧʔϓ݁߹

    View full-size slide

  39. ϋογϡ݁߹

    View full-size slide

  40. ΠϯσοΫε ͷ࣮૷
    • B-Tree ΠϯσοΫε
    • B-Tree ߏ଄Λ࣋ͭ൚༻తʹ༻͍ΒΕΔΠϯσοΫεɻ
    • ϋογϡΠϯσοΫε
    • ΠϯσοΫεΛϋογϡςʔϒϧͰอ࣋͢Δɻ
    • ݱࡏ͸ඇਪ঑ɻ
    • GIN ΠϯσοΫε
    • શจݕࡧ༻్ʹ༻͍ΔΠϯσοΫεɻ
    • GiST ΠϯσοΫε
    • ۭؒ৘ใ޲͚ͷΠϯσοΫεɻ

    View full-size slide

  41. B-Tree ͷߏ଄

    View full-size slide

  42. τϥϯβΫγϣϯ
    • ϦϨʔγϣφϧσʔλϞσϧͱਖ਼نԽཧ࿦͸σʔλͷ੔߹ੑΛอͭ
    ͨΊʹ༗ޮͰ͋Δ͕ɺෳ਺ͷؔ܎ԋࢉΛΞτϛοΫʹ࣮ࢪ͠ͳ͍ͱ
    σʔλ੔߹ੑ͕յΕΔέʔεͰ͸τϥϯβΫγϣϯͷ֓೦͕ඞਢʹ
    ͳΔɻ
    • τϥϯβΫγϣϯ͸ϦϨʔγϣφϧσʔλϞσϧʹ͓͚Δ֓೦Ͱ͸
    ͳ͍͕ RDB ʹ͸ඞਢͷ࢓૊ΈͰ͋Δɻ

    View full-size slide

  43. τϥϯβΫγϣϯ෼཭Ϩϕϧ
    • Read Uncommitted
    • τϥϯβΫγϣϯ෼཭Ϩϕϧ͕࠷΋௿͘ɺμʔςΟϦʔυʢଞͷτϥϯβΫγϣϯ
    ͕ॻ͖ࠐΜͰ·ͩίϛοτ͍ͯ͠ͳ͍σʔλΛಡΉʣ͕ൃੜ͢ΔՄೳੑ͕͋Δɻ
    • Read Committed
    • ଞͷτϥϯβΫγϣϯ͕ίϛοτͨ͠σʔλΛࢀর͢Δ͕൓෮ಡΈࠐΈͷՄೳੑ͸
    ͋Δɻ
    • Repeatable Read
    • ಉҰτϥϯβΫγϣϯ಺ͰԿ౓ಉ͡σʔλΛࢀরͯ͠΋಺༰͸มΘΒͣɺ൓෮ಡΈ
    औΓʹରͯ҆͠શʹͳΔɻ
    • Serializable
    • τϥϯβΫγϣϯ͕௚ྻʹฒͼଞͷ͢΂ͯͷτϥϯβΫγϣϯ͔Β෼཭͞ΕΔɻ

    View full-size slide

  44. ·ͱΊ
    • RDB Λ࢖͏ͨΊʹ͸ϦϨʔγϣφϧσʔλϞσϧΛ͖ͪΜͱཧղ͢
    Δඞཁ͕͋Δɻ
    • ϦϨʔγϣφϧσʔλϞσϧͷݶքΛ஌ΓɺϦϨʔγϣφϧσʔ
    λϞσϧ͕ۤखͱ͢Δ෦෼͸ผͷΞʔΩςΫνϟͰઃܭΛ͢Δɻ
    • RDB ͕࣮ࡍʹͲͷΑ͏ʹ࣮૷͞Ε͍ͯΔ͔Λཧղ͠ɺద੾ʹઃܭΛ
    ͢Δɻ

    View full-size slide

  45. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠

    View full-size slide