実行計画の話 - Speaker Deck

Slide 1

Slide 1 text

࣮ߦܭըͷ࿩ ୈճɹதࠃ஍ํ%#ษڧձ

Slide 2

Slide 2 text

What is it? ࣮ߦܭըͷݟͯ·͔͢ʁ

Slide 3

Slide 3 text

What is it? INDEXΛ੍͢Δऀ͸RDBΛ੍͢Δ

Slide 4

Slide 4 text

What is it? ·ͣ͸࣮ߦܭըͷೖ໳ฤ

Slide 5

Slide 5 text

What is it? ग़དྷΔʂEXPLAINʂʂ

Slide 6

Slide 6 text

What is it? ͨͩ͠ɺPostgreSQLͱMySQLʹݶΔ ଞͷRDBͷ࿩͸͠·ͤΜ

Slide 7

Slide 7 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ

Slide 8

Slide 8 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

Slide 11

Slide 11 text

What is it? d

Slide 12

Slide 12 text

No content

Slide 13

Slide 13 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ

Slide 14

Slide 14 text

MySQL .Z42-ͷಛ௃

Slide 15

Slide 15 text

MySQL .Z42-ͷಛ௃ ˣ γϯϓϧɾߴ଎

Slide 16

Slide 16 text

MySQL ߋ৽ܕΞʔΩςΫνϟ

Slide 17

Slide 17 text

MySQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ

Slide 18

Slide 18 text

MySQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ ಉ͡ߦΛߋ৽͢Δ

Slide 19

Slide 19 text

MySQL ετϨʔδΤϯδϯ

Slide 20

Slide 20 text

MySQL Ҿ༻ݩɿIUUQXXXTMJEFTIBSFOFUZPLVIBOEMFS !ZPLV͞Μɺ͍ͭ΋͋Γ͕ͱ͏͍͟͝·͢ɻ

Slide 21

Slide 21 text

MySQL Ҿ༻ݩɿIUUQXXXTMJEFTIBSFOFUZPLVIBOEMFS !ZPLV͞Μɺ͍ͭ΋͋Γ͕ͱ͏͍͟͝·͢ɻ

Slide 22

Slide 22 text

MySQL ετϨʔδΤϯδϯͱ͸

Slide 23

Slide 23 text

MySQL ετϨʔδΤϯδϯͱ͸ ˣ େମσʔλͷಡΈॻ͖͢Δͱ͜Ζ

Slide 24

Slide 24 text

MySQL ͔͜͜Βઌ͸*OOP%#ͷ࿩ ͋ͱ.Z42-ͷ࿩ ʁ.Z*4".ʁ஌Βͳ͍ࢠͰ͢Ͷʢന໨

Slide 25

Slide 25 text

MySQL γϯϓϧɾߴ଎

Slide 26

Slide 26 text

MySQL γϯϓϧɾߴ଎ ˣ */%&9͕ޮ͍͍ͯΔࣄ͕େࣄ

Slide 27

Slide 27 text

MySQL #5SFF*/%&9

Slide 28

Slide 28 text

MySQL #5SFF*/%&9 .Z42-͸جຊతʹ͜Ε ʢ35SFFʹ͍ͭͯ͸ࠓճ৮Εͳ͍ʣ

Slide 29

Slide 29 text

MySQL Ҿ༻ݩɿIUUQTKBXJLJQFEJBPSHXJLJ##&$" ΢ΟΩϖσΟΞʮ#໦ʯ

Slide 30

Slide 30 text

MySQL w 8)&3&۟Ͱ࢖͏ w (3061#:۟Ͱ΋࢖͏ w 03%&3#:۟Ͱ΋࢖͏

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

MySQL w ॏෳ͕গͳ͍΄͏͕ྑ͍ w ݁Ռ͕গͳ͍΄͏͕ྑ͍ w ར༻͢Δ*/%&9͸̍5"#-&ʹݸ

Slide 33

Slide 33 text

MySQL w ॏෳ͕গͳ͍΄͏͕ྑ͍ w ݁Ռ͕গͳ͍΄͏͕ྑ͍ w ར༻͢Δ*/%&9͸̍5"#-&ʹݸ .Z42-ͷେ͖ͳಛ௃ͷҰͭ ʢΠϯσοΫε݁߹ʹ͍ͭͯ͸ࠓճ৮Εͳ͍ʣ

Slide 34

Slide 34 text

MySQL ࣮ߦܭըΛݟΔ

Slide 35

Slide 35 text

MySQL &91-"*/

Slide 36

Slide 36 text

MySQL ׽ͷίϯϐϡʔλಓΛݟΑ͏ .Z42-ͷ&91-"*/Λపఈղઆ

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Slide 39

Slide 39 text

Slide 40

Slide 40 text

Slide 41

Slide 41 text

࣮ߦܭը mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code = C1.CountryCode; +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | | | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | | | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where | +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+ 3 rows in set (0.00 sec) Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!! http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html ɾUZQF ϨίʔυΞΫηελΠϓɻ ཁ͸*/%&9ͷར༻ͷ༗ແ΍࢖͍ํΛදͨ͠΋ͷ ɾओͳछྨ DPOTUɾɾɾ13*."3:,&:·ͨ͸6/*26&ΠϯσοΫεͷϧοΫΞοϓʹΑΔΞΫηεɻ࠷଎ɻ FR@SFGɾɾɾ+0*/ʹ͓͍ͯ13*"3:,&:·ͨ͸6/*26&,&:͕ར༻͞ΕΔ࣌ͷΞΫηελΠϓɻDPOTUͱࣅ͍ͯΔ͕+0*/ Ͱ༻͍ΒΕΔͱ͜Ζ͕ҧ͏ɻ SFGɾɾɾϢχʔΫʢ13*."3:PS6/*26&ʣͰͳ͍ΠϯσοΫεΛ࢖ͬͯ౳Ձݕࡧʢ8)&3&LFZWBMVFʣΛߦͬͨ ࣌ʹ࢖ΘΕΔΞΫηελΠϓɻ SBOHFɾɾɾΠϯσοΫεΛ༻͍ͨൣғݕࡧɻ JOEFYɾɾɾϑϧΠϯσοΫεεΩϟϯɻΠϯσοΫεશମΛεΩϟϯ͢Δඞཁ͕͋ΔͷͰͱͯ΋஗͍ɻ "--ɾɾɾϑϧςʔϒϧεΩϟϯɻΠϯσοΫε͕·ͬͨ͘ར༻͞Ε͍ͯͳ͍͜ͱΛࣔ͢ɻ0-51ܥͷॲཧͰ͸վળඞਢɻ

Slide 42

Slide 42 text

࣮ߦܭը mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code = C1.CountryCode; +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 237 | | | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | C1.CountryCode | 1 | | | 2 | DERIVED | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where | +----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+ 3 rows in set (0.00 sec) Ҿ༻ݩ:MySQLͷEXPLAINΛపఈղઆ!! http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html ɾUZQF ϨίʔυΞΫηελΠϓɻ ཁ͸*/%&9ͷར༻ͷ༗ແ΍࢖͍ํΛදͨ͠΋ͷ ɾओͳछྨ DPOTUɾɾɾ13*."3:,&:·ͨ͸6/*26&ΠϯσοΫεͷϧοΫΞοϓʹΑΔΞΫηεɻ࠷଎ɻ FR@SFGɾɾɾ+0*/ʹ͓͍ͯ13*"3:,&:·ͨ͸6/*26&,&:͕ར༻͞ΕΔ࣌ͷΞΫηελΠϓɻDPOTUͱࣅ͍ͯΔ͕+0*/ Ͱ༻͍ΒΕΔͱ͜Ζ͕ҧ͏ɻ SFGɾɾɾϢχʔΫʢ13*."3:PS6/*26&ʣͰͳ͍ΠϯσοΫεΛ࢖ͬͯ౳Ձݕࡧʢ8)&3&LFZWBMVFʣΛߦͬͨ ࣌ʹ࢖ΘΕΔΞΫηελΠϓɻ SBOHFɾɾɾΠϯσοΫεΛ༻͍ͨൣғݕࡧɻ JOEFYɾɾɾϑϧΠϯσοΫεεΩϟϯɻΠϯσοΫεશମΛεΩϟϯ͢Δඞཁ͕͋ΔͷͰͱͯ΋஗͍ɻ "--ɾɾɾϑϧςʔϒϧεΩϟϯɻΠϯσοΫε͕·ͬͨ͘ར༻͞Ε͍ͯͳ͍͜ͱΛࣔ͢ɻ0-51ܥͷॲཧͰ͸վળඞਢɻ ݟ͚ͭͨΒνϡʔχϯάର৅ʂ

Slide 43

Slide 43 text

Slide 44

Slide 44 text

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Slide 47

Slide 47 text

Slide 48

Slide 48 text

Slide 49

Slide 49 text

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

MySQL &91-"*/Ͱॏཁͳ͜ͱ w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ w ౷ܭ৘ใΛ࠷৽ʹ͢Δ w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ σʔλྔʹΑ࣮ͬͯߦܭը͕มΘΔ͜ͱ΋ଟ͍ ࣮ࡍΑΓগͳ͍σʔλͰ֬ೝͯ͠΋ҙຯ͕ແ͍

Slide 52

Slide 52 text

MySQL &91-"*/Ͱॏཁͳ͜ͱ w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ w ౷ܭ৘ใΛ࠷৽ʹ͢Δ w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ σʔλʹେྔͷ௥Ճɾ࡟আɾมߋ͕͋Δͱ౷ܭ৘ใ͕ෆਖ਼֬ʹͳΔ "/"-:;&5"#-&Ͱ౷ܭ৘ใΛߋ৽͔ͯ͠Β&91-"*/ʂʂ

Slide 53

Slide 53 text

MySQL &91-"*/Ͱॏཁͳ͜ͱ w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ w ౷ܭ৘ใΛ࠷৽ʹ͢Δ w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ ࣮ߦܭըͰߴ଎Խͯ͠΋࣮ߦ݁Ռ͕มΘΔͱҙຯ͕ແ͍ ࠷ऴతʹຊ൪Ͱ֬ೝ͔ͯ͠ΒϦϦʔε͢Δ͜ͱ

Slide 54

Slide 54 text

MySQL &91-"*/ͷ·ͱΊ w લड़ͷԞ໺͞ΜͷهࣄΛಡ΋͏ w ެࣜυΩϡϝϯτΛಡ΋͏ UZQFͳͲ͸ଞʹ΋͋Δ   IUUQTEFWNZTRMDPNEPDSFGNBOKBFYQMBJOPVUQVUIUNM w ૉৼΓେࣄ

Slide 55

Slide 55 text

MySQL &91-"*/͸೉͍͠ʜ

Slide 56

Slide 56 text

MySQL Ͱ΋େৎ෉ɺ(6*͕͋Δʂ

Slide 57

Slide 57 text

MySQL .Z42-8PSLCFODI

Slide 58

Slide 58 text

MySQL .Z42-8PSLCFODI ˣ ແྉͰ࢖͑Δ

Slide 59

Slide 59 text

MySQL

Slide 60

Slide 60 text

MySQL 0SBDMFΞΧ΢ϯτෆཁʂʂ

Slide 61

Slide 61 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 62

Slide 62 text

MySQL

Slide 63

Slide 63 text

MySQL

Slide 64

Slide 64 text

MySQL αϒΫΤϦͱ૬ؔαϒΫΤϦ

Slide 65

Slide 65 text

αϒΫΤϦ —— INDEXͱͯ͠PRIMARY KEY͕ޮ͍͍ͯΔ SELECT * FROM users WHERE id < 1000 AND id > 800 —— 1ճ͔࣮͠ߦ͞Εͳ͍ SELECT * FROM (SELECT * FROM users WHERE id < 1000 AND id > 800) AS dummy

Slide 66

Slide 66 text

MySQL

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

MySQL

Slide 69

Slide 69 text

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

Slide 70

Slide 70 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 71

Slide 71 text

MySQL

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 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 75

Slide 75 text

MySQL

Slide 76

Slide 76 text

MySQL +0*/͸ֻ͚ࢉ

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

MySQL γϯϓϧɾߴ଎

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔҧ͍ ̑ɹ·ͱΊ

Slide 84

Slide 84 text

PostgreSQL 1PTUHSF42-ͷಛ௃

Slide 85

Slide 85 text

PostgreSQL 1PTUHSF42-ͷಛ௃ ˣ ߴػೳ

Slide 86

Slide 86 text

PostgreSQL ௥هܕΞʔΩςΫνϟ

Slide 87

Slide 87 text

PostgreSQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ

Slide 88

Slide 88 text

PostgreSQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ࢀরΛ֎͢

Slide 89

Slide 89 text

PostgreSQL Ϩίʔυ Ϩίʔυ Ϩίʔυ ςʔϒϧ ςʔϒϧ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ৽ͨʹมߋ෼Λ௥Ճ͢Δ

Slide 90

Slide 90 text

PostgreSQL ͭ·Γɺߋ৽͸.Z42-ΑΓෆར ʢ%&-&5&͸.Z42-΋ࣅͨΑ͏ͳࣄͯ͠Δʣ

Slide 91

Slide 91 text

PostgreSQL ͔͠͠ɺখ೉͍͜͠ͱ͸ಘҙ

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

PostgreSQL &91-"*/

Slide 98

Slide 98 text

PostgreSQL

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

PostgreSQL 1($PO5PLZPʲ%ʳ 1PTUHSF42-42-νϡʔχϯάೖ໳ೖ໳ฤ ʢԼխҙඒلʣ IUUQTXXXZPVUVCFDPNXBUDI WHYT#JVCLOPIUNM'BMTF

Slide 101

Slide 101 text

PostgreSQL ͘͢͝ৄ͍͠ͷͰ ͜ͷ:PV5VCFΛݟ͍ͯͩ͘͞

Slide 102

Slide 102 text

PostgreSQL 1PTUHSF42-Ͱ΋(6*ͰݟΕΔ

Slide 103

Slide 103 text

PostgreSQL QHBENJO

Slide 104

Slide 104 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 105

Slide 105 text

PostgreSQL

Slide 106

Slide 106 text

PostgreSQL

Slide 107

Slide 107 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 108

Slide 108 text

PostgreSQL

Slide 109

Slide 109 text

PostgreSQL

Slide 110

Slide 110 text

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

Slide 111

Slide 111 text

PostgreSQL

Slide 112

Slide 112 text

PostgreSQL

Slide 113

Slide 113 text

PostgreSQL ΦϓςΟϚΠβ͕ αϒΫΤϦΛ+0*/ʹॻ͖׵͑ͯΔ

Slide 114

Slide 114 text

૬ؔαϒΫΤϦ ——INDEXΛར༻ͨ͠αϒΫΤϦ SELECT * FROM users WHERE id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)

Slide 115

Slide 115 text

PostgreSQL ΦϓςΟϚΠβ͕+0*/ʹʢ͈́

Slide 116

Slide 116 text

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

Slide 117

Slide 117 text

PostgreSQL

Slide 118

Slide 118 text

PostgreSQL

Slide 119

Slide 119 text

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

Slide 120

Slide 120 text

΢Πϯυ΢ؔ਺ SELECT rank() OVER (PARTITION BY age ORDER BY id) , * FROM users WHERE age BETWEEN 10 AND 30 LIMIT 100

Slide 121

Slide 121 text

PostgreSQL

Slide 122

Slide 122 text

PostgreSQL

Slide 123

Slide 123 text

PostgreSQL ෳࡶͳूܭ͸ಘҙ

Slide 124

Slide 124 text

PostgreSQL ΢Πϯυ΢ؔ਺ʹ͍ͭͯ ߹ΘͤͯಡΉͱྑ͍ࢿྉ 8FCΤϯδχΞ͕1PTUHSF42-ΛબͿͭͷཧ༝ IUUQXXXTMJEFTIBSFOFU4PVEBJ4POFXFCQPTUHSFTRM

Slide 125

Slide 125 text

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

Slide 126

Slide 126 text

PostgreSQL ෳࡶͳूܭ͸ಘҙ ˣ Ͱ΋3%#ͷجຊ͸.Z42-ͱҰॹ ਖ਼نԽ͕ॏཁ */%&9Λར༻͠ͳ͍ͱ஗͍ +0*/͸ϋΠίετͳΫΤϦ ࣮ߦܭը͸ݟΕΔΑ͏ʹͳΖ͏ʂ

Slide 127

Slide 127 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ

Slide 128

Slide 128 text

࣮ફͰϋϚΔ᠘ աڈͷ৘ใʢϊΠζʣ

Slide 129

Slide 129 text

࣮ફͰϋϚΔ᠘ աڈͷ৘ใʢϊΠζʣ ˣ (PPHMFݕࡧͷݹ͍هࣄ΍ ޱ఻ʹ࿭Θ͞ΕΔ

Slide 130

Slide 130 text

࣮ફͰϋϚΔ᠘ աڈͷ৘ใʢϊΠζʣ ˣ (PPHMFݕࡧͷݹ͍هࣄ΍ ޱ఻ʹ࿭Θ͞ΕΔ 3%#͕ۤखͳͳઌഐ΍্͕࢘ ෇͍͕ͨ࣌ͱͯ΋ةݥ

Slide 131

Slide 131 text

࣮ફͰϋϚΔ᠘ w"-5&3จͰϩοΫΛऔΔʢ.Z42-ʣ w7"$66.͸खಈʢ1PTUHSF42-ʣ

Slide 132

Slide 132 text

࣮ફͰϋϚΔ᠘ w"-5&3จͰϩοΫΛऔΔʢ.Z42-ʣ w7"$66.͸खಈʢ1PTUHSF42-ʣ .Z42-͔ΒΦϯϥΠϯ "-5&3จΛαϙʔτ

Slide 133

Slide 133 text

࣮ફͰϋϚΔ᠘ w"-5&3จͰϩοΫΛऔΔʢ.Z42-ʣ w7"$66.͸खಈʢ1PTUHSF42-ʣ 1PTUHSF42-͔Β "VUP7"$66.Λαϙʔτ

Slide 134

Slide 134 text

࣮ફͰϋϚΔ᠘ ࣗ෼ͷ࢖͍ͬͯΔόʔδϣϯΛ ͔ͬ͠Γ֬ೝ͢Δ͜ͱ

Slide 135

Slide 135 text

࣮ફͰϋϚΔ᠘ ग़དྷΔ͚ͩ৽͍͠όʔδϣϯ͕ྑ͍ ϝδϟʔόʔδϣϯʹؔͯ͠͸ ೥ޙ͙Β͍ͷόʔδϣϯ͕͍͍ײ͡

Slide 136

Slide 136 text

࣮ફͰϋϚΔ᠘ ৽ػೳΛ࢖͏

Slide 137

Slide 137 text

࣮ફͰϋϚΔ᠘ ৽ػೳΛ࢖͏ ˣ 3%#ͷ৽ػೳ͸஍ཕʢόάʣΛ ౿Ή֮ޛ͕ඞཁ

Slide 138

Slide 138 text

࣮ફͰϋϚΔ᠘ ৽ػೳΛ࢖͏ ˣ 3%#ͷ৽ػೳ͸஍ཕʢόάʣΛ ౿Ή֮ޛ͕ඞཁ خʑͱͯ͠஍ཕΛ౿Έൈ͘Α͏ʹͳͬͨΒ ͦΕ͸Ұྲྀͷূʢଟ෼

Slide 139

Slide 139 text

࣮ફͰϋϚΔ᠘ ࢥ͍ࠐΈ

Slide 140

Slide 140 text

࣮ફͰϋϚΔ᠘ ࢥ͍ࠐΈ ˣ ޓ׵ੑͷແ͍มߋͰ ΧδϡΞϧʹࢮ͵

Slide 141

Slide 141 text

࣮ફͰϋϚΔ᠘ ࢥ͍ࠐΈ ˣ ޓ׵ੑͷແ͍มߋͰ ΧδϡΞϧʹࢮ͵ όʔδϣϯҧ͍΍ҧ͏%#Λ ࢖ͬͨ࣌ʹΑ͘ൃੜ͢Δ

Slide 142

Slide 142 text

࣮ફͰϋϚΔ᠘ wυΩϡϝϯτΛݟΔ w࣮ߦܭըΛݟΔ wਪଌΑΓ΋ܭଌ

Slide 143

Slide 143 text

࣮ફͰϋϚΔ᠘ ͋Δ೔ಥવ஗͘ͳΔ

Slide 144

Slide 144 text

࣮ફͰϋϚΔ᠘ ͋Δ೔ಥવ஗͘ͳΔ ˣ σʔλྔ͕ϝϞϦʹ৐Βͳ͘ͳΔ

Slide 145

Slide 145 text

࣮ફͰϋϚΔ᠘ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦ ϝϞϦ Ϩίʔυ Ϩίʔυ Ϩίʔυ σʔλ૿

Slide 146

Slide 146 text

࣮ફͰϋϚΔ᠘ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦ ϝϞϦ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦʹ৐Βͳ͍ͷͰ%JTL*0͕ൃੜ͢Δ σʔλ૿

Slide 147

Slide 147 text

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

Slide 148

Slide 148 text

࣮ફͰϋϚΔ᠘ %#ͷ໰୊͸๨Εͨࠒʹ΍ͬͯ͘Δ

Slide 149

Slide 149 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ