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

MySQL/InnoDB の裏側 / Rails Developers Meetup 2018...

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/

Takeshi Arabiki

March 24, 2018
Tweet

More Decks by Takeshi Arabiki

Other Decks in Technology

Transcript

  1. େྔͷΠϯσοΫε 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;
  2. 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)
  3. 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)
  4. 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ͱͷ౳Ձൺֱ͸ల։͞ΕΔ
  5. w ίετͷ߹ܭ͕࠷খʹͳΔϓϥϯΛબ୒ ‣ ධՁ͢ΔϨίʔυ਺ $16DPTU  ‣ ಡΈࠐΉϖʔδ਺ *0DPTU 

    ‣ ιʔτͰൺֱ͢Δճ਺ $16DPTU  ‣ ςϯϙϥϦςʔϒϧͷSFBEXSJUF *0DPTU ίετϕʔεͷ࠷దϓϥϯͷબ୒
  6. 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)
  7. 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
  8. 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༧ଌ஋ʣ
  9. 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͕ฦͨ͠ߦ਺ʣ ΫϥΠΞϯτʹฦͨ͠ߦ਺ʢ৚݅Λຬͨͨ͠ߦ਺ʣ
  10. 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>
  11. 2 7 value value 2 1 value value 7 4

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

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

    value value 11 9 # 5SFFͷ஋ͷऔಘ read_key(4)
  14. # 5SFF NVMUJLFZ [2,3] value value value value value value

    [7,1] [1,8] [2,3] [4,2] [7,1] [9,3] [11,1]
  15. # 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όΠτ
  16. 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 ηΧϯμϦΩʔ ओΩʔ
  17. 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)
  18. 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)
  19. 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)
  20. 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)
  21. ࠶ܝ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༧ଌ஋ʣ
  22. ࠶ܝ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͕ฦͨ͠ߦ਺ʣ ΫϥΠΞϯτʹฦͨ͠ߦ਺ʢ৚݅Λຬͨͨ͠ߦ਺ʣ
  23. [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
  24. [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
  25. [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
  26. [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
  27. [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
  28. $MJFOU &YFDVUPS 4UPSBHF&OHJOF
 *OOP%# 0QUJNJ[FS 1BSTFS TUBUJTUJDT SFDPSET 4&-&$5ΫΤϦͷ࣮ߦϑϩʔ ηΧϯμϦΠϯσοΫεͷΠϯσοΫεϨίʔυΛऔಘ

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

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

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

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

    ࢒Γͷ৚݅ΛධՁ ΫϥΠΞϯτʹϨίʔυΛૹ৴ ηΧϯμϦΠϯσοΫεͷΠϯσοΫεϨίʔυΛऔಘ ΠϯσοΫεʹؚ·Ε͍ͯΔΧϥϜͷ৚݅ΛධՁ ओΩʔ͔ΒϨίʔυΛऔಘ 42-ͷॻ͖׵͑
 ౷ܭ஋͔Β2&1Λ࡞੒
  33. /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 { … } } }
  34. ΠϯσοΫεͷਫ਼ࠪ 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;
  35. # 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
  36. # 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͕แؚ͍ͯ͠ΔͷͰෆཁ
  37. 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; ΠϯσοΫεͷਫ਼ࠪ
  38. # 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 ཧ૝
  39. # 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 ݱ࣮
  40. # 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ͷޮՌ͸ظ଴Ͱ͖Δ
  41. 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; ΠϯσοΫεͷਫ਼ࠪ
  42. ςʔϒϧͷಛੑΛߟ͑Δ 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 ͸Ϩίʔυ͕૿͑Δ౓ʹ૿͑Δ
  43. ςʔϒϧͷಛੑΛߟ͑Δ 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ͷΠϯσοΫεෆཁͦ͏
  44. 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; ΠϯσοΫεͷਫ਼ࠪ
  45. ΠϯσοΫεͷਫ਼ࠪ 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;
  46. Ωϟογϡͷਫ਼ࠪ 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
  47. &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 ***************************
  48. &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)
  49. &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ͷ૸ࠪͱ ςϯϙϥϦςʔϒϧͷ࡞੒ʂ
  50. 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)
  51. &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 ***************************
  52. &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ʹΑΔ࠷దԽʹΑΔ/-+ͷૣظऴྃ