概要
本記事は、PHPフレームワークLaravel入門 第2版で学習している中の疑問・つまづきの備忘録です。
今回はLaravelからMySQLへのinsert速度について調べたことをまとめます。
なお、PHPフレームワークLaravel入門 第2版ではSQLiteを利用していますが、MySQLに読み替えて同じことを行なっています。
サンプルコード
以下はPHPフレームワークLaravel入門 第2版に記載されていた、シーディング処理です。
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);
}
このコードではinsert()メソッドが同じように複数回呼ばれています。
冗長で効率が悪く見えたので見えたので次のコードも試してみたところ、同様に動作しました。
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の呼び出しが1回で済む
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レコードを1レコードごとにinsertしてみました。
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しました。
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処理1つでこれほどの差が出て感動しました。
特に大規模なアプリになると、このようなDBとのやりとりの実装方法も重要になると実感できたので、今後に活かそうと思います。
参考文献
PHPフレームワークLaravel入門 第2版
バルクインサート(bulk insert)とは - IT用語辞典 e-Words
High-speed inserts with MySQL
MySQL 8.0 リファレンスマニュアル(INSERTステートメントの最適化)