このRDBについて私は驚くべき闇を見つけたがそこを発表するにはネットは怖すぎる

YAP(achimon)C::Asia Hachioji 2016midの資料です。

Transcript

1. ͜ͷRDBʹ͍ͭͯ
   ࢲ͸ڻ͘΂͖ҋΛݟ͚͕ͭͨ
   ͦ͜Λൃද͢Δʹ͸ωοτ͸ා͗͢Δ
   :"1$"TJB)BDIJPKJ
   

   View Slide

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

   View Slide

3. What is it?
   ͭ·Γɾɾɾ
   

   View Slide

4. What is it?
   ҋ΋ଟ͍
   

   View Slide

5. What is it?
   ͦΜͳࢲͷ ۪ஒ ܦݧஊΛ͓࿩͠·͢
   

   View Slide

6. ͋͐͡Μͩ
   ̍ɹࣗݾ঺հ
   ̎ɹMySQL
   ̏ɹPostgreSQL
   ̐ɹΞϯνύλʔϯ
   ̑ɹ·ͱΊ
   

   View Slide

7. ͋͐͡Μͩ
   ̍ɹࣗݾ঺հ
   ̎ɹMySQL
   ̏ɹPostgreSQL
   ̐ɹΞϯνύλʔϯ
   ̑ɹ·ͱΊ
   

   View Slide

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

   View Slide

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

   View Slide

10. What is it?
    d
    

    View Slide

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

    View Slide

12. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹMySQL
    ̏ɹPostgreSQL
    ̐ɹΞϯνύλʔϯ
    ̑ɹ·ͱΊ
    

    View Slide

13. MySQL
    ͦʔ͍ͩ͸ౖܹͨ͠ɻ
    

    View Slide

14. MySQL
    ͦʔ͍ͩ͸.Z42-͸Θ͔Β͵ɺ
    ͚ΕͲ΋ܕʹ͸ਓҰ൪හײͰ͋ͬͨ
    

    View Slide

15. ܕม׵
    -- ςʔϒϧͷߏ଄ `hoge`
    CREATE TABLE IF NOT EXISTS `hoge` (
    `id` int(11) NOT NULL,
    `val` varchar(255) NOT NULL
    );
    mysql> SELECT * FROM hoge;
    +----+--------+
    | id | val |
    +----+--------+
    | 1 | ONE |
    | 2 | 2 |
    | 3 | Three3 |
    | 4 | 4Four |
    +----+--------+
    4 rows in set (0,00 sec)
    

    View Slide

16. ܕม׵
    mysql> SELECT * FROM hoge WHERE val = "0";
    Empty set (0,00 sec)
    mysql> SELECT * FROM hoge WHERE val = 2;
    +----+-----+
    | id | val |
    +----+-----+
    | 2 | 2 |
    +----+-----+
    1 row in set, 3 warnings (0,00 sec)
    

    View Slide

17. ܕม׵
    mysql> SELECT * FROM hoge WHERE val = 4;
    +----+-------+
    | id | val |
    +----+-------+
    | 4 | 4Four |
    +----+-------+
    1 row in set, 3 warnings (0,00 sec)
    mysql> SELECT * FROM hoge WHERE val = 3;
    Empty set, 3 warnings (0,00 sec)
    

    View Slide

18. ܕม׵
    -- ςʔϒϧͷߏ଄ `hoge`
    CREATE TABLE IF NOT EXISTS `hoge` (
    `id` int(11) NOT NULL,
    `val` varchar(255) NOT NULL
    );
    mysql> SELECT * FROM hoge;
    +----+--------+
    | id | val |
    +----+--------+
    | 1 | ONE |
    | 2 | 2 |
    | 3 | Three3 |
    | 4 | 4Four |
    +----+--------+
    4 rows in set (0,00 sec)
    

    View Slide

19. ܕม׵
    mysql> SELECT * FROM hoge WHERE val = 4;
    +----+-------+
    | id | val |
    +----+-------+
    | 4 | 4Four |
    +----+-------+
    1 row in set, 3 warnings (0,00 sec)
    mysql> SELECT * FROM hoge WHERE val = 3;
    Empty set, 3 warnings (0,00 sec)
    ઌ಄͕਺ࣈ
    ຤ඌ͕਺ࣈ
    

    View Slide

20. MySQL
    8IZ .Z42-1FPQMFʂʂ
    

    View Slide

21. MySQL
    ࠔͬͨΒ!ZPLV͞Μʹฉ͘
    

    View Slide

22. MySQL
    ԶʮόάͰ͢ΑͶʂʯ
    

    View Slide

23. MySQL
    ԶʮόάͰ͢ΑͶʂʯ
    ˣ
    !ZPLV͞Μʮ࢓༷Ͱ͢ʈʈʯ
    

    View Slide

24. MySQL
    WBMͷ৔߹ɺΛจࣈྻʹΩϟετ͢ΔͷͰ͸ͳ͘ɺ
    WBMΛ%06#-&ʹΩϟετͯ͠ͱൺֱ͍ͯ͠·͢ɻ
    ӳࣈͰ࢝·Δ453*/(͸%06#-&ʹΩϟετͰ͖ͣʹΩϟετޙͷ஋͕ʹͳ
    Γ·͕͢ɺBCDͷΑ͏ͳจࣈྻ͸ʮͰ͖Δͱ͜Ζ·ͰΩϟετ͢ΔʯͷͰ
    Ωϟετޙͷ஋͸ʹͳΓ·͢ɻ
    ͭ·ΓͰ͢
    ͬͯ͜ͱͰɺʮ਺ࣈͰ࢝·Βͳ͍WBMʯͷ΋ͷ͚͕ͩݕࡧʹҾ͔͔ͬΔΘ͚Ͱ͢ɻ
    ͦͯ͠ٯʹ਺ࣈ͔Β࢝·Βͳ͍จࣈྻ͸8)&3&WBMʹ֘౰͢ΔΘ͚Ͱ͢ɻ
    

    View Slide

25. MySQL
    ೔෇ܕ
    

    View Slide

26. MySQL
    %"5&5*.&ܕ͕4+*4ͩͱ
    */%&9͕ޮ͔ͳ͍Ṗ
    

    View Slide

27. MySQL
    %"5&5*.&ܕ͕4+*4ͩͱ
    */%&9͕ޮ͔ͳ͍Ṗ
    ˣ
    ·͊65'࢖͑
    

    View Slide

28. MySQL
    .Z42-ҎԼͩͱ%"5&5*.&ܕ
    ͷσϑΥϧτ஋ʹ
    $633&/5@%"5&5*.&࢖͑ͳ͍
    

    View Slide

29. MySQL
    

    View Slide

30. MySQL
    

    View Slide

31. MySQL
    ͦΜͳ࢓༷ཁΒͳ͍ͩΖ͆
    

    View Slide

32. MySQL
    

    View Slide

33. MySQL
    ͦΜͳ࢓༷ඞཁͩͬͨʜ
    

    View Slide

34. MySQL
    ਏ͍ʢ͔Β͍
    

    View Slide

35. MySQL
    1PTUHSF42-ͱҧ͏ͱ͜Ζ
    

    View Slide

36. MySQL
    bTPOF`b40/&`໰୊
    

    View Slide

37. MySQL
    bTPOF`b40/&`໰୊
    σϑΥϧτͰ͸۠ผ͠ͳ͍
    όΠφϦܕΛࢦఆͨ͠Βग़དྷΔ
    

    View Slide

38. MySQL
    $)&$,੍໿ͱ*/%&9ͷ߱ॱ
    

    View Slide

39. MySQL
    $)&$,੍໿ͱ*/%&9ͷ߱ॱ
    ˣ
    ͦΜͳ΋ͷ͸ແ͍
    

    View Slide

40. MySQL
    $)&$,੍໿ͱ*/%&9ͷ߱ॱ
    ˣ
    ͦΜͳ΋ͷ͸ແ͍
    Ͱ΋ΫΤϦ͸௨Δ
    

    View Slide

41. MySQL
    ·͊ͦΜͳࡉ͔͍͜ͱ͸ྑ͍
    

    View Slide

42. MySQL
    ֆจࣈ
    

    View Slide

43. MySQL
    ໰୊
    

    View Slide

44. MySQL
    

    View Slide

45. MySQL
    ͦ΋ͦ΋ֆจࣈ੾ΓऔΒΕ໰୊
    

    View Slide

46. MySQL
    VOJDPEF@DJͳ৔߹
    ʢྫ͑͹VUG@HFOFSBM@DJͷ৔߹ʣ
    

    View Slide

47. MySQL
    ֆจࣈΛؚΊͨҎ߱ͷจࣈ͕
    શ෦ࣺͯΒΕͯอଘ͞Ε·͢
    

    View Slide

48. MySQL
    ରࡦͱͯ͠͸
    VUGNC@CJOΛ࢖͏
    

    View Slide

49. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹMySQL
    ̏ɹPostgreSQL
    ̐ɹΞϯνύλʔϯ
    ̑ɹ·ͱΊ
    

    View Slide

50. PostgreSQL
    QHBENJO
    

    View Slide

51. PostgreSQL
    

    View Slide

52. PostgreSQL
    

    View Slide

53. PostgreSQL
    

    View Slide

54. PostgreSQL
    

    View Slide

55. PostgreSQL
    Ͱ΋ෆຬͷ੠Λฉ͔ͳ͍
    

    View Slide

56. PostgreSQL
    ΞϯέʔτͱͬͯΈͨ
    

    View Slide

57. PostgreSQL
    

    View Slide

58. PostgreSQL
    

    View Slide

59. PostgreSQL
    গ਺೿ͩͬͨ
    

    View Slide

60. PostgreSQL
    ࣾ಺ͷ੠
    ˣ
    QIQ.Z"ENJOͰ͢Α
    

    View Slide

61. PostgreSQL
    ࣾ಺ͷ੠
    ˣ
    ۀ຿தʹ5XJUUFSͷΞϯέʔτʹ
    ౴͑ΕΔਓ͕গ਺೿Ͱ͢Αɻ
    

    View Slide

62. PostgreSQL
    ਖ਼࿦ͩͬͨ
    

    View Slide

63. PostgreSQL
    ϩέʔϧ໰୊
    

    View Slide

64. PostgreSQL
    ϩέʔϧ໰୊
    σϑΥϧτ͸04ͷϩέʔϧΛར༻͢Δɻ
    ͦͷͨΊιʔτ͕૝ఆͱมΘΔ
    ˠϩέʔϧʹ$Λࢦఆ͢Δඞཁ͕͋Δ
    

    View Slide

65. PostgreSQL
    04ʹΑͬͯίϚϯυ͕ҧ͏
    

    View Slide

66. PostgreSQL
    04ʹΑͬͯίϚϯυ͕ҧ͏
    ͪΐͬͱͨ͠ύονΛ౰ͯΔͱ͖ʹࠔΔ
    ಛʹ"OTJCMF͕ࠔΔ
    

    View Slide

67. PostgreSQL
    ϚςϏϡʔͷޭࡑ
    

    View Slide

68. PostgreSQL
    ͩͱϩοΫ͕ΩπΠ
    Ͱͪΐͬͱྑ͘ͳ͚ͬͨͲ
    
    

    View Slide

69. PostgreSQL
    ϚςϏϡʔͷࠩ෼ߋ৽ͳͲͳ͍
    

    View Slide

70. PostgreSQL
    ࣗಈߋ৽΋ͳ͍
    

    View Slide

71. PostgreSQL
    !ZPLV
    ʮͦΕී௨ʹςʔϒϧ࡞Ε͹͍͍͡ΌΜʯ
    

    View Slide

72. PostgreSQL
    ͙͎ਖ਼࿦
    

    View Slide

73. PostgreSQL
    ؂ࠪϩά͸ແ͍
    

    View Slide

74. PostgreSQL
    Ϛϧνϓϩηεͷͤ͘ʹόοΫΤϯ
    υϓϩηε͕མͪͨΒશ෦མͪΔ
    

    View Slide

75. PostgreSQL
    44%ͳͷʹ஗͍
    

    View Slide

76. PostgreSQL
    'SFF[Fॲཧ
    7"$66.ͷࡍʹ
    9*%ͷ࠶ઃఆͷͨΊʹ'6--4$"/͕૸Δ
    

    View Slide

77. PostgreSQL
    ͦ΋ͦ΋9*%͕CJUͳͷ͕ฅ
    

    View Slide

78. PostgreSQL
    ϙετ0SBDMF%#ʢসʣ
    

    View Slide

79. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹMySQL
    ̏ɹPostgreSQL
    ̐ɹΞϯνύλʔϯ
    ̑ɹ·ͱΊ
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

83. Ξϯνύλʔϯ
    ඇਖ਼نԽ
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

87. Ξϯνύλʔϯ
    ֎෦ΩʔϨεઃܭ
    ਖ਼نԽ΋֎෦Ωʔ੍໿΋
    σʔλΛकΔͨΊʹ͋Δʂʂ
    

    View Slide

88. Ξϯνύλʔϯ
    ͦ΋ͦ΋͓લΒ42-ษڧ͠Ζ
    

    View Slide

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

    View Slide

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

    View Slide

91. ࣮ફͰϋϚΔ᠘
    w ूܭର৅͕େ͖͘ͳͬͯूܭ࣌ʹϝϞϦʹ৐Βͳ͍

    ˠ5FNQPSBSZΛ%JTLʹు͘ͷͰॏ͘ͳΔ
    w */%&9͕ແ͘5"#-&εΩϟϯΛ͍ͯͨ͠

    ˠςετ࣌͸σʔλ͕খ͔ͬͨ͞ͷͰ໰୊ʹؾ͔ͮͳ͔ͬͨ
    w ϓϩάϥϜ͕ϧʔϓͷ਺͚ͩΫΤϦΛ౤͍͛ͯͨ

    ˠߦ਺͕૿͑Δͱϧʔϓճ਺͕૿͑ͯॏ͘ͳΔ
    w ߋ৽͕ܹ͘͠*/%&9ͷஅยԽ͕ൃੜͨ͠

    ˠ61%"5&΍%&-&5&ʹΑͬͯ*/%&9͕அยԽͯ͠஗͘ͳΔ
    

    View Slide

92. ࣮ફͰϋϚΔ᠘
    w ूܭର৅͕େ͖͘ͳͬͯूܭ࣌ʹϝϞϦʹ৐Βͳ͍

    ˠ5FNQPSBSZΛ%JTLʹు͘ͷͰॏ͘ͳΔ
    w */%&9͕ແ͘5"#-&εΩϟϯΛ͍ͯͨ͠

    ˠςετ࣌͸σʔλ͕খ͔ͬͨ͞ͷͰ໰୊ʹؾ͔ͮͳ͔ͬͨ
    w ϓϩάϥϜ͕ϧʔϓͷ਺͚ͩΫΤϦΛ౤͍͛ͯͨ

    ˠߦ਺͕૿͑Δͱϧʔϓճ਺͕૿͑ͯॏ͘ͳΔ
    w ߋ৽͕ܹ͘͠*/%&9ͷஅยԽ͕ൃੜͨ͠

    ˠ61%"5&΍%&-&5&ʹΑͬͯ*/%&9͕அยԽͯ͠஗͘ͳΔ
    ࣮ߦܭըΛ
    ݟΕΔΑ͏ʹͳΖ͏
    

    View Slide

93. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹMySQL
    ̏ɹPostgreSQL
    ̐ɹΞϯνύλʔϯ
    ̑ɹ·ͱΊ
    

    View Slide

94. ·ͱΊ
    %#ͷ໰୊͸๨Εͨࠒʹ΍ͬͯ͘Δ
    

    View Slide

95. ·ͱΊ
    

    View Slide

96. ·ͱΊ
    3%#ͷ஌ࣝ͸ण໋͕௕͍
    

    View Slide

97. ·ͱΊ
    3%#ͷ஌ࣝ͸ण໋͕௕͍
    ˣ
    ֮͑Ε͹࢓ࣄͰ௕͍ؒ໾ʹཱͭ
    

    View Slide

98. ·ͱΊ
    3%#ͷ஌ࣝ͸ण໋͕௕͍
    ˣ
    ֮͑Ε͹࢓ࣄͰ௕͍ؒ໾ʹཱͭ
    ͔ͩΒͦ͜एऀ΍ঁੑʹੋඇڵຯΛ࣋ͬͯཉ͍͠
    

    View Slide

99. ·ͱΊ
    %#ͷ໰୊͸ΫϦςΟΧϧ
    

    View Slide

100. ·ͱΊ
     σʔλͷࢮ͸αʔϏεͷࢮ
     

     View Slide

101. ·ͱΊ
     %#ͷ໰୊͸ΫϦςΟΧϧ
     ˣ
     ͭ·Γղܾ͢Ε͹ӳ༤
     

     View Slide

102. ·ͱΊ
     ۪ऀ͸ܦݧʹֶͿ
     ݡऀ͸աڈʹֶͿ
     

     View Slide

103. ·ͱΊ
     3%#͸ྺ࢙͕௕͍
     

     View Slide

104. ·ͱΊ
     3%#͸ྺ࢙͕௕͍
     ˣ
     ৭Μͳܦݧஊ͔Βֶ΂Δ
     

     View Slide

105. ·ͱΊ
     ΋ͬͱΈΜͳࣦഊஊΛڞ༗͠Α͏ʂ
     

     View Slide

106. ·ͱΊ
     3%#͸͍͍ͧɻ
     

     View Slide

107. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ
     

     View Slide