Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

ࣗݾ঺հ w 5XJUUFS!B@CJDLZ w #MPH͋Βͼ͖೔ه w 3BJMTྺ೥ͪΐͬͱ w ॴଐ3FQSPגࣜձࣾ ‣ ΠϯϑϥΤϯδχΞϲ݄໨

Slide 3

Slide 3 text

3BJMTͱ.Z42-

Slide 4

Slide 4 text

େྔͷΠϯσοΫε 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;

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

ຊ౰ʹඞཁͰ͔͢ʁ

Slide 7

Slide 7 text

IUUQUFDIMJGFDPPLQBEDPNFOUSZ

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

0QUJNJ[FS w 2VFSZ&YFDVUJPO1MBO 2&1 ͷ࡞੒ ‣ 42-ͷॻ͖׵͑ -PHJDBMUSBOTGPSNBUJPOT ‣ ίετϕʔεͷ࠷దϓϥϯͷબ୒ ‣ FUD

Slide 16

Slide 16 text

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)

Slide 17

Slide 17 text

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)

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

w ίετͷ߹ܭ͕࠷খʹͳΔϓϥϯΛબ୒ ‣ ධՁ͢ΔϨίʔυ਺ $16DPTU ‣ ಡΈࠐΉϖʔδ਺ *0DPTU ‣ ιʔτͰൺֱ͢Δճ਺ $16DPTU ‣ ςϯϙϥϦςʔϒϧͷSFBEXSJUF *0DPTU ίετϕʔεͷ࠷దϓϥϯͷબ୒

Slide 20

Slide 20 text

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)

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

*OOP%#ͷ֓ཁ

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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=

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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)

Slide 54

Slide 54 text

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)

Slide 55

Slide 55 text

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)

Slide 56

Slide 56 text

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)

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

ΧόϦϯάΠϯσοΫε 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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

[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

Slide 64

Slide 64 text

[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

Slide 65

Slide 65 text

[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

Slide 66

Slide 66 text

[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

Slide 67

Slide 67 text

[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

Slide 68

Slide 68 text

͜͜·Ͱͷ·ͱΊ

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

/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 { … } } }

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

Ԡ༻ྫ

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

#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

Slide 79

Slide 79 text

#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͕แؚ͍ͯ͠ΔͷͰෆཁ

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

ςʔϒϧͷಛੑΛߟ͑Δ 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 ͸Ϩίʔυ͕૿͑Δ౓ʹ૿͑Δ

Slide 86

Slide 86 text

ςʔϒϧͷಛੑΛߟ͑Δ 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ͷΠϯσοΫεෆཁͦ͏

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

Ωϟογϡͷਫ਼ࠪ 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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

&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)

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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)

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

ࢀߟจݙ

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

͓·͚

Slide 102

Slide 102 text

*OOP%#ͷ#5SFF IUUQTCMPHKDPMFVTCUSFFJOEFYTUSVDUVSFTJOJOOPEC

Slide 103

Slide 103 text

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