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

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

1. PostgreSQLͰֶͿ ϦϨʔγϣφϧσʔλϞσϧͱͦͷ࣮૷ Twitter: @a_suenami Github: a-suenami id: a_suenami
2. ໔੹ࣄ߲ • ຊൃදͷ಺༰͸ࢲݸਓͷݟղͰ͋Γɺॴଐ͢Δ૊৫ɾஂମͷݟղΛ ඞͣ͠΋൓өͨ͠΋ͷͰ͸͋Γ·ͤΜɻྃ͝ঝ͍ͩ͘͞ɻ
3. ࣗݾ঺հ • ຤ฒ ߊʢ͑͢ͳΈ ͖͋Βʣ • @a_suenami • ޷͖ͳ΋ͷ͸ RDB ͱ͔ΦϒδΣΫτࢦ޲ͱ͔ TDD ͱ͔ɻ • ࠷ۙ͸౶࣭ܯ࡯ͱͯ͠׆ಈதɻ
4. ࢓ࣄ ঁͷࢠͷ͔Θ͍͍Λ࡞ͬͯ·͢ɻ
5. ຊൃදͷ໨త • PostgreSQL Λ͸͡Ίͱ͢ΔϦϨʔγϣφϧσʔλϕʔεʢ RDB ʣ ͷࠜװΛ੒͢ϦϨʔγϣφϧσʔλϞσϧʹ͍ͭͯཧղͯ͠΋Β͏ɻ • RDB ΍ͦΕʹର͢Δ໰͍߹Θͤݴޠͱͯ͠ͷ SQL ͸ϦϨʔγϣφϧ σʔλϞσϧΛϕʔεʹ͠ͳ͕Β΋ɺͦΕҎ্ʹศརʹ࢖͏ͨΊʹଟ ͘ͷ௥Ճ࣮૷͕͋Δ͜ͱΛཧղͯ͠΋Β͏ɻ • PostgreSQL ΛྫʹͱͬͯɺϦϨʔγϣφϧσʔλϞσϧ͓ΑͼͦΕ Ҏ֎ͷ࣮૷ʹ͍ͭͯ஌ͬͯ΋Β͏͜ͱɻ
6. ·ͣ͸ࠓ೔͜ͷձ৔ʹ Ͳ͏͍͏ਓ͕͍Δͷ͔஌Γ͍ͨͰ͢
7. ֶੜͷਓ ϊϊϊ
8. ʢݚڀ΍ۀ຿Ͱʣ গͳ͘ͱ΋ͻͱͭҎ্ͷRDBMS ੡඼Λ ࢖ͬͨܦݧ͕͋Δਓ ϊϊϊ
9. ϦϨʔγϣφϧσʔλϞσϧͬͯ Կ͔஌ͬͯΔਓ ϊϊϊ
10. ͋Γ͕ͱ͏͍͟͝·͢ɻ ࢀߟʹ͠·͢ɻ
11. ϦϨʔγϣφϧσʔλϞσϧ
13. ϦϨʔγϣφϧσʔλϞσϧͷجૅ • ͋Δ෺ࣄʹؔ͢Δࣄ࣮ͷू߹ʢ=ϦϨʔγϣϯʣΛѻ͏σʔλϞσ ϧɻ • ϦϨʔγϣϯ ≠ ϦϨʔγϣϯγοϓ • ϦϨʔγϣϯʢؔ܎ʣͱ͸ɺRDB ʹ͓͚Δςʔϒϧؒͷؔ܎ʢࢀ রɺ֎෦ΩʔʣͰ͸ͳ͘ɺςʔϒϧ಺ͷΧϥϜؒͷؔ܎ɺͭ·Γςʔ ϒϧͦͷ΋ͷͷ͜ͱͰ͋Δɻ
14. ೋ߲ؔ܎ͱड़ޠ࿦ཧͱϦϨʔγϣϯ • ͋Δ 2 ͭͷू߹ͷཁૉ x, y ͷ૊Έ߹Θ͕ͤ͋Δ৚݅ R Λຬͨ͢৔߹ɺʮx ͱ y ͸ R ͷؔ܎ʹ͋Δʯͱ͍͍ɺ͜ͷ૊Έ߹Θͤͷू߹Λೋ߲ؔ܎ͱ͍͏ɻ • ྫ: x + y = 3 ͱͳΔΑ͏ͳࣗવ਺ͷू߹ • (0, 3), (1, 2), (2, 1), (3, 0) • ϦϨʔγϣφϧσʔλϞσϧ͸͜ΕΛ n ߲ʹ֦ுͨ͠΋ͷͰ͋Δɻ • ͕ͨͬͯ͠ɺϦϨʔγϣϯͷ֤ཁૉ͸ n ࣍ݩ্ۭؒͷ͋Δ࠲ඪΛද͢͜ͱʹ ͳΔɻ • ॏෳ͸ͳ͍ɻ • ॱং΋ͳ͍ɻ
15. ؔ܎ԋࢉ • ੍ݶʢ Restrict ʣ • ࣹӨʢ Project ʣ • ࿨ʢ Union ʣ • ੵʢ Intersect ʣ • ௚ੵʢ Product ʣ • ݁߹ʢ Join ʣ
16. ੍ݶ ձһ൪߸ ੏ ໊ ೥ྸ  ຤ฒ ߊ   ࢁా ଠ࿠   ాத Ֆࢠ   ࠤ౻ ୓໵  ձһ൪߸ ੏ ໊ ೥ྸ  ຤ฒ ߊ   ࠤ౻ ୓໵ 
17. ࣹӨ ձһ൪߸ ੏ ໊ ೥ྸ  ຤ฒ ߊ   ࢁా ଠ࿠   ాத Ֆࢠ   ࠤ౻ ୓໵  ձһ൪߸ ੏ ໊  ຤ฒ ߊ  ࢁా ଠ࿠  ాத Ֆࢠ  ࠤ౻ ୓໵
18. ࿨ ձһ൪߸ ੏ ໊ ೥ྸ  ຤ฒ ߊ   ࢁా ଠ࿠   ాத Ֆࢠ   ࠤ౻ ୓໵  ձһ൪߸ ੏ ໊ ೥ྸ  ླ໦ Ұ࿠   ߴڮ ࿨ࢠ  ձһ൪߸ ੏ ໊ ೥ྸ  ຤ฒ ߊ   ࢁా ଠ࿠   ాத Ֆࢠ   ࠤ౻ ୓໵   ླ໦ Ұ࿠   ߴڮ ࿨ࢠ 
19. ݁߹ ձһ൪߸ ੏ ໊ ೥ྸ ౎ಓ෎ݝ  ຤ฒ ߊ  େ෼  ࢁా ଠ࿠  ࢁޱ  ాத Ֆࢠ  ࢁޱ  ࠤ౻ ୓໵  ޿ౡ ౎ಓ෎ݝ ݝிॴࡏ஍ ๺ւಓ ࡳຈ   ࢁޱ ࢁޱ   େ෼ େ෼   ձһ൪߸ ੏ ໊ ೥ྸ ౎ಓ෎ݝ ݝிॴࡏ஍  ຤ฒ ߊ  େ෼ େ෼  ࢁా ଠ࿠  ࢁޱ ࢁޱ  ాத Ֆࢠ  ࢁޱ ࢁޱ  ࠤ౻ ୓໵  ޿ౡ ޿ౡ
20. ੵ • ݁߹ͷಛघͳέʔεɻ • ͢΂ͯͷଐੑ͕׬શʹҰக͢Δ৔߹ɻ ੏ ໊ ຤ฒ ߊ ࢁా ଠ࿠ ాத Ֆࢠ ࠤ౻ ୓໵ ੏ ໊ ຤ฒ ߊ ాத Ֆࢠ ੏ ໊ ຤ฒ ߊ ాத Ֆࢠ
21. ௚ੵ • ݁߹ͷಛघͳέʔεɻ • Ұக͢Δଐੑ͕ͻͱͭ΋ͳ͍৔߹ɻ ੏ ໊ ຤ฒ ߊ ࢁా ଠ࿠ εΩϧ 1PTUHSF42- .Z42- ੏ ໊ εΩϧ ຤ฒ ߊ 1PTUHSF42- ຤ฒ ߊ .Z42- ࢁా ଠ࿠ 1PTUHSF42- ࢁా ଠ࿠ .Z42-
22. SQL ͱͷରԠ ؔ܎ԋࢉ 42- ੍ݶ 8)&3& ࣹӨ 4&-&$5 ࿨ 6/*0/ ݁߹ +0*/
23. ϦϨʔγϣφϧσʔλϞσϧͱ ਖ਼نԽཧ࿦
24. Կނਖ਼نԽ͕ඞཁ͔ • σʔλ͕ϦϨʔγϣϯͰදݱ͞Ε͍ͯΕ͹ؔ܎ԋࢉΛߦͳ͏͜ͱ͸ ՄೳͰ͋Δɻ • ͨͩ͠ɺͦΕ͸σʔλͷ੔߹ੑΛอূͯ͘͠ΕΔ͜ͱΛҙຯ͢ΔΘ ͚Ͱ͸ͳ͍ɻ • ʮԋࢉ͕ՄೳͰ͋Δʯ͜ͱͱʮԋࢉ݁Ռ͕ଥ౰Ͱ͋Δ͜ͱ͕อূ͞ ΕΔʯ͜ͱ͸ҟͳΔɻ • ਖ਼نԽ͸σʔλͷ੔߹ੑΛҡ࣋͢Δ্Ͱඞཁͳཧ࿦Ͱ͋Δɻ
25. ਖ਼نԽͷछྨ • ୈ̍ਖ਼نܗ • ୈ̎ਖ਼نܗ • ୈ̏ਖ਼نܗ • ϘΠείουਖ਼نܗ • ୈ̐ਖ਼نܗ • ୈ̑ਖ਼نܗ • ୈ̒ਖ਼نܗ
26. ༻ޠ঺հ • ީิΩʔ • ϦϨʔγϣϯͷཁૉΛҰҙʹಛఆͰ͖Δଐੑͷू߹ͰɺͦΕҎ্ཁૉΛݮΒ͢͜ͱ͕Ͱ ͖ͳ͍΋ͷɻ • εʔύʔΩʔ • ީิΩʔͷεʔύʔηοτɻ • ͢΂ͯͷଐੑΛؚΉू߹͸ৗʹεʔύʔΩʔͰ͋Δɻ • ؔ਺ैଐੑ • ϦϨʔγϣϯͷཁૉͷ͋Δଐੑ a ͕ఆ·ͬͨΒผͷଐੑ b ΋ఆ·Δ৔߹ɺb ͸ a ʹؔ਺ ैଐ͢Δͱ͍͏ɻ • ݁߹ैଐੑ • ςʔϒϧΛ݁߹ͨ͠৔߹ʹݩʹ໭ΔΑ͏ʹແଛࣦ෼ղͰ͖Δ͜ͱɻ
27. ୈ̍ਖ਼نܗ • σʔλ͕ϦϨʔγϣϯͰදݱ͞Ε͍ͯΔ͜ͱɻ • ٯʹݴ͏ͱඇਖ਼نܗͷσʔλ͸ͦ΋ͦ΋ϦϨʔγϣφϧσʔλϞσ ϧʹैͬͯͳ͍ͱ͍͏͜ͱʹͳΔɻ • ΧϯϚ۠੾Γจࣈྻɺ഑ྻɺJSON ౳ͷσʔλ͸ؔ܎ԋࢉͰѻ͑ͳ ͍ͷͰ͜͏͍ͬͨσʔλ͕ഉআ͞Εͨঢ়ଶ͕ୈ1ਖ਼نܗͰ͋Δɻ
28. ୈ̎ਖ਼نܗ • ީิΩʔͷҰ෦͔ΒඇΩʔଐੑ΁ͷؔ਺ैଐ͕ഉআ͞Εͨঢ়ଶɻ • ୯ҰଐੑͷީิΩʔ͔͠ଘࡏ͠ͳ͍৔߹ʹ͸ࣗಈతʹୈ2ਖ਼نܗͱ ͳΔɻ ੏ ໊ ೥ྸ ॻ੶ ຤ฒ ߊ  σʔλϕʔε࣮ફೖ໳ ຤ฒ ߊ  42-Ξϯνύλʔϯ ຤ฒ ߊ  υϝΠϯۦಈઃܭ ࢁా ଠ࿠  σʔλϕʔε࣮ફೖ໳ ੏ ໊ ೥ྸ ຤ฒ ߊ  ࢁా ଠ࿠  ੏ ໊ ॻ੶ ຤ฒ ߊ σʔλϕʔε࣮ફೖ໳ ຤ฒ ߊ 42-Ξϯνύλʔϯ ຤ฒ ߊ υϝΠϯۦಈઃܭ ࢁా ଠ࿠ σʔλϕʔε࣮ફೖ໳
29. ୈ̏ਖ਼نܗ • ඇΩʔଐੑͷਪҠؔ਺ैଐ͕ഉআ͞Εͨঢ়ଶɻ ੏ ໊ ೥ྸ ౎ಓ෎ݝ ݝிॴࡏ஍ ຤ฒ ߊ  େ෼ େ෼ ࢁా ଠ࿠  ࢁޱ ࢁޱ ాத Ֆࢠ  ࢁޱ ࢁޱ ࠤ౻ ୓໵  ޿ౡ ޿ౡ ੏ ໊ ೥ྸ ౎ಓ෎ݝ ຤ฒ ߊ  େ෼ ࢁా ଠ࿠  ࢁޱ ాத Ֆࢠ  ࢁޱ ࠤ౻ ୓໵  ޿ౡ ౎ಓ෎ݝ ݝிॴࡏ஍ ๺ւಓ ࡳຈ   ࢁޱ ࢁޱ   େ෼ େ෼  
30. ϘΠείουਖ਼نܗ • ඇΩʔଐੑ͔ΒީิΩʔʹର͢Δؔ਺ैଐੑΛഉআͨ͠΋ͷɻ • ΄ͱΜͲͷ৔߹ʹ͓͍ͯୈ̏ਖ਼نܗͱಉ͡ʹͳΔ͕ɺީิΩʔ͕ෳ ਺ଘࡏ͢Δ৔߹ʹ͸ୈ̏ਖ਼نܗͰ͸͋Δ͕ϘΠείουਖ਼نܗͰ͸ ͳ͍͜ͱ͕ى͜Γ͏Δɻ ੏ ໊ ϓϩδΣΫτ Ϛωʔδϟʔ ຤ฒ ߊ 999 ҏ౻ ຤ฒ ߊ ::: தଜ ࢁా ଠ࿠ 999 খྛ ࢁా ଠ࿠ ;;; ࢁຊ Ϛωʔδϟʔ ϓϩδΣΫτ ҏ౻ 999 தଜ ::: খྛ 999 ࢁຊ ;;; ੏ ໊ Ϛωʔδϟʔ ຤ฒ ߊ ҏ౻ ຤ฒ ߊ தଜ ࢁా ଠ࿠ খྛ ࢁా ଠ࿠ ࢁຊ
31. ୈ4ਖ਼نܗ • Ұ෦ͷ݁߹ैଐੑʢଟ஋ैଐੑʣΛഉআͨ͠ঢ়ଶɻ • ୈ̐ਖ਼نܗͱୈ̑ਖ਼نܗ͸ީิΩʔͷΈͰߏ੒͞ΕΔϦϨʔγϣϯ ͷΈ͕ਖ਼نԽͷର৅ͱͳΔɻ ঎඼໊ ৭ αΠζ ϫϯϐʔε ന 4 ϫϯϐʔε ന . ϫϯϐʔε ϐϯΫ 4 ϫϯϐʔε ϐϯΫ . εΧʔτ ࠇ 4 εΧʔτ ࠇ . εΧʔτ ࠇ - εΧʔτ ന 4 εΧʔτ ന . εΧʔτ ന - ๧ࢠ ੨ . ঎඼໊ αΠζ ϫϯϐʔε 4 ϫϯϐʔε . εΧʔτ 4 εΧʔτ . εΧʔτ - ๧ࢠ . ঎඼໊ ৭ ϫϯϐʔε ന ϫϯϐʔε ϐϯΫ εΧʔτ ࠇ εΧʔτ ന ๧ࢠ ੨
32. ୈ5ਖ਼نܗ • ͢΂ͯͷ݁߹ैଐੑΛഉআͨ͠ঢ়ଶɻ • ࣄ্࣮ɺਖ਼نܗͷ࠷ऴஈ֊Ͱ͋Δɻ • ୈ̒ਖ਼نܗ΋ଘࡏ͸͢Δ͕࣮ࡍʹ͸͋·Γҙຯ͕ͳ͍ɻ
33. ୈ4ਖ਼نܗ / ୈ5ਖ਼نܗ • ݁߹ैଐੑΛഉআͨ͠ঢ়ଶɻ • ީิΩʔͷΈͰߏ੒͞ΕΔϦϨʔγϣϯͷ৔߹ʹ͸͜ΕΒͷਖ਼نԽ Λͨ͠΄͏͕Α͍৔߹΋͋Δɻ
34. ਖ਼نԽ͠ͳ͍ͱ͍͏બ୒ࢶ • ͦ΋ͦ΋ϦϨʔγϣφϧσʔλϞσϧͱ૬ੑ͕ѱ͍σʔλͷ৔߹͸ඞ ͣ͠΋ਖ਼نԽΛ͢Δඞཁ͸ͳ͍ɻ • ৔߹ʹΑͬͯ͸ͦ΋ͦ΋ҧ͏ΞʔΩςΫνϟΛݕ౼ͨ͠΄͏͕Α͍ɻ • ཤྺσʔλ • ໦ߏ଄σʔλ • άϥϑσʔλ • ෳࡶͳॱং෇͖Ϧετ
35. PostgreSQL ʹ͓͚Δ࣮૷
36. ϦϨʔγϣφϧσʔλϞσϧʹ͸ͳ͍͕SQLʹ͋Δ΋ͷ • NULL • ॱংɾฒͼସ͑ • τϥϯβΫγϣϯ • ΠϯσοΫε
37. PostgreSQL ͷ࣮૷ • JOIN • ΠϯσοΫε • τϥϯβΫγϣϯ
38. ݁߹ͷ࣮૷ • ωεςουϧʔϓ݁߹ • Ϛʔδ݁߹ • ϋογϡ݁߹
39. ωεςουϧʔϓ݁߹
40. Ϛʔδ݁߹
41. ϋογϡ݁߹
42. ΠϯσοΫε ͷ࣮૷ • B-Tree ΠϯσοΫε • B-Tree ߏ଄Λ࣋ͭ൚༻తʹ༻͍ΒΕΔΠϯσοΫεɻ • ϋογϡΠϯσοΫε • ΠϯσοΫεΛϋογϡςʔϒϧͰอ࣋͢Δɻ • ݱࡏ͸ඇਪ঑ɻ • GIN ΠϯσοΫε • શจݕࡧ༻్ʹ༻͍ΔΠϯσοΫεɻ • GiST ΠϯσοΫε • ۭؒ৘ใ޲͚ͷΠϯσοΫεɻ
43. B-Tree ͷߏ଄
44. τϥϯβΫγϣϯ • ϦϨʔγϣφϧσʔλϞσϧͱਖ਼نԽཧ࿦͸σʔλͷ੔߹ੑΛอͭ ͨΊʹ༗ޮͰ͋Δ͕ɺෳ਺ͷؔ܎ԋࢉΛΞτϛοΫʹ࣮ࢪ͠ͳ͍ͱ σʔλ੔߹ੑ͕յΕΔέʔεͰ͸τϥϯβΫγϣϯͷ֓೦͕ඞਢʹ ͳΔɻ • τϥϯβΫγϣϯ͸ϦϨʔγϣφϧσʔλϞσϧʹ͓͚Δ֓೦Ͱ͸ ͳ͍͕ RDB ʹ͸ඞਢͷ࢓૊ΈͰ͋Δɻ
45. τϥϯβΫγϣϯ෼཭Ϩϕϧ • Read Uncommitted • τϥϯβΫγϣϯ෼཭Ϩϕϧ͕࠷΋௿͘ɺμʔςΟϦʔυʢଞͷτϥϯβΫγϣϯ ͕ॻ͖ࠐΜͰ·ͩίϛοτ͍ͯ͠ͳ͍σʔλΛಡΉʣ͕ൃੜ͢ΔՄೳੑ͕͋Δɻ • Read Committed • ଞͷτϥϯβΫγϣϯ͕ίϛοτͨ͠σʔλΛࢀর͢Δ͕൓෮ಡΈࠐΈͷՄೳੑ͸ ͋Δɻ • Repeatable Read • ಉҰτϥϯβΫγϣϯ಺ͰԿ౓ಉ͡σʔλΛࢀরͯ͠΋಺༰͸มΘΒͣɺ൓෮ಡΈ औΓʹରͯ҆͠શʹͳΔɻ • Serializable • τϥϯβΫγϣϯ͕௚ྻʹฒͼଞͷ͢΂ͯͷτϥϯβΫγϣϯ͔Β෼཭͞ΕΔɻ
46. ·ͱΊ • RDB Λ࢖͏ͨΊʹ͸ϦϨʔγϣφϧσʔλϞσϧΛ͖ͪΜͱཧղ͢ Δඞཁ͕͋Δɻ • ϦϨʔγϣφϧσʔλϞσϧͷݶքΛ஌ΓɺϦϨʔγϣφϧσʔ λϞσϧ͕ۤखͱ͢Δ෦෼͸ผͷΞʔΩςΫνϟͰઃܭΛ͢Δɻ • RDB ͕࣮ࡍʹͲͷΑ͏ʹ࣮૷͞Ε͍ͯΔ͔Λཧղ͠ɺద੾ʹઃܭΛ ͢Δɻ
47. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠