Tweet
Tweet

Slide 1

Slide 1 text

RDBMS ϒʔτΩϟϯϓ #hatenaintern2022 1

Slide 2

Slide 2 text

͸͡Ίʹ ϒʔτΩϟϯϓͱ͸ • ޙ൒ύʔτͰࠔΒͳ͍͜ͱΛ໨తʹ • ֓೦͔ΒͰ͸ͳ͘ɺ۩ମྫ΍ςΫχοΫத৺ʹ • ݁ߏۦ͚଍ؾຯͳͷͰɺޙͰௐ΂Δͱ͔͔ͬΓͷҰͭʹͯ͠΋ Β͑Δͱ޾͍Ͱ͢ #hatenaintern2022 2

Slide 3

Slide 3 text

͸͡Ίʹ • ͸ͯͳͰ͸ओʹ MySQL ͱ PostgreSQL ͱ͍͏ DBMS ΛΑ͘ ࢖͍ͬͯ·͢ • ޙ൒ύʔτͷػೳ։ൃͰ͜ΕΒΛ࢖͏ࡍʹɺ0 ͔Βௐ΂Δ͜ͱ ͕ͳ͍Α͏ʹجૅతͳ෦෼Λઆ໌͠·͢ #hatenaintern2022 3

Slide 4

Slide 4 text

͜ͷϒʔτΩϟϯϓͷΰʔϧ MySQL ΍ PostgreSQL ΛͳΜͱͳ͘ѻ͑ΔΑ͏ʹͳΔ • σʔλΛ֨ೲग़དྷΔΑ͏ʹͳΔ • σʔλΛอଘɾࢀরɾߋ৽ɾ࡟আ͕ग़དྷΔ • ςʔϒϧઃܭͷߟ͑ํͱύϑΥʔϚϯε͕ͳΜͱͳ͘Θ͔Δ #hatenaintern2022 4

Slide 5

Slide 5 text

͜ͷߨٛͰѻ͑ͳ͍͜ͱ • σʔλϕʔεͷ಺෦ͷ࢓૊Έ • ৄ͍͠ςʔϒϧઃܭ΍ SQL ͷςΫχοΫ • ύϑΥʔϚϯεͷৄ͍͠࿩ • MySQL ͱ PostgreSQL Ҏ֎ͷ͜ͱ #hatenaintern2022 5

Slide 6

Slide 6 text

໨࣍ • RDBMS ʹ͍ͭͯ • جຊฤ • Ԡ༻ฤ • ෇࿥ #hatenaintern2022 6

Slide 7

Slide 7 text

RDBMS ʹ͍ͭͯ #hatenaintern2022 7

Slide 8

Slide 8 text

RDBMS ͱ͸ RDBMS = Relational Database Management System • Relational Database • ؔ܎σʔλϕʔε • Database Management System • σʔλϕʔε؅ཧγεςϜ ؔ܎σʔλϕʔεͷσʔλϕʔε؅ཧγεςϜ #hatenaintern2022 8

Slide 9

Slide 9 text

ؔ܎σʔλϕʔε ؔ܎Ϟσϧʹج͍ͮͨσʔλϕʔε ؔ܎Ϟσϧͱ͸ʁ → σʔλΛؔ܎ͱͯ͠දݱ͠औΓѻ͏σʔλͷදݱํ๏ ؔ܎σʔλϕʔεͰ͸ɺ ݟͨ໨ͱͯ͠͸දʢςʔϒϧʣͷܗࣜͰσʔλΛදݱ͢Δ #hatenaintern2022 9

Slide 10

Slide 10 text

ؔ܎σʔλϕʔε #hatenaintern2022 10

Slide 11

Slide 11 text

ؔ܎σʔλϕʔε ؔ܎σʔλϕʔεҎ֎ͷσʔλϕʔεγεςϜ΋͋Δ͕ɺ͜ͷߨٛͰ͸ѻΘͳ͍ ྫ͑͹͜͏͍͏΋ͷ͕͋Γ·͢: #hatenaintern2022 11

Slide 12

Slide 12 text

σʔλϕʔε؅ཧγεςϜ σʔλϕʔε؅ཧγεςϜʢDBMSʣͷ໾ׂ • σʔλΛந৅Խ͠ɺޮ཰Α͘อଘ • ༻్ʹ͋Θͤͯ࠷దͳߏ଄ͰσʔλΛอଘ͢Δ • ֎෦͔ΒͷࢦࣔΛड͚෇͚ͯσʔλΛૢ࡞͢ΔɺΠϯλʔϑΣʔεͷ໾ׂ • σʔλΛ࢖͏ਓ͸σʔλ͕ͲͷΑ͏ʹ֨ೲ͞Ε͍ͯΔ͔ҙࣝ͠ͳͯ͘΋͍͍ • σʔλΛݎ࿚ʹѻ͏࢓૊ΈΛఏڙ͢Δ • ੔߹ੑΛอͭ • ฒྻΞΫηε͞Εͯ΋ᴥᴪ͕ى͖ͳ͍Α͏ʹ͢Δ • σʔλͷଛࣦΛ๷͙ #hatenaintern2022 12

Slide 13

Slide 13 text

جຊฤ #hatenaintern2022 13

Slide 14

Slide 14 text

༻ޠ ༻ޠ ҙຯ ςʔϒϧ σʔλͷू߹ Ϩίʔυ ςʔϒϧͷσʔλͷ૊ 1 ߦ 1 ߦ ΧϥϜ ςʔϒϧͷσʔλͷଐੑ εΩʔϚ ςʔϒϧͷߏ଄ ʢͲΜͳΧϥϜ͕͋Δ͔ɺΧϥϜʹ͸Ͳ Μͳ஋͕ೖΔ͔ɺͳͲʣ #hatenaintern2022 14

Slide 15

Slide 15 text

༻ޠ #hatenaintern2022 15

Slide 16

Slide 16 text

SQL RDBMS ʹ໰͍߹Θͤʢૢ࡞ʣΛߦ͏ͨΊͷݴޠ • DDL (Data Definition Language) • εΩʔϚΛఆٛ͢Δ SQL • DML (Data Manipulation Language) • σʔλΛૢ࡞͢Δ SQL • ݕࡧɺߋ৽ͳͲ • DCL (Data Control Language) • σʔλ΁ͷΞΫηε੍ޚʹؔ͢Δ SQL • ͜ͷߨٛͰ͸঺հ͠ͳ͍ SQL Ͱॻ͔Εͨɺσʔλϕʔε΁ͷ໰͍߹ΘͤจΛɺʮΫΤϦʯͱݺͿ #hatenaintern2022 16

Slide 17

Slide 17 text

SQL SQL ʹ͸ن͕֨͋Δ͕ɺͦΕʹͲ͜·Ͱ४ڌ͍ͯ͠Δ͔͸ DBMS ʹΑͬͯ·ͪ·ͪ DBMS ͸ͦΕͧΕಠ֦ࣗுʢํݴʣΛ͍࣋ͬͯͯɺޓ׵ੑ͕ͳ͍ ͜ͱ΋͋Δ ͜ͷϒʔτΩϟϯϓͰ΋ɺʮMySQL Ͱ͸͜ͷॻ͖ํ͕Ͱ͖·͢ʯ ͷΑ͏ͳ࿩͕ग़͖ͯͨΒํݴͷ͜ͱͩͱࢥ͍ͬͯͩ͘͞ #hatenaintern2022 17

Slide 18

Slide 18 text

σʔλఆٛ • DDL (Data Definition Language) ← ͜Ε • εΩʔϚΛఆٛ͢Δ SQL • DML (Data Manipulation Language) • σʔλΛૢ࡞͢Δ SQL • ݕࡧɺߋ৽ͳͲ #hatenaintern2022 18

Slide 19

Slide 19 text

CREATE TABLE จ1 • ςʔϒϧΛఆٛ͢Δߏจ • CREATE TABLE ςʔϒϧ໊ (ςʔϒϧఆٛ); -- `--` ͸ίϝϯτ -- id, name, created_at ΧϥϜΛؚΉςʔϒϧΛɺ -- users ͱ͍͏໊લͰఆٛ CREATE TABLE users ( id BIGINT NOT NULL, name VARCHAR(32) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); 1 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 13.1.20 CREATE TABLE εςʔτϝϯτ PostgreSQL: Documentation: 14: CREATE TABLE #hatenaintern2022 19

Slide 20

Slide 20 text

CREATE TABLE จ id BIGINT NOT NULL, • BIGINT ͷ෦෼: ΧϥϜͷσʔλܕ • NOT NULL ͷ෦෼: ΧϥϜͷ੍໿ #hatenaintern2022 20

Slide 21

Slide 21 text

σʔλܕ #hatenaintern2022 21

Slide 22

Slide 22 text

σʔλܕ • ܕʹΑͬͯอଘʹ࢖ΘΕΔ༰ྔ͕ҟͳΔͷͰେࣄ • ޙ͔Βม͑Δͷ΋େมͳ͜ͱ͕ଟ͍ • Α͘࢖͏΋ͷʹ͍ͭͯ঺հ • ਺஋ܕ • จࣈྻܕ • ೔෇ͱ࣌ؒܕ #hatenaintern2022 22

Slide 23

Slide 23 text

਺஋ܕ • ੔਺ • TINYINT , INTEGER , BIGINT ͳͲ • ӈʹߦ͘΄Ͳଟ͘ͷܻΛอଘͰ͖Δ • MySQL Ͱ͸ UNSIGNED ଐੑΛ෇༩͢Δͱූ߸ແ͠ͰอଘͰ͖ΔΑ͏ʹͳΔʢBIGINT UNSIGNEDʣ • ଐੑ: ΧϥϜͷಛੑɻ͚ͭΒΕΔଐੑ͸ɺσʔλܕʹΑͬͯҟͳΔ • ුಈখ਺఺ • FLOAT , DOUBLE • ݻఆখ਺఺ • DECIMAL , NUMERIC #hatenaintern2022 23

Slide 24

Slide 24 text

਺஋ܕ • ܻ͋;ΕʹؾΛ͚ͭΔඞཁ͕͋Δ • Ϩίʔυ͝ͱʹϢχʔΫʹ͚ͭΔ id ͳͲ͸ BIGINT UNSIGNED ʹ ͓ͯ͘͠ͱ҆৺ • 18446744073709551615ʢ1844ژʣ·ͰอଘͰ͖Δ #hatenaintern2022 24

Slide 25

Slide 25 text

จࣈྻܕ • ଟ͘ͷ৔߹Ͱ VARCHAR(n) Λ࢖͏ • Մม௕จࣈྻͰ 0 ʙ 65,535 όΠτอଘՄೳ • VARCHAR(32) ͷΑ͏ʹɺอଘ͢Δ࠷େจࣈ਺Λࢦఆ͢Δ • ͋Δఔ౓௕͍จࣈྻΛอଘ͢Δͱ͖ʹ͸ςΩετܕΛར༻ • MySQL Ͱ͸ TEXT, MEDIUMTEXT, LONGTEXT ͳͲ • PostgreSQL ʹ͸ TEXT ͚ͩ༻ҙ͞Ε͍ͯΔ #hatenaintern2022 25

Slide 26

Slide 26 text

೔෇ͱ࣌ؒܕ MySQL ͷ৔߹ • DATETIME • ೔෇ͱ࣌ؒͷ྆ํΛؚΉ • αϙʔτ͢Δൣғ͸ 1000-01-01 00:00:00 ʙ 9999-12-31 23:59:59 • λΠϜκʔϯͷ֓೦͕ͳ͍ • TIMESTAMP • ೔෇ͱ࣌ؒͷ྆ํΛؚΉ • αϙʔτ͢Δൣғ͸ 1970-01-01 00:00:01 UTC ʙ 2038-01-19 03:14:07 UTC • λΠϜκʔϯͷ֓೦͕͋Δ #hatenaintern2022 26

Slide 27

Slide 27 text

೔෇ͱ࣌ؒܕ PostgreSQL Ͱ͸ɺλΠϜκʔϯͷ༗ແ͸ ↓ ͷΑ͏ʹ۠ผ͢Δ • TIMESTAMP WITHOUT TIME ZONE • TIMESTAMP WITH TIME ZONE #hatenaintern2022 27

Slide 28

Slide 28 text

೔෇ͱ࣌ؒܕ ೔෇ͱ࣌ؒͷ྆ํΛؚΜͰ͸͍ͳ͍ܕ΋͋Δ ʢొ৔ස౓͸গͳ͍ʣ • DATE: ೔෇ͷΈ • TIME: ࣌ؒͷΈ #hatenaintern2022 28

Slide 29

Slide 29 text

੍໿ #hatenaintern2022 29

Slide 30

Slide 30 text

੍໿ • ΧϥϜʹೖΔσʔλʹର͢Δ੍ݶ • ҙਤ͠ͳ͍σʔλ͕ೖΓࠐ·ͳ͍Α͏ʹ • ੍໿ʹ൓͢ΔͱΤϥʔʹͳΔ #hatenaintern2022 30

Slide 31

Slide 31 text

੍໿ ྫ: ʮusers ςʔϒϧͷ id ͕ҰҙʹͳΔʯͱ͍͏੍໿Λ͚ͭͨঢ়ଶͩͱ...2 -- id = 1 ͷ user Λ௥Ճͯ͠ɺ੒ޭ͢Δ INSERT INTO users (id, name, created_at) VALUES (1, 'name1', NOW()); Query OK, 1 row affected (0.01 sec) -- ಉ͡ id ͷ஋ΛೖΕΑ͏ͱ͢ΔͱɺΤϥʔʹͳΔ INSERT INTO users (id, name, created_at) VALUES (1, 'name2', NOW()); ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY' 2 ΫΤϦͷԼʹॻ͍ͯ͋Δͷ͸ RDBMS ͔Βฦ͞Ε͖ͯͨΫΤϦͷ݁Ռɻجຊతʹ MySQL ͷ΋ͷΛషͬͯ͋ΔͷͰ PostgreSQL Ͱ࣮ߦ͢Δͱҧ͏݁Ռ͕ฦͬͯ͘Δͱࢥ͍·͕͢ɺݟͨ໨͕ҧ ͏͚ͩͰɺҙຯ͍ͯ͠Δ͜ͱ͸͍͍ͩͨಉ͡Ͱ͢ɻ #hatenaintern2022 31

Slide 32

Slide 32 text

੍໿ͷྫ • NOT NULL੍໿: ΧϥϜ͸ NULL Ͱ͋ͬͯ͸͍͚ͳ͍ • NULL ͸ɺ஋͕ܽଛ͍ͯ͠Δ͜ͱΛද͢ • UNIQUE ੍໿: ΧϥϜͷ஋͕ςʔϒϧ಺ͰҰҙͰͳ͚Ε͹ͳΒͳ͍ • ͨͩ͠ NULL ͸͍ͭ͋ͬͯ͘΋ڐ͞ΕΔͷͰ஫ҙ • PRIMARY KEY ੍໿: ΧϥϜͷ஋͕ςʔϒϧ಺ͰϨίʔυΛҰҙʹࣝผͰ͖ͳ͚Ε͹ ͍͚ͳ͍ • FOREIGN KEY ੍໿: ඥ͍͍ͮͯΔςʔϒϧؒͰ੔߹ੑ͕औΕ͍ͯͳ͚Ε͹͍͚ͳ͍ #hatenaintern2022 32

Slide 33

Slide 33 text

PRIMARY KEY (ओΩʔ) • ςʔϒϧ಺ͰϨίʔυΛҰҙʹࣝผͰ͖ΔΧϥϜ • 1ͭͷςʔϒϧͰ1ͭࢦఆͰ͖Δ • PRIMARY KEY ੍໿͸ɺUNIQUE ੍໿ͱ NOT NULL ੍໿Λซͤ࣋ͬͯ ͍Δͱ͍͑Δ • PRIMARY KEY͸ΠϯσοΫεʢޙड़ʣͱͯ͠΋࢖͑Δ #hatenaintern2022 33

Slide 34

Slide 34 text

վΊͯ users ςʔϒϧͷఆٛΛݟΔ CREATE TABLE users ( id BIGINT NOT NULL, name VARCHAR(32) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); users ͱ͍͏໊લͷςʔϒϧΛఆٛ͠ɺҎԼͷΧ ϥϜΛ࣋ͨͤΔ • id • ੔਺ͷ஋͕ೖΓɺ͜ͷ஋ʹΑͬͯςʔϒϧ಺ ͷϨίʔυΛҰҙʹࣝผͰ͖Δ • name • ࠷େ32จࣈͷจࣈྻͷ஋͕ೖΓɺNULL Ͱͳ ͍͔ͭσʔλ͕ॏෳ͢Δ͜ͱ͕ͳ͍ • created_at • ࣌ࠁͷ஋͕ೖΓɺNULL Ͱ͸ͳ͍ #hatenaintern2022 34

Slide 35

Slide 35 text

σʔλૢ࡞ • DDL (Data Definition Language) • εΩʔϚΛఆٛ͢Δ SQL • DML (Data Manipulation Language) ← ͜Ε • σʔλΛૢ࡞͢Δ SQL • ݕࡧɺߋ৽ͳͲ #hatenaintern2022 35

Slide 36

Slide 36 text

CRUD • σʔλૢ࡞͢Δࡍͷجຊػೳ • ͦΕͧΕͷૢ࡞ͷ಄จࣈ͔Β • Create (௥Ճ) • Read (ࢀর) • Update (ߋ৽) • Delete (࡟আ) #hatenaintern2022 36

Slide 37

Slide 37 text

Ҏ߱͸ҎԼͷςʔϒϧ͕͋ΔલఏͰ ϢʔβʔͱɺϢʔβʔ͕ॴ༗͢ΔϒϩάΛ؅ཧ͍ͯ͠Δঢ়ଶ CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL, name VARCHAR(32) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id) ); CREATE TABLE blogs ( id BIGINT NOT NULL, -- ϒϩάΛ͍࣋ͬͯΔϢʔβʔͷ id user_id BIGINT NOT NULL, title VARCHAR(50) NOT NULL, description VARCHAR(512), PRIMARY KEY (id) ); #hatenaintern2022 37

Slide 38

Slide 38 text

INSERT จ (Create)3 ςʔϒϧʹϨίʔυΛ௥Ճ͢Δ INSERT จ INSERT INTO users (id, name, created_at) VALUES (1, '͸ͯͳଠ࿠', '2022-08-17 12:00:00'); Query OK, 1 row affected (0.01 sec) MySQLɺPostgreSQL ͰͦΕͧΕผͷॻ͖ํ΋͋Δ 3 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 13.2.6 INSERT εςʔτϝϯτ PostgreSQL: Documentation: 14: INSERT #hatenaintern2022 38

Slide 39

Slide 39 text

SELECT จ (Read)4 ςʔϒϧ͔ΒϨίʔυΛݕࡧ͢Δ SELECT จ -- users ςʔϒϧͷϨίʔυΛશ݅ݕࡧʢऔಘʣ SELECT * FROM users; * ͸ʮશͯͷΧϥϜʯͱ͍͏ҙຯɻ* ͷ୅ΘΓʹݸผͷΧϥϜ໊Λࢦఆ͢Δ͜ͱ΋Ͱ͖Δ +----+-----------------+---------------------+ | id | name | created_at | +----+-----------------+---------------------+ | 1 | ͸ͯͳଠ࿠ | 2022-08-17 12:00:00 | +----+-----------------+---------------------+ 1 row in set (0.00 sec) 4 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 13.2.10 SELECT εςʔτϝϯτ PostgreSQL: Documentation: 14: SELECT #hatenaintern2022 39

Slide 40

Slide 40 text

SELECT จ (Read) ߜΓࠐΈʹ͸ WHERE ۟Λ෇͚Δʢޙड़ʣ -- id ͕ 1 ͷϨίʔυΛݕࡧ SELECT * FROM users WHERE id = 1; +----+-----------------+---------------------+ | id | name | created_at | +----+-----------------+---------------------+ | 1 | ͸ͯͳଠ࿠ | 2022-08-17 12:00:00 | +----+-----------------+---------------------+ 1 row in set (0.00 sec) #hatenaintern2022 40

Slide 41

Slide 41 text

UPDATE จ (Update)5 طʹଘࡏ͢ΔϨίʔυΛߋ৽͢Δ UPDATE จ -- ߋ৽͍ͨ͠ϨίʔυΛ WHERE ۟Ͱࢦఆ͢Δ UPDATE users SET name = '͸ͯͳೋ࿠' WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- ஫ҙ! WHERE ۟Ͱߋ৽ର৅Λࢦఆ͠ͳ͚Ε͹ɺςʔϒϧ಺ͷϨίʔυ͕શ݅ߋ৽͞ΕΔ UPDATE users SET name = '͸ͯͳೋ࿠'; 5 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 13.2.13 UPDATE εςʔτϝϯτ PostgreSQL: Documentation: 14: UPDATE #hatenaintern2022 41

Slide 42

Slide 42 text

DELETE จ (Delete)6 ϨίʔυΛ࡟আ͢Δ DELETE FROM users WHERE id = 1; Query OK, 1 row affected (0.01 sec) -- ஫ҙ! WHERE ۟Ͱ࡟আର৅Λࢦఆ͠ͳ͚Ε͹ɺςʔϒϧ಺ͷϨίʔυ͕શ݅࡟আ͞ΕΔ DELETE FROM users; 6 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 13.2.2 DELETE εςʔτϝϯτ PostgreSQL: Documentation: 14: DELETE #hatenaintern2022 42

Slide 43

Slide 43 text

σʔλͷऔಘํ๏ ໨త खஈ ৚݅Λ͚ͭͯߜΓࠐΈ WHERE ۟ ιʔτ ORDER BY ۟ औಘ݅਺ͷ੍ݶ LIMIT ۟ ϖʔδϯά LIMIT ۟ͱ OFFSET ۟Λ૊Έ߹ΘͤΔ άϧʔϐϯά GROUP BY ۟ ςʔϒϧಉ࢜ͷ݁߹ JOIN ۟ #hatenaintern2022 43

Slide 44

Slide 44 text

WHERE ۟ ৚݅Λ͚ͭͯߜΓࠐΉ SELECT * FROM users WHERE ͜͜ʹ৚݅Λॻ͘; #hatenaintern2022 44

Slide 45

Slide 45 text

ൺֱ ʮ౳͍͠ʯ͸ =ɺʮ౳͘͠ͳ͍ʯ͸ !=ʢ<> ͱ΋ॻ͔ΕΔʣ ϓϩάϥϛϯάݴޠͷ਺஋΍จࣈྻͷΑ͏ʹ >, <, >=, <= ͰൺֱͰ͖Δ -- id ͕ 1 ͷϨίʔυΛݕࡧ SELECT * FROM users WHERE id = 1; -- id ͕ 1 Ͱ͸ͳ͍ϨίʔυΛݕࡧ SELECT * FROM users WHERE id != 1; -- created_at ͕ 2022-08-17 12:00:00 ΑΓେ͖͍ʢΑΓޙͷʣϨίʔυΛݕࡧ SELECT * FROM users WHERE created_at > '2022-08-17 12:00:00'; ͨͩ͠ɺNULL ͸஋Ͱ͸ͳ͍ͷͰ column_name = NULL ͱॻ͍ͯ΋ NULL ͷϨίʔυ͸ಘΒΕͳ͍ɻ column_name IS NULL ͱॻ͘͜ͱɻ #hatenaintern2022 45

Slide 46

Slide 46 text

ൺֱ • AND ΍ OR Ͱෳ਺ͷ৚݅Λ૊Έ߹ΘͤΔ͜ͱ͕Ͱ͖Δ • ಉ͡ΧϥϜʹର͢Δ OR ͸ɺIN Ͱ·ͱΊͯදݱ͢Δ͜ͱ΋Ͱ͖Δ -- id͕ 1 ͔ 2 ͔ 3 ͷϨίʔυΛݕࡧ SELECT * FROM users WHERE id = 1 OR id = 2 OR id = 3; -- ্ͷ SQL Λ IN Λ࢖ͬͯॻ͘ SELECT * FROM users WHERE id IN (1, 2, 3); • LIKE: จࣈྻͷύλʔϯϚονϯά -- name ͕ "͸ͯͳ" ͔Β࢝·ΔϨίʔυΛݕࡧ -- `%` ͸ϫΠϧυΧʔυจࣈͰɺ೚ҙͷ0จࣈҎ্ͷจࣈྻΛද͢ SELECT * FROM users WHERE name LIKE '͸ͯͳ%'; #hatenaintern2022 46

Slide 47

Slide 47 text

ORDER BY ۟ • ORDER BY ΧϥϜ໊ [ASC/DESC]: ݕࡧ݁Ռͷιʔτ͕ग़དྷΔ • ASC ͸ঢॱɺDESC ͸߱ॱ • WHERE ۟ͱ૊Έ߹ΘͤΔͱ͖͸ɺWHERE ۟Λઌʹॻ͔ͳ͍ͱಈ͔ͳ͍ -- user ςʔϒϧͷϨίʔυΛ created_at ͷ߱ॱͰݕࡧ SELECT * FROM users ORDER BY created_at DESC; +----+-----------------+---------------------+ | id | name | created_at | +----+-----------------+---------------------+ | 2 | ͸ͯͳೋ࿠ | 2022-08-17 13:00:00 | | 1 | ͸ͯͳଠ࿠ | 2022-08-17 12:00:00 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec) #hatenaintern2022 47

Slide 48

Slide 48 text

ϖʔδϯά (LIMIT ۟ / OFFSET ۟) • LIMIT ۟: ݕࡧ݁Ռͷऔಘ݅਺ΛࢦఆͰ͖Δ • OFFSET ۟: ࢦఆͨ͠஋͚ͩݕࡧ݁ՌΛಡΈඈ͹͢ • ͜ͷ2ͭΛ૊Έ߹ΘͤͯϖʔδϯάػೳΛ࣮ݱͰ͖Δ -- users ςʔϒϧͷશϨίʔυ͔Β created_at ͷ߱ॱͰݕࡧ͠ɺ3ͷഒ਺෼ͷϨίʔυΛಡΈඈ͹্ͨ͠Ͱ3݅ͷݕࡧ݁ՌΛಘΔ -- 1ϖʔδ໨ SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 0; -- 2ϖʔδ໨ SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 3; -- 3ϖʔδ໨ SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 6; #hatenaintern2022 48

Slide 49

Slide 49 text

ϖʔδϯά (LIMIT ۟ / OFFSET ۟) #hatenaintern2022 49

Slide 50

Slide 50 text

ϖʔδϯά (LIMIT ۟ / OFFSET ۟) #hatenaintern2022 50

Slide 51

Slide 51 text

ϖʔδϯά (LIMIT ۟ / OFFSET ۟) #hatenaintern2022 51

Slide 52

Slide 52 text

ϖʔδϯά ͨͩ͠ɺLIMIT / OFFSET Λ࢖ͬͨϖʔδϯάʹ͸ҎԼͷΑ͏ͳσϝϦοτ͕͋Δ • ύϑΥʔϚϯε • OFFSET Ͱࢦఆ͞Εͨߦ਺·ͰḷΓண͘·Ͱʹશͯͷߦ਺Λ਺͑ͳ͚Ε͹͍͚ͳ͍ • σʔλ͕ͣΕΔ • ϖʔδϯάΛ͍ͯ͠Δ్தʹߦ͕ࠩ͠ࠐ·ΕͨΓ࡟আ͞ΕͨΓͨ͠ΒɺͣΕͯ͠·͏ • ྫ: 1ϖʔδ໨(1ʙ10݅໨)ΛΫΤϦ → 1݅໨ͷσʔλ͕࡟আ͞ΕΔ → 2ϖʔδ໨(11ʙ20݅໨)ΛΫ ΤϦ → ΋ͱ΋ͱͷ11݅໨͕ͲͪΒͷϖʔδʹ΋ݱΕͳ͍ ͜ΕΒͷσϝϦοτΛආ͚ΔͨΊʹɺ࣍ͷํ๏͕Α͘࢖ΘΕΔ #hatenaintern2022 52

Slide 53

Slide 53 text

ϖʔδϯά લϖʔδͷ࠷ऴߦͷ஋Λɺϖʔδͷ۠੾ΓͷΩʔͱͯ͠ ࢖͏ ྫ: 1ϖʔδ໨͸ id = 3 ͷϨίʔυ͕࠷ޙͩͬͨͷͰɺ2 ϖʔδ໨Λऔಘ͢Δͱ͖ʹ͸ id > 3 ͷ΋ͷΛऔಘ͢Δ SELECT * FROM table WHERE id > લͷϖʔδͷ࠷ऴߦͷid ORDER BY id ASC LIMIT 1ϖʔδ͋ͨΓͷ݅਺ ϝϦοτ • ύϑΥʔϚϯε • ΠϯσοΫεʢޙड़ʣ͕͋Ε͹ɺݱࡏͷϖʔδͷߦ ·Ͱૉૣ͘ඈ΂Δ • σʔλͷͣΕ • ى͜Βͳ͍ σϝϦοτ • ೚ҙͷϖʔδ·Ͱඈ΂ͳ͍ • 1ϖʔδ໨͔Β10ϖʔδ໨ʹߦ͘ɺͱ͍͏ͷ͸ෆՄೳ #hatenaintern2022 53

Slide 54

Slide 54 text

άϧʔϐϯά (GROUP BY ۟) • GROUP BY ۟: σʔλΛάϧʔϐϯά͢Δ ྫ: Ϣʔβʔ͕ϒϩάΛ͍͍ͭ࣋ͬͯ͘Δͷ͔ Λूܭ͢Δ blogs ςʔϒϧͷϨίʔυΛ user_id ͝ͱʹά ϧʔϐϯά͠ɺͦΕͧΕͷάϧʔϓ಺ͷ߹ܭ਺ Λग़͢ • COUNT(*) : ৚݅ʹ߹க͢ΔϨίʔυͷ਺Λද ࣔ͢Δ • AS : ΧϥϜʹผ໊ʢΤΠϦΞεʣΛ͚ͭΔ SELECT user_id, COUNT(*) AS blog_count FROM blogs GROUP BY user_id; +---------+------------+ | user_id | blog_count | +---------+------------+ | 1 | 1 | | 2 | 2 | +---------+------------+ 2 rows in set (0.00 sec) #hatenaintern2022 54

Slide 55

Slide 55 text

άϧʔϐϯά (GROUP BY ۟) #hatenaintern2022 55

Slide 56

Slide 56 text

ςʔϒϧಉ࢜ͷ݁߹ (JOIN ۟) • JOIN ۟: ҟͳΔςʔϒϧʹอଘ͞ΕͨσʔλΛ݁߹Ͱ͖Δ -- users ςʔϒϧͱ blogs ςʔϒϧΛ݁߹ͯ͠ɺϒϩάͷ࡞ऀͱλΠτϧͷҰཡΛग़͢ SELECT users.name AS user_name, blogs.title AS blog_title FROM blogs JOIN users ON blogs.user_id = users.id; +-----------------+--------------------------+ | user_name | blog_title | +-----------------+--------------------------+ | ͸ͯͳଠ࿠ | ଠ࿠ͷ೔ه | | ͸ͯͳೋ࿠ | ೋ࿠ͷ೔ه | | ͸ͯͳೋ࿠ | ೋ࿠ͷٕज़ϒϩά | +-----------------+--------------------------+ 3 rows in set (0.00 sec) ΫΤϦʹςʔϒϧ͕ෳ਺ొ৔͢Δ৔߹ɺςʔϒϧ໊.ΧϥϜ໊ ͰΧϥϜΛද͢ #hatenaintern2022 56

Slide 57

Slide 57 text

ςʔϒϧಉ࢜ͷ݁߹ (JOIN ۟) #hatenaintern2022 57

Slide 58

Slide 58 text

ςʔϒϧಉ࢜ͷ݁߹ (JOIN ۟) • blogs JOIN users : blog ςʔϒϧʹ user ςʔϒϧΛ݁߹ͤ͞Δ • ON blogs.user_id = users.id : blogs.user_id ͱҰக͢Δ஋ͷ users.id Λ࣋ͭϨίʔυΛ݁߹ͤ͞Δ • JOIN ʹ͸ RIGHT JOIN, INNER JOIN, OUTER JOIN ͳͲͷछྨ͕͋ Δ͕ɺ͜͜Ͱ͸৮Εͳ͍ #hatenaintern2022 58

Slide 59

Slide 59 text

Ԡ༻ฤ #hatenaintern2022 59

Slide 60

Slide 60 text

Ԡ༻ฤ ͜ΕΒͷ࿩୊Λগ͚ͣͭͩ͠঺հ • ςʔϒϧઃܭ • ύϑΥʔϚϯεͷ࿩ #hatenaintern2022 60

Slide 61

Slide 61 text

ςʔϒϧઃܭ #hatenaintern2022 61

Slide 62

Slide 62 text

ςʔϒϧઃܭ ϒϩάαʔϏεΛྫʹߟ͑ͯΈΔ • Ϣʔβʔ͕͍Δ • Ϣʔβʔ͸໊લΛ࣋ͭ • Ϣʔβʔ͸ϒϩάΛ࣋ͯΔ • ϒϩάʹ͸λΠτϧ͕෇͘ • ϒϩάʹ͸આ໌จ͕ॻ͚Δ • Ϣʔβʔ͸ϒϩάΛෳ਺࣋ͯΔ ͲΜͳςʔϒϧ(ද)ͰදͤΔ͔ʁ #hatenaintern2022 62

Slide 63

Slide 63 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά શ෦Ұͭͷςʔϒϧ(ද)Ͱॻ͍ͯΈΔ #hatenaintern2022 63

Slide 64

Slide 64 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ͜ΕͰ΋ՄೳͰ͸͋Δ͚Ͳ... • Ϣʔβʔ໊ͷ৘ใ͕ॏෳ͍ͯ͠Δ • ؒҧ͑ͯҰ෦͚ͩߋ৽ͯ͠͠·ͬͨΒ੔߹ੑ͕ͳ͘ͳͬͯ͠·͏ • γϯϓϧͳ͜ͱΛ͍͚ͨͩ͠ͳͷʹෳࡶͳΫΤϦΛॻ͘৔ॴ͕Ͱ͖ͯ͠·͏ • ྫ: Ϣʔβʔ໊ҰཡΛऔಘ͢ΔͨΊʹ͸ SELECT DISTINCT user_name FROM blogs;ͱ͍͏ΫΤϦʹͳΔ • ѻ͏৘ใ͕૿͑ͨΒͲ͏͢Δʁ • ྫ: ϒϩάʹهࣄΛෳ਺͍࣋ͨͤͨ • هࣄΧϥϜΛ௥Ճͨ͠Β͜ͷςʔϒϧͰ΋࣮ݱͰ͖Δ͕ɺهࣄͷ਺͚ͩϨίʔυ͕࡞ΒΕΔͷͰɺͦͷ෼ Ϣʔβʔɾϒϩάͷσʔλͷॏෳ͕૿͑Δ #hatenaintern2022 64

Slide 65

Slide 65 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ѻ͏৘ใʹண໨ͯ͠ςʔϒϧΛ෼ׂ • ʮϢʔβʔʯͱʮϒϩάʯʹண໨ͯ͠ɺͦΕͧΕΛςʔϒϧͰද ݱ͢Δ • users ςʔϒϧͱ blogs ςʔϒϧʹ෼ׂ͢Δ • ͦΕͧΕͷςʔϒϧʹɺߦΛҰҙʹಛఆͰ͖ΔΩʔΛ௥Ճ͢Δ • ͦͷΩʔΛ࢖ͬͯɺςʔϒϧಉ͕࢜ؔ࿈Λ࣋ͯΔΑ͏ʹ͢Δ #hatenaintern2022 65

Slide 66

Slide 66 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ςʔϒϧΛ෼ׂ͢Δ #hatenaintern2022 66

Slide 67

Slide 67 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ςʔϒϧʹߦΛಛఆͰ͖ΔΩʔΛ௥Ճ͢Δ #hatenaintern2022 67

Slide 68

Slide 68 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ͦͷΩʔΛ࢖ͬͯςʔϒϧಉ࢜ʹؔ࿈Λ࣋ͨͤΔ #hatenaintern2022 68

Slide 69

Slide 69 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά users ͱ blogs ͸Ұରଟͷؔ܎ #hatenaintern2022 69

Slide 70

Slide 70 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά • ଟͷςʔϒϧʹɺҰͷςʔϒϧͷϨίʔυΛҰҙʹಛఆ͢ΔΧ ϥϜͷ஋Λ࣋ͨͤΕ͹ɺҰରଟͷؔ܎ΛදͤΔ • ண໨͢Δ৘ใͷؔ܎Λਤʹͯ͠ΈΔͱଊ͑΍͍͢ • ERਤ - Google ݕࡧ #hatenaintern2022 70

Slide 71

Slide 71 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ׬੒ܗ CREATE TABLE users ( id BIGINT NOT NULL, name VARCHAR(32) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE blogs ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, title VARCHAR(254) NOT NULL, description VARCHAR(512) NOT NULL, PRIMARY KEY (id) ); #hatenaintern2022 71

Slide 72

Slide 72 text

ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά Ϣʔβʔ໊ͱϒϩά໊͸ผͷςʔϒϧʹ֨ೲ͞ΕΔΑ͏ʹͳͬͨ ͷͰɺͦΕΒͷσʔλΛҰؾʹऔಘ͢Δʹ͸ JOIN ͕ඞཁʹͳΔ SELECT users.name AS user_name, blogs.title AS blog_title, blogs.description AS blog_description FROM blogs JOIN users ON blogs.user_id = users.id WHERE users.id = 2; +-----------------+----------------------------------------+-----------------------------------------------------------------------------+ | user_name | blog_title | blog_description | +-----------------+----------------------------------------+-----------------------------------------------------------------------------+ | ͸ͯͳೋ࿠ | ೋ࿠ͷ೔ه | ೔ৗʹ͍ͭͯॻ͖·͢ | | ͸ͯͳೋ࿠ | ೋ࿠ͷٕज़ϒϩά | ٕज़తͳ࿩୊Λॻ͖·͢ | +-----------------+----------------------------------------+-----------------------------------------------------------------------------+ 2 rows in set (0.00 sec) #hatenaintern2022 72

Slide 73

Slide 73 text

ςʔϒϧઃܭ - ߪಡػೳ ͜͜ͰߪಡػೳΛ௥Ճ͢Δ͜ͱΛߟ͑ͯΈΔ • Ϣʔβʔ͸ϒϩάΛߪಡͰ͖Δ • ྫ: Ϣʔβʔʮ͸ͯͳଠ࿠ʯ͸ɺʮೋ࿠ͷ೔هʯʮೋ࿠ͷٕज़ ϒϩάʯͷϒϩάΛߪಡͰ͖Δ • SNS Ͱ͍͏ʮϑΥϩʔʯػೳ • ͜ΕΛςʔϒϧߏ଄Ͱද͢ʹ͸ʁ #hatenaintern2022 73

Slide 74

Slide 74 text

ςʔϒϧઃܭ - ߪಡػೳ users ͱߪಡର৅ͷ blogs ͸ଟରଟͷؔ܎ #hatenaintern2022 74

Slide 75

Slide 75 text

ςʔϒϧઃܭ - ߪಡػೳ • blogs ʹ subscribe_user_idʢ͜ͷϒϩάΛߪಡ͍ͯ͠ΔϢʔ βʔ IDʣͷΑ͏ͳΧϥϜΛ଍͢ͱʁ • blogs.subscribe_user_id ͸1͔ͭ͠஋Λ֨ೲͰ͖ͳ͍ • → ෳ਺ਓ͕ಉ͡ϒϩάΛߪಡ͍ͯ͠Δঢ়ଶΛදݱͰ͖ͳ͍ • ଟରଟͷؔ܎͸ɺͲͪΒ͔ͷςʔϒϧʹΧϥϜΛ௥Ճ͢Δ͚ͩ Ͱ͸දݱͰ͖ͳ͍ #hatenaintern2022 75

Slide 76

Slide 76 text

ςʔϒϧઃܭ - ߪಡػೳ • users ͱ blogs ͷߪಡͷؔ܎Λද͢ςʔϒϧΛ࡞Δ • subscriptions ςʔϒϧ • subscriptions ͔Β users ͱ blogs ͷϨίʔυΛಛఆͰ͖ΔΑ͏ʹɺuser_id ͱ blog_id ΧϥϜΛ࣋ͭ • ͜ͷྫʹݶΒͣɺଟରଟͷؔ܎Λද͢ʹ͸ɺؔ܎Λද͢ςʔϒϧΛ࡞ͬͯΈΔͱ͍͍ #hatenaintern2022 76

Slide 77

Slide 77 text

ςʔϒϧઃܭ - ߪಡػೳ ׬੒ܗ CREATE TABLE subscriptions ( user_id BIGINT NOT NULL, blog_id BIGINT NOT NULL, PRIMARY KEY (user_id, blog_id) ); #hatenaintern2022 77

Slide 78

Slide 78 text

ςʔϒϧઃܭ - ߪಡػೳ ʮ͸ͯͳଠ࿠ʯͱ͍͏໊લͷϢʔβʔ͕ߪಡ͍ͯ͠ΔϒϩάҰཡΛऔಘ͢Δʹ͸ɺ3ͭͷςʔϒ ϧͷ݁߹͕ඞཁʹͳΔ SELECT blogs.* FROM subscriptions JOIN users ON subscriptions.user_id = users.id JOIN blogs ON subscriptions.blog_id = blogs.id WHERE users.name = '͸ͯͳଠ࿠'; +-----+---------+-----------------+--------------------------------+ | id | user_id | title | description | +-----+---------+-----------------+--------------------------------+ | 102 | 2 | ೋ࿠ͷ೔ه | ೔ৗʹ͍ͭͯॻ͖·͢ | +-----+---------+-----------------+--------------------------------+ 1 row in set (0.00 sec) #hatenaintern2022 78

Slide 79

Slide 79 text

ςʔϒϧઃܭ ·ͱΊ • ѻ͏৘ใʹண໨ͯ͠ςʔϒϧΛߟ͑ͯΈΔ • ςʔϒϧಉ࢜ͷؔ܎͸ਤʹͯ͠ΈΔͱΘ͔Γ΍͍͢ • ؔ܎ࣗମΛςʔϒϧͰද͢ͱ͖ͬ͢Γ͢Δ͜ͱ΋͋Δ #hatenaintern2022 79

Slide 80

Slide 80 text

ύϑΥʔϚϯε #hatenaintern2022 80

Slide 81

Slide 81 text

ύϑΥʔϚϯε DB ͸ Web αʔϏεΛ։ൃɾӡӦ͢Δ্ͰύϑΥʔϚϯεͷ໰୊ʹܨ͕Γ΍͍͢ • αʔϏε͕ଓ͘ݶΓσʔλ͸૿Ճ͠ଓ͚Δ • ࠷ॳ͸໰୊ͳ͔ͬͨΫΤϦ͕ɺσʔλྔ͕૿͑ͨ͜ͱͰ࣮ߦʹ͕͔͔࣌ؒΔΑ͏ʹͳͬͯ͠ ·ͬͨΓ • DB ͷύϑΥʔϚϯεͷ໰୊͸αʔϏεͷ඼࣭௿Լʹܨ͕Δ • ΫΤϦͷ࣮ߦʹ͕͔͔࣌ؒΔ • → Ϣʔβʹฦ͢Ϩεϙϯε͕஗͘ͳΔ • → Ϣʔβ͕཭Εͯ͠·͏ #hatenaintern2022 81

Slide 82

Slide 82 text

ύϑΥʔϚϯε ύϑΥʔϚϯεʹؔͯ͠ɺҎԼͷ2ͭͷ΋ͷΛ঺հ͠·͢ • ΠϯσοΫε • EXPLAIN #hatenaintern2022 82

Slide 83

Slide 83 text

ΠϯσοΫε ςʔϒϧͷϨίʔυΛߴ଎ʹݕࡧ͢ΔͨΊͷ࢓૊Έ • ϨίʔυΛݕࡧ͢ΔࡍɺΠϯσοΫε͕ͳ͚Ε͹ɺઌ಄ߦ͔ΒςʔϒϧશମΛಡΈऔΓର৅ͷϨίʔυΛݟ෇͚ͩ͢ • ྫ: SELECT * FROM users WHERE id = 100000; ͳΒɺςʔϒϧͷઌ಄͔Β1݅ͣͭݟ͍ͯͬͯ id ͕ 100000 ʹҰக ͢Δ΋ͷΛ୳͢ • ΧϥϜʹΠϯσοΫε͕࡞੒͞Ε͍ͯΔ৔߹ɺͦͷΧϥϜͷσʔλ͕ݕࡧʹ࠷దͳσʔλߏ଄Ͱอଘ͞ΕΔ • Α͘࢖ΘΕΔͷ͸ɺB-Tree ͱ͍͏σʔλߏ଄ • ݕࡧର৅ͷΧϥϜ͕ΠϯσοΫεΛ࣋ͭΧϥϜͩͱɺશͯͷϨίʔυΛௐ΂ͣʹݕࡧ͕Ͱ͖Δ • ΠϯσοΫε͕ఆٛ͞Ε͍ͯΕ͹ɺςʔϒϧʹσʔλΛૠೖͨ͠ࡍʹɺࣗಈͰΠϯσοΫεʹ΋σʔλ͕௥Ճ͞Ε Δ #hatenaintern2022 83

Slide 84

Slide 84 text

ΠϯσοΫε • ໌ࣔతʹ࡞Δํ๏ CREATE INDEX index_name ON table_name (column_name); • ੍໿ʹΑΓࣗಈͰ࡞ΒΕΔ • PRIMARY KEY • UNIQUE MySQL Ͱ͸ɺCREATE TABLE จͷதͰΠϯσοΫεΛఆٛ͢Δ͜ͱ΋Ͱ͖Δ CREATE TABLE table_name ( -- (ུ) INDEX index_name (column_name) ); #hatenaintern2022 84

Slide 85

Slide 85 text

ΠϯσοΫε Ͳ͏͍͏ͱ͖ʹΠϯσοΫεΛ࡞Δ͔ʁ • ΠϯσοΫε࡞੒ʹ͸ίετ͕͔͔Δʢޙड़ʣͨΊɺແҋʹ࡞Ε͹͍͍Θ͚Ͱ͸ͳ͍ • ΞϓϦέʔγϣϯͰ࣮ߦ͢ΔΫΤϦΛΠϝʔδͯ͠ɺ༗ޮʹ࢖ΘΕͦ͏ͳΠϯσοΫε Λߟ͑Δ • ྫ: ʮ͋ΔϢʔβʔͷϒϩάҰཡΛऔಘ͢Δʯ • ΫΤϦ: SELECT * FROM blogs WHERE user_id = ϢʔβʔID; • → user_id Λݩʹݕࡧ͢ΔͷͰ user_id ʹΠϯσοΫε͕͋Δͱߴ଎ʹݕࡧͰ͖ͦ͏ #hatenaintern2022 85

Slide 86

Slide 86 text

ΠϯσοΫε ΠϯσοΫεͷ஫ҙ఺ • ΠϯσοΫε࡞੒ʹ͸ίετ͕͔͔Δ • Ϩίʔυͷ࡞੒ɾߋ৽ɾ࡟আ࣌ʹΠϯσοΫε΋ߋ৽͢ΔͷͰɺΦʔόʔϔου͕͋Δ • ଟ͘ͷΞϓϦέʔγϣϯͰ͸ ࢀরॲཧ > ߋ৽ॲཧ ʹͳΔͷͰ͋·Γ໰୊ͳ͍ • ΠϯσοΫε͕࢖ΘΕͳ͍έʔε΋͋Δ • ྫ: શ݅୳ࡧͷํ͕ૣ͍ • ౎ಓ෎ݝΛද͢ςʔϒϧͩͱɺ47Ϩίʔυ͔͠ͳ͍ͷͰશ݅୳ࡧͨ͠ํ͕ૣ͍ #hatenaintern2022 86

Slide 87

Slide 87 text

EXPLAIN7 • ΫΤϦͷ࣮ߦܭըΛग़ྗ͢Δํ๏ • ࣮ߦܭը: ΫΤϦʹͲͷΑ͏ͳΠϯσοΫεΛ࢖༻͠ɺͲΜͳॱ൪ͰςʔϒϧΛ݁ ߹͢Δͷ͔ɺͳͲ • ࣮ߦܭը͕Θ͔Ε͹ɺͲͷ෦෼͕ύϑΥʔϚϯεΛѱ͍ͯ͘͠Δ͔Λ஌Δ͜ͱ͕Ͱ͖ Δ • ΫΤϦͷઌ಄ʹ EXPLAIN Λ෇͚Δ͜ͱͰɺͦͷΫΤϦͷ࣮ߦܭըΛಘΔ͜ͱ͕Ͱ͖Δ 7 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 8.8.1 EXPLAIN ʹΑΔΫΤϦʔͷ࠷దԽ PostgreSQL: Documentation: 14: EXPLAIN #hatenaintern2022 87

Slide 88

Slide 88 text

EXPLAIN ͜͜Ͱ͸ MySQL ͷྫΛग़͍ͯ͠·͢ -- ΠϯσοΫεͷޮ͔ͳ͍ΫΤϦͷEXPLAIN -- type ALL ͸ϑϧςʔϒϧεΩϟϯɺςʔϒϧશମΛಡΈऔ͍ͬͯΔ͜ͱΛࣔ͢ = ΠϯσοΫε͕࢖ΘΕ͍ͯͳ͍ EXPLAIN SELECT COUNT(*) FROM blogs WHERE title LIKE '%೔ه'; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | blogs | NULL | ALL | NULL | NULL | NULL | NULL | 109745 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) #hatenaintern2022 88

Slide 89

Slide 89 text

EXPLAIN -- ΠϯσοΫε͕ޮ͘ΫΤϦͷ EXPLAIN -- type range ͸ΠϯσοΫεΛ࢖༻͠ɺಛఆͷൣғʹ͋ΔߦͷΈ͕औಘ͞Ε͍ͯΔ͜ͱΛࣔ͢ EXPLAIN SELECT COUNT(*) FROM users WHERE id > 2000; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | users | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) #hatenaintern2022 89

Slide 90

Slide 90 text

ύϑΥʔϚϯε ʮਪଌ͢ΔͳɺܭଌͤΑʯͱ͍͏֨ݴ͕͋Γ·͢ • ύϑΥʔϚϯεରࡦΛ͢Δͱ͖͸צͰରॲͯ͠͸͍͚ͳ͍ • ͖ͪΜͱܭଌ͠ɺϘτϧωοΫΛ೺Ѳͯ͠ରॲ͍ͯ͘͠ඞཁ͕ ͋Δ #hatenaintern2022 90

Slide 91

Slide 91 text

ऴΘΓʹ #hatenaintern2022 91

Slide 92

Slide 92 text

ऴΘΓʹ • 1͓࣌ؒർΕ༷Ͱͨ͠ • ֓೦ɾSQLɾςʔϒϧઃܭɾύϑΥʔϚϯεʹগͣͭ͠৮Ε·ͨ͠ ͕ɺجૅతͳτϐοΫΛશͯѻ͍͑ͯΔΘ͚Ͱ͸ͳ͍Ͱ͢ • ஌ࣝ΍ཧ࿦ʹ͍ͭͯ͸ɺۭ͍ͨ࣌ؒ΍Πϯλʔϯ͕ऴΘͬͨޙʹݟͯ Έ͍ͯͩ͘͞ • ࣮αʔϏεͷσʔλྔΛѻ͏ܦݧ͸ɺޙ൒ύʔτͰମݧͯ͠΋Β͑Δ ͱࢥ͍·͢ #hatenaintern2022 92

Slide 93

Slide 93 text

ऴΘΓʹ ѻ͑ͳ͔ͬͨτϐοΫ΍Ωʔϫʔυʹ͍ͭͯྻڍ͓ͯ͘͠ͷͰɺڵຯ͕͋Ε͹ௐ΂ͯΈ͍ͯͩ͘͞ • SQL ʹ͍ͭͯ • ෳࡶͳΫΤϦ΍ूܭ (αϒΫΤϦ) • ૊ΈࠐΈؔ਺ • ςʔϒϧઃܭ • ਖ਼نԽ • τϥϯβΫγϣϯ • ACID ಛੑ • ϩοΫ • ϓϩάϥϛϯάݴޠ͔Β SQL Λѻ͏ (෇࿥ʹࢀߟϦϯ Ϋهࡌ) • N+1 ໰୊ • ηΩϡϦςΟ • SQL ΠϯδΣΫγϣϯ #hatenaintern2022 93

Slide 94

Slide 94 text

෇࿥ ͓खݩͰखܰʹ SQL ͷΫΤϦΛࢼͯ͠΋Β͑ΔΑ͏ɺDocker Ͱ σʔλϕʔεΛىಈͯͦ͠ͷதʹೖΔํ๏Λॻ͖·ͨ͠ #hatenaintern2022 94

Slide 95

Slide 95 text

Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (1/3) MySQL ͷίϯςφΛىಈ $ docker pull mysql:8.0 $ docker run --name rdbms-bootcamp -e MYSQL_ROOT_PASSWORD="root_passwd" \ -e MYSQL_USER='user' \ -e MYSQL_PASSWORD='passwd' \ -e MYSQL_DATABASE="bootcamp" \ -p 13306:3306 mysql:8.0 ্هͷίϯςφͷ MySQL ʹೖΔ $ docker container exec -it rdbms-bootcamp mysql -uuser -ppasswd bootcamp #hatenaintern2022 95

Slide 96

Slide 96 text

Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (2/3) ஫ҙ఺ • root ϢʔβʔͰ͸ Docker ίϯςφ಺͔ΒͰͳ͍ͱೖΕ·ͤΜ • → ݖݶ؅ཧͷؔ܎ • ެࣜͷΠϝʔδͩͱ mysql client ͔ΒೖΔͱ೔ຊޠ͕ೖྗͰ͖ͳ͍ɾग़ྗ͞Εͳ ͍ • OS ʹ೔ຊޠઃఆ͕ೖ͍ͬͯͳ͍ͨΊ • ҎԼͷํ๏Ͱ೔ຊޠೖྗͰ͖ΔΑ͏ʹͳΔ #hatenaintern2022 96

Slide 97

Slide 97 text

Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (3/3) ೔ຊޠͷઃఆํ๏ # ίϯςφ಺ʹೖΔ $ docker container exec -it rdbms-bootcamp /bin/bash # locale ઃఆΛΠϯετʔϧ͠ɺ೔ຊޠ͕࢖͑ΔΑ͏ʹ͢Δ root@xxx:/$ microdnf install glibc-langpack-ja root@xxx:/$ exit # LANG ؀ڥม਺Λ೔ຊޠʹͯ͠ɺmysql ʹϩάΠϯ $ docker container exec -it rdbms-bootcamp env LANG="ja_JP.utf8" mysql -uuser -ppasswd bootcamp #hatenaintern2022 97

Slide 98

Slide 98 text

MySQL ͰΑ͘࢖͏ίϚϯυ -- ςʔϒϧҰཡΛදࣔ͢Δ SHOW TABLES; -- ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ DESCRIBE table_name; -- MySQL ͔Βग़Δ exit #hatenaintern2022 98

Slide 99

Slide 99 text

Docker Λ࢖ͬͯ PostgreSQL Λૢ࡞͢Δ PostgreSQL ͷίϯςφΛىಈ $ docker pull postgres:14.5 $ docker run --name rdbms-bootcamp -e POSTGRES_PASSWORD=passwd postgres:14.5 ্هͷίϯςφͷ PostgreSQL ʹೖΔ $ docker container exec -it rdbms-bootcamp psql -h localhost -U postgres -d postgres #hatenaintern2022 99

Slide 100

Slide 100 text

PostgreSQL ͰΑ͘࢖͏ίϚϯυ -- ςʔϒϧҰཡΛදࣔ͢Δ \dt -- ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ \d table_name -- PostgreSQL ͔Βग़Δ exit #hatenaintern2022 100

Slide 101

Slide 101 text

ࢀߟϦϯΫ (1/2) RDBMS શൠ • σʔλϕʔε֓࿦ᶗ | ஜ೾େֶΦʔϓϯίʔε΢ΣΞʛTSUKUBA OCW • ͋Δఔ౓ DB Λѻ͏͜ͱʹ׳Ε͖͔ͯͯΒݟͯΈΔͱɺཧ࿦ͱ࣮ફ͕߹Θͬͯ͞Α͍ͱࢥ͏ ϦϑΝϨϯε • MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ • PostgreSQL: Documentation: 14: PostgreSQL 14.5 Documentation • ϦϑΝϨϯεͳͷͰɺࠔͬͨͱ͖ʹࣙॻతʹ࢖͏ͱྑ͍Ͱ͢ • ࣮ࡍʹ࢖͏όʔδϣϯʹ߹ͬͨϚχϡΞϧΛࢀরͨ͠ํ͕ྑ͍Ͱ͢ #hatenaintern2022 101

Slide 102

Slide 102 text

ࢀߟϦϯΫ (2/2) Go ݴޠ͔ΒσʔλϕʔεΛѻ͏ํ๏ • Accessing databases - The Go Programming Language • ϓϩάϥϛϯάݴޠ͔Βѻ͏Ұྫͱͯ͠ Go ͷυΩϡϝϯτΛ঺հ͠ ·͢ • ͜ͷυΩϡϝϯτࣗମ͸ Go ͷυΩϡϝϯτͰ͕͢ɺϓϩάϥϜ͔Β SQL Λѻ͏͜ͱʹ͍ͭͯͷνϡʔτϦΞϧ͔ΒҰൠతͳ࿩୊ɺ஫ҙ ఺ͳͲʹ৮ΕΒΕ·͢ #hatenaintern2022 102