优化Laravel Eloquent查询：降低MySQL查询耗时的可行方案

我在模型中定义了如下关联查询：

$this->hasMany('App\Inventory')
    ->where('status',1)
    ->whereNull('deleted_at')
    ->where(function($query){
        $query
            ->where('count', '>=', 1)
            ->orWhere(function($aQuery){
                $aQuery
                    ->where('count', '=' , 0)
                    ->whereHas('containers', function($bQuery){
                        $bQuery->whereIn('status', [0,1]);
                    });
            });
    })
    ->orderBy('updated_at','desc')
    ->with('address', 'cabin');

生成的SQL查询语句如下：

select
  *
from
  `inventories`
where
  `inventories`.`user_id` = 17
  and `inventories`.`user_id` is not null
  and `status` = 1
  and `deleted_at` is null
  and (
    `count` >= 1
    or (
      `count` = 0
      and exists (
        select
          *
        from
          `containers`
        where
          `inventories`.`id` = `containers`.`inventory_id`
          and `status` in (0, 1)
      )
    )
  )
  and `inventories`.`deleted_at` is null
order by
  `updated_at` desc
limit
  10 offset 0

该查询在MySQL中耗时超过2秒，现寻求优化方案以缩短查询时间。

背景说明 #

每个inventory对应多个containers，当inventory的count为0时（0代表缺货，但有时存在禁用的容器，此时库存并未真正缺货），实际库存需根据status为[0,1]的containers数量判断。我曾考虑在inventory表新增字段统计符合条件的containers数量，但该方案需修改其他业务流程，耗时较高。

表结构 #

Inventories表结构

CREATE TABLE `inventories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `cabin_id` bigint unsigned NOT NULL,
  `address_id` bigint unsigned NOT NULL,
  `count` mediumint NOT NULL,
  `status` mediumint NOT NULL,
  `name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `available_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37837 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Containers表结构

CREATE TABLE `containers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inventory_id` bigint unsigned NOT NULL,
  `order_id` bigint unsigned DEFAULT NULL,
  `status` tinyint unsigned NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=64503 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

最终优化方案（感谢@ysth、@vixducis、@Breezer） #

• 将Containers表的存储引擎从MyISAM改为InnoDB；
• 为containers.inventory_id添加索引；
• 优化查询代码并限制whereHas的查询字段：

$this->hasMany('App\Inventory')
    ->where('status',1)
    ->whereNull('deleted_at')
    ->where(function($query){
        $query
            ->where('count', '>=', 1)
            ->orWhere('count', '=' , 0)
            ->whereHas('containers', function ($bQuery) {
                $bQuery
                    ->select('inventory_id')
                    ->whereIn('status', [0, 1]);
            });

    })
    ->orderBy('updated_at','desc')
    ->with('address', 'cabin');

其他可选优化方式 #

1. 使用whereIn子查询替代whereHas：

->whereIn('id', function ($subQuery) {
    $subQuery->select('inventory_id')
         ->from('containers')
         ->whereIn('status', [0, 1]);
});

2. 优化doesntHave查询的字段选择：

->doesntHave('requests', 'and', function($query){
    $query->select('inventory_id');
})

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