Laravel groupBy性能优化及SQL分组only_full_group_by报错解决

问题背景 #

在Laravel 8项目中，需解析查询返回的日期用于图表格式化。查询返回约7万条数据仅耗时100ms，但getUptimeTimeline函数中对$uptimeChecks执行groupBy操作（对应timeline_2）却耗时800ms，性能极差。尝试在SQL层面分组时，又遇到only_full_group_by模式的报错，现寻求优化方案。

原始查询代码 #

$uptimeChecks = UptimeCheck::where('user_id', $user->id)
    ->where('monitor_id', $monitor['id'])
    ->where('checked_at', '>=', $from)
    ->where('checked_at', '<=', $to)
    ->orderBy('checked_at', 'asc')
    ->select('event', 'response_time', 'checked_at')
    ->get();

getUptimeTimeline函数代码 #

/**
 * Get uptime timeline
 *
 * @return Response
 */
protected function getUptimeTimeline($user, $id, $uptimeChecks, $period, $days)
{
    try {
        $start = microtime(true);
        $dates = collect($period->toArray())->mapWithKeys(function ($date) {
            return [$date->format('Y-m-d') => [
                'total_events' => 0,
                'down_events' => 0,
                'up_events' => 0,
                'uptime' => 'No Data',
                'fill' => '#ced1d7',
            ]];
        });

        $end = microtime(true);
        Log::debug('timeline_1', [
            'diff' => ($end - $start) * 1000
        ]);

        $start = microtime(true);
        $uptimeDates = $uptimeChecks->groupBy(function ($item, $key) {
            $date = Carbon::parse($item->checked_at);

            return $date->format('Y-m-d');
        });

        $end = microtime(true);
        Log::debug('timeline_2', [
            'diff' => ($end - $start) * 1000
        ]);

        return $uptimeDates;
    } catch (\Exception $e) { }
}

SQL分组尝试及报错 #

尝试在SQL层面分组的代码：

$responseTimes = UptimeCheck::where('user_id', $user->id)
                            ->where('monitor_id', $monitor['id'])
                            ->where('checked_at', '>=', $from)
                            ->where('checked_at', '<=', $to)
                            ->orderBy('checked_at', 'asc')
                            ->select('event', 'response_time', 'checked_at', DB::raw('DATE(checked_at) as check_date'))
                            ->groupBy('check_date')
                            ->limit(10)
                            ->get();

抛出错误：

[2023-01-30 20:42:31] local.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'domainmonitor_db.uptime_checks.event' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select event, response_time, checked_at, DATE(checked_at) as check_date from uptime_checks where user_id = 1 and monitor_id = 1 and checked_at >= 2022-12-26 20:42:31 and checked_at <= 2023-01-30 20:42:31 group by check_date order by checked_at asc limit 10) {"userId":1,"exception":"[object] (Illuminate\Database\QueryException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'domainmonitor_db.uptime_checks.event' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select event, response_time, checked_at, DATE(checked_at) as check_date from uptime_checks where user_id = 1 and monitor_id = 1 and checked_at >= 2022-12-26 20:42:31 and checked_at <= 2023-01-30 20:42:31 group by check_date order by checked_at asc limit 10) at C:\Users\Ryan\Desktop\web-projects\domain-monitor\domain-monitor-api\vendor\laravel\framework\src\Illuminate\Database\Connection.php:760)

解决方案 #

一、优化集合groupBy性能 #

timeline_2性能差的核心原因是7万条数据都要重复执行Carbon::parse解析日期，这是巨大的性能开销。可以通过以下方式优化：

1. 提前在SQL中格式化日期
   修改原始查询，直接在数据库层面生成格式化后的日期字段，避免PHP中重复解析：

$uptimeChecks = UptimeCheck::where('user_id', $user->id)
    ->where('monitor_id', $monitor['id'])
    ->where('checked_at', '>=', $from)
    ->where('checked_at', '<=', $to)
    ->orderBy('checked_at', 'asc')
    ->select('event', 'response_time', 'checked_at', DB::raw('DATE(checked_at) as check_date'))
    ->get();

2. 直接按字段分组，移除闭包解析
   修改groupBy逻辑，直接使用已生成的check_date字段分组，省去Carbon解析步骤：

$uptimeDates = $uptimeChecks->groupBy('check_date');

此操作可将timeline_2的耗时大幅降低，接近原生集合分组的性能。

二、解决SQL分组的only_full_group_by报错 #

报错原因是MySQL的only_full_group_by模式要求：SELECT语句中的非聚合列必须出现在GROUP BY子句中，或通过聚合函数处理。你原来的SQL中event、response_time等字段是每行的原始数据，分组后无法确定取哪一行的值，因此触发报错。

正确的做法是直接在SQL中完成数据聚合（统计每天的总事件、宕机/正常事件数等），无需取出所有原始数据：

$timelineData = UptimeCheck::where('user_id', $user->id)
    ->where('monitor_id', $monitor['id'])
    ->where('checked_at', '>=', $from)
    ->where('checked_at', '<=', $to)
    ->select([
        DB::raw('DATE(checked_at) as check_date'),
        DB::raw('COUNT(*) as total_events'),
        // 假设down事件标识为'down'，根据实际值调整
        DB::raw('SUM(CASE WHEN event = "down" THEN 1 ELSE 0 END) as down_events'),
        DB::raw('SUM(CASE WHEN event = "up" THEN 1 ELSE 0 END) as up_events'),
        // 可选：统计平均响应时间
        DB::raw('AVG(response_time) as avg_response_time')
    ])
    ->groupBy('check_date')
    ->orderBy('check_date', 'asc')
    ->get();

这种方式将数据处理逻辑交给数据库（数据库擅长批量聚合计算），返回的直接是每天的汇总数据，性能远优于PHP集合分组，同时完全符合only_full_group_by的要求，不会触发报错。

内容的提问来源于stack exchange，提问作者Ryan H