背景
bulk insertやbulk upsertはLaravelにセットでありますが、一部のカラムだけをbulk updateするってのは、なさそうです。
今回はlaravelBatchというライブラリでbulk updateをやってみました。
環境
PC
機種 : MacBook Pro 2021(M1 Max)
OS : Monterey(12.2.1)
Laravel Sail
PHP version : 8.1.9
Laravel version : 9.25.1
MySQL version : 8.0.31
やってみる
事前準備
今回はデフォルトで存在するusersテーブルを使います。
mysql> show columns from users;
+-------------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------+------+-----+---------+----------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | UNI | NULL | |
| email_verified_at | timestamp | YES | | NULL | |
| password | varchar(255) | NO | | NULL | |
| remember_token | varchar(100) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+-------------------+-----------------+------+-----+---------+----------------+
2レコード突っ込みます。
INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `remember_token`, `created_at`, `updated_at`) VALUES
(1, 'hoge987', 'example@example.com', '2000-10-01 00:00:00', '123', 'str', NULL, NULL),
(2, 'test_user', 'example2@example.com', null, 'str', 're', NULL, NULL);
Eloquentで1レコードずつ更新
比較のためにやってみます。
User::where("id", 1)
->update([
'name' => 'San Diego', 'email_verified_at' => null
]);
User::where("id", 2)
->update([
'name' => 'New York', 'email_verified_at' => '2022-11-21 11:39:20'
]);
SQL文でbulk update
nameとemail_verified_atのみ更新するSQLを書きます。
UPDATE users
SET name =
CASE id
WHEN 1 THEN 'San Diego'
WHEN 2 THEN 'New York'
END,
email_verified_at =
CASE id
WHEN 1 THEN null
WHEN 2 THEN '2022-11-21 11:39:20'
END
WHERE id IN(1, 2);
これを実行するようなコードを書ければ、bulk updateはできそうです。書けないことはないですが、SQL結構ややこしいし、バグ生みそうじゃない？
laravelBatchを使ってbulk update
エンジニアならスクラッチ開発だろ！という意見もあると思いますが、僕は既にあるものは利用したら良いと思う人間です。
READMEにある通りcomposerで入れます。
./vendor/bin/sail composer require mavinoo/laravel-batch
app.phpに追記します。
...
'providers' => [
...
+ Mavinoo\Batch\BatchServiceProvider::class,
],
...
laravelBatchを使ったコードを書いてみます。
※READMEの通りにやると
Non-static method Mavinoo\LaravelBatch\Batch::update() should not be called statically
とエラーになるので、use Mavinoo\Batch\BatchFacade as Batch;を追加します。
use Mavinoo\Batch\BatchFacade as Batch;
...
$userInstance = new User;
$records = [
[
'id' => 1,
'name' => 'San Diego',
'email_verified_at' => null
] ,
[
'id' => 2,
'name' => 'New York',
'email_verified_at' => '2022-11-21 11:39:20'
] ,
];
$index = 'id';
Batch::update($userInstance, $records, $index);
これでbulk updateが出来ました。
さて、これはどのようなSQLを生成しているのでしょうか。さっきの所に以下を追記してログを確認します。
...
+ \DB::enableQueryLog();
Batch::update($userInstance, $value, $index);
+ dd(\DB::getQueryLog());
....
どうやらCASE文を使っているようです。
laravelBatchのソースを読んでみてもCASE文でやってますね。
ついでに10,000件でやってみる
laravelBatchを使うところまでは↑で完了したのですが、｢1行アップデートとbulk updateってどんくらい速度違うんだってばよ!｣ と思い、ついでに色々試してみました。
まずはtruncateでusersテーブルをお掃除して新しくレコードを突っ込んでおきます。
$value = [];
for ($i = 1; $i <= 10000; $i++) {
$value[] = [
'name' => 'before' . $i,
'email' => 'example' . $i . '@example.com',
'email_verified_at' => '2000-01-01 00:00:00',
'password' => 'password' . $i
];
}
DB::table('users')->insert($value);
速度の計測には下記の記事を参考にしました。
Eloquentで1レコードずつ更新した場合のかかる時間
$records = [];
for ($i = 1; $i <= 10000; $i++) {
$records[] = [
'id' => $i,
'name' => 'no' . $i,
'email_verified_at' => '2022-11-21 11:39:20'
];
}
$timeStart = microtime(true);
foreach ($records as $record) {
User::where("id", Arr::get($record, 'id'))
->update([
'name' => Arr::get($record, 'name'),
'email_verified_at' => Arr::get($record, 'email_verified_at'),
]);
}
$timeEnd = microtime(true);
var_dump($timeEnd - $timeStart);
結果はfloat(19.99024200439453)でした。時間かかりますね。
trunsactionを設定してないので、設定してみます。
$records = [];
for ($i = 1; $i <= 10000; $i++) {
$records[] = [
'id' => $i,
'name' => 'no' . $i,
'email_verified_at' => '2022-11-21 11:39:20'
];
}
$timeStart = microtime(true);
+\DB::beginTransaction();
foreach ($records as $record) {
User::where("id", Arr::get($record, 'id'))
->update([
'name' => Arr::get($record, 'name'),
'email_verified_at' => Arr::get($record, 'email_verified_at'),
]);
}
+\DB::commit();
$timeEnd = microtime(true);
var_dump($timeEnd - $timeStart);
結果はfloat(5.771291017532349)で、trunsactionなしよりはかなり早くなってます。
laravelBatchを使ってbulk updateした場合のかかる時間
laravelBatchを使った場合でやってみます。
use Mavinoo\Batch\BatchFacade as Batch;
...
$userInstance = new User;
$records = [];
for ($i = 1; $i <= 10000; $i++) {
$records[] = [
'id' => $i,
'name' => 'no' . $i,
'email_verified_at' => '2022-11-21 11:39:20'
];
}
$index = 'id';
$timeStart = microtime(true);
Batch::update($userInstance, $records, $index);
$timeEnd = microtime(true);
var_dump($timeEnd - $timeStart);
結果はfloat(4.824389934539795)でした。Eloquentで1レコードずつ更新(trunsactionあり)と比べるとさほど大差ないのかも。。。
もっと高速化できないかと思い、array_chunkで100件ずつbulk updateもやってみました。
$userInstance = new User;
$records = [];
for ($i = 1; $i <= 10000; $i++) {
$records[] = [
'id' => $i,
'name' => 'no' . $i,
'email_verified_at' => '2022-11-21 11:39:20'
];
}
$index = 'id';
$timeStart = microtime(true);
foreach (array_chunk($records, 100) as $chunkRecords) {
Batch::update($userInstance, $chunkRecords, $index);
}
$timeEnd = microtime(true);
var_dump($timeEnd - $timeStart);
結果はfloat(0.8196589946746826)となりました。
適切な件数でbulk updateするのが最も早そうです。
パケットの最大サイズに注意
SQL文の長さにもMaxがあるので、適切なSQLを投げる必要があります。
array_chunkで100件ずつbulk updateしたSQLでも中々の長さ(遅そうにしか見えないが高速)。
行ロックに注意
transactionを張って時間のかかるUPDATE文を投げたときに行ロックされる場合があるので、サービスに影響ないように注意しないとですね。
(だからbulk updateってあまりないやらないのだろうか)
Discussion
ありがとうございます！
readme通りにやってうまくいかなかったので助かりました！