要求:

我们在两台服务器上有两张相似的表。服务器中的第一个表具有唯一键列 A、B、C，我们将 Table1 行插入到具有唯一键列 B、C、D 的 Table2 中。

由于不同的唯一键列约束，表 1 有大约 500 万行，而表 2 将插入大约 30 万行。

这里的要求是，如果 Table2 中不存在相同的记录，则从 Table1 中获取所有行并插入到 Table2 中，如果记录匹配，则增加计数并更新 Table2 中的 'cron_modified_date' 列。

此设置的 PHP 版本为 5.5，MySQL 版本为 5.7，数据库服务器有 6 GB RAM。

在执行以下脚本时，处理 200 万条记录后处理速度变得非常慢，并且 RAM 没有释放，一段时间后所有 RAM 都被脚本消耗，之后脚本根本没有处理。

如您所见，我正在重置变量并关闭数据库连接，但它没有释放数据库服务器 RAM。经过一番阅读，我开始知道，可能是 PHP 垃圾收集需要手动调用以释放资源，但它也不会释放 RAM。

我在这里做错了什么以及如何使用 PHP、MYSQL 处理数百万条记录？

有没有其他方法可以在执行脚本时释放 RAM，以便脚本可以竞争执行？

/* Fetch records count for batch insert*/

$queryCount = "SELECT count(*) as totalRecords FROM TABLE1 where created_date > = '2018-02-10'";
$rowsCount = $GLOBALS['db']->execRaw( $queryCount)->fetchAll();

$recordsPerIteration = 50000 ;
$totalCount = $rowsCount[0]['totalRecords']; 
$start = 0;

gc_disable() ;
if ( $totalCount > 0 ) {
    while ( $totalCount > 0 ) {
    $query = "SELECT *  FROM TABLE1
                WHERE where created_date > = '2018-02-10'
                ORDER BY suggestion_id DESC 
                LIMIT ".$start.",".$recordsPerIteration;

    print "sql is $query" ;

    $getAllRows = $GLOBALS['db']->execRaw( $query )->fetchAll();
    $GLOBALS['db']->queryString = null;
    $GLOBALS['db']->close() ;

    foreach ($getAllRows as  $getRow) {

        $insertRow  = " INSERT INTO TABLE2 (
                            Name,
                            Company,
                            ProductName,
                            Status,
                            cron_modified_date)
                VALUE (   
                            ".$GLOBALS['db_ab']->quote($getRow['Name']).", 
                            ".$GLOBALS['db_ab']->quote($getRow['Company']).", 
                            ".$GLOBALS['db_ab']->quote($getRow['ProductName']).",
                            ".$getRow['Status'].",
                            ".$GLOBALS['db_ab']->quote($getRow['created_date'])."
                        )
                    ON DUPLICATE KEY UPDATE count = (count + 1) , cron_modified_date =  '".$getRow['created_date']."'" ;

                $GLOBALS['db_ab']->execRaw( $insertRow ) ;
                $GLOBALS['db_ab']->queryString = null;
                $getRow = null;
                $insertRow = null;
                $GLOBALS['db_ab']->close() ;
           }
          gc_enable() ;
          $totalCount   = $totalCount- $recordsPerIteration;
          $start        += $recordsPerIteration ;
          $getAllRows = null;
          gc_collect_cycles() ;
    }

}

解决方案

在@ABelikov 提供的建议和一些 hit & trail 方法之后...最后，下面的代码工作得非常好，并且在每插入 50K 条记录后释放 RAM。

以下是主要发现

• 在每次涉及大数据操作的主要操作后释放 DB 连接变量，并重新连接 DB，以便刷新 DB 缓冲区。

• 合并插入语句并一次性执行插入。不要在循环中执行单条记录插入。

  感谢大家的宝贵建议和帮助。

  /* Fetch records count for batch insert*/
  
  
  $queryCount = "SELECT count(*) as totalRecords FROM TABLE1 where created_date > = '2018-02-10'";
  $rowsCount = $GLOBALS['db']->execRaw( $queryCount)->fetchAll();
  
  $recordsPerIteration = 50000 ;
  $totalCount = $rowsCount[0]['totalRecords']; 
  $start = 0;
  
  if ( $totalCount > 0 ) {
     while ( $totalCount > 0 ) {
         $query = "SELECT *  FROM TABLE1
              WHERE where created_date > = '2018-02-10'
              ORDER BY suggestion_id DESC 
              LIMIT ".$start.",".$recordsPerIteration;
  
  print "sql is $query" ;
  
  $getAllRows = $GLOBALS['db']->execRaw( $query )->fetchAll();
  $GLOBALS['db']->queryString = null;
  $GLOBALS['db']->close() ;
  
  $insertRow  = " INSERT INTO TABLE2 (
                          Name,
                          Company,
                          ProductName,
                          Status,
                          cron_modified_date)
              VALUE (  " ;
  
  
  foreach ($getAllRows as  $getRow) {
  
  
          $insertRow  .= (".$GLOBALS['db_ab']->quote($getRow['Name']).", 
                          ".$GLOBALS['db_ab']->quote($getRow['Company']).", 
                          ".$GLOBALS['db_ab']->quote($getRow['ProductName']).",
                          ".$getRow['Status'].",
                          ".$GLOBALS['db_ab']->quote($getRow['created_date'])."),";
          }
  
  $insertRow=rtrim($insertRow,','); // Remove last ','
  $insertRow.= " ON DUPLICATE KEY UPDATE count = (count + 1) , cron_modified_date =  '".$getRow['created_date']."'" ;
  
  $GLOBALS['db_ab']->execRaw( $insertRow ) ;              
  //Flushing all data to freeup RAM
  $GLOBALS['db_ab'] = null ;
  $GLOBALS['db'] = null ;
  $insertRow = null;
  
  $totalCount = $totalCount- $recordsPerIteration;
  $start      += $recordsPerIteration ;
  $getAllRows = array();
  $getAllRows = null;
  print " \n Records needs to process ".$totalCount."\n";
  
  }
  
  }
  

最佳答案

1.插入多行解决方案

您可以使用“插入多行”来加速您的脚本，请参阅此处 https://dev.mysql.com/doc/refman/5.5/en/insert.html

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

您只需要在 foreach 中保留 VALUES 部分并移出所有其他部分

 $insertRow  = " INSERT INTO TABLE2 (
                             Name,
                             Company,
                             ProductName,
                             Status,
                             cron_modified_date) VALUES ";
 foreach ($getAllRows as  $getRow) {
     $insertRow.="(".$GLOBALS['db_ab']->quote($getRow['Name']).",
                   ".$GLOBALS['db_ab']->quote($getRow['Company']).", 
                   ".$GLOBALS['db_ab']->quote($getRow['ProductName']).",
                   ".$getRow['Status'].",
                   ".$GLOBALS['db_ab']->quote($getRow['created_date'])."),";

 }
 $insertRow=rtrim($insertRow,','); // Remove last ','
 $insertRow .= " ON DUPLICATE KEY UPDATE count = (count + 1) , cron_modified_date =  '".$getRow['created_date']."'" ;
 $GLOBALS['db_ab']->execRaw( $insertRow ) ;
 $GLOBALS['db_ab']->queryString = null;
 $getRow = null;
 $insertRow = null;
 $GLOBALS['db_ab']->close() ;

只有当您的 foreach“主体”通常运行不止一次时，这才有用

2.MySQL服务器端解决方案

尝试使用 TRANSACTION https://dev.mysql.com/doc/refman/5.7/en/commit.html http://php.net/manual/en/pdo.begintransaction.php

只需在脚本开始时开始一个并在结束时提交。 取决于您的服务器，它确实可以提供帮助。 但小心点!这取决于您的 MySQL 服务器配置集。需要测试。

关于php - 使用 PHP 和 MySQL...如何释放内存？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/48800128/