MySQL & PostgreSQL

ࠓͦ͜஌Γ͍ͨɺ 2େOSSσʔλϕʔεͷ MySQLͱPostgreSQLͷҧ͍ 044ίϯιʔγΞϜɹσʔλϕʔε෦ձɹओ࠵ηϛφʔ ʮΦʔϓϯιʔεσʔλϕʔεൺֱηϛφʔʯ

What is it? MySQLͱPostgreSQL

What is it? ࠓ͸2ڧ࣌୅ʢͦʔ͍ͩௐ΂

What is it? ଟ෼ɺଟ͘ͷਓ͕࢖͍ͬͯΔ͸ͣ

What is it? ·ͨ͜Ε͔Β࢖͏ػձ͕ଟʑ͋Δ͸ͣ

What is it? ͦΜͳਓͨͪͷͨΊʹ ࠓ೔͔Β࢖͑Δϊ΢ϋ΢Λʂ

What is it?

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ ɹɹɹ೔ຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

Mackerel

͸ͯͳ͸஥ؒΛ୳ͯ͠·͢ curl -sIL mackerel.io | grep engineer

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ ɹɹɹ೔ຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

MySQL .Z42-ͷಛ௃

MySQL .Z42-ͷಛ௃ ˣ γϯϓϧɾߴ଎

MySQL ߋ৽ܕΞʔΩςΫνϟ

MySQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ

MySQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ ಉ͡ߦΛߋ৽͢Δ

MySQL ετϨʔδΤϯδϯ

MySQL Ҿ༻ݩɿIUUQXXXTMJEFTIBSFOFUZPLVIBOEMFS !ZPLV͞Μɺ͍ͭ΋͋Γ͕ͱ͏͍͟͝·͢ɻ

MySQL ετϨʔδΤϯδϯͱ͸

MySQL ετϨʔδΤϯδϯͱ͸ ˣ େମσʔλͷಡΈॻ͖͢Δͱ͜Ζ

MySQL ͔͜͜Βઌ͸*OOP%#ͷ࿩ .Z*4".ʁ஌Βͳ͍ࢠͰ͢Ͷʢന໨

MySQL γϯϓϧɾߴ଎

MySQL γϯϓϧɾߴ଎ ˣ */%&9͕ޮ͍͍ͯΔࣄ͕େࣄ

MySQL #5SFF*/%&9

MySQL #5SFF*/%&9 .Z42-͸جຊతʹ͜Ε ʢ35SFFʹ͍ͭͯ͸ࠓճ৮Εͳ͍ʣ

MySQL Ҿ༻ݩɿIUUQTKBXJLJQFEJBPSHXJLJ##&$" ΢ΟΩϖσΟΞʮ# ໦ʯ

MySQL w 8)&3&۟Ͱ࢖͏ w (3061#:۟Ͱ΋࢖͏ w 03%&3#:۟Ͱ΋࢖͏

MySQL w 8)&3&۟Ͱ࢖͏ w (3061#:۟Ͱ΋࢖͏ w 03%&3#:۟Ͱ΋࢖͏ ݕࡧશൠͰ࢖͏

MySQL w ॏෳ͕গͳ͍΄͏͕ྑ͍ w ݁Ռ͕গͳ͍΄͏͕ྑ͍ w ར༻͢Δ*/%&9͸̍5"#-&ʹݸ

MySQL w ॏෳ͕গͳ͍΄͏͕ྑ͍ w ݁Ռ͕গͳ͍΄͏͕ྑ͍ w ར༻͢Δ*/%&9͸̍5"#-&ʹݸ .Z42-ͷେ͖ͳಛ௃ͷҰͭ ʢΠϯσοΫε݁߹ʹ͍ͭͯ͸ࠓճ৮Εͳ͍ʣ

MySQL ࣮ߦܭըΛݟΔ

MySQL .Z42-8PSLCFODI

MySQL .Z42-8PSLCFODI ˣ ແྉͰ࢖͑Δ

MySQL 0SBDMFΞΧ΢ϯτෆཁʂʂ

࣮ߦܭը 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;

MySQL αϒΫΤϦͱ૬ؔαϒΫΤϦ

αϒΫΤϦ —— 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

૬ؔαϒΫΤϦ ——஗͍૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT
age FROM users WHERE id BETWEEN 10 AND 100000)

MySQL .Z42-͔Β."5&3*"-*;&%ʹ มΘΓɺ+0*/ͷΑ͏ͳڍಈʹͳͬͨ ੲΑΓૣ͘ͳͬͨ

૬ؔαϒΫΤϦ ——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)

MySQL ૬ؔαϒΫΤϦ͸஗͍ ৔߹͕ଟ͍

MySQL ૬ؔαϒΫΤϦ͸஗͍ ˣ +0*/ʹॻ͖׵͑Δ

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'

MySQL +0*/͸ֻ͚ࢉ

MySQL +0*/͸ֻ͚ࢉ ˣ ߦ☓ߦ͸ߦ

MySQL .Z42-ʹ͸ +0*/ͷΞϧΰϦζϜ͸Ұ͔ͭ͠ͳ͍

MySQL .Z42-ʹ͸ +0*/ͷΞϧΰϦζϜ͸Ұ͔ͭ͠ͳ͍ ܾͯ͠ಘҙͳΘ͚Ͱ͸ແ͍ +0*/͸ϋΠίετͳΫΤϦ

MySQL w ग़དྷΔ͚ͩখ͔ͯ͘͞͠Β+0*/ w ෆཁͳ+0*/͸ආ͚Δ w */%&9Λར༻ͨ͠+0*/Λ͢Δ

MySQL γϯϓϧɾߴ଎

MySQL γϯϓϧɾߴ଎ ˣ ೉͍͠ࣄΛ͠ͳ͍ ઃܭྗ͕ॏཁ

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔҧ͍ ̑ɹ·ͱΊ

PostgreSQL 1PTUHSF42-ͷಛ௃

PostgreSQL 1PTUHSF42-ͷಛ௃ ˣ ߴػೳ

PostgreSQL ௥هܕΞʔΩςΫνϟ

PostgreSQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ

ࢀরΛ֎͢

৽ͨʹมߋ෼Λ௥Ճ͢Δ

PostgreSQL ͭ·Γɺߋ৽͸.Z42-ΑΓෆར ʢ%&-&5&͸.Z42-΋ࣅͨΑ͏ͳࣄͯ͠Δʣ

PostgreSQL ͔͠͠ɺখ೉͍͜͠ͱ͸ಘҙ

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ ͭͷΫΤϦ಺Ͱෳ਺࢖͑Δ

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ #USFFɺ(*/ɺࣜ*/%&9ͳͲγʔϯʹ߹Θͤͯ ෳ਺ͷ*/%&9Λར༻Ͱ͖Δ

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ ࢖͑Δ42-ͷߏจ΋ଟ͍ ྫ͑͹΢Πϯυ΢ؔ਺ͳͲ

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ /FTUFE-PPQ+PJO 4PSU.FSHF+PJO )BTI+PJO

ෳ਺ར༻ͨ͠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';

PostgreSQL

ࣜINDEX ——ࣜINDEXΛ࡞੒ CREATE INDEX test_idx ON users (substr(name,10,12)); ——ࣜͷ݁ՌͰINDEX͕ޮ͘ SELECT
name FROM users WHERE substr(name,10,12) = '10'

PostgreSQL

૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age
FROM users WHERE id BETWEEN 10 AND 100000)

PostgreSQL

PostgreSQL ΦϓςΟϚΠβ͕ αϒΫΤϦΛ+0*/ʹॻ͖׵͑ͯΔ

૬ؔαϒΫΤϦ ——INDEXΛར༻ͨ͠αϒΫΤϦ SELECT * FROM users WHERE id IN (SELECT
id FROM users WHERE id BETWEEN 10 AND 100000)

PostgreSQL ΦϓςΟϚΠβ͕+0*/ʹʢ͈́

૬ؔαϒΫΤϦ ——σϞͰINDEXΛ࢖ͬͯ͘Εͳ͍ͷͰINDEXΛ༏ઌతʹར༻͢ΔΑ͏ʹࢦఆ SET ENABLE_SEQSCAN=OFF; SELECT * FROM users WHERE id
IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)

PostgreSQL

PostgreSQL 1PTUHSF42-ͷ૬ؔαϒΫΤϦ͸ ൺֱత଎͍

΢Πϯυ΢ؔ਺ SELECT rank() OVER (PARTITION BY age ORDER BY id)
, * FROM users WHERE age BETWEEN 10 AND 30 LIMIT 100

PostgreSQL

PostgreSQL ෳࡶͳूܭ͸ಘҙ

PostgreSQL ΢Πϯυ΢ؔ਺ʹ͍ͭͯ ߹ΘͤͯಡΉͱྑ͍ࢿྉ 8FCΤϯδχΞ͕1PTUHSF42-ΛબͿͭͷཧ༝ IUUQXXXTMJEFTIBSFOFU4PVEBJ4POFXFCQPTUHSFTRM

PostgreSQL ෳࡶͳूܭ͸ಘҙ ˣ Ͱ΋3%#ͷجຊ͸.Z42-ͱҰॹ

PostgreSQL ෳࡶͳूܭ͸ಘҙ ˣ Ͱ΋3%#ͷجຊ͸.Z42-ͱҰॹ ਖ਼نԽ͕ॏཁ */%&9Λར༻͠ͳ͍ͱ஗͍ +0*/͸ϋΠίετͳΫΤϦ ࣮ߦܭը͸ݟΕΔΑ͏ʹͳΖ͏ʂ

࣮ફͰϋϚΔ᠘ աڈͷ৘ใʢϊΠζʣ

࣮ફͰϋϚΔ᠘ աڈͷ৘ใʢϊΠζʣ ˣ (PPHMFݕࡧͷݹ͍هࣄ΍ ޱ఻ʹ࿭Θ͞ΕΔ

࣮ફͰϋϚΔ᠘ աڈͷ৘ใʢϊΠζʣ ˣ (PPHMFݕࡧͷݹ͍هࣄ΍ ޱ఻ʹ࿭Θ͞ΕΔ 3%#͕ۤखͳͳઌഐ΍্͕࢘ ෇͍͕ͨ࣌ͱͯ΋ةݥ

࣮ફͰϋϚΔ᠘ w"-5&3จͰϩοΫΛऔΔʢ.Z42-ʣ w7"$66.͸खಈʢ1PTUHSF42-ʣ

࣮ફͰϋϚΔ᠘ w"-5&3จͰϩοΫΛऔΔʢ.Z42-ʣ w7"$66.͸खಈʢ1PTUHSF42-ʣ .Z42-͔ΒΦϯϥΠϯ "-5&3จΛαϙʔτ

࣮ફͰϋϚΔ᠘ w"-5&3จͰϩοΫΛऔΔʢ.Z42-ʣ w7"$66.͸खಈʢ1PTUHSF42-ʣ 1PTUHSF42-͔Β "VUP7"$66.Λαϙʔτ

࣮ફͰϋϚΔ᠘ ࣗ෼ͷ࢖͍ͬͯΔόʔδϣϯΛ ͔ͬ͠Γ֬ೝ͢Δ͜ͱ

࣮ફͰϋϚΔ᠘ ग़དྷΔ͚ͩ৽͍͠όʔδϣϯ͕ྑ͍ ϝδϟʔόʔδϣϯʹؔͯ͠͸ ೥ޙ͙Β͍ͷόʔδϣϯ͕͍͍ײ͡

࣮ફͰϋϚΔ᠘ ৽ػೳΛ࢖͏

࣮ફͰϋϚΔ᠘ ৽ػೳΛ࢖͏ ˣ 3%#ͷ৽ػೳ͸஍ཕʢόάʣΛ ౿Ή֮ޛ͕ඞཁ

࣮ફͰϋϚΔ᠘ ৽ػೳΛ࢖͏ ˣ 3%#ͷ৽ػೳ͸஍ཕʢόάʣΛ ౿Ή֮ޛ͕ඞཁ خʑͱͯ͠஍ཕΛ౿Έൈ͘Α͏ʹͳͬͨΒ ͦΕ͸Ұྲྀͷূʢଟ෼

࣮ફͰϋϚΔ᠘ ࢥ͍ࠐΈ

࣮ફͰϋϚΔ᠘ ࢥ͍ࠐΈ ˣ ޓ׵ੑͷແ͍มߋͰ ΧδϡΞϧʹࢮ͵

࣮ફͰϋϚΔ᠘ ࢥ͍ࠐΈ ˣ ޓ׵ੑͷແ͍มߋͰ ΧδϡΞϧʹࢮ͵ όʔδϣϯҧ͍΍ҧ͏%#Λ ࢖ͬͨ࣌ʹΑ͘ൃੜ͢Δ

࣮ફͰϋϚΔ᠘ ֎෦Ωʔ੍໿ͷҧ͍

࣮ફͰϋϚΔ᠘ .Z42-͸֎෦Ωʔ੍໿ͷ ਌ʹ΋ࢠʹ΋*/%&9Λ࡞Δ

࣮ફͰϋϚΔ᠘ 1PTUHSF42-͸֎෦Ωʔ੍໿ʹΑΔ */%&9Λ࡞Βͳ͍

࣮ફͰϋϚΔ᠘ 1PTUHSF42-͸ */%&9Λ࡞Βͳ͍ Ϛελʔදଆʹ͸ϢχʔΫ੍໿͕ඞਢ ϢχʔΫ੍໿ΛϢχʔΫ*/%&9Λੜ੒͢Δ ͔͠͠ࢠଆ͸*/%&9͕͋Δͱ͸ݶΒͳ͍

࣮ફͰϋϚΔ᠘ τϥϯβΫγϣϯ෼཭Ϩϕϧ ໊લ ෼཭Ϩϕϧ આ໌ 4&3*"-*;"#-& ͍ͭ͞Α ௚ྻతʹॲཧ 3&1&"5"#-&3&"% ڧ͍
ಡΈऔΓର৅ͷσʔλ ΛৗʹಡΈऔΔ 3&"%$0..*55&% ·͊·ڧ͍ ֬ఆͨ͠࠷৽σʔλΛ ৗʹಡΈऔΔ 3&"% 6/$0..*55&% ऑ͍ ଞͷॲཧʹΑͬͯߦΘ Ε͍ͯΔɺॻ͖͔͚ͷ σʔλ·ͰಡΈऔΔɻ

ಡΈऔΓର৅ͷσʔλ ΛৗʹಡΈऔΔ 3&"%$0..*55&% ·͊·ڧ͍ ֬ఆͨ͠࠷৽σʔλΛ ৗʹಡΈऔΔ 3&"% 6/$0..*55&% ऑ͍ ଞͷॲཧʹΑͬͯߦΘ Ε͍ͯΔɺॻ͖͔͚ͷ σʔλ·ͰಡΈऔΔɻ .Z42-ͷσϑΥϧτ͸͜͜

ಡΈऔΓର৅ͷσʔλ ΛৗʹಡΈऔΔ 3&"%$0..*55&% ·͊·ڧ͍ ֬ఆͨ͠࠷৽σʔλΛ ৗʹಡΈऔΔ 3&"% 6/$0..*55&% ऑ͍ ଞͷॲཧʹΑͬͯߦΘ Ε͍ͯΔɺॻ͖͔͚ͷ σʔλ·ͰಡΈऔΔɻ 1PTUHSF42- 0SBDMF%# 42-4FSWFS ͷσϑΥϧτ͸͜͜

ಡΈऔΓର৅ͷσʔλ ΛৗʹಡΈऔΔ 3&"%$0..*55&% ·͊·ڧ͍ ֬ఆͨ͠࠷৽σʔλΛ ৗʹಡΈऔΔ 3&"% 6/$0..*55&% ऑ͍ ଞͷॲཧʹΑͬͯߦΘ Ε͍ͯΔɺॻ͖͔͚ͷ σʔλ·ͰಡΈऔΔɻ 1PTUHSF42- 0SBDMF%# 42-4FSWFS ͷσϑΥϧτ͸͜͜ ϑΝϯτϜϦʔυ΍μʔςΟϦʔυͳͲͷ࿩͸ࠓ೔͸͠·ͤΜ ʮ͑ʁͳʹͦΕʯͬͯਓ͸ࠓ͙͢άάοͯษڧͨ͠ํ͕͍͍Ͱ͢

࣮ફͰϋϚΔ᠘ wυΩϡϝϯτΛݟΔ w࣮ߦܭըΛݟΔ wਪଌΑΓ΋ܭଌ

࣮ફͰϋϚΔ᠘ %#ͷ໰୊͸๨Εͨࠒʹ΍ͬͯ͘Δ

·ͱΊ ͭͷ%#Λ஌Δ͜ͱͰ ҧ͍͔Βݸੑ͕ݟ͑ͯ͘Δ

·ͱΊ %#ͷݸੑΛ஌Δ͜ͱͰ ઃܭͷ෯͕޿͕Δ

·ͱΊ ઃܭͷ෯͕޿͕Δ͜ͱͰ ࢓ࣄͷ෯΋޿͕Δ

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

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

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

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

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

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

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

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

·ͱΊ 3%#͸͍͍ͧɻ

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

MySQL & PostgreSQL

MySQL & PostgreSQL

More Decks by soudai sone

Other Decks in Technology

Featured

Transcript