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

pixivにおけるMySQL運用の実際

19d76165a349908cca693668be1046a0?s=47 konoiz
February 23, 2018

 pixivにおけるMySQL運用の実際

MySQL Trend Seminar 2018 @ 福岡

19d76165a349908cca693668be1046a0?s=128

konoiz

February 23, 2018
Tweet

Transcript

  1. QJYJWʹ͓͚Δ.Z42-ӡ༻ͷ࣮ࡍ .Z42-5SFOE4FNJOBS!෱Ԭ ϐΫγϒגࣜձࣾΠϯϑϥ෦ࢁࠜ঵೭

  2. ࣗݾ঺հ

  3. ࣗݾ঺հ w :"."/&:PTIJZVLJLPOPJ[ w ೥ʹ৽ଔೖࣾɺΠϯϑϥνʔϜʹ഑ଐ w ීஈ͸౦ژΦϑΟεۈ຿

  4. BHFOEB w QJYJWʹ͍ͭͯ w QJYJWͷΠϯϑϥɺ.Z42-ߏ੒ w QJYJWͰͷ.Z42-ͷӡ༻ʹ͍ͭͯ w .BTUFSঢ֨ɺ"-5&3

  5. QJYJWʹ͍ͭͯ

  6. QJYJWʹ͍ͭͯ w ΫϦΤΠλʔͷͨΊͷ4/4 w ೥݄αʔϏε։࢝ w ొ࿥Ϣʔβ਺ ສਓҎ্ w ݄ؒͷ΂ར༻ऀ਺໿

    ສਓ w ૯౤ߘ਺໿ ສ࡞඼
  7. QJYJWʹ͍ͭͯ

  8. QJYJWͷΠϯϑϥʹ͍ͭͯ

  9. QJYJWͷΠϯϑϥʹ͍ͭͯ

  10. QJYJWͷΠϯϑϥʹ͍ͭͯ

  11. QJYJWͷΠϯϑϥʹ͍ͭͯ w ݱࡏ͸σʔληϯλͰӡ༻ w ڌ఺ϥοΫ w पลαʔϏεͱ߹Θͤͯαʔό໿୆ w ໿(CQT

  12. QJYJWͷΠϯϑϥʹ͍ͭͯ w νʔϜϝϯόʔਓ w αʔϏεͷ਺ΠϯϑϥνʔϜ w .Z42-αʔόͷӡ༻΋ਓ

  13. QJYJWͷΠϯϑϥʹ͍ͭͯ 'SPOU4FSWFS
 OHJOY -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- TMBWF *NBHF


    4UPSBHF ,74
 3FEJT /"5(BUFXBZ -PH4UPSBHF XSJUF JNHVQMPBE SFQMJDBUJPO SFBE
 SFR SFBE SFT MPH DBDIF ˞/"5HBUFXBZ͸*OUFSOFUʹ௚͍݁ͯ͠ͳ͍ αʔόʹରͯ͠ΠϯλʔωοτΞΫηεΛఏڙ *OUFSOFU $POWFSU $BDIF4FSWFS *NBHF'MVY "QQMJDBUJPO4FSWFS BQBDIF QIQ 4FBSDI4FSWFS
 4PMS TFBSDI JNQPSU *NBHF %JTQBUDIFS
 OHJOY
  14. QJYJWͷΠϯϑϥʹ͍ͭͯ 'SPOU4FSWFS
 OHJOY -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- TMBWF *NBHF


    4UPSBHF ,74
 3FEJT /"5(BUFXBZ -PH4UPSBHF XSJUF JNHVQMPBE SFQMJDBUJPO SFBE
 SFR SFBE SFT MPH DBDIF ˞/"5HBUFXBZ͸*OUFSOFUʹ௚͍݁ͯ͠ͳ͍ αʔόʹରͯ͠ΠϯλʔωοτΞΫηεΛఏڙ *OUFSOFU $POWFSU $BDIF4FSWFS *NBHF'MVY "QQMJDBUJPO4FSWFS BQBDIF QIQ 4FBSDI4FSWFS
 4PMS TFBSDI JNQPSU *NBHF %JTQBUDIFS
 OHJOY
  15. QJYJWͷ.Z42-

  16. QJYJWͷ.Z42- w ༻్ผʹܥ౷ͷ.Z42-σʔλϕʔε w .Z42-ࣗମ͸͘͝ී௨ͷ.BTUFS4MBWFߏ੒ w SFBE͸-JOVY7JSUVBM4FSWFS -74 Ͱ෼ࢄ w

    ։ൃ΍όοΫΞοϓ༻ʹ.Z42-4BOECPYΛ࢖༻
  17. ܥ౷ͷ.Z42- w Ϣʔβؔ࿈σʔλɺΠϥετؔ࿈σʔλɺܾࡁؔ࿈σʔλʜͱ͍ͬ ͨܗͰਨ௚෼ׂ w ݪଇͱ֤ͯ͠ܥ౷୆͸4MBWF͕͍Δ w ߹ܭ.BTUFS୆ɺ4MBWF୆ w ࠷΋େ͖ͳߏ੒ͷܥ౷͸.BTUFS୆ʹରͯ͠4MBWF୆

  18. -74 w -JOVY7JSUVBM4FSWFS w ܥ౷ຖʹTMBWF༻ͷԾ૝*1Λ༻ҙ w BQQ͕Ծ૝*1ʹରͯ͠ࢀরϦΫΤετ w -74͕όϥϯγϯάͯ͠TMBWFʹ࠶ૹ w

    TMBWF͸BQQʹରͯ͠௚઀Ԡ౴ %43 -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- TMBWF XSJUF SFQMJDBUJPO SFBE
 SFR SFBE SFT "QQMJDBUJPO4FSWFS BQBDIF QIQ
  19. .Z42-4BOECPY w αʔόͰෳ਺ͷ.Z42-Λ؆୯ʹىಈͰ͖Δ w ։ൃ؀ڥ΍όοΫΞοϓ༻ͷ.Z42-ʹ࢖༻ w 4BOECPYͰ࡞ͬͨTMBWFͷEBUBσΟϨΫτϦΛίϐʔͯ͠όοΫ ΞοϓΛ࡞੒ w ։ൃ؀ڥ΋ಉ༷ͷख๏Ͱຖ೔ຊ൪σʔλͱಉظ͠ͳ͓͢

  20. .Z42-ͷӡ༻ʹ͍ͭͯ

  21. લఏ w QJYJWͰ͸ݪଇϝϯςφϯεϞʔυʹ͠ͳ͍ w ਺݅ͷΤϥʔ͸ڐ༰͠ɺαʔϏεແఀࢭͰϝϯςφϯε͢Δ w .Z42-4MBWFΛ.BTUFSʹঢ֨ͤ͞Δࡍ΋ಉ༷

  22. .BTUFSঢ֨

  23. .BTUFSঢ֨ w QJYJWͷ.Z42-.BTUFS͸ܥ౷͝ͱʹԾ૝*1Λ͍࣋ͬͯΔ w Ծ૝*1Λ4MBWFʹ෇͚ସ͑ΔࣄͰ.BTUFSঢ֨Λॠؒతʹ࣮ࢪ͢Δ w ྗٕӡ༻

  24. .BTUFSঢ֨ -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- TMBWF XSJUF SFQMJDBUJPO SFBE

    "QQMJDBUJPO4FSWFS BQBDIF QIQ .Z42- TMBWF     Ծ૝*1 ࣮*1
  25. .BTUFSঢ֨ -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- TMBWF XSJUF SFQMJDBUJPO "QQMJDBUJPO4FSWFS

    BQBDIF QIQ .Z42- TMBWF     Ծ૝*1 ࣮*1 SFBE LJMMNZTRME
  26. .BTUFSঢ֨ -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- TMBWF XSJUF "QQMJDBUJPO4FSWFS BQBDIF

    QIQ .Z42- TMBWF     Ծ૝*1 ࣮*1 SFBE
  27. .BTUFSঢ֨ -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- TMBWF XSJUF "QQMJDBUJPO4FSWFS BQBDIF

    QIQ .Z42- TMBWF     Ծ૝*1 ࣮*1 SFBE
  28. .BTUFSঢ֨ -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- /FX.BTUFS XSJUF "QQMJDBUJPO4FSWFS BQBDIF

    QIQ .Z42- TMBWF     Ծ૝*1 ࣮*1 SFBE
  29. .BTUFSঢ֨ -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- /FX.BTUFS XSJUF "QQMJDBUJPO4FSWFS BQBDIF

    QIQ .Z42- TMBWF     Ծ૝*1 ࣮*1 SFBE
  30. .BTUFSঢ֨ -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- /FX.BTUFS XSJUF "QQMJDBUJPO4FSWFS BQBDIF

    QIQ .Z42- TMBWF     $)"/(&."45&350 ."45&3@)045 Ծ૝*1 ࣮*1 SFBE
  31. .BTUFSঢ֨ -PBE#BMBODFS
 -74 .Z42- NBTUFS .Z42- /FX.BTUFS XSJUF "QQMJDBUJPO4FSWFS BQBDIF

    QIQ .Z42- TMBWF     SFQMJDBUJPO Ծ૝*1 ࣮*1 SFBE
  32. .BTUFSঢ֨ w ܭըతʹ࣮ߦ͢Δ৔߹͸֓Ͷඵఔ౓Ͱ.BTUFSঢ֨Λ׬ྃͤ͞Δ w TMBWFಉ࢜ͰQPTJUJPO͕ҟͳΔ৔߹͸࠷΋৽͍͠΋ͷ·ͨ͸ଟ਺ܾͰ ଟ͍΋ͷΛਖ਼ͱͯ͠.BTUFSʹ͢Δ w ݩ.BTUFS͸ࢮΜͰ͍ΔͷͰ࠶ηοτΞοϓͯ͠4MBWFͱͯ͠໭͢ w ো֐Ͱ.BTUFS͕ࢮ๢ͨ͠৔߹΋खॱ͸΄΅ಉ͡

  33. .BTUFSঢ֨ͷλΠϛϯά w %#ͷ࠶ىಈ͕ඞཁͳ࣌ w .Z42-ͷόʔδϣϯΞοϓ w ΦϯϥΠϯมߋෆՄͳઃఆͷมߋ࣌ w αʔόҠసɾ෺ཧαʔόͷϝϯςφϯε࣌ w

    8SJUFʹӨڹ͕͋Δέʔε w େن໛ͳςʔϒϧͷ"-5&3 w .BTUFSো֐ൃੜ࣌
  34. "-5&3

  35. CPPLNBSLUBCMF w QJYJWͷػೳͷҰͭͰ͋ΔΠϥετͷʮϒοΫϚʔΫʯΛอଘ͢Δ UBCMF w QJYJWͷ%#ͷதͰ΋ಛʹϨίʔυ਺ͷଟ͍ςʔϒϧ w ໿(#ͷσʔλΛΦϯϝϞϦͰॲཧ w ౥ࡌϝϞϦ(#ͷαʔό

  36. CPPLNBSLͷ"-5&3 w ೥Πϯϑϥ഑ଐޙ࠷ॳͷ࢓ࣄ w ౰࣌ͷ#PPLNBSLUBCMF͸.Z*4".Ͱ.Z42-ɺ໿ԯϨίʔυ w ͜ΕΛ*OOP%#ɺ.Z42-ʹมߋ w ී௨ʹ"-5&35"#&-ͨ͠৔߹ɺҰिؒఔ౓͔͔Δ

  37. CPPLNBSLͷ"-5&3 w "-5&3Λ࢖Θͳ͍ w ฒྻ*/4&354&-&$5

  38. ฒྻ*/4&354&-&$5 w ִ཭͞ΕͨTMBWF؀ڥΛ༻ҙ w "-5&3ޙͷঢ়ଶͷςʔϒϧΛ࡞੒ w "-5&3લͷςʔϒϧΛ4&-&$5ͯ͠CVML*/4&35 w ͨͩ͠*%ͰൣғΛ੾ͬͯɺฒྻ࣮ߦ͢Δ

  39. *%CJHJOU ฒྻ*/4&354&-&$5 *%JOU  ʜ   ʜ  

    ʜ   ʜ   ʜ HSPVQ" HSPVQ# PVQ$
  40. *%CJHJOU ฒྻ*/4&354&-&$5 *%JOU  ʜ   ʜ  

    ʜ   ʜ   ʜ HSPVQ" HSPVQ# PVQ$ 4&-&$5 4&-&$5 4&-&$5
  41. *%CJHJOU  ʜ   ʜ   ʜ ฒྻ*/4&354&-&$5

    *%JOU  ʜ   ʜ   ʜ   ʜ   ʜ HSPVQ" HSPVQ# PVQ$ 4&-&$5 4&-&$5 4&-&$5 */4&35 */4&35 */4&35
  42. *%CJHJOU  ʜ   ʜ   ʜ ฒྻ*/4&354&-&$5

    *%JOU  ʜ   ʜ   ʜ   ʜ   ʜ HSPVQ" HSPVQ# PVQ$ 4&-&$5 4&-&$5
  43. *%CJHJOU  ʜ   ʜ   ʜ 

     ʜ   ʜ ฒྻ*/4&354&-&$5 *%JOU  ʜ   ʜ   ʜ   ʜ   ʜ HSPVQ" HSPVQ# PVQ$ 4&-&$5 4&-&$5 */4&35 */4&35
  44. ฒྻ*/4&354&-&$5 w ฒྻ࣮ߦ͢Δͱ΄΅׬શʹ*0ϘτϧωοΫʹͳΔ w ଎౓͸σΟεΫੑೳ࣍ୈ w ͜ͷ࣌͸໿೔൒Ͱ׬ྃ

  45. CPPLNBSLUBCMF

  46. w ΋͏௚͙*/5ܕͷݶքͷԯʹୡ͢Δ w ͜͜਺೥Ͱ૿Ճϖʔε͸Ճ଎܏޲ w ૣٸʹ*/5Λ#*(*/5ʹ͢Δඞཁ͕͋Δ CPPLNBSLUBCMF

  47. w ೥લͱಉ༷ʹฒྻ*/4&54&-&$5Λ࣮ߦ */5UP#*(*/5

  48. */5UP#*(*/5 TUBSU FOE

  49. w ೥લͱಉ༷ʹฒྻ*/4&54&-&$5Λ࣮ߦ w ೔͔Β։࢝ͯ͠ࠓே׬ྃ w ೔ؒ */5UP#*(*/5

  50. ฒྻ*/4&354&-&$5 w "-5&3ͱൺֱͨ͠ϝϦοτ w ௨ৗͷ"-5&3ΑΓߴ଎ʹॲཧͰ͖ΔՄೳੑ͕͋Δ w ࿦ཧ࡟আͷϨίʔυΛ෺ཧ࡟আ͢Δͱ͍ͬͨࣄ͕ಉ࣌ʹͰ͖Δ

  51. ·ͱΊ w QJYJWͰͷ.Z42-ʹ͍ͭͯ঺հ͠·ͨ͠ w QJYJWͷ৔߹ɺجຊతʹ͸γϯϓϧͳ.BTUFS4MBWFߏ੒Ͱɺ
 ͋Δఔ౓ͷن໛·Ͱ͸໰୊ͳ͘ӡ༻Ͱ͖͍ͯ·͢

  52. QJYJWͰ͸.Z42-αʔόͷӡ༻ɾվળ ͍ͨ͠ΠϯϑϥΤϯδχΞΛืू͍ͯ͠·͢ʂ XXXQJYJWDPKQ