使用附加条件通过中间表计算Laravel和Mysql相关的模型的数量。

Question

我有一个MySQL查询（使用Laravel Eloquent的急切加载和withCount函数构建），在处理大数据集时有一些性能问题，有没有办法改进下面的查询？

我需要获取所有的商店，并计算与商店相关的产品数量（通过中间表关联），但是还有一个附加条件，即商店的type_id等于产品的type_id。我认为这个第二个条件导致查询没有使用正确的索引。

两个模型之间有一个中间表。

商店（id，type_id，owner_id）产品（id，type_id）商店产品（shop_id，product_id）

我在所有外键上都有索引，还在shop_product（shop_id，product_id）上有一个复合索引。

所以我的查询是这样的：

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 (?)

is it possible that this query could be optimized somehow, maybe not using this laravel's withCount whereColumn query?

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

完整的查询是这样的：

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

我是否需要在商店（id，type_id）和产品（id，type_id）上添加组合索引？

Answer 1

我没有测试过这个，但我会尝试类似的东西

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

所以我刚刚添加了一些字段（你需要的字段和应用程序需要识别产品的字段），但如果只需要计数，我会尝试不使用ID。

我假设当你获取“products”时，它会拉取所有数据，如果有像body/description等“text”类型字段，速度会很慢。

此外，不确定，但你可以尝试使用'type_id'而不是'products.type_id'，因为你已经在'products'关系中了。还可以检查优化拉取商店的方式。