PHPerに知ってほしいRDBの事その3

by soudai sone

Slide 1

Slide 1 text

PHPerʹ஌ͬͯ΄͍͠RDBͷࣄ Version 3.0 1)1ΧϯϑΝϨϯεˏؔ੢

Slide 2

Slide 2 text

What is it? σʔλϕʔεͷण໋͸ ΞϓϦέʔγϣϯΑΓ΋௕͍

Slide 3

Slide 3 text

What is it? ͦΜͳ௕͍෇͖߹͍ʹͳΔσʔλϕʔε ͷେ੾ͳࣄΛ͓఻͑͠·͢

Slide 4

Slide 4 text

What is it? σʔλϕʔεͰ େ੾ͳ͜ͱ ΞϓϦέʔγϣϯͰ େ੾ͳ͜ͱ

Slide 5

Slide 5 text

What is it? ΞϓϦέʔγϣϯͰ େ੾ͳ͜ͱ σʔλϕʔεͰ େ੾ͳ͜ͱ ͜͜ͷ࿩Λ͠·͢

Slide 6

Slide 6 text

What is it? ର৅ͷσʔλϕʔειϑτ΢ΣΞ

Slide 7

Slide 7 text

What is it? PostgreSQL 9.5ͱMySQL 5.7(InnoDB)ʹݶΔ ଞͷRDBͷ࿩͸͠·ͤΜ

Slide 8

Slide 8 text

What is it? ׆༻ࣄྫ΍ηΩϡϦςΟͳͲͷ࿩ ↓ ֤σʔλϕʔεͷίϛϡχςΟʹࢀՃ͠Α͏ https://mysql-casual-slackin.herokuapp.com/ https://postgresql-hackers-jp.herokuapp.com/ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ

Slide 9

Slide 9 text

What is it? ࠓ೔ͷ࿩͢Δ͜ͱ

Slide 10

Slide 10 text

What is it? RDBΛ࢖͏্Ͱ͓͍֮͑ͯͯཉ͍͜͠ͱ

Slide 11

Slide 11 text

What is it? RDBΛ࢖͏্Ͱ͓͍֮͑ͯͯཉ͍͜͠ͱ ↓ جૅతͳ͜ͱͰ͙࣮͢ફͰ͖Δ͜ͱ

Slide 12

Slide 12 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹεΩʔϚϨε ̐ɹΞϯνύλʔϯ ̑ɹ·ͱΊ

Slide 13

Slide 13 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹεΩʔϚϨε ̐ɹΞϯνύλʔϯ ̑ɹ·ͱΊ

Slide 14

Slide 14 text

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ31ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

Slide 15

Slide 15 text

What is it? d

Slide 16

Slide 16 text

Slide 17

Slide 17 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹεΩʔϚϨε ̐ɹΞϯνύλʔϯ ̑ɹ·ͱΊ

Slide 18

Slide 18 text

ݕࡧͱINDEX #5SFF*/%&9

Slide 19

Slide 19 text

ݕࡧͱINDEX #5SFF*/%&9 3%#ͷ*/%&9͸جຊతʹ͜Ε ʢ(*/΍35SFFʹ͍ͭͯ͸ࠓճ৮Εͳ͍ʣ

Slide 20

Slide 20 text

ݕࡧͱINDEX Ҿ༻ݩɿIUUQTKBXJLJQFEJBPSHXJLJ##&$" ΢ΟΩϖσΟΞʮ#໦ʯ

Slide 21

Slide 21 text

ݕࡧͱINDEX w 8)&3&۟Ͱ࢖͏ w (3061#:۟Ͱ΋࢖͏ w 03%&3#:۟Ͱ΋࢖͏

Slide 22

Slide 22 text

ݕࡧͱINDEX w 8)&3&۟Ͱ࢖͏ w (3061#:۟Ͱ΋࢖͏ w 03%&3#:۟Ͱ΋࢖͏ ݕࡧશൠͰ࢖͏

Slide 23

Slide 23 text

ݕࡧͱINDEX w 8)&3&۟Ͱ࢖͏ w (3061#:۟Ͱ΋࢖͏ w 03%&3#:۟Ͱ΋࢖͏ ݕࡧશൠͰ࢖͏ ͔͠͠.Z42-͸ͭͷ5"#-&Ͱ ͔ͭ͠*/%&9Λ࢖Θͳ͍

Slide 24

Slide 24 text

ݕࡧͱINDEX */%&9ΛޮՌతʹ࢖͏ʹ͸ w ॏෳ͕গͳ͍΄͏͕ྑ͍ w ݁Ռ͕গͳ͍΄͏͕ྑ͍

Slide 25

Slide 25 text

ݕࡧͱINDEX */%&9ΛޮՌతʹ࢖͏ʹ͸ w ॏෳ͕গͳ͍΄͏͕ྑ͍ w ݁Ռ͕গͳ͍΄͏͕ྑ͍ 6OJRVF͕ཧ૝ ॏෳ͕ଟ͍৔߹ɺ*/%&9ͷҙຯ͕ബΕΔ Α͋͘Δͷ͸%&-&5&@'-"(

Slide 26

Slide 26 text

ݕࡧͱINDEX */%&9ΛޮՌతʹ࢖͏ʹ͸ w ॏෳ͕গͳ͍΄͏͕ྑ͍ w ݁Ռ͕গͳ͍΄͏͕ྑ͍ ࢀরͷ5"#-&ͷશͯΛදࣔ͢ΔͳΒݩ͔Βશ ෦ݟͨ΄͏͕ྑ͍ શମͷʙ·Ͱ͕*/%&9ར༻ͷ໨҆

Slide 27

Slide 27 text

ݕࡧͱINDEX */%&9Λ੍͢Δऀ͸ݕࡧΛ੍͢Δ

Slide 28

Slide 28 text

ݕࡧͱINDEX ࣮ߦܭըΛݟΔ

Slide 29

Slide 29 text

ݕࡧͱINDEX &91-"*/Ͱॏཁͳ͜ͱ w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ w ౷ܭ৘ใΛ࠷৽ʹ͢Δ w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ

Slide 30

Slide 30 text

ݕࡧͱINDEX &91-"*/ͷ·ͱΊ w ެࣜυΩϡϝϯτΛಡ΋͏ UZQFͳͲ͸छྨ͕୔ࢁ͋Δ   IUUQTEFWNZTRMDPNEPDSFGNBOKBFYQMBJOPVUQVUIUNM  IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMFYQMBJOIUNM w ૉৼΓେࣄ

Slide 31

Slide 31 text

ݕࡧͱINDEX .Z42-8PSLCFODI QHBENJO

Slide 32

Slide 32 text

࣮ߦܭը CREATE TABLE `demo`.ùsers` ( ìd` INT NOT NULL AUTO_INCREMENT COMMENT '', `name` VARCHAR(45) NOT NULL COMMENT '', àge` INT NOT NULL COMMENT '', `created` DATETIME NOT NULL DEFAULT NOW () COMMENT '', PRIMARY KEY (ìd`) COMMENT '' ); ——શ݅Λબ୒͢ΔͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users; ——PRIMARY KEYʢINDEXʣΛར༻ͨ͠ྫ SELECT * FROM demo.users WHERE id > 100; ——INDEX͕ແ͍ͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users WHERE age > 20;

Slide 33

Slide 33 text

MySQL

Slide 34

Slide 34 text

MySQL

Slide 35

Slide 35 text

MySQL ૬ؔαϒΫΤϦ

Slide 36

Slide 36 text

૬ؔαϒΫΤϦ ——஗͍૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age FROM users WHERE id BETWEEN 10 AND 100000)

Slide 37

Slide 37 text

MySQL

Slide 38

Slide 38 text

MySQL .Z42-͔Β."5&3*"-*;&%ʹ มΘΓɺ+0*/ͷΑ͏ͳڍಈʹͳͬͨ ੲΑΓૣ͘ͳͬͨ

Slide 39

Slide 39 text

૬ؔαϒΫΤϦ ——INDEX͕ར༻Ͱ͖Ε͹૬ؔαϒΫΤϦͰ΋଎͍ SELECT * FROM users WHERE created = '2016-02-27 04:31:32' AND id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)

Slide 40

Slide 40 text

MySQL

Slide 41

Slide 41 text

MySQL ૬ؔαϒΫΤϦ͸஗͍ ৔߹͕ଟ͍

Slide 42

Slide 42 text

MySQL ૬ؔαϒΫΤϦ͸஗͍ ˣ +0*/ʹॻ͖׵͑Δ

Slide 43

Slide 43 text

JOIN SELECT * FROM users INNER JOIN users AS tmp ON tmp.id = users.id AND tmp.id BETWEEN 10 AND 100000 WHERE users.created = '2016-02-27 04:31:32'

Slide 44

Slide 44 text

MySQL

Slide 45

Slide 45 text

MySQL +0*/͸ֻ͚ࢉ

Slide 46

Slide 46 text

MySQL +0*/͸ֻ͚ࢉ ˣ ߦ☓ߦ͸ߦ

Slide 47

Slide 47 text

MySQL .Z42-ʹ͸ +0*/ͷΞϧΰϦζϜ͸Ұ͔ͭ͠ͳ͍

Slide 48

Slide 48 text

MySQL .Z42-ʹ͸ +0*/ͷΞϧΰϦζϜ͸Ұ͔ͭ͠ͳ͍ ܾͯ͠ಘҙͳΘ͚Ͱ͸ແ͍ +0*/͸ϋΠίετͳΫΤϦ

Slide 49

Slide 49 text

MySQL w ग़དྷΔ͚ͩখ͔ͯ͘͞͠Β+0*/ w ෆཁͳ+0*/͸ආ͚Δ w */%&9Λར༻ͨ͠+0*/Λ͢Δ

Slide 50

Slide 50 text

MySQL γϯϓϧɾߴ଎

Slide 51

Slide 51 text

MySQL γϯϓϧɾߴ଎ ˣ ೉͍͠ࣄΛ͠ͳ͍ ઃܭྗ͕ॏཁ

Slide 52

Slide 52 text

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ

Slide 53

Slide 53 text

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ ͭͷΫΤϦ಺Ͱෳ਺࢖͑Δ

Slide 54

Slide 54 text

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ #USFFɺ(*/ɺࣜ*/%&9ͳͲγʔϯʹ߹Θͤͯ ෳ਺ͷ*/%&9Λར༻Ͱ͖Δ

Slide 55

Slide 55 text

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ ࢖͑Δ42-ͷߏจ΋ଟ͍ ྫ͑͹΢Πϯυ΢ؔ਺ͳͲ

Slide 56

Slide 56 text

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ /FTUFE-PPQ+PJO 4PSU.FSHF+PJO )BTI+PJO

Slide 57

Slide 57 text

PostgreSQL

Slide 58

Slide 58 text

PostgreSQL ͜ͷຊ͕͘͢͝ྑ͍ ͔͠͠ͷ࿩ IUUQXXXBNB[PODPKQ಺෦ߏ଄͔ΒֶͿ1PTUHSF42-ઃܭɾӡ༻ܭըͷమଇ4PGUXBSF%FTJHOQMVTEQ

Slide 59

Slide 59 text

ෳ਺ར༻ͨ͠INDEX CREATE TABLE public.users ( id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass), name text NOT NULL, age integer NOT NULL, created timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT users_pkey PRIMARY KEY (id)); CREATE INDEX users_created_idx ON public.users USING tree (created); ——idͱcreatedͷINDEXΛར༻͢Δ SELECT * FROM users WHERE id < 100 AND created < '2016-02-27 05:41:28';

Slide 60

Slide 60 text

PostgreSQL

Slide 61

Slide 61 text

ࣜINDEX ——ࣜINDEXΛ࡞੒ CREATE INDEX test_idx ON users (substr(name,10,12)); ——ࣜͷ݁ՌͰINDEX͕ޮ͘ SELECT name FROM users WHERE substr(name,10,12) = '10'

Slide 62

Slide 62 text

PostgreSQL

Slide 63

Slide 63 text

૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age FROM users WHERE id BETWEEN 10 AND 100000)

Slide 64

Slide 64 text

PostgreSQL

Slide 65

Slide 65 text

૬ؔαϒΫΤϦ ——σϞͰINDEXΛ࢖ͬͯ͘Εͳ͍ͷͰINDEXΛ༏ઌతʹར༻͢ΔΑ͏ʹࢦఆ SET ENABLE_SEQSCAN=OFF; SELECT * FROM users WHERE id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)

Slide 66

Slide 66 text

PostgreSQL

Slide 67

Slide 67 text

PostgreSQL 1PTUHSF42-ͷ૬ؔαϒΫΤϦ͸ ൺֱత଎͍

Slide 68

Slide 68 text

PostgreSQL ෳࡶͳूܭ͸ಘҙ ˣ Ͱ΋3%#ͷجຊ͸.Z42-ͱҰॹ

Slide 69

Slide 69 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹεΩʔϚϨε ̐ɹΞϯνύλʔϯ ̑ɹ·ͱΊ

Slide 70

Slide 70 text

εΩʔϚϨεͱਖ਼نԽ w σʔλ͸ফͤͳ͍ w σʔλΛมߋͰ͖ͳ͍ w σʔλͷ௥ՃͰରԠ

Slide 71

Slide 71 text

εΩʔϚϨεͱਖ਼نԽ σʔλͷෆ੔߹Ͱ೰·ͳ͍ͨΊʹ

Slide 72

Slide 72 text

εΩʔϚϨεͱਖ਼نԽ σʔλͷෆ੔߹Ͱ೰·ͳ͍ͨΊʹ جຊ͸ਖ਼نԽʂʂ

Slide 73

Slide 73 text

ਖ਼نԽ ΞϯέʔτϑΥʔϜ

Slide 74

Slide 74 text

JE ໊લ ճ౴ TPOF IPHF 40/& GVHB TPVEBJ GPP UBLFUPNP CBS

Slide 75

Slide 75 text

ਖ਼نԽ ΫϥΠΞϯτʮ͜͜ʹ࢓༷͕͋Δ͡ΌΖʁʯ ʈТʈ 㱭ʮ୒Ұճ౴ʯ㱬

Slide 76

Slide 76 text

ਖ਼نԽ ΫϥΠΞϯτʮ͜͜ʹ࢓༷͕͋Δ͡ΌΖʁʯ ͜ΕΛ ʈТʈ 㲇㱭㱬㲇

Slide 77

Slide 77 text

ਖ਼نԽ ΫϥΠΞϯτʮ͜͜ʹ࢓༷͕͋Δ͡ΌΖʁʯ ʈТʈ 㱭ʮෳ਺ճ౴ʯ㱬

Slide 78

Slide 78 text

ਖ਼نԽ ରԠྫ w $47ʹͯ͠ೖΕΔ w ΧϥϜΛ૿΍ͯ͠ೖΕΔ w +40/ΛUFYUͰೖΕΔˡ/&8ʂ

Slide 79

Slide 79 text

ਖ਼نԽ ରԠྫ w $47ʹͯ͠ೖΕΔ w ΧϥϜΛ૿΍ͯ͠ೖΕΔ w +40/ΛUFYUͰೖΕΔˡ/&8ʂ

Slide 80

Slide 80 text

ਖ਼نԽ ରԠྫ w $47ʹͯ͠ೖΕΔ w ΧϥϜΛ૿΍ͯ͠ೖΕΔ w +40/ΛUFYUͰೖΕΔˡ/&8ʂ

Slide 81

Slide 81 text

ਖ਼نԽ ରԠྫ w $47ʹͯ͠ೖΕΔ w ΧϥϜΛ૿΍ͯ͠ೖΕΔ w +40/ΛUFYUͰೖΕΔˡ/&8ʂ શ෦42-Ξϯνύλʔϯʂ

Slide 82

Slide 82 text

ਖ਼نԽ δΣΠ΢ΥʔΫ͕ͳͥμϝ͔ w ݕࡧ͕ࢮ͵ -*,&bژ౎`Ͱ౦ژ౎΋ώοτ w ूܭ͕ࢮ͵ (3061#:ͳͲू໿ؔ਺͕࢖͑ͳ͍ w ߋ৽͕ࢮ͵ ੍໿͕ग़དྷͳ͍͠෦෼ߋ৽ग़དྷͳ͍

Slide 83

Slide 83 text

ਖ਼نԽ ͳͷͰਖ਼نԽΛ͢Δ

Slide 84

Slide 84 text

JE OBNF TPOF 40/& TPVEBJ UBLFUPNP JE ࣭໰ ࣭໰̍ ࣭໰̎ ࣭໰̏ ࣭໰̐ ճ౴ऀ@JE ࣭໰@JE ճ౴ ճ౴೔ IPHF GVHB GPP CBS ճ౴ऀ ࣭໰ ճ౴

Slide 85

Slide 85 text

ਖ਼نԽ ਖ਼نԽ͢Δ͜ͱͰ σʔλͷߏ଄͕ڧ͘ͳΔ

Slide 86

Slide 86 text

εΩʔϚϨε ࢓༷มߋͷͨͼʹ %#ߏ଄Λมߋ͢Δͷ͸େม

Slide 87

Slide 87 text

εΩʔϚϨε ͦ͜Ͱ+40/ܕ

Slide 88

Slide 88 text

εΩʔϚϨε .Z42-ˠ+40/ܕ cc 1PTUHSF42-ˠ+40/#ܕ

Slide 89

Slide 89 text

εΩʔϚϨε 5&95ܕʹ+40/ೖΕΔͷͱ Կ͕ҧ͏ͷʁ

Slide 90

Slide 90 text

εΩʔϚϨε ରԠྫ w ਖ਼͍͠+40/Ͱ༗Δ͜ͱ͕୲อग़དྷΔ w +40/ͷॊೈͳݕࡧ͕ग़དྷΔ w */%&9ΛషΔखஈ͕༻ҙ͞Ε͍ͯΔ

Slide 91

Slide 91 text

εΩʔϚϨε ରԠྫ w ਖ਼͍͠+40/Ͱ༗Δ͜ͱ͕୲อग़དྷΔ w +40/ͷॊೈͳݕࡧ͕ग़དྷΔ w */%&9ΛషΔखஈ͕༻ҙ͞Ε͍ͯΔ */4&35ͷ࣌ʹσʔλͷόϦσʔγϣϯ͕͋Δ

Slide 92

Slide 92 text

εΩʔϚϨε ରԠྫ w ਖ਼͍͠+40/Ͱ༗Δ͜ͱ͕୲อग़དྷΔ w +40/ͷॊೈͳݕࡧ͕ग़དྷΔ w */%&9ΛషΔखஈ͕༻ҙ͞Ε͍ͯΔ ,FZͷ͍͋·͍ݕࡧͳͲͦΕͧΕ༻ҙ͞ΕͯΔ

Slide 93

Slide 93 text

εΩʔϚϨε ରԠྫ w ਖ਼͍͠+40/Ͱ༗Δ͜ͱ͕୲อग़དྷΔ w +40/ͷॊೈͳݕࡧ͕ग़དྷΔ w */%&9ΛషΔखஈ͕༻ҙ͞Ε͍ͯΔ .Z42-ͳΒ(FOFSBUFEDPMVNO 1PTUHSF42-ͳΒࣜ*/%&9ͰରԠՄೳ

Slide 94

Slide 94 text

JE OBNF QSPQFSUJFT UFTU \lOBNFzzIPHF lBHFz zSPMFz^ UFTU \lOBNFzzGVHB lBHFz zSPMFz^ UFTU \lOBNFzzCBSz lBHFz zSPMFz^ UFTU \lOBNFzzGPPz lBHFz zSPMFz^ UFTU \lOBNFzUPUP lBHFz zSPMFz^ UFTU \lOBNFzzUJUJz lBHFz zSPMFz^ UFTU \lOBNFzzUBUBz lBHFz zSPMFz^ ɿ ɿ ɿ ɿ ɿ ɿ

Slide 95

Slide 95 text

εΩʔϚϨε ஋Λࢦఆͨ͠ݕࡧ͕ग़དྷΔ

Slide 96

Slide 96 text

࣮ࡍͷSQL(PostgreSQL) SELECT * FROM demo_json WHERE properties->>”name” = “hoge”

Slide 97

Slide 97 text

࣮ࡍͷSQL(PostgreSQL) SELECT * FROM demo_json WHERE properties->>”name” = ‘hoge’ +40/ͷ஋Λࢦఆͯ͠௚઀ݕࡧग़དྷΔ

Slide 98

Slide 98 text

࣮ࡍͷSQL(MySQL) SELECT * FROM demo_json WHERE json_extract(properties,'$.name') = 'hoge';

Slide 99

Slide 99 text

࣮ࡍͷSQL(MySQL) SELECT * FROM demo_json WHERE json_extract(properties,'$.name') = 'hoge'; ؔ਺ʹݕࡧ͍ͨ͠,FZΛࢦఆ͢Δ

Slide 100

Slide 100 text

JSONܕͱJSONBܕ */%&9Λࢦఆ͢Δ

Slide 101

Slide 101 text

࣮ࡍͷSQL(PostgreSQL) CREATE INDEX ɹɹdemo_json_index ON ɹɹdemo_json ɹɹ(properties->>'name');

Slide 102

Slide 102 text

࣮ࡍͷSQL(PostgreSQL) CREATE INDEX ɹɹdemo_json_index ON ɹɹdemo_json ɹɹ(properties->>'name'); */%&9໊

Slide 103

Slide 103 text

࣮ࡍͷSQL(PostgreSQL) CREATE INDEX ɹɹdemo_json_index ON ɹɹdemo_json ɹɹ(properties->>'name'); */%&9໊ ର৅ςʔϒϧ

Slide 104

Slide 104 text

࣮ࡍͷSQL(PostgreSQL) CREATE INDEX ɹɹdemo_json_index ON ɹɹdemo_json ɹɹ(properties->>'name'); */%&9໊ ର৅ςʔϒϧ ࣜΛࢦఆ͢Δ

Slide 105

Slide 105 text

MySQLͷ৔߹ ࣜ*/%&9͕ແ͍ ˣ (FOFSBUFEDPMVNOΛ࢖͏

Slide 106

Slide 106 text

MySQLͷ৔߹ JE OBNF QSPQFSUJFT Ծ૝ྻ UFTU \lOBNFzzIPHF lBHFz zSPMFz^ IPHF UFTU \lOBNFzzGVHB lBHFz zSPMFz^ GVHB UFTU \lOBNFzzCBSz lBHFz zSPMFz^ CBS UFTU \lOBNFzzGPPz lBHFz zSPMFz^ GPP ɿ ɿ ɿ ɿ ɿ ɿ

Slide 107

Slide 107 text

Slide 108

Slide 108 text

Slide 109

Slide 109 text

JSONܕͱJSONBܕ ੍໿Λઃ͚Εͳ͍ ˣ ֎෦Ωʔ΍Ұҙ੍໿͕ͳ͍

Slide 110

Slide 110 text

JSONܕͱJSONBܕ σʔλొ࿥ͷτϨʔυΦϑ

Slide 111

Slide 111 text

JSONܕͱJSONBܕ σʔλొ࿥ͷτϨʔυΦϑ ˣ ࣗ༝ͳอଘɹ㱻ɹਖ਼͘͠कΔ

Slide 112

Slide 112 text

JSONܕͱJSONBܕ ཁ݅ʹ߹Θͤͯબ୒ࢶΛબͿ

Slide 113

Slide 113 text

JSONܕͱJSONBܕ ·ͱΊ ̍ɹ஋Ͱݕࡧ͕ग़དྷΔ ̎ɹ஋ͷݕࡧͰ*/%&9͕࢖͑Δ ̏ɹཁ݅ʹ߹Θͤͯબ୒ࢶΛબͿ

Slide 114

Slide 114 text

εΩʔϚϨε ͜Μͳ৔߹͸࢖ͬͯ΋͍͍

Slide 115

Slide 115 text

εΩʔϚϨε ར༻ྫ w +40/ͱଞͷςʔϒϧ͕௚઀ؔ࿈͠ͳ͍ w +40/ͷதͷ஋΍,FZͷ"EEͳͲͷ61%"5&ແ͍ w */4&35ͱ4&-&$5͕େ൒ͷߏ଄Ͱ͋Δ

Slide 116

Slide 116 text

εΩʔϚϨε ར༻ྫ w +40/ͱଞͷςʔϒϧ͕௚઀ؔ࿈͠ͳ͍ w +40/ͷதͷ஋΍,FZͷ"EEͳͲͷ61%"5&ແ͍ w */4&35ͱ4&-&$5͕େ൒ͷߏ଄Ͱ͋Δ +40/ͷதͷ஋͕֎෦Ωʔ੍໿Λඞཁͱ͠ͳ͍

Slide 117

Slide 117 text

εΩʔϚϨε ར༻ྫ w +40/ͱଞͷςʔϒϧ͕௚઀ؔ࿈͠ͳ͍ w +40/ͷதͷ஋΍,FZͷ"EEͳͲͷ61%"5&ແ͍ w */4&35ͱ4&-&$5͕େ൒ͷߏ଄Ͱ͋Δ ෦෼ߋ৽͸͋Δ͕ߴ଎Ͱ͸ͳ͍ খ͍͞+40/Λѻ͏ͳΒྑ͍

Slide 118

Slide 118 text

εΩʔϚϨε ར༻ྫ w +40/ͱଞͷςʔϒϧ͕௚઀ؔ࿈͠ͳ͍ w +40/ͷதͷ஋΍,FZͷ"EEͳͲͷ61%"5&ແ͍ w */4&35ͱ4&-&$5͕େ൒ͷߏ଄Ͱ͋Δ +40/Λؙͬͱ61%"5&Ͱ͸ͳ͘සൟʹ ཁૉͷมߋ͕ඞཁͳ৔߹ɺͦΕ͸ਖ਼نԽ͢΂͖

Slide 119

Slide 119 text

εΩʔϚϨε ͦ΋ͦ΋ʜ03.͕ࢮ͵ʂ

Slide 120

Slide 120 text

εΩʔϚϨε ͝ར༻͸ܭըతʹ

Slide 121

Slide 121 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹεΩʔϚϨε ̐ɹΞϯνύλʔϯ ̑ɹ·ͱΊ

Slide 122

Slide 122 text

Ξϯνύλʔϯ %&-&5&@'-"(

Slide 123

Slide 123 text

Ξϯνύλʔϯ ϑϥά໊ͬͯલ4&-&$5ͨ͠Β ͱ͔/6--͕͍Δ

Slide 124

Slide 124 text

Ξϯνύλʔϯ ϑϥά໊ͬͯલ4&-&$5ͨ͠Β ͱ͔/6--͕͍Δ ΧϥϜ໊ͱσʔλ͕ണ཭͍ͯ͠Δ TUBUVTͷJOU͕ϚδοΫφϯόʔԽ

Slide 125

Slide 125 text

Ξϯνύλʔϯ ඇਖ਼نԽ

Slide 126

Slide 126 text

Ξϯνύλʔϯ ඇਖ਼نԽ ଎౓ͱਖ਼نԽ͸τϨʔυΦϑͱ ݴ͏৔߹΋͋Δ͕ਖ਼͍͠σʔλΛ࡞Ζ͏

Slide 127

Slide 127 text

Ξϯνύλʔϯ ඇਖ਼نԽ ଎౓ͱਖ਼نԽ͸τϨʔυΦϑͱ ݴ͏৔߹΋͋Δ͕ਖ਼͍͠σʔλΛ࡞Ζ͏ ϚςϏϡʔͩͬͨΓΩϟογϡͰ ղܾग़དྷΔࣄ΋ଟ͍

Slide 128

Slide 128 text

Ξϯνύλʔϯ ֎෦ΩʔϨεઃܭ

Slide 129

Slide 129 text

Ξϯνύλʔϯ ֎෦ΩʔϨεઃܭ ਖ਼نԽ΋֎෦Ωʔ੍໿΋ σʔλΛकΔͨΊʹ͋Δʂʂ

Slide 130

Slide 130 text

Ξϯνύλʔϯ ͦ΋ͦ΋3%#ษڧ͠Α͏

Slide 131

Slide 131 text

Ξϯνύλʔϯ ӡ༻ͱઃܭͱཧ࿦ͷ࿩ ↓ SQLΞϯνύλʔϯͱDB࣮ફೖ໳Λಡ΋͏

Slide 132

Slide 132 text

·ͱΊ

Slide 133

Slide 133 text

Ξϯνύλʔϯ SQLͷ࢖͍ํͩͱ͜ͷຊ͕Φεεϝ

Slide 134

Slide 134 text

·ͱΊ

Slide 135

Slide 135 text

Ξϯνύλʔϯ ͋Δ೔ಥવ஗͘ͳΔ

Slide 136

Slide 136 text

Ξϯνύλʔϯ ͋Δ೔ಥવ஗͘ͳΔ ˣ σʔλྔ͕ϝϞϦʹ৐Βͳ͘ͳΔ

Slide 137

Slide 137 text

࣮ફͰϋϚΔ᠘ w ूܭର৅͕େ͖͘ͳͬͯूܭ࣌ʹϝϞϦʹ৐Βͳ͍  ˠ5FNQPSBSZΛ%JTLʹు͘ͷͰॏ͘ͳΔ w */%&9͕ແ͘5"#-&εΩϟϯΛ͍ͯͨ͠  ˠςετ࣌͸σʔλ͕খ͔ͬͨ͞ͷͰ໰୊ʹؾ͔ͮͳ͔ͬͨ w ϓϩάϥϜ͕ϧʔϓͷ਺͚ͩΫΤϦΛ౤͍͛ͯͨ  ˠߦ਺͕૿͑Δͱϧʔϓճ਺͕૿͑ͯॏ͘ͳΔ w ߋ৽͕ܹ͘͠*/%&9ͷஅยԽ͕ൃੜͨ͠  ˠ61%"5&΍%&-&5&ʹΑͬͯ*/%&9͕அยԽͯ͠஗͘ͳΔ

Slide 138

Slide 138 text

Slide 139

Slide 139 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹεΩʔϚϨε ̐ɹΞϯνύλʔϯ ̑ɹ·ͱΊ