如何提速Excel到数据库的PHP导入脚本？

问题背景 #

我写了一个PHP脚本用来把Excel数据导入数据库，20行的测试文件运行正常，但处理50k-100k+行的大文件时速度极慢。这是我第一次写导入脚本，求优化提速的建议，当前代码如下：

<?php

namespace App\Services\Import;

use App\Models\Tags;
use App\Models\Videos;
use GuzzleHttp\Client;
use App\Models\Stars;
use App\Models\Categories;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Storage;
use Spatie\MediaLibrary\MediaCollections\Models\Media;

class DataImport
{
    public function import()
    {
        $path = Storage::disk('public')->path('database.xlsx'); 
        $data = Excel::toArray([], $path); 

        for ($i = 1; $i < count($data[0]); $i++) { 
            $row = $data[0][$i]; 

            $video = Videos::where('url', $row[0])->first(); 
            if(!$video){ 

                $slug = Str::slug($row[1], '-');
                $i = 1;
                while(Videos::where('slug', $slug)->exists()) {
                    $slug = Str::slug($row[1], '-') . '-' . $i;
                    $i++;
                }

                $video = new Videos();
                $video->url = $row[0]; 
                $video->title = $row[1]; 
                $video->slug = $slug;
                $video->length = intval($row[2]); 
                $video->code = intval($row[7]); 
                $video->embed_url = $row[4]; 
                $video->channel = $row[9]; 
                $video->save(); 
            } 

            $title = str_replace('_', ' ', $row[8]);
            $slug = strtolower(str_replace(' ', '-', $title));
            $title = ucfirst(strtolower($title));
            $category = Categories::firstOrCreate(['title' => $title], ['slug' => $slug]);
            $video->categories()->syncWithoutDetaching([$category->id]);

            $names = explode(',', $row[6]);
            foreach ($names as $name) {
                $name = trim($name);
                $slug = strtolower(str_replace(' ', '-', $name));
                $star = Stars::firstOrCreate(['name' => $name], ['slug' => $slug]);
                $video->stars()->syncWithoutDetaching([$star->id]);
            }

            $tags = explode(',', $row[5]);
            foreach ($tags as $tagName) {
                $tagName = str_replace('-', ' ', $tagName);
                $tag = Tags::firstOrCreate(['name' => $tagName, 'slug' => Str::slug($tagName)]);
                $tagIds[] = $tag->id;
            }
            $video->tags()->syncWithoutDetaching($tagIds);

            $media = Media::where('file_name', $row[3])->first();
            if (!$media) {
                $media = new Media();
                $filename = basename($row[3]);
                $media->file_name = $filename;
                $media->model_type = 'App\Models\Videos';
                $media->model_id = $video->id;
                $media->collection_name = 'video_index_images';
                $media->name = pathinfo($row[3], PATHINFO_FILENAME);
                $media->disk = 'public';
                $media->conversions_disk = 'public';
                $media->manipulations = [];
                $media->custom_properties = [];
                $generatedConversionsString = '{"video_index_images":true}';
                $generatedConversionsStringNoSlashes = stripslashes($generatedConversionsString);
                $generatedConversions = json_decode($generatedConversionsStringNoSlashes, true);
                $media->generated_conversions = $generatedConversions;
                $media->responsive_images = [];
                $media->mime_type = 'image/jpeg';

                try {
                    $client = new Client();
                    $response = $client->get($row[3], ['http_errors' => false]);
                    $image = (string) $response->getBody();
                    $size = strlen($image);
                    $media->size = $size;
                    $media->uuid = (string) Str::uuid();

                    $media->save();

                    $storagePath = $media->id . '/';
                    Storage::disk('public')->put($storagePath  . $media->file_name, $image);
                } catch (\GuzzleHttp\Exception\ConnectException $e) {
                    Log::error('Could not connect to ' . $row[3] . ': ' . $e->getMessage());
                    continue;
                }
            }
            $video->media()->touch();
        }      
    }
}

核心优化方案 #

1. 分批读取Excel数据，避免内存溢出 #

原代码用toArray一次性把整个Excel加载到内存，大文件会导致内存占用过高且处理缓慢。改用Maatwebsite Excel的分块读取功能，每次处理1000行（可根据服务器配置调整）：

public function import()
{
    $path = Storage::disk('public')->path('database.xlsx'); 
    // 分块读取，每次处理1000行
    Excel::filter('chunk')->load($path)->chunk(1000, function ($rows) {
        // 每一批行的处理逻辑放在这里
        DB::beginTransaction();
        try {
            foreach ($rows as $row) {
                // 原有的单条数据处理逻辑
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Batch import failed: ' . $e->getMessage());
        }
    });
}

2. 缓存已存在的模型，减少重复数据库查询 #

原代码每行都执行多次where->first()、firstOrCreate，大量重复查询会拖慢速度。用数组缓存已查询过的模型，比如视频URL、分类、明星、标签：

public function import()
{
    $path = Storage::disk('public')->path('database.xlsx'); 
    // 初始化缓存数组
    $existingVideos = [];
    $existingCategories = [];
    $existingStars = [];
    $existingTags = [];

    Excel::filter('chunk')->load($path)->chunk(1000, function ($rows) use (&$existingVideos, &$existingCategories, &$existingStars, &$existingTags) {
        DB::beginTransaction();
        try {
            foreach ($rows as $row) {
                // 缓存视频URL，避免重复查询
                $videoUrl = $row[0];
                if (!isset($existingVideos[$videoUrl])) {
                    $existingVideos[$videoUrl] = Videos::where('url', $videoUrl)->first();
                }
                $video = $existingVideos[$videoUrl];

                if(!$video){ 
                    // ... 原有创建视频逻辑
                    $video->save();
                    $existingVideos[$videoUrl] = $video;
                } 

                // 缓存分类
                $categoryTitle = ucfirst(strtolower(str_replace('_', ' ', $row[8])));
                if (!isset($existingCategories[$categoryTitle])) {
                    $slug = strtolower(str_replace(' ', '-', $categoryTitle));
                    $existingCategories[$categoryTitle] = Categories::firstOrCreate(['title' => $categoryTitle], ['slug' => $slug]);
                }
                $category = $existingCategories[$categoryTitle];
                $video->categories()->syncWithoutDetaching([$category->id]);

                // 缓存明星
                $names = explode(',', $row[6]);
                $starIds = [];
                foreach ($names as $name) {
                    $name = trim($name);
                    if (!isset($existingStars[$name])) {
                        $slug = strtolower(str_replace(' ', '-', $name));
                        $existingStars[$name] = Stars::firstOrCreate(['name' => $name], ['slug' => $slug]);
                    }
                    $starIds[] = $existingStars[$name]->id;
                }
                $video->stars()->syncWithoutDetaching($starIds);

                // 缓存标签
                $tags = explode(',', $row[5]);
                $tagIds = [];
                foreach ($tags as $tagName) {
                    $tagName = str_replace('-', ' ', $tagName);
                    if (!isset($existingTags[$tagName])) {
                        $existingTags[$tagName] = Tags::firstOrCreate(['name' => $tagName, 'slug' => Str::slug($tagName)]);
                    }
                    $tagIds[] = $existingTags[$tagName]->id;
                }
                $video->tags()->syncWithoutDetaching($tagIds);

                // ... 图片处理逻辑
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Batch import failed: ' . $e->getMessage());
        }
    });
}

3. 异步处理图片下载，避免IO阻塞 #

原代码同步下载图片是最大的性能瓶颈之一，改用Laravel队列异步处理：

1. 创建一个DownloadImageJob队列任务：

<?php

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use GuzzleHttp\Client;
use Spatie\MediaLibrary\MediaCollections\Models\Media;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\Log;

class DownloadImageJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected $imageUrl;
    protected $videoId;

    public function __construct($imageUrl, $videoId)
    {
        $this->imageUrl = $imageUrl;
        $this->videoId = $videoId;
    }

    public function handle()
    {
        $filename = basename($this->imageUrl);
        $media = Media::where('file_name', $filename)->first();
        if ($media) return;

        try {
            $client = new Client();
            $response = $client->get($this->imageUrl, ['http_errors' => false]);
            $image = (string) $response->getBody();
            $size = strlen($image);

            $media = new Media();
            $media->file_name = $filename;
            $media->model_type = 'App\Models\Videos';
            $media->model_id = $this->videoId;
            $media->collection_name = 'video_index_images';
            $media->name = pathinfo($this->imageUrl, PATHINFO_FILENAME);
            $media->disk = 'public';
            $media->conversions_disk = 'public';
            $media->manipulations = [];
            $media->custom_properties = [];
            $media->generated_conversions = ["video_index_images" => true];
            $media->responsive_images = [];
            $media->mime_type = 'image/jpeg';
            $media->size = $size;
            $media->uuid = (string) Str::uuid();
            $media->save();

            $storagePath = $media->id . '/';
            Storage::disk('public')->put($storagePath . $media->file_name, $image);
        } catch (\GuzzleHttp\Exception\ConnectException $e) {
            Log::error('Could not connect to ' . $this->imageUrl . ': ' . $e->getMessage());
        }
    }
}

2. 在导入脚本中替换图片处理逻辑，分发队列任务：

// 原有的图片处理逻辑替换为：
$imageUrl = $row[3];
$filename = basename($imageUrl);
$media = Media::where('file_name', $filename)->first();
if (!$media) {
    // 分发异步任务
    DownloadImageJob::dispatch($imageUrl, $video->id);
}

4. 优化Slug生成逻辑 #

原代码循环查询数据库生成Slug效率极低，改用数据库唯一索引+异常捕获：

// 替换原有的Slug生成逻辑
$slug = Str::slug($row[1], '-');
try {
    $video->slug = $slug;
    $video->save();
} catch (\Illuminate\Database\QueryException $e) {
    // 捕获唯一索引冲突异常，添加随机后缀
    $slug = $slug . '-' . Str::random(4);
    $video->slug = $slug;
    $video->save();
}

前提：给videos表的slug字段添加唯一索引。

5. 临时禁用模型事件和观察者 #

如果你的模型（比如Videos、Categories）注册了事件监听或观察者，导入时会触发大量不必要的逻辑，可临时禁用：

// 在创建视频的逻辑外包裹
Videos::withoutEvents(function () use ($video) {
    $video->save();
});

6. 使用批量插入关联数据 #

对于关联关系，比如视频和分类、明星、标签的关联，可以收集一批关联数据后批量插入，而不是每行单独syncWithoutDetaching。例如，收集所有视频-分类的关联对，最后批量插入中间表：

// 在分块处理中初始化关联数组
$videoCategoryRelations = [];

foreach ($rows as $row) {
    // ... 获取video和category后
    $videoCategoryRelations[] = [
        'video_id' => $video->id,
        'category_id' => $category->id
    ];
}

// 批量插入中间表（需确保表名正确，且无重复数据）
DB::table('category_video')->insertOrIgnore($videoCategoryRelations);

注意：insertOrIgnore需要数据库支持唯一索引（比如video_id和category_id的联合唯一索引）。

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