はじめに
CSVダウンロード処理で以下のような問題が発生したので、原因と対応した内容を備忘録として残します。
- CSVダウンロード時に
ERR_INVALID_RESPONSE/ 500エラーになる - 件数が多いとメモリ不足になる
- ダウンロードに時間がかかる
最終的には、取得対象を絞る・1行ずつ出力する・UTF-8 BOM付きにする・gzip圧縮することで改善。
開発環境
- PHP
- PDO
- MySQL
- CSVダウンロード機能
- ExcelでCSVを開く想定
500エラー / ERR_INVALID_RESPONSE
CSVダウンロード時に、 ERR_INVALID_RESPONSE のエラーあり。
原因は、phpファイルのの末尾にPHPの閉じタグ ?> があり、その後ろに改行が入っていたことでした。
<?php
// DB設定など
?>
?> の後ろにある改行が、意図せずレスポンスとして出力されていました。
CSVダウンロード処理では、以下のように header() でレスポンスヘッダーを設定する。
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="export.csv"');
ただ、header() より前に空白や改行などの出力があると、正常にヘッダーを送れなくなります。
その結果、CSVのレスポンスが壊れて500エラーのようになっていました。
メモリ不足
次に、CSVの件数が多いと以下のエラーが出ました。
Allowed memory size exhausted
原因は、全年度・全件のデータを取得して、CSV全体を $csvstr に溜め込んでから出力していたことでした。
// 修正前
$csvstr = '';
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$csvstr .= implode(',', $row) . "\r\n";
}
echo mb_convert_encoding($csvstr, 'SJIS-win', 'UTF-8');
この書き方だと、件数が増えるほど $csvstr が大きくなり、最後に mb_convert_encoding() で一括変換していたので、変換前と変換後の文字列がメモリ上に存在して、メモリをかなり使っていました。
対応1：取得対象を絞る
まず、全件取得をやめて、当年度のデータだけ取得するようにしました。
$year = ((int)date('n') >= 4)
? (int)date('Y')
: (int)date('Y') - 1;
SQLも当年度だけ取得するように修正しました。
SELECT *
FROM users
WHERE fiscal_year = :year
$stmt = $pdo->prepare(
'SELECT * FROM users WHERE fiscal_year = :year'
);
$stmt->execute([
':year' => $year,
]);
不要なデータを取らないだけでも、かなり改善できました。
対応2：1行ずつ出力する
次に、CSV全体を1つの文字列に溜め込むのをやめました。
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo implode(',', array_map('csvEscape', $row)) . "\r\n";
}
取得したデータをそのまま1行ずつ出力することで、CSV全体をメモリに持たなくてよくなりました。
大量データをCSV出力するときは、一度全部作ってから出力するより、取得しながら順番に出力する方がよさそうです。
ダウンロードが遅い
メモリ不足は解消しましたが、まだダウンロードに時間がかかっていました。
原因は主に以下の2つでした。
-
mb_convert_encoding()を毎行呼んでいた - 出力バッファなしで毎行
echoしていた
mb_convert_encodingをやめる
元々はExcelで文字化けしないように、毎行SJISに変換していました。
echo mb_convert_encoding($line, 'SJIS-win', 'UTF-8');
ただ、件数が多いとこの変換処理がかなり重くなります。
今回は、SJIS変換をやめてUTF-8 BOM付きCSVにしました。
echo "\xEF\xBB\xBF";
レスポンスヘッダーもUTF-8にします。
header('Content-Type: text/csv; charset=UTF-8');
ExcelはUTF-8 BOM付きであればそのまま開けるので、文字コード変換が不要になりました。
gzip圧縮バッファを使う
1行ずつ echo する形にしたことでメモリ使用量は減りましたが、そのままだと細かい出力が大量に発生します。
そこで、ob_start('ob_gzhandler') を使ってgzip圧縮バッファを有効にしました。
ob_start('ob_gzhandler');
CSVはテキストなのでgzip圧縮と相性がよく、今回のケースでは以下のように転送サイズが減りました。
30MB → 約3〜5MB
転送量が減ったことで、ダウンロードもかなり速くなりました。
最終的なコード
最終的には以下のような形にしました。
<?php
// 1. 年度を計算
$year = ((int)date('n') >= 4)
? (int)date('Y')
: (int)date('Y') - 1;
// 2. gzip圧縮バッファ開始
ob_start('ob_gzhandler');
// 3. CSV用ヘッダー
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="export.csv"');
// 4. UTF-8 BOMを出力
echo "\xEF\xBB\xBF";
// 5. 当年度のみ取得
$stmt = $pdo->prepare(
'SELECT * FROM users WHERE fiscal_year = :year'
);
$stmt->execute([
':year' => $year,
]);
// 6. 1行ずつCSV出力
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo implode(',', array_map('csvEscape', $row)) . "\r\n";
}
ob_end_flush();
CSVエスケープ
CSVでは値にカンマやダブルクォート、改行が入る可能性があります。
そのため、単純に implode(',', $row) するだけだと崩れる可能性があります。
function csvEscape($value): string
{
$value = (string)$value;
if (
str_contains($value, ',') ||
str_contains($value, '"') ||
str_contains($value, "\n") ||
str_contains($value, "\r")
) {
$value = '"' . str_replace('"', '""', $value) . '"';
}
return $value;
}
PHP7系などで str_contains() が使えない場合は、strpos() を使います。
function csvEscape($value): string
{
$value = (string)$value;
if (
strpos($value, ',') !== false ||
strpos($value, '"') !== false ||
strpos($value, "\n") !== false ||
strpos($value, "\r") !== false
) {
$value = '"' . str_replace('"', '""', $value) . '"';
}
return $value;
}
まとめ
今回はCSVダウンロード処理で発生した、500エラー・メモリ不足・低速化に対応しました。
CSV出力は単純そうに見えますが、データ量が増えるとメモリや速度の問題が出やすいと感じました。
今後、大量データをCSVで出力する場合は、最初から「必要なデータだけ取得する」「全件をメモリに持たない」「不要な変換をしない」を意識して実装しようと思います。