目指せ3つ星インデックス #yokohama_north

MySQLのインデックスのお話

Transcript

1. ໨ࢦͤͭ੕ΠϯσοΫε
   MySQL 5.6 ͷ InnoDB ͷ͓࿩Ͱ͢ɻ
   
   2015/11/14 Yokohama North Meetup ˌ1
   
   #yokohama_north
   

   View Slide

2. ໨࣍
   • #ΠϯσοΫε
   • ͳͥΠϯσοΫεͰ଎͘ͳΔͷ͔ʁ
   • ͭ੕ΠϯσοΫε
   • ·ͱΊ
   

   View Slide

3. *OOP%#ͷΠϯσοΫε
   

   View Slide

4. ΠϯσοΫεʁ
   • *OOP%#ͷΠϯσοΫε͸ɺຊͷࡧҾʢΠϯσοΫεʣͷΑ͏ͳΠϝʔδ
   • ಛఆͷ୯ޠΛࡧҾͰௐ΂ΔͱɺԿϖʔδʹࡌ͍ͬͯΔ͔͕෼͔Δʢෳ਺ϖʔ
   δͷ৔߹΋͋Δʣ
   • *OOP%#Ͱ΋ಉ͡Α͏ͳײ͡Ͱɺ͋Δྻʹର͢ΔΠϯσοΫεΛߏங͢Δ͜ͱ
   ͕Ͱ͖ΔɻΫΤϦʹ͓͍ͯ৚݅Λຬͨ͢ߦͷओΩʔΛߏஙͨ͠ΠϯσοΫε
   Ͱ୳͢͜ͱ͕Ͱ͖Δ
   • *OOP%#Ͱ͸ɺΠϯσοΫεΛ#໦ߏ଄Ͱ࣋ͭ
   

   View Slide

5. #໦
   • #໦Ͱ͸࣮ࡍʹඞཁͳ஋͸શ
   ͯ༿ϊʔυʹ֨ೲ͞ΕΔ
   • ϧʔτͱ༿ͷؒͷϊʔυ͸ࢠϊʔ
   υ΁ͷϙΠϯλΛ࣋ͭ
   • શͯͷ༿ϊʔυ͸ϧʔτ͔Β౳
   ڑ཭ʹ͋Δ
   • *OOP%#Ͱ͸ɺ༿ϊʔυʹओΩʔ
   ͷ஋Λ֨ೲ͢Δʢޙड़ʣ
   ը૾Ҿ༻ݩ8JLJQFEJB
   IUUQTKBXJLJQFEJBPSHXJLJ##
   &$"
   

   View Slide

6. ෳ਺ྻΠϯσοΫε͸ྻͷॱং͕େࣄ
   • ΠϯσοΫε͸ɺͭͷྻʹରͯ͠ߏஙͯ͠΋͍͍͠ɺෳ਺ྻʹରͯ͠ߏங͠
   ͯ΋͍͍
   • ෳ਺ྻͷΠϯσοΫεͰ͸ɺྻͷॱং͕ͱͬͯ΋େࣄʂ
   

   View Slide

7. Sato
   Aomori
   32
   Nakamura
   Kyoto
   39
   Aoki
   Hokkaido
   19
   Baba
   Ishikawa
   65
   Endo
   Chiba
   24
   Endo
   Chiba
   24
   Endo
   Tokyo
   33
   Dobashi
   Nigata
   30
   Morita
   Okayama
   58
   Yamada
   Kanagawa
   39
   Nakamura
   Kyoto
   39
   Okada
   Shizuoka
   15
   ɾɾɾ
   ɾɾɾ
   ɾɾɾ
   ɾɾɾ
   6TFSTςʔϒϧ
   LFZ OBNF BEESFTT BHF
   
   

   View Slide

8. Sato
   Aomori
   32
   Nakamura
   Kyoto
   39
   Aoki
   Hokkaido
   19
   Baba
   Ishikawa
   65
   Endo
   Chiba
   24
   Endo
   Chiba
   24
   Endo
   Tokyo
   33
   Dobashi
   Nigata
   30
   Morita
   Okayama
   58
   Yamada
   Kanagawa
   39
   Nakamura
   Kyoto
   39
   Okada
   Shizuoka
   15
   ɾɾɾ
   ɾɾɾ
   ɾɾɾ
   ɾɾɾ
   SELECT * FROM users WHERE name = 'Endo' AND address = 'Tokyo' AND age = 33
   SELECT * FROM users ORDER BY name, address, age
   

   View Slide

9. Sato
   Aomori
   32
   Nakamura
   Kyoto
   39
   Aoki
   Hokkaido
   19
   Baba
   Ishikawa
   65
   Endo
   Chiba
   24
   Endo
   Chiba
   24
   Endo
   Tokyo
   33
   Dobashi
   Nigata
   30
   Morita
   Okayama
   58
   Yamada
   Kanagawa
   39
   Nakamura
   Kyoto
   39
   Okada
   Shizuoka
   15
   ɾɾɾ
   ɾɾɾ
   ɾɾɾ
   ɾɾɾ
   SELECT * FROM users WHERE name = 'Endo' AND address = 'Tokyo' AND age = 33
   SELECT * FROM users ORDER BY name, address, age
   ΠϯσοΫεͷશͯͷྻΛࢦఆ͍ͯ͠ΔͷͰޮ͘
   

   View Slide

10. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Morita' AND address = 'Okayama'
    SELECT * FROM users ORDER BY name, address
    

    View Slide

11. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Morita' AND address = 'Okayama'
    SELECT * FROM users ORDER BY name, address
    ΠϯσοΫεͷࠨ୺͔Βͷྻ͕ࢦఆ͞Ε͍ͯΔͷͰޮ͘
    

    View Slide

12. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE address = 'Okayama' AND age = 58
    

    View Slide

13. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE address = 'Okayama' AND age = 58
    ΠϯσοΫεͷࠨ୺ͷྻ͕ࢦఆ͞Εͯͳ͍ͷͰޮ͔ͳ͍
    

    View Slide

14. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Morita' AND age = 58
    

    View Slide

15. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Morita' AND age = 58
    ΠϯσοΫεͷྻ͕εΩοϓ͞Ε͍ͯΔͨΊࠨ୺ͷྻͷΈ͕ޮ͘
    .PSJUB͞ΜશͯͷߦΛಡΈࠐΉඞཁ͕͋Δ
    

    View Slide

16. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name LIKE 'M%'
    SELECT * FROM users WHERE name > 'Endo'
    

    View Slide

17. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name LIKE 'M%'
    SELECT * FROM users WHERE name > 'Endo'
    ൣғͷݕࡧͰ΋ΠϯσοΫε͸ޮ͘
    

    View Slide

18. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' AND address like 'T%'
    

    View Slide

19. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' AND address like 'T%'
    ͋Δྻͷ׬શҰகͱผͷྻͷൣғݕࡧͰ΋ޮ͘
    

    View Slide

20. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Morita' AND address LIKE 'O%' AND age = 58
    

    View Slide

21. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Morita' AND address LIKE 'O%' AND age = 58
    ൣғݕࡧͰ࢖ΘΕͨྻΑΓӈଆͷྻ͸ޮ͔ͳ͍
    0`Ͱ࢝·ΔॅॴʹॅΉ`.PSJUB`͞ΜͷߦΛશͯऔಘ͢Δඞཁ͕͋Δ
    

    View Slide

22. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Morita'
    AND address IN ('Okinawa', 'Osaka') AND age = 58
    

    View Slide

23. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Morita'
    AND address IN ('Okinawa', 'Osaka') AND age = 58
    -*,&Λ*/ʹॻ͖௚͢͜ͱ͕Ͱ͖Ε͹ޮ͘
    

    View Slide

24. ΫϥελԽΠϯσο
    Ϋε
    • ΫϥελԽΠϯσοΫε͸ɺΠ
    ϯσοΫεͷछྨͷ໊લͰ͸ͳ
    ͍
    • ༿ϊʔυͷ஋ʹɺߦࣗମ͕֨ೲ
    ͞Ε͍ͯΔΠϯσοΫεͷ͜ͱ
    • *OOP%#͸ओΩʔͰΫϥελԽ
    ΠϯσοΫεΛͭ͘Δ
    • ओΩʔ͕ྡͷߦ͸ɺσΟεΫ্
    ͰฒΜͰ͍ΔՄೳੑ͕ߴ͍
    ߦ ߦ ߦ ߦ
    

    View Slide

25. ΫϥελԽΠϯσοΫεͷ஫ҙ఺
    • ΫϥελԽʹΑΓۙ͘ʹฒΜͩσʔλΛऔಘ͢Δͱ͖ʹ*0ΛݮΒ͢͜ͱ͕Ͱ
    ͖Δɻͦ΋ͦ΋શͯͷσʔλ͕ϝϞϦʹऩ·Δ৔߹͸ͦΕ΄ͲޮՌతͰͳ͍
    • ओΩʔͷॱংͰૠೖ͢Δͷ͕࠷΋ޮ཰త͕ͩɺओΩʔͷॱংҎ֎Ͱૠೖ͞Ε
    Δͱϖʔδ෼ׂ͕ى͖ΔՄೳੑ͕͋Γඇޮ཰ʹͳΔ
    • ओΩʔҎ֎ͷΠϯσοΫεΛ࢖͏ͱ͖ΠϯσοΫεͷ୳ࡧ͕ճʹͳΔ
    ओΩʔ
    ओΩʔ
    ओΩʔ
    ओΩʔ
    ηΧϯμϦ
    ΠϯσοΫε
    ߦ ߦ ߦ ߦ
    ओΩʔͷ
    ΠϯσοΫε
    ʢΫϥελԽʣ
    

    View Slide

26. ͳͥ*OEFYͰ଎͘ͳΔͷ͔ʁ
    

    View Slide

27. 8)&3&۟
    • 4&-&$5'30.UBCMF8)&3&DPM"/%DPM
    • ओΩʔ͕DPMɺηΧϯμϦΠϯσοΫε͕ͳ͍৔߹ɺΫϥελԽΠϯσο
    ΫεͷશͯͷϦʔϑΛσΟεΫͷϒϩοΫʢϖʔδʣ͔ΒϝϞϦʹಡΈࠐ
    Ή
    • ϝϞϦʹಡΈࠐΜͰ͔ΒɺDPMΛຬͨ͢ߦ͚ͩΛฦ͢
    • σʔλ͕ଟ͍৔߹ɺଟ਺ͷϖʔδΛಡΈඞཁ͕͋ΔͨΊɺ*0͕ͨ͘͞Μൃ
    ੜ͢Δ
    • σΟεΫ*0͸ɺγʔΫ΍ճస଴͕ͪൃੜ͢ΔͨΊɺͱͯ΋஗͍ʂ
    

    View Slide

28. • 4&-&$5'30.UBCMF8)&3&DPM"/%DPM
    • ओΩʔ͕DPMɺDPͷηΧϯμϦΠϯσοΫε͕͋Δ৔߹ɺηΧϯμ
    ϦΠϯσοΫεΛ࢖͍ɺDPMΛຬͨ͢ϦʔϑΛऔಘ͢Δ
    • ͜ͷ࣌ɺDPM͸ॱ൪ʹฒΜͰ͍ΔͨΊɺඞཁͳϦʔϑ͕ಉҰϖʔδ಺ʹ
    ͋ΔՄೳੑ͕ߴ͘ɺಡΉϖʔδ਺͕ݮΓɺ*0͸ͦ͜·Ͱ૿͑ͳ͍ʢՄ
    ೳੑ͕ߴ͍ʣ
    • ࣍ʹɺηΧϯμϦΠϯσοΫε͔Βऔಘͨ͠Ϧʔϑ͔ΒಘΒΕΔओΩʔ
    Λ࢖ͬͯɺΫϥελԽΠϯσοΫε͔ΒߦΛಡΈࠐΉ
    • ߦ͸ओΩʔͷॱ൪ͰฒΜͰ͍Δͷ͕ɺηΧϯμϦΠϯσοΫε͔Βऔ
    ಘͨ͠ओΩʔ͕ॱ൪ʹฒΜͰ͍Δͱ͸ݶΒͳ͍ͨΊɺෳ਺ͷϖʔδΛ
    ಡΉඞཁ͕͋ΔϥϯμϜ*0
    • ηΧϯμϦΠϯσοΫεͰओΩʔ͕ߜΕͯͳ͍ͱ*0͕ଟ͍ʂʢߦ਺
    ͕গͳ͍৔߹͸ϑϧεΩϟϯͷํ͕଎͍Մೳੑ͋Γʣ
    

    View Slide

29. 03%&3#:۟
    • 4&-&$5'30.UBCMF8)&3&03%&3#:DPM%&4$-*.*5
    • ओΩʔ͕DPMɺηΧϯμϦΠϯσοΫε͕ͳ͍৔߹ɺΫϥελԽΠϯσο
    ΫεͷશͯͷϦʔϑΛσΟεΫͷϒϩοΫʢϖʔδʣ͔ΒϝϞϦʹಡΈࠐ
    Ή
    • ϝϞϦʹಡΈࠐΜͰ͔ΒɺDPMͷ߱ॱͰιʔτͯ݅͠ฦ͢
    • σʔλ͕ଟ͍৔߹ɺଟ਺ͷϖʔδΛಡΈඞཁ͕͋ΔͨΊɺ*0͕ͨ͘͞Μൃ
    ੜ͢Δ
    

    View Slide

30. • 4&-&$5'30.UBCMF8)&3&03%&3#:DPM%&4$-*.*5
    • ओΩʔ͕DPMɺDPͷ߱ॱͷηΧϯμϦΠϯσοΫε͕͋Δ৔߹ɺη
    ΧϯμϦΠϯσοΫεΛ࢖͏
    • ͜ͷ࣌ɺDPM͸࠷ॳ͔Β03%&3#:Ͱࢦఆͨ͠ॱ൪ʹฒΜͰ͍Δͨ
    Ίɺιʔτ͢Δඞཁ͕ͳ͍ʢιʔτͰඞཁʹͳΔҰ࣌ςʔϒϧ΋ෆཁ
    ʹͳΔʣ
    • ηΧϯμϦΠϯσοΫε͔Βඞཁͳ݅ͷϦʔϑ͔ΒओΩʔΛಘΒΕ
    Δ
    

    View Slide

31. ΠϯσοΫε͕ղܾͯ͘͠ΕΔ͜ͱ
    • 8)&3&۟ɺ03%&3#:۟ɺ(3061#:۟Ͱར༻͢Δσʔλ͕ۙ͘ʹฒͿɻ
    ͦΕʹΑͬͯɾɾɾ
    • ௐ΂ͳ͚Ε͹ͳΒͳ͍ߦͷྔ͕গͳ͘ͳΔ*0͕ݮΔ
    • .Z42-಺Ͱɺιʔτ΍Ұ࣌ςʔϒϧ͕ෆཁʹͳΔ
    • ͨͩ͠ɺ্هͷޮՌΛಘΒΕΔΑ͏ʹΠϯσοΫεΛઃܭ͢Δඞཁ͕͋Δ
    

    View Slide

32. ͭ੕ΠϯσοΫε
    

    View Slide

33. ͭ੕ͰΠϯσοΫεΛධՁ
    ͭ੕
    zؔ࿈͢ΔߦΛྡΓ߹Θͤʹ഑ஔ͍ͯ͠Δz
    ͭ੕
    zߦ͕ΫΤϦͰඞཁͳॱংʹฒΜͰ͍Δz
    ͭ੕
    zΫΤϦʹඞཁͳྻ͕શؚͯ·Ε͍ͯΔz
    ࣮ફϋΠύϑΥʔϚϯε.Z42-ୈ൛ΑΓҾ༻
    

    View Slide

34. ͭ੕ΠϯσοΫε
    zؔ࿈͢ΔߦΛྡΓ߹Θͤʹ഑ஔ͍ͯ͠Δz
    • ετϨʔδ͔ΒϖʔδΛಡΈऔΓɺͦͷதͷߦʹ͔͠ΞΫηε͠ͳ͍ͷ͸ແ
    ବ͕ଟ͍
    • ϖʔδ಺ʹඞཁͳߦ͕ଟؚ͘·Ε͍ͯΔํ͕ޮ཰త
    

    View Slide

35. ओΩʔͱૠೖॱং
    • ύϑΥʔϚϯεͷ؍఺͔Β͢ΔͱɺαϩήʔτΩʔ͸ޮՌతͰ͋Γɺ66*%Λ
    ओΩʔʹ͢Δ͜ͱ͸ྑ͘ͳ͍
    • 66*%஋ʹΑͬͯσʔλͷΫϥελԽ͕΄ͱΜͲͰ͖ͳ͘ͳΔɺͭ·Γϖʔδ
    ෼ׂ͕ൃੜ͠அยԽ͢Δ
    

    View Slide

36. ͭ੕ΠϯσοΫε
    zߦ͕ΫΤϦͰඞཁͳॱংʹฒΜͰ͍Δz
    • ΠϯσοΫε͕ඞཁͳॱংͰฒΜͰ͍Δ৔߹͸ɺߦΛऔಘ͔ͯ͠Βιʔτ͢
    ΔॲཧΛආ͚Δ͜ͱ͕Ͱ͖Δ
    

    View Slide

37. ιʔτͱΠϯσοΫεεΩϟϯ
    • ߦΛऔಘ͔ͯ͠Βιʔτ͢Δ͔ɺΠϯσοΫεΛऔಘ͍ͨ͠ॱ൪ʹฒ΂ͯஔ
    ͍͔ͯΒߦΛऔಘ͢Δ͔
    • ߦΛऔಘ͔ͯ͠Βιʔτ͢Δ৔߹ɺ&YQMBJOͷ&YUSBྻʹ6TJOHpMFTPSUͱ
    දࣔ͞ΕΔ
    • ΠϯσοΫε͕औಘ͍ͨ͠ॱ൪ʹฒΜͰ͍Δ৔߹ɺಛʹදࣔ͸ͳ͍ɺ6TJOH
    pMFTPSU΋දࣔ͞Εͳ͍
    

    View Slide

38. • 'JMFTPSUΛڐ༰͠ͳ͍ͱ͢ΔͱɺΫΤϦͱΠϯσοΫεͷઃܭ੍͕ݶ͞
    ΕΔ
    • ͭͷΠϯσοΫεͰ8)&3&۟ͱ03%&3#:۟ͰඞཁͳྻΛΧόʔ͢
    Δඞཁ͕͋Δ
    • 8)&3&۟Λ༏ઌ͢Δ͔ɺ03%&3#:۟Λ༏ઌ͢Δ͔ɺσʔλͱΫΤϦ
    ͷಛੑΛݟͯΑ͘ݕ౼͢Δඞཁ͕͋Δ
    ˞8)&3&۟Ͱඞཁͳߦ͕ߦͱ͔ʹߜΓࠐ·ΕΔͷͰ͋Ε͹ɺߦ
    Λऔಘ͔ͯ͠Βιʔτͯ͠΋໰୊ͳ͍
    ˞ߦΛऔಘ͔ͯ͠Β8)&3&۟ͷߜΓࠐΈΛ͢Δ৔߹ɺ&YQMBJOͷ&YUSB
    ྻʹ6TJOHXIFSFͱදࣔ͞ΕΔ
    • 8IFSFૂ͍ͷΩʔɺPSEFSCZૂ͍ͷΩʔ
    IUUQXXXTMJEFTIBSFOFUZPLVXIFSFPSEFSCZ
    

    View Slide

39. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    6TFSTςʔϒϧ
    LFZ OBNF BEESFTT BHF
    
    

    View Slide

40. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' ORDER BY address
    

    View Slide

41. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' ORDER BY address
    ΠϯσοΫεͷྻΛࠨ͔Βॱ൪ʹࢦఆ͍ͯ͠ΔͷͰޮ͘
    

    View Slide

42. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' ORDER BY address, age
    

    View Slide

43. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' ORDER BY address, age
    ΠϯσοΫεͷྻΛࠨ͔Βॱ൪ʹࢦఆ͍ͯ͠ΔͷͰޮ͘
    

    View Slide

44. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users ORDER BY address
    

    View Slide

45. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users ORDER BY address
    ΠϯσοΫεͷྻΛεΩοϓ͍ͯ͠ΔͷͰޮ͔ͳ͍
    

    View Slide

46. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' ORDER BY address DESC
    

    View Slide

47. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' ORDER BY address DESC
    ΠϯσοΫεͷྻ͸શͯঢॱͳͷͰޮ͘ͷ͸OBNFͰͷߜΓࠐΈ·Ͱ
    

    View Slide

48. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' ORDER BY age
    

    View Slide

49. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' ORDER BY age
    ΠϯσοΫεͷྻΛεΩοϓ͍ͯ͠ΔͷͰޮ͘ͷ͸OBNFͰͷߜΓࠐΈ·Ͱ
    

    View Slide

50. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' address LIKE ’T%'
    ORDER BY address, age
    

    View Slide

51. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' address LIKE ’T%'
    ORDER BY address, age
    ൣғݕࡧͰ࢖ͬͨྻΑΓޙΖͷྻ͸࢖͑ͳ͍ͷͰޮ͘ͷ͸BEESFTTͰͷߜΓࠐΈ·Ͱ
    

    View Slide

52. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' address IN ('Tokyo', 'Tokushima')
    ORDER BY address, age
    

    View Slide

53. Sato
    Aomori
    32
    Nakamura
    Kyoto
    39
    Aoki
    Hokkaido
    19
    Baba
    Ishikawa
    65
    Endo
    Chiba
    24
    Endo
    Chiba
    24
    Endo
    Tokyo
    33
    Dobashi
    Nigata
    30
    Morita
    Okayama
    58
    Yamada
    Kanagawa
    39
    Nakamura
    Kyoto
    39
    Okada
    Shizuoka
    15
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    ɾɾɾ
    SELECT * FROM users WHERE name = 'Endo' address IN ('Tokyo', 'Tokushima')
    ORDER BY address, age
    ιʔτͷ৔߹͸*/۟Ͱ΋มΘΒͣɺޮ͘ͷ͸BEESFTTͰͷߜΓࠐΈ·Ͱ
    

    View Slide

54. +0*/03%&3#:
    • 4&-&$5DPM'30.U*//&3+0*/U0/UDPMUDPM8)&3&
    UDPM03%&3#:UDPM
    • +0*/ͯ͠ɺUBCMFͷΧϥϜΛ8)&3&۟ͰɺUBCMFͷΧϥϜΛ03%&3#:
    ۟Ͱ࢖༻͍ͯ͠Δ৔߹ɺ6TJOHUFNQPSBSZ6TJOHpMFTPSUʹͳͬͯ͠·͏
    • ׽ Φτί
    ͷίϯϐϡʔλಓ6TJOHpMFTPSU
    IUUQOJQQPOEBOKJCMPHTQPUKQVTJOHpMFTPSUIUNM
    

    View Slide

55. ͭ੕ΠϯσοΫε
    zΫΤϦʹඞཁͳྻ͕શؚͯ·Ε͍ͯΔz
    • ߦΛऔಘ͢Δඞཁ͕ͳ͘ͳΓϥϯμϜ*0Λආ͚Δ͜ͱ͕Ͱ͖Δ
    

    View Slide

56. ΧόϦϯάΠϯσοΫε
    • ηΧϯμϦΠϯσοΫεͷϦʔϑʹ͸ɺΠϯσοΫεͰࢦఆͨ͠ྻͱओΩʔ
    ͕֨ೲ͞Ε͍ͯΔ
    • ΫΤϦͰඞཁͳྻ͢΂͕ͯηΧϯμϦΠϯσοΫεͷϦʔϑʹଘࡏ͢Δͱ͖ɺ
    ΧόϦϯάΠϯσοΫεͱݺͿ
    • ΫΤϦͰΧόϦϯάΠϯσοΫεΛ࢖͑Δͱ͖ɺ&YQMBJOͷ&YUSBྻʹ 6TJOH
    JOEFY͕දࣔ͞ΕΔ
    

    View Slide

57. • ΧόϦϯάΠϯσοΫεΛ࢖͑͹ߦΛऔಘ͢Δඞཁ͕ͳ͘ͳΓɺओΩʔ
    ͷΠϯσοΫεΛ୳ࡧ͢Δඞཁ΋ͳ͘ͳΔ
    • ϥϯμϜ*0͕ͳ͘ͳΔʂ
    ओΩʔ
    ओΩʔ
    ओΩʔ
    ओΩʔ
    ηΧϯμϦ
    ΠϯσοΫε
    ߦ ߦ ߦ ߦ
    ओΩʔͷ
    ΠϯσοΫε
    ʢΫϥελԽʣ
    ηΧϯμϦΠϯσο
    Ϋε͚ͩͰ׬݁
    ओΩʔͷΠϯσοΫ
    εΛݟΔඞཁͳ͠
    

    View Slide

58. ஗Ԇ݁߹
    • ΠϯσοΫεʹؚ·Ε͍ͯͳ͍ྻ͕͋ΔͨΊߦΛऔಘ͢Δඞཁ͕͋Δ
    • ߦΛऔಘ͔ͯ͠Β0''4&5 -*.*5ʹΑ͍ͬͯΒͳ͍ߦ͕ࣺͯΒΕΔ
    SELECT name, introduction FROM users ORDER BY name, address LIMIT 100, 10
    SELECT name, introduction
    FROM users t1
    INNER JOIN ( SELECT id FROM users ORDER BY name, address LIMIT 100, 10 ) t2
    ON t1.id = t2.id
    • ΧόϦϯάΠϯσοΫεͰ݅ʹߜΓࠐΜͰ͔ΒߦΛऔಘ͢Δ
    • ߦΛऔಘ͢Δલʹඞཁͳߦ਺Λ0''4&5 -*.*5ͰߜΓࠐΊΔ
    

    View Slide

59. ·ͱΊ
    

    View Slide

60. ͭ੕ΠϯσοΫε
    • ؔ࿈͢ΔߦΛྡΓ߹Θͤʹ഑ஔ͍ͯ͠Δ
    ετϨʔδ͔ΒϖʔδΛಡΈऔΓɺͦͷதͷߦʹ͔͠ΞΫηε͠ͳ͍ͷ
    ͸ແବ͕ଟ͍ɻϖʔδ಺ʹඞཁͳߦ͕ଟؚ͘·Ε͍ͯΔํ͕ޮ཰తͰ͋Δ
    • 8)&3&۟Ͱࢦఆ͢ΔྻͰɺΠϯσοΫεΛͭ͘Δ
    • ൣғݕࡧʹ͓͚ΔϥϯμϜ*0ΛݮΒ͢
    

    View Slide

61. ͭ੕ΠϯσοΫε
    • ߦ͕ΫΤϦͰඞཁͳॱংʹฒΜͰ͍Δ
    ΠϯσοΫε͕ඞཁͳॱংͰฒΜͰ͍Δ৔߹͸ɺߦΛऔಘ͔ͯ͠Βιʔτ
    ͢ΔॲཧΛආ͚Δ͜ͱ͕Ͱ͖Δ
    • ΠϯσοΫεΛऔಘ͍ͨ͠ॱ൪ʹฒ΂͓ͯ͘ʢ6TJOHpMFTPSUΛආ͚Δʣ
    • ͭͷΠϯσοΫεͰ8)&3&۟ͱ03%&3#:۟ΛΧόʔ
    • ͦΕ͕೉͍͠৔߹͸ɺ8)&3&۟ʹΑΔߜΓࠐΈΛॏࢹ͢Δ͔ɺ6TJOH
    pMFTPSUΛආ͚Δ͔ɺͲͪΒΛ༏ઌ͢Δ͔Λݕ౼͢Δ͜ͱ
    

    View Slide

62. ͭ੕ΠϯσοΫε
    • ΫΤϦʹඞཁͳྻ͕શؚͯ·Ε͍ͯΔ
    ߦΛऔಘ͢Δඞཁ͕ͳ͘ͳΓϥϯμϜ*0Λආ͚Δ͜ͱ͕Ͱ͖Δ
    • ΫΤϦͰඞཁͳྻΛશͯΧόʔ͢ΔΠϯσοΫεΛΧόϦϯάΠϯσοΫε
    ͱݺͿ
    • 6TJOHJOEFYͱ&YQMBJOͰදࣔ͞ΕͨΒΧόϦϯάΠϯσοΫεͱͯ͠Πϯ
    σοΫεΛ࢖͍͑ͯΔ
    

    View Slide

63. ࢀߟ
    • ࣮ફϋΠύϑΥʔϚϯε.Z42-ୈ൛
    IUUQXXXBNB[PODPKQ࣮ફϋΠύϑΥʔϚϯε.Z42-ୈ൛#BSPO
    4DIXBSU[EQ
    • 8IFSFૂ͍ͷΩʔɺPSEFSCZૂ͍ͷΩʔ
    IUUQXXXTMJEFTIBSFOFUZPLVXIFSFPSEFSCZ
    • ׽ Φτί
    ͷίϯϐϡʔλಓ6TJOHpMFTPSU
    IUUQOJQQPOEBOKJCMPHTQPUKQVTJOHpMFTPSUIUNM
    

    View Slide