MySQLとPostgreSQLのこと(実践編)

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.


10.

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ31ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

11.

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ31ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

12.

What is it? d

13.


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`.ùsers` ( ìd` INT NOT NULL AUTO_INCREMENT
COMMENT '', `name` VARCHAR(45) NOT NULL COMMENT '', àge` INT NOT NULL COMMENT '', `created` DATETIME NOT NULL DEFAULT NOW () COMMENT '', PRIMARY KEY (ìd`) 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.

ࢀরΛ֎͢

67.

৽ͨʹมߋ෼Λ௥Ճ͢Δ

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.


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-͔Β "VUP7"$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.


122.

·ͱΊ 3%#ͷ࢖Θͳ͍γεςϜ͸ແ͍ ʢҰ෦ྫ֎͋Γʣ

123.

·ͱΊ 3%#ͷ஌ࣝ͸ण໋͕௕͍

124.

·ͱΊ 3%#ͷ஌ࣝ͸ण໋͕௕͍ ˣ ֮͑Ε͹࢓ࣄͰ௕͍ؒ໾ʹཱͭ

125.

·ͱΊ %#ͷ໰୊͸ΫϦςΟΧϧ

126.

·ͱΊ %#ͷ໰୊͸ΫϦςΟΧϧ ˣ ͭ·Γղܾ͢Ε͹ӳ༤

127.

·ͱΊ ۪ऀ͸ܦݧʹֶͿ ݡऀ͸աڈʹֶͿ

128.

·ͱΊ 3%#͸ྺ࢙͕௕͍

129.

·ͱΊ 3%#͸ྺ࢙͕௕͍ ˣ ৭Μͳܦݧஊ͔Βֶ΂Δ

130.

·ͱΊ 3%#͸͍͍ͧɻ

131.

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ

MySQLとPostgreSQLのこと(実践編)

MySQLとPostgreSQLのこと(実践編)

Want more?

Transcript