Laravel多withCount()全量数据加载过慢的优化方案咨询

在Laravel项目中，需要从关联表统计不同条件的数据，目前使用多个带条件的withCount()来获取结果。分页查询时性能正常，但需求要求一次性获取所有2000条数据并处理，此时加载速度极慢。

已尝试三种优化方式：

• 遍历每条数据计算关联表数据：性能最差
• 使用with()预加载关联表再做集合筛选：性能无提升
• 当前的多withCount()方案：三者中最优，但仍不理想

所有表已添加索引，现寻求更优的性能优化实践或方法。

当前代码示例：

$products = Product::withCount(['tableA AS tableA_condition_A' => function($query){
   $query->whereIn('status', [1,2,3])->whereNotIn('type', [1,2,3]);
}])
->withCount(['tableA AS tableA_condition_B' => function($query){
   $query->whereIn('status', [1,2,3])->whereIn('type', [1,2,3]);
}])
->withCount(['tableB AS tableB_condition_A' => function($query){
   $query->whereNotIn('type', [1,2,3])->whereHas('tableC.tableD', function($subquery){
        $subquery->whereIn('group', [1,2,3])->whereIn('state', [1,2,3]);
   });
}])
->withCount(['tableB AS tableB_condition_B' => function($query){
   $query->whereIn('type', [1,2,3])->whereHas('tableC.tableD', function($subquery){
        $subquery->whereIn('group', [1,2,3])->whereIn('state', [1,2,3]);
   });
}])
// 还有更多类似带条件的withCount()
->get();

// 在进入foreach前，get()耗时很长

// 获取带统计数据的products后进行处理
foreach($products as $model){
   // 处理逻辑...
}

优化方案 #

1. 合并关联统计查询，用原生SQL子查询替代多withCount() #

多个withCount()会生成多条独立的子查询，每一个都会对关联表进行一次扫描。通过自定义select子查询，把多个统计合并到更少的查询中，减少数据库扫描次数。

示例代码：

$products = Product::select('products.*',
    // 统计tableA的两个条件
    DB::raw('(SELECT COUNT(*) FROM table_a WHERE table_a.product_id = products.id AND status IN (1,2,3) AND type NOT IN (1,2,3)) AS tableA_condition_A'),
    DB::raw('(SELECT COUNT(*) FROM table_a WHERE table_a.product_id = products.id AND status IN (1,2,3) AND type IN (1,2,3)) AS tableA_condition_B'),
    // 统计tableB的两个条件，用EXISTS替代whereHas提升性能
    DB::raw('(SELECT COUNT(*) FROM table_b WHERE table_b.product_id = products.id AND type NOT IN (1,2,3) AND EXISTS (SELECT 1 FROM table_c tc JOIN table_d td ON tc.id = table_b.table_c_id WHERE tc.table_d_id = td.id AND td.`group` IN (1,2,3) AND td.state IN (1,2,3))) AS tableB_condition_A'),
    DB::raw('(SELECT COUNT(*) FROM table_b WHERE table_b.product_id = products.id AND type IN (1,2,3) AND EXISTS (SELECT 1 FROM table_c tc JOIN table_d td ON tc.id = table_b.table_c_id WHERE tc.table_d_id = td.id AND td.`group` IN (1,2,3) AND td.state IN (1,2,3))) AS tableB_condition_B')
)->get();

EXISTS在找到匹配项后会停止扫描，性能比JOIN更优；同时把多个统计合并到主查询的select中，避免Laravel自动生成多条子查询的开销。

2. 使用数据库视图（Database View） #

如果统计条件固定，可以创建数据库视图封装统计逻辑，之后在Laravel中直接关联视图查询。

步骤：

1. 创建视图product_statistics：

CREATE VIEW product_statistics AS
SELECT
    p.id AS product_id,
    COUNT(CASE WHEN ta.status IN (1,2,3) AND ta.type NOT IN (1,2,3) THEN 1 END) AS tableA_condition_A,
    COUNT(CASE WHEN ta.status IN (1,2,3) AND ta.type IN (1,2,3) THEN 1 END) AS tableA_condition_B,
    COUNT(CASE WHEN tb.type NOT IN (1,2,3) AND EXISTS (SELECT 1 FROM table_c tc JOIN table_d td ON tc.id = tb.table_c_id WHERE tc.table_d_id = td.id AND td.`group` IN (1,2,3) AND td.state IN (1,2,3)) THEN 1 END) AS tableB_condition_A,
    COUNT(CASE WHEN tb.type IN (1,2,3) AND EXISTS (SELECT 1 FROM table_c tc JOIN table_d td ON tc.id = tb.table_c_id WHERE tc.table_d_id = td.id AND td.`group` IN (1,2,3) AND td.state IN (1,2,3)) THEN 1 END) AS tableB_condition_B
FROM products p
LEFT JOIN table_a ta ON ta.product_id = p.id
LEFT JOIN table_b tb ON tb.product_id = p.id
GROUP BY p.id;

2. 在Laravel中关联视图查询：

$products = Product::join('product_statistics', 'products.id', '=', 'product_statistics.product_id')
    ->select('products.*', 'product_statistics.*')
    ->get();

视图会预先计算统计数据，查询时直接读取，避免每次重复执行复杂统计逻辑。

3. 分批处理数据 #

如果业务允许，用chunk()分批获取处理数据，减少单次查询的内存和数据库负载：

Product::withCount([/* 原有的withCount配置 */])
    ->chunk(200, function ($products) {
        foreach ($products as $model) {
            // 处理逻辑
        }
    });

每次只处理200条数据，数据库压力更小，不会出现一次性加载的长时间等待。

4. 优化关联查询的索引 #

确保索引有效性，针对查询场景创建复合索引：

• table_a：(product_id, status, type)
• table_b：(product_id, type)
• table_c：(id, table_d_id)
• table_d：(id, group, state)

用EXPLAIN分析查询语句，验证索引是否被正确使用。

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