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/

Takeshi Arabiki

March 24, 2018
Tweet

More Decks by Takeshi Arabiki

Other Decks in Technology

Transcript

  1. .Z42-*OOP%#ͷཪଆ
    3BJMT%FWFMPQFST.FFUVQ

    3FQSPגࣜձࣾ
    5BLFTIJ"SBCJLJ [email protected]

    View Slide

  2. ࣗݾ঺հ
    w [email protected]
    w #MPH͋Βͼ͖೔ه
    w 3BJMTྺ೥ͪΐͬͱ
    w ॴଐ3FQSPגࣜձࣾ
    ‣ ΠϯϑϥΤϯδχΞϲ݄໨

    View Slide

  3. 3BJMTͱ.Z42-

    View Slide

  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;

    View Slide

  5. ԿͰ΋͔ΜͰ΋Ωϟογϡ
    # ஫จཤྺͷ͋ΔϢʔβ໊Λऔಘ
    user_names = Rails.cache.fetch(“user_names”) do
    User.joins(:orders).distinct.pluck(:name)
    end

    View Slide

  6. ຊ౰ʹඞཁͰ͔͢ʁ

    View Slide

  7. IUUQUFDIMJGFDPPLQBEDPNFOUSZ

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  13. 4&-&$5ΫΤϦͷ࣮ߦϑϩʔ
    $MJFOU
    &YFDVUPS
    4UPSBHF&OHJOF

    *OOP%#

    0QUJNJ[FS
    1BSTFS
    TUBUJTUJDT SFDPSET

    View Slide

  14. 4&-&$5ΫΤϦͷ࣮ߦϑϩʔ
    $MJFOU
    &YFDVUPS
    4UPSBHF&OHJOF

    *OOP%#

    0QUJNJ[FS
    1BSTFS
    TUBUJTUJDT SFDPSET

    View Slide

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

    ‣ ίετϕʔεͷ࠷దϓϥϯͷબ୒
    ‣ FUD

    View Slide

  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)

    View Slide

  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)

    View Slide

  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ͱͷ౳Ձൺֱ͸ల։͞ΕΔ

    View Slide

  19. w ίετͷ߹ܭ͕࠷খʹͳΔϓϥϯΛબ୒
    ‣ ධՁ͢ΔϨίʔυ਺ $16DPTU

    ‣ ಡΈࠐΉϖʔδ਺ *0DPTU

    ‣ ιʔτͰൺֱ͢Δճ਺ $16DPTU

    ‣ ςϯϙϥϦςʔϒϧͷSFBEXSJUF *0DPTU

    ίετϕʔεͷ࠷దϓϥϯͷબ୒

    View Slide

  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)

    View Slide

  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

    View Slide

  22. 4&-&$5ΫΤϦͷ࣮ߦϑϩʔ
    $MJFOU
    &YFDVUPS
    4UPSBHF&OHJOF

    *OOP%#

    0QUJNJ[FS
    1BSTFS
    TUBUJTUJDT SFDPSET

    View Slide

  23. &YFDVUPS
    w 0QUJNJ[FS͕࡞੒ͨ͠2&1Λ࣮ߦ
    w 4UPSBHF&OHJOF͔ΒϨίʔυΛऔಘ
    w ৚݅ʹ߹Θͳ͍ϨίʔυΛϑΟϧλϦϯά
    ‣ &YQMBJOͷ&YUSBʹ6TJOHXIFSF͕ग़Δ
    w (3061#: 03%&3#:FUD
    w ΫϥΠΞϯτ΁ϨίʔυΛૹ৴

    View Slide

  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༧ଌ஋ʣ

    View Slide

  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͕ฦͨ͠ߦ਺ʣ
    ΫϥΠΞϯτʹฦͨ͠ߦ਺ʢ৚݅Λຬͨͨ͠ߦ਺ʣ

    View Slide

  26. 4&-&$5ΫΤϦͷ࣮ߦϑϩʔ
    $MJFOU
    &YFDVUPS
    4UPSBHF&OHJOF

    *OOP%#

    0QUJNJ[FS
    1BSTFS
    TUBUJTUJDT SFDPSET

    View Slide

  27. 4UPSBHF&OHJOF *OOP%#

    w 0QUJNJ[FSʹ౷ܭ஋Λฦ͢
    w &YFDVUPSʹϨίʔυΛฦ͢

    View Slide

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

    View Slide

  29. *OOP%#ͷ֓ཁ

    View Slide

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

    View Slide

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

    View Slide

  32. ࣍਺ͷ#5SFF
    2 7
    value value
    2
    1
    value value
    7
    4
    value value
    11
    9

    View Slide

  33. w ໦ߏ଄ͷҰछ
    w ಺෦ϊʔυ͸࠷େCݸͷΩʔͱ࠷େCݸͷࢠϊʔυΛ࣋ͭ
    ‣ C͸࣍਺
    w ಺෦ϊʔυ͸஋Λ࣋ͨͳ͍
    w ༿ϊʔυͷ֤Ωʔ͸஋Λ࣋ͭ
    w ༿ϊʔυ͸࣍ͷ༿ϊʔυ΁ͷϙΠϯλΛ࣋ͭ
    w ͰݕࡧͰ͖Δ
    #5SFF
    O(logb n)
    AAACfnicfVBdSxtBFJ1srdXU2mgf+zIYRFs0blTQvkntQ19EBVOF7BLuTm7WwflYZmalYdk/4q/xVf+B/8bZmIDfF4Y5nHvu10kywa0Lw7ta8GHq4/Snmdn657kv818bC4v/rM4Nww7TQpuzBCwKrrDjuBN4lhkEmQg8TS72q/zpJRrLtTpxwwxjCaniA87AearX2C6iUZOuSZO4CFubYRVrL0B5uBoJnfYSqn6UvUZzwtOXoN0a/WGTjOOot1Dbivqa5RKVYwKs7bbDzMUFGMeZwLIe5RYzYBeQYtdDBRJtXIw2K+myZ/p0oI1/ytER+7iiAGntUCZeKcGd2+e5inw1l8hnk91gNy64ynKHij0MHuSCOk0r72ifG2RODD0AZrjfnbJzMMCcd7ge/UF/m8EDP+cwQwNOm59FBCaVXJX+1jRaq9B7Qvg/EXrkbZ54Sd8Gnc3Wr1b7eLu593vs9wz5TpbIKmmTHbJH/pIj0iGMXJFrckNug1qwEqwHGw/SoDau+UaeRLB7D6bRv64=
    AAACfnicfVBdSxtBFJ1srdXU2mgf+zIYRFs0blTQvkntQ19EBVOF7BLuTm7WwflYZmalYdk/4q/xVf+B/8bZmIDfF4Y5nHvu10kywa0Lw7ta8GHq4/Snmdn657kv818bC4v/rM4Nww7TQpuzBCwKrrDjuBN4lhkEmQg8TS72q/zpJRrLtTpxwwxjCaniA87AearX2C6iUZOuSZO4CFubYRVrL0B5uBoJnfYSqn6UvUZzwtOXoN0a/WGTjOOot1Dbivqa5RKVYwKs7bbDzMUFGMeZwLIe5RYzYBeQYtdDBRJtXIw2K+myZ/p0oI1/ytER+7iiAGntUCZeKcGd2+e5inw1l8hnk91gNy64ynKHij0MHuSCOk0r72ifG2RODD0AZrjfnbJzMMCcd7ge/UF/m8EDP+cwQwNOm59FBCaVXJX+1jRaq9B7Qvg/EXrkbZ54Sd8Gnc3Wr1b7eLu593vs9wz5TpbIKmmTHbJH/pIj0iGMXJFrckNug1qwEqwHGw/SoDau+UaeRLB7D6bRv64=
    AAACfnicfVBdSxtBFJ1srdXU2mgf+zIYRFs0blTQvkntQ19EBVOF7BLuTm7WwflYZmalYdk/4q/xVf+B/8bZmIDfF4Y5nHvu10kywa0Lw7ta8GHq4/Snmdn657kv818bC4v/rM4Nww7TQpuzBCwKrrDjuBN4lhkEmQg8TS72q/zpJRrLtTpxwwxjCaniA87AearX2C6iUZOuSZO4CFubYRVrL0B5uBoJnfYSqn6UvUZzwtOXoN0a/WGTjOOot1Dbivqa5RKVYwKs7bbDzMUFGMeZwLIe5RYzYBeQYtdDBRJtXIw2K+myZ/p0oI1/ytER+7iiAGntUCZeKcGd2+e5inw1l8hnk91gNy64ynKHij0MHuSCOk0r72ifG2RODD0AZrjfnbJzMMCcd7ge/UF/m8EDP+cwQwNOm59FBCaVXJX+1jRaq9B7Qvg/EXrkbZ54Sd8Gnc3Wr1b7eLu593vs9wz5TpbIKmmTHbJH/pIj0iGMXJFrckNug1qwEqwHGw/SoDau+UaeRLB7D6bRv64=
    AAACfnicfVBdSxtBFJ1srdXU2mgf+zIYRFs0blTQvkntQ19EBVOF7BLuTm7WwflYZmalYdk/4q/xVf+B/8bZmIDfF4Y5nHvu10kywa0Lw7ta8GHq4/Snmdn657kv818bC4v/rM4Nww7TQpuzBCwKrrDjuBN4lhkEmQg8TS72q/zpJRrLtTpxwwxjCaniA87AearX2C6iUZOuSZO4CFubYRVrL0B5uBoJnfYSqn6UvUZzwtOXoN0a/WGTjOOot1Dbivqa5RKVYwKs7bbDzMUFGMeZwLIe5RYzYBeQYtdDBRJtXIw2K+myZ/p0oI1/ytER+7iiAGntUCZeKcGd2+e5inw1l8hnk91gNy64ynKHij0MHuSCOk0r72ifG2RODD0AZrjfnbJzMMCcd7ge/UF/m8EDP+cwQwNOm59FBCaVXJX+1jRaq9B7Qvg/EXrkbZ54Sd8Gnc3Wr1b7eLu593vs9wz5TpbIKmmTHbJH/pIj0iGMXJFrckNug1qwEqwHGw/SoDau+UaeRLB7D6bRv64=

    View Slide

  34. ࣍਺ͷ#5SFF
    2 7
    value value
    2
    1
    value value
    7
    4
    value value
    11
    9

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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]

    View Slide

  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όΠτ

    View Slide

  51. ΫϥελΠϯσοΫε
    w Ϩίʔυͷશσʔλ͸ओΩʔͷ༿ϊʔυʹ֨ೲ͞Ε͍ͯΔ
    ‣ ϨίʔυΛऔಘ͢Δʹ͸ओΩʔΛ୧Δ͜ͱʹͳΔ
    wηΧϯμϦΠϯσοΫεʹ͸ओΩʔͷ஋͕֨ೲ͞Ε͍ͯΔ
    ‣ ओΩʔͷ஋Λऔಘͨ͠ΒओΩʔΛ୧ͬͯϨίʔυΛऔಘ
    wओΩʔ͔ΒϨίʔυΛҾ͘৔߹͸ߴ଎

    View Slide

  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
    ηΧϯμϦΩʔ
    ओΩʔ

    View Slide

  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)

    View Slide

  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)

    View Slide

  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)

    View Slide

  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)

    View Slide

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

    View Slide

  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

    View Slide

  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༧ଌ஋ʣ

    View Slide

  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͕ฦͨ͠ߦ਺ʣ
    ΫϥΠΞϯτʹฦͨ͠ߦ਺ʢ৚݅Λຬͨͨ͠ߦ਺ʣ

    View Slide

  61. SPXTͱ͸
    w *OOP%#͕ΠϯσοΫεϨίʔυΛ૸ࠪ͢Δ਺
    ‣ ϖʔδ਺͕গͳ͍ͱ࣮ଌ஋
    ‣ ϖʔδ਺͕ଟ͍ͱ༧ଌ஋
    ‣ *$1͸ߟྀ͞Εͳ͍

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  68. ͜͜·Ͱͷ·ͱΊ

    View Slide

  69. $MJFOU
    &YFDVUPS
    4UPSBHF&OHJOF

    *OOP%#

    0QUJNJ[FS
    1BSTFS
    TUBUJTUJDT SFDPSET
    4&-&$5ΫΤϦͷ࣮ߦϑϩʔ
    ηΧϯμϦΠϯσοΫεͷΠϯσοΫεϨίʔυΛऔಘ
    ΠϯσοΫεʹؚ·Ε͍ͯΔΧϥϜͷ৚݅ΛධՁ
    ओΩʔ͔ΒϨίʔυΛऔಘ
    42-ͷॻ͖׵͑

    ౷ܭ஋͔Β2&1Λ࡞੒
    4UPSBHF&OHJOFʹϨίʔυΛཁٻ
    ࢒Γͷ৚݅ΛධՁ
    ΫϥΠΞϯτʹϨίʔυΛૹ৴

    View Slide

  70. $MJFOU
    &YFDVUPS
    4UPSBHF&OHJOF

    *OOP%#

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

    ౷ܭ஋͔Β2&1Λ࡞੒

    View Slide

  71. $MJFOU
    &YFDVUPS
    4UPSBHF&OHJOF

    *OOP%#

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

    ౷ܭ஋͔Β2&1Λ࡞੒

    View Slide

  72. $MJFOU
    &YFDVUPS
    4UPSBHF&OHJOF

    *OOP%#

    0QUJNJ[FS
    1BSTFS
    TUBUJTUJDT SFDPSET
    6TJOHXIFSF6TJOHJOEFY
    4UPSBHF&OHJOFʹϨίʔυΛཁٻ
    ࢒Γͷ৚݅ΛධՁ
    ΫϥΠΞϯτʹϨίʔυΛૹ৴
    ηΧϯμϦΠϯσοΫεͷΠϯσοΫεϨίʔυΛऔಘ
    ΠϯσοΫεʹؚ·Ε͍ͯΔΧϥϜͷ৚݅ΛධՁ
    ओΩʔ͔ΒϨίʔυΛऔಘ
    42-ͷॻ͖׵͑

    ౷ܭ஋͔Β2&1Λ࡞੒

    View Slide

  73. $MJFOU
    &YFDVUPS
    4UPSBHF&OHJOF

    *OOP%#

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

    ౷ܭ஋͔Β2&1Λ࡞੒

    View Slide

  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 {

    }
    }
    }

    View Slide

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

    View Slide

  76. Ԡ༻ྫ

    View Slide

  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;

    View Slide

  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

    View Slide

  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
    [email protected]@D͕แؚ͍ͯ͠ΔͷͰෆཁ

    View Slide

  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;
    ΠϯσοΫεͷਫ਼ࠪ

    View Slide

  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
    ཧ૝

    View Slide

  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
    ݱ࣮

    View Slide

  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ͷޮՌ͸ظ଴Ͱ͖Δ

    View Slide

  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;
    ΠϯσοΫεͷਫ਼ࠪ

    View Slide

  85. ςʔϒϧͷಛੑΛߟ͑Δ
    w ঎඼͸೔ʑ௥Ճ͞Ε͍ͯ͘
    w աڈͷ೔͕࣌[email protected]΍[email protected]ʹೖΔ͜ͱ͸ͳ͍
    w 4&-&$5'30.QSPEVDUT

    8)&3&[email protected]/08
    "/%[email protected]/08

    ͷΑ͏ͳ42-Λଟ༻͢Δ
    w [email protected]/08
    ͸Ϩίʔυ͕૿͑ͯ΋΄΅Ұఆ
    w [email protected]/08
    ͸Ϩίʔυ͕૿͑Δ౓ʹ૿͑Δ

    View Slide

  86. ςʔϒϧͷಛੑΛߟ͑Δ
    w ঎඼͸೔ʑ௥Ճ͞Ε͍ͯ͘
    w աڈͷ೔͕࣌[email protected]΍[email protected]ʹೖΔ͜ͱ͸ͳ͍
    w 4&-&$5'30.QSPEVDUT

    8)&3&[email protected]/08
    "/%[email protected]/08

    ͷΑ͏ͳ42-Λଟ༻͢Δ
    w [email protected]/08
    ͸Ϩίʔυ͕૿͑ͯ΋΄΅Ұఆ
    w [email protected]/08
    ͸Ϩίʔυ͕૿͑Δ౓ʹ૿͑Δ
    [email protected]ͷΠϯσοΫεෆཁͦ͏

    View Slide

  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;
    ΠϯσοΫεͷਫ਼ࠪ

    View Slide

  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;

    View Slide

  89. Ωϟογϡͷਫ਼ࠪ
    # ஫จཤྺͷ͋ΔϢʔβ໊Λऔಘ
    user_names = Rails.cache.fetch(“user_names”) do
    User.joins(:orders).distinct.pluck(:name)
    end

    View Slide

  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

    View Slide

  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 ***************************

    View Slide

  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)

    View Slide

  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ͷ૸ࠪͱ
    ςϯϙϥϦςʔϒϧͷ࡞੒ʂ

    View Slide

  94. 42-ͷվળ
    # ஫จཤྺͷ͋ΔϢʔβ໊Λऔಘ
    user_names = User.where(
    id: Order.select(:user_id)
    ).pluck(:name)

    View Slide

  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)

    View Slide

  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 ***************************

    View Slide

  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ʹΑΔ࠷దԽʹΑΔ/-+ͷૣظऴྃ

    View Slide

  98. .Z42-Λద੾ʹར༻ͯ͠
    շదͳ3BJMTϥΠϑΛʂ

    View Slide

  99. ࢀߟจݙ

    View Slide

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

    View Slide

  101. ͓·͚

    View Slide

  102. *OOP%#ͷ#5SFF
    IUUQTCMPHKDPMFVTCUSFFJOEFYTUSVDUVSFTJOJOOPEC

    View Slide

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

    View Slide