PostgreSQL环境下Laravel如何优化访谈记录权限查询性能

你当前代码的性能瓶颈非常明确：

• 先把所有符合archived条件的记录全量加载到PHP内存，后续的权限判断、排序全在内存里做，数据量越大内存占用越高、循环耗时越长
• 每条记录都要做JSON解码、数组类型转换、内容判断，全是重复的CPU开销
• 排序逻辑用PHP层的sortBy()->reverse()实现，没有利用数据库的排序能力，属于典型的低效实现

直接把过滤、排序逻辑全部下推到PostgreSQL层执行，数据库仅返回你有权限访问的记录，从根源上减少无效数据传输和内存计算开销。
PostgreSQL原生支持JSON/JSONB类型的数组包含判断，完全可以在SQL层直接校验当前用户ID是否存在于shared_user_ids数组中。

优化后的Eloquent实现代码 #

$targetUserId = (int)$user->id;

// 核心查询：所有过滤、排序全在数据库完成
$interviews = interviews::query()
    // 必选条件：archived值为0/false/null
    ->whereIn('archived', [0, false, null])
    // 权限条件：满足任意一个即可
    ->where(function ($query) use ($targetUserId) {
        $query->where('user_id', $targetUserId)
            // PG JSONB包含判断：校验用户ID是否在shared_user_ids数组中
            ->orWhereRaw('shared_user_ids::jsonb @> jsonb_build_array(?)', [$targetUserId]);
    })
    // 直接在数据库层按创建时间倒序，替换原PHP内存排序
    ->orderBy('created_at', 'desc')
    // 仅查询后续需要用到的字段，减少数据传输量
    ->get([
        'id', 'title', 'created_at', 'sent_count_vlog',
        'openedInterview', 'openedBespoke', 'openedMarketing', 'openedVlog', 'archived'
    ]);

$interviews__ = [];
foreach ($interviews as $in) {
    $interviews__[] = [
        "id" => $in->id,
        "title" => $in->title,
        "created" => $in->created_at,
        "open_rate" => $in->getOpenRates(),
        'total_leads' => $in->getLeads(),
        'newOpensCounter' => $in->getNewOpens(),
        'openDiff' => $in->getOpenDiff(),
        'interview_count' => $in->getInterviewCount(),
        'sent_count' => $in->getCount(),
        'sent_count_marketing' => $in->getCountMarketing(),
        'sent_count_vlog' => $in->sent_count_vlog,
        'openedInterview' => $in->openedInterview ?? 0,
        'openedBespoke' => $in->openedBespoke ?? 0,
        'openedMarketing' => $in->openedMarketing ?? 0,
        'openedVlog' => $in->openedVlog ?? 0,
        'archived' => $in->archived
    ];
}

进一步优化建议 #

• 将shared_user_ids字段类型从json修改为jsonb，为该字段添加GIN索引，同时为archived、user_id、created_at字段添加联合索引，数据量较大时查询性能还能提升数十倍
• 如果getOpenRates()、getLeads()等方法是通过关联查询获取数据，使用with()方法预加载关联关系，避免循环查询产生N+1问题
• 如果返回结果集较大，可以用分页查询代替全量返回，进一步降低接口响应时间

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