Laravel+MySQL多表关联计数查询优化：带同类型ID条件的性能问题

需求：获取指定owner_id的所有店铺（shops），同时统计每个店铺通过中间表shop_product关联且与该店铺type_id相同的商品（products）数量。

当前表结构：

• shops (id, type_id, owner_id)
• products (id, type_id)
• shop_product (shop_id, product_id)

已创建所有外键索引，shop_product表有(shop_id, product_id)复合索引。

当前生成的SQL查询：

select
    shops.*,
    (
    select
        count (*)
    from
        products
    inner join shop_products on
        products.id = shop_products.product_id
    where
        shops.id = shop_products.shop_id
        and products.type_id = shops.type_id)
from
    shops
where
    shops.owner_id in (?)

对应的Laravel代码：

Shop::whereIn('owner_id', [123])
    ->withCount(['products' => fn($query) => $query->whereColumn('products.type_id', '=', 'shops.type_id')])
    ->get()

怀疑products.type_id = shops.type_id的条件导致索引未被正确使用，寻求优化方案，包括是否可以不使用withCount的whereColumn，以及是否需要添加复合索引。

一、索引优化 #

• 给products表添加(type_id, id)复合索引：查询中需要先按type_id过滤商品，再匹配product_id关联中间表，该索引能让MySQL快速定位同类型商品，避免全表扫描。
• 给shops表添加(owner_id, id, type_id)复合索引：查询店铺时先按owner_id过滤，同时直接取出id和type_id字段，避免回表查询，提升后续关联或子查询的效率。

二、改写查询逻辑（替换子查询为JOIN） #

原查询使用关联子查询，大数据集下会对每个店铺单独执行一次统计查询，性能损耗大。改用JOIN + GROUP BY的方式，一次性完成所有店铺的统计：

优化后的SQL： #

SELECT
    shops.*,
    COUNT(products.id) AS products_count
FROM shops
LEFT JOIN shop_product ON shops.id = shop_product.shop_id
LEFT JOIN products ON shop_product.product_id = products.id AND products.type_id = shops.type_id
WHERE shops.owner_id IN (?)
GROUP BY shops.id

对应的Laravel代码： #

Shop::select('shops.*', DB::raw('COUNT(products.id) AS products_count'))
    ->leftJoin('shop_product', 'shops.id', '=', 'shop_product.shop_id')
    ->leftJoin('products', function ($join) {
        $join->on('shop_product.product_id', '=', 'products.id')
             ->whereColumn('products.type_id', '=', 'shops.type_id');
    })
    ->whereIn('owner_id', [123])
    ->groupBy('shops.id')
    ->get();

三、调整withCount使用方式（保留子查询场景） #

如果坚持使用withCount，可以在闭包中明确关联逻辑，引导MySQL使用索引，但性能仍不如JOIN方案：

Shop::whereIn('owner_id', [123])
    ->withCount(['products' => function ($query) {
        $query->select(DB::raw('count(*)'))
              ->join('shop_product', 'products.id', '=', 'shop_product.product_id')
              ->whereColumn('shop_product.shop_id', '=', 'shops.id')
              ->whereColumn('products.type_id', '=', 'shops.type_id');
    }])
    ->get();

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