🙌

laravelBatchを使ってbulk updateする

2022/11/23に公開1

背景

bulk insertやbulk upsertはLaravelにセットでありますが、一部のカラムだけをbulk updateするってのは、なさそうです。

今回はlaravelBatchというライブラリでbulk updateをやってみました。

https://github.com/mavinoo/laravelBatch

環境

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レコードずつ更新

比較のためにやってみます。

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を書きます。

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

エンジニアならスクラッチ開発だろ!という意見もあると思いますが、僕は既にあるものは利用したら良いと思う人間です。

https://github.com/mavinoo/laravelBatch

READMEにある通りcomposerで入れます。

./vendor/bin/sail composer require mavinoo/laravel-batch

app.phpに追記します。

config/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;を追加します。

laravelBatchを使ってbulk update
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文でやってますね。

https://github.com/mavinoo/laravelBatch/blob/master/src/Batch.php#L55-L138

ついでに10,000件でやってみる

laravelBatchを使うところまでは↑で完了したのですが、「1行アップデートとbulk updateってどんくらい速度違うんだってばよ!」 と思い、ついでに色々試してみました。
まずはtruncateでusersテーブルをお掃除して新しくレコードを突っ込んでおきます。

10,000件のレコードをDBに登録する
$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);

速度の計測には下記の記事を参考にしました。

https://eclair.blog/php-microtime/

Eloquentで1レコードずつ更新した場合のかかる時間

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を設定してないので、設定してみます。

Eloquentで1レコードずつ更新(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を使った場合でやってみます。

laravelBatchを使ってbulk update
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もやってみました。

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を投げる必要があります。
https://www.softel.co.jp/blogs/tech/archives/577

https://www.wakuwakubank.com/posts/807-mysql-max-allowed-packet/

array_chunkで100件ずつbulk updateしたSQLでも中々の長さ(遅そうにしか見えないが高速)。

行ロックに注意

transactionを張って時間のかかるUPDATE文を投げたときに行ロックされる場合があるので、サービスに影響ないように注意しないとですね。
(だからbulk updateってあまりないやらないのだろうか)

https://blog.katty.in/3082#toc3

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

株式会社ゆめみ

Discussion

かーちすかーちす

ありがとうございます!
readme通りにやってうまくいかなかったので助かりました!