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

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

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

3BJMTͱ.Z42-

େྔͷΠϯσοΫε CREATE TABLE `products` ( ìd` 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, ènded_at` datetime NOT NULL, PRIMARY KEY (ìd`), KEY ìx_shop` (`shop_id`), KEY ìx_shop_start_end` (`shop_id`,`started_at`,ènded_at`), KEY ìx_shop_end_start` (`shop_id`,ènded_at`,`started_at`), KEY ìx_end_start` (ènded_at`,`started_at`) ) ENGINE=InnoDB;

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

ຊ౰ʹඞཁͰ͔͢ʁ

IUUQUFDIMJGFDPPLQBEDPNFOUSZ

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

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

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

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

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

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

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)

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)

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

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

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)

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

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

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

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

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

*OOP%#ͷ֓ཁ

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

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

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>

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

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

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

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

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

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

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

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

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

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)

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

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

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

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

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

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

[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

[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

[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

[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

[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

͜͜·Ͱͷ·ͱΊ

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

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

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

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

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

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

Ԡ༻ྫ

ΠϯσοΫεͷਫ਼ࠪ CREATE TABLE `products` ( ìd` 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, ènded_at` datetime NOT NULL, PRIMARY KEY (ìd`), KEY ìx_shop` (`shop_id`), KEY ìx_shop_start_end` (`shop_id`,`started_at`,ènded_at`), KEY ìx_shop_end_start` (`shop_id`,ènded_at`,`started_at`), KEY ìx_end_start` (ènded_at`,`started_at`) ) ENGINE=InnoDB;

# 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

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

CREATE TABLE `products` ( ìd` 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, ènded_at` datetime NOT NULL, PRIMARY KEY (ìd`), KEY ìx_shop` (`shop_id`), KEY ìx_shop_start_end` (`shop_id`,`started_at`,ènded_at`), KEY ìx_shop_end_start` (`shop_id`,ènded_at`,`started_at`), KEY ìx_end_start` (ènded_at`,`started_at`) ) ENGINE=InnoDB; ΠϯσοΫεͷਫ਼ࠪ

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

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

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

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

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

ΠϯσοΫεͷਫ਼ࠪ CREATE TABLE `products` ( ìd` 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, ènded_at` datetime NOT NULL, PRIMARY KEY (ìd`), KEY ìx_shop_end` (`shop_id`,ènded_at`), KEY ìx_end` (ènded_at`) ) ENGINE=InnoDB;

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

Ωϟογϡͷਫ਼ࠪ SELECT DISTINCT ùsers`.`name` FROM ùsers` INNER JOIN òrders` ON
òrders`.ùser_id` = ùsers`.ìd` # ஫จཤྺͷ͋ΔϢʔβ໊Λऔಘ user_names = Rails.cache.fetch(“user_names”) do User.joins(:orders).distinct.pluck(:name) end

&YQMBJO mysql> EXPLAIN SELECT DISTINCT ùsers`.`name` FROM ùsers` -> INNER
JOIN òrders` ON òrders`.ùser_id` = ùsers`.ìd`\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 ***************************

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

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

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

SELECT ùsers`.`name` FROM ùsers` WHERE ùsers`.ìd` IN (SELECT òrders`.ùser_id` FROM
òrders`) 42-ͷվળ # ஫จཤྺͷ͋ΔϢʔβ໊Λऔಘ user_names = User.where( id: Order.select(:user_id) ).pluck(:name)

&YQMBJOʢվળ൛ʣ mysql> EXPLAIN SELECT ùsers`.`name` FROM ùsers` WHERE ùsers`.ìd` ->
IN (SELECT òrders`.ùser_id` FROM òrders`)\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 ***************************

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

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

ࢀߟจݙ

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

͓·͚

*OOP%#ͷ# 5SFF IUUQTCMPHKDPMFVTCUSFFJOEFYTUSVDUVSFTJOJOOPEC

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

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

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

More Decks by Takeshi Arabiki

Other Decks in Technology

Featured

Transcript