知って欲しいSQLなこと

中国地方DB勉強会 in 岡山のLT資料です

Transcript

1. ஌ͬͯͯཉ͍͠SQLͳ͜ͱ தࠃ஍ํ%#ษڧձ
2. What is it? DB͸͋Δ೔ɺಥવ஗͘ͳΔ
3. What is it? ͦΜͳ໰୊ʹཱͪ޲͔͏ ׽ͨͪͷ෺ޠͰ͋Δ… ※ϑΟΫγϣϯͰ͢
4. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
5. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
6. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ31ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
7. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
8. ϧʔϓͰINSERT ͦʔ͍ͩ͸ౖܹͨ͠ɻ
9. ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); for ($index = 0; $index < count($array); $index++) { $this->db->inset($array["{$index}"]) } $this->db->commit();
10. ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); for ($index = 0; $index < count($array); $index++) { $this->db->inset($array["{$index}"]) } $this->db->commit(); ϧʔϓͷ਺͚ͩ*/4&35͕ݺ͹ΕΔ
11. ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); for ($index = 0; $index < count($array); $index++) { $this->db->inset($array["{$index}"]) } $this->db->commit(); ϧʔϓͷ਺͚ͩ*/4&35͕ݺ͹ΕΔ ϧʔϓͷ਺͚ͩDPVOU ͕ݺ͹ΕΔ
12. ϧʔϓͰINSERT ϧʔΫɺόϧΫΠϯαʔτΛ࢖͑
13. ϧʔϓͰINSERT INSERT INTO hoge (id,name) values (1,’sone1’), (2,’sone2’), (3,’sone3’), (4,’sone4’), (5,’sone5’), (6,’sone6’) … # ࠷ۙͷFWͳΒ഑ྻ౉͚ͩ͢Ͱ͍͍ this->db->inset($array);
14. ϧʔϓͰINSERT ͍΍Ͱ΋಺༰ʹΑͬͯ͸
    VQEBUFͳΜͰ͢
15. ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); $count = count($array); for ($index = 0; $index < $count; $index++) { if (empty($array[“{$index}”][“id”])) { $this->db->inset($array["{$index}"]) } else { $this->db->update($array["{$index}"]) } } $this->db->commit();
16. ϧʔϓͰINSERT ϧʔΫɺ.FSHFจ 614&35 Λ࢖͑
    ͔͠͠03.΍ΫΤϦϏϧμ͸
    େ఍࢖͑ͳ͍
17. ϧʔϓͰINSERT ——PostgreSQL INSERT INTO ςʔϒϧ໊ VALUES ('஋̍', '஋̎', ...ʣ ON CONFLICT ON CONSTRAINT ੍໿໊ DO UPDATE SET ΧϥϜ̍='஋', ΧϥϜ̎='஋', ...; ——MySQL INSERT INTO ςʔϒϧ໊ VALUES ('஋̍', '஋̎', …ʣ ON DUPLICATE KEY ※ࣗ෼Ͱࢦఆ͢Δ͜ͱ͕ग़དྷͳ͍ UPDATE ΧϥϜ̍='஋', ΧϥϜ̎='஋', ...;
18. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
19. N+1 / ໰୊
20. ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); for ($index = 0; $index < count($array); $index++) { $this->db->inset($array["{$index}"]) } $this->db->commit();
21. N+1 ϧʔϓͷ਺͚ͩRVFSZ౤͛Δ
22. N+1 $users = $this->db->users->get(); $this->db->begin(); foreach ($users as $user_id => $user) { if ($user[“pref”] == ‘Ԭࢁ’) { $user[“pref”] = ‘େ౎ձ’ } elseif ($user[“pref”] == ‘޿ౡ’){ $user[“pref”] = ‘෱ࢁ’ } $this->db->update($user); } $this->db->commit();
23. N+1 $users = $this->db->users->get(); $this->db->begin(); foreach ($users as $user_id => $user) { if ($user[“pref”] == ‘Ԭࢁ’) { $user[“pref”] = ‘େ౎ձ’ } elseif ($user[“pref”] == ‘޿ౡ’){ $user[“pref”] = ‘෱ࢁ’ } $this->db->update($user); } $this->db->commit(); ͦ΋ͦ΋8)&3&۟ͰQSFGࢦఆͯ͠ 61%"5&ͳΒճ
24. N+1 ճͷ61%"5&จʹม͑Δ
25. N+1 UPDATE users SET pref = CASE pref WHEN ‘Ԭࢁ’ THEN ‘େ౎ձ’ WHEN ‘޿ౡ’ THEN ‘෱ࢁ’ ELSE pref END WHERE pref IN (‘Ԭࢁ’,’޿ౡ’)
26. N+1 $"4&͸ࣜͳͷͰ
    ৭Μͳͱ͜ΖͰ࢖͑Δ
27. N+1 $"4&͸ࣜͳͷͰ
    ৭Μͳͱ͜ΖͰ࢖͑Δ 03%&3ɹ#:ͱ͔΋࢖͑Δ
28. N+1 Ԡ༻ฤ
    ͭͷ%#Λൺֱͯ͠61%"5&
29. N+1 JE OBNF BHF  TPOF   UBLFUPNP   TPVEBJ  VTFS@JE CJSUIEBZ QSFG   ޿ౡ   ෱ࢁ   େ౎ձ VTFST VTFS@QSPQFSUJFT 
30. N+1 UPDATE users AS u, (SELECT users.id AS id, CASE WHEN user_properties.birthday > ‘1996-07-30’ THEN ‘ϋλν’ ELSE age END AS age FROM users INNER JOIN user_properties ON user.id = user_properties.user_id AND user_properties.pref IN (‘෱ࢁ’,’ͳ͝΍’,’େ౎ձ’)) AS tmp SET u.age = tmp.age WHERE u.id = tmp.id
31. N+1 JE OBNF BHF  TPOF   UBLFUPNP ϋλν  TPVEBJ  VTFS@JE CJSUIEBZ QSFG   ޿ౡ   ෱ࢁ   େ౎ձ
32. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
33. ςʔϒϧεΩϟϯΛԥΔ ϧʔΫɺۚͷ஄ؙΛ࢖͑ʢਅإ
34. ςʔϒϧεΩϟϯΛԥΔ .Z42-ͳΒ44%
    1PTUHSF42-ͳΒϝϞϦΛ
    ·ͣ૿΍͢
35. ςʔϒϧεΩϟϯΛԥΔ 1PTUHSF42-
    ͔Β͸
    ςʔϒϧεΩϟϯΛ
    ύϥϨϧΫΤϦʢฒྻॲཧʣग़དྷΔ
36. ςʔϒϧεΩϟϯΛԥΔ 1PTUHSF42-
    ͔Β͸
    ςʔϒϧεΩϟϯΛ
    ύϥϨϧΫΤϦʢฒྻॲཧʣग़དྷΔ $16ͷ਺Λ૿΍ͯ͠ԥΕΔΑ͏ʹͳΔ
37. ςʔϒϧεΩϟϯΛԥΔ ͦΕͰ΋஗͍ͳΒ
    ύʔςʔγϣϯ͔γϟʔσΟϯά
38. ςʔϒϧεΩϟϯΛԥΔ ͦΕ͕೉͍࣌͠
    ˞ྫ͑͹σʔλϋ΢ε΢ΣΞ ʢ%)8ʣ
39. ςʔϒϧεΩϟϯΛԥΔ ͦ͏͍͏࣌͸঎༻%#Λങ͓͏
    ʢ˓˓˓˓ສ͘Β͍͢Δ΍ͭʣ
40. ςʔϒϧεΩϟϯΛԥΔ ςʔϒϧεΩϟϯ͠ͳ͍ઃܭ͕େࣄ
41. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
42. ·ͱΊ ษڧͯ͘͠ΕɺͨͷΉ
43. ·ͱΊ +16(͸ϝϯόʔΛืूͯ͠·͢
44. ·ͱΊ ϙδγϣϯ͸ਓΛҭͯΔ
45. ·ͱΊ ࣍ظࢧ෦௕͸܅ͩʂʂ
46. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ