Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Sign up for free
Menu
Search
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Pricing
Search
Sign in
Sign up for free
知って欲しいSQLなこと
Search
Sponsored
·
SiteGround - Reliable hosting with speed, security, and support you can count on.
→
soudai sone
PRO
July 31, 2016
Technology
1.9k
4
Share
Embed
Copy iframe code
Copy JS code
Copy link
Start on current slide
知って欲しいSQLなこと
中国地方DB勉強会 in 岡山のLT資料です
soudai sone
PRO
July 31, 2016
Transcript
46 slides
ͬͯͯཉ͍͠SQLͳ͜ͱ தࠃํ%#ษڧձ
What is it? DB͋Δɺಥવ͘ͳΔ
What is it? ͦΜͳʹཱ͔ͪ͏ ͨͪͷޠͰ͋Δ… ※ϑΟΫγϣϯͰ͢
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
ࣗݾհ ໊લɿીࠜɹେʢͦͶɹ͚ͨͱʣ ྸɿ31ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦ ɹɹٕज़తʹLLܥݴޠͱ͔RDB͕͖Ͱ͢
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
ϧʔϓͰINSERT ͦʔౖ͍ܹͩͨ͠ɻ
ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); for ($index = 0; $index
< count($array); $index++) { $this->db->inset($array["{$index}"]) } $this->db->commit();
ϧʔϓͰ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͕ݺΕΔ
ϧʔϓͰ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 ͕ݺΕΔ
ϧʔϓͰINSERT ϧʔΫɺόϧΫΠϯαʔτΛ͑
ϧʔϓͰINSERT INSERT INTO hoge (id,name) values (1,’sone1’), (2,’sone2’), (3,’sone3’), (4,’sone4’),
(5,’sone5’), (6,’sone6’) … # ࠷ۙͷFWͳΒྻ͚ͩ͢Ͱ͍͍ this->db->inset($array);
ϧʔϓͰINSERT ͍Ͱ༰ʹΑͬͯ VQEBUFͳΜͰ͢
ϧʔϓͰ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();
ϧʔϓͰINSERT ϧʔΫɺ.FSHFจ 614&35 Λ͑ ͔͠͠03.ΫΤϦϏϧμ େ͑ͳ͍
ϧʔϓͰINSERT ——PostgreSQL INSERT INTO ςʔϒϧ໊ VALUES ('̍', '̎', ...ʣ ON
CONFLICT ON CONSTRAINT ੍໊ DO UPDATE SET ΧϥϜ̍='', ΧϥϜ̎='', ...; ——MySQL INSERT INTO ςʔϒϧ໊ VALUES ('̍', '̎', …ʣ ON DUPLICATE KEY ※ࣗͰࢦఆ͢Δ͜ͱ͕ग़དྷͳ͍ UPDATE ΧϥϜ̍='', ΧϥϜ̎='', ...;
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
N+1 /
ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); for ($index = 0; $index
< count($array); $index++) { $this->db->inset($array["{$index}"]) } $this->db->commit();
N+1 ϧʔϓͷ͚ͩRVFSZ͛Δ
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();
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&ͳΒճ
N+1 ճͷ61%"5&จʹม͑Δ
N+1 UPDATE users SET pref = CASE pref WHEN ‘Ԭࢁ’
THEN ‘େձ’ WHEN ‘ౡ’ THEN ‘ࢁ’ ELSE pref END WHERE pref IN (‘Ԭࢁ’,’ౡ’)
N+1 $"4&ࣜͳͷͰ ৭Μͳͱ͜ΖͰ͑Δ
N+1 $"4&ࣜͳͷͰ ৭Μͳͱ͜ΖͰ͑Δ 03%&3ɹ#:ͱ͔͑Δ
N+1 Ԡ༻ฤ ͭͷ%#Λൺֱͯ͠61%"5&
N+1 JE OBNF BHF TPOF UBLFUPNP
TPVEBJ VTFS@JE CJSUIEBZ QSFG ౡ ࢁ େձ VTFST VTFS@QSPQFSUJFT
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
N+1 JE OBNF BHF TPOF UBLFUPNP ϋλν
TPVEBJ VTFS@JE CJSUIEBZ QSFG ౡ ࢁ େձ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
ςʔϒϧεΩϟϯΛԥΔ ϧʔΫɺۚͷؙΛ͑ʢਅإ
ςʔϒϧεΩϟϯΛԥΔ .Z42-ͳΒ44% 1PTUHSF42-ͳΒϝϞϦΛ ·ͣ૿͢
ςʔϒϧεΩϟϯΛԥΔ 1PTUHSF42-͔Β ςʔϒϧεΩϟϯΛ ύϥϨϧΫΤϦʢฒྻॲཧʣग़དྷΔ
ςʔϒϧεΩϟϯΛԥΔ 1PTUHSF42-͔Β ςʔϒϧεΩϟϯΛ ύϥϨϧΫΤϦʢฒྻॲཧʣग़དྷΔ $16ͷΛ૿ͯ͠ԥΕΔΑ͏ʹͳΔ
ςʔϒϧεΩϟϯΛԥΔ ͦΕͰ͍ͳΒ ύʔςʔγϣϯ͔γϟʔσΟϯά
ςʔϒϧεΩϟϯΛԥΔ ͦΕ͕͍࣌͠ ˞ྫ͑σʔλϋεΣΞ ʢ%)8ʣ
ςʔϒϧεΩϟϯΛԥΔ ͦ͏͍͏࣌༻%#Λങ͓͏ ʢ˓˓˓˓ສ͘Β͍͢Δͭʣ
ςʔϒϧεΩϟϯΛԥΔ ςʔϒϧεΩϟϯ͠ͳ͍ઃܭ͕େࣄ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϧʔϓͰINSERT ̏ɹN+1 ̐ɹςʔϒϧεΩϟϯΛԥΔ ̑ɹ·ͱΊ
·ͱΊ ษڧͯ͘͠ΕɺͨͷΉ
·ͱΊ +16(ϝϯόʔΛืूͯ͠·͢
·ͱΊ ϙδγϣϯਓΛҭͯΔ
·ͱΊ ࣍ظࢧ෦܅ͩʂʂ
͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ
More Decks by soudai sone
See All by soudai sone
ビジネスを止めない技術的負債の返済のための戦略とその手法 - 技術的負債と向き合う / Complexity and Simplicity
soudai
PRO
3
790
AIを活用するために決めた "やらないこと" - 価値に注目する / Not betting on AI
soudai
PRO
3
770
目の前の楽しいが人生を変える - コミュニティの螺旋の歩き方と楽しむコツ / change your life
soudai
PRO
5
850
医療の現場を変革に挑戦した半年間の軌跡 - PythonとAIで現場を変える / From Code to Care
soudai
PRO
1
790
形骸化しない社内勉強会の取り組み - オーナーシップの作り方 / In-house study session
soudai
PRO
1
520
Djangoユーザが知っ得なPostgreSQL機能 - 設計の選択肢を増やす / Djang-use-PostgreSQL
soudai
PRO
1
370
AI時代における具体と抽象の往復 - 日常にチャンスがある / Moving Between the Concrete
soudai
PRO
9
4.8k
制約を設計する - 非決定性との境界線 / Designing constraints
soudai
PRO
6
4.9k
APMの世界から見るOpenTelemetryのTraceの世界 / OpenTelemetry in the Java
soudai
PRO
2
1k
Other Decks in Technology
See All in Technology
マネジメントされる側の仕事は「文句」を言うこと
riyaamemiya
7
5.9k
変更差分を読むために環境を整備しよう
pipepasta
1
480
Platform Engineering をベースとした Agentic Software Factory の実践
110y
1
420
認知負荷の減らし方 ~開発にも日常業務にも活かせるノウハウ~
minorun365
PRO
12
8.3k
MCPでネットワーク障害調査を自動化する前に直すべき3つのこと
yama3133
1
650
gradle-best-practices 使ってみた
mikanichinose
0
200
Gov-JAWS#9 統合監視機能のマネージドサービス化+AIによる障害時自動調査
fj_yoshimura_kosuke
0
220
目を覚ませ!僕らの認証情報が何者かに侵略されてるぞ
tkito
2
480
3文字略語に騙されない! 世界一わかりやすい『EDR』超入門
kamadamakoto
1
280
IoTのデータ保存をSREの観点で考える
grimoh
1
110
Context parameters で作る!はじめての型クラス
kworkdev
PRO
1
160
業務の傍らで進める改善ループエンジニアリング
bengo4com
1
220
Featured
See All Featured
Writing Fast Ruby
sferik
630
63k
First, design no harm
axbom
PRO
2
1.4k
技術選定の審美眼(2025年版) / Understanding the Spiral of Technologies 2025 edition
twada
PRO
121
130k
Site-Speed That Sticks
csswizardry
13
1.6k
Hiding What from Whom? A Critical Review of the History of Programming languages for Music
tomoyanonymous
3
1.5k
Color Theory Basics | Prateek | Gurzu
gurzu
1
530
From π to Pie charts
rasagy
1
450
Building AI with AI
inesmontani
PRO
1
1.4k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
141
35k
Building Applications with DynamoDB
mza
96
7.3k
WCS-LA-2024
lcolladotor
0
1k
Test your architecture with Archunit
thirion
2
2.5k