超倍速！？複数のレコード更新を1回で実施するbulk update！【MySQL用】

バッチ処理などでMysqlのupdate文を何度も実行する機会があるかと思います。

システム条件は以下の様になっているものとします。

• サーバー(AWS)
• DB(RDS Mysql)

update文は、処理する度に通信が発生します。updateの回数が少なければ問題ないですが、1万件のレコードを更新することになると、通信も1万回発生するため、処理が遅くなってしまいます。

update文そのものには、1回の通信でまとめて変更する機能はありませんが、update文の書き方によって、複数のレコードを更新することが可能です。

まとめてupdateすることを「bulk update」と言います。

＼IT未経験者からのサポートあり！転職サービス3選！！／

サービス名
未経験	未経験OK	未経験の転職専用	経験者向け
公開の求人数	ITエンジニア全体で1万件以上 ITエンジニア未経験で600件以上	未公開	5,000件以上
利用対象	全職種	IT特化	IT特化
特徴	✓誰もが知る転職サービス ✓経歴を登録しておくとオファーが来る	✓企業担当者と条件交渉 ✓スキルの身につく企業を紹介	✓IT専門のエージェントが対応 ✓転職成功すると年収200万円以上の大幅アップがある
転職サポート内容	求人検索	企業担当者と交渉 求人紹介 ライフプランのサポート	キャリア相談 求人紹介 提出書類の添削 面接対策
公式サイト	リクナビネクスト	テックゲート	レバテックキャリア

概要

23000件のデータをupdate文で更新したときの実行速度を比較します。

１レコード更新の度にupdateを実施する

23000件の更新にかかった時間は、2分37秒です。

23000件の通信が発生したためでしょう。

bulk updateを実施する

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に更新されます。

MySQLトラブルシューティング

posted with ヨメレバ

スヴェータ・スミルノヴァ/豊福剛 オライリー・ジャパン 2012年10月

Amazon

Kindle

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件を超えるデータを一度に更新したいときは、実行速度に大きな差が出てくるので、使ってみてはいかがでしょうか？