はじめに
データベースにあるテーブルの情報をCSV出力するためのベストな方法を模索し、速度を計測したので記事にまとめたいと思います。
動作確認環境
- PHP 8.0
- Laravel 8.0
※下位環境でも動作する場合がございます
補足
Dockerを使っていて、PHPコンテナからMySQLコンテナにコマンドが通らない場合はこちらの記事に解決法が記載されています。
Laravel(PHP)でのCSV出力方法について
主に以下の3つの方法がありました
- fwrite
- fputcsv
- mysqlコマンドを直接実行する
fwriteとfputcsvの違いについては、こちらの記事が勉強になりました。
fwriteとfputcsvではCSV化したものをExcelで開くことがある場合は、fputcsvの方が軍配が上がる印象です。
ということで、今回はfwriteは候補から除外し、fputcsvとmysqlとでどちらが便利かを調査しました。
計測条件
- 1つのテーブルに一千万のレコードが入っている
- テーブルから指定期間のデータをCSV出力する
- その際出力するデータ量は約100万レコードとする
計測データ
計測に使ったソースコード
$sumCount = DB::table('users')
->where('updated_at', '>=', '2021-07-02 00:00:00')
->count();
$fetchNeedCount = (int)ceil($sumCount / 50000);
for ($i = 0; $i < $fetchNeedCount; $i++) {
$records = DB::table('users')
->where('updated_at', '>=', '2021-07-02 00:00:00')
->skip($i * 50000)
->take(50000)
->get();
$stream = fopen('php://temp', 'r+b');
foreach ($records as $record) {
fputcsv($stream, (array)$record);
}
rewind($stream);
$csv = str_replace(PHP_EOL, "\r\n", stream_get_contents($stream));
$csv = mb_convert_encoding($csv, 'SJIS-win', 'UTF-8');
file_put_contents('users.csv', $csv, FILE_APPEND);
}
$cmd = 'mysql -B -N -u phper -h db -psecret -D local_laravel -e "SELECT * FROM users where updated_at >= \'2021-07-02 00:00:00\'" | sed -e "s/\t/,/g" > test.csv';
echo exec($cmd);
結果
| 項目 | 計測時間 |
|---|---|
| fputcsv | 約5分 |
| mysqlコマンド | 約30秒 |
なお、下記SQLを生でDBに叩いた時の計測時刻は約20秒でした。
SELECT * FROM users where updated_at >= '2021-07-02 00:00:00';
まとめ
取得件数が少ない場合は、fputcsvとmysqlコマンドの差はそれほどなかったのですが、CSV化の対象が多くなると、fputcsv側ではメモリオーバーの発生を避けるためにクエリー発行を複数回に分ける必要が出てきたため、より時間がかかってしまう結果となりました。
なお、今回はupdated_atの型はTIMESTAMP型としましたが、時刻をUNIXタイムスタンプでも管理し、そのカラムで絞り込むというチューニング方法を用いるとさらに処理が速くなりそうです。
[追記]
実際にint型でタイムスタンプを挿入し、その値基準でしぼりこみを行うとSQL実行にかかる時間が3秒程度速くなること確認できました。
完成版コード
mysqlコマンドの方がベターであることが分かったので、これを基準としてLaravelで全テーブルの情報をCSV化するコマンドを作成しましたので合わせて記載したいと思います。
<?php
namespace App\Console\Commands;
use Carbon\Carbon;
use DB;
use Illuminate\Console\Command;
use Illuminate\Filesystem\Filesystem;
use LogicException;
/**
* Class ConvertCSV.
*/
class ConvertCSV extends Command
{
/**
* The filesystem instance.
*
* @var \Illuminate\Filesystem\Filesystem
*/
protected $file;
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'convert_csv {--day=} {--period=1}';
/**
* The console command description.
*
* @var string
*/
protected $description = 'convert_csv';
/**
* List of connection names to be output.
*
* @var array
*/
protected $targetConnectionNames = [
'mysql',
];
/**
* FileOperation constructor.
*/
public function __construct(
Filesystem $file
) {
$this->file = $file;
parent::__construct();
}
/**
* Execute the console command.
*/
public function handle(): void
{
[$startDate, $endDate] = $this->getStartEndTime();
$this->createDirectory(storage_path(sprintf('dump_csv/%s', $startDate)));
foreach ($this->targetConnectionNames as $connectionName) {
$configConnection = config('database.connections.'.$connectionName);
if (empty($configConnection)) {
throw new LogicException(sprintf('The target was not found:%s', $connectionName));
}
$databaseName = $configConnection['database'];
$username = $configConnection['username'];
$password = $configConnection['password'];
$host = $configConnection['host'];
if (empty($databaseName) || empty($username) || empty($password) || empty($host)) {
throw new LogicException(sprintf(
'Incorrect config settings:databaseName[%s] username[%s] password[%s] host[%s]',
$connectionName, $username, $password, $host));
}
$schema = DB::connection($connectionName)->getDoctrineSchemaManager();
$tableNames = $schema->listTableNames();
foreach ($tableNames as $tableName) {
$mysqlBaseCommand = sprintf('mysql -B -N -u %s -h %s -p%s -D %s -e', $username, $host, $password, $databaseName);
$mysqlCommand = sprintf("SELECT * FROM %s where created_at >= '%s 00:00:00' and created_at < '%s 00:00:00'",
$tableName,
$startDate,
$endDate
);
$outputFilePath = storage_path(sprintf('dump_csv/%s/%s.csv', $startDate, $tableName));
$cmd = sprintf('%s "%s" | sed -e "s/\t/,/g" > %s', $mysqlBaseCommand, $mysqlCommand, $outputFilePath);
exec($cmd);
$this->comment(sprintf('Completed:%s', $tableName));
}
}
}
/**
* Directory creation.
*
* @param string $directoryPath
*/
protected function createDirectory(string $directoryPath): void
{
if (! is_dir($directoryPath)) {
$result = $this->file->makeDirectory($directoryPath, 0777, true);
if (! $result) {
throw new LogicException(sprintf('Directory creation failure:%s', $directoryPath));
}
}
}
/**
* Get start date and time and end date and time.
*/
protected function getStartEndTime(): array
{
$period = $this->option('period');
if ($this->option('day')) {
$startDate = $this->option('day');
$endDate = Carbon::parse($startDate)->addDays($period)->format('Y-m-d');
} else {
$carbon = Carbon::now();
$startDate = $carbon->format('Y-m-d');
$endDate = $carbon->addDays($period)->format('Y-m-d');
}
if ($startDate > $endDate) {
return [$endDate, $startDate];
}
return [$startDate, $endDate];
}
}
解説
mysqlのコマンド部分を変数化してより汎用性ある形にしました。
出力先は日付単位でstorage直下に出力されるようにしてあります。
Laravelではconfigでデータベース関連の値を記述する仕組みとなっているため、mysqlコマンドに使う値はそこから取得しています。
また、テーブル名一覧においてもLaravelであれば下記記事の方法で取得することが可能です。
なお、CSVファイルの先頭にヘッダーを付けたい場合はmysqlコマンドの「-N」を外すだけで大丈夫です。