php - laravel 慢查询问题

Question

我想优化我正在使用 Laravel 执行的查询。我在我的项目中使用 MySQL，并且有一个名为 locations 的表。它有超过200万条记录。当我执行下面的代码时，它太慢了。如何优化我的查询以提高速度？

foreach ($employees as $employee){
    $percentage = 0;
    if ($employee->position->zones->count() > 0) {
        if($employee->position->zones->first()->workingzones->count() > 0) {
            $workingZone = $employee->position->zones->first()->workingzones->last();
            $tagCode = $employee->rfids->first()->rfid_id;
            $zoneTime = DB::table('location')
                ->select(DB::raw('count(*) as count'))
                ->where('tagCode', $tagCode)
                ->where('xLocation', '>', $workingZone->x1)
                ->where('xLocation', '<', $workingZone->x3)
                ->where('yLocation', '>', $workingZone->y3)
                ->where('yLocation', '<', $workingZone->y1)
                ->where('locationDate', '>=',''.$currentDate.' 00:00:01')
                ->where('locationDate', '<=', $currentDate. ' 23:59:59')
                ->get();


            $totalWorkedTime = DB::table('location')
                ->select(DB::raw('count(*) as count'))
                ->where('tagCode', $tagCode)
                ->where('locationDate', '>=',''.$currentDate.' 00:00:01')
                ->where('locationDate', '<=', $currentDate. ' 23:59:59')->get();


                if ($zoneTime->first()->count == 0 || $totalWorkedTime->first()->count == 0) {
                    $percentage = 0;
                }else {
                    $percentage = (int)ceil(($zoneTime->first()->count /12 )* 100 / ($totalWorkedTime->first()->count  / 12));
                }
            }
        }

        $employee->percentage = $percentage;
    }

Answer 1

您执行了两次完整的 ->get() 并且只使用了 ->first() 结果。当您只需要 1 时，只需使用 ->first() 而不是 ->get()。

---

您还可以在获取 employees 时预先加载位置和区域，并在每个循环中保存 2 个额外的查询(- 分组预先加载查询总数)，如下所示:

$employees = Employee::where(/*where foo here*/)
    ->with('position.zones')
    ->get();

---

为了消耗更少的内存，将其分块。

Employee::where(/*where foo here*/)
    ->with('position.zones')
    ->chunk(200, function ($employees) {
        foreach ($employees as $employee) {
            // current code here with ->first() instead of ->get()
        }
    });