php - 大型 SQL 查询在 PHP 中占用的内存比 SQL 数据多 10 倍

Question

所以我们有一个现有的系统，我们正试图扩大它并用尽内存来检索接近 3M 的记录。

我试图通过确定查询返回的数据大小来确定增加服务器内存作为权宜之计解决方案的可行性，方法如下:

select sum(row_size) 
from (
SELECT
    ifnull(LENGTH(qr.id), 0)+
    ifnull(LENGTH(qr.question_id), 0)+
    ifnull(LENGTH(qr.form_response_id), 0)+
    ifnull(LENGTH(qr.`value`), 0)+
    ifnull(LENGTH(qr.deleted_at), 0)+
    ifnull(LENGTH(qr.created_at), 0)+
    ifnull(LENGTH(qr.updated_at), 0)
    as row_size
    FROM
    ....
    LIMIT 500000
) as tbl1;

返回 30512865，大约是 30MB 的数据。

然而，当我交叉检查 PHP 实际使用什么来存储结果时:

$memBefore = memory_get_usage();
$formResponses = DB::select($responsesSQL, $questionIDsForSQL);
$memAfter = memory_get_usage();
dd($memBefore, $memAfter);

我得到 315377552 和 22403248，这意味着 292974304 字节或大约 300MB 的内存使用量来存储简单数组!

我想了解为什么内存占用量是检索到的数据的 10 倍，我能做些什么来减少内存占用量，除了从后端修改 API 响应，而前端不需要整个结果设置这需要时间。

对于上下文，当前实现使用上述结果(由 getQuestionResponses 返回)使用 Laravel 集合将它们转换为由 question_id 分组的关联数组:collect($this->questionResponseRepo->getQuestionResponses($questions))->groupBy('question_id')->toArray();

我正在考虑用自己的内存效率更高的实现替换 collect ，这将使用从查询返回的数组，通过将该数组转换为 Laravel 的集合来减少内存膨胀，但这仍然无济于事数组本身为 500k 记录响应占用 300MB，而不是 30MB。

在线解决方案之一是使用 SplFixedArray 但我不确定如何强制 DB::select 使用它而不是数组？

另一种可能的解决方案涉及确保它返回简单的关联数组而不是标准类数组 https://stackoverflow.com/a/37532052/373091但是当我尝试这样做时:

// get original model
$fetchMode = DB::getFetchMode();
// set mode to custom
DB::setFetchMode(\PDO::FETCH_ASSOC);
$memBefore = memory_get_usage();
$formResponses = DB::select($responsesSQL, $questionIDsForSQL);
DB::setFetchMode($fetchMode);
$memAfter = memory_get_usage();
dd($memBefore, $memAfter, $formResponses);

，我得到错误 Call to undefined method Illuminate\\Database\\MySqlConnection::getFetchMode() 这意味着显然它不能再从 Laravel 完成 > 5.4 :(

有什么建议吗？

Answer 1

我认为真正的问题是您一次将所有 300 万条记录加载到内存中。您应该改为分块处理它们或使用游标。

分块

要将记录分块，您可以使用 Laravel 的 chunk方法。此方法接受两个参数， block 大小和一个回调，该回调传递模型或对象的子集以进行处理。这将针对每个 block 的查询执行。

这是从文档中摘取的示例:

Flight::chunk(200, function ($flights) {
    foreach ($flights as $flight) {
        //
    }
});

光标

或者，您也可以使用 cursor方法，如果你只想执行一个查询。在这种情况下，Laravel 一次只会混合一个模型，因此您一次不会在内存中拥有超过一个模型(如果您不使用 Eloquent，则为对象)。

foreach (Flight::where('destination', 'Zurich')->cursor() as $flight) {
    //
}