如何优化getTripCoverage函数的Eloquent查询及添加合适索引？

一、核心性能瓶颈分析 #

原代码存在两个关键问题：

1. 两次重复查询points表，浪费数据库资源
2. 第二次查询使用关联子查询，每条符合条件的points记录都会触发一次events表查询，当points表有5000条记录时，会产生5000次小查询，这是性能慢的主要原因

二、索引添加方案 #

1. points表复合索引 #

原查询对points表的过滤条件是cart_zone_id = ? AND enabled = 1 AND tipo = 'controllo' AND deleted_at IS NULL，现有单字段索引无法覆盖所有条件，需创建复合索引让数据库直接通过索引完成计数，无需回表：

CREATE INDEX idx_points_cartzone_enabled_type_deleted ON points (cart_zone_id, enabled, tipo, deleted_at);

字段顺序说明：将等值查询且过滤性强的字段放在前面，cart_zone_id作为核心过滤条件优先，后续依次匹配enabled、tipo，最后包含deleted_at覆盖软删除判断。

2. events表复合索引 #

关联查询的条件是schedule_id = ? AND boa_id = points.id，需创建复合索引加速关联匹配：

CREATE INDEX idx_events_schedule_boa ON events (schedule_id, boa_id);

字段顺序说明：schedule_id是每次查询的固定值，先过滤出对应schedule的所有events，再匹配boa_id，效率远高于反向顺序。

三、优化后的代码实现 #

方案一：单次查询获取所有统计值（推荐） #

合并两次查询为一次，用EXISTS判断点位是否有对应events记录，避免循环子查询：

public static function getTripCoverage($schedule_id, $cart_zone_id)
{
    $stats = Points::where('cart_zone_id', $cart_zone_id)
        ->where('enabled', 1)
        ->where('tipo', 'controllo')
        ->select(
            DB::raw('COUNT(*) as totboe'),
            DB::raw('SUM(EXISTS(SELECT 1 FROM events WHERE boa_id = points.id AND schedule_id = ?)) as totpassate')
        )
        ->setBindings([$schedule_id])
        ->first();

    $totboe = $stats->totboe ?? 0;
    $totpassate = $stats->totpassate ?? 0;

    return $totboe > 0 ? round($totpassate * 100 / $totboe) : 0;
}

方案二：JOIN+去重统计（适合events存在重复记录的场景） #

通过JOIN关联两张表，用DISTINCT确保每个点位只被统计一次：

public static function getTripCoverage($schedule_id, $cart_zone_id)
{
    $totboe = Points::where('cart_zone_id', $cart_zone_id)
        ->where('enabled', 1)
        ->where('tipo', 'controllo')
        ->count();

    if ($totboe === 0) {
        return 0;
    }

    $totpassate = Points::where('cart_zone_id', $cart_zone_id)
        ->where('enabled', 1)
        ->where('tipo', 'controllo')
        ->join('events', 'points.id', '=', 'events.boa_id')
        ->where('events.schedule_id', $schedule_id)
        ->distinct('points.id')
        ->count('points.id');

    return round($totpassate * 100 / $totboe);
}

额外注意事项 #

• 原代码中直接将$schedule_id拼接进DB::raw存在SQL注入风险，优化后的代码使用参数绑定解决了这个问题
• 原查询中select('*')会返回所有points字段，优化后的代码只统计必要数值，减少了数据传输开销

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