はてなリモートインターンシップ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
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

7. RDBMS ʹ͍ͭͯ
   #hatenaintern2022 7
   

   View Slide

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

   View Slide

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

   View Slide

10. ؔ܎σʔλϕʔε
    #hatenaintern2022 10
    

    View Slide

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

    View Slide

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

    View Slide

13. جຊฤ
    #hatenaintern2022 13
    

    View Slide

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

    View Slide

15. ༻ޠ
    #hatenaintern2022 15
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

21. σʔλܕ
    #hatenaintern2022 21
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

29. ੍໿
    #hatenaintern2022 29
    

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

59. Ԡ༻ฤ
    #hatenaintern2022 59
    

    View Slide

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

    View Slide

61. ςʔϒϧઃܭ
    #hatenaintern2022 61
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

80. ύϑΥʔϚϯε
    #hatenaintern2022 80
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

91. ऴΘΓʹ
    #hatenaintern2022 91
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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

     View Slide

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

     View Slide

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

     View Slide