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

MySQL/InnoDB の裏側 / Rails Developers Meetup 2018 Day 1

MySQL/InnoDB の裏側 / Rails Developers Meetup 2018 Day 1

Rails Developers Meetup 2018: Day 1 (https://techplay.jp/event/639872) の発表資料です。
See also Rails Developers Meetup 2018 で「MySQL/InnoDB の裏側」を発表しました - あらびき日記 https://abicky.net/2018/03/25/141353/

B38b75d449a3b6bc60aaf7a0c44674cf?s=128

Takeshi Arabiki

March 24, 2018
Tweet

Transcript

  1. .Z42-*OOP%#ͷཪଆ 3BJMT%FWFMPQFST.FFUVQ   3FQSPגࣜձࣾ 5BLFTIJ"SBCJLJ !B@CJDLZ

  2. ࣗݾ঺հ w 5XJUUFS!B@CJDLZ w #MPH͋Βͼ͖೔ه w 3BJMTྺ೥ͪΐͬͱ w ॴଐ3FQSPגࣜձࣾ ‣

    ΠϯϑϥΤϯδχΞϲ݄໨
  3. 3BJMTͱ.Z42-

  4. େྔͷΠϯσοΫε CREATE TABLE `products` ( `id` bigint(20) unsigned NOT NULL

    AUTO_INCREMENT, `shop_id` bigint(20) unsigned NOT NULL, `name` varchar(255) NOT NULL, `price` int(10) unsigned NOT NULL, `started_at` datetime NOT NULL, `ended_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `ix_shop` (`shop_id`), KEY `ix_shop_start_end` (`shop_id`,`started_at`,`ended_at`), KEY `ix_shop_end_start` (`shop_id`,`ended_at`,`started_at`), KEY `ix_end_start` (`ended_at`,`started_at`) ) ENGINE=InnoDB;
  5. ԿͰ΋͔ΜͰ΋Ωϟογϡ # ஫จཤྺͷ͋ΔϢʔβ໊Λऔಘ user_names = Rails.cache.fetch(“user_names”) do User.joins(:orders).distinct.pluck(:name) end

  6. ຊ౰ʹඞཁͰ͔͢ʁ

  7. IUUQUFDIMJGFDPPLQBEDPNFOUSZ

  8. ΋ͬͱ.Z42-ͷ͜ͱΛ ཧղͯ͋͛͠Α͏ʂʂ

  9. ΞδΣϯμ w4&-&$5ΫΤϦͷ࣮ߦϑϩʔ w*OOP%#ͷ֓ཁ wԠ༻ྫ

  10. લఏ w .Z42- w 4UPSBHF&OHJOFͱͯ͠*OOP%#Λ࢖༻

  11. ΞδΣϯμ w4&-&$5ΫΤϦͷ࣮ߦϑϩʔ w*OOP%#ͷ֓ཁ wԠ༻ྫ

  12. 4&-&$5ΫΤϦͷ ࣮ߦϑϩʔ

  13. 4&-&$5ΫΤϦͷ࣮ߦϑϩʔ $MJFOU &YFDVUPS 4UPSBHF&OHJOF
 *OOP%# 0QUJNJ[FS 1BSTFS TUBUJTUJDT SFDPSET

  14. 4&-&$5ΫΤϦͷ࣮ߦϑϩʔ $MJFOU &YFDVUPS 4UPSBHF&OHJOF
 *OOP%# 0QUJNJ[FS 1BSTFS TUBUJTUJDT SFDPSET

  15. 0QUJNJ[FS w 2VFSZ&YFDVUJPO1MBO 2&1 ͷ࡞੒ ‣ 42-ͷॻ͖׵͑ -PHJDBMUSBOTGPSNBUJPOT  ‣

    ίετϕʔεͷ࠷దϓϥϯͷબ୒ ‣ FUD
  16. 2&1ͷ֬ೝ &YQMBJO mysql> EXPLAIN SELECT name FROM users WHERE id

    = 1 AND id < 10 AND 1 = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
  17. 42-ͷॻ͖׵͑ mysql> EXPLAIN SELECT name FROM users WHERE id =

    1 AND id < 10 AND 1 = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select 'Edison Marks' AS `name` from `railsdm`.`users` where 1 1 row in set (0.00 sec)
  18. 42-ͷॻ͖׵͑ mysql> EXPLAIN SELECT name FROM users WHERE id =

    1 AND id < 10 AND 1 = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select 'Edison Marks' AS `name` from `railsdm`.`users` where 1 1 row in set (0.00 sec) ແବͳ৚݅ͷ࡟আ QSJNBSZLFZ΍VOJRVFLFZͱͷ౳Ձൺֱ͸ల։͞ΕΔ
  19. w ίετͷ߹ܭ͕࠷খʹͳΔϓϥϯΛબ୒ ‣ ධՁ͢ΔϨίʔυ਺ $16DPTU  ‣ ಡΈࠐΉϖʔδ਺ *0DPTU 

    ‣ ιʔτͰൺֱ͢Δճ਺ $16DPTU  ‣ ςϯϙϥϦςʔϒϧͷSFBEXSJUF *0DPTU ίετϕʔεͷ࠷దϓϥϯͷબ୒
  20. 0QUJNJ[FS5SBDF mysql> SET optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.00

    sec) mysql> SET optimizer_trace_max_mem_size = 1048576; Query OK, 0 rows affected (0.00 sec) mysql> SELECT name FROM users WHERE id = 1\G *************************** 1. row *************************** name: Edison Marks 1 row in set (0.00 sec) mysql> SELECT * FROM information_schema.optimizer_trace\G *************************** 1. row *************************** QUERY: SELECT name FROM users WHERE id = 1 TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { (snip)
  21. mysql> SET optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.00 sec)

    mysql> SET optimizer_trace_max_mem_size = 1048576; Query OK, 0 rows affected (0.00 sec) mysql> SELECT name FROM users WHERE id = 1\G *************************** 1. row *************************** name: Edison Marks 1 row in set (0.00 sec) mysql> SELECT * FROM information_schema.optimizer_trace\G *************************** 1. row *************************** QUERY: SELECT name FROM users WHERE id = 1 TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { (snip) ৄ͘͠͸ʮৄղ.Z42-ʯͷઅࢀর 0QUJNJ[FS5SBDF
  22. 4&-&$5ΫΤϦͷ࣮ߦϑϩʔ $MJFOU &YFDVUPS 4UPSBHF&OHJOF
 *OOP%# 0QUJNJ[FS 1BSTFS TUBUJTUJDT SFDPSET

  23. &YFDVUPS w 0QUJNJ[FS͕࡞੒ͨ͠2&1Λ࣮ߦ w 4UPSBHF&OHJOF͔ΒϨίʔυΛऔಘ w ৚݅ʹ߹Θͳ͍ϨίʔυΛϑΟϧλϦϯά ‣ &YQMBJOͷ&YUSBʹ6TJOHXIFSF͕ग़Δ w

    (3061#: 03%&3#:FUD w ΫϥΠΞϯτ΁ϨίʔυΛૹ৴
  24. 1FSGPSNBODF4DIFNB mysql> SET @s = '2018-03-22 12:00:00', @e = '2018-03-24

    00:00:00'; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN SELECT * FROM products -> WHERE started_at <= @s AND ended_at >= @e AND price >= 1000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: products partitions: NULL type: range possible_keys: ix_ended_at_started_at key: ix_ended_at_started_at key_len: 5 ref: NULL rows: 412 filtered: 11.11 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM products -> WHERE started_at <= @s AND ended_at >= @e AND price >= 1000\G (snip) 4UPSBHF&OHJOF͕૸ࠪ͢ΔϨίʔυ਺ ʢ࣮ଌ஋PS༧ଌ஋ʣ
  25. 1FSGPSNBODF4DIFNB mysql> SELECT -> SQL_TEXT, -> ROWS_SENT, -> ROWS_EXAMINED ->

    FROM -> performance_schema.events_statements_history -> INNER JOIN -> performance_schema.threads -> ON threads.THREAD_ID = events_statements_history.THREAD_ID -> WHERE -> threads.PROCESSLIST_ID = CONNECTION_ID() -> AND EVENT_NAME = 'statement/sql/select' -> ORDER BY -> TIMER_END DESC LIMIT 1 -> \G *************************** 1. row *************************** SQL_TEXT: SELECT * FROM products WHERE started_at <= @s AND ended_at >= @e AND price >= 1000 ROWS_SENT: 53 ROWS_EXAMINED: 83 1 row in set (0.00 sec) &YFDVUPS͕ධՁͨ͠ߦ਺ʢ4UPSBHF&OHJOF͕ฦͨ͠ߦ਺ʣ ΫϥΠΞϯτʹฦͨ͠ߦ਺ʢ৚݅Λຬͨͨ͠ߦ਺ʣ
  26. 4&-&$5ΫΤϦͷ࣮ߦϑϩʔ $MJFOU &YFDVUPS 4UPSBHF&OHJOF
 *OOP%# 0QUJNJ[FS 1BSTFS TUBUJTUJDT SFDPSET

  27. 4UPSBHF&OHJOF *OOP%# w 0QUJNJ[FSʹ౷ܭ஋Λฦ͢ w &YFDVUPSʹϨίʔυΛฦ͢

  28. ΞδΣϯμ w4&-&$5ΫΤϦͷ࣮ߦϑϩʔ w*OOP%#ͷ֓ཁ wԠ༻ྫ

  29. *OOP%#ͷ֓ཁ

  30. *OOP%# w .Z42-ͷσϑΥϧτͷ4UPSBHF&OHJOF w "$*%४ڌͷτϥϯβΫγϣϯ w ߦϨϕϧϩοΫ w # 5SFFΠϯσοΫε

    w ΫϥελΠϯσοΫε w ֎෦Ωʔαϙʔτ
  31. *OOP%# w .Z42-ͷσϑΥϧτͷ4UPSBHF&OHJOF w "$*%४ڌͷτϥϯβΫγϣϯ w ߦϨϕϧϩοΫ w # 5SFFΠϯσοΫε

    w ΫϥελΠϯσοΫε w ֎෦Ωʔαϙʔτ
  32. ࣍਺ͷ# 5SFF 2 7 value value 2 1 value value

    7 4 value value 11 9
  33. w ໦ߏ଄ͷҰछ w ಺෦ϊʔυ͸࠷େCݸͷΩʔͱ࠷େCݸͷࢠϊʔυΛ࣋ͭ ‣ C͸࣍਺ w ಺෦ϊʔυ͸஋Λ࣋ͨͳ͍ w ༿ϊʔυͷ֤Ωʔ͸஋Λ࣋ͭ

    w ༿ϊʔυ͸࣍ͷ༿ϊʔυ΁ͷϙΠϯλΛ࣋ͭ w ͰݕࡧͰ͖Δ # 5SFF O(logb n) <latexit sha1_base64="rWtSNavdy1xFzCXdauHA4dKxbZ0=">AAACfnicfVBdSxtBFJ1srdXU2mgf+zIYRFs0blTQvkntQ19EBVOF7BLuTm7WwflYZmalYdk/4q/xVf+B/8bZmIDfF4Y5nHvu10kywa0Lw7ta8GHq4/Snmdn657kv818bC4v/rM4Nww7TQpuzBCwKrrDjuBN4lhkEmQg8TS72q/zpJRrLtTpxwwxjCaniA87AearX2C6iUZOuSZO4CFubYRVrL0B5uBoJnfYSqn6UvUZzwtOXoN0a/WGTjOOot1Dbivqa5RKVYwKs7bbDzMUFGMeZwLIe5RYzYBeQYtdDBRJtXIw2K+myZ/p0oI1/ytER+7iiAGntUCZeKcGd2+e5inw1l8hnk91gNy64ynKHij0MHuSCOk0r72ifG2RODD0AZrjfnbJzMMCcd7ge/UF/m8EDP+cwQwNOm59FBCaVXJX+1jRaq9B7Qvg/EXrkbZ54Sd8Gnc3Wr1b7eLu593vs9wz5TpbIKmmTHbJH/pIj0iGMXJFrckNug1qwEqwHGw/SoDau+UaeRLB7D6bRv64=</latexit> <latexit sha1_base64="rWtSNavdy1xFzCXdauHA4dKxbZ0=">AAACfnicfVBdSxtBFJ1srdXU2mgf+zIYRFs0blTQvkntQ19EBVOF7BLuTm7WwflYZmalYdk/4q/xVf+B/8bZmIDfF4Y5nHvu10kywa0Lw7ta8GHq4/Snmdn657kv818bC4v/rM4Nww7TQpuzBCwKrrDjuBN4lhkEmQg8TS72q/zpJRrLtTpxwwxjCaniA87AearX2C6iUZOuSZO4CFubYRVrL0B5uBoJnfYSqn6UvUZzwtOXoN0a/WGTjOOot1Dbivqa5RKVYwKs7bbDzMUFGMeZwLIe5RYzYBeQYtdDBRJtXIw2K+myZ/p0oI1/ytER+7iiAGntUCZeKcGd2+e5inw1l8hnk91gNy64ynKHij0MHuSCOk0r72ifG2RODD0AZrjfnbJzMMCcd7ge/UF/m8EDP+cwQwNOm59FBCaVXJX+1jRaq9B7Qvg/EXrkbZ54Sd8Gnc3Wr1b7eLu593vs9wz5TpbIKmmTHbJH/pIj0iGMXJFrckNug1qwEqwHGw/SoDau+UaeRLB7D6bRv64=</latexit> <latexit sha1_base64="rWtSNavdy1xFzCXdauHA4dKxbZ0=">AAACfnicfVBdSxtBFJ1srdXU2mgf+zIYRFs0blTQvkntQ19EBVOF7BLuTm7WwflYZmalYdk/4q/xVf+B/8bZmIDfF4Y5nHvu10kywa0Lw7ta8GHq4/Snmdn657kv818bC4v/rM4Nww7TQpuzBCwKrrDjuBN4lhkEmQg8TS72q/zpJRrLtTpxwwxjCaniA87AearX2C6iUZOuSZO4CFubYRVrL0B5uBoJnfYSqn6UvUZzwtOXoN0a/WGTjOOot1Dbivqa5RKVYwKs7bbDzMUFGMeZwLIe5RYzYBeQYtdDBRJtXIw2K+myZ/p0oI1/ytER+7iiAGntUCZeKcGd2+e5inw1l8hnk91gNy64ynKHij0MHuSCOk0r72ifG2RODD0AZrjfnbJzMMCcd7ge/UF/m8EDP+cwQwNOm59FBCaVXJX+1jRaq9B7Qvg/EXrkbZ54Sd8Gnc3Wr1b7eLu593vs9wz5TpbIKmmTHbJH/pIj0iGMXJFrckNug1qwEqwHGw/SoDau+UaeRLB7D6bRv64=</latexit> <latexit sha1_base64="rWtSNavdy1xFzCXdauHA4dKxbZ0=">AAACfnicfVBdSxtBFJ1srdXU2mgf+zIYRFs0blTQvkntQ19EBVOF7BLuTm7WwflYZmalYdk/4q/xVf+B/8bZmIDfF4Y5nHvu10kywa0Lw7ta8GHq4/Snmdn657kv818bC4v/rM4Nww7TQpuzBCwKrrDjuBN4lhkEmQg8TS72q/zpJRrLtTpxwwxjCaniA87AearX2C6iUZOuSZO4CFubYRVrL0B5uBoJnfYSqn6UvUZzwtOXoN0a/WGTjOOot1Dbivqa5RKVYwKs7bbDzMUFGMeZwLIe5RYzYBeQYtdDBRJtXIw2K+myZ/p0oI1/ytER+7iiAGntUCZeKcGd2+e5inw1l8hnk91gNy64ynKHij0MHuSCOk0r72ifG2RODD0AZrjfnbJzMMCcd7ge/UF/m8EDP+cwQwNOm59FBCaVXJX+1jRaq9B7Qvg/EXrkbZ54Sd8Gnc3Wr1b7eLu593vs9wz5TpbIKmmTHbJH/pIj0iGMXJFrckNug1qwEqwHGw/SoDau+UaeRLB7D6bRv64=</latexit>
  34. ࣍਺ͷ# 5SFF 2 7 value value 2 1 value value

    7 4 value value 11 9
  35. ࣍਺ͷ# 5SFF ༿ϊʔυ ಺෦ϊʔυ 2 7 value value 2 1

    value value 7 4 value value 11 9
  36. # 5SFFͷ஋ͷऔಘ read_key(4) 2 7 value value 2 1 value

    value 7 4 value value 11 9
  37. 2 7 value value 2 1 value value 7 4

    value value 11 9 # 5SFFͷ஋ͷऔಘ read_key(4)
  38. 2 7 value value 2 1 value value 7 4

    value value 11 9 # 5SFFͷ஋ͷऔಘ read_key(4)
  39. 2 7 value value 2 1 value value 7 4

    value value 11 9 # 5SFFͷ஋ͷऔಘ read_key(4)
  40. # 5SFFͷൣғεΩϟϯ read_range(from: 2, to: 10) 2 7 value value

    2 1 value value 7 4 value value 11 9
  41. # 5SFFͷൣғεΩϟϯ read_range(from: 2, to: 10) 2 7 value value

    2 1 value value 7 4 value value 11 9
  42. # 5SFFͷൣғεΩϟϯ read_range(from: 2, to: 10) 2 7 value value

    2 1 value value 7 4 value value 11 9
  43. # 5SFFͷൣғεΩϟϯ read_range(from: 2, to: 10) 2 7 value value

    2 1 value value 7 4 value value 11 9
  44. # 5SFFͷൣғεΩϟϯ read_range(from: 2, to: 10) 2 7 value value

    2 1 value value 7 4 value value 11 9
  45. # 5SFFͷൣғεΩϟϯ read_range(from: 2, to: 10) 2 7 value value

    2 1 value value 7 4 value value 11 9
  46. # 5SFFͷൣғεΩϟϯ read_range(from: 2, to: 10) 2 7 value value

    2 1 value value 7 4 value value 11 9
  47. # 5SFFͷൣғεΩϟϯ read_range(from: 2, to: 10) 2 7 value value

    2 1 value value 7 4 value value 11 9
  48. # 5SFFͷൣғεΩϟϯ read_range(from: 2, to: 10) 2 7 value value

    2 1 value value 7 4 value value 11 9
  49. # 5SFF NVMUJLFZ [2,3] value value value value value value

    [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1]
  50. # 5SFF NVMUJLFZ [2,3] value value value value value value

    [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1] ԼҐNόΠτ ্ҐOόΠτ
  51. ΫϥελΠϯσοΫε w Ϩίʔυͷશσʔλ͸ओΩʔͷ༿ϊʔυʹ֨ೲ͞Ε͍ͯΔ ‣ ϨίʔυΛऔಘ͢Δʹ͸ओΩʔΛ୧Δ͜ͱʹͳΔ wηΧϯμϦΠϯσοΫεʹ͸ओΩʔͷ஋͕֨ೲ͞Ε͍ͯΔ ‣ ओΩʔͷ஋Λऔಘͨ͠ΒओΩʔΛ୧ͬͯϨίʔυΛऔಘ wओΩʔ͔ΒϨίʔυΛҾ͘৔߹͸ߴ଎

  52. 2 7 1 2 4 7 9 11 c1: 1

    id: 3 c1: 2 id: 5 c1: 4 id: 6 c1: 7 id: 1 c1: 9 id: 4 c1:11 id: 2 ηΧϯμϦΠϯσοΫεͷ࢖༻ 2 id: 3 
 c1: 1 c2: 8 4 1 2 3 4 5 6 id: 5 
 c1: 2 c2: 3 id: 6 
 c1: 4 c2: 2 id: 1 
 c1: 7 c2: 1 id: 4 
 c1: 9 c2: 3 id: 2 
 c1:11 c2: 1 ηΧϯμϦΩʔ ओΩʔ
  53. 2 7 1 2 4 7 9 11 c1: 1

    id: 3 c1: 2 id: 5 c1: 4 id: 6 c1: 7 id: 1 c1: 9 id: 4 c1:11 id: 2 ηΧϯμϦΠϯσοΫεͷ࢖༻ 2 id: 3 
 c1: 1 c2: 8 4 1 2 3 4 5 6 id: 5 
 c1: 2 c2: 3 id: 6 
 c1: 4 c2: 2 id: 1 
 c1: 7 c2: 1 id: 4 
 c1: 9 c2: 3 id: 2 
 c1:11 c2: 1 get_record(c1: 1)
  54. 2 7 1 2 4 7 9 11 c1: 1

    id: 3 c1: 2 id: 5 c1: 4 id: 6 c1: 7 id: 1 c1: 9 id: 4 c1:11 id: 2 ηΧϯμϦΠϯσοΫεͷ࢖༻ 2 id: 3 
 c1: 1 c2: 8 4 1 2 3 4 5 6 id: 5 
 c1: 2 c2: 3 id: 6 
 c1: 4 c2: 2 id: 1 
 c1: 7 c2: 1 id: 4 
 c1: 9 c2: 3 id: 2 
 c1:11 c2: 1 get_record(c1: 1)
  55. 2 7 1 2 4 7 9 11 c1: 1

    id: 3 c1: 2 id: 5 c1: 4 id: 6 c1: 7 id: 1 c1: 9 id: 4 c1:11 id: 2 ηΧϯμϦΠϯσοΫεͷ࢖༻ 2 id: 3 
 c1: 1 c2: 8 4 1 2 3 4 5 6 id: 5 
 c1: 2 c2: 3 id: 6 
 c1: 4 c2: 2 id: 1 
 c1: 7 c2: 1 id: 4 
 c1: 9 c2: 3 id: 2 
 c1:11 c2: 1 get_record(c1: 1)
  56. 2 7 1 2 4 7 9 11 c1: 1

    id: 3 c1: 2 id: 5 c1: 4 id: 6 c1: 7 id: 1 c1: 9 id: 4 c1:11 id: 2 ηΧϯμϦΠϯσοΫεͷ࢖༻ 2 id: 3 
 c1: 1 c2: 8 4 1 2 3 4 5 6 id: 5 
 c1: 2 c2: 3 id: 6 
 c1: 4 c2: 2 id: 1 
 c1: 7 c2: 1 id: 4 
 c1: 9 c2: 3 id: 2 
 c1:11 c2: 1 get_record(c1: 1)
  57. ΧόϦϯάΠϯσοΫε w ηΧϯμϦΠϯσοΫεʹඞཁͳ৘ใ͕શؚͯ·Ε͍ͯΔ৔߹ ‣ 8)&3& 4&-&$5Ͱࢀর͍ͯ͠Δ৘ใ wओΩʔʹΞΫηε͠ͳ͍ͷͰߴ଎ w&YQMBJOͷ&YUSBʹ6TJOH*OEFY͕ग़Δ

  58. ΧόϦϯάΠϯσοΫε w ηΧϯμϦΠϯσοΫεʹඞཁͳ৘ใ͕શؚͯ·Ε͍ͯΔ৔߹ ‣ 8)&3& 4&-&$5Ͱࢀর͍ͯ͠Δ৘ใ wओΩʔʹΞΫηε͠ͳ͍ͷͰߴ଎ w&YQMBJOͷ&YUSBʹ6TJOH*OEFY͕ग़Δ 2 7

    1 2 4 7 9 11 c1: 1 id: 3 c1: 2 id: 5 c1: 4 id: 6 c1: 7 id: 1 c1: 9 id: 4 c1:11 id: 2 get_record(c1: 1, select: [:id]) => 3
  59. ࠶ܝ1FSGPSNBODF4DIFNB mysql> SET @s = '2018-03-22 12:00:00', @e = '2018-03-24

    00:00:00'; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN SELECT * FROM products -> WHERE started_at <= @s AND ended_at >= @e AND price >= 1000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: products partitions: NULL type: range possible_keys: ix_ended_at_started_at key: ix_ended_at_started_at key_len: 5 ref: NULL rows: 412 filtered: 11.11 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM products -> WHERE started_at <= @s AND ended_at >= @e AND price >= 1000\G (snip) 4UPSBHF&OHJOF͕૸ࠪ͢ΔϨίʔυ਺ ʢ࣮ଌ஋PS༧ଌ஋ʣ
  60. ࠶ܝ1FSGPSNBODF4DIFNB mysql> SELECT -> SQL_TEXT, -> ROWS_SENT, -> ROWS_EXAMINED ->

    FROM -> performance_schema.events_statements_history -> INNER JOIN -> performance_schema.threads -> ON threads.THREAD_ID = events_statements_history.THREAD_ID -> WHERE -> threads.PROCESSLIST_ID = CONNECTION_ID() -> AND EVENT_NAME = 'statement/sql/select' -> ORDER BY -> TIMER_END DESC LIMIT 1 -> \G *************************** 1. row *************************** SQL_TEXT: SELECT * FROM products WHERE started_at <= @s AND ended_at >= @e AND price >= 1000 ROWS_SENT: 53 ROWS_EXAMINED: 83 1 row in set (0.00 sec) &YFDVUPS͕ධՁͨ͠ߦ਺ʢ4UPSBHF&OHJOF͕ฦͨ͠ߦ਺ʣ ΫϥΠΞϯτʹฦͨ͠ߦ਺ʢ৚݅Λຬͨͨ͠ߦ਺ʣ
  61. SPXTͱ͸ w *OOP%#͕ΠϯσοΫεϨίʔυΛ૸ࠪ͢Δ਺ ‣ ϖʔδ਺͕গͳ͍ͱ࣮ଌ஋ ‣ ϖʔδ਺͕ଟ͍ͱ༧ଌ஋ ‣ *$1͸ߟྀ͞Εͳ͍

  62. *OEFY$POEJUJPO1VTIEPXO w 4UPSBHF&OHJOFଆͰ΋8)&3&৚݅ΛධՁ͢Δ ‣ ओΩʔͷ૸ࠪճ਺ΛݮΒ͢͜ͱ͕Ͱ͖Δ ‣ &YQMBJOͷ&YUSBʹ6TJOH*OEFY$POEJUJPO͕ग़Δ

  63. [2,3] [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1] get_record(c1: 1..6,

    c2: 3) *OEFY$POEJUJPO1VTIEPXO c1: 1 c2: 8 id: 3 c1: 2 c2: 3 id: 5 c1: 4 c2: 2 id: 6 c1: 7 c2: 1 id: 4 c1: 9 c2: 3 id: 1 c1:11 c2: 1 id:2
  64. [2,3] [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1] get_record(c1: 1..6,

    c2: 3) *OEFY$POEJUJPO1VTIEPXO c1: 1 c2: 8 id: 3 c1: 2 c2: 3 id: 5 c1: 4 c2: 2 id: 6 c1: 7 c2: 1 id: 4 c1: 9 c2: 3 id: 1 c1:11 c2: 1 id:2 c2 != 3
  65. [2,3] [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1] get_record(c1: 1..6,

    c2: 3) *OEFY$POEJUJPO1VTIEPXO c1: 1 c2: 8 id: 3 c1: 2 c2: 3 id: 5 c1: 4 c2: 2 id: 6 c1: 7 c2: 1 id: 4 c1: 9 c2: 3 id: 1 c1:11 c2: 1 id:2 c2 == 3
  66. [2,3] [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1] get_record(c1: 1..6,

    c2: 3) *OEFY$POEJUJPO1VTIEPXO c1: 1 c2: 8 id: 3 c1: 2 c2: 3 id: 5 c1: 4 c2: 2 id: 6 c1: 7 c2: 1 id: 4 c1: 9 c2: 3 id: 1 c1:11 c2: 1 id: 2 GFUDIUIFSFDPSEGSPNQSJNBSZLFZ
  67. [2,3] [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1] get_record(c1: 1..6,

    c2: 3) *OEFY$POEJUJPO1VTIEPXO c1: 1 c2: 8 id: 3 c1: 2 c2: 3 id: 5 c1: 4 c2: 2 id: 6 c1: 7 c2: 1 id: 4 c1: 9 c2: 3 id: 1 c1:11 c2: 1 id:2 c2 != 3
  68. ͜͜·Ͱͷ·ͱΊ

  69. $MJFOU &YFDVUPS 4UPSBHF&OHJOF
 *OOP%# 0QUJNJ[FS 1BSTFS TUBUJTUJDT SFDPSET 4&-&$5ΫΤϦͷ࣮ߦϑϩʔ ηΧϯμϦΠϯσοΫεͷΠϯσοΫεϨίʔυΛऔಘ

    ΠϯσοΫεʹؚ·Ε͍ͯΔΧϥϜͷ৚݅ΛධՁ ओΩʔ͔ΒϨίʔυΛऔಘ 42-ͷॻ͖׵͑
 ౷ܭ஋͔Β2&1Λ࡞੒ 4UPSBHF&OHJOFʹϨίʔυΛཁٻ ࢒Γͷ৚݅ΛධՁ ΫϥΠΞϯτʹϨίʔυΛૹ৴
  70. $MJFOU &YFDVUPS 4UPSBHF&OHJOF
 *OOP%# 0QUJNJ[FS 1BSTFS TUBUJTUJDT SFDPSET 6TJOHXIFSF ηΧϯμϦΠϯσοΫεͷΠϯσοΫεϨίʔυΛऔಘ

    ΠϯσοΫεʹؚ·Ε͍ͯΔΧϥϜͷ৚݅ΛධՁ ओΩʔ͔ΒϨίʔυΛऔಘ 4UPSBHF&OHJOFʹϨίʔυΛཁٻ ࢒Γͷ৚݅ΛධՁ ΫϥΠΞϯτʹϨίʔυΛૹ৴ 42-ͷॻ͖׵͑
 ౷ܭ஋͔Β2&1Λ࡞੒
  71. $MJFOU &YFDVUPS 4UPSBHF&OHJOF
 *OOP%# 0QUJNJ[FS 1BSTFS TUBUJTUJDT SFDPSET 6TJOHJOEFY ηΧϯμϦΠϯσοΫεͷΠϯσοΫεϨίʔυΛऔಘ

    ΠϯσοΫεʹؚ·Ε͍ͯΔΧϥϜͷ৚݅ΛධՁ ओΩʔ͔ΒϨίʔυΛऔಘ 4UPSBHF&OHJOFʹϨίʔυΛཁٻ ࢒Γͷ৚݅ΛධՁ ΫϥΠΞϯτʹϨίʔυΛૹ৴ 42-ͷॻ͖׵͑
 ౷ܭ஋͔Β2&1Λ࡞੒
  72. $MJFOU &YFDVUPS 4UPSBHF&OHJOF
 *OOP%# 0QUJNJ[FS 1BSTFS TUBUJTUJDT SFDPSET 6TJOHXIFSF6TJOHJOEFY 4UPSBHF&OHJOFʹϨίʔυΛཁٻ

    ࢒Γͷ৚݅ΛධՁ ΫϥΠΞϯτʹϨίʔυΛૹ৴ ηΧϯμϦΠϯσοΫεͷΠϯσοΫεϨίʔυΛऔಘ ΠϯσοΫεʹؚ·Ε͍ͯΔΧϥϜͷ৚݅ΛධՁ ओΩʔ͔ΒϨίʔυΛऔಘ 42-ͷॻ͖׵͑
 ౷ܭ஋͔Β2&1Λ࡞੒
  73. $MJFOU &YFDVUPS 4UPSBHF&OHJOF
 *OOP%# 0QUJNJ[FS 1BSTFS TUBUJTUJDT SFDPSET 6TJOHJOEFYDPOEJUJPO 4UPSBHF&OHJOFʹϨίʔυΛཁٻ

    ࢒Γͷ৚݅ΛධՁ ΫϥΠΞϯτʹϨίʔυΛૹ৴ ηΧϯμϦΠϯσοΫεͷΠϯσοΫεϨίʔυΛऔಘ ΠϯσοΫεʹؚ·Ε͍ͯΔΧϥϜͷ৚݅ΛධՁ ओΩʔ͔ΒϨίʔυΛऔಘ 42-ͷॻ͖׵͑
 ౷ܭ஋͔Β2&1Λ࡞੒
  74. /FTUFE-PPQ+PJO /-+ w .Z42-Ͱ࠾༻͞Ε͍ͯΔ+PJOͷํࣜ w U U Uͱ݁߹͢Δ৔߹ɺߦ਺͚ͩϧʔϓΛճ͢Πϝʔδ for row1

    in t1 { evaluate_join_record(row1) for row2 in t2 { evaluate_join_record(row1, row2) for row3 in t3 { … } } }
  75. ΞδΣϯμ w4&-&$5ΫΤϦͷ࣮ߦϑϩʔ w*OOP%#ͷ֓ཁ wԠ༻ྫ

  76. Ԡ༻ྫ

  77. ΠϯσοΫεͷਫ਼ࠪ CREATE TABLE `products` ( `id` bigint(20) unsigned NOT NULL

    AUTO_INCREMENT, `shop_id` bigint(20) unsigned NOT NULL, `name` varchar(255) NOT NULL, `price` int(10) unsigned NOT NULL, `started_at` datetime NOT NULL, `ended_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `ix_shop` (`shop_id`), KEY `ix_shop_start_end` (`shop_id`,`started_at`,`ended_at`), KEY `ix_shop_end_start` (`shop_id`,`ended_at`,`started_at`), KEY `ix_end_start` (`ended_at`,`started_at`) ) ENGINE=InnoDB;
  78. # 5SFFΛࢥ͍ग़͢ 2 7 1 2 4 7 9 11

    c1: 1 id: 3 c1: 2 id: 5 c1: 4 id: 6 c1: 7 id: 1 c1: 9 id: 4 c1:11 id: 2 [2,3] [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1] c1: 1 c2: 8 id: 3 c1: 2 c2: 3 id: 5 c1: 4 c2: 2 id: 6 c1: 7 c2: 1 id: 4 c1: 9 c2: 3 id: 1 c1:11 c2: 1 id:2 ix_c1 ix_c1_c2
  79. # 5SFFΛࢥ͍ग़͢ [2,3] [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1]

    c1: 1 c2: 8 id: 3 c1: 2 c2: 3 id: 5 c1: 4 c2: 2 id: 6 c1: 7 c2: 1 id: 4 c1: 9 c2: 3 id: 1 c1:11 c2: 1 id:2 2 7 1 2 4 7 9 11 c1: 1 id: 3 c1: 2 id: 5 c1: 4 id: 6 c1: 7 id: 1 c1: 9 id: 4 c1:11 id: 2 ix_c1 ix_c1_c2 JY@D@D͕แؚ͍ͯ͠ΔͷͰෆཁ
  80. CREATE TABLE `products` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

    `shop_id` bigint(20) unsigned NOT NULL, `name` varchar(255) NOT NULL, `price` int(10) unsigned NOT NULL, `started_at` datetime NOT NULL, `ended_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `ix_shop` (`shop_id`), KEY `ix_shop_start_end` (`shop_id`,`started_at`,`ended_at`), KEY `ix_shop_end_start` (`shop_id`,`ended_at`,`started_at`), KEY `ix_end_start` (`ended_at`,`started_at`) ) ENGINE=InnoDB; ΠϯσοΫεͷਫ਼ࠪ
  81. # 5SFFΛࢥ͍ग़͢ [2,3] [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1]

    get_record(c1: 1..10, c2: 3) c1: 1 c2: 8 id: 3 c1: 2 c2: 3 id: 5 c1: 4 c2: 2 id: 6 c1: 7 c2: 1 id: 4 c1: 9 c2: 3 id: 1 c1:11 c2: 1 id:2 ཧ૝
  82. # 5SFFΛࢥ͍ग़͢ [2,3] [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1]

    get_record(c1: 1..10, c2: 3) c1: 1 c2: 8 id: 3 c1: 2 c2: 3 id: 5 c1: 4 c2: 2 id: 6 c1: 7 c2: 1 id: 4 c1: 9 c2: 3 id: 1 c1:11 c2: 1 id:2 ݱ࣮
  83. # 5SFFΛࢥ͍ग़͢ [2,3] [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1]

    get_record(c1: 1..10, c2: 3) c1: 1 c2: 8 id: 3 c1: 2 c2: 3 id: 5 c1: 4 c2: 2 id: 6 c1: 7 c2: 1 id: 4 c1: 9 c2: 3 id: 1 c1:11 c2: 1 id:2 ݱ࣮ ࠷ॳͷΧϥϜ͕ൣғεΩϟϯͩͱ
 ࣍ͷΧϥϜ͸ར༻Ͱ͖ͳ͍
 ˞*$1ͷޮՌ͸ظ଴Ͱ͖Δ
  84. CREATE TABLE `products` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

    `shop_id` bigint(20) unsigned NOT NULL, `name` varchar(255) NOT NULL, `price` int(10) unsigned NOT NULL, `started_at` datetime NOT NULL, `ended_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `ix_shop` (`shop_id`), KEY `ix_shop_start_end` (`shop_id`,`started_at`,`ended_at`), KEY `ix_shop_end_start` (`shop_id`,`ended_at`,`started_at`), KEY `ix_end_start` (`ended_at`,`started_at`) ) ENGINE=InnoDB; ΠϯσοΫεͷਫ਼ࠪ
  85. ςʔϒϧͷಛੑΛߟ͑Δ w ঎඼͸೔ʑ௥Ճ͞Ε͍ͯ͘ w աڈͷ೔͕࣌TUBSUFE@BU΍FOEFE@BUʹೖΔ͜ͱ͸ͳ͍ w 4&-&$5 '30.QSPEVDUT
 8)&3&FOEFE@BU/08 "/%TUBSUFE@BU/08

    
 ͷΑ͏ͳ42-Λଟ༻͢Δ w FOEFE@BU/08 ͸Ϩίʔυ͕૿͑ͯ΋΄΅Ұఆ w TUBSUFE@BU/08 ͸Ϩίʔυ͕૿͑Δ౓ʹ૿͑Δ
  86. ςʔϒϧͷಛੑΛߟ͑Δ w ঎඼͸೔ʑ௥Ճ͞Ε͍ͯ͘ w աڈͷ೔͕࣌TUBSUFE@BU΍FOEFE@BUʹೖΔ͜ͱ͸ͳ͍ w 4&-&$5 '30.QSPEVDUT
 8)&3&FOEFE@BU/08 "/%TUBSUFE@BU/08

    
 ͷΑ͏ͳ42-Λଟ༻͢Δ w FOEFE@BU/08 ͸Ϩίʔυ͕૿͑ͯ΋΄΅Ұఆ w TUBSUFE@BU/08 ͸Ϩίʔυ͕૿͑Δ౓ʹ૿͑Δ TUBSUFE@BUͷΠϯσοΫεෆཁͦ͏
  87. CREATE TABLE `products` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

    `shop_id` bigint(20) unsigned NOT NULL, `name` varchar(255) NOT NULL, `price` int(10) unsigned NOT NULL, `started_at` datetime NOT NULL, `ended_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `ix_shop` (`shop_id`), KEY `ix_shop_start_end` (`shop_id`,`started_at`,`ended_at`), KEY `ix_shop_end_start` (`shop_id`,`ended_at`,`started_at`), KEY `ix_end_start` (`ended_at`,`started_at`) ) ENGINE=InnoDB; ΠϯσοΫεͷਫ਼ࠪ
  88. ΠϯσοΫεͷਫ਼ࠪ CREATE TABLE `products` ( `id` bigint(20) unsigned NOT NULL

    AUTO_INCREMENT, `shop_id` bigint(20) unsigned NOT NULL, `name` varchar(255) NOT NULL, `price` int(10) unsigned NOT NULL, `started_at` datetime NOT NULL, `ended_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `ix_shop_end` (`shop_id`,`ended_at`), KEY `ix_end` (`ended_at`) ) ENGINE=InnoDB;
  89. Ωϟογϡͷਫ਼ࠪ # ஫จཤྺͷ͋ΔϢʔβ໊Λऔಘ user_names = Rails.cache.fetch(“user_names”) do User.joins(:orders).distinct.pluck(:name) end

  90. Ωϟογϡͷਫ਼ࠪ SELECT DISTINCT `users`.`name` FROM `users` INNER JOIN `orders` ON

    `orders`.`user_id` = `users`.`id` # ஫จཤྺͷ͋ΔϢʔβ໊Λऔಘ user_names = Rails.cache.fetch(“user_names”) do User.joins(:orders).distinct.pluck(:name) end
  91. &YQMBJO mysql> EXPLAIN SELECT DISTINCT `users`.`name` FROM `users` -> INNER

    JOIN `orders` ON `orders`.`user_id` = `users`.`id`\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 100 filtered: 100.00 Extra: Using temporary *************************** 2. row ***************************
  92. &YQMBJOʢଓ͖ʣ *************************** 2. row *************************** id: 1 select_type: SIMPLE table:

    orders partitions: NULL type: ref possible_keys: index_orders_on_user_id key: index_orders_on_user_id key_len: 8 ref: railsdm.users.id rows: 98 filtered: 100.00 Extra: Using index; Distinct 2 rows in set, 1 warning (0.00 sec)
  93. &YQMBJOʢଓ͖ʣ *************************** 2. row *************************** id: 1 select_type: SIMPLE table:

    orders partitions: NULL type: ref possible_keys: index_orders_on_user_id key: index_orders_on_user_id key_len: 8 ref: railsdm.users.id rows: 98 filtered: 100.00 Extra: Using index; Distinct 2 rows in set, 1 warning (0.00 sec) SPXTYSPXTͷ૸ࠪͱ ςϯϙϥϦςʔϒϧͷ࡞੒ʂ
  94. 42-ͷվળ # ஫จཤྺͷ͋ΔϢʔβ໊Λऔಘ user_names = User.where( id: Order.select(:user_id) ).pluck(:name)

  95. SELECT `users`.`name` FROM `users` WHERE `users`.`id` IN (SELECT `orders`.`user_id` FROM

    `orders`) 42-ͷվળ # ஫จཤྺͷ͋ΔϢʔβ໊Λऔಘ user_names = User.where( id: Order.select(:user_id) ).pluck(:name)
  96. &YQMBJOʢվળ൛ʣ mysql> EXPLAIN SELECT `users`.`name` FROM `users` WHERE `users`.`id` ->

    IN (SELECT `orders`.`user_id` FROM `orders`)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 100 filtered: 100.00 Extra: NULL *************************** 2. row ***************************
  97. &YQMBJOʢվળ൛ଓ͖ʣ *************************** 2. row *************************** id: 1 select_type: SIMPLE table:

    orders partitions: NULL type: ref possible_keys: index_orders_on_user_id key: index_orders_on_user_id key_len: 8 ref: railsdm.users.id rows: 98 filtered: 100.00 Extra: Using index; FirstMatch(users) 2 rows in set, 1 warning (0.01 sec) 'JSTU.BUDIʹΑΔ࠷దԽʹΑΔ/-+ͷૣظऴྃ
  98. .Z42-Λద੾ʹར༻ͯ͠ շదͳ3BJMTϥΠϑΛʂ

  99. ࢀߟจݙ

  100. w Ԟ໺װ໵  ʰৄղ.Z42-ࢭ·Β͵ਐԽʹ৐Γ஗Εͳ͍ͨΊͷςΫ χΧϧΨΠυʱᠳӭࣾ w #BSPO4DIXBSU[΄͔ஶɺ٠஑ݚࣗ؂༁ɺגࣜձࣾΫΠʔϓ༁ʢʣʰ࣮ ફϋΠύϑΥʔϚϯε.Z42-ୈ൛ʱΦϥΠϦʔδϟύϯ w ZPLV

     ʮࡶͳ.Z42-ύϑΥʔϚϯενϡʔχϯάʯIUUQT XXXTMJEFTIBSFOFUZPLVNZTRM ࢀߟจݙ
  101. ͓·͚

  102. *OOP%#ͷ# 5SFF IUUQTCMPHKDPMFVTCUSFFJOEFYTUSVDUVSFTJOJOOPEC

  103. *OOP%#ͷ# 5SFF ɹ̍ϊʔυ ʹ̍ϖʔδ ʹLFZͷঢॱͰฒΜͰ͍ΔϨίʔυͷू߹ IUUQTCMPHKDPMFVTCUSFFJOEFYTUSVDUVSFTJOJOOPEC