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

2大OSSデータベースのMySQLとPostgreSQLの違い(後半)

 2大OSSデータベースのMySQLとPostgreSQLの違い(後半)

第32回 PostgreSQL 勉強会(2015年10月10日)の資料です。
https://www.postgresql.jp/wg/shikumi/pgstudy_32/view

soudai sone

April 02, 2016
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. 2େOSSσʔλϕʔεͷ
    MySQLͱPostgreSQLͷҧ͍
    (ޙ൒)
    ୈ32ճ PostgreSQL ษڧձ

    View full-size slide

  2. What is it?
    ͜ͷεϥΠυͷ
    గਖ਼Օॴ
    ΛؚΊͨϒϩά͸ͪ͜Β
    IUUQTPVEBJCMPHTQPUKQPTTNZTRMQPTUHSFTRMIUNM

    View full-size slide

  3. What is it?
    લ൒ઓΛऴ͑ͯ

    View full-size slide

  4. What is it?
    લ൒ઓΛऴ͑ͯ
    ˣ
    .Z42-ͱ1PTUHSF42-͕ҧ͏ͷ͸Θ͔ͬͨ

    View full-size slide

  5. What is it?
    લ൒ઓΛऴ͑ͯ
    ˣ
    .Z42-ͱ1PTUHSF42-͕ҧ͏ͷ͸Θ͔ͬͨ
    ˣ
    ΠϯυͱΠϯυωγΞ͘Β͍ҧ͏

    View full-size slide

  6. What is it?
    લ൒ઓΛऴ͑ͯ
    ˣ
    .Z42-ͱ1PTUHSF42-͕ҧ͏ͷ͸Θ͔ͬͨ
    ˣ
    +BWBͱ+BWB4DSJQU͘Β͍ҧ͏

    View full-size slide

  7. What is it?
    લ൒ઓΛऴ͑ͯ
    ˣ
    .Z42-ͱ1PTUHSF42-͕ҧ͏ͷ͸Θ͔ͬͨ
    ˣ
    +BWBͱ+BWB4DSJQU͘Β͍ҧ͏
    ྨࣅྫҰཡ
    ΠϯυͱΠϯυωγΞҧ͏
    ,0'ͷژͱ,0'ͷژ͘Β͍ҧ͏
    ετͷϦϡ΢ͱέϯ͘Β͍ҧ͏
    ए೫Ֆͱو೫Ֆ͘Β͍ҧ͏
    ϙέϞϯͱ༯ո΢Υον͘Β͍ҧ͏
    લా݈ଠͱલాஐಙ͘Β͍ҧ͏
    ʜͳͲ

    View full-size slide

  8. What is it?
    ͭ·Γ͸ಉ͡3%#Ͱ΋݁ߏҧ͏

    View full-size slide

  9. What is it?
    Ͱ΋ػೳͷҧ͍͚ͩͰ͸ઓྗͷҧ͍ʹͳΒͳ͍

    View full-size slide

  10. What is it?
    Ћͷ෦෼Λ͝঺հ͠·͢

    View full-size slide

  11. What is it?
    લఏ৚݅
    લ൒ઓͷ࿩Λௌ͍ͯΔ͜ͱ·ͨ͸ݟͯΔ͜ͱ

    View full-size slide

  12. What is it?
    લఏ৚݅
    ଓɾ1PTUHSF42-ŧŔŕŪƂŜſ

    View full-size slide

  13. What is it?
    લఏ৚݅
    ଓɾ1PTUHSF42-ŧŔŕŪƂŜſ
    ˣ
    ࠷৽൛ͱ͔1PTUHSF42-ͷ᠘ͷ࿩͸ͳ͍Ͱ͢

    View full-size slide

  14. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹίϛϡχςΟͷҧ͍
    ̏ɹϋϚϦͲ͜ΖͷTips
    ̐ɹMySQL 5.7ͱPostgreSQL 9.5
    ̑ɹ·ͱΊ

    View full-size slide

  15. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹίϛϡχςΟͷҧ͍
    ̏ɹϋϚϦͲ͜ΖͷTips
    ̐ɹMySQL 5.7ͱPostgreSQL 9.5
    ̑ɹ·ͱΊ

    View full-size slide

  16. ࣗݾ঺հ
    ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ
    ೥ྸɿ̏̌ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ
    ৬ۀɿWebΤϯδχΞ
    ॴଐɿ೔ຊPostgreSQLϢʔβձ
    ɹɹɹதࠃࢧ෦ ࢧ෦௕
    ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

    View full-size slide

  17. ࣗݾ঺հ
    ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ
    ೥ྸɿ̏̌ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ
    ৬ۀɿWebΤϯδχΞ
    ॴଐɿ೔ຊPostgreSQLϢʔβձ
    ɹɹɹதࠃࢧ෦ ࢧ෦௕
    ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

    View full-size slide

  18. தࠃ஍ํDBษڧձ
    https://dbstudychugoku.github.io/

    View full-size slide

  19. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹίϛϡχςΟͷҧ͍
    ̏ɹϋϚϦͲ͜ΖͷTips
    ̐ɹMySQL 5.7ͱPostgreSQL 9.5
    ̑ɹ·ͱΊ

    View full-size slide

  20. ίϛϡχςΟͷҧ͍
    ߲໨ίϛϡχςΟ໊ .Z/" +16(
    ࢀՃํ๏ .-ͷొ࿥ .-ʹొ࿥ਖ਼ձһొ࿥
    ૊৫छྨ ༗ࢤʹΑΔϘϥϯςΟΞ /10๏ਓ
    ӡӦࢿۚ ɹແ͠ ༗Γ
    ίϛϡχέʔγϣϯ
    πʔϧ
    .-ɾTMBDL .-ɾTMBDL

    View full-size slide

  21. ίϛϡχςΟͷҧ͍
    ߲໨ίϛϡχςΟ໊ .Z/" +16(
    ࢀՃํ๏ .-ͷొ࿥ .-ʹొ࿥ਖ਼ձһొ࿥
    ૊৫छྨ ༗ࢤʹΑΔϘϥϯςΟΞ /10๏ਓ
    ӡӦࢿۚ ɹແ͠ ༗Γ
    ίϛϡχέʔγϣϯ
    πʔϧ
    .-ɾTMBDL .-ɾTMBDL

    View full-size slide

  22. ίϛϡχςΟͷҧ͍
    ߲໨ίϛϡχςΟ໊ .Z/" +16(
    ࢀՃํ๏ .-ͷొ࿥ .-ʹొ࿥ਖ਼ձһొ࿥
    ૊৫छྨ ༗ࢤʹΑΔϘϥϯςΟΞ /10๏ਓ
    ӡӦࢿۚ ɹແ͠ ༗Γ
    ίϛϡχέʔγϣϯ
    πʔϧ
    .-ɾTMBDL .-ɾTMBDL

    View full-size slide

  23. ίϛϡχςΟͷҧ͍
    ༧ࢉͷ༗ແ
    w .Z42-Ϣʔβձ͸͓ۚͷ੍໿Λ࡞Βͳ͍ͨΊ
    ʹ༧ࢉΛ׶͑ͯઃ͚ͯͳ͍
    w ೔ຊ1PTUHSFTϢʔβձ͸ڠࢍۚΛूΊɺ/10
    ๏ਓͱͯ͠؅ཧ͍ͯ͠Δ

    View full-size slide

  24. ίϛϡχςΟͷҧ͍
    ༧ࢉͷ༗ແ
    w .Z42-Ϣʔβձ͸͓ۚͷ੍໿Λ࡞Βͳ͍ͨΊ
    ʹ༧ࢉΛ׶͑ͯઃ͚ͯͳ͍
    w ೔ຊ1PTUHSFTϢʔβձ͸ڠࢍۚΛूΊɺ/10
    ๏ਓͱͯ͠؅ཧ͍ͯ͠Δ
    ࠓ೔ͷަ௨අ͸+16(ʹࢧԉͯ͠΋Βͬͯ·͢

    View full-size slide

  25. ίϛϡχςΟͷҧ͍
    ϢʔβձͷΞ΢τϓοτͷҧ͍
    w +16(͸υΩϡϝϯτ͸ίϛϡχςΟͰ࡞੒
    w .Z/"͸ݸʑͷΞ΢τϓοτʹҕͶΔ

    ެࣜυΩϡϝϯτ͸0SBDMF͕࡞੒
    w ษڧձ͸ͲͪΒ΋ਵ࣌ओମͰߦ͍ͬͯΔ

    View full-size slide

  26. ίϛϡχςΟͷҧ͍
    TMBDL͕ؾܰʹ࣭໰Ͱ͖ͯϨεϙϯε΋ྑ͍
    .Z42-ɿIUUQNZTRMDBTVBMTMBDLJOIFSPLVBQQDPN
    1PTUHSF42-ɿIUUQTQPTUHSFTRMIBDLFSTKQIFSPLVBQQDPN

    View full-size slide

  27. ίϛϡχςΟͷҧ͍
    ಥવͰ͕͢ʜ

    View full-size slide

  28. ίϛϡχςΟͷҧ͍
    ಥવͰ͕͢ʜ
    ˣ
    ࠓ͙͢ϑΥϩʔ͢΂͖ΤϯδχΞϦετ

    View full-size slide

  29. ίϛϡχςΟͷҧ͍
    ಥવͰ͕͢ʜ
    ˣ
    ࠓ͙͢ϑΥϩʔ͢΂͖ΤϯδχΞϦετ
    େମࠓ೔ͷ࿩͸͜ͷਓͨͪΛݟͯΕ͹ࣗવͱ֮͑Δ

    View full-size slide

  30. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖.Z42-ΤϯδχΞ

    View full-size slide

  31. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖.Z42-ΤϯδχΞ
    !3,BKJZBNB

    View full-size slide

  32. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖.Z42-ΤϯδχΞ
    !LBNJQP

    View full-size slide

  33. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖.Z42-ΤϯδχΞ
    !ZZBNBTBLJ

    View full-size slide

  34. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖.Z42-ΤϯδχΞ
    !OJQQPOEBOKJ

    View full-size slide

  35. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖.Z42-ΤϯδχΞ
    !ZPLV

    View full-size slide

  36. ίϛϡχςΟͷҧ͍
    ԶΑΓڧ͍.Z42-ΤϯδχΞʹձ͍ʹߦ͘
    ೔ຊ.Z42-Ϣʔβձձʢ.Z/"ձʣಛผฤ
    Ϣʔβձप೥ه೦ύʔςΟ
    ʙϢʔβձͱ.Z42-ͷ೥ΛৼΓฦΔʂʙ
    ։࠵೔෇ɿ೥݄೔ ۚ

    ։࠵࣌ࠁɿΛ༧ఆ
    ձ৔ɿגࣜձࣾίϩϓϥ
    ʢ౦ژɾܙൺणΨʔσϯϓϨΠελϫʔ֊ʣ

    View full-size slide

  37. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖1PTUHSF42-ΤϯδχΞ

    View full-size slide

  38. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖1PTUHSF42-ΤϯδχΞ
    1PTUHSF42-ͳਓɺ5XJUUFS࢖ͬͯͳ͍໰୊

    View full-size slide

  39. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖1PTUHSF42-ΤϯδχΞ
    !GVKJJ@NBTBP

    View full-size slide

  40. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖1PTUHSF42-ΤϯδχΞ
    !TOBHB

    View full-size slide

  41. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖1PTUHSF42-ΤϯδχΞ
    !T

    View full-size slide

  42. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖1PTUHSF42-ΤϯδχΞ
    !TBXBEB@NBTBIJLP

    View full-size slide

  43. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖1PTUHSF42-ΤϯδχΞ
    !OPCPSVT

    View full-size slide

  44. ίϛϡχςΟͷҧ͍
    ࠓ͙͢ϑΥϩʔ͢΂͖1PTUHSF42-ΤϯδχΞ
    !OVLP@ZPLPIBNB

    View full-size slide

  45. ίϛϡχςΟͷҧ͍
    1PTUHSF42-ΧϯϑΝϨϯε
    ೔࣌ɿ೥݄೔ ۚ
    ɹʙ
    ৔ॴɿ"1඼઒ɹ ౦ژ౎ߓ۠ߴྠ౦ٸୈೋϏϧ

    ಺༰ɿޕલجௐߨԋΛؚΉߨԋΛ༧ఆ
    ޕޙτϥοΫʹͯߨԋఔ౓Λ༧ఆɻ
    ΧϯϑΝϨϯεऴྃޙʹ࠙਌ձ͕͋Γ·͢ɻ
    ࢀՃਓ਺ݟࠐΈɿ໊ ҰൠࢀՃ໊ఔ౓

    ࢀՃඅɿ ԁΛ༧ఆ
    IUUQXXXQPTUHSFTRMKQFWFOUTKQVHQHDPO

    View full-size slide

  46. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹίϛϡχςΟͷҧ͍
    ̏ɹϋϚϦͲ͜ΖͷTips
    ̐ɹMySQL 5.7ͱPostgreSQL 9.5
    ̑ɹ·ͱΊ

    View full-size slide

  47. ϋϚϦͲ͜ΖͷTips
    ͓଴ͨͤ͠·ͨ͠
    ຊ೔ͷϝΠϯͰ͢

    View full-size slide

  48. ϋϚϦͲ͜ΖͷTips
    ຊ೔ͷ͓඼ॻ͖ .Z42-ฤ

    w ᐆດͳάϧʔϓ
    w SPPU͸౓ࢗ͢
    w ᐆດͳ஋
    w ΪϟοϓϩοΫͱτϥϯβΫγϣϯϨϕϧ

    View full-size slide

  49. ϋϚϦͲ͜ΖͷTips
    ຊ೔ͷ͓඼ॻ͖ .Z42-ฤ

    w ᐆດͳάϧʔϓ
    w SPPU͸౓ࢗ͢
    w ᐆດͳ஋
    w ΪϟοϓϩοΫͱτϥϯβΫγϣϯϨϕϧ

    View full-size slide

  50. ϋϚϦͲ͜ΖͷTips
    4&-&$5'30.TBNQMF
    JE HSPVQ OBNF BHF
    ޿ౡ 4POF
    ޿ౡ TPOF
    ෱ࢁ TPVEBJ
    ෱ࢁ UBLFUPNP

    View full-size slide

  51. ᐆດͳGROUP
    SELECT
    id, `group`,age
    FROM
    sample
    GROUP BY `group`

    View full-size slide

  52. ᐆດͳGROUP
    SELECT
    id, `group`,age
    FROM
    sample
    GROUP BY `group`
    HSPVQ͕༧໿ޠͳͷͰAͰғΉ

    View full-size slide

  53. ϋϚϦͲ͜ΖͷTips
    ݁Ռ
    JE HSPVQ BHF
    ޿ౡ
    ෱ࢁ

    View full-size slide

  54. ϋϚϦͲ͜ΖͷTips
    ݁Ռ
    JE HSPVQ BHF
    ޿ౡ
    ෱ࢁ
    6OJRVFͰ͸ແ͍ͷͰԿ͕ग़Δ͔͸Θ͔Βͳ͍

    View full-size slide

  55. ϋϚϦͲ͜ΖͷTips
    LBNJQP53"%*5*0/"-

    View full-size slide

  56. ᐆດͳGROUP
    SET SESSION
    sql_mode=‘TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY’;
    SELECT
    id, `group`,age
    FROM
    sample
    GROUP BY `group`;

    View full-size slide

  57. ᐆດͳGROUP
    SET SESSION
    sql_mode=‘TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY’;
    SELECT
    id, `group`,age
    FROM
    sample
    GROUP BY `group`;

    View full-size slide

  58. ᐆດͳGROUP
    SET SESSION
    sql_mode=‘TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY’;
    SELECT
    id, `group`,age
    FROM
    sample
    GROUP BY `group`;
    .Z42-ͷಠ֦ࣗுΛແޮʹ͢Δ

    View full-size slide

  59. ᐆດͳGROUP
    SET SESSION
    sql_mode=‘TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY’;
    SELECT
    id, `group`,age
    FROM
    sample
    GROUP BY `group`;
    ΋ͪΖΜNZDPOGʹ͔͍ͯ΋ྑ͍

    View full-size slide

  60. ϋϚϦͲ͜ΖͷTips
    ຊ೔ͷ͓඼ॻ͖ .Z42-ฤ

    w ᐆດͳάϧʔϓ
    w SPPU͸౓ࢗ͢
    w ᐆດͳ஋
    w ΪϟοϓϩοΫͱτϥϯβΫγϣϯϨϕϧ
    w ࢖͑Δͷ͸ͭ

    View full-size slide

  61. root͸2౓ࢗ͢
    ——ΞΧ΢ϯτΛ֬ೝ͢Δ
    SELECT
    user
    , host
    FROM
    user;

    View full-size slide

  62. ϋϚϦͲ͜ΖͷTips
    ݁Ռ
    VTFS IPTU
    SPPU
    SPPU
    SPPU MPDBMIPTU

    View full-size slide

  63. ϋϚϦͲ͜ΖͷTips
    ݁Ռ
    VTFS IPTU
    SPPU
    SPPU
    SPPU MPDBMIPTU
    ͦΕͧΕผϢʔβ

    View full-size slide

  64. ϋϚϦͲ͜ΖͷTips
    ݁Ռ
    VTFS IPTU
    SPPU
    SPPU
    SPPU MPDBMIPTU
    ΞΫηεग़དྷΔIPTU

    View full-size slide

  65. ϋϚϦͲ͜ΖͷTips
    ݁Ռ
    VTFS IPTU
    SPPU
    SPPU
    SPPU MPDBMIPTU
    QH@ICB@DPOG૬౰

    View full-size slide

  66. ϋϚϦͲ͜ΖͷTips
    ຊ೔ͷ͓඼ॻ͖ .Z42-ฤ

    w ᐆດͳάϧʔϓ
    w SPPU͸౓ࢗ͢
    w ᐆດͳ஋
    w ΪϟοϓϩοΫͱτϥϯβΫγϣϯϨϕϧ

    View full-size slide

  67. ᐆດͳ஋
    SELECT
    *
    FROM
    sample
    WHERE
    name = 'sone'

    View full-size slide

  68. ϋϚϦͲ͜ΖͷTips
    ݁Ռ
    JE HSPVQ OBNF BHF
    ޿ౡ 4POF
    ޿ౡ TPOF

    View full-size slide

  69. ϋϚϦͲ͜ΖͷTips
    ݁Ռ
    JE HSPVQ OBNF BHF
    ޿ౡ 4POF
    ޿ౡ TPOF

    View full-size slide

  70. ϋϚϦͲ͜ΖͷTips
    ݁Ռ
    JE HSPVQ OBNF BHF
    ޿ౡ 4POF
    ޿ౡ TPOF
    େจࣈখจࣈΛൺֱͯ͠ͳ͍

    View full-size slide

  71. ϋϚϦͲ͜ΖͷTips
    ຊ೔ͷ͓඼ॻ͖ .Z42-ฤ

    w ᐆດͳάϧʔϓ
    w SPPU͸౓ࢗ͢
    w ᐆດͳ஋
    w ΪϟοϓϩοΫͱτϥϯβΫγϣϯϨϕϧ

    View full-size slide

  72. ϋϚϦͲ͜ΖͷTips
    τϥϯβΫγϣϯϨϕϧ
    ෼཭Ϩϕϧ
    μʔςΟ
    Ϧʔυ
    ϑΝδʔ
    Ϧʔυ
    ϑΝϯτϜ
    Ϧʔυ
    ಡΈऔΓ
    ϩοΫ
    3&"%
    6/$0..*55&%
    :FT :FT :FT /P
    3&"%
    $0..*55&%
    /P :FT :FT /P
    3&1&"5"#-&
    3&"%
    /P /P :FT /P
    4&3*"-*;"#-& /P /P /P /P

    View full-size slide

  73. ϋϚϦͲ͜ΖͷTips
    τϥϯβΫγϣϯϨϕϧ
    ෼཭Ϩϕϧ
    μʔςΟ
    Ϧʔυ
    ϑΝδʔ
    Ϧʔυ
    ϑΝϯτϜ
    Ϧʔυ
    ಡΈऔΓ
    ϩοΫ
    3&"%
    6/$0..*55&%
    :FT :FT :FT /P
    3&"%
    $0..*55&%
    /P :FT :FT /P
    3&1&"5"#-&
    3&"%
    /P /P :FT /P
    4&3*"-*;"#-& /P /P /P /P
    .Z42-ͷσϑΥϧτ

    View full-size slide

  74. ϋϚϦͲ͜ΖͷTips
    τϥϯβΫγϣϯϨϕϧ
    ෼཭Ϩϕϧ
    μʔςΟ
    Ϧʔυ
    ϑΝδʔ
    Ϧʔυ
    ϑΝϯτϜ
    Ϧʔυ
    ಡΈऔΓ
    ϩοΫ
    3&"%
    6/$0..*55&%
    :FT :FT :FT /P
    3&"%
    $0..*55&%
    /P :FT :FT /P
    3&1&"5"#-&
    3&"%
    /P /P :FT /P
    4&3*"-*;"#-& /P /P /P /P
    1PTUHSF42-ͷσϑΥϧτ

    View full-size slide

  75. ίϛϡχςΟͷҧ͍
    ΪϟοϓϩοΫ
    w .Z42-ͷσϑΥϧτ͸3&1&"5"#-&3&"%
    w *OOPEC͸ߋ৽ର৅ͷߦ͚ͩΛϩοΫͰ͸ͳ͍ʂ
    w ༧૝֎ͷϩοΫΛऔಘ͢Δ
    w ߋ৽ॲཧͳͲͰϩοΫ଴ͪ౳͕ൃੜ͢Δ͜ͱ͕͋Δ

    View full-size slide

  76. ίϛϡχςΟͷҧ͍
    ΪϟοϓϩοΫ
    w .Z42-ͷσϑΥϧτ͸3&1&"5"#-&3&"%
    w *OOPEC͸ߋ৽ର৅ͷߦ͚ͩΛϩοΫͰ͸ͳ͍ʂ
    w ༧૝֎ͷϩοΫΛऔಘ͢Δ
    w ߋ৽ॲཧͳͲͰϩοΫ଴ͪ౳͕ൃੜ͢Δ͜ͱ͕͋Δ

    View full-size slide

  77. ΪϟοϓϩοΫ
    begin;
    SELECT * FROM
    lock_demo
    WHERE
    id >= 5 AND id <= 10
    FOR UPDATE;
    commit;

    View full-size slide

  78. ΪϟοϓϩοΫ
    begin;
    SELECT * FROM
    lock_demo
    WHERE
    id >= 5 AND id <= 10
    FOR UPDATE;
    commit;
    ൣғΛࢦఆͯ͠ϩοΫΛऔಘ

    View full-size slide

  79. ΪϟοϓϩοΫ
    INSERT INTO
    `demo`.`lock_demo`
    (`id`, `name`)
    VALUES
    ('7', 'test');

    View full-size slide

  80. ΪϟοϓϩοΫ
    INSERT INTO
    `demo`.`lock_demo`
    (`id`, `name`)
    VALUES
    ('7', 'test');
    ؒͷۭߦ΁*/4&35

    View full-size slide

  81. ΪϟοϓϩοΫ
    INSERT INTO
    `demo`.`lock_demo`
    (`id`, `name`)
    VALUES
    ('7', 'test');
    ؒͷۭߦ΁*/4&35
    ͜ͷ*/4&35͸଴ͨ͞ΕΔʂʂ

    View full-size slide

  82. ίϛϡχςΟͷҧ͍
    ΪϟοϓϩοΫͷ஫ҙ఺
    w ࣮ମҎ֎ͷϨίʔυ΋ϩοΫΛऔΔ
    w ൣғ಺ͷۭߦͰ΋ϩοΫ
    w ۭৼΓͷ4&-&$5'0361%"5&Ͱ΋ϩοΫ
    w ্ݶͷແ͍8)&3&ͩͱશ෦ϩοΫ

    View full-size slide

  83. ίϛϡχςΟͷҧ͍
    ΪϟοϓϩοΫͷ஫ҙ఺
    w ࣮ମҎ֎ͷϨίʔυ΋ϩοΫΛऔΔ
    w ൣғ಺ͷۭߦͰ΋ϩοΫ
    w ۭৼΓͷ4&-&$5'0361%"5&Ͱ΋ϩοΫ
    w ্ݶͷແ͍8)&3&ͩͱશ෦ϩοΫ
    ؒͷۭߦ΁*/4&35

    View full-size slide

  84. ίϛϡχςΟͷҧ͍
    ΪϟοϓϩοΫͷ஫ҙ఺
    w ࣮ମҎ֎ͷϨίʔυ΋ϩοΫΛऔΔ
    w ൣғ಺ͷۭߦͰ΋ϩοΫ
    w ۭৼΓͷ4&-&$5'0361%"5&Ͱ΋ϩοΫ
    w ্ݶͷແ͍8)&3&ͩͱશ෦ϩοΫ
    8)&3&JEͳͲ͸ةݥ

    View full-size slide

  85. ϋϚϦͲ͜ΖͷTips
    ߋʹωΫετΩʔϩοΫ΋͋Δ

    View full-size slide

  86. ϋϚϦͲ͜ΖͷTips
    ߋʹωΫετΩʔϩοΫ΋͋Δ
    ˣ
    ߋ৽ߦͷલޙͷϩοΫΛऔΔ

    View full-size slide

  87. ϋϚϦͲ͜ΖͷTips
    ௕͍ͷͰଓ͖͸8FCͰ
    !LBNJQP͞ΜͷϒϩάΛҾ༻
    *OOP%#ͷϩοΫͷൣғͱωΫετΩʔϩοΫͷ࿩
    IUUQCMPHLBNJQPOFUFOUSZ

    View full-size slide

  88. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹίϛϡχςΟͷҧ͍
    ̏ɹϋϚϦͲ͜ΖͷTips
    ̐ɹMySQL 5.7ͱPostgreSQL 9.5
    ̑ɹ·ͱΊ

    View full-size slide

  89. MySQL 5.7ͱPostgreSQL 9.5
    ྆%#ͷ࠷৽൛

    View full-size slide

  90. MySQL 5.7ͱPostgreSQL 9.5
    ྆%#ͷ࠷৽൛
    ˣ
    ͦΖͦΖਖ਼ࣜ൛͕ग़Δ

    View full-size slide

  91. MySQL 5.7ͱPostgreSQL 9.5
    1PTUHSF42-ͷ࿩Λฉ͖ʹདྷͨਓʁ

    View full-size slide

  92. MySQL 5.7ͱPostgreSQL 9.5
    1PTUHSF42-ͷ࿩Λฉ͖ʹདྷͨਓʁ
    ˣ
    ࢒೦ɺ๯಄Ͱ΋৮Ε·͕ͨ͠આ໌͠·ͤΜ

    View full-size slide

  93. MySQL 5.7ͱPostgreSQL 9.5
    1PTUHSF42-

    View full-size slide

  94. MySQL 5.7ͱPostgreSQL 9.5
    1PTUHSF42-
    ˣ
    ϧʔΫɺʮࣰాͷދͷרͦͷ̐ʯΛ࢖͑
    ελʔɾ΢Υʔζݟͨ͜ͱແ͍͚Ͳ

    View full-size slide

  95. MySQL 5.7ͱPostgreSQL 9.5
    ஶऀͷࣰా͞Μ

    View full-size slide

  96. MySQL 5.7ͱPostgreSQL 9.5
    ஶऀͷࣰా͞Μ
    ˣ
    ୈճதࠃ஍ํ%#ษڧձJOग़Ӣ

    Ͱ1PTUHSF42-ͷ࿩Λͯ͘͠Ε·͢

    View full-size slide

  97. MySQL 5.7ͱPostgreSQL 9.5
    ͋ͱᖒా͞Μ͕1PTUHSF42-ৄ͍͠

    View full-size slide

  98. MySQL 5.7ͱPostgreSQL 9.5
    ͋ͱᖒా͞Μ͕1PTUHSF42-ৄ͍͠
    ˣ
    1PTUHSF42-৽ػೳ঺հ
    IUUQXXXTMJEFTIBSFOFUIBEPPQYOUUEBUBQPTUHSFTRMOFXGFBUVSFTOUUEBUB
    ֆͱ͔͍ͬͺ͍͋ΔͷͰॳ৺ऀʹ΋༏͍͠ࢿྉͱͳ͓ͬͯΓ·͢

    View full-size slide

  99. MySQL 5.7ͱPostgreSQL 9.5
    1PTUHSF42-ΧϯϑΝϨϯε
    ߦͬͨΒ୭͔࿩ͯ͠ΔΑ
    ʢଟ෼ɺ͖ͬͱɺ.BZCFʣ

    View full-size slide

  100. MySQL 5.7ͱPostgreSQL 9.5
    .Z42-ͷৄࡉ

    View full-size slide

  101. MySQL 5.7ͱPostgreSQL 9.5
    .Z42-ͷৄࡉ
    ˣ
    தࠃ஍ํ%#ษڧձʹஔ͍ͯ͋ΔΑ
    IUUQTECTUVEZDIVHPLVHJUIVCJP

    View full-size slide

  102. MySQL 5.7ͱPostgreSQL 9.5
    .Z42-ͷৄࡉ
    ˣ
    தࠃ஍ํ%#ษڧձʹஔ͍ͯ͋ΔΑ
    IUUQTECTUVEZDIVHPLVHJUIVCJP
    ͔͠΋ಈը෇͖

    View full-size slide

  103. MySQL 5.7ͱPostgreSQL 9.5
    தࠃ஍ํ%#ษڧձ
    ୈճ.Z42-ͷ࠷৽ࣄ৘ ֿࢁ͞Μ

    ୈճ.Z42-࠷৽ࣄ৘ ֿࢁ͞Μ

    ୈճ.Z42-3$ొ৔ʂ࣍ͷ.Z42-͸͕͜͜ҧ͏ͬʂ ֿࢁ͞Μ

    ୈճ.Z42-։ൃ࠷৽ಈ޲ ࢁ࡚͞Μ

    ͦͷதͰ͍͔ͭ͘ϐοΫΞοϓ

    View full-size slide

  104. MySQL 5.7ͱPostgreSQL 9.5
    ιʔείʔυ͕HJUIVCͰެ։͞Εͨ
    ެࣜϦϙδτϦ
    IUUQTHJUIVCDPNNZTRM
    .Z42-ຊମҎ֎ʹ΋໘ന͍΋ͷ͕୔ࢁʂ

    View full-size slide

  105. MySQL 5.7ͱPostgreSQL 9.5
    ߴ଎Խ
    w ಉҰεΩʔϚ಺Ͱ΋ϚϧνεϨουରԠ
    w ΦϓςΟϚΠβ͕ΑΓݡ͘
    w *OOP%#ʹ೔ຊޠશจݕࡧ͕௥Ճ
    w WJSUVBMDPMVNO
    w .Z42-ΑΓഒɺΑΓഒૣ͘ʂ

    View full-size slide

  106. MySQL 5.7ͱPostgreSQL 9.5
    ߴ଎Խ
    w ಉҰεΩʔϚ಺Ͱ΋ϚϧνεϨουରԠ
    w ΦϓςΟϚΠβ͕ΑΓݡ͘
    w *OOP%#ʹ೔ຊޠશจݕࡧ͕௥Ճ
    w WJSUVBMDPMVNO
    w .Z42-ΑΓഒɺΑΓഒૣ͘ʂ

    View full-size slide

  107. MySQL 5.7ͱPostgreSQL 9.5

    View full-size slide

  108. MySQL 5.7ͱPostgreSQL 9.5

    View full-size slide

  109. MySQL 5.7ͱPostgreSQL 9.5
    ߴ଎Խ
    w ಉҰεΩʔϚ಺Ͱ΋ϚϧνεϨουରԠ
    w ΦϓςΟϚΠβ͕ΑΓݡ͘
    w *OOP%#ʹ೔ຊޠશจݕࡧ͕௥Ճ
    w WJSUVBMDPMVNO
    w .Z42-ΑΓഒɺΑΓഒૣ͘ʂ
    ಛʹߴෛՙ͕࣌ૣ͘ͳͬͨ

    View full-size slide

  110. MySQL 5.7ͱPostgreSQL 9.5
    ߴػೳԽ
    w ΫΤϦɾϦϥΠτɾϓϥάΠϯ
    w +40/ܕΛαϙʔτ
    w ϨϓϦέʔγϣϯͷڧԽ

    View full-size slide

  111. MySQL 5.7ͱPostgreSQL 9.5
    ߴػೳԽ
    w ΫΤϦɾϦϥΠτɾϓϥάΠϯ
    w +40/ܕΛαϙʔτ
    w ϨϓϦέʔγϣϯͷڧԽ
    ·ͩ1PTUHSF42-ͷํ͕Ұ೔ͷ௕͕͋Δ

    View full-size slide

  112. MySQL 5.7ͱPostgreSQL 9.5
    ߴػೳԽ
    w ΫΤϦɾϦϥΠτɾϓϥάΠϯ
    w +40/ܕΛαϙʔτ
    w ϨϓϦέʔγϣϯͷڧԽ
    .Z42-ຊମͰࣗಈϑΣΠϧΦʔόʔΛαϙʔτ
    1PTUHSF42-ίϛολ༷ɺ·ͩͰ͔͢ʁ

    View full-size slide

  113. MySQL 5.7ͱPostgreSQL 9.5
    ͦͷଞ
    w 4:4εΩʔϚΛαϙʔτ
    w 0/-:@'6--@(3061@#: .Z42-
    ͕σϑΥϧτ
    w ೔Ͱ1BTTXPSEมߋڧ੍͕σϑΥϧτ
    w ͭͷςʔϒϧʹϚϧντϦΨʔΛαϙʔτ

    View full-size slide

  114. MySQL 5.7ͱPostgreSQL 9.5
    1PTUHSF42-

    View full-size slide

  115. MySQL 5.7ͱPostgreSQL 9.5
    1PTUHSF42-
    ˣ
    ਖ਼౷೿ͳ3%#ΛΑΓ֦ு͍ͯ͘͠ײ͡

    View full-size slide

  116. MySQL 5.7ͱPostgreSQL 9.5
    .Z42-

    View full-size slide

  117. MySQL 5.7ͱPostgreSQL 9.5
    .Z42-
    ˣ
    ਖ਼͍͠3%#ͬΆ͘ͳͬͨ
    ʢͦͷӨڹͰԼҐޓ׵ੑͷແ͍มߋ΋ଟ͍ʣ
    ݩ͕ΏΔ;Θܥա͗ͨͷͰྑ͍ͱࢥ͏

    View full-size slide

  118. MySQL 5.7ͱPostgreSQL 9.5
    ࠓޙͷ.Z42-

    View full-size slide

  119. MySQL 5.7ͱPostgreSQL 9.5
    ࠓޙͷ.Z42-
    ΑΓେن໛ΞʔΩςΫνϟʹಛԽ͍͖ͯͦ͠͏

    View full-size slide

  120. MySQL 5.7ͱPostgreSQL 9.5
    ࠓޙͷ.Z42-
    ΑΓେن໛ΞʔΩςΫνϟʹಛԽ͍͖ͯͦ͠͏
    ˣ
    ϥΠόϧ͸1PTUHSF42-Ͱ͸ͳ͘/042-

    View full-size slide

  121. MySQL 5.7ͱPostgreSQL 9.5
    ࠓޙͷ1PTUHSF42-

    View full-size slide

  122. MySQL 5.7ͱPostgreSQL 9.5
    ࠓޙͷ1PTUHSF42-
    ਖ਼౷೿3%#ͱͯ͠ਐԽ͍͖ͯͦ͠͏

    View full-size slide

  123. MySQL 5.7ͱPostgreSQL 9.5
    ࠓޙͷ1PTUHSF42-
    ਖ਼౷೿3%#ͱͯ͠ਐԽ͍͖ͯͦ͠͏
    ˣ
    ϥΠόϧ͸.Z42-Ͱ͸ͳ͘ɺ঎༻%#

    View full-size slide

  124. MySQL 5.7ͱPostgreSQL 9.5
    .Z42-ͱ1PTUHSF42-

    View full-size slide

  125. MySQL 5.7ͱPostgreSQL 9.5
    .Z42-ͱ1PTUHSF42-
    ࠓ·Ͱ΋͜Ε͔Β΋ॅΈ෼͚͕ਐΜͰ͍͘

    View full-size slide

  126. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹίϛϡχςΟͷҧ͍
    ̏ɹϋϚϦͲ͜ΖͷTips
    ̐ɹMySQL 5.7ͱPostgreSQL 9.5
    ̑ɹ·ͱΊ

    View full-size slide

  127. ·ͱΊ
    ෳ਺ͷબ୒ࢶΛ஌Δ

    View full-size slide

  128. ·ͱΊ
    ෳ਺ͷબ୒ࢶΛ஌Δ
    ˣ
    ධՁ͕࣠૿͑ͯൃ૝͕޿͕Δ

    View full-size slide

  129. ·ͱΊ
    ෳ਺ͷબ୒ࢶΛ஌Δ
    ˣ
    ධՁ͕࣠૿͑ͯൃ૝͕޿͕Δ
    ˣ
    ઃܭྗ্͕͕Δ

    View full-size slide

  130. ·ͱΊ
    બ୒ࢶ͕૿͑Δ

    View full-size slide

  131. ·ͱΊ
    બ୒ࢶ͕૿͑Δ
    ˣ
    ࢓ࣄͷ෯͕૿͑Δ

    View full-size slide

  132. ·ͱΊ
    બ୒ࢶ͕૿͑Δ
    ˣ
    ࢓ࣄͷ෯͕૿͑Δ
    ˣ
    .Z42-΋1PTUHSF42-΋Θ͔Δਓ͸গͳ͍

    View full-size slide

  133. ·ͱΊ
    બ୒ࢶ͕૿͑Δ
    ˣ
    ࢓ࣄͷ෯͕૿͑Δ
    ˣ
    .Z42-΋1PTUHSF42-΋Θ͔Δਓ͸গͳ͍
    ࠓޙ΋3%#૚ͷ044ར༻͸૿͑Δ͸ͣ

    View full-size slide

  134. ·ͱΊ
    બ୒ࢶ͕૿͑Δ
    ˣ
    ࢓ࣄͷ෯͕૿͑Δ
    ˣ
    .Z42-΋1PTUHSF42-΋Θ͔Δਓ͸গͳ͍
    ࠓޙ΋3%#૚ͷ044ར༻͸૿͑Δ͸ͣ
    %#ΤϯδχΞͷੜ͖࢒ΓઓུͷҰͭ

    View full-size slide

  135. ·ͱΊ
    ࠷৽൛ͷௐࠪ

    View full-size slide

  136. ·ͱΊ
    ࠷৽൛ͷௐࠪ
    ˣ
    ίϛϡχςΟΛ্ख͘࢖͏

    View full-size slide

  137. ·ͱΊ
    ࠷৽൛ͷௐࠪ
    ˣ
    ίϛϡχςΟΛ্ख͘࢖͏
    ˣ
    ೔ຊޠ৘ใ΋ଟ͍

    View full-size slide

  138. ·ͱΊ
    ࠷৽൛ͷௐࠪ
    ˣ
    ίϛϡχςΟΛ্ख͘࢖͏
    ˣ
    ೔ຊޠ৘ใ΋ଟ͍
    ߴ଎Խ΋ϝϦοτ͕ͩ
    ࠓࠔͬͯΔࣄΛղܾͯ͘͠ΕΔࣄ΋ଟ͍
    ྫɿ1PTUHSF42-ͷ.BSHFจ
    .Z42-ͷτϦΨʔͳͲ

    View full-size slide

  139. ·ͱΊ
    ΤϯδχΞͱͯ͠ੜ͖࢒Δʹ͸

    View full-size slide

  140. ·ͱΊ
    ΤϯδχΞͱͯ͠ੜ͖࢒Δʹ͸
    ˣ
    3%#ͷٕज़ͷण໋͸௕͍

    View full-size slide

  141. ·ͱΊ
    ΤϯδχΞͱͯ͠ੜ͖࢒Δʹ͸
    ˣ
    3%#ͷٕज़ͷण໋͸௕͍
    ˣ
    ϕϯμʔϩοΫΠϯ͞Εͳ͍஌ࣝ͸وॏ

    View full-size slide

  142. ·ͱΊ
    σʔλͷण໋͸ίʔυΑΓ௕͍

    View full-size slide

  143. ·ͱΊ
    σʔλͷण໋͸ίʔυΑΓ௕͍
    ˣ
    ͔ͩΒͦ͜%#ͷ஌ࣝ͸େ੾Ͱ͢

    View full-size slide

  144. ·ͱΊ
    %#ͷʮ͜Ε஌Βͳ͔ͬͨʯ

    View full-size slide

  145. ·ͱΊ
    %#ͷʮ͜Ε஌Βͳ͔ͬͨʯ
    ˣ
    க໋ইʹͳΔ͜ͱ΋ଟ͍

    View full-size slide

  146. ·ͱΊ
    ීஈ͔Βͷ஌ࣝͷΞοϓσʔτΛେ੾ʹ

    View full-size slide

  147. ·ͱΊ
    ීஈ͔Βͷ஌ࣝͷΞοϓσʔτΛେ੾ʹ
    ˣ
    ൺֱ͢Δ͜ͱͰؔ࿈෇͚ͨ͠هԱʹͳΔ

    View full-size slide

  148. ·ͱΊ
    ٕज़͸ෳ਺ֶͼ·͠ΐ͏

    View full-size slide

  149. ͝੩ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ

    View full-size slide