30
2019

Laravelのcursorとchunkの違いとバッファクエリの対処法

CATEGORYPHP
Laravelでデータを小分けに取得するcursorchunkについての話。前回の記事でどっちでもいいから使えば的に書いたりと、自分も両者を使い方が違うだけで中身同じでしょとか思ってたんだけど、よくよく調べてみたら結構差分あったのでちょっとまとめる。なおLaravelは6.x。

初めに結論というか、両者の違いを表にまとめる。

cursorchunk
処理単位1件ずつ指定した件数まとめて
戻り値LazyCollectioncallbackでCollection
SQLSELECTを1度だけ実行
(結果を1行ずつ読み込む)
SELECTを複数回実行
(OFFSET/LIMITを使って繰り返し)
with使用不可可能
ソート不問Eloquentモデル: idでORDER BYされる
生クエリ: ORDER BY句必須
データ不整合の可能性
バッファクエリの影響

見てわかる通り、使い方が違うのも勿論だけど、内部的な動作も全く異なるし、お互い何かしら制限を抱えている。なので、実際に使う上では、用途に合わせて使い分ける必要がある。

curosr

まずcursorの方だが、戻り値がLazyCollectionで返ってくる(Laravel5の頃はIterator)ので、使う側からすると普通のgetと同じ用に使えて非常に使い易い。ただ、Eloquentのwithは使えない(エラーにはならないが取得もされない)ので、リレーションが欲しい場合は自分で別途loadとかする必要がある。

が、他に重大な問題が一つ。MySQLのデフォルトではバッファクエリにより、ほとんどメモリが節約されないのである(他のDBでどうなるかは未確認)。せっかく1件ずつ読み込んでメモリを節約しようとしているにも関わらず、PDO等のDBライブラリが最初に全件バッファに読み込んでしまいメモリを大量消費してしまう。一度に全部インスタンスにされるよりマシではあるが、これではcursorの存在意義の大半が失われてしまう。

後述のようにバッファクエリをOFFにすることは出来るが、そうすると今度は、cursorの最後に達するまで同じコネクションで別のSQLを投げられないという問題が発生してしまう。これも対策はあるのだが、知らずに使うとハマるだろう(ハマった)。

chunk/chunkById

次いでchunkの方だが、こちらはようするに自動でページングしながら全件取得してくれるメソッドである。cursorと異なり、内部的には何回もSELECTが発行される。なので個別のループ自体は普通の検索と同じように使える。

が、こちらもその仕組み上面倒な点が。まず一つ目、必ずソートされていなければならない。というかそれだけならいいのだが、Eloquentで使うと勝手にorderByの先頭にidが足されてしまう(後述のchunkByIdじゃなくてchunkでも!)。これは普通に困る。Eloquentじゃなくて生クエリでやれば平気ではあるが、面倒くさい。

次に二つ目。処理が1 SQLで完結しないので、裏で誰かが更新してると、途中で結果がずれる可能性がある。これを防止するために最初はid=100まで次はid=200まで…って感じに絞り込むchunkByIdというメソッドもあるが、そのためにはID順でソートが必須なので、ID順でソートできない状況じゃ使用できない。DBのトランザクションレベルによっては、トランザクション掛けるだけで一貫性を保てるケースもあるけど、普通の設定だと出来ないことの方が多い筈なので難しい。

cursorが(致命的な問題さえ何とかすれば)割と汎用的に使えるのに対して、chunkはデータが更新されないとか、id順にソートしてよいとか、そういう仕様的にOKな状況じゃないと使い難いイメージ。

バッファクエリの対処法

ということで、cursorを使えるようにするために、PDOでバッファクエリをOFFにする方法も書いておく(PDO以外は未調査)。同じ問題に遭遇した方の記事があったのでそちらを参考にした。
全部変える場合は、↑で紹介されている通り config/database.php で options に PDO::MYSQL_ATTR_USE_BUFFERED_QUERY を設定してやればよい。

次に、途中で変える場合。こんな感じに設定を変更して再接続してやればOK。
function disableBufferedQuery() : void
{
$conn = \DB::connection();
$name = $conn->getName();
$options = \Config::get("database.connections.{$name}.options", []);
$options[\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = false;
\Config::set("database.connections.{$name}.options", $options);
$conn->reconnect();
}
ただ、上二つの対応だと、結果を読み込みながらDBに保存する、的な処理ではさっきも言ったようにcursorが終わっていないとしてエラーになってしまう。そこで個人的におススメなのが、バッファクエリをOFFにしたコネクションをもう一つ作って、cursorはそちらで実行する方法。
function unbufferedConnection() : Connection
{
$conn = \DB::connection();
$name = $conn->getName();
$config = \Config::get("database.connections.{$name}");
$config['options'][\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = false;

static $no = 0;
$newname = "copied_{$name}_" . ++$no;

\Config::set("database.connections.{$newname}", $config);
return \DB::connection($newname);
}

// 使用例
$i = 0;
foreach (Users::on(unbufferedConnection())->orderBy('score', 'desc')->cursor() as $user) {
UserRanking::create(['user_id' => $user->id, 'rank' => ++$i, 'score' => $user->score]);
}
単純に database.php に別名でoptionsだけ変えた設定を追加してもいいが、プログラム側でもこんな風に動的に接続先を作ることは出来るので、こうやってもう一つコネクションを作って使い分けると楽。
(だいぶ無理やりな感じだけど、Laravelで普通にもう一つConnectionを作るやり方は見つからなかった…orz)


以上、cursor/chunkを使っとけば大丈夫だろうと思ったら予想外にハマってしまったので、参考までに。
スポンサーサイト



Tag: PHP Laravel 性能問題

0 Comments

Leave a comment