如何优化PHP（Laravel）大数据量下的搜索查询速度？

问题场景 #

我开发的视频搜索功能在数据量较小时运行正常，但数据库存入上万条数据后，执行搜索操作时网站出现加载卡顿。当前实现代码如下：

public function searchVideo(Request $request)
{
    $searchTerm = $request->search;
    $videos = Videos::with(['tags:id', 'stars:id'])
        ->where('title', 'LIKE', '%'.$searchTerm.'%')
        ->orWhereHas('tags', function ($query) use ($searchTerm) {
            $query->where('name', 'LIKE', '%'.$searchTerm.'%');
        })
        ->orWhereHas('stars', function ($query) use ($searchTerm) {
            $query->where('name', 'LIKE', '%'.$searchTerm.'%');
        })
        ->orderBy('created_at', 'desc')
        ->paginate(15);

    return $videos;
}

删除关联标签和明星的orWhereHas查询片段后，搜索恢复正常，但无法获取完整的预期搜索结果。需要在保留全量搜索结果的同时提升查询速度。

性能瓶颈分析 #

1. 前缀通配符LIKE '%xxx%'无法利用索引：这种写法会触发全表扫描，数据量上万时，数据库需要遍历大量数据，导致查询耗时剧增。
2. 多orWhereHas导致关联查询开销过大：orWhereHas会多次关联中间表，加上OR条件会让数据库优化器难以选择最优执行计划，进一步拖慢查询速度。

优化方案 #

1. 替换LIKE为全文索引查询

主流数据库都支持全文索引，相比LIKE通配符查询，能极大提升模糊搜索的性能。

• 给对应字段添加全文索引：

  -- 给videos表的title字段加全文索引
  ALTER TABLE videos ADD FULLTEXT INDEX ft_videos_title(title);
  -- 给tags表的name字段加全文索引
  ALTER TABLE tags ADD FULLTEXT INDEX ft_tags_name(name);
  -- 给stars表的name字段加全文索引
  ALTER TABLE stars ADD FULLTEXT INDEX ft_stars_name(name);
  

• 修改Laravel查询代码，使用全文搜索语法：

  public function searchVideo(Request $request)
  {
      $searchTerm = $request->search;
      $videos = Videos::with(['tags:id', 'stars:id'])
          ->where(function ($query) use ($searchTerm) {
              // 匹配视频标题
              $query->whereRaw("MATCH(title) AGAINST(? IN BOOLEAN MODE)", [$searchTerm])
                    // 匹配关联标签
                    ->orWhereHas('tags', function ($subQuery) use ($searchTerm) {
                        $subQuery->whereRaw("MATCH(name) AGAINST(? IN BOOLEAN MODE)", [$searchTerm]);
                    })
                    // 匹配关联明星
                    ->orWhereHas('stars', function ($subQuery) use ($searchTerm) {
                        $subQuery->whereRaw("MATCH(name) AGAINST(? IN BOOLEAN MODE)", [$searchTerm]);
                    });
          })
          ->orderBy('created_at', 'desc')
          ->paginate(15);
  
      return $videos;
  }
  

2. 调整查询结构，分组OR条件

将所有OR条件包裹在一个where()闭包内，避免和其他潜在条件冲突，同时帮助数据库优化器更高效解析查询逻辑：

$videos = Videos::with(['tags:id', 'stars:id'])
    ->where(function ($query) use ($searchTerm) {
        $query->where('title', 'LIKE', '%'.$searchTerm.'%')
              ->orWhereHas('tags', fn($sub) => $sub->where('name', 'LIKE', '%'.$searchTerm.'%'))
              ->orWhereHas('stars', fn($sub) => $sub->where('name', 'LIKE', '%'.$searchTerm.'%'));
    })
    ->orderBy('created_at', 'desc')
    ->paginate(15);

3. 预查询关联ID，减少关联开销

先查询匹配的标签/明星ID，再通过子查询筛选对应视频，避免多次关联中间表的开销：

public function searchVideo(Request $request)
{
    $searchTerm = $request->search;
    
    // 先获取匹配的标签ID和明星ID
    $matchingTagIds = Tag::where('name', 'LIKE', '%'.$searchTerm.'%')->pluck('id');
    $matchingStarIds = Star::where('name', 'LIKE', '%'.$searchTerm.'%')->pluck('id');

    $videos = Videos::with(['tags:id', 'stars:id'])
        ->where(function ($query) use ($searchTerm, $matchingTagIds, $matchingStarIds) {
            $query->where('title', 'LIKE', '%'.$searchTerm.'%')
                  // 通过子查询匹配关联标签的视频
                  ->orWhereIn('id', function ($subQuery) use ($matchingTagIds) {
                      $subQuery->select('video_id')
                               ->from('video_tag') // 替换为你的视频-标签中间表名
                               ->whereIn('tag_id', $matchingTagIds);
                  })
                  // 通过子查询匹配关联明星的视频
                  ->orWhereIn('id', function ($subQuery) use ($matchingStarIds) {
                      $subQuery->select('video_id')
                               ->from('video_star') // 替换为你的视频-明星中间表名
                               ->whereIn('star_id', $matchingStarIds);
                  });
        })
        ->orderBy('created_at', 'desc')
        ->paginate(15);

    return $videos;
}

4. 添加关键词长度限制

避免过短的关键词（比如1个字符）导致返回大量无关结果，同时减少数据库扫描范围：

$searchTerm = trim($request->search);
if (strlen($searchTerm) < 2) {
    return response()->json(['message' => '关键词长度不能少于2个字符'], 400);
}

5. 缓存热门搜索结果

对于高频搜索的关键词，用Redis等缓存工具缓存查询结果，避免重复查询数据库：

use Illuminate\Support\Facades\Cache;

public function searchVideo(Request $request)
{
    $searchTerm = trim($request->search);
    if (strlen($searchTerm) < 2) {
        return response()->json(['message' => '关键词长度不能少于2个字符'], 400);
    }

    $cacheKey = 'search_videos_' . md5($searchTerm);
    // 缓存1小时
    $videos = Cache::remember($cacheKey, 3600, function () use ($searchTerm) {
        return Videos::with(['tags:id', 'stars:id'])
            ->where(function ($query) use ($searchTerm) {
                $query->whereRaw("MATCH(title) AGAINST(? IN BOOLEAN MODE)", [$searchTerm])
                      ->orWhereHas('tags', fn($sub) => $sub->whereRaw("MATCH(name) AGAINST(? IN BOOLEAN MODE)", [$searchTerm]))
                      ->orWhereHas('stars', fn($sub) => $sub->whereRaw("MATCH(name) AGAINST(? IN BOOLEAN MODE)", [$searchTerm]));
            })
            ->orderBy('created_at', 'desc')
            ->paginate(15);
    });

    return $videos;
}

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