mysql - 如何在查询中优化使用 orWhere()？

问题描述

这是我的查询：

$notifications = \App\Notification::query()->where('users_scope', 'customer')
   ->with('userSeen')
   ->where(function ($query) use ($user) {
    $query->where('user_id_to', 20288)
        ->orWhere(function($q) use ($user) {
            $q->Where('user_id_to', null)
              ->Where(function($q) use ($user) {
                    $q->where('expire_at', null)->where('created_at', '>=', "2020-04-03 04:18:42");
                    $q->orWhere('expire_at', '!=', null)->where('expire_at', '>', Carbon::now());
                });
        });
    })
    ->select([DB::raw('SQL_CALC_FOUND_ROWS *')])->orderBy('id', 'desc')->limit(20)->get();

这里也是关系（用作with()）：

public function userSeen()
{
    return $this->belongsToMany(User::class, NotificationSeen::class, 'notification_id', 'user_id');
}

这是场景：上面的查询获取最后 20 个用户的通知（最新通知列表）。最近，notifications表的大小增加了太多。目前它有超过 800 万行。并且查询需要超过 10 秒才能执行。

请注意，所有需要的索引也已在两个表（notifications和userSeen表）上创建。此外，该关系 ( userSeen) 就像一个数据透视表，指示用户是否已看到通知。

知道如何将该查询重写为更优化吗？

关于逻辑的解释：

20288是硬编码的，将$user->id在现实中。
when user_id_tois null，这意味着它是一个批量通知（必须对所有用户可见）
created_at如果批量通知的值大于用户的值，则用户可以看到批量通知created_at。
有时批量通知有一个过期时间（如营销活动），如果仍未过期，则必须向用户显示。

标签： mysqllaravel

根据您在问题中提到的逻辑，这应该可以满足您的需求，但是，它可能对速度没有太大帮助：

$notifications = \App\Notification::query()
    ->select([DB::raw('SQL_CALC_FOUND_ROWS *')])
    ->with('userSeen')
    ->where('users_scope', 'customer')
    ->where(function ($query) use ($user) {
        $query
            ->where('user_id_to', $user->id)
            ->orWhere(function ($query) use ($user) {
                $query
                    ->whereNull('user_id_to')
                    ->where('created_at', '>=', $user->created_at)
                    ->where(function ($query) {
                        $query->whereNull('expire_at')->orWhere('expire_at', '>=', now());
                    });
            });
    })
    ->orderByDesc('id')
    ->limit(20)
    ->get();

我还建议尝试 2 个单独的查询，而不是使用SQL_CALC_FOUND_ROWS. 以下是一些解释原因的 SO 帖子：

哪个最快？SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)
SELECT SQL_CALC_FOUND_ROWS 查询非常慢，超过 250000 条记录

您的查询将类似于：

$query = \App\Notification::query()
    ->with('userSeen')
    ->where('users_scope', 'customer')
    ->where(function ($query) use ($user) {
        $query
            ->where('user_id_to', $user->id)
            ->orWhere(function ($query) use ($user) {
                $query
                    ->whereNull('user_id_to')
                    ->where('created_at', '>=', $user->created_at)
                    ->where(function ($query) {
                        $query->whereNull('expire_at')->orWhere('expire_at', '>=', now());
                    });
            });
    })
    ->orderByDesc('id');

$count = $query->count();
$notifications = $query->limit(20)->get();

或者，您可以使用类似paginate()的东西。

mysql - 如何在查询中优化使用 orWhere()？

问题描述

解决方案

推荐阅读