みなさんこんにちは。株式会社アップルワールドに所属しております、T.Hです。
PHPでWebアプリケーションを作っているとバッチ処理が必要になるので大量のレコードの入ったテーブルを逐次処理するようなプログラムを組まなくてはいけなくなります。
私は弊社に入社以前、長年フリーランスで活動しており、いろんなPHPの現場に行ってきましたがバッチ処理のボトルネックが多くありました。それを改善するベストプラクティスがありますので、みなさんに共有します。
PHPで大量のレコードの入ったテーブルを処理する場合どうしますか？
ini_set('memory_limit', '-1');
set_time_limit(0);
やはり、メモリ利用制限を外して、タイムアウト時間を無限としていないでしょうか？
非常に富豪的プログラミングですね。時間もメモリも無駄なので、ぜひとも止めていただきたい。
結論
memory_limitに触れない程度のレコード数を読み込んで処理したのちにメモリを解放する。
ini_set('memory_limit', '512M');
$offset = 0;
$limit = 1000; // memory_limitに触れない程度のレコード数 ※１
$rows = $db->SQL("SELECT * FROM `table` LIMIT @limit OFFSET @offset",[
"offset"=>$offset
,"limit"=>$limit
]);//シーケンシャル(連番）なIDがある場合はそちらを使った方が早い場合がある ※２
while(0 != count($rows)){
//処理する
foreach($rows as $i=>$record){
//処理。ごにょごにょ
}
unset($rows);//メモリ解放
//次を読み込む
$offset += $limit;
$rows = $db->SQL("SELECT * FROM `table` LIMIT @limit OFFSET @offset",[
"offset"=>$offset
,"limit"=>$limit
]);
}
※１：レコード数（$limit)＝memory_limit✖️ 3分の2➗１レコードの使用メモリを計算してレコード数を決める。memory_limitに設定したメモリ数の３分の２くらいだとおおよそ動く。（経験則）
※２：シーケンシャル（連番）なID（1スタートと仮定）があり、そのIDがINDEXされている場合は以下のようなSQLでSELECTした方が早い。
ini_set('memory_limit', '512M');
$position = 0;
$limit = 1000;
$rows = $db->SQL("SELECT * FROM `table` WHERE id > @position LIMIT @limit",[
"position"=>$position
,"limit"=>$limit
]);
while(0 != count($rows)){
//処理する
foreach($rows as $i=>$record){
//処理。ごにょごにょ
}
unset($rows);//メモリ解放
//次を読み込む
$position += $limit;
$rows = $db->SQL("SELECT * FROM `table` WHERE id > @position LIMIT @limit",[
"position"=>$position
,"limit"=>$limit
]);
}
ダメな例
全件取得
$rows = $db->SQL("SELECT * FROM `table`",[]);//全件取得
foreach($rows as $i=>$record){
//処理。ごにょごにょ
}
レコード数がメモリ内に収まる場合は良いが、溢れた場合、プログラムは破綻して止まるか応答を返さなくなる。
1行毎処理
while($row = $db->getRow()){
//処理。ごにょごにょ
}
１行ごとにDBにアクセスに行くのでオーバーヘッドが１行ごとにかかり遅くなる。いつまでたっても処理が終わらない原因になる。
最後に
初学者サイトでは全件取得や１行毎処理でレコードを処理するサンプルコードがよく出てきますが、これをレコード数が多いテーブルの処理に適用してしまうと処理効率が著しく落ちる、もしくはOut of Memoryエラーで止まってしまうので、是非、本テクニックを使ってPHPのバッチ処理を最適化して欲しいです。
Discussion