はてなリモートインターンシップ2022 RDBMSブートキャンプ 講義資料

https://hatena.co.jp/recruit/intern/2022

脚注などで紹介したリンクを以下に再掲します:
P19
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.20 CREATE TABLE ステートメント: https://dev.mysql.com/doc/refman/8.0/ja/create-table.html
- PostgreSQL: Documentation: 14: CREATE TABLE: https://www.postgresql.org/docs/current/sql-createtable.html
P38
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.6 INSERT ステートメント: https://dev.mysql.com/doc/refman/8.0/ja/insert.html
- PostgreSQL: Documentation: 14: INSERT: https://www.postgresql.org/docs/current/sql-insert.html
P39
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.10 SELECT ステートメント: https://dev.mysql.com/doc/refman/8.0/ja/select.html
- PostgreSQL: Documentation: 14: SELECT: https://www.postgresql.org/docs/current/sql-select.html
P41
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.13 UPDATE ステートメント: https://dev.mysql.com/doc/refman/8.0/ja/update.html
- PostgreSQL: Documentation: 14: UPDATE: https://www.postgresql.org/docs/current/sql-update.html
P42
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.2 DELETE ステートメント: https://dev.mysql.com/doc/refman/8.0/ja/delete.html
- PostgreSQL: Documentation: 14: DELETE: https://www.postgresql.org/docs/current/sql-delete.html
P87
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.8.1 EXPLAIN によるクエリーの最適化: https://dev.mysql.com/doc/refman/8.0/ja/using-explain.html
- PostgreSQL: Documentation: 14: EXPLAIN: https://www.postgresql.org/docs/current/sql-explain.html
P90
- 「推測するな、計測せよ」: http://users.ece.utexas.edu/~adnan/pike.html
P101
- データベース概論Ⅰ | 筑波大学オープンコースウェア｜TSUKUBA OCW: https://ocw.tsukuba.ac.jp/course/systeminformation/database-systems-i/
- MySQL 8.0 リファレンスマニュアル: https://dev.mysql.com/doc/refman/8.0/ja/
- PostgreSQL: Documentation: 14: PostgreSQL 14.5 Documentation: https://www.postgresql.org/docs/current/index.html
P102
- Accessing databases - The Go Programming Language: https://golang.org/doc/#data-access

Transcript

1. RDBMS ϒʔτΩϟϯϓ #hatenaintern2022 1
2. ͸͡Ίʹ ϒʔτΩϟϯϓͱ͸ • ޙ൒ύʔτͰࠔΒͳ͍͜ͱΛ໨తʹ • ֓೦͔ΒͰ͸ͳ͘ɺ۩ମྫ΍ςΫχοΫத৺ʹ • ݁ߏۦ͚଍ؾຯͳͷͰɺޙͰௐ΂Δͱ͔͔ͬΓͷҰͭʹͯ͠΋ Β͑Δͱ޾͍Ͱ͢ #hatenaintern2022 2
3. ͸͡Ίʹ • ͸ͯͳͰ͸ओʹ MySQL ͱ PostgreSQL ͱ͍͏ DBMS ΛΑ͘ ࢖͍ͬͯ·͢ • ޙ൒ύʔτͷػೳ։ൃͰ͜ΕΒΛ࢖͏ࡍʹɺ0 ͔Βௐ΂Δ͜ͱ ͕ͳ͍Α͏ʹجૅతͳ෦෼Λઆ໌͠·͢ #hatenaintern2022 3
4. ͜ͷϒʔτΩϟϯϓͷΰʔϧ MySQL ΍ PostgreSQL ΛͳΜͱͳ͘ѻ͑ΔΑ͏ʹͳΔ • σʔλΛ֨ೲग़དྷΔΑ͏ʹͳΔ • σʔλΛอଘɾࢀরɾߋ৽ɾ࡟আ͕ग़དྷΔ • ςʔϒϧઃܭͷߟ͑ํͱύϑΥʔϚϯε͕ͳΜͱͳ͘Θ͔Δ #hatenaintern2022 4
5. ͜ͷߨٛͰѻ͑ͳ͍͜ͱ • σʔλϕʔεͷ಺෦ͷ࢓૊Έ • ৄ͍͠ςʔϒϧઃܭ΍ SQL ͷςΫχοΫ • ύϑΥʔϚϯεͷৄ͍͠࿩ • MySQL ͱ PostgreSQL Ҏ֎ͷ͜ͱ #hatenaintern2022 5
6. ໨࣍ • RDBMS ʹ͍ͭͯ • جຊฤ • Ԡ༻ฤ • ෇࿥ #hatenaintern2022 6
7. RDBMS ʹ͍ͭͯ #hatenaintern2022 7
8. RDBMS ͱ͸ RDBMS = Relational Database Management System • Relational Database • ؔ܎σʔλϕʔε • Database Management System • σʔλϕʔε؅ཧγεςϜ ؔ܎σʔλϕʔεͷσʔλϕʔε؅ཧγεςϜ #hatenaintern2022 8
9. ؔ܎σʔλϕʔε ؔ܎Ϟσϧʹج͍ͮͨσʔλϕʔε ؔ܎Ϟσϧͱ͸ʁ → σʔλΛؔ܎ͱͯ͠දݱ͠औΓѻ͏σʔλͷදݱํ๏ ؔ܎σʔλϕʔεͰ͸ɺ ݟͨ໨ͱͯ͠͸දʢςʔϒϧʣͷܗࣜͰσʔλΛදݱ͢Δ #hatenaintern2022 9
10. ؔ܎σʔλϕʔε #hatenaintern2022 10
11. ؔ܎σʔλϕʔε ؔ܎σʔλϕʔεҎ֎ͷσʔλϕʔεγεςϜ΋͋Δ͕ɺ͜ͷߨٛͰ͸ѻΘͳ͍ ྫ͑͹͜͏͍͏΋ͷ͕͋Γ·͢: #hatenaintern2022 11
12. σʔλϕʔε؅ཧγεςϜ σʔλϕʔε؅ཧγεςϜʢDBMSʣͷ໾ׂ • σʔλΛந৅Խ͠ɺޮ཰Α͘อଘ • ༻్ʹ͋Θͤͯ࠷దͳߏ଄ͰσʔλΛอଘ͢Δ • ֎෦͔ΒͷࢦࣔΛड͚෇͚ͯσʔλΛૢ࡞͢ΔɺΠϯλʔϑΣʔεͷ໾ׂ • σʔλΛ࢖͏ਓ͸σʔλ͕ͲͷΑ͏ʹ֨ೲ͞Ε͍ͯΔ͔ҙࣝ͠ͳͯ͘΋͍͍ • σʔλΛݎ࿚ʹѻ͏࢓૊ΈΛఏڙ͢Δ • ੔߹ੑΛอͭ • ฒྻΞΫηε͞Εͯ΋ᴥᴪ͕ى͖ͳ͍Α͏ʹ͢Δ • σʔλͷଛࣦΛ๷͙ #hatenaintern2022 12
13. جຊฤ #hatenaintern2022 13
14. ༻ޠ ༻ޠ ҙຯ ςʔϒϧ σʔλͷू߹ Ϩίʔυ ςʔϒϧͷσʔλͷ૊ 1 ߦ 1 ߦ ΧϥϜ ςʔϒϧͷσʔλͷଐੑ εΩʔϚ ςʔϒϧͷߏ଄ ʢͲΜͳΧϥϜ͕͋Δ͔ɺΧϥϜʹ͸Ͳ Μͳ஋͕ೖΔ͔ɺͳͲʣ #hatenaintern2022 14
15. ༻ޠ #hatenaintern2022 15
16. SQL RDBMS ʹ໰͍߹Θͤʢૢ࡞ʣΛߦ͏ͨΊͷݴޠ • DDL (Data Definition Language) • εΩʔϚΛఆٛ͢Δ SQL • DML (Data Manipulation Language) • σʔλΛૢ࡞͢Δ SQL • ݕࡧɺߋ৽ͳͲ • DCL (Data Control Language) • σʔλ΁ͷΞΫηε੍ޚʹؔ͢Δ SQL • ͜ͷߨٛͰ͸঺հ͠ͳ͍ SQL Ͱॻ͔Εͨɺσʔλϕʔε΁ͷ໰͍߹ΘͤจΛɺʮΫΤϦʯͱݺͿ #hatenaintern2022 16
17. SQL SQL ʹ͸ن͕֨͋Δ͕ɺͦΕʹͲ͜·Ͱ४ڌ͍ͯ͠Δ͔͸ DBMS ʹΑͬͯ·ͪ·ͪ DBMS ͸ͦΕͧΕಠ֦ࣗுʢํݴʣΛ͍࣋ͬͯͯɺޓ׵ੑ͕ͳ͍ ͜ͱ΋͋Δ ͜ͷϒʔτΩϟϯϓͰ΋ɺʮMySQL Ͱ͸͜ͷॻ͖ํ͕Ͱ͖·͢ʯ ͷΑ͏ͳ࿩͕ग़͖ͯͨΒํݴͷ͜ͱͩͱࢥ͍ͬͯͩ͘͞ #hatenaintern2022 17
18. σʔλఆٛ • DDL (Data Definition Language) ← ͜Ε • εΩʔϚΛఆٛ͢Δ SQL • DML (Data Manipulation Language) • σʔλΛૢ࡞͢Δ SQL • ݕࡧɺߋ৽ͳͲ #hatenaintern2022 18
19. 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
20. CREATE TABLE จ id BIGINT NOT NULL, • BIGINT ͷ෦෼: ΧϥϜͷσʔλܕ • NOT NULL ͷ෦෼: ΧϥϜͷ੍໿ #hatenaintern2022 20
21. σʔλܕ #hatenaintern2022 21
22. σʔλܕ • ܕʹΑͬͯอଘʹ࢖ΘΕΔ༰ྔ͕ҟͳΔͷͰେࣄ • ޙ͔Βม͑Δͷ΋େมͳ͜ͱ͕ଟ͍ • Α͘࢖͏΋ͷʹ͍ͭͯ঺հ • ਺஋ܕ • จࣈྻܕ • ೔෇ͱ࣌ؒܕ #hatenaintern2022 22
23. ਺஋ܕ • ੔਺ • TINYINT , INTEGER , BIGINT ͳͲ • ӈʹߦ͘΄Ͳଟ͘ͷܻΛอଘͰ͖Δ • MySQL Ͱ͸ UNSIGNED ଐੑΛ෇༩͢Δͱූ߸ແ͠ͰอଘͰ͖ΔΑ͏ʹͳΔʢBIGINT UNSIGNEDʣ • ଐੑ: ΧϥϜͷಛੑɻ͚ͭΒΕΔଐੑ͸ɺσʔλܕʹΑͬͯҟͳΔ • ුಈখ਺఺ • FLOAT , DOUBLE • ݻఆখ਺఺ • DECIMAL , NUMERIC #hatenaintern2022 23
24. ਺஋ܕ • ܻ͋;ΕʹؾΛ͚ͭΔඞཁ͕͋Δ • Ϩίʔυ͝ͱʹϢχʔΫʹ͚ͭΔ id ͳͲ͸ BIGINT UNSIGNED ʹ ͓ͯ͘͠ͱ҆৺ • 18446744073709551615ʢ1844ژʣ·ͰอଘͰ͖Δ #hatenaintern2022 24
25. จࣈྻܕ • ଟ͘ͷ৔߹Ͱ VARCHAR(n) Λ࢖͏ • Մม௕จࣈྻͰ 0 ʙ 65,535 όΠτอଘՄೳ • VARCHAR(32) ͷΑ͏ʹɺอଘ͢Δ࠷େจࣈ਺Λࢦఆ͢Δ • ͋Δఔ౓௕͍จࣈྻΛอଘ͢Δͱ͖ʹ͸ςΩετܕΛར༻ • MySQL Ͱ͸ TEXT, MEDIUMTEXT, LONGTEXT ͳͲ • PostgreSQL ʹ͸ TEXT ͚ͩ༻ҙ͞Ε͍ͯΔ #hatenaintern2022 25
26. ೔෇ͱ࣌ؒܕ 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
27. ೔෇ͱ࣌ؒܕ PostgreSQL Ͱ͸ɺλΠϜκʔϯͷ༗ແ͸ ↓ ͷΑ͏ʹ۠ผ͢Δ • TIMESTAMP WITHOUT TIME ZONE • TIMESTAMP WITH TIME ZONE #hatenaintern2022 27
28. ೔෇ͱ࣌ؒܕ ೔෇ͱ࣌ؒͷ྆ํΛؚΜͰ͸͍ͳ͍ܕ΋͋Δ ʢొ৔ස౓͸গͳ͍ʣ • DATE: ೔෇ͷΈ • TIME: ࣌ؒͷΈ #hatenaintern2022 28
29. ੍໿ #hatenaintern2022 29
30. ੍໿ • ΧϥϜʹೖΔσʔλʹର͢Δ੍ݶ • ҙਤ͠ͳ͍σʔλ͕ೖΓࠐ·ͳ͍Α͏ʹ • ੍໿ʹ൓͢ΔͱΤϥʔʹͳΔ #hatenaintern2022 30
31. ੍໿ ྫ: ʮ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
32. ੍໿ͷྫ • NOT NULL੍໿: ΧϥϜ͸ NULL Ͱ͋ͬͯ͸͍͚ͳ͍ • NULL ͸ɺ஋͕ܽଛ͍ͯ͠Δ͜ͱΛද͢ • UNIQUE ੍໿: ΧϥϜͷ஋͕ςʔϒϧ಺ͰҰҙͰͳ͚Ε͹ͳΒͳ͍ • ͨͩ͠ NULL ͸͍ͭ͋ͬͯ͘΋ڐ͞ΕΔͷͰ஫ҙ • PRIMARY KEY ੍໿: ΧϥϜͷ஋͕ςʔϒϧ಺ͰϨίʔυΛҰҙʹࣝผͰ͖ͳ͚Ε͹ ͍͚ͳ͍ • FOREIGN KEY ੍໿: ඥ͍͍ͮͯΔςʔϒϧؒͰ੔߹ੑ͕औΕ͍ͯͳ͚Ε͹͍͚ͳ͍ #hatenaintern2022 32
33. PRIMARY KEY (ओΩʔ) • ςʔϒϧ಺ͰϨίʔυΛҰҙʹࣝผͰ͖ΔΧϥϜ • 1ͭͷςʔϒϧͰ1ͭࢦఆͰ͖Δ • PRIMARY KEY ੍໿͸ɺUNIQUE ੍໿ͱ NOT NULL ੍໿Λซͤ࣋ͬͯ ͍Δͱ͍͑Δ • PRIMARY KEY͸ΠϯσοΫεʢޙड़ʣͱͯ͠΋࢖͑Δ #hatenaintern2022 33
34. վΊͯ 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
35. σʔλૢ࡞ • DDL (Data Definition Language) • εΩʔϚΛఆٛ͢Δ SQL • DML (Data Manipulation Language) ← ͜Ε • σʔλΛૢ࡞͢Δ SQL • ݕࡧɺߋ৽ͳͲ #hatenaintern2022 35
36. CRUD • σʔλૢ࡞͢Δࡍͷجຊػೳ • ͦΕͧΕͷૢ࡞ͷ಄จࣈ͔Β • Create (௥Ճ) • Read (ࢀর) • Update (ߋ৽) • Delete (࡟আ) #hatenaintern2022 36
37. Ҏ߱͸ҎԼͷςʔϒϧ͕͋ΔલఏͰ ϢʔβʔͱɺϢʔβʔ͕ॴ༗͢ΔϒϩάΛ؅ཧ͍ͯ͠Δঢ়ଶ 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
38. 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
39. 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
40. 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
41. 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
42. 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
43. σʔλͷऔಘํ๏ ໨త खஈ ৚݅Λ͚ͭͯߜΓࠐΈ WHERE ۟ ιʔτ ORDER BY ۟ औಘ݅਺ͷ੍ݶ LIMIT ۟ ϖʔδϯά LIMIT ۟ͱ OFFSET ۟Λ૊Έ߹ΘͤΔ άϧʔϐϯά GROUP BY ۟ ςʔϒϧಉ࢜ͷ݁߹ JOIN ۟ #hatenaintern2022 43
44. WHERE ۟ ৚݅Λ͚ͭͯߜΓࠐΉ SELECT * FROM users WHERE ͜͜ʹ৚݅Λॻ͘; #hatenaintern2022 44
45. ൺֱ ʮ౳͍͠ʯ͸ =ɺʮ౳͘͠ͳ͍ʯ͸ !=ʢ<> ͱ΋ॻ͔ΕΔʣ ϓϩάϥϛϯάݴޠͷ਺஋΍จࣈྻͷΑ͏ʹ >, <, >=, <= ͰൺֱͰ͖Δ -- 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
46. ൺֱ • 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
47. 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
48. ϖʔδϯά (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
49. ϖʔδϯά (LIMIT ۟ / OFFSET ۟) #hatenaintern2022 49
50. ϖʔδϯά (LIMIT ۟ / OFFSET ۟) #hatenaintern2022 50
51. ϖʔδϯά (LIMIT ۟ / OFFSET ۟) #hatenaintern2022 51
52. ϖʔδϯά ͨͩ͠ɺLIMIT / OFFSET Λ࢖ͬͨϖʔδϯάʹ͸ҎԼͷΑ͏ͳσϝϦοτ͕͋Δ • ύϑΥʔϚϯε • OFFSET Ͱࢦఆ͞Εͨߦ਺·ͰḷΓண͘·Ͱʹશͯͷߦ਺Λ਺͑ͳ͚Ε͹͍͚ͳ͍ • σʔλ͕ͣΕΔ • ϖʔδϯάΛ͍ͯ͠Δ్தʹߦ͕ࠩ͠ࠐ·ΕͨΓ࡟আ͞ΕͨΓͨ͠ΒɺͣΕͯ͠·͏ • ྫ: 1ϖʔδ໨(1ʙ10݅໨)ΛΫΤϦ → 1݅໨ͷσʔλ͕࡟আ͞ΕΔ → 2ϖʔδ໨(11ʙ20݅໨)ΛΫ ΤϦ → ΋ͱ΋ͱͷ11݅໨͕ͲͪΒͷϖʔδʹ΋ݱΕͳ͍ ͜ΕΒͷσϝϦοτΛආ͚ΔͨΊʹɺ࣍ͷํ๏͕Α͘࢖ΘΕΔ #hatenaintern2022 52
53. ϖʔδϯά લϖʔδͷ࠷ऴߦͷ஋Λɺϖʔδͷ۠੾ΓͷΩʔͱͯ͠ ࢖͏ ྫ: 1ϖʔδ໨͸ id = 3 ͷϨίʔυ͕࠷ޙͩͬͨͷͰɺ2 ϖʔδ໨Λऔಘ͢Δͱ͖ʹ͸ id > 3 ͷ΋ͷΛऔಘ͢Δ SELECT * FROM table WHERE id > લͷϖʔδͷ࠷ऴߦͷid ORDER BY id ASC LIMIT 1ϖʔδ͋ͨΓͷ݅਺ ϝϦοτ • ύϑΥʔϚϯε • ΠϯσοΫεʢޙड़ʣ͕͋Ε͹ɺݱࡏͷϖʔδͷߦ ·Ͱૉૣ͘ඈ΂Δ • σʔλͷͣΕ • ى͜Βͳ͍ σϝϦοτ • ೚ҙͷϖʔδ·Ͱඈ΂ͳ͍ • 1ϖʔδ໨͔Β10ϖʔδ໨ʹߦ͘ɺͱ͍͏ͷ͸ෆՄೳ #hatenaintern2022 53
54. άϧʔϐϯά (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
55. άϧʔϐϯά (GROUP BY ۟) #hatenaintern2022 55
56. ςʔϒϧಉ࢜ͷ݁߹ (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
57. ςʔϒϧಉ࢜ͷ݁߹ (JOIN ۟) #hatenaintern2022 57
58. ςʔϒϧಉ࢜ͷ݁߹ (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
59. Ԡ༻ฤ #hatenaintern2022 59
60. Ԡ༻ฤ ͜ΕΒͷ࿩୊Λগ͚ͣͭͩ͠঺հ • ςʔϒϧઃܭ • ύϑΥʔϚϯεͷ࿩ #hatenaintern2022 60
61. ςʔϒϧઃܭ #hatenaintern2022 61
62. ςʔϒϧઃܭ ϒϩάαʔϏεΛྫʹߟ͑ͯΈΔ • Ϣʔβʔ͕͍Δ • Ϣʔβʔ͸໊લΛ࣋ͭ • Ϣʔβʔ͸ϒϩάΛ࣋ͯΔ • ϒϩάʹ͸λΠτϧ͕෇͘ • ϒϩάʹ͸આ໌จ͕ॻ͚Δ • Ϣʔβʔ͸ϒϩάΛෳ਺࣋ͯΔ ͲΜͳςʔϒϧ(ද)ͰදͤΔ͔ʁ #hatenaintern2022 62
63. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά શ෦Ұͭͷςʔϒϧ(ද)Ͱॻ͍ͯΈΔ #hatenaintern2022 63
64. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ͜ΕͰ΋ՄೳͰ͸͋Δ͚Ͳ... • Ϣʔβʔ໊ͷ৘ใ͕ॏෳ͍ͯ͠Δ • ؒҧ͑ͯҰ෦͚ͩߋ৽ͯ͠͠·ͬͨΒ੔߹ੑ͕ͳ͘ͳͬͯ͠·͏ • γϯϓϧͳ͜ͱΛ͍͚ͨͩ͠ͳͷʹෳࡶͳΫΤϦΛॻ͘৔ॴ͕Ͱ͖ͯ͠·͏ • ྫ: Ϣʔβʔ໊ҰཡΛऔಘ͢ΔͨΊʹ͸ SELECT DISTINCT user_name FROM blogs;ͱ͍͏ΫΤϦʹͳΔ • ѻ͏৘ใ͕૿͑ͨΒͲ͏͢Δʁ • ྫ: ϒϩάʹهࣄΛෳ਺͍࣋ͨͤͨ • هࣄΧϥϜΛ௥Ճͨ͠Β͜ͷςʔϒϧͰ΋࣮ݱͰ͖Δ͕ɺهࣄͷ਺͚ͩϨίʔυ͕࡞ΒΕΔͷͰɺͦͷ෼ Ϣʔβʔɾϒϩάͷσʔλͷॏෳ͕૿͑Δ #hatenaintern2022 64
65. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ѻ͏৘ใʹண໨ͯ͠ςʔϒϧΛ෼ׂ • ʮϢʔβʔʯͱʮϒϩάʯʹண໨ͯ͠ɺͦΕͧΕΛςʔϒϧͰද ݱ͢Δ • users ςʔϒϧͱ blogs ςʔϒϧʹ෼ׂ͢Δ • ͦΕͧΕͷςʔϒϧʹɺߦΛҰҙʹಛఆͰ͖ΔΩʔΛ௥Ճ͢Δ • ͦͷΩʔΛ࢖ͬͯɺςʔϒϧಉ͕࢜ؔ࿈Λ࣋ͯΔΑ͏ʹ͢Δ #hatenaintern2022 65
66. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ςʔϒϧΛ෼ׂ͢Δ #hatenaintern2022 66
67. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ςʔϒϧʹߦΛಛఆͰ͖ΔΩʔΛ௥Ճ͢Δ #hatenaintern2022 67
68. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ͦͷΩʔΛ࢖ͬͯςʔϒϧಉ࢜ʹؔ࿈Λ࣋ͨͤΔ #hatenaintern2022 68
69. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά users ͱ blogs ͸Ұରଟͷؔ܎ #hatenaintern2022 69
70. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά • ଟͷςʔϒϧʹɺҰͷςʔϒϧͷϨίʔυΛҰҙʹಛఆ͢ΔΧ ϥϜͷ஋Λ࣋ͨͤΕ͹ɺҰରଟͷؔ܎ΛදͤΔ • ண໨͢Δ৘ใͷؔ܎Λਤʹͯ͠ΈΔͱଊ͑΍͍͢ • ERਤ - Google ݕࡧ #hatenaintern2022 70
71. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ׬੒ܗ 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
72. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά Ϣʔβʔ໊ͱϒϩά໊͸ผͷςʔϒϧʹ֨ೲ͞ΕΔΑ͏ʹͳͬͨ ͷͰɺͦΕΒͷσʔλΛҰؾʹऔಘ͢Δʹ͸ 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
73. ςʔϒϧઃܭ - ߪಡػೳ ͜͜ͰߪಡػೳΛ௥Ճ͢Δ͜ͱΛߟ͑ͯΈΔ • Ϣʔβʔ͸ϒϩάΛߪಡͰ͖Δ • ྫ: Ϣʔβʔʮ͸ͯͳଠ࿠ʯ͸ɺʮೋ࿠ͷ೔هʯʮೋ࿠ͷٕज़ ϒϩάʯͷϒϩάΛߪಡͰ͖Δ • SNS Ͱ͍͏ʮϑΥϩʔʯػೳ • ͜ΕΛςʔϒϧߏ଄Ͱද͢ʹ͸ʁ #hatenaintern2022 73
74. ςʔϒϧઃܭ - ߪಡػೳ users ͱߪಡର৅ͷ blogs ͸ଟରଟͷؔ܎ #hatenaintern2022 74
75. ςʔϒϧઃܭ - ߪಡػೳ • blogs ʹ subscribe_user_idʢ͜ͷϒϩάΛߪಡ͍ͯ͠ΔϢʔ βʔ IDʣͷΑ͏ͳΧϥϜΛ଍͢ͱʁ • blogs.subscribe_user_id ͸1͔ͭ͠஋Λ֨ೲͰ͖ͳ͍ • → ෳ਺ਓ͕ಉ͡ϒϩάΛߪಡ͍ͯ͠Δঢ়ଶΛදݱͰ͖ͳ͍ • ଟରଟͷؔ܎͸ɺͲͪΒ͔ͷςʔϒϧʹΧϥϜΛ௥Ճ͢Δ͚ͩ Ͱ͸දݱͰ͖ͳ͍ #hatenaintern2022 75
76. ςʔϒϧઃܭ - ߪಡػೳ • users ͱ blogs ͷߪಡͷؔ܎Λද͢ςʔϒϧΛ࡞Δ • subscriptions ςʔϒϧ • subscriptions ͔Β users ͱ blogs ͷϨίʔυΛಛఆͰ͖ΔΑ͏ʹɺuser_id ͱ blog_id ΧϥϜΛ࣋ͭ • ͜ͷྫʹݶΒͣɺଟରଟͷؔ܎Λද͢ʹ͸ɺؔ܎Λද͢ςʔϒϧΛ࡞ͬͯΈΔͱ͍͍ #hatenaintern2022 76
77. ςʔϒϧઃܭ - ߪಡػೳ ׬੒ܗ CREATE TABLE subscriptions ( user_id BIGINT NOT NULL, blog_id BIGINT NOT NULL, PRIMARY KEY (user_id, blog_id) ); #hatenaintern2022 77
78. ςʔϒϧઃܭ - ߪಡػೳ ʮ͸ͯͳଠ࿠ʯͱ͍͏໊લͷϢʔβʔ͕ߪಡ͍ͯ͠ΔϒϩάҰཡΛऔಘ͢Δʹ͸ɺ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
79. ςʔϒϧઃܭ ·ͱΊ • ѻ͏৘ใʹண໨ͯ͠ςʔϒϧΛߟ͑ͯΈΔ • ςʔϒϧಉ࢜ͷؔ܎͸ਤʹͯ͠ΈΔͱΘ͔Γ΍͍͢ • ؔ܎ࣗମΛςʔϒϧͰද͢ͱ͖ͬ͢Γ͢Δ͜ͱ΋͋Δ #hatenaintern2022 79
80. ύϑΥʔϚϯε #hatenaintern2022 80
81. ύϑΥʔϚϯε DB ͸ Web αʔϏεΛ։ൃɾӡӦ͢Δ্ͰύϑΥʔϚϯεͷ໰୊ʹܨ͕Γ΍͍͢ • αʔϏε͕ଓ͘ݶΓσʔλ͸૿Ճ͠ଓ͚Δ • ࠷ॳ͸໰୊ͳ͔ͬͨΫΤϦ͕ɺσʔλྔ͕૿͑ͨ͜ͱͰ࣮ߦʹ͕͔͔࣌ؒΔΑ͏ʹͳͬͯ͠ ·ͬͨΓ • DB ͷύϑΥʔϚϯεͷ໰୊͸αʔϏεͷ඼࣭௿Լʹܨ͕Δ • ΫΤϦͷ࣮ߦʹ͕͔͔࣌ؒΔ • → Ϣʔβʹฦ͢Ϩεϙϯε͕஗͘ͳΔ • → Ϣʔβ͕཭Εͯ͠·͏ #hatenaintern2022 81
82. ύϑΥʔϚϯε ύϑΥʔϚϯεʹؔͯ͠ɺҎԼͷ2ͭͷ΋ͷΛ঺հ͠·͢ • ΠϯσοΫε • EXPLAIN #hatenaintern2022 82
83. ΠϯσοΫε ςʔϒϧͷϨίʔυΛߴ଎ʹݕࡧ͢ΔͨΊͷ࢓૊Έ • ϨίʔυΛݕࡧ͢ΔࡍɺΠϯσοΫε͕ͳ͚Ε͹ɺઌ಄ߦ͔ΒςʔϒϧશମΛಡΈऔΓର৅ͷϨίʔυΛݟ෇͚ͩ͢ • ྫ: SELECT * FROM users WHERE id = 100000; ͳΒɺςʔϒϧͷઌ಄͔Β1݅ͣͭݟ͍ͯͬͯ id ͕ 100000 ʹҰக ͢Δ΋ͷΛ୳͢ • ΧϥϜʹΠϯσοΫε͕࡞੒͞Ε͍ͯΔ৔߹ɺͦͷΧϥϜͷσʔλ͕ݕࡧʹ࠷దͳσʔλߏ଄Ͱอଘ͞ΕΔ • Α͘࢖ΘΕΔͷ͸ɺB-Tree ͱ͍͏σʔλߏ଄ • ݕࡧର৅ͷΧϥϜ͕ΠϯσοΫεΛ࣋ͭΧϥϜͩͱɺશͯͷϨίʔυΛௐ΂ͣʹݕࡧ͕Ͱ͖Δ • ΠϯσοΫε͕ఆٛ͞Ε͍ͯΕ͹ɺςʔϒϧʹσʔλΛૠೖͨ͠ࡍʹɺࣗಈͰΠϯσοΫεʹ΋σʔλ͕௥Ճ͞Ε Δ #hatenaintern2022 83
84. ΠϯσοΫε • ໌ࣔతʹ࡞Δํ๏ 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
85. ΠϯσοΫε Ͳ͏͍͏ͱ͖ʹΠϯσοΫεΛ࡞Δ͔ʁ • ΠϯσοΫε࡞੒ʹ͸ίετ͕͔͔Δʢޙड़ʣͨΊɺແҋʹ࡞Ε͹͍͍Θ͚Ͱ͸ͳ͍ • ΞϓϦέʔγϣϯͰ࣮ߦ͢ΔΫΤϦΛΠϝʔδͯ͠ɺ༗ޮʹ࢖ΘΕͦ͏ͳΠϯσοΫε Λߟ͑Δ • ྫ: ʮ͋ΔϢʔβʔͷϒϩάҰཡΛऔಘ͢Δʯ • ΫΤϦ: SELECT * FROM blogs WHERE user_id = ϢʔβʔID; • → user_id Λݩʹݕࡧ͢ΔͷͰ user_id ʹΠϯσοΫε͕͋Δͱߴ଎ʹݕࡧͰ͖ͦ͏ #hatenaintern2022 85
86. ΠϯσοΫε ΠϯσοΫεͷ஫ҙ఺ • ΠϯσοΫε࡞੒ʹ͸ίετ͕͔͔Δ • Ϩίʔυͷ࡞੒ɾߋ৽ɾ࡟আ࣌ʹΠϯσοΫε΋ߋ৽͢ΔͷͰɺΦʔόʔϔου͕͋Δ • ଟ͘ͷΞϓϦέʔγϣϯͰ͸ ࢀরॲཧ > ߋ৽ॲཧ ʹͳΔͷͰ͋·Γ໰୊ͳ͍ • ΠϯσοΫε͕࢖ΘΕͳ͍έʔε΋͋Δ • ྫ: શ݅୳ࡧͷํ͕ૣ͍ • ౎ಓ෎ݝΛද͢ςʔϒϧͩͱɺ47Ϩίʔυ͔͠ͳ͍ͷͰશ݅୳ࡧͨ͠ํ͕ૣ͍ #hatenaintern2022 86
87. EXPLAIN7 • ΫΤϦͷ࣮ߦܭըΛग़ྗ͢Δํ๏ • ࣮ߦܭը: ΫΤϦʹͲͷΑ͏ͳΠϯσοΫεΛ࢖༻͠ɺͲΜͳॱ൪ͰςʔϒϧΛ݁ ߹͢Δͷ͔ɺͳͲ • ࣮ߦܭը͕Θ͔Ε͹ɺͲͷ෦෼͕ύϑΥʔϚϯεΛѱ͍ͯ͘͠Δ͔Λ஌Δ͜ͱ͕Ͱ͖ Δ • ΫΤϦͷઌ಄ʹ EXPLAIN Λ෇͚Δ͜ͱͰɺͦͷΫΤϦͷ࣮ߦܭըΛಘΔ͜ͱ͕Ͱ͖Δ 7 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 8.8.1 EXPLAIN ʹΑΔΫΤϦʔͷ࠷దԽ PostgreSQL: Documentation: 14: EXPLAIN #hatenaintern2022 87
88. 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
89. 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
90. ύϑΥʔϚϯε ʮਪଌ͢ΔͳɺܭଌͤΑʯͱ͍͏֨ݴ͕͋Γ·͢ • ύϑΥʔϚϯεରࡦΛ͢Δͱ͖͸צͰରॲͯ͠͸͍͚ͳ͍ • ͖ͪΜͱܭଌ͠ɺϘτϧωοΫΛ೺Ѳͯ͠ରॲ͍ͯ͘͠ඞཁ͕ ͋Δ #hatenaintern2022 90
91. ऴΘΓʹ #hatenaintern2022 91
92. ऴΘΓʹ • 1͓࣌ؒർΕ༷Ͱͨ͠ • ֓೦ɾSQLɾςʔϒϧઃܭɾύϑΥʔϚϯεʹগͣͭ͠৮Ε·ͨ͠ ͕ɺجૅతͳτϐοΫΛશͯѻ͍͑ͯΔΘ͚Ͱ͸ͳ͍Ͱ͢ • ஌ࣝ΍ཧ࿦ʹ͍ͭͯ͸ɺۭ͍ͨ࣌ؒ΍Πϯλʔϯ͕ऴΘͬͨޙʹݟͯ Έ͍ͯͩ͘͞ • ࣮αʔϏεͷσʔλྔΛѻ͏ܦݧ͸ɺޙ൒ύʔτͰମݧͯ͠΋Β͑Δ ͱࢥ͍·͢ #hatenaintern2022 92
93. ऴΘΓʹ ѻ͑ͳ͔ͬͨτϐοΫ΍Ωʔϫʔυʹ͍ͭͯྻڍ͓ͯ͘͠ͷͰɺڵຯ͕͋Ε͹ௐ΂ͯΈ͍ͯͩ͘͞ • SQL ʹ͍ͭͯ • ෳࡶͳΫΤϦ΍ूܭ (αϒΫΤϦ) • ૊ΈࠐΈؔ਺ • ςʔϒϧઃܭ • ਖ਼نԽ • τϥϯβΫγϣϯ • ACID ಛੑ • ϩοΫ • ϓϩάϥϛϯάݴޠ͔Β SQL Λѻ͏ (෇࿥ʹࢀߟϦϯ Ϋهࡌ) • N+1 ໰୊ • ηΩϡϦςΟ • SQL ΠϯδΣΫγϣϯ #hatenaintern2022 93
94. ෇࿥ ͓खݩͰखܰʹ SQL ͷΫΤϦΛࢼͯ͠΋Β͑ΔΑ͏ɺDocker Ͱ σʔλϕʔεΛىಈͯͦ͠ͷதʹೖΔํ๏Λॻ͖·ͨ͠ #hatenaintern2022 94
95. 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
96. Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (2/3) ஫ҙ఺ • root ϢʔβʔͰ͸ Docker ίϯςφ಺͔ΒͰͳ͍ͱೖΕ·ͤΜ • → ݖݶ؅ཧͷؔ܎ • ެࣜͷΠϝʔδͩͱ mysql client ͔ΒೖΔͱ೔ຊޠ͕ೖྗͰ͖ͳ͍ɾग़ྗ͞Εͳ ͍ • OS ʹ೔ຊޠઃఆ͕ೖ͍ͬͯͳ͍ͨΊ • ҎԼͷํ๏Ͱ೔ຊޠೖྗͰ͖ΔΑ͏ʹͳΔ #hatenaintern2022 96
97. 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
98. MySQL ͰΑ͘࢖͏ίϚϯυ -- ςʔϒϧҰཡΛදࣔ͢Δ SHOW TABLES; -- ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ DESCRIBE table_name; -- MySQL ͔Βग़Δ exit #hatenaintern2022 98
99. 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
100. PostgreSQL ͰΑ͘࢖͏ίϚϯυ -- ςʔϒϧҰཡΛදࣔ͢Δ \dt -- ࢦఆͨ͠ςʔϒϧͷఆٛΛදࣔ͢Δ \d table_name -- PostgreSQL ͔Βग़Δ exit #hatenaintern2022 100
101. ࢀߟϦϯΫ (1/2) RDBMS શൠ • σʔλϕʔε֓࿦ᶗ | ஜ೾େֶΦʔϓϯίʔε΢ΣΞʛTSUKUBA OCW • ͋Δఔ౓ DB Λѻ͏͜ͱʹ׳Ε͖͔ͯͯΒݟͯΈΔͱɺཧ࿦ͱ࣮ફ͕߹Θͬͯ͞Α͍ͱࢥ͏ ϦϑΝϨϯε • MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ • PostgreSQL: Documentation: 14: PostgreSQL 14.5 Documentation • ϦϑΝϨϯεͳͷͰɺࠔͬͨͱ͖ʹࣙॻతʹ࢖͏ͱྑ͍Ͱ͢ • ࣮ࡍʹ࢖͏όʔδϣϯʹ߹ͬͨϚχϡΞϧΛࢀরͨ͠ํ͕ྑ͍Ͱ͢ #hatenaintern2022 101
102. ࢀߟϦϯΫ (2/2) Go ݴޠ͔ΒσʔλϕʔεΛѻ͏ํ๏ • Accessing databases - The Go Programming Language • ϓϩάϥϛϯάݴޠ͔Βѻ͏Ұྫͱͯ͠ Go ͷυΩϡϝϯτΛ঺հ͠ ·͢ • ͜ͷυΩϡϝϯτࣗମ͸ Go ͷυΩϡϝϯτͰ͕͢ɺϓϩάϥϜ͔Β SQL Λѻ͏͜ͱʹ͍ͭͯͷνϡʔτϦΞϧ͔ΒҰൠతͳ࿩୊ɺ஫ҙ ఺ͳͲʹ৮ΕΒΕ·͢ #hatenaintern2022 102