Laravel统计指定时段匹配最新状态的事项数及性能优化

• 涉及3张数据表：matters（事项表）、statuses（状态字典表）、matter_status（多对多关联中间表）
• matters与statuses为多对多关联关系，通过中间表matter_status绑定，中间表核心字段为matter_id（事项ID）、status_id（状态ID）、created_at（状态绑定时间）
• 统计需求：计算指定时间段内，最新状态为目标状态值的事项总数。同一事项如果在统计时段内先后绑定多个状态，仅以创建时间最晚的状态记录作为判断依据，历史状态关联不计入统计。

示例中间表数据如下：

上述场景中matter_id=2当日先后绑定了status_id=1（New）、status_id=2（Pending）两个状态，统计status_id=1的事项数时该事项不应计入，正确结果为1而非2。

最初编写的Eloquent查询未做最新状态过滤，返回结果不符合预期，代码如下：

Matter::select('id')
  ->with('statuses')
  ->whereHas('statuses', function ($query) {
    $query->where('statuses.id', 1)->whereMonth('statuses.created_at', Carbon::now()->month);
})
->latest()
->count();

调整后的查询虽然可以返回正确结果，但性能表现较差，8000行数据规模下查询耗时达2秒，需要更高性能的实现方案，慢查询代码如下：

Matter::select('matters.*')
    ->join('matter_status', 'matters.id', 'matter_status.matter_id')
    ->where('matter_status.status_id', $this->status)
    ->whereBetween('matter_status.created_at', [Carbon::now()->startOfDay(), Carbon::now()->endOfDay()])
    ->where('matter_status.created_at', function ($query) {
        $query->selectRaw('max(created_at)')
            ->from('matter_status')
            ->whereColumn('matter_id', 'matters.id');
    })
    ->count();

原慢查询的核心问题是使用了相关子查询，子查询会依赖外层查询的matter_id逐行执行MAX()计算，产生大量重复运算。以下两种方案都可以将查询耗时压缩到几十毫秒级别：

方案1：窗口函数实现（MySQL 8.0+ / PostgreSQL 支持，性能最优） #

通过窗口函数给每个事项的状态记录按绑定时间倒序排号，仅取每个事项排名为1（即最新）的记录，再筛选目标状态和时间范围统计即可，Eloquent实现代码：

$start = Carbon::now()->startOfDay();
$end = Carbon::now()->endOfDay();
$targetStatus = $this->status;

$count = Matter::join(
    DB::raw("(
        SELECT matter_id, status_id,
               ROW_NUMBER() OVER (PARTITION BY matter_id ORDER BY created_at DESC) as rn
        FROM matter_status
        WHERE created_at BETWEEN '{$start}' AND '{$end}'
    ) as latest_status"),
    function ($join) use ($targetStatus) {
        $join->on('matters.id', '=', 'latest_status.matter_id')
             ->where('latest_status.rn', 1)
             ->where('latest_status.status_id', $targetStatus);
    }
)->count();

若存在同一事项同一时间绑定多个不同状态的极端场景，可将ROW_NUMBER()替换为RANK()适配业务逻辑。

方案2：聚合后关联（兼容MySQL 5.x版本） #

放弃逐行执行的相关子查询，先一次性聚合出统计时间范围内每个事项对应的最新状态绑定时间，再关联回中间表筛选目标状态，无需关联matters表即可完成统计，性能远高于原实现：

$start = Carbon::now()->startOfDay();
$end = Carbon::now()->endOfDay();
$targetStatus = $this->status;

$count = DB::table('matter_status as ms')
    ->join(
        DB::raw("(
            SELECT matter_id, MAX(created_at) as latest_at
            FROM matter_status
            WHERE created_at BETWEEN '{$start}' AND '{$end}'
            GROUP BY matter_id
        ) as latest_map"),
        function ($join) {
            $join->on('ms.matter_id', '=', 'latest_map.matter_id')
                 ->on('ms.created_at', '=', 'latest_map.latest_at');
        }
    )
    ->where('ms.status_id', $targetStatus)
    ->distinct('ms.matter_id')
    ->count('ms.matter_id');

索引优化 #

给中间表matter_status添加联合覆盖索引，可以进一步将查询性能提升30%以上，索引语句：

ALTER TABLE matter_status ADD INDEX idx_mid_ctime_sid (matter_id, created_at, status_id);

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