Laravel+MySQL：如何优化含3000个值的批量查询？

当前查询耗时10分钟的核心问题是：3000个循环生成了15000个OR条件，加上%xxx%的模糊匹配完全失效索引，导致数据库被迫全表扫描多次。以下是针对性的优化步骤：

1. 合并精确匹配条件，用IN替代大量OR #

原循环中每个编码对应4个字段的orWhere，会生成冗余的SQL逻辑。改用whereIn批量处理，数据库对IN的优化远优于零散OR：

$q = Product::query();

// 合并四个精确匹配字段的查询逻辑
$q->where(function ($query) use ($keys) {
    $query->whereIn('codfabrica', $keys)
          ->orWhereIn('gtin', $keys)
          ->orWhereIn('codoriginal', $keys)
          ->orWhereIn('codreferencia', $keys);
});

2. 彻底优化模糊查询（aplicacao LIKE %xxx%） #

%前缀的模糊匹配会直接失效索引，3000次这类查询是性能瓶颈，建议替换为全文索引：

步骤1：给aplicacao创建全文索引 #

执行SQL语句添加索引：

ALTER TABLE products ADD FULLTEXT INDEX idx_aplicacao_fulltext (aplicacao);

步骤2：用全文搜索替代模糊匹配 #

将3000个编码拼成布尔模式的查询字符串，一次匹配所有关键词：

$fulltextKeywords = implode(' ', $keys);
$q->orWhereRaw('MATCH(aplicacao) AGAINST (? IN BOOLEAN MODE)', [$fulltextKeywords]);

如果业务不允许用全文索引，可考虑先查精确匹配的记录，再单独查询模糊匹配且未被精确匹配到的记录，缩小扫描范围。

3. 给精确匹配字段添加单独索引 #

给四个varchar字段分别添加普通索引，让whereIn能命中索引，避免全表扫描：

ALTER TABLE products ADD INDEX idx_codfabrica (codfabrica);
ALTER TABLE products ADD INDEX idx_gtin (gtin);
ALTER TABLE products ADD INDEX idx_codoriginal (codoriginal);
ALTER TABLE products ADD INDEX idx_codreferencia (codreferencia);

4. 分批次处理大数量编码 #

3000个编码单次处理仍可能给数据库带来压力，拆分为小批次查询后合并结果：

$found = collect();
// 每100个编码为一组，可根据数据库性能调整批次大小
$batches = array_chunk($keys, 100);

foreach ($batches as $batch) {
    $batchResults = Product::query()
        ->where(function ($query) use ($batch) {
            $query->whereIn('codfabrica', $batch)
                  ->orWhereIn('gtin', $batch)
                  ->orWhereIn('codoriginal', $batch)
                  ->orWhereIn('codreferencia', $batch);
        })
        ->orWhere(function ($query) use ($batch) {
            $fulltextKeywords = implode(' ', $batch);
            $query->whereRaw('MATCH(aplicacao) AGAINST (? IN BOOLEAN MODE)', [$fulltextKeywords]);
        })
        ->get();
    
    $found = $found->merge($batchResults);
}

// 去重避免重复记录
$found = $found->unique('id');

5. 清理无用条件 #

原代码中的$q->where('codigo', '');如果不是业务必需，直接删除；如果必须保留，给codigo字段添加索引，避免额外的全表扫描。

6. 原生SQL进一步优化（可选） #

用UNION ALL合并精确匹配和模糊查询的结果，减少数据库执行逻辑的复杂度：

SELECT * FROM products
WHERE codfabrica IN (...) 
   OR gtin IN (...) 
   OR codoriginal IN (...) 
   OR codreferencia IN (...)
UNION ALL
SELECT * FROM products
WHERE MATCH(aplicacao) AGAINST ('关键词1 关键词2 ...' IN BOOLEAN MODE)

在Laravel中通过DB::select()执行原生SQL即可。

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