バッチ処理などでMysqlのupdate文を何度も実行する機会があるかと思います。
システム条件は以下の様になっているものとします。
・サーバー(AWS)
・DB(RDS Mysql)
update文は、処理する度に通信が発生します。updateの回数が少なければ問題ないですが、1万件のレコードを更新することになると、通信も1万回発生するため、処理が遅くなってしまいます。
update文は、1回の通信でまとめて変更する機能はありませんが、update文の書き方によって、複数のレコードを更新することが可能です。
まとめてupdateすることを「bulk update」と言います。
Contents
- 1 bulk updateの効果
- 1.1 概要
- 1.2 １レコード更新の度にupdateを実施する
- 1.3 bulk updateを実施する
- 2 MySQLにおいてのbulk updateの書き方
- 3 bulk updateを試してみる
- 4 bulk updateを使ったPHPの実装例
- 5 まとめ
bulk updateの効果
概要
23000件のデータをupdate文で更新したときの実行速度を比較します。
１レコード更新の度にupdateを実施する
23000件の更新にかかった時間は、2分37秒です。
23000件の通信が発生したためでしょう。
bulk updateを実施する
23000件の更新にかかった時間は、30秒です！
つまり約5倍ほどの速度で処理が完了しました。
ただし、私は23000件を一度のupdate文で実施せず、1000件ごとに分割しました。その理由は、一度にupdateすると通信負荷がかかると思ったからです。
つまり23回の通信で23000件分の更新を実施できたことになります。
MySQLにおいてのbulk updateの書き方
通常のupdate文は、以下のように書きますが、
update m_address set address = 'tokyo' where id = 2;
update m_address set address = 'kyoto' where id = 3;
bulk updateの場合は、以下のように書きます。
update `m_address` SET
`address` =
case `id`
WHEN 2 THEN 'tokyo'
WHEN 3 THEN 'kyoto'
END
, `modified` = NOW()
WHERE `id` IN (2,3);
idを指定してcase、WHENを使って更新していることがわかるかと思います。
デメリットは、SQL文の書き方が若干複雑で、ソースコード上にSQLを埋め込むのが面倒なことです。
bulk updateを試してみる
bulk updateを試すことができるようにするため、以下のようなデータを用意しました。
m_addressテーブルを作成します。
CREATE TABLE `m_address` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(40) NOT NULL DEFAULT '',
`address` varchar(11) NOT NULL DEFAULT '',
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
`del` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
m_addressにデータを追加します
INSERT INTO `m_address` (`id`, `name`, `address`, `created`, `modified`, `del`)
VALUES
(1,'name1','add1','2018-01-10 14:15:47','2018-01-10 14:15:47',0),
(2,'name2','add2','2018-01-10 15:15:47','2018-01-10 14:15:47',0),
(3,'name3','add3','2018-01-10 15:15:47','2018-01-10 14:15:47',0),
(4,'name4','add4','2018-01-10 15:15:47','2018-01-10 14:15:47',0),
(5,'name5','add5','2018-01-10 15:15:47','2018-01-10 14:15:47',0);
bulk updateを実施してみる
update `m_address` SET
`address` =
case `id`
WHEN 2 THEN 'tokyo'
WHEN 3 THEN 'kyoto'
END
, `modified` = NOW()
WHERE `id` IN (2,3);
その結果、
id2のaddressがtokyo、id3のaddressがkyotoに更新されます。
bulk updateを使ったPHPの実装例
Cakephpの例になりますが、PHPの実装例を掲載します。
実際に使う際は、これから実装しようとしているシステム要件に合わせて修正してください。
使用する際は自己責任でお願いします。
説明：
以下のPHPの関数は、Modelクラス内に実装しています。
カラムにidが設定されていることが前提です。
/**
* バルクアップデート
* @param 登録データ
*/
public function bulkUpdate($datas){
$Divid = 1000; //1000件に分割してupdateを実施している
//一度に大量のSQL文が送信されないように分割する
$temps = array();
$count = 0;
$index = 0;
for($i = 0; $i < count($datas); $i++){
if($count >= $Divid ){
$index++;
$count = 0;
}
$temps[$index][] = $datas[$i];
$count++;
}
foreach($temps as $data){
//アップデート処理
$this->bulkUpdateMain($data);
}
return false;
}
private function bulkUpdateMain($data){
if(count($data)>0){
$tableName = $this->tablePrefix.$this->useTable;
//要素分解
//カラムごと
$columns = $data[0];
if(!isset($columns['id'])){
return false;
}
//下記の処理ではidカラムは使わないのでここで削除
unset($columns['id']);
//idの情報を取得する
$ids = array_column($data, "id");
$count = 0;
$partSql = "";
foreach($columns as $key => $value){
$date2 = array_column($data, $key);
//データ更新
$columnDatas = "";
for($i = 0; $i<count($date2 );$i++){
$a = "";
if(is_int($date2[$i])){
$a = $date2[$i];
}else{
$a = "'$date2[$i]'";
}
if(!is_int($ids[$i])) continue;
$columnDatas = $columnDatas . "WHEN {$ids[$i]} THEN {$a} ";
}
$partSql = $partSql . "`{$key}` = case `id` {$columnDatas} END ";
$count++;
if(count($columns) > $count ){
$partSql = $partSql . " ,";
}
//var_dump($partSql);
}
$ids =implode($ids,',');
$sql = "update `{$tableName}` SET {$partSql} WHERE `id` IN ({$ids}) ";
//echo $sql;
$ret = $this->query($sql);
if ($ret === false) {
// エラー処理
return false;
}
if ($this->getAffectedRows() != count($data)) {
// エラー処理
return false;
}
return true;
}
まとめ
いかがでしょうか？bulk updateを使えば、一度に複数レコードのデータを更新できます。
bulk updateをSQL文にすると複雑になってしまいますが、10000件を超えるデータを一度に更新したいときは、実行速度に大きな差が出てくるので、使ってみてはいかがでしょうか？