YAPC::Tokyo 2019 Log Friendly DB Design

ログにやさしいDB設計

Transcript

1. ϩάʹ΍͍͞͠%#ઃܭ @fist0 2019.01.26 YAPC::Tokyo 2019
2. 2019.01.26 :"1$5PLZP
    2 ࣗݾ঺հ )/
   ;͌͢ͱ
   +PC
   αʔόαΠυΤϯδχΞ
   *%
   
   
   
   
   
   
   
   pTU
   
   
   
   
   
   
   
   
   pTUP
   ೥͸ͣͬͱHPMBOHॻ͍ͯͨ
   ೥͸4DBMBॻ͘ػӡ
   4DBMB
   ೥ੜ
3. 2019.01.26 :"1$5PLZP
    3 ࢲͱ1FSM ͸͡Ίͯͷ
   :"1$
   ͕
   :"1$"TJB
   5PLZP
   
   ৽ଔͰೖࣾͨ͠ͷ͕1FSMͷձࣾ
   
   ৽ଔݚमͰ1FSM
   
   ഑ଐ͞Εͯ࠷ॳͷϓϩμΫτ΋1FSM
4. 2019.01.26 :"1$5PLZP
    αʔϏεӡ༻ͯ͠·͔͢ʁ
5. 2019.01.26 :"1$5PLZP
    ϩάݟͯ·͔͢ʁ
6. 2019.01.26 :"1$5PLZP
    6 ӡ༻ͱϩά ʁʁʁʮϩάʹ͸αʔϏεͷେ੾ͳ͜ͱ͕͢΂ͯ٧·ͬͯΔΜͩΑʯ
   ʁʁʁʮͰ΋ɺ΄ͱΜͲͷਓ͸ͦΕʹؾ͔ͮͳ͍Μͩʜʯ
   ਆ༷ɺ෹༷ɺϩά༷
   
7. 2019.01.26 :"1$5PLZP
    ใԸँಙ
8. 2019.01.26 :"1$5PLZP
    ϩά
9. 2019.01.26 :"1$5PLZP
    9 ϩά ͍Ζ͍Ζ͋Δ
   ΞΫηεϩά
   )551αʔό FY
   OHJOY
   BQBDIF
   ͷϩά
   ΞϓϦϩά
   8FCΞϓϦέʔγϣϯ FY
   1FSM
   1)1
   3VCZ
   ͷϩά
   %#ϩά
   %# FY
   .Z42-
   1PTUHSF42-
   ͷϩά
10. 2019.01.26 :"1$5PLZP
     10 ͳͥඞཁ͔ʁ αʔϏεʹԿ͕ى͖ͨͷ͔஌ΔͨΊ
    w σʔλ෼ੳ
    w ໰͍߹Θͤͷࣄ࣮֬ೝ
    w ো֐ͷݪҼڀ໌
    w ো֐ͷࣗಈݕ஌
11. 2019.01.26 :"1$5PLZP
     11 ϩάͷཁ݅ ੔߹ੑ
    
    ΞΫηεϩάʹ͸ϩά͕͋Δ͚ͲΞϓϦϩάʹϩά͕ͳ͍ͱ͔
    ໢ཏੑ
    
    8FCΞϓϦ಺ͷಛघͳ৚݅෼ذͰΞϓϦϩά͕࿙Εͯͨͱ͔
    ௥੻༰қੑ
    
    ΞΫηεϩάͱΞϓϦϩάΛඥ෇͚Δखஈ͕ͳ͘೔࣌Ͱඥ෇͚Δ͔͠ͳ͍ͱ͔
    
    ௐࠪͷͨΊʹΞΫηεϩάΛߴස౓Ͱݟʹߦ͔ͳ͍ͱ͍͚ͳ͍ͱ͔
12. 2019.01.26 :"1$5PLZP
     'JO5FDI
13. 2019.01.26 :"1$5PLZP
     13 ͱ͋ΔԾ૝௨՟ΞϓϦͷ։ൃ Ϣʔβͷࢿ࢈͸αʔόͰ؅ཧ͢Δߏ੒
    %#ʹอଘ͞ΕΔσʔλͷ੔߹ੑ͕࠷༏ઌ
    αʔϏε͕ࢭ·ΔҎ্ʹσʔλͷෆ੔߹͕ා͍
    Ճ͑ͯɺۚ༥γεςϜͱͯ͠ূ੻ΛٻΊΒΕΔ
    ϩά͸ͱͯ΋େࣄ
14. 2019.01.26 :"1$5PLZP
     14 ͱ͋ΔԾ૝௨՟ΞϓϦͷαʔόαΠυ αʔόαΠυ͸("&(PΛબ୒͕ͯͬͭ͠ΓΫϥ΢υ
    4UBDL
    %SJWFS
    Λ࢖͑͹ϩάͷ੔߹ੑʹ͍ͭͯ͸͋·Γ৺഑ͳ͠
    ৺഑ͳͷ͸໢ཏੑͱ௥੻༰қੑ
15. 2019.01.26 :"1$5PLZP
     %#ઃܭ
16. 2019.01.26 :"1$5PLZP
     16 ߟ͑Δ͜ͱ
    ঢ়ଶΛ࣋ͬͨσʔλΛ%#ͰͲ͏ѻ͏͔
    
    ঢ়ଶͷมԽΛϩάʹͲ͏ه࿥͢Δ͔
    ྫɿϢʔβͷ೔ຊԁ࢒ߴɺຊਓ֬ೝεςʔλεɺϢʔβొ࿥৘ใ
17. 2019.01.26 :"1$5PLZP
     17 ঢ়ଶͷมԽͱϩά Α͋͘ΔྫɿΞΧ΢ϯτͷ৐ͬऔΓʢෆਖ਼ϩάΠϯʴ*%1BTTมߋʣ
    Ϣʔβ৘ใͷมߋཤྺΛௐࠪ͠ͳ͚Ε͹ͳΒͳ͍
    Ϣʔβ৘ใͷมߋ͕Ͳ͔͜ͷϩάʹ࢒͍ͬͯͳ͚Ε͹ͳΒͳ͍
18. 2019.01.26 :"1$5PLZP
     18 ։ൃݱ৔ͷ%#ઃܭ΍͍͖ͬͯ
19. 2019.01.26 :"1$5PLZP
     19 Ϣʔβͷ೔ຊԁ࢒ߴ ೔ຊԁ࢒ߴ͸શϢʔβ͕ਓͭ࣋ͭ
    ࠷৽ͷԁ࢒ߴ͸Ϣʔβʹදࣔ͢Δ
    ೔ຊԁ࢒ߴ͸ೖۚɺग़ۚɺԾ૝௨՟ചങ͕ൃੜ͢ΔͱมԽ͢Δ
20. 2019.01.26 :"1$5PLZP
     20 Ϣʔβͷ೔ຊԁ࢒ߴͷઃܭᶃ VTFS JE 1,
    VTFSOBNF
    
    VTFS@CBMBODF VTFS@JE 1,
    BNPVOU
    VQEBUFE@BU Ϣʔβͷ࢒ߴͷཤྺʢ࢒ߴͷਪҠʣΛ؅ཧը໘ʹද͍ࣔͨ͠ͱ͖
    ˠ
    ࢒ߴมߋʹؔΘΔΤϯυϙΠϯτ΁ͷΞΫηεϩάΛࢀর͢Δʁ
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
22. 2019.01.26 :"1$5PLZP
     22 Ϣʔβͷ೔ຊԁ࢒ߴͷઃܭᶄ աڈͷ࢒ߴͷϨίʔυ͕େྔʹͰ͖ͯύϑΥʔϚϯε͸΍΍ݒ೦͕ͩ
    ؅ཧը໘ʹඞཁͳσʔλ͸อଘͰ͖͍ͯΔͷͰѱ͘͸ͳͦ͞͏
    ଞ௨՟ͷ࢒ߴ΋VTFS@CBMBODFςʔϒϧͰ؅ཧ͢Δ͜ͱʹͳͬͨΒʁ
23. 2019.01.26 :"1$5PLZP
     23 Ϣʔβͷ࢒ߴ ࢒ߴ͸શϢʔβ͕௨՟ʹ͖ͭਓͭ࣋ͭ
    ௨՟ͷछผ͸ɺ+1:ɺ#5$ɺ&5)ͳͲ
    ࠷৽ͷ֤࢒ߴ͸Ϣʔβʹදࣔ͢Δ
    ࢒ߴ͸ೖۚɺग़ۚɺԾ૝௨՟ചങ͕ൃੜ͢ΔͱมԽ͢Δ
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
    จΛଧͭͷ͔ʁʢଧͪͨ͘ͳ͍'ʣ
25. 2019.01.26 :"1$5PLZP
     25 Ϣʔβ࢒ߴͷઃܭᶄ VTFS JE 1,
    VTFSOBNF
    
    VTFS@CBMBODF VTFS@JE 1,
    DVSSFODZ@JE
    BNPVOU
    VQEBUFE@BU ೔ຊԁ࢒ߴͷͱ͖ͱಉ༷ʹɺ
    Ϣʔβͷ࢒ߴͷཤྺʢ࢒ߴͷਪҠʣΛ؅ཧը໘ʹද͍ࣔͨ͠
26. 2019.01.26 :"1$5PLZP
     26 Ϣʔβ࢒ߴͷઃܭᶅ VTFS JE 1,
    VTFSOBNF
    
    VTFS@CBMBODF JE 1,
    VTFS@JE
    DVSSFODZ@JE
    BNPVOU
    DSFBUFE@BU ߋ৽࣌͸มԽޙͷ৽͍͠࢒ߴΛ*/4&35
    Ͱ͸ɺϢʔβͷ࠷৽ͷ࢒ߴΛऔΔͱ͖͸ʜʁ
27. 2019.01.26 :"1$5PLZP
     27 Ϣʔβ࢒ߴͷઃܭᶅ Ϣʔβͷ࠷৽ͷ࢒ߴΛ௨՟ࢦఆͰऔಘ͍ͨ͠ͱ͖
    SELECT ~ WHERE user_id = hoge AND currency_id = jpy ORDER BY created_at LIMIT 1 Ϣʔβͷ࠷৽ͷ࢒ߴΛશ௨՟छผͰऔಘ͍ͨ͠ͱ͖
    ˠΫΤϦͰ͍͘ͳΒαϒΫΤϦ஍ࠈ
    ˠ্ͷΫΤϦΛ௨՟छผ͝ͱʹ࣮ߦ
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,
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
30. 2019.01.26 :"1$5PLZP
     30 ϩάςʔϒϧͷ෼཭ αʔϏεͰར༻͢ΔςʔϒϧͱϩάςʔϒϧΛ෼཭ͨ͠
    
    σʔλมߋͷཤྺΛ֬ೝ͢ΔͨΊʹΞΫηεϩάࢀর͸ͭΒ͍
    
    ΞΫηεϩά͸جຊͰ͔͍͠ݕࡧʹ΋͕͔͔࣌ؒΔ
    
    %#ϩάΛݟʹߦ͘ͷ΋ӡ༻తʹָͰ͸ͳ͍
31. 2019.01.26 :"1$5PLZP
     31 ϩάςʔϒϧͷಋೖํ਑ ϨίʔυΛߋ৽͢Δςʔϒϧ͸Ұ཯ϩάςʔϒϧΛ࡞Δํ਑ʹͨ͠
    ঢ়ଶͷมԽΛؚΊσʔλͷมߋ͕શ෦ςʔϒϧʹ࢒Δ
    ˠ
    શ෦࢒Δɻ໢ཏੑͷ޲্
    ˠ
    ςʔϒϧʹ࢒ΔͷͰௐࠪ͠΍͍͢ɻ௥੻༰қੑͷ޲্
32. 2019.01.26 :"1$5PLZP
     32 ϩάςʔϒϧͷઃܭ
33. 2019.01.26 :"1$5PLZP
     33 ϝΠϯςʔϒϧͷΧϥϜ໊ʹॏෳ͠ͳ໋໊͍ IPHF JE 1,
    IPHF
    VQEBUFE@BU IPHF@MPH MPH@JE 1,
    JE
    IPHF
    VQEBUFE@BU
    MPHHFE@BU ϩάςʔϒϧͷ໋໊نଇɺڞ௨ΧϥϜ໊͸౷ҰͰ͖ΔΑ͏ʹ
    
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ʹͲΜͳϨίʔυ͕ೖΕ͹Α͍͔
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 ෺ཧ࡟আ૝ఆ͕ͩɺສ͕Ұͷ࿦ཧ࡟আʹ΋ରԠͰ͖ΔΑ͏ʹϑϥάΛ໋໊
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
37. 2019.01.26 :"1$5PLZP
     37 ࠷ऴઃܭͷײ૝
    ϝϦοτ
    ΍Γ͍ͨ͜ͱʹରͯ͠ΫΤϦ͕ૉ௚ʹͳΔ
    มߋཤྺʹ͍ͭͯߟ͑ͳͯ͘Α͍
    ෺ཧ࡟আ࿦ཧ࡟আͰ೰·ͳͯ͘Α͍
    
    σϝϦοτ
    ࡞੒ߋ৽࡟আܥΫΤϦͰ࣮ߦΫΤϦ͕Ұͭ૿͑ΔʢཁτϥϯβΫγϣϯʣ
    ϩάςʔϒϧʹϨίʔυ͕ͲΜͲΜཷ·͍ͬͯ͘
38. 2019.01.26 :"1$5PLZP
     38 ϩάςʔϒϧංେԽ໰୊ ϩάςʔϒϧʹϨίʔυ͕ͲΜͲΜཷ·͍ͬͯ͘
    ˠ
    %#Λຊ൪༻ͱϩάςʔϒϧ༻Ͱ෼͚Δʁ
    ˠ
    ύʔςΟγϣχϯάͯ͠ఆظతʹຊ൪%#͔ΒऔΓग़͢ʁ
39. 2019.01.26 :"1$5PLZP
     39 ϩάςʔϒϧ༻ʹ%#෼ׂ͢Δ͔ʁ ӡ༻Λߟ͑ͨΒ%#෼ׂͨ͘͠ͳΔ
    ͔͠͠ϩάςʔϒϧʹ͸τϥϯβΫγϣϯඞਢ
    ˠ
    ෼ׂ͢Δͱ෼ࢄτϥϯβΫγϣϯ໰୊͕ݦࡏԽ
    4QBOOFS
    ͷಉҰϓϩδΣΫτͰΠϯελϯε෼͚Δͷ͕Αͦ͞͏ʁ
    .Z42-
    ͳΒૉ௚ʹύʔςΟγϣχϯά͢Δͷ͕Αͦ͞͏
40. 2019.01.26 :"1$5PLZP
     40 %BUBTUPSF
    ΋ซ༻͍ͯ͘͠ελΠϧ ֎෦"1*͔ΒԾ૝௨՟Ձ֨Λऔಘͯ͠อଘ͢Δ
    औಘස౓͸ۃΊͯ୹͍ ʙඵʹճ ͷͰϨίʔυ਺΋๲େ
    .Z42-ͳΒ
    %#෼ׂ͢Δ
    PS
    %BUBTUPSF
    ͕Αͦ͞͏
41. 2019.01.26 :"1$5PLZP
     41 ࠓ೔఻͔͑ͨͬͨ͜ͱ
42. 2019.01.26 :"1$5PLZP
     42 ࠓ೔఻͔͑ͨͬͨ͜ͱ ϩάςʔϒϧΛ༻ҙ͢ΔઃܭͲ͏ʁ
    ϩάʹ΍ͯ͘͞͠ӡ༻ͷ؍఺͔ΒݟΔͱྑ͍ͱߟ͍͑ͯΔ
    ݱঢ়ɺ͜ΕҎ্ͷઃܭΛࢥ͍͔ͭͳ͔ͬͨ
    ͜ͷ৔ΛआΓͯઃܭϨϏϡʔ͕͍ͨ͠
    Έͳ͞·ͷ͝ҙݟΛ͓଴͍ͪͯ͠·͢
43. 2019.01.26 :"1$5PLZP
     5IBOLT