php - 需要由 PHP 逐行处理的 CSV 需要很长时间(或超时)才能进入 SQL 数据库

Question

我有一个比较网站，每天晚上我都会在该网站上更新商店的信息。这些通常不是最大的 feed(即最多 15,000 行)，但即使只有 1000 行的 CSV feed 也可能需要很长时间，因为我必须执行(很多？)SQL 查询。这是我的程序:

• 每个 CSV Feed 来自不同的联属网络，因此具有不同的索引和结构。这就是为什么我编写了一个页面，首先将每个提要标准化为正确的结构(即一个提要中的 $row['name'] = $col[1]，但另一个提要中的 $col[2])。这些“正确”的数据将由另一个函数 CheckProduct(); 处理。
• 在 CheckProduct() 中，我首先检查产品是否已存在。如果是，那么我将更新标准信息:当前价格、库存、网址等。如果否，我将把产品插入数据库。

但是最后一点需要花费太多时间，即使是一个额外的查询也可能需要几分钟的时间。我认为不需要花这么长时间，但我一直在寻找这段代码并寻找 SQL 查询优化，但我似乎无法弄清楚当我批量或组合 CSV 时如何保留此过程行。我已经尝试优化查询(使用 COUNT() 而不是 num_rows，仅选择我需要的列等)。

我不是编程专家，但我知道一定有不同的选择。我知道您可以将内容存储在数组中，然后循环它，或者使用 LOAD DATA INFILE，但我不知道在这种情况下如何编程(并且仍然对每一行进行相同类型的严格检查)。换句话说:如何优化这段代码？

这是函数 CheckProduct()

    function CheckProduct($SiteID, $StoreID, $FeedID, $Name, $URL, $Description, $EANSKU, $Image, $Brand, $Color, $Price, $CategoryPath, $Stock, $Deliverycosts, $Deliverytime, $Length, $Width, $Depth, $Height, $Material)
{
    global $db;

    if ( !filter_var($URL, FILTER_VALIDATE_URL) === false && !filter_var($Image, FILTER_VALIDATE_URL) === false && !empty($Name) && !empty($URL) && !empty($Price) && !empty($Image) ) {

        $sCountProduct = $db->query("SELECT COUNT(*) as total FROM furniture WHERE name_slug='".CreateSlug(trim($db->real_escape_string($Naam)))."' AND feed_id='".$FeedID."' LIMIT 1");

            $fCountProduct = $sCountProduct->fetch_assoc();
            if($fCountProduct['total'] == '0') {

                $iProduct = $db->query("INSERT INTO furniture (site_id,feed_id,store_id,name,name_slug,affiliate_url,description,ean_sku,image_big,brand,brand_slug,color,color_slug,price,category_path,in_stock,visible,shipping_costs,check_today,last_update,deliverytime,length,width,depth,height,material,material_slug,added) VALUES ('1','".$FeedID."','".$StoreID."','".trim($db->real_escape_string($Name))."','".CreateSlug(trim($db->real_escape_string($Name)))."','".trim($AffiliateURL)."','".$db->real_escape_string(trim($Description))."', '".$db->real_escape_string(trim($EANSKU))."','".trim($Afbeelding)."','".$db->real_escape_string(trim($Brand))."','".CreateSlug($db->real_escape_string(trim($Brand)))."','".$db->real_escape_string(trim($Color))."','".CreateSlug($db->real_escape_string(trim($Color)))."','".$db->real_escape_string(trim($Price))."','".$db->real_escape_string(trim(strtolower($CategoryPath)))."','".$Stock."', '3','".$db->real_escape_string(trim($Deliverycosts))."','1','".time()."','".$db->real_escape_string(trim($Deliverytime))."','".$db->real_escape_string(trim($Length))."','".$db->real_escape_string(trim($Width))."','".$db->real_escape_string(trim($Depth))."','".$db->real_escape_string(trim($Height))."','".$db->real_escape_string(trim($Material))."','".CreateSlug($db->real_escape_string(trim($Material)))."','".date('d-m-Y')."')");

                $IDProduct = $db->insert_id;

                if($iProduct) {
                    $dOthers = $vm->query("DELETE FROM furniture WHERE name_slug='".CreateSlug(trim($db->real_escape_string($Name)))."' AND id != '".$IDProduct."' AND feed_id='".$FeedID."' AND visible != '1'");
                }
            }
            else {
                $sExistProduct = $db->query("SELECT id,site_id,name_slug,feed_id,price_old,visible,price FROM furniture WHERE site_id='1' AND name_slug='".CreateSlug(trim($db->real_escape_string($Name)))."' AND feed_id='".$FeedID."' LIMIT 1");

                if(!$sExistProduct) { }
                else {
                    // Check if it is a salesproduct
                $fExistProduct = $sExistProduct->fetch_assoc();
                $OudePrijs = $fExistProduct['price_old'];
                $Zichtbaar = $fExistProduct['visible'];

                if($fExistProduct['visible'] == '2'){ $Visible = '1'; }             
                if($Price < $fExistProduct['price']){ $OldPrice = $fExistProduct['price']; }

                $uProduct = $db->query("UPDATE furniture SET affiliate_url='".trim($URL)."', description='".$db->real_escape_string(trim($Description))."', price='".$db->real_escape_string(trim($Price))."', price_old='".$db->real_escape_string(trim($OldPrice))."', in_stock='".$Stock."', shipping_costs='".$db->real_escape_string(trim($Deliverycosts))."', check_today='1', last_update='".time()."', deliverytime='".$Deliverytime."', visible='".$Visible."' WHERE id='".$fExistProduct['id']."' LIMIT 1");

                    if($uProduct) {
                    // Updated
                    }
                }
            }
    }
    else
    {
        // Error in image (not every image send by the feed is a good one)
    } 
}

这就是我处理其中一个 Feed 以匹配正确列的方式:

    $SiteID = '1';
$FeedID = '1';
$StoreID = '1';

$Link = 'URL';

if (($handle = fopen($Link, "r")) !== FALSE) {
    fgetcsv($handle);
    $i = 0;

    while (($data = fgetcsv($handle, 6000, ";")) !== FALSE) {
        $num = count($data);
        for ($c=0; $c < $num; $c++){ $col[$c] = $data[$c]; }

        ### VARIABLES FROM FEED ###
        $Name = $col[1]; 
        $URL = $col[5]; 
        $Description = strip_tags($col[4]); 
        $EANSKU = $col[0]; 
        $Image = $col[6]; 
        $Brand = $col[8]; 
        $Color = ''; 
        $Price = $col[3]; 
        $CategoryPath = $col[9]; 
        $Stock = $col[10]; 
        $Deliverycosts = $col[14]; 
        $Deliverytime = '';

        $Length = '';
        $Width = '';
        $Height = '';
        $Depth = ''; 
        $Material = ''; 

        ### STOCK ###
        if($Stock > 0) {
            $Stock = '1';
        }
        else {
            $Stock = '0';
        }

        ### SEND PRODUCT ###
        CheckProduct($SiteID, $StoreID, $FeedID, $Name, $URL, $Description, $EANSKU, $Image, $Brand, $Color, $Price, $CategoryPath, $Stock, $Deliverycosts, $Deliverytime, $Length, $Width, $Depth, $Height, $Material);
        $i++;

    }

    fclose($handle);
}

我使用的是 PHP 5.6.32 和 MySQL 5.0.11。

Answer 1

SELECT COUNT(*) 不需要限制...

只需预先选择数据:从家具组中选择 name_slug,feed_id, COUNT(*) as cnt as Total BY name_slug,feed_id");

从家具中选择 name_slug,feed_id, id,site_id,name_slug,feed_id,price_old,visible,price WHERE site_id='1'

并将它们用作查找表。

然后批量插入/删除/更新，整个脚本应该需要不到几秒。

或者全部在 mysql 中完成...