ループを使用して1度に大量のSQLの実行は問題でしょうか？

Question

前提・実現したいこと

PHP7とMariaDBを使用してWEBアプリを開発しています。
その中で、約10個ほどのSELECT/INSERT/UPDATEのSQLを数千ループ実行するのですが、
1度にまとめて実行することはDB的に問題無いでしょうか。または例えばループ10回ごとに、
0.1秒のsleepを挟む、等行った方がいいのでしょうか。

内容は以下の様な感じです.

[例]

beginTransaction();
for($i = 0;$i < 3000;$i++)
{
　　select1();
　　select2();
　　insert1();
　　update1();
　　insert2();
　　insert3();
　　update2();
　　update3();
}
commit();

この処理は夜間にスケジュール実行する予定ですが、かなり高負荷をかけてしまって
DBサーバがダウンしてしまう事が心配です。

Answer 1

高付加といっても、夜間のこのプロセスだけが走っているのであれば問題ありません。
sleepを挟むよりも、夜間のうちに確実に終わらせる方が通常は優先度が高いと思います。

時々「休ませる」ことで解決されるのは、温度の問題くらいですが、これは一応は気にしたほうがよいでしようね。サーバーの放熱が適切にできているかということです。ただCPUが高温になるので「休ませる」というのは悪手で、放熱の問題自体を解決すべきかと思います。

外部から呼び出すSQL文の数が多いために非効率になるということはありえます。たとえば、ストアドプロシジャにまとめるなどの方法はとれませんか?

Comments

[colt]

ありがとうございます。サーバはクラウドの為、
放熱などは適切に処理されているかと思います。
ただしプロセスはこの他にも走っており、それらでは同様にSQLを発行しています。
ストアドプロシージャの線で考えてみます。

Answer 2

SQLの中ではinsertは負荷が高い作業になります
仕様上可能であればinsertはバルク処理を検討ください

Comments

[colt]

ありがとうございます。残念ながらInsertするデータだけまとめておく
と言うのが難しい状態です。

[yambejp]

そうですか、insertを複数発行すると、一気に流し込むのだと
何十、何百倍スピードが違うことも多いです
（１～２時間かかる処理が数秒でおわるとか）
運用方法については精査したほうがよいでしょう

Answer 3

ストアドプロシージャにするのが吉でしょう。
MySQL】プロシージャを使ってforeach的なことをしてみる
MySQLのストアドプロシージャを使ってinsertとかdeleteとかをループさせて実行

Comments

[colt]

参考URLありがとうございます

Answer 4

負荷という点では問題はないでしょうが、トランザクションの粒度が気になります。
サンプルですと全件を一つのトランザクションで処理していますが、それで問題はないのでしょうか。ある程度の回数で分割しても構わないものでしょうか。

※途中で失敗した場合に「そこまでの成功分は（ある程度）保証して、リカバリはその後ろから」とするのか、「とにかく全部やり直せるように元に戻す」のか、どちらが業務上適しているのかという話です

Comments

[colt]

できれば途中で失敗した場合に再度実行するだけでいい様にしたい
と考えて、全体を１つのトランザクションで囲みました。
トランザクションを分けて動かす方法も考慮してみます。ありがとうございます。

Answer 5

高い負荷のSQLというのは単独SQLまたは並列実行された場合の話です。
単なるループ処理という事であれば、完了するまでに要する時間が長いという事になるでしょう。

ですのでどちらかというと、SQLの発行回数を減らす事を考えられた方が良いかと思います。

Comments

[colt]

ありがとうございます。できる限り減らした結果、
どうしてもこれだけ必要、という状態でした。。。

[sazi]

ループ内でSQLを発行するような作りになっていませんか？
その場合はループの元になっているSQLに含めるようにすれば、減らすことができます。
基本的にループ処理を前提とせず、段階的に処理するように考えるとループを無くすことができます。

Answer 6

元々入っているデータ量とかindexの貼られかたとか頻度によりますが何回も実行するよりはSQL文をひたすら変数に詰め込んでおいて一度だけ実行する方が総合的な負荷は少ないと思います。

Comments

[退会済みユーザー]

なんとなく、insert into select on DUPLICATE　文が思い浮かんだ

[m.ts10806]

それもできたらなおよしですね。
select1()とかマジックナンバーついたメソッドが大量にある作りも気になりますけど・・
いずれにしても結構な作り直しが発生しますね

[colt]

ありがとうございます。
select1()などはあくまで例であって、実際は全て違います。
selectで取得した値にphpで計算を行って、その結果をinsert、update したりと複雑に行っていたのでいたので、１つのsqlでは厳しい状態でした。

[m.ts10806]

「1つのSQLにまとめる」ではなく「一度に全てのSQLを実行する」という意味の回答です。

[m.ts10806]

とはいうものの、要件に合わないのは確かですね。
ただ、そのあたりの複雑さは要件としては質問に書いておいてもらえると的確な回答も可能になりますので、今後気を付けてみてください。

[colt]

ありがとうございます。以後気を付けます。