MySQLでバルクアップデート処理

1 はじめに
2 使用するテーブル
3 パターン数が少ないシンプルな更新
4 パターン数が多い場合
5 さいごに
6 おすすめ書籍

はじめに

バッチ処理の実装では大量データを更新する場面がちょいちょい出てくると思います。
パフォーマンスを考えて出来る限りSQL発行回数を少なくして一括更新したい所です。
そんな時に使えるかもしれないバルクアップデート処理を紹介したいと思います。

使用するテーブル

テスト用に使用するテーブルです。

 create table t_students ( id bigint unsigned auto_increment primary key, name varchar(255) null comment '名前', age int null comment '年齢', attendance_days int default 0 not null comment '出席日数', test_status varchar(255) null comment 'テスト結果', test_score int default 0 not null comment 'テストの点数' )

create table t_students

(

id bigint unsigned auto_increment primary key,

name varchar(255) null comment '名前',

age int null comment '年齢',

attendance_days int default 0 not null comment '出席日数',

test_status varchar(255) null comment 'テスト結果',

test_score int default 0 not null comment 'テストの点数'

)

パターン数が少ないシンプルな更新

いつもやっている様なシンプルな更新処理で書けます。
例えば、テストの点数によって生徒のステータスを更新する場合、
パターンが一つしかない場合は、シンプルにwhereで条件付けて書けます

 // 例 テストの点数が40点以下の生徒は、テスト不合格 update students set test_status = 'failed' where score <= 40

1 2	// 例テストの点数が40点以下の生徒は、テスト不合格 update students set test_status = 'failed' where score <= 40

パターンが少数の場合は、case文で対応する事も可能です。

 update t_students set test_status = case when test_score >= 80 then 'passed' when test_score < 80 and test_score >= 40 then 'retry' when test_score < 80 and test_score < 40 then 'failed' end

update t_students

set test_status =

case

when test_score >= 80 then 'passed'

when test_score < 80 and test_score >= 40 then 'retry'

when test_score < 80 and test_score < 40 then 'failed'

end

パターン数が多い場合

生徒の出席日数を更新したいと思います。
先程と異なり値の規則性がない為、whereやcaseで条件を付けるのは辛いものがあります。
その場合は、FIELD関数とELT関数の組み合わせで対応する事が可能です。

FIELD関数

第一引数のインデックスを返す関数。
下の例の場合、 ドイツ の文字列が文字リストの2番目にある為、2が返却されます。

 // 2が返却される SELECT FIELD('ドイツ ', '日本', 'ドイツ ', 'スペイン', 'コスタリカ');

1 2	// 2が返却される SELECT FIELD('ドイツ ', '日本', 'ドイツ ', 'スペイン', 'コスタリカ');

詳細はこちら

ELT関数

引数から指定した番号の要素を返す関数。
下の例の場合、文字リストの1番目に当たる 日本 が返却されます。

 // 日本が返却される SELECT ELT(1, '日本', 'ドイツ', 'スペイン', 'コスタリカ');

1 2	// 日本が返却される SELECT ELT(1, '日本', 'ドイツ', 'スペイン', 'コスタリカ');

詳細はこちら

組み合わせ

この2つを組み合わせて、student毎に出席日数をupdate出来ます。
where句で指定しているnameのインデックスをFIELD関数で取得し、それをELT関数の取得インデックスに利用しています。
結果的に、where句でname指定されている生徒の出席日数をそれぞれ指定された日数（10,20,30,40）に更新しています。
同様に、FILED関数の引数、ELT関数の引数、WHERE句に更新したい分の値を入れてやれば、一度のSQLでバルクアップデートが行う事が出来ます。

 // 一郎の出席日数は10、二郎の出席日数は20、三郎の出席日数は30、四郎の出席日数は40 update t_students set attendance_days = ELT(FIELD(id, '一郎', '次郎', '三郎', '四郎'), 10, 20, 30, 40) where name IN ('一郎', '二郎', '三郎', '四郎')

// 一郎の出席日数は10、二郎の出席日数は20、三郎の出席日数は30、四郎の出席日数は40

update t_students set attendance_days = ELT(FIELD(id, '一郎', '次郎', '三郎', '四郎'), 10, 20, 30, 40)

where name IN ('一郎', '二郎', '三郎', '四郎')

パフォーマンス

試しに1万レコードを一つずつアップデートする場合とバルクアップデートする場合で所要時間を比較してみたいと思います。
テストした際のコードは以下の通りです。
バルクアップデートする場合としない場合で、処理時間を計測してみました。

 $start = microtime(true); // 1レコードずつSQL発行して更新する場合 DB::transaction(function () { $students = Student::all(); foreach ($students as $student) { $student->attendance_days = 10; $student->save(); } }); // バルクアップデートで一括更新する場合（テスト用に名前ではなく生徒IDで指定） $ids = range(1, 10000); // 1~10000までの配列 $ids = implode(', ', $ids); $days = array_fill(0, 10000, 10); // 要素10が10000個ある配列 $days = implode(', ', $days); $query = "update t_students set attendance_days = ELT(FIELD(id, {$ids}), {$days}) where id in ({$ids})"; DB::statement($query); // queryの実行 // 処理時間計測 $end = microtime(true); echo '処理時間:'.($end - $start)."秒<br>\n";

$start = microtime(true);

// 1レコードずつSQL発行して更新する場合

DB::transaction(function () {

$students = Student::all();

foreach ($students as $student) {

$student->attendance_days = 10;

$student->save();

}

});

// バルクアップデートで一括更新する場合（テスト用に名前ではなく生徒IDで指定）

$ids = range(1, 10000); // 1~10000までの配列

$ids = implode(', ', $ids);

$days = array_fill(0, 10000, 10); // 要素10が10000個ある配列

$days = implode(', ', $days);

$query = "update t_students set attendance_days = ELT(FIELD(id, {$ids}), {$days}) where id in ({$ids})";

DB::statement($query); // queryの実行

// 処理時間計測

$end = microtime(true);

echo '処理時間:'.($end - $start)."秒<br>\n";

私のローカル環境ではバルクアップデートした場合0.5秒、1つずつアップデートした場合で20秒程度掛かっていました。
1万レコードでこれなので数が増えると結構な差が生まれそうです。

さいごに

関数の組み合わせで複数レコードを一括更新する方法を紹介しました。バッチ処理位しか使う場面はないと思いますが、機会があれば一度試してみてください。

日	月	火	水	木	金	土
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30