📉

Laravel で DB のデータを CSV ダウンロードする方法をいろいろと試してみたり速度を計測してみたり

に公開
Laravel
PHP
SQL
CSV
tech

仕事で DB のデータを CSV で出力する必要がありました。
もろもろの条件としては次の通りです。

前提条件

  • 環境
    • CentOS 7系
    • PHP 7.3
    • Laravel 5.5
    • mysql 5.7
  • 要件的なもの
    • CSV 定義は次の通り(よくあるケース)
      • ヘッダあり
      • 文字コードは Shift-JIS
      • 改行コードは CR+LF
      • 区切り文字は ,
      • 囲み文字は “ で必須ではない
    • レコード数は 15 万行以上 (なお、無限に増える)
    • SQLからエクスポートした際の CSV ファイルサイズは 30MB 以上 (なお、無限に増える)
    • テーブル構成は別途記載

テーブル構成は次の通りです。

テーブル構成

CSV の項目は次の通りです。

  • 子ども情報もろもろ
  • 親情報もろもろ
  • 所属している所属クラス名1
  • 所属している所属クラス名2
  • 所属している所属クラス名3

ここでネックなのが次の条件です。

  • 子どもは３つのクラスに所属できる
  • CSV 出力時は所属クラス名をカラムを分けて出力

つまり、正規化から非正規化して出力する必要があるわけですね。
mysql であれば group_concat が使えますが、さてさて。

なお、一身上の都合による外部キーは使用しません(えっ)(だって引き継いだ状態が。。)

準備

環境は下記です。

https://github.com/yoshik159753/laravel-download-csv-sample

使う場合は readme.md を参照ください。
各テーブルの詳細はマイグレーションファイルをご確認ください。

検証環境や条件など

なるべく前提条件に近づけようとは思いますが、ローカル PC で試す以上はパフォーマンスに差がでるのは仕方ない、ということで。
検証環境のスペックは次のとおりです。

  • ホストPC
    • Ubuntu 20.04
    • CPU: Intel Corei7 1.80GHz
    • Memory: 16GB
  • Laradock の docker コンテナ環境で検証
  • 20 万件のデータを CSV に出力
  • php.ini などはデフォルト(Laradock に依存)
  • 時間の測定は JS 側で実施
    • リクエストで開始、レスポンスを受けて停止
    • 厳密な測定ではなく、 100ms 程度の誤差あり(詳細は処理を参照)

パフォーマンスに影響を与えると思われる要素について

この検証を行うにあたって、パフォーマンスに影響を与える要素を考えてみました。

  • DB IO
    • クエリ
      • SQL、ORM
      • join、group_concat、withの有無、withのチェーン、
    • 取得方法
      • get、chunk、cursor
  • File IO
    • 一時書き込み先
      • 外部ファイル、php://temp
    • 書き込み方法
      • fputcsv、league/csv

こんなところでしょうか。
(CSV の検証と言うよりも DB に依存しそうな気ががが)

検証

Case1 (メモリ枯渇)

とりあえず、ベースとするための激重となりそうな構成で試してみます。

条件。

項目 設定
クエリ なるべくSQL
クエリ出力 get
非正規化 get 後にクエリを呼び出し foreach で取得
一時書き込み先 外部ファイル
書き込み方法 fputcsv
実装のURL こちら

結果。

  • 500エラー

エラー内容(nginx のログより)。

PHP message: PHP Fatal error:  Allowed memory size of 268435456 bytes exhausted (tried to allocate 20480 bytes) in /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 332PHP message: PHP Fatal error:  Allowed memory size of 268435456 bytes exhausted (tried to allocate 32768 bytes) in /var/www/vendor/symfony/debug/Exception/FatalErrorException.php on line 1

考察。

たぶん、クエリを get したタイミングでメモリの許容量を超えてエラーになっている感じですね。
20 万件のデータなので、まぁ想定通りです。
クエリ出力には chunk か cursor を使いましょう。

Case2 (タイムアウト)

Case1 を cursor で試してみましょう。

条件。

項目 設定
クエリ なるべくSQL
クエリ出力 cursor
非正規化 get 後にクエリを呼び出し foreach で取得
一時書き込み先 外部ファイル
書き込み方法 fputcsv
実装のURL こちら

結果。

  • 504エラー

エラー内容(nginx のログより)。

[error] 10#10: *18 upstream timed out (110: Operation timed out) while reading response header from upstream

考察。

タイムアウトですね。ファイルに書き込まれていることは確認できたのですが。。こちらの gif を見てみましょう。


(氏名などはダミーです)

激遅！これで 20 万件とか何時間かかるんだ、という感じです。
となると、なにがボトルネックなのでしょうか？書き込み？１行ずつ書いてるのがダメ？それともクエリ？

Case3 (group_concat)

非正規化に group_concat を試してみましょう。

条件。

項目 設定
クエリ なるべくSQL
クエリ出力 cursor
非正規化 group_concat
一時書き込み先 外部ファイル
書き込み方法 fputcsv
実装のURL こちら

結果。

回数 応答時間
1回目 8301.479248046875 ms
2回目 8703.90576171875 ms
3回目 9302.893310546875 ms
平均 約 8769.43 ms

考察。

3回目にしてダウンロードできました。
所属クラスを取得するためにループ内でクエリを叩くと激遅になる感じですね。
やはりクエリの実行は高コストの処理、ということでしょうか。

そして想定外の速さでびっくりしました。。
この速度だと検証をする意味があるのか微妙になってきますね。
ひとまずこの結果をベースとして考えてみましょう。

Case4 (php://temp)

DB から取得したデータの書き込み先を php の一時ファイル(php://temp)にしてみましょう。

条件。

項目 設定
クエリ なるべくSQL
クエリ出力 cursor
非正規化 group_concat
一時書き込み先 php://temp
書き込み方法 fputcsv
実装のURL こちら

結果。

回数 応答時間
1回目 7404.696044921875 ms
2回目 7302.8369140625 ms
3回目 7202.437744140625 ms
平均 約 7303.33 ms

考察。

Case3 と比較すると約 1.4s ほど短縮されました。
Case3 では少しファイル操作をしていますので、そのコストが短縮されたと考えられますね。
そして、ファイル管理を一時ファイルにして php に任せるというのは自前でファイル操作するよりも安全な方法だと思います。

Case5 (league/csv)

CSV の書き込みに league/csv を使ってみましょう。

条件。

項目 設定
クエリ なるべくSQL
クエリ出力 cursor
非正規化 group_concat
一時書き込み先 外部ファイル
書き込み方法 league/csv
実装のURL こちら

結果。

回数 応答時間
1回目 11405.967041015625 ms
2回目 10906.249267578125 ms
3回目 14303.52392578125 ms
平均 約 12205.25 ms

考察。

遅いですね。。
実装を参照すると原因はわかるかと思います。
次はそこを改善してみましょう。

Case6 (league/csv でまとめて書き込み)

league/csv で書き込む際に1行ずつ書き込むと遅そうなのでまとめて書き込むようにしてみましょう。

条件。

項目 設定
クエリ なるべくSQL
クエリ出力 cursor
非正規化 group_concat
一時書き込み先 外部ファイル
書き込み方法 league/csv で insertAll を使用
実装のURL こちら

結果。

回数 応答時間
1回目 10205.012939453125 ms
2回目 10302.284912109375 ms
3回目 10802.7529296875 ms
平均 約 10436.33 ms

考察。

おっと、想定外の速度でした。
てっきりまとめて書き込んだ方が速度がでるかと想定していたのですが、あまり変わりませんでした。
ライブラリのオーバーヘッドは意外と大きいのかもしれません。
速度を重視する php ネイティブな方法か、安全性を重視してライブラリを使用するか。。
トレードオフを考える必要がありそうですね。

Case7 (ORM のリレーション / タイムアウト)

ここからは O/R マッパーの観点から見ていきましょう。
まずはなにも考えずに Eloquent のリレーションを使ってみます。

条件。

項目 設定
クエリ ORM
クエリ出力 cursor
非正規化 Eloquent のリレーション
一時書き込み先 外部ファイル
書き込み方法 fputcsv
実装
public function downloadCsvCase7()
{
    $query = Child::orderby('children.id', 'asc');

    $now = \now();
    $nowYyyyMmDdHhMmSs = $now->format('Ymd-His');
    $workspace = 'tmp/'.$nowYyyyMmDdHhMmSs;
    Storage::disk('local')->makeDirectory($workspace);
    $filename = 'families.csv';
    $outputCsv = storage_path('app/'.$workspace.'/'.$filename);

    $fp = fopen($outputCsv, 'w');
    fputcsv($fp, $this->header());
    foreach ($query->cursor() as $child) {
        $classes = $child->classes;
        fputcsv($fp, [
            $child->id,
            $child->name,
            $child->kana,
            $child->sex,
            $child->birthday,
            $classes->get(0)->class->name ?? null,
            $classes->get(1)->class->name ?? null,
            $classes->get(2)->class->name ?? null,
            $child->toParent->id,
            $child->toParent->name,
            $child->toParent->kana,
            $child->toParent->sex,
            $child->toParent->zip,
            $child->toParent->address,
            $child->toParent->tel,
            $child->toParent->email,
        ]);
    }
    rewind($fp);
    $buffer = str_replace(PHP_EOL, "\r\n", stream_get_contents($fp));
    $buffer = mb_convert_encoding($buffer, 'SJIS-win', 'UTF-8');
    rewind($fp);
    fwrite($fp, $buffer);
    fclose($fp);

    Cookie::queue("watchKeyDownloadCsv", "true", 0, "", "", false, false);
    return response()->download($outputCsv, $filename);
}

詳細はこちら

結果。

  • Case2 と同様に 504 エラー (タイムアウト)

考察。

Case2 と同様ですが、俗に言う N＋１ クエリ問題というやつですね。
Laravel の ORM である Eloquent には、これに対応する方法がありますので試してみましょう。

Case8 (Eager ロード / タイムアウト)

Eloquent には N＋１ クエリ問題を軽減するために Eager ロード という仕組みを用意しています。
こちらを使っていきましょう。

条件。

項目 設定
クエリ ORM + Eager ロード
クエリ出力 cursor
非正規化 Eloquent のリレーション
一時書き込み先 外部ファイル
書き込み方法 fputcsv
実装
public function downloadCsvCase8()
{
    $query = Child::with('classes.class')->orderby('children.id', 'asc');

    $now = \now();
    $nowYyyyMmDdHhMmSs = $now->format('Ymd-His');
    $workspace = 'tmp/'.$nowYyyyMmDdHhMmSs;
    Storage::disk('local')->makeDirectory($workspace);
    $filename = 'families.csv';
    $outputCsv = storage_path('app/'.$workspace.'/'.$filename);

    $fp = fopen($outputCsv, 'w');
    fputcsv($fp, $this->header());
    foreach ($query->cursor() as $child) {
        $classes = $child->classes;
        fputcsv($fp, [
            $child->id,
            $child->name,
            $child->kana,
            $child->sex,
            $child->birthday,
            $classes->get(0)->class->name ?? null,
            $classes->get(1)->class->name ?? null,
            $classes->get(2)->class->name ?? null,
            $child->toParent->id,
            $child->toParent->name,
            $child->toParent->kana,
            $child->toParent->sex,
            $child->toParent->zip,
            $child->toParent->address,
            $child->toParent->tel,
            $child->toParent->email,
        ]);
    }
    // 強制書き込み
    fflush($fp);
    rewind($fp);
    $buffer = str_replace(PHP_EOL, "\r\n", stream_get_contents($fp));
    $buffer = mb_convert_encoding($buffer, 'SJIS-win', 'UTF-8');
    rewind($fp);
    // クリア後に改行コードとエンコードを変更したデータを書き込む
    ftruncate($fp, 0);
    fwrite($fp, $buffer);
    fclose($fp);

    Cookie::queue("watchKeyDownloadCsv", "true", 0, "", "", false, false);
    return response()->download($outputCsv, $filename);
}

詳細はこちら

結果。

  • Case7 と同様に 504 エラー (タイムアウト)

考察。

ホワァァァ！！！
どうしてこうなった。。という感じですね。
いろいろ試したところ解決しましたので次に進みます。

Case9 (Eager ロード / chunk)

Case8 で何が起こっていたかと言いますと。
クエリの出力に cursor を使うと Eager ロードが効かないようでして。。(マニュアルにそんなの書いてあったかなぁ。。。)
ということで chunk を使っていきましょう。

条件。

項目 設定
クエリ ORM + Eager ロード
クエリ出力 chunk
非正規化 Eloquent のリレーション
一時書き込み先 外部ファイル
書き込み方法 fputcsv
実装
public function downloadCsvCase9()
{
    $query = Child::with('classes')->orderby('children.id', 'asc');

    $now = \now();
    $nowYyyyMmDdHhMmSs = $now->format('Ymd-His');
    $workspace = 'tmp/'.$nowYyyyMmDdHhMmSs;
    Storage::disk('local')->makeDirectory($workspace);
    $filename = 'families.csv';
    $outputCsv = storage_path('app/'.$workspace.'/'.$filename);

    $fp = fopen($outputCsv, 'w+');
    fputcsv($fp, $this->header());
    $query->chunk(10000, function ($children) use (&$fp) {
        foreach ($children as $child) {
            $classes = $child->classes;
            fputcsv($fp, [
                $child->id,
                $child->name,
                $child->kana,
                $child->sex,
                $child->birthday,
                $classes->get(0)->class->name ?? null,
                $classes->get(1)->class->name ?? null,
                $classes->get(2)->class->name ?? null,
                $child->toParent->id,
                $child->toParent->name,
                $child->toParent->kana,
                $child->toParent->sex,
                $child->toParent->zip,
                $child->toParent->address,
                $child->toParent->tel,
                $child->toParent->email,
            ]);
        }
    });
    // 強制書き込み
    fflush($fp);
    rewind($fp);
    $buffer = str_replace(PHP_EOL, "\r\n", stream_get_contents($fp));
    $buffer = mb_convert_encoding($buffer, 'SJIS-win', 'UTF-8');
    rewind($fp);
    // クリア後に改行コードとエンコードを変更したデータを書き込む
    ftruncate($fp, 0);
    fwrite($fp, $buffer);
    fclose($fp);

    Cookie::queue("watchKeyDownloadCsv", "true", 0, "", "", false, false);
    return response()->download($outputCsv, $filename);
}

詳細はこちら

結果。

回数 応答時間
1回目 185042.0947265625 ms
2回目 262838.6423339844 ms
3回目 199103.99389648438 ms
平均 約 215661.58 ms

考察。

なかなか遅いですね。
動いてはいますが、ベースとしている case3 と比べると約２５倍の遅さです。
パフォーマンスをあげることはできないでしょうか？ 🤔

Case10 (Eager ロード / chunk / ネストした Eager ロード)

Eager ロードはネストすることが可能です。
今回としては Child -> ChildToClass -> Clazz という形ですね。
これを使うことで子以降のテーブルに対しても Eager ロードすることが可能です。
試してみましょう。

条件。

項目 設定
クエリ ORM + Eager ロード(ネストあり)
クエリ出力 chunk
非正規化 Eloquent のリレーション
一時書き込み先 外部ファイル
書き込み方法 fputcsv
実装

case9 の with 句を classes.class にしただけです。

public function downloadCsvCase10()
{
    $query = Child::with('classes.class')->orderby('children.id', 'asc');

    $now = \now();
    $nowYyyyMmDdHhMmSs = $now->format('Ymd-His');
    $workspace = 'tmp/'.$nowYyyyMmDdHhMmSs;
    Storage::disk('local')->makeDirectory($workspace);
    $filename = 'families.csv';
    $outputCsv = storage_path('app/'.$workspace.'/'.$filename);

    $fp = fopen($outputCsv, 'w+');
    fputcsv($fp, $this->header());
    $query->chunk(10000, function ($children) use (&$fp) {
        foreach ($children as $child) {
            $classes = $child->classes;
            fputcsv($fp, [
                $child->id,
                $child->name,
                $child->kana,
                $child->sex,
                $child->birthday,
                $classes->get(0)->class->name ?? null,
                $classes->get(1)->class->name ?? null,
                $classes->get(2)->class->name ?? null,
                $child->toParent->id,
                $child->toParent->name,
                $child->toParent->kana,
                $child->toParent->sex,
                $child->toParent->zip,
                $child->toParent->address,
                $child->toParent->tel,
                $child->toParent->email,
            ]);
        }
    });
    // 強制書き込み
    fflush($fp);
    rewind($fp);
    $buffer = str_replace(PHP_EOL, "\r\n", stream_get_contents($fp));
    $buffer = mb_convert_encoding($buffer, 'SJIS-win', 'UTF-8');
    rewind($fp);
    // クリア後に改行コードとエンコードを変更したデータを書き込む
    ftruncate($fp, 0);
    fwrite($fp, $buffer);
    fclose($fp);

    Cookie::queue("watchKeyDownloadCsv", "true", 0, "", "", false, false);
    return response()->download($outputCsv, $filename);
}

詳細はこちら

結果。

回数 応答時間
1回目 95237.02416992188 ms
2回目 93401.94116210938 ms
3回目 101420.0478515625 ms
平均 約 96686.34 ms

考察。

case9 と比べるとだいぶはやくなりました。
ただベースとしている case3 と比べるとまだ約１２倍ほどの遅さです。
やはり ORM は遅いですね。。

まとめ

まとめていきましょう。

個人的 best case1 (可読性重視)

可読性を重視するのであれば ORM を使用するのがよいでしょう。

条件。

項目 設定
クエリ ORM + Eager ロード(ネストあり)
クエリ出力 chunk
非正規化 Eloquent のリレーション
一時書き込み先 php://temp
書き込み方法 league/csv
実装
public function downloadCsvCaseZ1()
{
    $query = Child::with('classes.class')->orderby('children.id', 'asc');

    $csvWriter = $this->csvWriter('php://temp');
    $csvWriter->insertOne($this->header());

    $query->chunk(10000, function ($children) use ($csvWriter) {
        foreach ($children as $child) {
            $csvWriter->insertOne($this->childToColumn($child));
        }
    });

    Cookie::queue("watchKeyDownloadCsv", "true", 0, "", "", false, false);
    return Response::make($csvWriter->getContent(), 200, [
        'Content-Type' => 'text/csv',
        'Content-Disposition' => 'attachment; filename=families.csv',
    ]);
}

詳細はこちら

結果。

回数 応答時間
1回目 87813.49096679688 ms
2回目 89394.37890625 ms
3回目 85869.84912109375 ms
平均 約 87692.57 ms

考察。

case3 と比べると１０倍ほどパフォーマンスが悪くなっていますが、クエリを考える手間は省けますね。

個人的 best case2 (速度重視)

速度を重視するのであれば ORM を使わず SQL でいきましょう。

条件。

項目 設定
クエリ なるべくSQL
クエリ出力 cursor
非正規化 group_concat
一時書き込み先 php://temp
書き込み方法 league/csv
実装
public function downloadCsvCaseZ2()
{
    $query = Child::join('parents', 'children.parent', 'parents.id')
        ->join('child_to_class', 'children.id', 'child_to_class.child_id')
        ->join('class', 'child_to_class.class_id', 'class.id');
    $selectBaseItems = array_merge(
        $this->selectBaseItems(),
        [DB::raw('GROUP_CONCAT(class.name) AS class_names')]
    );
    $query->select($selectBaseItems)
        ->groupby('children.id')
        ->orderby('children.id', 'asc');

    $csvWriter = $this->csvWriter('php://temp');
    $csvWriter->insertOne($this->header());

    foreach ($query->cursor() as $family) {
        $csvWriter->insertOne($this->familyToColumn($family));
    }

    Cookie::queue("watchKeyDownloadCsv", "true", 0, "", "", false, false);
    return Response::make($csvWriter->getContent(), 200, [
        'Content-Type' => 'text/csv',
        'Content-Disposition' => 'attachment; filename=families.csv',
    ]);
}

詳細はこちら

結果。

回数 応答時間
1回目 10601.968994140625 ms
2回目 10205.130126953125 ms
3回目 10501.994140625 ms
平均 約 10436.36 ms

考察。

case3 と比べると２秒程度パフォーマンスが悪くなっていますが、ファイル操作がないので可読性は高くて良いかと思います。

速度を求めるならば fputcsv を使うべきでは？と考える方もいらっしゃるでしょう。
確かにご指摘はごもっともです。
ただ、実装を見てもらうとわかりますが、あちらでは一度バッファにデータを保持しています。
このため、扱うデータ量次第ではメモリ不足でエラーになる可能性が否めません。
要件次第ではありますが、私としてはリスクが少ない方法がよいかと思います。
(自分の考えが足りていないだけかもしれませんが。。)

各条件についてまとめ

最後に各条件についてまとめてみました。
下記のようになるかと思います。

  • DB IO
    • クエリ
      • 速度優先 or SQLが得意 -> SQL + group_concat + cursor (※1)
      • 可読性優先 or SQLが苦手 -> ORM + Eager ロード + chunk
  • File IO
    • 一時書き込み先
      • 外部ファイル or php://temp
    • 書き込み方法
      • 速度優先 -> fputcsv
      • 可読性優先 -> league/csv

参考記事

本記事を書くにあたって参考にさせていただいた記事です！ありがとうございます！

Discussion