【Laravel,MySQL】MySQLへのinsert速度について調べてみた

#概要
本記事は、[PHPフレームワークLaravel入門 第2版](https://www.amazon.co.jp/PHP%E3%83%95%E3%83%AC%E3%83%BC%E3%83%A0%E3%83%AF%E3%83%BC%E3%82%AFLaravel%E5%85%A5%E9%96%80-%E7%AC%AC2%E7%89%88-%E6%8E%8C%E7%94%B0-%E6%B4%A5%E8%80%B6%E4%B9%83/dp/4798060992/ref=pd_lpo_1?pd_rd_i=4798060992&psc=1)で学習している中の疑問・つまづきの備忘録です。

今回はLaravelからMySQLへのinsert速度について調べたことをまとめます。
なお、[PHPフレームワークLaravel入門 第2版](https://www.amazon.co.jp/PHP%E3%83%95%E3%83%AC%E3%83%BC%E3%83%A0%E3%83%AF%E3%83%BC%E3%82%AFLaravel%E5%85%A5%E9%96%80-%E7%AC%AC2%E7%89%88-%E6%8E%8C%E7%94%B0-%E6%B4%A5%E8%80%B6%E4%B9%83/dp/4798060992/ref=pd_lpo_1?pd_rd_i=4798060992&psc=1)ではSQLiteを利用していますが、MySQLに読み替えて同じことを行なっています。


#サンプルコード
以下は[PHPフレームワークLaravel入門 第2版](https://www.amazon.co.jp/PHP%E3%83%95%E3%83%AC%E3%83%BC%E3%83%A0%E3%83%AF%E3%83%BC%E3%82%AFLaravel%E5%85%A5%E9%96%80-%E7%AC%AC2%E7%89%88-%E6%8E%8C%E7%94%B0-%E6%B4%A5%E8%80%B6%E4%B9%83/dp/4798060992/ref=pd_lpo_1?pd_rd_i=4798060992&psc=1)に記載されていた、シーディング処理です。



```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版](https://www.amazon.co.jp/PHP%E3%83%95%E3%83%AC%E3%83%BC%E3%83%A0%E3%83%AF%E3%83%BC%E3%82%AFLaravel%E5%85%A5%E9%96%80-%E7%AC%AC2%E7%89%88-%E6%8E%8C%E7%94%B0-%E6%B4%A5%E8%80%B6%E4%B9%83/dp/4798060992/ref=pd_lpo_1?pd_rd_i=4798060992&psc=1)

このコードでは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](https://e-words.jp/w/%E3%83%90%E3%83%AB%E3%82%AF%E3%82%A4%E3%83%B3%E3%82%B5%E3%83%BC%E3%83%88.html)

下のグラフは一度にinsertする件数と速度の関係を示しています。
>![mysql_インサート速度.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2475547/f4ab1250-11b7-3aaa-ba89-00fd3333f1b7.png)

>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](https://medium.com/@benmorel/high-speed-inserts-with-mysql-9d3dcd76f723)

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

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


#実際に試してみた
まずは、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版](https://www.amazon.co.jp/PHP%E3%83%95%E3%83%AC%E3%83%BC%E3%83%A0%E3%83%AF%E3%83%BC%E3%82%AFLaravel%E5%85%A5%E9%96%80-%E7%AC%AC2%E7%89%88-%E6%8E%8C%E7%94%B0-%E6%B4%A5%E8%80%B6%E4%B9%83/dp/4798060992/ref=pd_lpo_1?pd_rd_i=4798060992&psc=1)
>[バルクインサート（bulk insert）とは - IT用語辞典 e-Words](https://e-words.jp/w/%E3%83%90%E3%83%AB%E3%82%AF%E3%82%A4%E3%83%B3%E3%82%B5%E3%83%BC%E3%83%88.html)
>[High-speed inserts with MySQL](https://medium.com/@benmorel/high-speed-inserts-with-mysql-9d3dcd76f723)
>[MySQL 8.0 リファレンスマニュアル(INSERTステートメントの最適化)](https://dev.mysql.com/doc/refman/8.0/ja/insert-optimization.html)