laravel+mysql 大量の行があるcsvファイルのSeederをBulk insertで高速化した話。

以前、大量の行があるcsvデータをSeederで高速に挿入したいことがあって・・・

laravelなどのフレームワークでcsvファイルで提供されたマスタリストなどを、DBにシーディングとして取り込むこと、よくありますよね。

100行程度のcsvであれば、１行づつINSERTしてもそんなに時間はかからないのですが、これが数千行、数万行となってくると、シーディングの時間だけで結構待たされます。

開発環境で頻繁にDBのリセット、シーディングのやり直しをしているとその度長い時間待たされてイライラしませんか？

それを解決するために、mysqlのinsertを高速に動作させるBulk insertという手法があります。

Bluk insertとは

要は、一度のINSERT文に複数の行を含めてしまうというSQLです。

INSERT INTO table_name values('1','hoge'), ('2','huga'), ('3','exam');

これを使って複数行づつINSERTすると、かなり速度が変わります。
と書いてある記事が多いのですが、実際どれぐらい変わるのでしょうか。

実際に比較してみた

1行の文字数が100文字程度、区切りが9個、
26406行あるcsvを使って試してみました。

1行づつinsert

list total lines:26406 inserts:26406
Seeded: listSeeder (135.77 seconds)

10行づつinsert

list total lines:26406 inserts:26406
Seeded: listSeeder (15.26 seconds)

100行づつinsert

list total lines:26406 inserts:26406
Seeded: listSeeder (3.4 seconds)

1000行づつinsert（最速）

list total lines:26406 inserts:26406
Seeded: listSeeder (2.74 seconds)

5000行づつinsert

list total lines:26406 inserts:26406
Seeded: listSeeder (3.2 seconds)

5000行まで増やすと、逆に遅くなってしまいました。
使うcsvの1行あたりのデータ量やDBの処理能力にもよりますが、100〜1000行程度が一番効率が良いようです。

ええい！サンプルコードはないのか！サンプルは！

ここまでの情報であれば、ネットにいくらでもあったのですが、実際に行をまとめてINSERTするlaravelのシーディングのサンプルコードが見つかりませんでした。。。

以下は実際にcsvファイルをSplFileObjectでインポートしつつバルクインサートして高速にシーディングするサンプルだよ！ほぼコピペでそのまま使えちゃうよ！

実際のコード

<?php

use App\XXXXXXXXlist;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

class XXXXXXXXlist extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $file = new SplFileObject('database/csv/すごくたくさん行のある.csv');
        $file->setFlags(
            \SplFileObject::READ_CSV |
                \SplFileObject::READ_AHEAD |
                \SplFileObject::SKIP_EMPTY |
                \SplFileObject::DROP_NEW_LINE
        );
        $lists = [];
        foreach ($file as $i => $line) {
            if ($i > 0) {
                // お使いのcsvに合わせて区切り設定やカラム名を変えてくださいね
                $lists[] = [
                    'aaa' => $line[0],
                    'bbb' => $line[1],
                    'ccc' => $line[2],
                    'ddd' => $line[3],
                    'eee' => $line[4],
                    'fff' => $line[5],
                    'ggg' => $line[6],
                    'hhh' => $line[7],
                    'iii' => $line[8],
                ];
            }
        }
        // トランザクションをまとめることで若干高速化します
        DB::transaction(function () use ($lists) {
            $pack = [];
            $inserts = 0;
            foreach($lists as $list){
                $pack[] = $list;
                // ここでまとめる行数を選びます。
                if(count($pack) >= 1000){
                    $inserts += count($pack);
                    // BULK INSERT SQLを発行します
                    XXXXXXXXlist::insert($pack);
                    $pack = [];
                }
            }
            $inserts += count($pack);

            // 余った行のBULK INSERT SQLを発行します
            XXXXXXXXlist::insert($pack);

            // 念のため、実際にINSERTした行数とcsvの行数を確認すると良いでしょう。
            $this->command->info("list total lines:" . count($lists) . " inserts:". $inserts);
        });
    }
}

それでは良いlaravelライフをお過ごしください。

関連記事

https://qiita.com/k_eita/items/a8ec42c7bbb22846b603
https://qiita.com/buntafujikawa/items/91283160b0a38956655e