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

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

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

View Slide

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

View Slide

3BJMTͱ.Z42-

View Slide

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

View Slide

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

View Slide

ຊ౰ʹඞཁͰ͔͢ʁ

View Slide

IUUQUFDIMJGFDPPLQBEDPNFOUSZ

View Slide

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

View Slide

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

View Slide

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

View Slide

ΞδΣϯμ
w*OOP%#ͷ֓ཁ
wԠ༻ྫ

View Slide

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

View Slide

4&-&$5ΫΤϦͷ࣮ߦϑϩʔ
$MJFOU
&YFDVUPS
4UPSBHF&OHJOF 
*OOP%#

0QUJNJ[FS
1BSTFS
TUBUJTUJDT SFDPSET

View Slide

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

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

View Slide

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

42-ͷॻ͖׵͑
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: const
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
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

42-ͷॻ͖׵͑
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: const
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select 'Edison Marks' AS `name` from `railsdm`.`users` where 1
ແବͳ৚݅ͷ࡟আ
QSJNBSZLFZ΍VOJRVFLFZͱͷ౳Ձൺֱ͸ల։͞ΕΔ

View Slide

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

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

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

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

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

View Slide

0QUJNJ[FS5SBDF
mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
mysql> SET optimizer_trace_max_mem_size = 1048576;
mysql> SELECT name FROM users WHERE id = 1\G
*************************** 1. row ***************************
name: Edison Marks
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

mysql> SET optimizer_trace="enabled=on";
mysql> SET optimizer_trace_max_mem_size = 1048576;
mysql> SELECT name FROM users WHERE id = 1\G
*************************** 1. row ***************************
name: Edison Marks
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

$MJFOU
&YFDVUPS
4UPSBHF&OHJOF 
*OOP%#

0QUJNJ[FS
1BSTFS
TUBUJTUJDT SFDPSET

View Slide

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

View Slide

1FSGPSNBODF4DIFNB
mysql> SET @s = '2018-03-22 12:00:00', @e = '2018-03-24 00:00:00';
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
mysql> SELECT * FROM products
(snip)
4UPSBHF&OHJOF͕૸ࠪ͢ΔϨίʔυ਺
ʢ࣮ଌ஋PS༧ଌ஋ʣ

View Slide

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
&YFDVUPS͕ධՁͨ͠ߦ਺ʢ4UPSBHF&OHJOF͕ฦͨ͠ߦ਺ʣ
ΫϥΠΞϯτʹฦͨ͠ߦ਺ʢ৚݅Λຬͨͨ͠ߦ਺ʣ

View Slide

$MJFOU
&YFDVUPS
4UPSBHF&OHJOF 
*OOP%#

0QUJNJ[FS
1BSTFS
TUBUJTUJDT SFDPSET

View Slide

4UPSBHF&OHJOF *OOP%#

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

View Slide

ΞδΣϯμ
w*OOP%#ͷ֓ཁ
wԠ༻ྫ

View Slide

*OOP%#ͷ֓ཁ

View Slide

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

View Slide

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

View Slide

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=

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

#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

#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

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

View Slide

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

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

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

View Slide

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

࠶ܝ1FSGPSNBODF4DIFNB
mysql> SET @s = '2018-03-22 12:00:00', @e = '2018-03-24 00:00:00';
mysql> EXPLAIN SELECT * FROM products
*************************** 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
mysql> SELECT * FROM products
(snip)
4UPSBHF&OHJOF͕૸ࠪ͢ΔϨίʔυ਺
ʢ࣮ଌ஋PS༧ଌ஋ʣ

View Slide

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

View Slide

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

View Slide

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

View Slide

[2,3] [7,1]
[1,8] [2,3] [4,2] [7,1] [9,3] [11,1]
get_record(c1: 1..6, 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

[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
c2 != 3

View Slide

[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
c2 == 3

View Slide

[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
GFUDIUIFSFDPSEGSPNQSJNBSZLFZ

View Slide

[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
c2 != 3

View Slide

͜͜·Ͱͷ·ͱΊ

View Slide

$MJFOU
&YFDVUPS
4UPSBHF&OHJOF 
*OOP%#

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

View Slide

$MJFOU
&YFDVUPS
4UPSBHF&OHJOF 
*OOP%#

0QUJNJ[FS
1BSTFS
TUBUJTUJDT SFDPSET
6TJOHXIFSF
࢒Γͷ৚݅ΛධՁ
42-ͷॻ͖׵͑ 
౷ܭ஋͔Β2&1Λ࡞੒

View Slide

$MJFOU
&YFDVUPS
4UPSBHF&OHJOF 
*OOP%#

0QUJNJ[FS
1BSTFS
TUBUJTUJDT SFDPSET
6TJOHJOEFY
࢒Γͷ৚݅ΛධՁ
42-ͷॻ͖׵͑ 
౷ܭ஋͔Β2&1Λ࡞੒

View Slide

$MJFOU
&YFDVUPS
4UPSBHF&OHJOF 
*OOP%#

0QUJNJ[FS
1BSTFS
TUBUJTUJDT SFDPSET
6TJOHXIFSF6TJOHJOEFY
࢒Γͷ৚݅ΛධՁ
42-ͷॻ͖׵͑ 
౷ܭ஋͔Β2&1Λ࡞੒

View Slide

$MJFOU
&YFDVUPS
4UPSBHF&OHJOF 
*OOP%#

0QUJNJ[FS
1BSTFS
TUBUJTUJDT SFDPSET
6TJOHJOEFYDPOEJUJPO
࢒Γͷ৚݅ΛධՁ
42-ͷॻ͖׵͑ 
౷ܭ஋͔Β2&1Λ࡞੒

View Slide

/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

ΞδΣϯμ
w*OOP%#ͷ֓ཁ
wԠ༻ྫ

View Slide

Ԡ༻ྫ

View Slide

ΠϯσοΫεͷਫ਼ࠪ
PRIMARY KEY (`id`),
) ENGINE=InnoDB;

View Slide

#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

#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

PRIMARY KEY (`id`),
) ENGINE=InnoDB;

View Slide

#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

#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

#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

PRIMARY KEY (`id`),
) ENGINE=InnoDB;

View Slide

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

View Slide

ςʔϒϧͷಛੑΛߟ͑Δ
w ঎඼͸೔ʑ௥Ճ͞Ε͍ͯ͘
w աڈͷ೔͕࣌[email protected]΍[email protected]ʹೖΔ͜ͱ͸ͳ͍
w 4&-&$5'30.QSPEVDUT 
8)&3&[email protected]/08
"/%[email protected]/08
 
ͷΑ͏ͳ42-Λଟ༻͢Δ
͸Ϩίʔυ͕૿͑ͯ΋΄΅Ұఆ
͸Ϩίʔυ͕૿͑Δ౓ʹ૿͑Δ
[email protected]ͷΠϯσοΫεෆཁͦ͏

View Slide

PRIMARY KEY (`id`),
) ENGINE=InnoDB;

View Slide

PRIMARY KEY (ìd`),
KEY ìx_shop_end` (`shop_id`,ènded_at`),
KEY ìx_end` (ènded_at`)
) ENGINE=InnoDB;

View Slide

Ωϟογϡͷਫ਼ࠪ
end

View Slide

Ωϟογϡͷਫ਼ࠪ
SELECT DISTINCT ùsers`.`name` FROM ùsers`
INNER JOIN òrders` ON òrders`.ùser_id` = ùsers`.ìd`
end

View Slide

&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
key: NULL
key_len: NULL
ref: NULL
rows: 100
filtered: 100.00
Extra: Using temporary
*************************** 2. row ***************************

View Slide

&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

&YQMBJOʢଓ͖ʣ
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
key_len: 8
rows: 98
filtered: 100.00
Extra: Using index; Distinct
SPXTYSPXTͷ૸ࠪͱ
ςϯϙϥϦςʔϒϧͷ࡞੒ʂ

View Slide

42-ͷվળ
user_names = User.where(
id: Order.select(:user_id)
).pluck(:name)

View Slide

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)

View Slide

&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
key: NULL
key_len: NULL
ref: NULL
rows: 100
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************

View Slide

&YQMBJOʢվળ൛ଓ͖ʣ
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
key_len: 8
rows: 98
filtered: 100.00
Extra: Using index; FirstMatch(users)
'JSTU.BUDIʹΑΔ࠷దԽʹΑΔ/-+ͷૣظऴྃ

View Slide

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

View Slide

ࢀߟจݙ

View Slide

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

View Slide

͓·͚

View Slide

*OOP%#ͷ#5SFF
IUUQTCMPHKDPMFVTCUSFFJOEFYTUSVDUVSFTJOJOOPEC

View Slide

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

View Slide

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

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

Takeshi Arabiki

More Decks by Takeshi Arabiki

Other Decks in Technology

Featured

Transcript