如何在 PostgreSQL 中高效查询 JSON 字段并优化用户权限过滤逻辑-php教程-PHP中文网

如何在 PostgreSQL 中高效查询 JSON 字段并优化用户权限过滤逻辑

本文介绍如何将低效的 PHP 循环 + JSON 解析逻辑，重构为一条高性能的 Laravel Eloquent 查询，直接在数据库层完成 user_id 匹配与 shared_user_ids（JSON 数组）包含判断，显著提升面试记录列表加载速度。

本文介绍如何将低效的 php 循环 + json 解析逻辑，重构为一条高性能的 laravel eloquent 查询，直接在数据库层完成 `user_id` 匹配与 `shared_user_ids`（json 数组）包含判断，显著提升面试记录列表加载速度。

在 Laravel + PostgreSQL 应用中，当需要根据当前用户权限动态筛选记录（如：用户本人创建的记录，或被显式共享给该用户的记录），若将 JSON 字段解析和匹配逻辑放在 PHP 层处理（如遍历集合、json_decode()、in_array()），会导致严重的性能瓶颈——尤其当 interviews 表数据量增长时，全表加载 + 逐行解码会引发内存飙升与响应延迟。

根本问题在于：本应在数据库完成的过滤，被错误地移到了应用层。 PostgreSQL 原生支持高效的 JSONB 操作，Laravel 9+ 也提供了完善的 whereJsonContains() 等高级查询方法，可直接利用数据库索引加速匹配。

✅ 正确做法：将全部条件下推至 SQL 层，一次性获取授权数据：

$interviews = Interview::whereIn('archived', [0, false, null])
    ->where(function (Builder $query) use ($user) {
        $query->where('user_id', $user->id)
              ->orWhereJsonContains('shared_user_ids', $user->id);
    })
    ->orderBy('created_at', 'DESC')
    ->get();

? 关键说明：

Json Schema Toolkit
使用 JSON Schema 验证 JSON 数据，从示例 JSON 生成 schema，并将其转换为 TypeScript 接口、Python 数据类或 Markdown 文档。

下载
whereJsonContains('shared_user_ids', $user->id) 要求 shared_user_ids 字段类型为 jsonb（PostgreSQL 推荐），且需在该字段上创建 GIN 索引以保障性能：
CREATE INDEX idx_interviews_shared_users ON interviews USING GIN (shared_user_ids);
whereIn('archived', [0, false, null]) 在 PostgreSQL 中需注意：NULL 不能用 = 判断，但 WHERE archived IN (0, FALSE) 会自动忽略 NULL；若需显式包含 NULL，应改写为：
->where(function ($q) {
    $q->whereIn('archived', [0, false])
      ->orWhereNull('archived');
})
orderBy('created_at', 'DESC') 替代 sortBy()->reverse()：后者是在内存中对 Eloquent Collection 排序，无法利用数据库索引；前者生成 ORDER BY created_at DESC，由数据库高效执行。

? 进阶建议：

若 shared_user_ids 存储格式为纯整数数组（如 [1, 5, 8]），确保其为标准 JSON 数组，避免字符串化（如 "[1,5,8]"）导致 whereJsonContains 失效；
对于高频访问场景，可考虑冗余一个 shared_user_ids_text 字段（text 类型 + GIN 索引）或使用物化视图预计算权限关系；
避免在 SELECT * 后大量调用模型访问器（如 getOpenRates()）。如需聚合数据，优先使用 withCount() 或原生 JOIN/GROUP BY 优化。