bulk insertで大量のデータをDBに登録する

毎日走らせているcronのジョブの１つが、なかなか時間が掛かるのでどうにか改善できないかと悩んでいました。DBへデータを挿入する箇所で時間が掛かっており、コードを確認するとforループで１レコードずつinsert処理を行っていました。bulk insertするように改修したところ、劇的に処理時間が短くなりました。今回はそんな妙薬、bulk insertについてです。

Bulk insertとは？

bulk insertとはDBにレコードを保存する際に、複数のレコードを１クエリでまとめて挿入する方法です。１レコードずつクエリを発行するよりも効率的で高速です。

Laravelでは以下のようにクエリビルダのinsertメソッドを使い実装します。

DB::table('users')->insert([
    ['name' => 'John', 'email' => 'john@example.com', 'password' => 'testtest'],
    ['name' => 'Ben', 'email' => 'ben@example.com', 'password' => 'testtest'],
    ['name' => 'Tyler', 'email' => 'tyler@example.com', 'password' => 'testtest'],
]);

１レコードずつ挿入した場合とbulk insertを使った場合でどれ位処理時間が異なるのでしょうか？seederでusersテーブルに10万件のダミーレコードを登録するケースで比較してみます。

１レコードずつinsertする場合

※Laravelインストール後、DB接続設定やmigration実行完了時点の状態から進めています。

まずはseederを用意します。以下のコマンドでUsersSeederを作成してください。

php artisan make:seeder UsersSeeder

作成されたUsersSeederクラスを以下の様に編集します。

use App\User;
use Faker\Factory as Faker;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\Hash;

class UsersSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $faker = Faker::create('ja_JP');

        for ($i=0; $i < 100000; $i++) { 
            $param = [ 
                'name' => $faker->name(),
                'email' => "test{$i}@example.com",
                'password' => 'testtest',
            ];
            User::create($param);
        }
    }
}

上記のコードではforループ内でcreateメソッドを呼んでおり、10万回ループ毎にDBにレコードが挿入されます。

emailにはループカウンターの$iが使用されています、これはusersテーブルに挿入する際にemailがユニークである必要があるためです。fakerのuniqueメソッドを使えばユニークなemailを生成することが出来ますが、10万件となると重複が発生し失敗してしまいます。

seederを追加したら以下のコマンドでcomposerのオートローダを再生成し、UsersSeederが読み込まれるようにしましょう。

$ composer dump-autoload

DatabaseSeeder.php も編集し、db:seedでUsersSeederを呼び出すようにします。コマンド実行毎にusersテーブルをtruncateメソッドで初期化し、挿入するemailが重複しないようにしています。

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

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database. 
     * 
     * @return void 
     */ 
    public function run()
    { 
        DB::table('users')->truncate();
        $this->call(UsersSeeder::class);
    }
}

これで準備が出来ました、db:seedコマンドを実行してみましょう。

$ php artisan db:seed
Seeding: UsersSeeder
Seeded: UsersSeeder (157.48 seconds)
Database seeding completed successfully.

上記はM1 MacBookAirでの実行で、約２分半掛かりました。また、Windows + VirtualBoxの仮想環境では約２０分掛かっていました。記事の内容とは関係無いですが、Appleシリコン速いですね！

bulk insertの場合

次に、UsersSeederを編集してbulk insertにしてみましょう。bulk insertにするには一度、挿入するデータを連想配列で用意し、insertメソッドに渡します。

...
public function run()
{ 
    $faker = Faker::create('ja_JP');

    $params = [];

    for ($i=0; $i < 100000; $i++) { 
        $params[] = [ 
            'name' => $faker->name(),
            'email' => "test{$i}@example.com",
            'password' => 'testtest',
        ];
    } 

    User::insert($params);
}

db:seedを実行してみましょう。

$ php artisan db:seed 
Seeding: UsersSeeder
PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4194312 bytes) in ...

流石に10万件のデータを一つの変数に格納するとメモリの使用上限に達し、エラーが発生してしまいました。（memory_limitは初期設定の128MBです）

ini_set()でmemory_limitを上げても良いですが、一体どれだけメモリを食うのか分かりません。1000件毎にinsertメソッドを実行、$paramsを初期化し、メモリ使用量を抑える事にしました。以下が修正を加えたコードです。

...
public function run()
{ 
    $faker = Faker::create('ja_JP');

    $params = [];

    for ($i=0; $i < 100000; $i++) { 
        $params[] = [ 
            'name' => $faker->name(),
            'email' => "test{$i}@example.com",
            'password' => 'testtest',
        ];

        if (count($params) >= 1000) {
            User::insert($params);
            $params = [];
        }
    } 
}

蛇足ですが、こちらの記事、High-speed inserts with MySQLにて

It takes around 1,000 inserts per query to reach the maximum throughput

と言及されているように、bulk insertの場合は１クエリ辺り1000件のインサートが最も効率が良いらしいです。

再度、db:seedを実行してみましょう。

$ php artisan db:seed
Seeding: UsersSeeder
Seeded:  UsersSeeder (5.8 seconds)
Database seeding completed successfully.

約２分半掛かっていたのが、約６秒です。約２０分掛かっていた Windows + VirtualBox の環境でも約６秒でした。すごい差です！

created_at, updated_atは自動入力されない

一点注意が必要なのは、bulk insertではModelクラスにて$timestampsをtrueに設定していても、created_atやupdated_atは自動入力されません。なぜならinsertメソッドはEloquent側ではなく、クエリビルダ側のメソッドだからです。したがって、insertするパラメタに明示的に含める必要があります。

まとめ

大量のレコードを挿入する場合、１レコードずつではなく、bulk insertで一括挿入することでとても速く処理できます。これは逆にDBへのクエリ発行がプログラムにおいて如何に重い処理か、という事を示しています。普段コードを書く際にも無駄なクエリを発行していないか気をつけねば、と思った次第です。