如何使导入脚本运行得更快

Question

我编写了一个脚本，将数据从excel文件导入数据库，但是运行时非常慢。它工作得很好，样本excel有20行，但我需要从excel有50k-100k+行导入。有一种方法，使我的脚本运行得更快吗?

这是我第一次尝试制作一个导入脚本，所以任何建议都会很感激。

<?php
namespace AppServicesImport;
use AppModelsTags;
use AppModelsVideos;
use GuzzleHttpClient;
use AppModelsStars;
use AppModelsCategories;
use IlluminateSupportStr;
use IlluminateSupportFacadesDB;
use IlluminateSupportFacadesLog;
use MaatwebsiteExcelFacadesExcel;
use IlluminateSupportFacadesStorage;
use SpatieMediaLibraryMediaCollectionsModelsMedia;
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 = 'AppModelsVideos';
$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 (GuzzleHttpExceptionConnectException $e) {
Log::error('Could not connect to ' . $row[3] . ': ' . $e->getMessage());
continue;
}
}
$video->media()->touch();
}      
}
}

Answer 1

我认为你可以加速，至少一点，你的导入通过减少脚本完成的查询数量，目前你将使至少100k查询数据库当你执行完整的导入

例如，在开始循环之前，您可以使用url作为键，模型作为值构建视频映射，如下所示:

$videoMap = [];
$videosCollection = Videos::all()->get();
foreach($videosCollection as $video){
$videoMap[$video->url] = $video;
}

在循环中，您将用isset替换$video = Videos::where('url', $row[0])->first();函数，您将得到如下内容:

$video = isset($videoMap[$row[0]]) ? $videoMap[$row[0]] : null;

在此之后，您还需要更新地图，当您在数据库中保存视频。

我希望我对你有帮助。