mysql - 在 Perl 中使用 CSV 更新 MYSQL 数据库？

Question

我正在尝试使用 perl 更新 mysql 数据库。我尝试了不同的方法，但它们都非常慢。更新 14k 条记录需要 10-15 分钟。我认为最好的方法是创建存储过程并从 perl 文件调用它，但响应时间仍然相同。

elsif($update eq "incrementalLoad") {
    $filename = param("customPricing");
    $handle = upload("customPricing");
    while (<$handle>)  {                
        $currentRecord++;
        @currentLine = split( /,/, $_ );
        $i = 0;
        foreach $l(@currentLine){
            $currentLine[$i] =~ s/\\r//g; 
            $i++;           
        }       
        $query = "CALL upsertIncremental('$currentLine[0]', '$currentLine[1]', '$currentLine[2]', '$currentLine[3]', '$currentLine[4]', '$currentLine[5]', '$currentLine[6]', '$currentLine[7]', '$currentLine[8]', '$currentLine[9]', '$currentLine[10]', '$currentLine[11]', '$currentLine[12]', '$currentLine[13]', '$currentLine[14]', '$currentLine[15]', '$currentLine[16]', '$currentLine[17]', '$currentLine[18]', '$currentLine[19]', '$currentLine[20]', '$currentLine[21]', '$currentLine[22]', '$currentLine[23]', '$currentLine[24]', '$currentLine[25]')";
        $sth = $dbh->do($query) or die "Afasdf";
    }   
    print $currentRecord . " Record(s) uploaded.<br/>";
    $dbh->disconnect;           
}

我可以做些什么来提高性能？

这样更好吗？

elsif($update eq "incrementalLoad") {
    $filename = param("customPricing");
    $handle = upload("customPricing");

    my $update_handle = $dbh->prepare_cached("UPDATE custompricingtest SET partNumberSKU= ?, customerClass= ?, customerName= ?, customerId= ?, customerNumber= ?, custPartNumber=?, svcType= ?, sppl= ? , svcDuration= ?, durationPeriod= ?, priceMSRP= ?, partnerPriceDistiDvarOEM= ?, msrpSvcPrice=?, partnerSvcPrice=?, msrpBundlePrice=?, partnerBundlePrice=?, startDate=?, endDate=?, currency=?, countryCode=?, inventoryItemId=?, flexField1=?, flexField2=?, flexField3=?, flexField4=?, flexField5=? WHERE partNumberSKU=? and ifnull(customerClass,0)=ifnull(?,0) and ifnull(customerName,0)=ifnull(?,0) and ifnull(svcType,0)=ifnull(?,0) and ifnull(svcDuration,0)=ifnull(?,0) and ifnull(durationPeriod,0)=ifnull(?,0)") or $error = 1;

    while (<$handle>)  {                
        $currentRecord++;
        @currentLine = split( /,/, $_ );
        $i = 0;
        foreach $l(@currentLine){
            $currentLine[$i] =~ s/\\r//g; 
            $i++;           
        }       
        $update_handle->execute($currentLine[0],$currentLine[1],$currentLine[2],$currentLine[3],$currentLine[4],$currentLine[5],$currentLine[6],$currentLine[7],$currentLine[8],$currentLine[9],$currentLine[10],$currentLine[11],$currentLine[12],$currentLine[13],$currentLine[14],$currentLine[15],$currentLine[16],$currentLine[17],$currentLine[18],$currentLine[19],$currentLine[20],$currentLine[21],$currentLine[22],$currentLine[23],$currentLine[24],$currentLine[25],$currentLine[0],$currentLine[1],$currentLine[2],$currentLine[6],$currentLine[8],$currentLine[9]) or die "can't execute UPDATE  query. \n";
        print $currentRecord . "<br/>";
    }   
    print $currentRecord . " Record(s) uploaded.<br/>";
    $dbh->disconnect;           
}

表格格式

  CREATE TABLE `custompricingtest` (
  `partNumberSKU` varchar(255) DEFAULT NULL,
  `customerClass` varchar(255) DEFAULT NULL,
  `customerName` varchar(255) DEFAULT NULL,
  `customerId` varchar(255) DEFAULT NULL,
  `customerNumber` varchar(255) DEFAULT NULL,
  `custPartNumber` varchar(255) DEFAULT NULL,
  `svcType` varchar(255) DEFAULT NULL,
  `sppl` varchar(255) DEFAULT NULL,
  `svcDuration` varchar(255) DEFAULT NULL,
  `durationPeriod` varchar(255) DEFAULT NULL,
  `priceMSRP` varchar(255) DEFAULT NULL,
  `partnerPriceDistiDvarOEM` varchar(255) DEFAULT NULL,
  `msrpSvcPrice` varchar(255) DEFAULT NULL,
  `partnerSvcPrice` varchar(255) DEFAULT NULL,
  `msrpBundlePrice` varchar(255) DEFAULT NULL,
  `partnerBundlePrice` varchar(255) DEFAULT NULL,
  `startDate` varchar(255) DEFAULT NULL,
  `endDate` varchar(255) DEFAULT NULL,
  `currency` varchar(255) DEFAULT NULL,
  `countryCode` varchar(255) DEFAULT NULL,
  `inventoryItemId` varchar(255) DEFAULT NULL,
  `flexField1` varchar(255) DEFAULT NULL,
  `flexField2` varchar(255) DEFAULT NULL,
  `flexField3` varchar(255) DEFAULT NULL,
  `flexField4` varchar(255) DEFAULT NULL,
  `flexField5` varchar(255) DEFAULT NULL,
  KEY `part_num_sku` (`partNumberSKU`),
  KEY `svcType` (`svcType`),
  KEY `svcDuration` (`svcDuration`),
  KEY `durationPeriod` (`durationPeriod`),
  KEY `customerClass` (`customerClass`)
) 

我最终将文件保存在临时表中(需要几秒钟)，然后对表进行更新连接，但速度仍然很慢。下面是更新查询

UPDATE custompricingtest t1, custompricingtesttemp t2 
SET t1.customerId         = t2.customerId, 
    t1.customerNumber     = t2.customerNumber, 
    t1.custPartNumber     = t2.custPartNumber, 
    t1.sppl               = t2.sppl , 
    t1.priceMSRP          = t2.priceMSRP, 
    t1.partnerPriceDistiDvarOEM = t2.partnerPriceDistiDvarOEM,
    t1.msrpSvcPrice       = t2.msrpSvcPrice, 
    t1.partnerSvcPrice    = t2.partnerSvcPrice, 
    t1.msrpBundlePrice    = t2.msrpBundlePrice,
    t1.partnerBundlePrice = t2.partnerBundlePrice, 
    t1.startDate          = t2.startDate, 
    t1.endDate            = t2.endDate, 
    t1.currency           = t2.currency, 
    t1.countryCode        = t2.countryCode, 
    t1.inventoryItemId    = t2.inventoryItemId, 
    t1.flexField1         = t2.flexField1, 
    t1.flexField2         = t2.flexField2, 
    t1.flexField3         = t2.flexField3, 
    t1.flexField4         = t2.flexField4, 
    t1.flexField5         = t2.flexField5
WHERE t1.partNumberSKU  = t2.partNumberSKU 
  and t1.customerClass  = t2.customerClass 
  and t1.customerName   = t2.customerName 
  and t1.svcType        = t2.svcType 
  and t1.svcDuration    = t2.svcDuration 
  and t1.durationPeriod = t2.durationPeriod

解释扩展结果

id  select_type     table   type    possible_keys                                                               key               key_len   ref                            rows     Extra
1   SIMPLE           t2     ALL     part_num_sku,customerName,customerClass,durationPeriod,svcDuration,svcType  NULL                NULL    NULL                          28758      
1   SIMPLE           t1     ref     part_num_sku,svcDuration,customerName,customerClass,durationPeriod,svcType  part_num_sku         13     testbrocade.t2.partNumberSKU    394     Using where

尼特什

Answer 1

不要使用存储过程；在同一语句中执行多行。根据您的 max_allowed_pa​​cket，您应该能够在一个或几个插入语句中完成所有行。

糟糕，抱歉，我不知何故误将更新误读为插入。更新更难；如果您要展示您的创建表，我将尝试展示一个示例。