php - 如何节省内存负载并防止 ZEND HEAP php 错误查询创建 .txt 文件的数百万条记录

Question

PHP 在处理了大约 45 分钟的记录后出现了 ZEND HEAP 错误，我花了无数小时研究并试图解决这个错误，但未能解决。当我研究并尝试了所有方法时，没有 .ini 配置可以解决这个问题!这似乎只是一个 php 5xx 限制或错误。

我有 6 个表和超过 54,000,000 条要查询的记录。从这些查询中，我生成了一个专有文本文件。

我正在寻找以下脚本中的最佳建议或脚本的重新创建，因为我在过去一年中对其进行了 100 次修改并且它变得更快，但我无法摆脱 ZEND HEAD 错误，该错误会终止我的脚本非常大的记录集。

这不是基于网络的脚本，所有工作都是本地化的，并通过 php cli 运行。

我还应该注意到，我花了无数个小时重新配置 php.ini 内存设置和任何其他可能的调整，我尝试了但没有成功。

我希望脚本可以用 OOP 结构编写，从而提高内存效率。

提前感谢任何时间或想法。以下是我现有的代码:

<?php
ini_set('mysql.connect_timeout', '9999999999999999999999999999999999999999999999');
ini_set('default_socket_timeout', '9999999999999999999999999999999999999999999999');
ini_set('memory_limit','9999999999999999999999999999999999999999999999M');
// Opens a connection to a MySQL server.
$connection = mysql_connect ("localhost", "root", "");
if (!$connection) 
{
die('Not connected : ' . mysql_error());
}
// Sets the active MySQL database.
$db_selected = mysql_select_db("charities", $connection);
if (!$db_selected) 
{
   die ('Can\'t use db : ' . mysql_error());
}
// Basic settings
$txtdate = date('Ymdhis');
// Filter only
$_POST["state_abbr"] = 'NY'; // State 
$_POST["loc_type"] = 'Non Profit'; // Church, School, Non Profit etc...
// Query parameters - eg for Open or Closed Status.
$_POST["case_disp"] = 'C';
$_POST["case_disp_txt"] = 'CLOSED';
$_POST["case_disp_gp"] = 'CLOSED';
$_POST["last_act_txt"] = 'CLOSED'; 
// Text output only.
$_POST["status_word"] = 'Closed'; // Location status: "ABANDONED" or "ACTIVE"
$_POST["stateU"] = 'New York'; 

$Locations_yr = "2012-" . date('Y');

// Selects all the rows in the markers table.
$query = "SELECT * FROM charity_full_merged as a 
INNER JOIN charity_full_merged_st_load_case as b
ON a.charity_id=b.charity_id
INNER JOIN charity_full_merged_land as c
ON b.charity_id=c.charity_id
INNER JOIN charity_full_merged_county as d
ON c.charity_id=d.charity_id
INNER JOIN charity_full_merged_customer as e
ON d.charity_id=e.charity_id 
WHERE
a.township_range_quarters != ''
AND 
b.geo_state = '".$_POST["state_abbr"]."' 
AND
b.casetype_txt LIKE \"%".$_POST["loc_type"]."%\"    
AND 
b.case_disp_txt = '".$_POST["case_disp_txt"]."' GROUP BY b.charity_nm;";

$result = mysql_query($query);

if($result === FALSE) {
    die(mysql_error());
}
$results_count = mysql_num_rows($result);

 $result = mysql_query($query);
     if (!$result) 
 {
      die('Invalid query: ' . mysql_error());
 }
// Creates an array of strings to hold the lines of the txt file.
$txt = array('<?xml version="1.0" encoding="UTF-8"?>');
$txt[] = "<Document>
<name>" . date('Y') . " Data Map of " . $_POST["stateU"] . ". " . $_POST["status_word"]   . " " . $_POST["loc_type"] . " Locations. </name>
<description>" . $_POST["stateU"] . " " . $_POST["status_word"]  . " " .    $_POST["loc_type"] . " " . " map." .  " " . $results_count . " Records. Created by Charity    Group 5." . date('Y') . ". </description>";
while ($row = @ mysql_fetch_assoc($result))
{
        if ($row['last_action_txt'] == 'NOP' 

        {
            $boxclr = 'activeLoc';
        }
        else if ($row['case_disp'] == 'C' 

    {
            $boxclr = 'closedLoc';
        }
    else { // Unknown
        $boxclr = "yellowBox";
}
  $txt[] = "... general content written here ... (about 100 lines of text per  record ";
} 
// End XML file
$txt[] = ' </Document>';
$txt[] = '</txt>';
$txtOutput = join("\n", $txt);

// Create .txt file.
$txtfile =  $_POST["stateU"] . "Charities" . $_POST["status_word"] . "-" .     $_POST["loc_type"] . "-LocationS-" . $results_count . "-" . $txtdate . ".txt";

// Put the contents of $txtOutput into the $txtfile.
file_put_contents($txtfile, $txtOutput);

echo "$results_count " . $_POST["status_word"] . " " . $_POST["loc_type"] . " Location     records processed...";
?>

Answer 1

我想您已经理解了一般问题。您正在尝试将整个结果集累积到 $txt 数组中 - 如您所见，这不适用于庞大的数据集。相反，您应该允许 PHP 在生成数据时输出数据。

所以代替:

$txt[] = "(header stuff)";
while ($row = mysql_fetch_assoc($result) {  // Remove the @ here!!!
   $txt[] = "(content stuff)";              // This becomes 'huge'
}
$txt[] = "(footer stuff)";

$txtOutput = join("\n", $txt);              // Now your memory usage is
                                            // ~ 2 * 'huge'

file_put_contents($filename, $txtOutput);

你应该 fopen文件提前，fwrite你需要什么。

$fp = fopen($filename, "w");

fwrite($fp, "(header stuff)");

while ($row = mysql_fetch_assoc($result) {
   fwrite($fp, "(content stuff)");
}

fwrite($fp, "(footer stuff)");

fclose($fp);

此外，当您必须尝试使用​​像 9999999999999999999999999999999999999999999999 这样的数字时对于套接字超时或内存限制，这应该是一个很大的警告，表明您做错了什么。