YAPC::Tokyo 2019 Log Friendly DB Design

ログにやさしいDB設計

Transcript

1. ϩάʹ΍͍͞͠%#ઃܭ
   @fist0
   2019.01.26 YAPC::Tokyo 2019
   

   View Slide

2. 2019.01.26
   :"1$5PLZP 2
   ࣗݾ঺հ
   )/;͌͢ͱ
   +PCαʔόαΠυΤϯδχΞ
   *%pTUpTUP
   ೥͸ͣͬͱHPMBOHॻ͍ͯͨ
   ೥͸4DBMBॻ͘ػӡ 4DBMB೥ੜ
   
   

   View Slide

3. 2019.01.26
   :"1$5PLZP 3
   ࢲͱ1FSM
   ͸͡Ίͯͷ:"1$͕:"1$"TJB5PLZP
   ৽ଔͰೖࣾͨ͠ͷ͕1FSMͷձࣾ
   ৽ଔݚमͰ1FSM
   ഑ଐ͞Εͯ࠷ॳͷϓϩμΫτ΋1FSM
   

   View Slide

4. 2019.01.26
   :"1$5PLZP
   αʔϏεӡ༻ͯ͠·͔͢ʁ
   

   View Slide

5. 2019.01.26
   :"1$5PLZP
   ϩάݟͯ·͔͢ʁ
   

   View Slide

6. 2019.01.26
   :"1$5PLZP 6
   ӡ༻ͱϩά
   ʁʁʁʮϩάʹ͸αʔϏεͷେ੾ͳ͜ͱ͕͢΂ͯ٧·ͬͯΔΜͩΑʯ
   ʁʁʁʮͰ΋ɺ΄ͱΜͲͷਓ͸ͦΕʹؾ͔ͮͳ͍Μͩʜʯ
   ਆ༷ɺ෹༷ɺϩά༷
   

   View Slide

7. 2019.01.26
   :"1$5PLZP
   ใԸँಙ
   

   View Slide

8. 2019.01.26
   :"1$5PLZP
   ϩά
   

   View Slide

9. 2019.01.26
   :"1$5PLZP 9
   ϩά
   ͍Ζ͍Ζ͋Δ
   ΞΫηεϩά
   )551αʔό FYOHJOY BQBDIF
   ͷϩά
   ΞϓϦϩά
   8FCΞϓϦέʔγϣϯ FY1FSM 1)1
   3VCZ
   ͷϩά
   %#ϩά
   %# FY.Z42- 1PTUHSF42-
   ͷϩά
   

   View Slide

10. 2019.01.26
    :"1$5PLZP 10
    ͳͥඞཁ͔ʁ
    αʔϏεʹԿ͕ى͖ͨͷ͔஌ΔͨΊ
    w σʔλ෼ੳ
    w ໰͍߹Θͤͷࣄ࣮֬ೝ
    w ো֐ͷݪҼڀ໌
    w ো֐ͷࣗಈݕ஌
    

    View Slide

11. 2019.01.26
    :"1$5PLZP 11
    ϩάͷཁ݅
    ੔߹ੑ
    ΞΫηεϩάʹ͸ϩά͕͋Δ͚ͲΞϓϦϩάʹϩά͕ͳ͍ͱ͔
    ໢ཏੑ
    8FCΞϓϦ಺ͷಛघͳ৚݅෼ذͰΞϓϦϩά͕࿙Εͯͨͱ͔
    ௥੻༰қੑ
    ΞΫηεϩάͱΞϓϦϩάΛඥ෇͚Δखஈ͕ͳ͘೔࣌Ͱඥ෇͚Δ͔͠ͳ͍ͱ͔
    ௐࠪͷͨΊʹΞΫηεϩάΛߴස౓Ͱݟʹߦ͔ͳ͍ͱ͍͚ͳ͍ͱ͔
    

    View Slide

12. 2019.01.26
    :"1$5PLZP
    'JO5FDI
    

    View Slide

13. 2019.01.26
    :"1$5PLZP 13
    ͱ͋ΔԾ૝௨՟ΞϓϦͷ։ൃ
    Ϣʔβͷࢿ࢈͸αʔόͰ؅ཧ͢Δߏ੒
    %#ʹอଘ͞ΕΔσʔλͷ੔߹ੑ͕࠷༏ઌ
    αʔϏε͕ࢭ·ΔҎ্ʹσʔλͷෆ੔߹͕ා͍
    Ճ͑ͯɺۚ༥γεςϜͱͯ͠ূ੻ΛٻΊΒΕΔ
    ϩά͸ͱͯ΋େࣄ
    

    View Slide

14. 2019.01.26
    :"1$5PLZP 14
    ͱ͋ΔԾ૝௨՟ΞϓϦͷαʔόαΠυ
    αʔόαΠυ͸("&(PΛબ୒͕ͯͬͭ͠ΓΫϥ΢υ
    4UBDL%SJWFSΛ࢖͑͹ϩάͷ੔߹ੑʹ͍ͭͯ͸͋·Γ৺഑ͳ͠
    ৺഑ͳͷ͸໢ཏੑͱ௥੻༰қੑ
    

    View Slide

15. 2019.01.26
    :"1$5PLZP
    %#ઃܭ
    

    View Slide

16. 2019.01.26
    :"1$5PLZP 16
    ߟ͑Δ͜ͱ
    ঢ়ଶΛ࣋ͬͨσʔλΛ%#ͰͲ͏ѻ͏͔
    ঢ়ଶͷมԽΛϩάʹͲ͏ه࿥͢Δ͔
    ྫɿϢʔβͷ೔ຊԁ࢒ߴɺຊਓ֬ೝεςʔλεɺϢʔβొ࿥৘ใ
    

    View Slide

17. 2019.01.26
    :"1$5PLZP 17
    ঢ়ଶͷมԽͱϩά
    Α͋͘ΔྫɿΞΧ΢ϯτͷ৐ͬऔΓʢෆਖ਼ϩάΠϯʴ*%1BTTมߋʣ
    Ϣʔβ৘ใͷมߋཤྺΛௐࠪ͠ͳ͚Ε͹ͳΒͳ͍
    Ϣʔβ৘ใͷมߋ͕Ͳ͔͜ͷϩάʹ࢒͍ͬͯͳ͚Ε͹ͳΒͳ͍
    

    View Slide

18. 2019.01.26
    :"1$5PLZP 18
    ։ൃݱ৔ͷ%#ઃܭ΍͍͖ͬͯ
    

    View Slide

19. 2019.01.26
    :"1$5PLZP 19
    Ϣʔβͷ೔ຊԁ࢒ߴ
    ೔ຊԁ࢒ߴ͸શϢʔβ͕ਓͭ࣋ͭ
    ࠷৽ͷԁ࢒ߴ͸Ϣʔβʹදࣔ͢Δ
    ೔ຊԁ࢒ߴ͸ೖۚɺग़ۚɺԾ૝௨՟ചങ͕ൃੜ͢ΔͱมԽ͢Δ
    

    View Slide

20. 2019.01.26
    :"1$5PLZP 20
    Ϣʔβͷ೔ຊԁ࢒ߴͷઃܭᶃ
    VTFS
    JE 1,
    
    VTFSOBNF
    
    VTFS@CBMBODF
    VTFS@JE 1,
    
    BNPVOU
    VQEBUFE@BU
    Ϣʔβͷ࢒ߴͷཤྺʢ࢒ߴͷਪҠʣΛ؅ཧը໘ʹද͍ࣔͨ͠ͱ͖
    ˠ࢒ߴมߋʹؔΘΔΤϯυϙΠϯτ΁ͷΞΫηεϩάΛࢀর͢Δʁ
    

    View Slide

21. 2019.01.26
    :"1$5PLZP 21
    Ϣʔβͷ೔ຊԁ࢒ߴͷઃܭᶄ
    VTFS
    JE 1,
    
    VTFSOBNF
    
    VTFS@CBMBODF
    JE 1,
    
    VTFS@JE
    BNPVOU
    DSFBUFE@BU
    ߋ৽࣌͸มԽޙͷ৽͍͠࢒ߴΛ*/4&35ͯ͠ɺ࠷৽ͷ࢒ߴΛऔΔͱ͖͸
    SELECT ~ WHERE user_id = hoge ORDER BY created_at LIMIT 1
    

    View Slide

22. 2019.01.26
    :"1$5PLZP 22
    Ϣʔβͷ೔ຊԁ࢒ߴͷઃܭᶄ
    աڈͷ࢒ߴͷϨίʔυ͕େྔʹͰ͖ͯύϑΥʔϚϯε͸΍΍ݒ೦͕ͩ
    ؅ཧը໘ʹඞཁͳσʔλ͸อଘͰ͖͍ͯΔͷͰѱ͘͸ͳͦ͞͏
    ଞ௨՟ͷ࢒ߴ΋VTFS@CBMBODFςʔϒϧͰ؅ཧ͢Δ͜ͱʹͳͬͨΒʁ
    

    View Slide

23. 2019.01.26
    :"1$5PLZP 23
    Ϣʔβͷ࢒ߴ
    ࢒ߴ͸શϢʔβ͕௨՟ʹ͖ͭਓͭ࣋ͭ
    ௨՟ͷछผ͸ɺ+1:ɺ#5$ɺ&5)ͳͲ
    ࠷৽ͷ֤࢒ߴ͸Ϣʔβʹදࣔ͢Δ
    ࢒ߴ͸ೖۚɺग़ۚɺԾ૝௨՟ചങ͕ൃੜ͢ΔͱมԽ͢Δ
    

    View Slide

24. 2019.01.26
    :"1$5PLZP 24
    Ϣʔβ࢒ߴͷઃܭᶃ
    VTFS
    JE 1,
    
    VTFSOBNF
    
    VTFS@CBMBODF
    VTFS@JE 1,
    
    KQZ@BNPVOU
    CUD@BNPVOU
    VQEBUFE@BU
    ߋ৽࣌͸৽͍͠࢒ߴͰ61%"5&
    ௨՟ͷछผ͕૿͑Δͨͼʹ"-5&3จΛଧͭͷ͔ʁʢଧͪͨ͘ͳ͍'ʣ
    

    View Slide

25. 2019.01.26
    :"1$5PLZP 25
    Ϣʔβ࢒ߴͷઃܭᶄ
    VTFS
    JE 1,
    
    VTFSOBNF
    
    VTFS@CBMBODF
    VTFS@JE 1,
    
    DVSSFODZ@JE
    BNPVOU
    VQEBUFE@BU
    ೔ຊԁ࢒ߴͷͱ͖ͱಉ༷ʹɺ
    Ϣʔβͷ࢒ߴͷཤྺʢ࢒ߴͷਪҠʣΛ؅ཧը໘ʹද͍ࣔͨ͠
    

    View Slide

26. 2019.01.26
    :"1$5PLZP 26
    Ϣʔβ࢒ߴͷઃܭᶅ
    VTFS
    JE 1,
    
    VTFSOBNF
    
    VTFS@CBMBODF
    JE 1,
    
    VTFS@JE
    DVSSFODZ@JE
    BNPVOU
    DSFBUFE@BU
    ߋ৽࣌͸มԽޙͷ৽͍͠࢒ߴΛ*/4&35
    Ͱ͸ɺϢʔβͷ࠷৽ͷ࢒ߴΛऔΔͱ͖͸ʜʁ
    

    View Slide

27. 2019.01.26
    :"1$5PLZP 27
    Ϣʔβ࢒ߴͷઃܭᶅ
    Ϣʔβͷ࠷৽ͷ࢒ߴΛ௨՟ࢦఆͰऔಘ͍ͨ͠ͱ͖
    SELECT ~ WHERE user_id = hoge AND currency_id = jpy
    ORDER BY created_at LIMIT 1
    Ϣʔβͷ࠷৽ͷ࢒ߴΛશ௨՟छผͰऔಘ͍ͨ͠ͱ͖
    ˠΫΤϦͰ͍͘ͳΒαϒΫΤϦ஍ࠈ
    ˠ্ͷΫΤϦΛ௨՟छผ͝ͱʹ࣮ߦ
    

    View Slide

28. 2019.01.26
    :"1$5PLZP 28
    Ϣʔβ࢒ߴͷઃܭᶆ
    VTFS
    JE 1,
    
    VTFSOBNF
    
    VTFS@CBMBODF
    VTFS@JE 1,
    
    DVSSFODZ@JE 1,
    
    BNPVOU
    VQEBUFE@BU
    VTFS@CBMBODFʹ61%"5&ͷΫΤϦΛ࣮ߦ͢Δͱ͖
    VTFS@CBMBODF@MPHʹ΄΅ಉ౳ͷϨίʔυΛ*/4&35͢Δ
    VTFS@CBMBODF@MPH
    JE 1,
    
    VTFS@JE
    DVSSFODZ@JE
    BNPVOU
    VQEBUFE@BU
    ෳ߹1,
    
    

    View Slide

29. 2019.01.26
    :"1$5PLZP 29
    Ϣʔβ࢒ߴͷઃܭᶆ
    Ϣʔβͷ࠷৽ͷ࢒ߴΛ௨՟ࢦఆͰऔಘ͍ͨ͠ͱ͖
    SELECT * FROM user_balance WHERE user_id = hoge AND
    currency_id = jpy
    Ϣʔβͷ࠷৽ͷ࢒ߴΛશ௨՟छผͰऔಘ͍ͨ͠ͱ͖
    SELECT * FROM user_balance WHERE user_id = hoge
    ؅ཧը໘ͰཤྺΛऔಘ͍ͨ͠ͱ͖ʢ௨՟ࢦఆʣ
    SELECT * FROM user_balance_log WHERE user_id = hoge AND
    currency_id = jpy
    

    View Slide

30. 2019.01.26
    :"1$5PLZP 30
    ϩάςʔϒϧͷ෼཭
    αʔϏεͰར༻͢ΔςʔϒϧͱϩάςʔϒϧΛ෼཭ͨ͠
    σʔλมߋͷཤྺΛ֬ೝ͢ΔͨΊʹΞΫηεϩάࢀর͸ͭΒ͍
    ΞΫηεϩά͸جຊͰ͔͍͠ݕࡧʹ΋͕͔͔࣌ؒΔ
    %#ϩάΛݟʹߦ͘ͷ΋ӡ༻తʹָͰ͸ͳ͍
    

    View Slide

31. 2019.01.26
    :"1$5PLZP 31
    ϩάςʔϒϧͷಋೖํ਑
    ϨίʔυΛߋ৽͢Δςʔϒϧ͸Ұ཯ϩάςʔϒϧΛ࡞Δํ਑ʹͨ͠
    ঢ়ଶͷมԽΛؚΊσʔλͷมߋ͕શ෦ςʔϒϧʹ࢒Δ
    ˠશ෦࢒Δɻ໢ཏੑͷ޲্
    ˠςʔϒϧʹ࢒ΔͷͰௐࠪ͠΍͍͢ɻ௥੻༰қੑͷ޲্
    

    View Slide

32. 2019.01.26
    :"1$5PLZP 32
    ϩάςʔϒϧͷઃܭ
    

    View Slide

33. 2019.01.26
    :"1$5PLZP 33
    ϝΠϯςʔϒϧͷΧϥϜ໊ʹॏෳ͠ͳ໋໊͍
    IPHF
    JE 1,
    
    IPHF
    VQEBUFE@BU
    IPHF@MPH
    MPH@JE 1,
    
    JE
    IPHF
    VQEBUFE@BU
    MPHHFE@BU
    ϩάςʔϒϧͷ໋໊نଇɺڞ௨ΧϥϜ໊͸౷ҰͰ͖ΔΑ͏ʹ
    

    View Slide

34. 2019.01.26
    :"1$5PLZP 34
    Ϩίʔυ࡟আ΁ͷରԠ
    IPHF
    JE 1,
    
    IPHF
    VQEBUFE@BU
    IPHF@MPH
    MPH@JE 1,
    
    JE
    IPHF
    VQEBUFE@BU
    MPHHFE@BU
    IPHFͷϨίʔυΛ࡟আͨ͠ͱ͖IPHF@MPHʹͲΜͳϨίʔυ͕ೖΕ͹Α͍͔
    

    View Slide

35. 2019.01.26
    :"1$5PLZP 35
    Ϩίʔυ࡟আϑϥάͷ௥Ճ
    IPHF
    JE 1,
    
    IPHF
    VQEBUFE@BU
    JT@EFMFUFE
    
    IPHF@MPH
    MPH@JE 1,
    
    JE
    IPHF
    VQEBUFE@BU
    MPHHFE@BU
    JT@EFMFUF@MPH
    ෺ཧ࡟আ૝ఆ͕ͩɺສ͕Ұͷ࿦ཧ࡟আʹ΋ରԠͰ͖ΔΑ͏ʹϑϥάΛ໋໊
    

    View Slide

36. 2019.01.26
    :"1$5PLZP 36
    ϩάςʔϒϧͷ࠷ऴઃܭ
    IPHF
    JE 1,
    
    IPHF
    VQEBUFE@BU
    IPHF@MPH
    MPH@JE 1,
    
    JE
    IPHF
    VQEBUFE@BU
    MPHHFE@BU
    JT@EFMFUF@MPH
    

    View Slide

37. 2019.01.26
    :"1$5PLZP 37
    ࠷ऴઃܭͷײ૝
    ϝϦοτ
    ΍Γ͍ͨ͜ͱʹରͯ͠ΫΤϦ͕ૉ௚ʹͳΔ
    มߋཤྺʹ͍ͭͯߟ͑ͳͯ͘Α͍
    ෺ཧ࡟আ࿦ཧ࡟আͰ೰·ͳͯ͘Α͍
    σϝϦοτ
    ࡞੒ߋ৽࡟আܥΫΤϦͰ࣮ߦΫΤϦ͕Ұͭ૿͑ΔʢཁτϥϯβΫγϣϯʣ
    ϩάςʔϒϧʹϨίʔυ͕ͲΜͲΜཷ·͍ͬͯ͘
    

    View Slide

38. 2019.01.26
    :"1$5PLZP 38
    ϩάςʔϒϧංେԽ໰୊
    ϩάςʔϒϧʹϨίʔυ͕ͲΜͲΜཷ·͍ͬͯ͘
    ˠ%#Λຊ൪༻ͱϩάςʔϒϧ༻Ͱ෼͚Δʁ
    ˠύʔςΟγϣχϯάͯ͠ఆظతʹຊ൪%#͔ΒऔΓग़͢ʁ
    

    View Slide

39. 2019.01.26
    :"1$5PLZP 39
    ϩάςʔϒϧ༻ʹ%#෼ׂ͢Δ͔ʁ
    ӡ༻Λߟ͑ͨΒ%#෼ׂͨ͘͠ͳΔ
    ͔͠͠ϩάςʔϒϧʹ͸τϥϯβΫγϣϯඞਢ
    ˠ෼ׂ͢Δͱ෼ࢄτϥϯβΫγϣϯ໰୊͕ݦࡏԽ
    4QBOOFSͷಉҰϓϩδΣΫτͰΠϯελϯε෼͚Δͷ͕Αͦ͞͏ʁ
    .Z42-ͳΒૉ௚ʹύʔςΟγϣχϯά͢Δͷ͕Αͦ͞͏
    

    View Slide

40. 2019.01.26
    :"1$5PLZP 40
    %BUBTUPSF΋ซ༻͍ͯ͘͠ελΠϧ
    ֎෦"1*͔ΒԾ૝௨՟Ձ֨Λऔಘͯ͠อଘ͢Δ
    औಘස౓͸ۃΊͯ୹͍ ʙඵʹճ
    ͷͰϨίʔυ਺΋๲େ
    .Z42-ͳΒ%#෼ׂ͢ΔPS%BUBTUPSF͕Αͦ͞͏
    

    View Slide

41. 2019.01.26
    :"1$5PLZP 41
    ࠓ೔఻͔͑ͨͬͨ͜ͱ
    

    View Slide

42. 2019.01.26
    :"1$5PLZP 42
    ࠓ೔఻͔͑ͨͬͨ͜ͱ
    ϩάςʔϒϧΛ༻ҙ͢ΔઃܭͲ͏ʁ
    ϩάʹ΍ͯ͘͞͠ӡ༻ͷ؍఺͔ΒݟΔͱྑ͍ͱߟ͍͑ͯΔ
    ݱঢ়ɺ͜ΕҎ্ͷઃܭΛࢥ͍͔ͭͳ͔ͬͨ
    ͜ͷ৔ΛआΓͯઃܭϨϏϡʔ͕͍ͨ͠
    Έͳ͞·ͷ͝ҙݟΛ͓଴͍ͪͯ͠·͢
    

    View Slide

43. 2019.01.26
    :"1$5PLZP
    5IBOLT
    

    View Slide