Laravel-与关系的收集需要很多时间 [英] Laravel - Collection with relations take a lot of time

问题描述

我们正在与LUMEN合作开发API. 今天，我们在收集"TimeLog"模型时遇到了一个困惑的问题. 我们只是想获取所有时间日志以及来自董事会模型和任务模型的其他信息. 在一行时间日志中，我们有一个board_id和一个task_id.两者都是1:1的关系.

We are developing an API with LUMEN. Today we had a confused problem with getting the collection of our "TimeLog"-model. We just wanted to get all time logs with additional informationen from the board model and task model. In one row of time log we had a board_id and a task_id. It is a 1:1 relation on both.

这是我们获取完整数据的第一个代码.这花了很多时间，有时我们超时了: BillingController.php

This was our first code for getting the whole data. This took a lot of time and sometimes we got a timeout: BillingController.php

public function byYear() {

       $timeLog = TimeLog::get(); 

        $resp = array(); 

        foreach($timeLog->toArray() as $key => $value) {  

            if(($timeLog[$key]->board_id && $timeLog[$key]->task_id) > 0 ) {      

                 array_push($resp, array(
                    'board_title' => isset($timeLog[$key]->board->title) ? $timeLog[$key]->board->title : null,
                    'task_title' => isset($timeLog[$key]->task->title) ? $timeLog[$key]->task->title : null,
                    'id' => $timeLog[$key]->id
                )); 
            }
        }


        return response()->json($resp);
    }   

建立联系的 TimeLog.php .

public function board()
        {
            return $this->belongsTo('App\Board', 'board_id',  'id');
        }

        public function task()
        {
            return $this->belongsTo('App\Task', 'task_id',  'id');
        }

我们的新方法是这样的: BillingController.php

Our new way is like this: BillingController.php

 public function byYear() {



            $timeLog = TimeLog::
join('oc_boards', 'oc_boards.id', '=', 'oc_time_logs.board_id')
                            ->join('oc_tasks', 'oc_tasks.id', '=', 'oc_time_logs.task_id')
                            ->join('oc_users', 'oc_users.id', '=', 'oc_time_logs.user_id')
                            ->select('oc_boards.title AS board_title', 'oc_tasks.title AS task_title','oc_time_logs.id','oc_time_logs.time_used_sec','oc_users.id AS user_id')
                            ->getQuery()
                            ->get(); 

            return response()->json($timeLog);
        }   

我们在TimeLog.php中删除了该关系，因为我们不再需要它.现在，我们的加载时间约为1秒，这很好！ 时间日志表中大约有2万条记录.

We deleted the relation in TimeLog.php, cause we don't need it anymore. Now we have a load time about 1 sec, which is fine! There are about 20k entries in the time log table.

我的问题是:

1. 为什么第一种方法超出范围(什么原因导致超时?)
2. getQuery()是什么?完全可以吗?

如果您需要更多信息，请问我.

If you need more information just ask me.

推荐答案

-第一个问题-

您可能面临的问题之一是将所有这些大量数据存储在内存中，即:

One of the issues you might be facing is having all those huge amount of data in memory, i.e:

$timeLog = TimeLog::get();

这已经是巨大的了.然后，当您尝试将集合转换为数组时:

This is already enormous. Then when you are trying to convert the collection to array:

1. 在收藏中有一个循环.
2. 根据我的理解在初始化循环时使用$timeLog->toArray()效率不高(尽管我对此可能并不完全正确)
3. 进行了大量查询以检索相关模型

1. There is a loop through the collection.
2. Using the $timeLog->toArray() while initializing the loop based on my understanding is not efficient (I might not be entirely correct about this though)
3. Thousands of queries are made to retrieve the related models

所以我建议的是 5 方法(一种方法可以使您免于数百次查询)，最后一种方法可以有效地返回自定义的结果:

So what I would propose are five methods (one which saves you from hundreds of query), and the last which is efficient in returning the result as customized:

1. 因为您有很多数据，所以chunk结果参考: Laravel块，所以您有了它:

1. Since you have many data, then chunk the result ref: Laravel chunk so you have this instead:

$timeLog = TimeLog::chunk(1000, function($logs){
    foreach ($logs as $log) {
    // Do the stuff here
    }
}); 

其他方式使用游标(仅在条件匹配的情况下运行一个查询)，游标的内部操作被理解为使用

这看起来像第一个，但是您已经将查询范围缩小到所需的范围:

This looks like the first but instead you have already narrowed your query down to what you need:

TimeLog::where([['board_id','>',0],['task_id', '>', 0]])->get()

急切加载已经显示了您的关系随时随地，但也可能会导致更多数据在内存中.因此，即使您急于加载相关模型，chunk方法也可能使事情更易于管理

Eager Loading would already present the relationship you need on the fly but might lead to more data in memory too. So possibly the chunk method would make things more easier to manage (even though you eagerload related models)

TimeLog::with(['board','task'],  function ($query) {
    $query->where([['board_id','>',0],['task_id', '>', 0]]);
}])->get();

您可以简单地使用变形金刚

• 使用互感器，即使大小很大，也可以用优雅，干净，受控的方法加载相关模型，即使它很大，另一个好处是您可以转换结果而不必担心如何对其进行循环 您可以简单地参考此答案，以简单地使用它.但是，如果您不需要转换响应，则可以采取其他选择.

• With transformer, you can load related model, in elegant, clean and more controlled methods even if the size is huge, and one greater benefit is you can transform the result without having to worry about how to loop round it You can simply refer to this answer in order to perform a simple use of it. However incase you don't need to transform your response then you can take other options.

尽管这可能不能完全解决问题，但是由于您面临的主要问题是基于内存管理的，因此上述方法应该很有用.

Although this might not entirely solve the problem, but because the main issues you face is based on memory management, so the above methods should be useful.

-第二个问题-

基于Laravel API 此处看到:

Based on Laravel API here You could see that:

它只是返回基础查询构建器实例.根据我的观察，根据您的示例，它是不需要的.

It simply returns the underlying query builder instance. To my observation, it is not needed based on your example.

更新

对于问题1，由于您似乎只是想简单地将结果作为响应返回，因此分页结果的效率更高. Laravel提供分页其中最简单的是SimplePaginate，这很好.唯一的事情是它对数据库进行了更多查询，但但仍检查最后一个索引.我猜它也使用cursor但不确定.我猜最后，采用以下方法可能会更理想:

For question 1, since it seems you want to simply return the result as response, truthfully, its more efficient to paginate this result. Laravel offers pagination The easiest of which is SimplePaginate which is good. The only thing is that it makes some few more queries on the database, but keeps a check on the last index; I guess it uses cursor as well but not sure. I guess finally this might be more ideal, having:

return TimeLog::paginate(1000);

这篇关于Laravel-与关系的收集需要很多时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！