MySQLとPostgreSQLのこと(実践編)

第13回 中国地方DB勉強会 in 山口の発表資料です。

https://dbstudychugoku.github.io/

Transcript

1. MySQLͱPostgreSQLͷ͜ͱ ʢ࣮ફฤʣ ୈճɹதࠃ஍ํ%#ษڧձ
2. What is it? MySQLͱPostgreSQL
3. What is it? ࠓ͸2ڧ࣌୅ʢͦʔ͍ͩௐ΂
4. What is it? ଟ෼ɺଟ͘ͷਓ͕࢖͍ͬͯΔ͸ͣ
5. What is it? ·ͨ͜Ε͔Β࢖͏ػձ͕ଟʑ͋Δ͸ͣ
6. What is it? ͦΜͳਓͨͪͷͨΊʹ ࠓ೔͔Β࢖͑Δϊ΢ϋ΢Λʂ
7. What is it?
8. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ
9. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ
10. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ31ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
11. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ31ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
12. What is it? d
13. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ
14. MySQL .Z42-ͷಛ௃
    
15. MySQL .Z42-ͷಛ௃
    ˣ
    γϯϓϧɾߴ଎
16. MySQL ߋ৽ܕΞʔΩςΫνϟ
17. MySQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ
18. MySQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ ಉ͡ߦΛߋ৽͢Δ
19. MySQL ετϨʔδΤϯδϯ
20. MySQL Ҿ༻ݩɿIUUQXXXTMJEFTIBSFOFUZPLVIBOEMFS
    !ZPLV͞Μɺ͍ͭ΋͋Γ͕ͱ͏͍͟͝·͢ɻ
21. MySQL Ҿ༻ݩɿIUUQXXXTMJEFTIBSFOFUZPLVIBOEMFS
    !ZPLV͞Μɺ͍ͭ΋͋Γ͕ͱ͏͍͟͝·͢ɻ
22. MySQL ετϨʔδΤϯδϯͱ͸
    
23. MySQL ετϨʔδΤϯδϯͱ͸
    ˣ
    େମσʔλͷಡΈॻ͖͢Δͱ͜Ζ
24. MySQL ͔͜͜Βઌ͸*OOP%#ͷ࿩
    .Z*4".ʁ஌Βͳ͍ࢠͰ͢Ͷʢന໨
25. MySQL γϯϓϧɾߴ଎
    
26. MySQL γϯϓϧɾߴ଎
    ˣ
    */%&9͕ޮ͍͍ͯΔࣄ͕େࣄ
27. MySQL #5SFF
    */%&9
28. MySQL #5SFF
    */%&9 .Z42-͸جຊతʹ͜Ε
    ʢ35SFFʹ͍ͭͯ͸ࠓճ৮Εͳ͍ʣ
29. MySQL Ҿ༻ݩɿIUUQTKBXJLJQFEJBPSHXJLJ##&$"
    ΢ΟΩϖσΟΞʮ# ໦ʯ
30. MySQL w 8)&3&۟Ͱ࢖͏
    w (3061
    #:۟Ͱ΋࢖͏
    w 03%&3
    #:۟Ͱ΋࢖͏
31. MySQL w 8)&3&۟Ͱ࢖͏
    w (3061
    #:۟Ͱ΋࢖͏
    w 03%&3
    #:۟Ͱ΋࢖͏ ݕࡧશൠͰ࢖͏
32. MySQL w ॏෳ͕গͳ͍΄͏͕ྑ͍
    w ݁Ռ͕গͳ͍΄͏͕ྑ͍
    w ར༻͢Δ*/%&9͸̍5"#-&ʹݸ
33. MySQL w ॏෳ͕গͳ͍΄͏͕ྑ͍
    w ݁Ռ͕গͳ͍΄͏͕ྑ͍
    w ར༻͢Δ*/%&9͸̍5"#-&ʹݸ .Z42-ͷେ͖ͳಛ௃ͷҰͭ
    ʢΠϯσοΫε݁߹ʹ͍ͭͯ͸ࠓճ৮Εͳ͍ʣ
34. MySQL ࣮ߦܭըΛݟΔ
35. MySQL .Z42-
    8PSLCFODI
    
36. MySQL .Z42-
    8PSLCFODI
    ˣ
    ແྉͰ࢖͑Δ
37. MySQL
38. MySQL 0SBDMFΞΧ΢ϯτෆཁʂʂ
39. ࣮ߦܭը CREATE TABLE `demo`.`users` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT '', `name` VARCHAR(45) NOT NULL COMMENT '', `age` INT NOT NULL COMMENT '', `created` DATETIME NOT NULL DEFAULT NOW () COMMENT '', PRIMARY KEY (`id`) COMMENT '' ); ——શ݅Λબ୒͢ΔͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users; ——PRIMARY KEYʢINDEXʣΛར༻ͨ͠ྫ SELECT * FROM demo.users WHERE id > 100; ——INDEX͕ແ͍ͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users WHERE age > 20;
40. MySQL
41. MySQL
42. MySQL αϒΫΤϦͱ૬ؔαϒΫΤϦ
43. αϒΫΤϦ —— INDEXͱͯ͠PRIMARY KEY͕ޮ͍͍ͯΔ SELECT * FROM users WHERE id < 1000 AND id > 800 —— 1ճ͔࣮͠ߦ͞Εͳ͍ SELECT * FROM (SELECT * FROM users WHERE id < 1000 AND id > 800) AS dummy
44. MySQL
45. ૬ؔαϒΫΤϦ ——஗͍૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age FROM users WHERE id BETWEEN 10 AND 100000)
46. MySQL
47. MySQL .Z42-
    ͔Β."5&3*"-*;&%ʹ มΘΓɺ+0*/ͷΑ͏ͳڍಈʹͳͬͨ
    ੲΑΓૣ͘ͳͬͨ
48. ૬ؔαϒΫΤϦ ——INDEX͕ར༻Ͱ͖Ε͹૬ؔαϒΫΤϦͰ΋଎͍ SELECT * FROM users WHERE created = '2016-02-27 04:31:32' AND id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)
49. MySQL
50. MySQL ૬ؔαϒΫΤϦ͸஗͍
    ৔߹͕ଟ͍
51. MySQL ૬ؔαϒΫΤϦ͸஗͍
    ˣ
    +0*/ʹॻ͖׵͑Δ
52. JOIN SELECT * FROM users INNER JOIN users AS tmp ON tmp.id = users.id AND tmp.id BETWEEN 10 AND 100000 WHERE users.created = '2016-02-27 04:31:32'
53. MySQL
54. MySQL +0*/͸ֻ͚ࢉ
55. MySQL +0*/͸ֻ͚ࢉ
    ˣ
    ߦ☓ߦ͸ߦ
56. MySQL .Z42-ʹ͸
    +0*/ͷΞϧΰϦζϜ͸Ұ͔ͭ͠ͳ͍
57. MySQL .Z42-ʹ͸
    +0*/ͷΞϧΰϦζϜ͸Ұ͔ͭ͠ͳ͍ ܾͯ͠ಘҙͳΘ͚Ͱ͸ແ͍
    +0*/͸ϋΠίετͳΫΤϦ
58. MySQL w ग़དྷΔ͚ͩখ͔ͯ͘͞͠Β+0*/
    w ෆཁͳ+0*/͸ආ͚Δ
    w */%&9Λར༻ͨ͠+0*/Λ͢Δ
59. MySQL γϯϓϧɾߴ଎
60. MySQL γϯϓϧɾߴ଎
    ˣ
    ೉͍͠ࣄΛ͠ͳ͍
    ઃܭྗ͕ॏཁ
61. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔҧ͍ ̑ɹ·ͱΊ
62. PostgreSQL 1PTUHSF42-ͷಛ௃
63. PostgreSQL 1PTUHSF42-ͷಛ௃
    ˣ
    ߴػೳ
64. PostgreSQL ௥هܕΞʔΩςΫνϟ
65. PostgreSQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ
66. PostgreSQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ࢀরΛ֎͢
67. PostgreSQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ৽ͨʹมߋ෼Λ௥Ճ͢Δ
68. PostgreSQL ͭ·Γɺߋ৽͸.Z42-ΑΓෆར
    ʢ%&-&5&͸.Z42-΋ࣅͨΑ͏ͳࣄͯ͠Δʣ
69. PostgreSQL ͔͠͠ɺখ೉͍͜͠ͱ͸ಘҙ
70. PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ
    wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ
    w૬ؔαϒΫΤϦ΋଎͍
    w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ
71. PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ
    wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ
    w૬ؔαϒΫΤϦ΋଎͍
    w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ ͭͷΫΤϦ಺Ͱෳ਺࢖͑Δ
72. PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ
    wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ
    w૬ؔαϒΫΤϦ΋଎͍
    w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ #USFFɺ(*/ɺࣜ*/%&9ͳͲγʔϯʹ߹Θͤͯ
    ෳ਺ͷ*/%&9Λར༻Ͱ͖Δ
73. PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ
    wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ
    w૬ؔαϒΫΤϦ΋଎͍
    w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ ࢖͑Δ42-ͷߏจ΋ଟ͍
    ྫ͑͹΢Πϯυ΢ؔ਺ͳͲ
74. PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ
    wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ
    w૬ؔαϒΫΤϦ΋଎͍
    w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ /FTUFE
    -PPQ
    +PJO
    4PSU
    .FSHF
    +PJO
    )BTI
    +PJO
75. PostgreSQL ߹ΘͤͯಡΉͱָ͍͠ࢿྉ
    
76. ෳ਺ར༻ͨ͠INDEX CREATE TABLE public.users ( id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass), name text NOT NULL, age integer NOT NULL, created timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT users_pkey PRIMARY KEY (id)); CREATE INDEX users_created_idx ON public.users USING tree (created); ——idͱcreatedͷINDEXΛར༻͢Δ SELECT * FROM users WHERE id < 100 AND created < '2016-02-27 05:41:28';
77. PostgreSQL
78. PostgreSQL
79. ࣜINDEX ——ࣜINDEXΛ࡞੒ CREATE INDEX test_idx ON users (substr(name,10,12)); ——ࣜͷ݁ՌͰINDEX͕ޮ͘ SELECT name FROM users WHERE substr(name,10,12) = '10'
80. PostgreSQL
81. PostgreSQL
82. ૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age FROM users WHERE id BETWEEN 10 AND 100000)
83. PostgreSQL
84. PostgreSQL
85. PostgreSQL ΦϓςΟϚΠβ͕
    αϒΫΤϦΛ+0*/ʹॻ͖׵͑ͯΔ
86. ૬ؔαϒΫΤϦ ——INDEXΛར༻ͨ͠αϒΫΤϦ SELECT * FROM users WHERE id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)
87. PostgreSQL ΦϓςΟϚΠβ͕+0*/ʹʢ͈́
88. ૬ؔαϒΫΤϦ ——σϞͰINDEXΛ࢖ͬͯ͘Εͳ͍ͷͰINDEXΛ༏ઌతʹར༻͢ΔΑ͏ʹࢦఆ SET ENABLE_SEQSCAN=OFF; SELECT * FROM users WHERE id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)
89. PostgreSQL
90. PostgreSQL
91. PostgreSQL 1PTUHSF42-ͷ૬ؔαϒΫΤϦ͸
    ൺֱత଎͍
92. ΢Πϯυ΢ؔ਺ SELECT rank() OVER (PARTITION BY age ORDER BY id) , * FROM users WHERE age BETWEEN 10 AND 30 LIMIT 100
93. PostgreSQL
94. PostgreSQL
95. PostgreSQL ෳࡶͳूܭ͸ಘҙ
96. PostgreSQL ΢Πϯυ΢ؔ਺ʹ͍ͭͯ
    ߹ΘͤͯಡΉͱྑ͍ࢿྉ
    8FCΤϯδχΞ͕1PTUHSF42-ΛબͿͭͷཧ༝
    IUUQXXXTMJEFTIBSFOFU4PVEBJ4POFXFCQPTUHSFTRM
97. PostgreSQL ෳࡶͳूܭ͸ಘҙ
    ˣ
    Ͱ΋3%#ͷجຊ͸.Z42-ͱҰॹ
98. PostgreSQL ෳࡶͳूܭ͸ಘҙ
    ˣ
    Ͱ΋3%#ͷجຊ͸.Z42-ͱҰॹ ਖ਼نԽ͕ॏཁ
    */%&9Λར༻͠ͳ͍ͱ஗͍
    +0*/͸ϋΠίετͳΫΤϦ
    ࣮ߦܭը͸ݟΕΔΑ͏ʹͳΖ͏ʂ
99. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ
100. ࣮ફͰϋϚΔ᠘ աڈͷ৘ใʢϊΠζʣ
101. ࣮ફͰϋϚΔ᠘ աڈͷ৘ใʢϊΠζʣ
     ˣ
     (PPHMFݕࡧͷݹ͍هࣄ΍
     ޱ఻ʹ࿭Θ͞ΕΔ
102. ࣮ફͰϋϚΔ᠘ աڈͷ৘ใʢϊΠζʣ
     ˣ
     (PPHMFݕࡧͷݹ͍هࣄ΍
     ޱ఻ʹ࿭Θ͞ΕΔ 3%#͕ۤखͳͳઌഐ΍্͕࢘
     ෇͍͕ͨ࣌ͱͯ΋ةݥ
103. ࣮ફͰϋϚΔ᠘ w"-5&3จͰϩοΫΛऔΔʢ.Z42-ʣ w7"$66.͸खಈʢ1PTUHSF42-ʣ
104. ࣮ફͰϋϚΔ᠘ w"-5&3จͰϩοΫΛऔΔʢ.Z42-ʣ w7"$66.͸खಈʢ1PTUHSF42-ʣ .Z42-
     ͔ΒΦϯϥΠϯ
     "-5&3จΛαϙʔτ
105. ࣮ફͰϋϚΔ᠘ w"-5&3จͰϩοΫΛऔΔʢ.Z42-ʣ w7"$66.͸खಈʢ1PTUHSF42-ʣ 1PTUHSF42-
     ͔Β
     "VUP
     7"$66.Λαϙʔτ
106. ࣮ફͰϋϚΔ᠘ ࣗ෼ͷ࢖͍ͬͯΔόʔδϣϯΛ
     ͔ͬ͠Γ֬ೝ͢Δ͜ͱ
107. ࣮ફͰϋϚΔ᠘ ग़དྷΔ͚ͩ৽͍͠όʔδϣϯ͕ྑ͍
     ϝδϟʔόʔδϣϯʹؔͯ͠͸
     ೥ޙ͙Β͍ͷόʔδϣϯ͕͍͍ײ͡
108. ࣮ફͰϋϚΔ᠘ ৽ػೳΛ࢖͏
109. ࣮ફͰϋϚΔ᠘ ৽ػೳΛ࢖͏
     ˣ
     3%#ͷ৽ػೳ͸஍ཕʢόάʣΛ
     ౿Ή֮ޛ͕ඞཁ
110. ࣮ફͰϋϚΔ᠘ ৽ػೳΛ࢖͏
     ˣ
     3%#ͷ৽ػೳ͸஍ཕʢόάʣΛ
     ౿Ή֮ޛ͕ඞཁ خʑͱͯ͠஍ཕΛ౿Έൈ͘Α͏ʹͳͬͨΒ
     ͦΕ͸Ұྲྀͷূʢଟ෼
111. ࣮ફͰϋϚΔ᠘ ࢥ͍ࠐΈ
112. ࣮ફͰϋϚΔ᠘ ࢥ͍ࠐΈ
     ˣ
     ޓ׵ੑͷແ͍มߋͰ
     ΧδϡΞϧʹࢮ͵
113. ࣮ફͰϋϚΔ᠘ ࢥ͍ࠐΈ
     ˣ
     ޓ׵ੑͷແ͍มߋͰ
     ΧδϡΞϧʹࢮ͵ όʔδϣϯҧ͍΍ҧ͏%#Λ
     ࢖ͬͨ࣌ʹΑ͘ൃੜ͢Δ
114. ࣮ફͰϋϚΔ᠘ wυΩϡϝϯτΛݟΔ
     w࣮ߦܭըΛݟΔ
     wਪଌΑΓ΋ܭଌ
115. ࣮ફͰϋϚΔ᠘ ͋Δ೔ಥવ஗͘ͳΔ
116. ࣮ફͰϋϚΔ᠘ ͋Δ೔ಥવ஗͘ͳΔ
     ˣ
     σʔλྔ͕ϝϞϦʹ৐Βͳ͘ͳΔ
117. ࣮ફͰϋϚΔ᠘ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦ ϝϞϦ Ϩίʔυ Ϩίʔυ Ϩίʔυ σʔλ૿
118. ࣮ફͰϋϚΔ᠘ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦ ϝϞϦ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦʹ৐Βͳ͍ͷͰ%JTL
     *0͕ൃੜ͢Δ σʔλ૿
119. ࣮ફͰϋϚΔ᠘ w ूܭର৅͕େ͖͘ͳͬͯूܭ࣌ʹϝϞϦʹ৐Βͳ͍
 ˠ5FNQPSBSZΛ%JTLʹు͘ͷͰॏ͘ͳΔ
     w */%&9͕ແ͘5"#-&εΩϟϯΛ͍ͯͨ͠
 ˠςετ࣌͸σʔλ͕খ͔ͬͨ͞ͷͰ໰୊ʹؾ͔ͮͳ͔ͬͨ
     w ϓϩάϥϜ͕ϧʔϓͷ਺͚ͩΫΤϦΛ౤͍͛ͯͨ
 ˠߦ਺͕૿͑Δͱϧʔϓճ਺͕૿͑ͯॏ͘ͳΔ
     w ߋ৽͕ܹ͘͠*/%&9ͷஅยԽ͕ൃੜͨ͠
 ˠ61%"5&΍%&-&5&ʹΑͬͯ*/%&9͕அยԽͯ͠஗͘ͳΔ
120. ࣮ફͰϋϚΔ᠘ %#ͷ໰୊͸๨Εͨࠒʹ΍ͬͯ͘Δ
121. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ
122. ·ͱΊ 3%#ͷ࢖Θͳ͍γεςϜ͸ແ͍
     ʢҰ෦ྫ֎͋Γʣ
123. ·ͱΊ 3%#ͷ஌ࣝ͸ण໋͕௕͍
124. ·ͱΊ 3%#ͷ஌ࣝ͸ण໋͕௕͍
     ˣ
     ֮͑Ε͹࢓ࣄͰ௕͍ؒ໾ʹཱͭ
125. ·ͱΊ %#ͷ໰୊͸ΫϦςΟΧϧ
     
126. ·ͱΊ %#ͷ໰୊͸ΫϦςΟΧϧ
     ˣ
     ͭ·Γղܾ͢Ε͹ӳ༤
127. ·ͱΊ ۪ऀ͸ܦݧʹֶͿ
     ݡऀ͸աڈʹֶͿ
128. ·ͱΊ 3%#͸ྺ࢙͕௕͍
     
129. ·ͱΊ 3%#͸ྺ࢙͕௕͍
     ˣ
     ৭Μͳܦݧஊ͔Βֶ΂Δ
130. ·ͱΊ 3%#͸͍͍ͧɻ
131. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ