Laravel 5.6快速插入200+条关联表数据的优化方法咨询

表结构 #

vacancies 表 #

• id：int unsigned 自增主键
• code：varchar(191) 可空
• start_time：timestamp 可空
• end_time：timestamp 可空
• business_unit_id：int unsigned
• job_id：int unsigned
• on_date：date
• break：int 可空
• shift_id：int unsigned
• total_numbers：int

vacancy_details 表 #

• id：int unsigned 自增主键
• vacancy_id：int unsigned
• day_id：int unsigned
• shift_id：int unsigned
• rate_type：enum('flat_rate','hourly_rate') 默认值为hourly_rate
• start_time：timestamp 可空
• end_time：timestamp 可空
• break：int 可空
• total_hours：decimal(10,2) 可空
• client_rate：decimal(8,2) 可空

vacancy_staff_rates 表 #

• id：int unsigned 自增主键
• vacancy_id：int unsigned
• vacancy_detail_id：int unsigned 可空
• employment_type_id：int unsigned
• staff_rate：decimal(8,2) 可空

现有实现代码 #

public function store_multiple($subdomain, Request $request){
    if($request){
        $payloads = $request->data;
        foreach($payloads as $payload){
            $payload = (object) $payload;
            // 3张表的插入逻辑、所有计算和业务逻辑都在add_vacancy()方法中执行
            $vacancy = Vacancy::add_vacancy($payload);
        }
    }
}

优化方案 #

1. 禁用查询日志 #

Laravel默认开启的查询日志会记录所有执行的SQL语句，大量插入时会占用额外内存拖慢速度，在代码开头关闭即可：

DB::disableQueryLog();

2. 包裹事务减少提交开销 #

默认情况下MySQL每次写入都会自动提交事务，200条数据就会产生至少600次事务提交（3张表各插一次），把整个插入逻辑包裹在一个事务里可以大幅降低IO开销，同时还能保证数据一致性：

public function store_multiple($subdomain, Request $request){
    if(!$request->data) return;
    $payloads = $request->data;
    
    DB::beginTransaction();
    try {
        foreach($payloads as $payload){
            $payload = (object) $payload;
            Vacancy::add_vacancy($payload);
        }
        DB::commit();
    } catch (\Exception $e) {
        DB::rollBack();
        throw $e;
    }
}

仅这一步就能提升3-5倍的插入速度。

3. 批量插入替代单次插入 #

原来的逻辑是每条数据单独插入3张表，大量数据时ORM的实例化开销、SQL请求开销都会被放大。可以分批次批量写入：

• 第一步先遍历所有payload，组装所有vacancies的写入数组，批量插入后获取所有生成的vacancy_id
• 第二步组装所有vacancy_details的写入数组，绑定对应的vacancy_id后批量插入，再获取所有vacancy_detail_id
• 第三步组装所有vacancy_staff_rates的写入数组，绑定对应ID后批量插入

注意Laravel的insert()方法不会触发模型事件，也不会自动维护created_at/updated_at字段，需要手动添加。如果需要获取批量插入的自增ID，在没有并发写入的场景下可以通过lastInsertId()获取第一个ID，按插入顺序推导所有ID；高并发场景可以提前给每条vacancy生成唯一的code字段，插入后批量查询code和id的映射关系即可。

4. 移除冗余的数据库查询 #

检查add_vacancy()方法内的逻辑，把重复查询的公共配置、关联数据提前查好缓存到数组中，避免循环内产生N+1查询问题。

5. 异步队列处理（可选） #

如果对插入实时性要求不高，可以把插入请求全部丢到异步队列中执行，前端直接返回提交成功，后台慢慢处理写入，完全避免用户等待。

6. 临时禁用模型事件（可选） #

如果add_vacancy()中用到了模型的creating/created等事件但批量插入时不需要触发，可以临时禁用事件，或者直接用DB::insert()替代ORM写入，进一步降低开销。

内容的提问来源于stack exchange，提问作者Prasanth Premzon