Tweet
Tweet

Slide 1

Slide 1 text

WebΤϯδχΞͷͨΊͷ
 MySQLೖ໳ 2017-07-06 αϙʔλʔζ CoLab @mihyaeru21

Slide 2

Slide 2 text

ࣗݾ঺հ • ϛώϟΤϧ @mihyaeru21 • ৬ྺ • 2014-04 גࣜձࣾϞόΠϧϑΝΫτϦʔ • αϙʔλʔζܦ༝ • Ґஔ৘ใήʔϜ x 3 • 2016-10 freeeגࣜձࣾ • ձܭαʔϏε • ػೳ։ൃͱ͔ύϑΥʔϚϯενϡʔχϯάͱ͔ • ओʹαʔόʔαΠυͰΫϥΠΞϯτ΋΍Δ

Slide 3

Slide 3 text

ͳͥ஌Δඞཁ͕͋Δͷ͔ • Web։ൃͰ͸ORMΛ࢖͏͜ͱ͕ଟ͍ʢͨͿΜʣ • ORM͸͍Ζ͍Ζͱศར • ൓໘... • ͲΜͳΫΤϦʹͳΔͷ͔Θ͔Γʹ͍͘৔߹͋Γ • ࣮ଶ͕DBͰɺSQLΛ࣮ߦ͍ͯ͠Δҙ͕ࣝബΕ͕ͪ • ࣮ଶΛҙࣝ͠ͳ͍ͱ... • ਂࠁͳύϑΥʔϚϯε໰୊ͷՄೳੑ • DBಛ༗ͷڍಈͰϋϚΔ

Slide 4

Slide 4 text

ͱ͍͏Θ͚ͰMySQLʹ͍ͭͯ

Slide 5

Slide 5 text

ର৅ऀ • Web։ൃΛ΍ͬͯΔ • ΠϯσοΫε͸஌ͬͯΔ͠࢖͑Δ • ͱ͸͍͑งғؾͰDB·ΘΓΛѻ͍ͬͯΔ • (࣮ફϋΠύϑΥʔϚϯεMySQLͱ͔ΛͪΌΜͱಡΜͩ ͜ͱ͕͋ΔͳΒฉ͘ඞཁ͕·ͬͨ͘ͳ͍Ͱ͢)

Slide 6

Slide 6 text

ͬ͘͟Γͨ͠໨࣍ • MySQLͷΞʔΩςΫνϟ • ςʔϒϧ΍ΠϯσοΫεͷσʔλߏ଄ • ύϑΥʔϚϯεʹؔ܎͢Δ࿩ • ڍಈʹؔ܎͢Δ࿩

Slide 7

Slide 7 text

MySQLͷΞʔΩςΫνϟ αʔόʔ ετϨʔδΤϯδϯ ετϨʔδΤϯδϯAPI ΫϥΠΞϯτͱͷ઀ଓ ※͜ͷࢿྉʹ͓͍ͯαʔόʔͱಡΜͰ͍Δ෦෼͸MySQLͷαʔόʔͷ͏ͪɺετϨʔδΤϯδϯͰͳ͍෦෼ͷ૯শͰ͢ɻ ͏·͍૯শ͕ͳ͍ͷͰɺ࣮ફϋΠύϑΥʔϚϯεMySQLʹशͬͯαʔόʔͱݺͿ͜ͱʹ͠·͢ɻ

Slide 8

Slide 8 text

αʔόʔ αʔόʔ ετϨʔδΤϯδϯ • ӬଓԽʹؔΘΔ෦෼Ҏ֎ • ΫΤϦͷղੳ • ࠷దԽ • ࣮ߦ • ΫΤϦΩϟογϡ • ઀ଓॲཧ • etc...

Slide 9

Slide 9 text

αʔόʔ ετϨʔδΤϯδϯ ετϨʔδΤϯδϯ • σʔλͷӬଓԽΛ࢘Δ • ΠϯσοΫε • τϥϯβΫγϣϯ • ετϨʔδΤϯδϯAPI • ετϨʔδΤϯδϯ͸໨తʹԠͯ͡
 બͿ͜ͱ͕Ͱ͖Δ

Slide 10

Slide 10 text

༷ʑͳετϨʔδΤϯδϯ • InnoDB • MyISAM • MEMORY • CSV • BLACKHOLE • MyRocks • etc... (see https://dev.mysql.com/doc/refman/5.6/ja/storage-engines.html )

Slide 11

Slide 11 text

InnoDB • MySQL 5.5Ҏ߱ͷσϑΥϧτ • τϥϯβΫγϣϯ͕࢖͑Δ • ֎෦Ωʔ੍໿͕࢖͑Δ • ଞͷετϨʔδΤϯδϯΛ࢖͏ඞવੑΛઆ໌Ͱ͖ͳ͍ ͳΒ͜ΕΛ࢖͑͹OK • ͜ͷޙͷ࿩͸InnoDBʹ͍ͭͯͷ࿩

Slide 12

Slide 12 text

MyISAM • MySQL 5.5ΑΓલͷσϑΥϧτ • ۭؒΠϯσοΫε͕࢖͑Δ • (InnoDBͰ΋5.7.5Ҏ߱ͳΒ࢖͑Δ) • τϥϯβΫγϣϯ͕࢖͑ͳ͍ • ςʔϒϧ୯ҐͰ͔͠ϩοΫͰ͖ͳ͍

Slide 13

Slide 13 text

ͦͷଞ • MEMORY • ϝϞϦ಺ͷΈɻফ͑ͯ΋໰୊ͳ͍σʔλ͚ͩ • CSV • CSVϑΝΠϧʹอଘ͞ΕΔ • BLACKHOLE • Կ΋อଘ͠ͳ͍ʢʁʂʣ • MyRocks • Facebook͕InnoDBͰਏ͘ͳͬͯ࡞ͬͨ΍ͭ • লσΟεΫεϖʔεɺߴॻ͖ࠐΈੑೳ

Slide 14

Slide 14 text

※Ҏ߱͸InnoDBʹ͍ͭͯͷ࿩Ͱ͢

Slide 15

Slide 15 text

ςʔϒϧͷσʔλߏ଄ • ࿦ཧతͳσʔλߏ଄͸୯७ͳϋογϡͷ഑ྻͰ͸ͳ͍ < \JE OBNF෨^ \JE OBNFߴ໺^ \JE OBNFాத^ >

Slide 16

Slide 16 text

ςʔϒϧͷσʔλߏ଄ • ࿦ཧతͳσʔλߏ଄͸୯७ͳϋογϡͷ഑ྻͰ͸ͳ͍ < \JE OBNF෨^ \JE OBNFߴ໺^ \JE OBNFాத^ >

Slide 17

Slide 17 text

ٸ͕͹ճΕ • ઌʹΠϯσοΫεͷσʔλߏ଄ • InnoDBͷσϑΥϧτͰ͸B+໦ͱ͍͏σʔλߏ଄ ※Ҏ߱ɺΠϯσοΫε͸B+໦લఏͷ࿩Ͱ͢

Slide 18

Slide 18 text

B໦ • ͞ΒʹճΓಓ • B+໦ͷલʹB໦ʢ໊લ͔Β࡯͍ͯͩ͘͠͞ʣ • ໦ߏ଄ • ฏߧ໦

Slide 19

Slide 19 text

B໦ 8 13 2 2 3 3 6 6 8 8 9 9 15 15 10 10 20 20 28 28

Slide 20

Slide 20 text

B໦ 8 13 2 2 3 3 6 6 8 8 9 9 15 15 10 10 20 20 28 28 ϊʔυ ※Ϧʔϑϊʔυͷදݱํ๏͸͍͔ͭ͋͘Γ·͢ ※͜ͷਤͰ͸Լଆͷ3͕ͭϦʔϑϊʔυʹͳΓ·͢

Slide 21

Slide 21 text

B໦ 8 13 2 2 3 3 6 6 8 8 9 9 15 15 10 10 20 20 28 28 Ωʔ

Slide 22

Slide 22 text

B໦ 8 13 2 2 3 3 6 6 8 8 9 9 15 15 10 10 20 20 28 28 ϊʔυΛࢦ͢ϙΠϯλ

Slide 23

Slide 23 text

B໦ 8 13 2 2 3 3 6 6 8 8 9 9 15 15 10 10 20 20 28 28 ֨ೲ͞ΕΔ஋

Slide 24

Slide 24 text

B໦: ݕࡧ 8 13 2 2 3 3 6 6 8 8 9 9 15 15 10 10 20 20 28 28 ݕࡧ஋x͕9

Slide 25

Slide 25 text

B໦: ݕࡧ 8 13 2 2 3 3 6 6 8 8 9 9 15 15 10 10 20 20 28 28 8 ≦ x < 13 ݕࡧ஋x͕9

Slide 26

Slide 26 text

B໦: ݕࡧ 8 13 2 2 3 3 6 6 8 8 9 9 15 15 10 10 20 20 28 28 ݕࡧ஋x͕9 8 ≦ x < 13 x = 9

Slide 27

Slide 27 text

B໦: ݕࡧ 8 13 2 2 3 3 6 6 8 8 9 9 15 15 10 10 20 20 28 28 ݕࡧ஋x͕9 8 ≦ x < 13 x = 9

Slide 28

Slide 28 text

B+໦ 8 13 2 2 3 3 6 6 8 8 9 9 10 10 15 15 20 20 28 28

Slide 29

Slide 29 text

B+໦ 8 13 2 2 3 3 6 6 8 8 9 9 10 10 15 15 20 20 28 28 Ϧʔϑϊʔυͷ຤ඌ͕࣍ͷϦʔϑϊʔυΛࢦ͢

Slide 30

Slide 30 text

B+໦ 8 13 2 2 3 3 6 6 8 8 9 9 10 10 15 15 20 20 28 28 ιʔτࡁΈͷ஋Λૉૣ͘ಡΈऔΕΔ

Slide 31

Slide 31 text

ΠϯσοΫε • B+໦ͷߏ଄Ͱσʔλ͕֨ೲ͞ΕΔ

Slide 32

Slide 32 text

ΠϯσοΫε 8 13 2 3 6 8 9 10 15 20 28

Slide 33

Slide 33 text

ΠϯσοΫε ʁʁʁ 8 13 2 3 6 8 9 10 15 20 28

Slide 34

Slide 34 text

ΠϯσοΫε ओΩʔ͕ೖΔ 8 13 2 4 3 8 6 7 8 1 9 6 10 2 15 5 20 3 28 9

Slide 35

Slide 35 text

ηΧϯμϦΠϯσοΫε 8 13 2 4 3 8 6 7 8 1 9 6 10 2 15 5 20 3 28 9 • ओΩʔҎ֎ͷΠϯσοΫε͸ࠓઆ໌ͨ͠ߏ଄ • ηΧϯμϦΠϯσοΫεͱݺͿ

Slide 36

Slide 36 text

ςʔϒϧͷσʔλߏ଄ • ΍ͬͱؼ͖ͬͯ·ͨ͠

Slide 37

Slide 37 text

ςʔϒϧͷσʔλߏ଄ 4 7 1 2 ্ా 4/29 10 3 ࡾӜ 7/5 8 ໊લ ஀ੜ೔ ฒͼॱ ্໺ 1/8 20 4 5 தౡ 1/16 15 6 தࢁ 12/27 2 த઒ 2/2 9 7 8 த໺ 3/13 3 9 தଜ 11/25 6 Ճ౻ 9/23 28

Slide 38

Slide 38 text

ςʔϒϧͷσʔλߏ଄ 4 7 1 2 ্ా 4/29 10 3 ࡾӜ 7/5 8 ໊લ ஀ੜ೔ ฒͼॱ ্໺ 1/8 20 4 5 தౡ 1/16 15 6 தࢁ 12/27 2 த઒ 2/2 9 7 8 த໺ 3/13 3 9 தଜ 11/25 6 Ճ౻ 9/23 28 B+໦ͷߏ଄ͦͷ·· Ϧʔϑϊʔυʹ࣮σʔλ͕ೖ͍ͬͯΔ

Slide 39

Slide 39 text

ςʔϒϧͷσʔλߏ଄ 4 7 1 2 ্ా 4/29 10 3 ࡾӜ 7/5 8 ໊લ ஀ੜ೔ ฒͼॱ ্໺ 1/8 20 4 5 தౡ 1/16 15 6 தࢁ 12/27 2 த઒ 2/2 9 7 8 த໺ 3/13 3 9 தଜ 11/25 6 Ճ౻ 9/23 28 • ओΩʔͰΫϥελԽ͞Ε͍ͯΔ • ΫϥελԽΠϯσοΫε

Slide 40

Slide 40 text

ηΧϯμϦΠϯσοΫεΛ࢖ͬͨݕࡧ 8 13 2 4 3 8 6 7 8 1 9 6 10 2 15 5 20 3 28 9

Slide 41

Slide 41 text

ηΧϯμϦΠϯσοΫεΛ࢖ͬͨݕࡧ 8 13 2 4 3 8 6 7 8 1 9 6 10 2 15 5 20 3 28 9 ฒͼॱ = 10

Slide 42

Slide 42 text

ηΧϯμϦΠϯσοΫεΛ࢖ͬͨݕࡧ 8 13 2 4 3 8 6 7 8 1 9 6 10 2 15 5 20 3 28 9 ฒͼॱ = 10 ओΩʔ = 2

Slide 43

Slide 43 text

ηΧϯμϦΠϯσοΫεΛ࢖ͬͨݕࡧ 8 13 2 4 3 8 6 7 8 1 9 6 10 2 15 5 20 3 28 9 ฒͼॱ = 10 4 7 1 2 ্ా 4/29 10 3 ࡾӜ 7/5 8 ໊લ ஀ੜ೔ ฒͼॱ ্໺ 1/8 20 4 5 தౡ 1/16 15 6 தࢁ 12/27 2 த઒ 2/2 9 7 8 த໺ 3/13 3 9 தଜ 11/25 6 Ճ౻ 9/23 28

Slide 44

Slide 44 text

ηΧϯμϦΠϯσοΫεΛ࢖ͬͨݕࡧ 8 13 2 4 3 8 6 7 8 1 9 6 10 2 15 5 20 3 28 9 ฒͼॱ = 10 4 7 1 2 ্ా 4/29 10 3 ࡾӜ 7/5 8 ໊લ ஀ੜ೔ ฒͼॱ ্໺ 1/8 20 4 5 தౡ 1/16 15 6 தࢁ 12/27 2 த઒ 2/2 9 7 8 த໺ 3/13 3 9 தଜ 11/25 6 Ճ౻ 9/23 28

Slide 45

Slide 45 text

Ҏ্͕جຊతͳߏ଄ͷ࿩

Slide 46

Slide 46 text

ͬ͘͟Γͨ͠໨࣍ • MySQLͷΞʔΩςΫνϟ • ςʔϒϧ΍ΠϯσοΫεͷσʔλߏ଄ • ύϑΥʔϚϯεʹؔ܎͢Δ࿩ • ڍಈʹؔ܎͢Δ࿩

Slide 47

Slide 47 text

ύϑΥʔϚϯεʹؔ܎͢Δ࿩ • ΠϯσοΫε͕࢖͑ͳ͍ͱͲ͏ͳΔͷ͔ • ΠϯσοΫε͸ࠨ͔Βӈ • ෳ߹ΠϯσοΫε • NOTͷѻ͍ • NULLͷѻ͍ • ιʔτ • ΧόϦϯάΠϯσοΫε

Slide 48

Slide 48 text

ςʔϒϧ • id • ໊લ • ஀ੜ೔ • ݂ӷܕ • ฒͼॱʢ͖ͯͱʔͳ਺ࣈʣ • ి࿩൪߸ʢNULLͷ৔߹͋Γʣ • 100ສߦ

Slide 49

Slide 49 text

ΠϯσοΫε͕࢖͑ͳ͍ͱͲ͏ͳΔͷ͔ • ετϨʔδΤϯδϯ͕100ສ݅શ݅Λ
 ϝϞϦ಺ʢόοϑΝϓʔϧʣ΋͘͠͸ ετϨʔδ͔ΒಡΈऔΔʢʂʂʂʣ αʔόʔ ετϨʔδΤϯδϯ όοϑΝϓʔϧ ετϨʔδ ສ݅ ਏ͍

Slide 50

Slide 50 text

ΠϯσοΫε͕࢖͑ͳ͍ͱͲ͏ͳΔͷ͔ • ετϨʔδΤϯδϯ͔Βαʔόʔ΁ αʔόʔ ετϨʔδΤϯδϯ όοϑΝϓʔϧ ετϨʔδ ສ݅ ਏ͍

Slide 51

Slide 51 text

ΠϯσοΫε͕࢖͑ͳ͍ͱͲ͏ͳΔͷ͔ • 100ສߦΛݟ͍ͯͬͯɺWHERE۟ͷ৚ ݅ʹҾ͔͔ͬΒͳ͍ߦΛࣺͯΔ αʔόʔ ετϨʔδΤϯδϯ όοϑΝϓʔϧ ετϨʔδ ໊લΧϥϜ͕ʮՃ౻ʯʁ໊લΧϥϜ͕ʮՃ ౻ʯʁ໊લΧϥϜ͕ʮՃ౻ʯʁ໊લΧϥϜ͕ ʮՃ౻ʯʁ໊લΧϥϜ͕ʮՃ౻ʯʁ໊લΧϥ Ϝ͕ʮՃ౻ʯʁ໊લΧϥϜ͕ʮՃ౻ʯʁ໊લ ΧϥϜ͕ʮՃ౻ʯʁ໊લΧϥϜ͕ʮՃ౻ʯʁ ໊લΧϥϜ͕ʮՃ౻ʯʁ໊લΧϥϜ͕ʮՃ ౻ʯʁ໊લΧϥϜ͕ʮՃ౻ʯʁ໊લΧϥϜ͕ ʮՃ౻ʯʁ໊લΧϥϜ͕ʮՃ౻ʯʁ໊લΧʜ

Slide 52

Slide 52 text

ΠϯσοΫε͕࢖͑ͳ͍ͱͲ͏ͳΔͷ͔ • ࢒ͬͨ5݅ΛΫϥΠΞϯτʹฦ ͢ αʔόʔ ετϨʔδΤϯδϯ όοϑΝϓʔϧ ετϨʔδ ݅͋Γ·ͨ͠ʂʂʂ ΫϥΠΞϯτ

Slide 53

Slide 53 text

ΠϯσοΫε͕࢖͑ͳ͍ͱͲ͏ͳΔͷ͔ αʔόʔ ετϨʔδΤϯδϯ όοϑΝϓʔϧ ετϨʔδ Զͨͪ͸ԿͷͨΊʹ ຾Γ͔Βݺͼ֮·͞Εͨͷ͔ ΫϥΠΞϯτ ࣺͯΒΕͨ ݅

Slide 54

Slide 54 text

ΠϯσοΫε͕࢖͑ΔͱͲ͏ͳΔͷ͔ • ΠϯσοΫεΛݟΔͱ5͚݅ͩಡΈࠐ Ί͹ྑ͍͜ͱ͕Θ͔Δ • ͜ͷ5݅͸O(log n)Ͱݟ͔ͭΔ αʔόʔ ετϨʔδΤϯδϯ όοϑΝϓʔϧ ετϨʔδ ໊લΧϥϜ͕ʮՃ౻ʯ
 Ͱ͋Δ͚݅ͩ

Slide 55

Slide 55 text

ΠϯσοΫε͕࢖͑ΔͱͲ͏ͳΔͷ͔ • ετϨʔδΤϯδϯ͔Βαʔόʔ΁ αʔόʔ ετϨʔδΤϯδϯ όοϑΝϓʔϧ ετϨʔδ ༨༟ͷ݅

Slide 56

Slide 56 text

ΠϯσοΫε͕࢖͑ΔͱͲ͏ͳΔͷ͔ • 5݅ΛΫϥΠΞϯτʹฦ͚ͩ͢ ͷ؆୯ͳ͓࢓ࣄ αʔόʔ ετϨʔδΤϯδϯ όοϑΝϓʔϧ ετϨʔδ ݅͋Γ·ͨ͠ʂʂʂ ΫϥΠΞϯτ

Slide 57

Slide 57 text

ΠϯσοΫε͕࢖͑ΔͱͲ͏ͳΔͷ͔ αʔόʔ ετϨʔδΤϯδϯ όοϑΝϓʔϧ ετϨʔδ ฏ࿨ͩ ҆຾Ͱ͖Δ ΫϥΠΞϯτ ಡΈऔΒΕͳ͔ͬͨ ݅

Slide 58

Slide 58 text

ΠϯσοΫε͕࢖͑ͳ͍ͱͲ͏ͳΔ͔ • σϞ • explainΛݟΔ • explain select * from users where name = 'Ճ౻'\G • explain select * from slow_users where name = 'Ճ౻'\G

Slide 59

Slide 59 text

ΠϯσοΫε͸ࠨ͔Βӈ • ΠϯσοΫεͷΩʔ͸ࠨ୺͔Β͔͠࢖͑ͳ͍ • LIKEͰ͸%͕લʹ͋Δͱ࢖͑ͳ͍ • ࠨ୺͕ෆఆ͔ͩΒ • ࠨ୺͔Βݟ͍ͯ͘ͳΒɺ్த·ͰͰ΋OK • σϞ • select * from users where name like 'ాத%' limit 1\G • select * from users where name like '%ాத' limit 1\G

Slide 60

Slide 60 text

ΠϯσοΫε͸ࠨ͔Βӈ ాத ݈ଠ த઒ ᣦࢠ ాத ͘͞Β 68407 ాத Ұً 11322 ాத Ұ࿠ 75891 ాத ಸʑ 58408 த઒ ঊ 604143 த઒ ༔ਅ 547645

Slide 61

Slide 61 text

ΠϯσοΫε͸ࠨ͔Βӈ ాத ݈ଠ த઒ ᣦࢠ ాத ͘͞Β 68407 ాத Ұً 11322 ాத Ұ࿠ 75891 ాத ಸʑ 58408 த઒ ঊ 604143 த઒ ༔ਅ 547645 ాத%

Slide 62

Slide 62 text

ΠϯσοΫε͸ࠨ͔Βӈ ాத ݈ଠ த઒ ᣦࢠ ాத ͘͞Β 68407 ాத Ұً 11322 ాத Ұ࿠ 75891 ాத ಸʑ 58408 த઒ ঊ 604143 த઒ ༔ਅ 547645 ాத% ాதͰ࢝·͍ͬͯΔ෦෼͚ͩݟΔ

Slide 63

Slide 63 text

ΠϯσοΫε͸ࠨ͔Βӈ ాத ݈ଠ த઒ ᣦࢠ ాத ͘͞Β 68407 ాத Ұً 11322 ాத Ұ࿠ 75891 ాத ಸʑ 58408 த઒ ঊ 604143 த઒ ༔ਅ 547645 ాத%

Slide 64

Slide 64 text

ΠϯσοΫε͸ࠨ͔Βӈ ాத ݈ଠ த઒ ᣦࢠ ాத ͘͞Β 68407 ాத Ұً 11322 ాத Ұ࿠ 75891 ాத ಸʑ 58408 த઒ ঊ 604143 த઒ ༔ਅ 547645 ాத%

Slide 65

Slide 65 text

ΠϯσοΫε͸ࠨ͔Βӈ ాத ݈ଠ த઒ ᣦࢠ ాத ͘͞Β 68407 ాத Ұً 11322 ాத Ұ࿠ 75891 ాத ಸʑ 58408 த઒ ঊ 604143 த઒ ༔ਅ 547645 %ాத

Slide 66

Slide 66 text

ΠϯσοΫε͸ࠨ͔Βӈ ాத ݈ଠ த઒ ᣦࢠ ాத ͘͞Β 68407 ాத Ұً 11322 ాத Ұ࿠ 75891 ాத ಸʑ 58408 த઒ ঊ 604143 த઒ ༔ਅ 547645 %ాத

Slide 67

Slide 67 text

ΠϯσοΫε͸ࠨ͔Βӈ ాத ݈ଠ த઒ ᣦࢠ ాத ͘͞Β 68407 ాத Ұً 11322 ాத Ұ࿠ 75891 ాத ಸʑ 58408 த઒ ঊ 604143 த઒ ༔ਅ 547645 %ాத จࣈྻࠨଆ(ઌ಄ଆ)͔Βιʔτ͞ΕͯΔ͔Β
 ιʔτ͞Ε͍ͯΔ͜ͱΛੜ͔ͤͳ͍

Slide 68

Slide 68 text

ෳ߹ΠϯσοΫε • ෳ਺ͷΧϥϜΛ·ͱΊͯΠϯσοΫεΛுΔ • ΠϯσοΫεͷҰ෦͚ͩΛ࢖͏͜ͱ΋Ͱ͖Δ • ͜͜Ͱ΋ࠨ͔Βӈ͕ग़ͯ͘Δ • σϞ • select count(*) from users where blood_type = 'A' and name like 'ాத%'\G • select count(*) from users where blood_type = 'A'\G

Slide 69

Slide 69 text

ෳ߹ΠϯσοΫε A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645

Slide 70

Slide 70 text

ෳ߹ΠϯσοΫε: ෳ߹৚݅ʹΑΔݕࡧ A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 A, ాத%

Slide 71

Slide 71 text

ෳ߹ΠϯσοΫε: ෳ߹৚݅ʹΑΔݕࡧ A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 A, ాத%

Slide 72

Slide 72 text

ෳ߹ΠϯσοΫε: ෳ߹৚݅ʹΑΔݕࡧ A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 A, ాத%

Slide 73

Slide 73 text

ෳ߹ΠϯσοΫε: ෳ߹৚݅ʹΑΔݕࡧ A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 A, ాத%

Slide 74

Slide 74 text

ෳ߹ΠϯσοΫε: Ұ෦ͷ৚݅ʹΑΔݕࡧ(OK) A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 A

Slide 75

Slide 75 text

ෳ߹ΠϯσοΫε: Ұ෦ͷ৚݅ʹΑΔݕࡧ(OK) A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 A

Slide 76

Slide 76 text

ෳ߹ΠϯσοΫε: Ұ෦ͷ৚݅ʹΑΔݕࡧ(OK) A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 A

Slide 77

Slide 77 text

ෳ߹ΠϯσοΫε: Ұ෦ͷ৚݅ʹΑΔݕࡧ(OK) A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 A

Slide 78

Slide 78 text

ෳ߹ΠϯσοΫε: Ұ෦ͷ৚݅ʹΑΔݕࡧ(NG) A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 த઒

Slide 79

Slide 79 text

ෳ߹ΠϯσοΫε: Ұ෦ͷ৚݅ʹΑΔݕࡧ(NG) A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 த઒ ݂ӷܕ͕༏ઌ͞Εͯιʔτ͞Ε͍ͯΔ͔Β ໊લ͚ͩͩͱιʔτ͞Ε͍ͯΔ͜ͱΛੜ͔ͤͳ͍

Slide 80

Slide 80 text

NOTͷѻ͍ • Ұக͍ͤͯ͘͞ͷ͕جຊʹͳΔͨΊΠϯσοΫε͕ޮ ͔ͳ͍ • NOT͸Ͱ͖Δ͚ͩॻ͔ͳ͍Α͏ʹ • σϞ • select * from users where name not like 'Ճ౻%' limit 1\G • select * from users where name like 'Ճ౻%' limit 1\G • select * from users where name != 'Ճ౻' limit 1\G • ͜Ε͸MySQL 5.6Ҏ߱ͩͱ͍͚Δ

Slide 81

Slide 81 text

NULLͷѻ͍ • ࣮͸ΠϯσοΫε͕ޮ͘ • IS NULL • IS NOT NULL • ͜ͷڍಈ͸MySQL͚ͩͬΆ͍ • σϞ • select count(*) from users where phone_num is null\G • select count(*) from slow_users where phone_num is null\G • select count(*) from users where phone_num is not null\G • select count(*) from slow_users where phone_num is not null\G

Slide 82

Slide 82 text

ιʔτ • ݕࡧ͚ͩͰͳ͘ιʔτʹ΋ΠϯσοΫε͕࢖͑Δ • Կ౓΋ग़͖ͯͨΑ͏ʹB+໦ͷϦʔϑ͸ιʔτ͞ΕͯΔ • ෳ߹ΠϯσοΫε͸1൪໨ͰߜΓࠐΜͰ2൪໨Ͱιʔτ Ͱ͖Δ • σϞ • select * from users order by order_num limit 1\G • select * from slow_users order by order_num limit 1\G • select * from users where blood_type = 'B' order by birthday limit 1\G • select * from slow_users where blood_type = 'B' order by birthday limit 1\G

Slide 83

Slide 83 text

ιʔτ A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 A

Slide 84

Slide 84 text

ιʔτ A, ాத ݈ଠ B, த઒ ᣦࢠ A, ాத ͘͞Β 68407 A, ాத Ұً 11322 A, த઒ Ұ࿠ 75891 A, த઒ ಸʑ 58408 B, த઒ ঊ 604143 B, த઒ ༔ਅ 547645 A ઌ಄͕AͰ֬ఆ͍ͯ͠Δͱ͍͏͜ͱ͸
 ΩʔશମͰݟͨΒ໊લͰ΋ιʔτ͞Ε͍ͯΔ

Slide 85

Slide 85 text

ΧόϦϯάΠϯσοΫε • ʢηΧϯμϦʣΠϯσοΫεʹ͸ΩʔͷΧϥϜͱओΩʔؚ͕·ΕΔ • ΫΤϦதͷSELECTɺWHEREɺORDERͳͲ͕ͦΕΒͷΧϥϜͷΈ Ͱߏ੒͞Ε͍ͯΕ͹σʔλຊମͷB+໦ΛݟΔඞཁͳ͍ͷͰ͸ʁ • Exactlyʢͦͷͱ͓ΓͰ͍͟͝·͢ʣ ͜Εਂ໷ςϯγϣϯͩ • σʔλຊମΛݕࡧ͢Δख͕ؒল͔Εͯߴ଎ʹͳΔ • ࢥߟఀࢭͨ͠ঢ়ଶͰORMΛ࢖͏ͱৗʹ SELECT * ʹͳΓɺ͜ͷԸܙ ͕ड͚ΒΕͳ͘ͳΔ • σϞ • select count(*) from users where name = 'Ճ౻ ܙ'\G • select order_num, name from users order by order_num desc limit 1\G

Slide 86

Slide 86 text

ΧόϦϯάΠϯσοΫε Ճ౻ ݈ଠ த઒ ᣦࢠ Ճ౻ ܙ 13521 Ճ౻ ܙ 14491 Ճ౻ ݈հ 75891 த઒ ಸʑ 58408 த઒ ঊ 604143 த઒ ༔ਅ 547645 Ճ౻ ܙ

Slide 87

Slide 87 text

ΧόϦϯάΠϯσοΫε Ճ౻ ݈ଠ த઒ ᣦࢠ Ճ౻ ܙ 13521 Ճ౻ ܙ 14491 Ճ౻ ݈հ 75891 த઒ ಸʑ 58408 த઒ ঊ 604143 த઒ ༔ਅ 547645 COUNT(*) ʹඞཁͳ৘ใ͸े෼ʹ଍Γ͍ͯΔ name ͔ id ͰΧ΢ϯτ͞ΕΔ Ճ౻ ܙ

Slide 88

Slide 88 text

ͬ͘͟Γͨ͠໨࣍ • MySQLͷΞʔΩςΫνϟ • ςʔϒϧ΍ΠϯσοΫεͷσʔλߏ଄ • ύϑΥʔϚϯεʹؔ܎͢Δ࿩ • ڍಈʹؔ܎͢Δ࿩

Slide 89

Slide 89 text

ڍಈʹؔ܎͢Δ࿩ • τϥϯβΫγϣϯ෼཭Ϩϕϧ • σʔλߏ଄ͷ࿩ͱ͸͋·Γؔ࿈ͳ͍

Slide 90

Slide 90 text

τϥϯβΫγϣϯ෼཭Ϩϕϧ • READ UNCOMMITTED • READ COMMITTED • REPEATABLE READ • SERIALIZABLE

Slide 91

Slide 91 text

READ UNCOMMITTED • ίϛοτ͍ͯ͠ͳ͍σʔλ͕ଞͷτϥϯβΫγϣϯ͔ Βݟ͑Δʢʂʁʣ • ී௨͸࢖Θͳ͍

Slide 92

Slide 92 text

READ COMMITTED • ଟ͘ͷDBMSʹ͓͚ΔσϑΥϧτ • ଞͷτϥϯβΫγϣϯͰίϛοτ͞Εͨσʔλ͕ݟ͑ Δ

Slide 93

Slide 93 text

REPEATABLE READ • MySQLͷσϑΥϧτ • ࠓͷτϥϯβΫγϣϯͰ͍ͬͨΜಡΈࠐΜͩσʔλ ͸ɺଞͷτϥϯβΫγϣϯͰมߋ͕ίϛοτ͞Εͨ ޙ΋࠷ॳʹಡΈࠐΜͩͷͱಉ͡಺༰͕ݟ͑Δ

Slide 94

Slide 94 text

SERIALIZABLE • શͯͷಡΈऔΓͰϩοΫΛͱͬͯଞͷτϥϯβΫγϣ ϯͱͷڝ߹͕ى͖ͳ͍Α͏ʹ͢Δ • ฒྻੑೳ͕ѱ͍ • ϩοΫऔΓ·͘Δ͔ΒσουϩοΫى͖΍͘͢ͳΔ

Slide 95

Slide 95 text

REPEATABLE READ஫ҙ • ৽ଔ1೥໨ͷ࣌ʹɺ͜ΕΛཧղ͍ͯ͠ͳͯ͘DBͷόά ͩͱࢥͬͯ͠·ͬͨ... • σϞ • A: begin; • B: begin; • A: select * from users where id = 123456; • B: select * from users where id = 123456 for update; -- ͜ͷ࣌఺ͰഉଞϩοΫ • B: update users set order_num = order_num + 1 where id = 123456; • B: select * from users where id = 123456; • A: select * from users where id = 123456; -- ·ͩίϛοτͯ͠ͳ͍͔ΒมΘͬͯͳ͍ • B: commit; • A: select * from users where id = 123456; -- ίϛοτޙ͚ͩͲมΘͬͯͳ͍ • A: select * from users where id = 123456 for update; -- B͕ίϛοτͨ͠஋ͰऔΕΔ

Slide 96

Slide 96 text

·ͱΊ • Web։ൃ࣌͸DBΛ͔ͬ͠Γҙࣝ͢Δ • MySQL(InnoDB(ͷσϑΥϧτ))ͷΠϯσοΫε͸B+໦ • ͦͷಛੑΛཧղ্ͨ͠ͰΠϯσοΫεΛߟ͑Δ • FOR UPDATEͷ༗ແͰݟ͑Δ஋͕มΘΔͷ͸࢓༷

Slide 97

Slide 97 text

͓ܾ·Γͷ • த్ɾ৽ଔ໰Θͣ࠾༻ڧԽதͰ͢ • ڵຯ͋Δํ͸αϙʔλʔζ or @mihyaeru21 ·Ͱ