91K条记录的Locality模型关联查询：已选数据时API性能问题求助

1. 优化数据库索引 #

先补全必要索引，这是提升查询速度的核心基础：

• 给localities.city_id添加普通索引：关联查询时该字段用于匹配cities.id，无索引会触发全表扫描。

  CREATE INDEX idx_localities_city_id ON localities(city_id);
  

• 给localities.name添加普通索引：虽然前缀模糊查询（%xxx%）无法利用索引，但orderBy('localities.name')可借助索引避免文件排序（filesort），大幅降低排序耗时。

  CREATE INDEX idx_localities_name ON localities(name);
  

2. 调整查询逻辑顺序 #

原查询先执行join再筛选selected的ID，会先关联全表数据再过滤，浪费大量资源。调整为先筛选再关联，缩小关联数据集：

$data = Locality::query()
    // 先处理selected筛选，提前缩小数据集范围
    ->when($request->exists('selected'), fn (Builder $query) => $query->whereIn('id', $request->input('selected', [])),
        fn (Builder $query) => $query->limit(10))
    // 再执行表关联操作
    ->join('cities', 'cities.id', '=', 'localities.city_id')
    ->select('cities.id as id', DB::raw("CONCAT(localities.name, ', ', cities.name) AS localityCity"))
    ->orderBy('localities.name')
    ->when($request->search, fn (Builder $query) => $query->where('localities.name', 'like', "%{$request->search}%"))
    ->get();

3. 分批次处理大量ID的whereIn #

当selected传入的ID数量极大（比如上万条），单次whereIn会导致查询超时。将ID分批次查询后合并结果：

if ($request->exists('selected')) {
    $selectedIds = $request->input('selected', []);
    $data = collect();
    
    // 每次处理1000条ID，可根据数据库性能调整批次大小
    foreach (array_chunk($selectedIds, 1000) as $idChunk) {
        $chunkData = Locality::query()
            ->whereIn('id', $idChunk)
            ->join('cities', 'cities.id', '=', 'localities.city_id')
            ->select('cities.id as id', DB::raw("CONCAT(localities.name, ', ', cities.name) AS localityCity"))
            ->when($request->search, fn (Builder $query) => $query->where('localities.name', 'like', "%{$request->search}%"))
            ->get();
        
        $data = $data->merge($chunkData);
    }
    
    // 统一排序（分批次查询的排序是局部的，需整体调整）
    $data = $data->sortBy('localityCity')->values();
} else {
    $data = Locality::query()
        ->join('cities', 'cities.id', '=', 'localities.city_id')
        ->select('cities.id as id', DB::raw("CONCAT(localities.name, ', ', cities.name) AS localityCity"))
        ->orderBy('localities.name')
        ->when($request->search, fn (Builder $query) => $query->where('localities.name', 'like', "%{$request->search}%"))
        ->limit(10)
        ->get();
}

4. 优化模糊查询（可选） #

如果业务允许，将前缀模糊查询（%xxx%）改为后缀模糊查询（xxx%），可直接利用localities.name的索引大幅提速。若必须保留全模糊查询，可改用全文搜索：

• 给localities.name添加全文索引：

  CREATE FULLTEXT INDEX idx_localities_name_fulltext ON localities(name);
  

  对应的查询代码修改为：

  ->when($request->search, fn (Builder $query) => $query->whereRaw("MATCH(localities.name) AGAINST(? IN BOOLEAN MODE)", [$request->search]))
  

5. 缓存重复请求（可选） #

如果相同的selected和search组合会频繁出现，用Laravel缓存缓存查询结果，避免重复执行数据库查询：

$cacheKey = 'locality_data_' . md5(implode(',', $request->input('selected', [])) . '_' . $request->search);
$data = Cache::remember($cacheKey, 300, function () use ($request) {
    // 放入上述优化后的查询逻辑
});

内容的提问来源于stack exchange，提问作者Syed Kounain Abbas Rizvi