概要

本記事は、PHPフレームワークLaravel入門第2版で学習している中の疑問・つまづきの備忘録です。

今回はLaravelからMySQLへのinsert速度について調べたことをまとめます。
なお、PHPフレームワークLaravel入門第2版ではSQLiteを利用していますが、MySQLに読み替えて同じことを行なっています。

サンプルコード

以下はPHPフレームワークLaravel入門第2版に記載されていた、シーディング処理です。

PeopleTableSeeder.php

public function run()
{
    $param = [
        'name' => 'taro',
        'mail' => 'taro@yamada.jp',
        'age' => '12',
    ];
    DB::table('people')->insert($param);

    $param = [
        'name' => 'hanako',
        'mail' => 'hanako@flower.jp',
        'age' => '34',
    ];
    DB::table('people')->insert($param);

    $param = [
        'name' => 'sachiko',
        'mail' => 'sachiko@happy.jp',
        'age' => '56',
    ];
    DB::table('people')->insert($param);
}

PHPフレームワークLaravel入門第2版

このコードではinsert()メソッドが同じように複数回呼ばれています。
冗長で効率が悪く見えたので見えたので次のコードも試してみたところ、同様に動作しました。

PeopleTableSeeder.php

public function run()
{
    $param1 = [
        'name' => 'taro',
        'mail' => 'taro@yamada.jp',
        'age' => '12',
    ];

    $param2 = [
        'name' => 'hanako',
        'mail' => 'hanako@flower.jp',
        'age' => '34',
    ];

    $param3 = [
        'name' => 'sachiko',
        'mail' => 'sachiko@happy.jp',
        'age' => '56',
    ];

    //ここを変更
    $insert_params = [$param1, $param2, $param3];
    //insertの呼び出しが１回で済む
    DB::table('people')->insert($insert_params);
}

結局どちらがいいのか

コードのきれいさでは後者の方がよさそうですが、大量のレコードをinsertする場合はどちらの方が効率がいいのかを調査しました。

その結果、MySQLの場合は、一度に複数レコードをinsertする方が高速に処理できるようです。
また、このことをバルクインサートと言います。

バルクインサートとは、リレーショナルデータベース（RDB）のテーブルに行を追加する際、複数の行を一回のSQL文の実行で追加すること。 INSERT文に複数行のデータを列挙する
バルクインサート（bulk insert）とは - IT用語辞典 e-Words

下のグラフは一度にinsertする件数と速度の関係を示しています。

It takes around 1,000 inserts per query to reach the maximum throughput in both cases, but 40 inserts per query are enough to achieve 90% of this throughput on localhost, which could be a good tradeoff here. It’s also important to note that after a peak, the performance actually decreases as you throw in more inserts per query.
High-speed inserts with MySQL

従って、MySQLでは一度に1000レコード程度をinsertした場合に最速となります。特にlocalhostの場合は、一度に40レコード程度で最速の90%の速度が出るようです。
一方で1000レコード以上に増やしていくと、徐々に速度が落ちていきます。
MySQLのドキュメントにも以下のような記載があります。

同じクライアントから同時に多数の行を挿入する場合は、複数の VALUES リストで INSERT ステートメントを使用して、同時に複数の行を挿入します。これは、個別の単一行の INSERT ステートメントを使用するより、大幅に (場合によっては数倍) 速くなります。
MySQL 8.0 リファレンスマニュアル(INSERTステートメントの最適化)

実際に試してみた

まずは、10000レコードを１レコードごとにinsertしてみました。

PeopleTableSeeder.php

public function run()
{
    for ($i = 0; $i < 10000; $i++) {
        $param = [
            'name' => "name{$i}",
            'mail' => "test{$i}@example.com",
            'age' => '30',
        ];
        DB::table('people')->insert($param);
    }
}

$ ./vendor/bin/sail artisan db:seed

Seeding: Database\Seeders\PeopleTableSeeder
Seeded:  Database\Seeders\PeopleTableSeeder (67,399.28ms)
Database seeding completed successfully.

結果は67秒程度。
次に、10000レコードを1000ずつ分割してinsertしました。

PeopleTableSeeder.php

public function run()
{
    $params = [];

    for ($i = 0; $i < 10000; $i++) {
        $param = [
            'name' => "name{$i}",
            'mail' => "test{$i}@example.com",
            'age' => '30',
        ];
        $params[$i] = $param;

        if (count($params) >= 1000) {
            DB::table('people')->insert($params);
            $params = [];
        }
    }
}

$ ./vendor/bin/sail artisan db:seed

Seeding: Database\Seeders\PeopleTableSeeder
Seeded:  Database\Seeders\PeopleTableSeeder (236.20ms)
Database seeding completed successfully.

速い！！

こちらは1秒以下で終了しました。
10000レコードでこの差なので、件数が増えるとさらに影響が大きくなりそうですね。

まとめ

MySQLにおいてレコードをinsertする際は、1000レコード程度をバルクインサートするのが最も高速になります。
実際に試してみて、insert処理１つでこれほどの差が出て感動しました。
特に大規模なアプリになると、このようなDBとのやりとりの実装方法も重要になると実感できたので、今後に活かそうと思います。

参考文献

PHPフレームワークLaravel入門第2版
 バルクインサート（bulk insert）とは - IT用語辞典 e-Words
High-speed inserts with MySQL
MySQL 8.0 リファレンスマニュアル(INSERTステートメントの最適化)