Upgrade to Pro — share decks privately, control downloads, hide ads and more …

実行計画の話

 実行計画の話

第14回 中国地方DB勉強会 in 福山の登壇資料です。
https://dbstudychugoku.github.io/events/event-014.html

合わせてこちらのYouTube動画を見ることをオススメします。

PGCon 2014 Tokyo【D3】PostgreSQL SQL チューニング入門 入門編(下雅意 美紀)

https://www.youtube.com/watch?v=gxsBi-6ub3k&nohtml5=False

soudai sone

April 14, 2016
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. ࣮ߦܭըͷ࿩
    ୈճɹதࠃ஍ํ%#ษڧձ

    View Slide

  2. What is it?
    ࣮ߦܭըͷݟͯ·͔͢ʁ

    View Slide

  3. What is it?
    INDEXΛ੍͢Δऀ͸RDBΛ੍͢Δ

    View Slide

  4. What is it?
    ·ͣ͸࣮ߦܭըͷೖ໳ฤ

    View Slide

  5. What is it?
    ग़དྷΔʂEXPLAINʂʂ

    View Slide

  6. What is it?
    ͨͩ͠ɺPostgreSQLͱMySQLʹݶΔ
    ଞͷRDBͷ࿩͸͠·ͤΜ

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  11. What is it?
    d

    View Slide

  12. View Slide

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

    View Slide

  14. MySQL
    .Z42-ͷಛ௃

    View Slide

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

    View Slide

  16. MySQL
    ߋ৽ܕΞʔΩςΫνϟ

    View Slide

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

    View Slide

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

    View Slide

  19. MySQL
    ετϨʔδΤϯδϯ

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  25. MySQL
    γϯϓϧɾߴ଎

    View Slide

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

    View Slide

  27. MySQL
    #5SFF*/%&9

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  34. MySQL
    ࣮ߦܭըΛݟΔ

    View Slide

  35. MySQL
    &91-"*/

    View Slide

  36. MySQL
    ׽ͷίϯϐϡʔλಓΛݟΑ͏
    .Z42-ͷ&91-"*/Λపఈղઆ

    View Slide

  37. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html

    View Slide

  38. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾJETFMFDU@UZQF
    JE͸ΫΤϦͷॱ൪Λද͢ʢ+0*/ͷ࣌ʹॏཁʣ
    TFMFDU@UZQF͸ΫΤϦͷछྨΛද͢ʢαϒΫΤϦͷ࣌ʹॏཁʣ
    ԼهҎ֎ʹ΋͋Δ
    ɾ+0*/ͷTFMFDU@UZQF
    4*.1-& /FTUFE-PPL+PJO͔͠ͳ͍ʣ
    ɾαϒΫΤϦͷTFMFDU@UZQF
    13*."3:ɾɾɾ֎෦ΫΤϦΛࣔ͢ɻ
    46#26&3:ɾɾɾ૬ؔؔ܎ͷͳ͍αϒΫΤϦɻ
    %&1&/%&/546#26&3:ɾɾɾ૬ؔؔ܎ͷ͋ΔαϒΫΤϦɻ
    6/$"$)&"#-&46#26&3:ɾɾɾ࣮ߦ͢Δ౓ʹ݁Ռ͕มΘΔՄೳੑͷ͋ΔαϒΫΤϦɻ
    %&3*7&%ɾɾɾ'30.۟Ͱ༻͍ΒΕ͍ͯΔαϒΫΤϦɻ
    ."5&3*"-*;&%ɾɾɾ࣮ମԽ͞ΕͨαϒΫΤϦ

    View Slide

  39. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾJETFMFDU@UZQF
    JE͸ΫΤϦͷॱ൪Λද͢ʢ+0*/ͷ࣌ʹॏཁʣ
    TFMFDU@UZQF͸ΫΤϦͷछྨΛද͢ʢαϒΫΤϦͷ࣌ʹॏཁʣ
    ԼهҎ֎ʹ΋͋Δ
    ɾ+0*/ͷTFMFDU@UZQF
    4*.1-& /FTUFE-PPL+PJO͔͠ͳ͍ʣ
    ɾαϒΫΤϦͷTFMFDU@UZQF
    13*."3:ɾɾɾ֎෦ΫΤϦΛࣔ͢ɻ
    46#26&3:ɾɾɾ૬ؔؔ܎ͷͳ͍αϒΫΤϦɻ
    %&1&/%&/546#26&3:ɾɾɾ૬ؔؔ܎ͷ͋ΔαϒΫΤϦɻ
    6/$"$)&"#-&46#26&3:ɾɾɾ࣮ߦ͢Δ౓ʹ݁Ռ͕มΘΔՄೳੑͷ͋ΔαϒΫΤϦɻ
    %&3*7&%ɾɾɾ'30.۟Ͱ༻͍ΒΕ͍ͯΔαϒΫΤϦɻ
    ."5&3*"-*;&%ɾɾɾ࣮ମԽ͞ΕͨαϒΫΤϦ
    ߦ͝ͱʹ࣮ߦ͞ΕΔαϒΫΤϦ
    ͭ·Γܶతʹ஗͍

    View Slide

  40. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾUBCMF
    ΞΫηε͢Δର৅ͷςʔϒϧ

    View Slide

  41. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾUZQF
    ϨίʔυΞΫηελΠϓɻ
    ཁ͸*/%&9ͷར༻ͷ༗ແ΍࢖͍ํΛදͨ͠΋ͷ
    ɾओͳछྨ
    DPOTUɾɾɾ13*."3:,&:·ͨ͸6/*26&ΠϯσοΫεͷϧοΫΞοϓʹΑΔΞΫηεɻ࠷଎ɻ
    FR@SFGɾɾɾ+0*/ʹ͓͍ͯ13*"3:,&:·ͨ͸6/*26&,&:͕ར༻͞ΕΔ࣌ͷΞΫηελΠϓɻDPOTUͱࣅ͍ͯΔ͕+0*/
    Ͱ༻͍ΒΕΔͱ͜Ζ͕ҧ͏ɻ
    SFGɾɾɾϢχʔΫʢ13*."3:PS6/*26&ʣͰͳ͍ΠϯσοΫεΛ࢖ͬͯ౳Ձݕࡧʢ8)&3&LFZWBMVFʣΛߦͬͨ
    ࣌ʹ࢖ΘΕΔΞΫηελΠϓɻ
    SBOHFɾɾɾΠϯσοΫεΛ༻͍ͨൣғݕࡧɻ
    JOEFYɾɾɾϑϧΠϯσοΫεεΩϟϯɻΠϯσοΫεશମΛεΩϟϯ͢Δඞཁ͕͋ΔͷͰͱͯ΋஗͍ɻ
    "--ɾɾɾϑϧςʔϒϧεΩϟϯɻΠϯσοΫε͕·ͬͨ͘ར༻͞Ε͍ͯͳ͍͜ͱΛࣔ͢ɻ0-51ܥͷॲཧͰ͸վળඞਢɻ

    View Slide

  42. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾUZQF
    ϨίʔυΞΫηελΠϓɻ
    ཁ͸*/%&9ͷར༻ͷ༗ແ΍࢖͍ํΛදͨ͠΋ͷ
    ɾओͳछྨ
    DPOTUɾɾɾ13*."3:,&:·ͨ͸6/*26&ΠϯσοΫεͷϧοΫΞοϓʹΑΔΞΫηεɻ࠷଎ɻ
    FR@SFGɾɾɾ+0*/ʹ͓͍ͯ13*"3:,&:·ͨ͸6/*26&,&:͕ར༻͞ΕΔ࣌ͷΞΫηελΠϓɻDPOTUͱࣅ͍ͯΔ͕+0*/
    Ͱ༻͍ΒΕΔͱ͜Ζ͕ҧ͏ɻ
    SFGɾɾɾϢχʔΫʢ13*."3:PS6/*26&ʣͰͳ͍ΠϯσοΫεΛ࢖ͬͯ౳Ձݕࡧʢ8)&3&LFZWBMVFʣΛߦͬͨ
    ࣌ʹ࢖ΘΕΔΞΫηελΠϓɻ
    SBOHFɾɾɾΠϯσοΫεΛ༻͍ͨൣғݕࡧɻ
    JOEFYɾɾɾϑϧΠϯσοΫεεΩϟϯɻΠϯσοΫεશମΛεΩϟϯ͢Δඞཁ͕͋ΔͷͰͱͯ΋஗͍ɻ
    "--ɾɾɾϑϧςʔϒϧεΩϟϯɻΠϯσοΫε͕·ͬͨ͘ར༻͞Ε͍ͯͳ͍͜ͱΛࣔ͢ɻ0-51ܥͷॲཧͰ͸վળඞਢɻ
    ݟ͚ͭͨΒνϡʔχϯάର৅ʂ

    View Slide

  43. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾQPTTJCMF@LFZT
    ΦϓςΟϚΠβ͕ςʔϒϧͷΞΫηεʹར༻Մೳ
    ͳΠϯσοΫεͷީิͱͯ͠ڍ͛ͨΩʔͷҰཡ

    View Slide

  44. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾLFZ
    ΦϓςΟϚΠβʹΑͬͯબ୒͞ΕͨΩʔ

    View Slide

  45. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾLFZ@MFO
    બ୒͞ΕͨΩʔͷ௕͞
    ΠϯσοΫεͷ૸ࠪ͸ɺΩʔ௕͕୹͍ํ͕ߴ଎

    View Slide

  46. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾSFG
    ݕࡧ৚݅ͰɺLFZͱൺֱ͞Ε͍ͯΔ஋΍ΧϥϜͷछྨ
    ఆ਺͕ࢦఆ͞Ε͍ͯΔ৔߹͸DPOTUͱදࣔ͞ΕΔ
    +0*/͕࣮ߦ͞Ε͍ͯΔ࣌ʹ͸ɺ݁߹͢Δ૬खଆͷςʔϒϧͰݕࡧ৚݅ͱͯ͠ར༻͞Ε͍ͯΔΧϥϜ͕දࣔ͞ΕΔ

    View Slide

  47. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾSFG
    ݕࡧ৚݅ͰɺLFZͱൺֱ͞Ε͍ͯΔ஋΍ΧϥϜͷछྨ
    ఆ਺͕ࢦఆ͞Ε͍ͯΔ৔߹͸DPOTUͱදࣔ͞ΕΔ
    +0*/͕࣮ߦ͞Ε͍ͯΔ࣌ʹ͸ɺ݁߹͢Δ૬खଆͷςʔϒϧͰݕࡧ৚݅ͱͯ͠ར༻͞Ε͍ͯΔΧϥϜ͕දࣔ͞ΕΔ
    $PVOUSZςʔϒϧ͸$JUZςʔϒϧͱ$JUZ$PVOUSZ$PEF
    ΧϥϜͰ+0*/͞ΕΔͱ͍͏͜ͱΛ͍ࣔͯ͠Δ

    View Slide

  48. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾSPXT
    ͦͷςʔϒϧ͔ΒϑΣον͞ΕΔߦ਺ͷݟੵ΋Γ
    ͜ͷϑΟʔϧυ͸͋͘·Ͱ΋ςʔϒϧશମͷߦ਺΍ΠϯσοΫεͷ෼ࢄ۩߹͔Βಋ͖ग़͞Εͨେ·͔ͳݟੵ΋Γͳ
    ͷͰɺ࣮ࡍʹϑΣον͞ΕΔਖ਼֬ͳߦ਺Ͱ͸ͳ͍
    ͔͠͠ྫ֎ͱͯ͠%&3*7&%ςʔϒϧ͸࣮ࡍʹ࣮ߦ͢ΔͷͰਖ਼֬ͳݟੵ΋ΓʹͳΔ
    ඇৗʹ஗͍αϒΫΤϦͷ৔߹ɺ&91-"*/Ͱ΋͕͔͔࣌ؒΔՄೳੑ͕ߴ͍
    ޙड़͢Δ6TJOHXIFSF͕&YUSBϑΟʔϧυʹදࣔ͞Ε͍ͯΔ৔߹͸ɺϑΣονͨ͠ߦʹରͯ͠͞Βʹ8)&3&۟ͷݕ
    ࡧ৚͕݅ద༻͞ΕͯߦͷߜΓࠐΈ͕ߦΘΕΔͷͰɺΫϥΠΞϯτ΁ฦ͞ΕΔ݁Ռߦ͸গͳ͘ͳΔՄೳੑ͕͋Δ
    +0*/ͷࡍͷॏཁͳࢦඪʹͳΔ

    View Slide

  49. ࣮ߦܭը
    mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code =
    C1.CountryCode;
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | |
    | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | |
    | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
    +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
    3 rows in set (0.00 sec)
    Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!!
    http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
    ɾ&YUSB
    ͦͷΫΤϦΛ࣮ߦ͢ΔͨΊʹΦϓςΟϚΠβ͕ͲͷΑ͏ͳઓུΛબ୒͔ͨ͠ͱ͍͏͜ͱΛࣔ͢
    ɾओͳछྨʢಉ࣌ʹෳ਺දࣔ͞ΕΔ৔߹͕͋Δʣ
    6TJOHXIFSFɾɾɾසൟʹग़ྗ͞ΕΔ௥Ճ৘ใͰ͋Δɻ8)&3&۟ʹݕࡧ৚͕݅ࢦఆ͞Ε͓ͯΓɺͳ͓͔ͭΠϯσο
    ΫεΛݟ͚ͨͩͰ͸8)&3&۟ͷ৚݅Λશͯద༻͢Δ͜ͱ͕ग़དྷͳ͍৔߹ʹදࣔ͞ΕΔɻ
    6TJOHJOEFYɾɾɾΫΤϦ͕ΠϯσοΫε͚ͩΛ༻͍ͯղܾͰ͖Δ͜ͱΛࣔ͢ɻ$PWFSJOH*OEFYΛར༻͍ͯ͠Δ৔
    ߹ͳͲʹදࣔ͞ΕΔɻ
    6TJOHpMFTPSUɾɾɾpMFTPSUʢΫΠοΫιʔτʣͰιʔτΛߦ͍ͬͯΔ͜ͱΛࣔ͢ɻ6TJOHpMFTPSUʹ͍ͭͯ͸ઌ೔
    ৄ͘͠આ໌ͨ͠ͷͰࢀর͞Ε͍ͨɻ
    6TJOHUFNQPSBSZɾɾɾ+0*/ͷ݁ՌΛιʔτͨ͠Γɺ%*45*/$5ʹΑΔॏෳͷഉআΛߦ͏৔߹ͳͲɺΫΤϦͷ࣮ߦ
    ʹςϯϙϥϦςʔϒϧ͕ඞཁͳ͜ͱΛࣔ͢ɻ
    6TJOHJOEFYGPSHSPVQCZɾɾɾ.*/
    ."9
    ͕(3061#:۟ͱซ༻͞Ε͍ͯΔͱ͖ɺΫΤϦ͕ΠϯσοΫεͩ
    +0*/ͷࡍͷॏཁͳࢦඪʹͳΔ

    View Slide

  50. MySQL
    &91-"*/Ͱॏཁͳ͜ͱ
    w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ
    w ౷ܭ৘ใΛ࠷৽ʹ͢Δ
    w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ

    View Slide

  51. MySQL
    &91-"*/Ͱॏཁͳ͜ͱ
    w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ
    w ౷ܭ৘ใΛ࠷৽ʹ͢Δ
    w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ
    σʔλྔʹΑ࣮ͬͯߦܭը͕มΘΔ͜ͱ΋ଟ͍
    ࣮ࡍΑΓগͳ͍σʔλͰ֬ೝͯ͠΋ҙຯ͕ແ͍

    View Slide

  52. MySQL
    &91-"*/Ͱॏཁͳ͜ͱ
    w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ
    w ౷ܭ৘ใΛ࠷৽ʹ͢Δ
    w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ
    σʔλʹେྔͷ௥Ճɾ࡟আɾมߋ͕͋Δͱ౷ܭ৘ใ͕ෆਖ਼֬ʹͳΔ
    "/"-:;&5"#-&Ͱ౷ܭ৘ใΛߋ৽͔ͯ͠Β&91-"*/ʂʂ

    View Slide

  53. MySQL
    &91-"*/Ͱॏཁͳ͜ͱ
    w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ
    w ౷ܭ৘ใΛ࠷৽ʹ͢Δ
    w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ
    ࣮ߦܭըͰߴ଎Խͯ͠΋࣮ߦ݁Ռ͕มΘΔͱҙຯ͕ແ͍
    ࠷ऴతʹຊ൪Ͱ֬ೝ͔ͯ͠ΒϦϦʔε͢Δ͜ͱ

    View Slide

  54. MySQL
    &91-"*/ͷ·ͱΊ
    w લड़ͷԞ໺͞ΜͷهࣄΛಡ΋͏
    w ެࣜυΩϡϝϯτΛಡ΋͏ UZQFͳͲ͸ଞʹ΋͋Δ

    IUUQTEFWNZTRMDPNEPDSFGNBOKBFYQMBJOPVUQVUIUNM
    w ૉৼΓେࣄ

    View Slide

  55. MySQL
    &91-"*/͸೉͍͠ʜ

    View Slide

  56. MySQL
    Ͱ΋େৎ෉ɺ(6*͕͋Δʂ

    View Slide

  57. MySQL
    .Z42-8PSLCFODI

    View Slide

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

    View Slide

  59. MySQL

    View Slide

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

    View Slide

  61. ࣮ߦܭը
    CREATE TABLE `demo`.`users` (
    `id` INT NOT NULL AUTO_INCREMENT COMMENT '',
    `name` VARCHAR(45) NOT NULL COMMENT '',
    `age` INT NOT NULL COMMENT '',
    `created` DATETIME NOT NULL DEFAULT NOW () COMMENT '',
    PRIMARY KEY (`id`) COMMENT ''
    );
    ——શ݅Λબ୒͢ΔͷͰςʔϒϧεΩϟϯ
    SELECT * FROM demo.users;
    ——PRIMARY KEYʢINDEXʣΛར༻ͨ͠ྫ
    SELECT * FROM demo.users WHERE id > 100;
    ——INDEX͕ແ͍ͷͰςʔϒϧεΩϟϯ
    SELECT * FROM demo.users WHERE age > 20;

    View Slide

  62. MySQL

    View Slide

  63. MySQL

    View Slide

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

    View Slide

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

    View Slide

  66. MySQL

    View Slide

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

    View Slide

  68. MySQL

    View Slide

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

    View Slide

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

    View Slide

  71. MySQL

    View Slide

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

    View Slide

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

    View Slide

  74. 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'

    View Slide

  75. MySQL

    View Slide

  76. MySQL
    +0*/͸ֻ͚ࢉ

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  80. MySQL
    w ग़དྷΔ͚ͩখ͔ͯ͘͞͠Β+0*/
    w ෆཁͳ+0*/͸ආ͚Δ
    w */%&9Λར༻ͨ͠+0*/Λ͢Δ

    View Slide

  81. MySQL
    γϯϓϧɾߴ଎

    View Slide

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

    View Slide

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

    View Slide

  84. PostgreSQL
    1PTUHSF42-ͷಛ௃

    View Slide

  85. PostgreSQL
    1PTUHSF42-ͷಛ௃
    ˣ
    ߴػೳ

    View Slide

  86. PostgreSQL
    ௥هܕΞʔΩςΫνϟ

    View Slide

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

    View Slide

  88. PostgreSQL
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    ςʔϒϧ ςʔϒϧ
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    ࢀরΛ֎͢

    View Slide

  89. PostgreSQL
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    ςʔϒϧ ςʔϒϧ
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    ৽ͨʹมߋ෼Λ௥Ճ͢Δ

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  97. PostgreSQL
    &91-"*/

    View Slide

  98. PostgreSQL

    View Slide

  99. PostgreSQL
    ͜ͷຊ͕͘͢͝ྑ͍
    ͔͠͠ͷ࿩
    IUUQXXXBNB[PODPKQ಺෦ߏ଄͔ΒֶͿ1PTUHSF42-ઃܭɾӡ༻ܭըͷమଇ4PGUXBSF%FTJHOQMVTEQ

    View Slide

  100. PostgreSQL
    1($PO5PLZPʲ%ʳ
    1PTUHSF42-42-νϡʔχϯάೖ໳ೖ໳ฤ
    ʢԼխҙඒلʣ
    IUUQTXXXZPVUVCFDPNXBUDI WHYT#JVCLOPIUNM'BMTF

    View Slide

  101. PostgreSQL
    ͘͢͝ৄ͍͠ͷͰ
    ͜ͷ:PV5VCFΛݟ͍ͯͩ͘͞

    View Slide

  102. PostgreSQL
    1PTUHSF42-Ͱ΋(6*ͰݟΕΔ

    View Slide

  103. PostgreSQL
    QHBENJO

    View Slide

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

    View Slide

  105. PostgreSQL

    View Slide

  106. PostgreSQL

    View Slide

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

    View Slide

  108. PostgreSQL

    View Slide

  109. PostgreSQL

    View Slide

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

    View Slide

  111. PostgreSQL

    View Slide

  112. PostgreSQL

    View Slide

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

    View Slide

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

    View Slide

  115. PostgreSQL
    ΦϓςΟϚΠβ͕+0*/ʹʢ͈́

    View Slide

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

    View Slide

  117. PostgreSQL

    View Slide

  118. PostgreSQL

    View Slide

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

    View Slide

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

    View Slide

  121. PostgreSQL

    View Slide

  122. PostgreSQL

    View Slide

  123. PostgreSQL
    ෳࡶͳूܭ͸ಘҙ

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  135. ࣮ફͰϋϚΔ᠘
    ग़དྷΔ͚ͩ৽͍͠όʔδϣϯ͕ྑ͍
    ϝδϟʔόʔδϣϯʹؔͯ͠͸
    ೥ޙ͙Β͍ͷόʔδϣϯ͕͍͍ײ͡

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  139. ࣮ફͰϋϚΔ᠘
    ࢥ͍ࠐΈ

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  143. ࣮ફͰϋϚΔ᠘
    ͋Δ೔ಥવ஗͘ͳΔ

    View Slide

  144. ࣮ફͰϋϚΔ᠘
    ͋Δ೔ಥવ஗͘ͳΔ
    ˣ
    σʔλྔ͕ϝϞϦʹ৐Βͳ͘ͳΔ

    View Slide

  145. ࣮ફͰϋϚΔ᠘
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    ϝϞϦ ϝϞϦ
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    σʔλ૿

    View Slide

  146. ࣮ફͰϋϚΔ᠘
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    ϝϞϦ ϝϞϦ
    Ϩίʔυ
    Ϩίʔυ
    Ϩίʔυ
    ϝϞϦʹ৐Βͳ͍ͷͰ%JTL*0͕ൃੜ͢Δ
    σʔλ૿

    View Slide

  147. ࣮ફͰϋϚΔ᠘
    w ूܭର৅͕େ͖͘ͳͬͯूܭ࣌ʹϝϞϦʹ৐Βͳ͍

    ˠ5FNQPSBSZΛ%JTLʹు͘ͷͰॏ͘ͳΔ
    w */%&9͕ແ͘5"#-&εΩϟϯΛ͍ͯͨ͠

    ˠςετ࣌͸σʔλ͕খ͔ͬͨ͞ͷͰ໰୊ʹؾ͔ͮͳ͔ͬͨ
    w ϓϩάϥϜ͕ϧʔϓͷ਺͚ͩΫΤϦΛ౤͍͛ͯͨ

    ˠߦ਺͕૿͑Δͱϧʔϓճ਺͕૿͑ͯॏ͘ͳΔ
    w ߋ৽͕ܹ͘͠*/%&9ͷஅยԽ͕ൃੜͨ͠

    ˠ61%"5&΍%&-&5&ʹΑͬͯ*/%&9͕அยԽͯ͠஗͘ͳΔ

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  151. ·ͱΊ
    3%#ͷ஌ࣝ͸ण໋͕௕͍

    View Slide

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

    View Slide

  153. ·ͱΊ
    %#ͷ໰୊͸ΫϦςΟΧϧ

    View Slide

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

    View Slide

  155. ·ͱΊ
    ۪ऀ͸ܦݧʹֶͿ
    ݡऀ͸աڈʹֶͿ

    View Slide

  156. ·ͱΊ
    3%#͸ྺ࢙͕௕͍

    View Slide

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

    View Slide

  158. ·ͱΊ
    3%#͸͍͍ͧɻ

    View Slide

  159. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ

    View Slide