Laravel中OrWhere/OrWhereHas致应用卡顿的解决方案咨询

1. 补充数据库索引 #

OrWhereHas 慢的核心原因之一是关联查询未命中索引，先给关键字段添加索引：

-- 给properties表的权限校验字段加索引
CREATE INDEX idx_properties_subscriber_id ON properties(subscriber_id);
CREATE INDEX idx_properties_auction_booked_by ON properties(auction_booked_by);

-- 给presentations表的预订字段加索引
CREATE INDEX idx_presentations_auction_booked_by ON presentations(auction_booked_by);

-- 多对多中间表（替换为你的实际表名，比如property_presentation）加联合索引
CREATE INDEX idx_property_presentation_ids ON property_presentation(property_id, presentation_id);

2. 用WHERE EXISTS替代OrWhereHas #

OrWhereHas 本质是嵌套子查询，换成显式的WHERE EXISTS更利于数据库优化执行计划，同时避免多对多关联导致的重复数据：

$currentSubscriber = Auth::user()->subscriber_id;
$linkedOfficesSubscribers = Auth::user()->subscriber->getLinkedAgencyIdsAttribute();
$subscriberIds = $linkedOfficesSubscribers->merge($currentSubscriber);
$sidewaysSubscriberIds = Auth::user()->subscriber->getSidewaysAgenciesIdsAttribute();

$query = $this->model
    ->join('subscribers', 'properties.subscriber_id', '=', 'subscribers.id')
    ->join('property_auction_details', 'property_auction_details.property_id', '=', 'properties.id')
    ->leftjoin('property_listing_details','property_listing_details.property_id', '=', 'properties.id')
    ->with('subscriber')
    ->where(function ($mainQuery) use ($subscriberIds, $sidewaysSubscriberIds, $currentSubscriber) {
        // 条件1+2：当前订阅者或关联办公点直接拥有房产
        $mainQuery->whereIn('properties.subscriber_id', $subscriberIds)
            // 条件3：关联办公点房产且当前订阅者预订过
            ->orWhere(function ($subQuery) use ($sidewaysSubscriberIds, $currentSubscriber) {
                $subQuery->whereIn('properties.subscriber_id', $sidewaysSubscriberIds)
                    ->where(function ($innerQuery) use ($currentSubscriber) {
                        $innerQuery->where('properties.auction_booked_by', $currentSubscriber)
                            ->orWhereExists(function ($existsQuery) use ($currentSubscriber) {
                                $existsQuery->select(DB::raw(1))
                                    ->from('property_presentation') // 替换为你的多对多中间表名
                                    ->join('presentations', 'property_presentation.presentation_id', '=', 'presentations.id')
                                    ->whereRaw('property_presentation.property_id = properties.id')
                                    ->where('presentations.auction_booked_by', $currentSubscriber);
                            });
                    });
            });
    })
    ->distinct(); // 避免多对多关联产生重复数据

3. 优化关联ID集合的获取 #

检查getLinkedAgencyIdsAttribute和getSidewaysAgenciesIdsAttribute两个属性的实现，避免N+1查询：

• 如果是动态查询，改成一次性whereIn批量获取ID
• 对高频访问的ID集合添加缓存（比如用Redis或Laravel缓存）

4. 移除不必要的Join #

如果join('subscribers')只是为了关联模型，但with('subscriber')已经实现预加载，且查询中没有用到subscribers表的过滤条件，可以直接移除这个Join，减少查询复杂度。

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