我使用的是Laravel 4.2,我的应用程序用于跟踪多个地点的库存。
数据库由一个inventory_items
表,inventory_locations
表和它们之间的透视表inventory_items_inventory_location
组成,其中包含数量值,同时引用记录所属的库存项目和位置。
我的查询是查找任何位置数量值大于或等于0的库存项。在Laravel中,我使用了一个子查询,或者类似这样:
InventoryItem::whereHas('inventoryLocations', function($q) {
$q->where('reserved', '>=', 0)
->orWhere('available', '>=', 0) # slow
->orWhere('inbound', '>=', 0) # slow
->orWhere('total', '>=', 0); # slow
})->toSql();
它给出了以下SQL:
select * from `inventory_items`
where `inventory_items`.`deleted_at` is null
and (
select count(*) from `inventory_locations`
inner join `inventory_item_inventory_location`
on `inventory_locations`.`id` = `inventory_item_inventory_location`.`inventory_location_id`
where `inventory_item_inventory_location`.`inventory_item_id` = `inventory_items`.`id`
and `reserved` >= ?
or `available` >= ? # slow
or `inbound` >= ? # slow
or `total` >= ? # slow
) >= 1
问题是,对于
or
语句(在代码中用#slow
标记),Sequel Pro的查询时间最长可达1秒,通过我的Laravel应用程序(或通过artisan tinker)的查询时间超过5秒。如果没有这些“或”检查(即只检查一种数量类型,如“保留”),Sequel Pro上的查询小于100ms,app/tinker上的查询与此类似。我不知道为什么添加这些额外的“或”检查会给查询增加这么多时间。有什么办法让查询更高效吗?
最佳答案
查看结果查询及其WHERE条件。你肯定漏掉了一些括号,我想你需要的是
where `inventory_item_inventory_location`.`inventory_item_id` = `inventory_items`.`id`
and (
`reserved` >= ?
or `available` >= ? #
or `inbound` >= ?
or `total` >= ?
)
而不是
where `inventory_item_inventory_location`.`inventory_item_id` = `inventory_items`.`id`
and `reserved` >= ?
or `available` >= ? # slow
or `inbound` >= ? # slow
or `total` >= ?
它会导致全表扫描,这对于行数较大的表来说非常慢。
为了解决这个问题,更换
InventoryItem::whereHas('inventoryLocations', function($q) {
$q->where('reserved', '>=', 0)
->orWhere('available', '>=', 0) # slow
->orWhere('inbound', '>=', 0) # slow
->orWhere('total', '>=', 0); # slow
})->toSql();
具有
InventoryItem::whereHas('inventoryLocations', function($q) {
$q->where(function($subquery) {
$subquery->where('reserved', '>=', 0)
->orWhere('available', '>=', 0)
->orWhere('inbound', '>=', 0)
->orWhere('total', '>=', 0);
});
})->toSql();
查看MySQL的EXPLAIN命令,该命令允许您分析如何执行查询以及将查询多少行-http://dev.mysql.com/doc/refman/5.7/en/explain.html