php - MySQL在多次查询后内存不足

Question

我正在尝试迭代两个表并提取记录。这些表有数十万行，所以我知道一次拉动它们是行不通的，所以循环遍历并批量拉动它们似乎是最好的方法，但它不会释放内存，所以在几次拉动之后内存不足并崩溃。

我的输出

    400000 Pulled, currently on 0 || Out of 1623230    400000 Pulled, currently on 400000 || Out of 1623230    Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 39 bytes) in C:\xampp\htdocs\misc\manage tickets\test2.php on line 38

Ignore the line number, I have a giant block of commented out code at the top of the file.

The code is:

$iterate = 400000;


$query = "SELECT id FROM $old_db.ticketaudit_archive";
$get = $g_mysqli->query($query);
$count = $get->num_rows;

$i = 0;
while($i <= $count){
    $query = "SELECT * FROM $old_db.ticketaudit_archive LIMIT $i, $iterate";
    $get = $g_mysqli->query($query);
    echo $get->num_rows.' Pulled, currently on '.$i.' || Out of '.$count.'<br>';
    while($z = $get->fetch_assoc()){
        $old_ticket_id = $z[old_ticket_id];
        $ticket_id = $z[ticket_id];
        unset($z[old_ticket_id], $z[ticket_id], $z[id]);
        if($old_ticket_id != ''){
            $ticketaudit[old_ticket_id][$old_ticket_id][] = $z;
            $xx += 1;
        }
        elseif($ticket_id != ''){
            $ticketaudit[ticket_id][$ticket_id][] = $z;
            $xx += 1;
        }
    }
    $i += $iterate;
    $get->free();
    $get->free_result();
    $get->close();
}

我一整天都在研究这个问题，但找不到一种方法让 MySQL 删除先前查询的该死的内存缓存。

Answer 1

我认为您可能正在寻找无缓冲的查询:

Unbuffered MySQL queries execute the query and then return a resource while the data is still waiting on the MySQL server for being fetched. This uses less memory on the PHP-side, but can increase the load on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection. Unbuffered queries can also be referred to as "use result".

这是一个无缓冲查询示例:

$get = $g_mysqli->query($query, MYSQLI_USE_RESULT);

您可以阅读更多here