Laravel 数据库性能优化实战（四）：通过子查询优化关联查询性能

接下来的几篇教程我们一起来看几个 Eloquent 模型类关联查询场景性能优化的例子，首先看一个从一对多关联模型中获取一条记录的查询场景，我们以用户与文章之间的一对多关联为例进行演示。

用户模型数据准备

开始之前，还是为相关模型准备好测试数据。

为 posts 数据表创建一个填充类：

php artisan make:seeder PostsSeeder 

通过模型工厂为 User ID 为 9980~10000 的每个用户创建 100 篇文章：

<?php

namespace Database\Seeders;

use App\Models\Post;
use Illuminate\Database\Seeder;

class PostsSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        for ($id = 10000; $id >= 9980; $id--) {
            Post::factory(['user_id' => $id])->count(100)->create();
        }
     }
}

运行这个填充器：

php artisan db:seed --class=PostsSeeder  

数据填充成功后，就完成了用户与文章模型的数据准备工作。在 User 模型类中定义用户与文章模型的一对多关联关系：

class User extends Authenticatable
{
    ...
    
    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

用户列表展示最新发布文章

接下来，我们来模拟一个一对多关联查询获取单条记录的业务场景 —— 在用户列表为每个用户显示其最新创建的已发布文章（用户与文章记录之间是一对多关联，然后在该用户的多条相关文章记录中选择已发布的最新一篇文章）。

首先来看常规实现，要实现该功能，最常见的实现思路如下（源码位于 UserController 中）：

<?php

namespace App\Http\Controllers;

use App\Models\Post;
use App\Models\User;

class UserController extends Controller
{
    public function index()
    {
        $columns = ['id', 'name', 'email', 'created_at'];
        $users = User::with(['posts' => function($query) {
            $query->select(['id', 'title', 'created_at', 'user_id'])
                ->where('status', Post::STATUS_NORMAL);
        }])->orderByDesc('id')->paginate(20, $columns);
        return view('user.index', ['users' => $users]);
    }
}

我们前面已经介绍过通过设置索引、渴求式加载和指定查询字段优化查询性能，这些知识点已经都应用到上面的关联查询代码中。

接下来，将视图模板 resources/views/user/index.blade.php 中的用户创建时间字段 Created At 修改为最新发布文章字段 Last Created：

<table class="w-full m-8">
    <thead>
    <tr>
        <th class="px-4 py-2">ID</th>
        <th class="px-4 py-2">Name</th>
        <th class="px-4 py-2">Email</th>
        <th class="px-4 py-2">Last Created</th>
    </tr>
    </thead>
    <tbody>
    @foreach ($users as $user)
        <tr>
            <td class="border px-4 py-2">{{ $user->id }}</td>
            <td class="border px-4 py-2">{{ $user->name }}</td>
            <td class="border px-4 py-2">{{ $user->email }}</td>
            <td class="border px-4 py-2">{{ $user->posts->sortByDesc('created_at')->first()->title }}</td>
        </tr>
    @endforeach
    </tbody>
</table>

需要注意的是，这段代码中的 $users->posts 包含了该用户对应的所有文章实例集合，所以这里我们使用了相应的集合方法 sortByDesc 和 first 方法过滤出按照发布时间逆序排序的第一篇文章，最后渲染该文章的标题字段。

刷新用户列表页，就可以看到对应的渲染结果了：

由于我们在 user_id 外键字段设置了索引，并且通过渴求式加载解决了 N+1 查询问题，以及指定查询字段优化了查询性能，所以整体数据库查询性能还算 OK，但是这里引入的是另外一个问题，用户与文章模型是一对多关联，而我们无法在上述渴求式加载对应的关联查询代码中针对单个用户的单个文章关联关系进行过滤，所以引入了很多的根本没有用的模型实例：

理论上来说，我们只需要获取 21 个 Post 模型实例即可（懒惰式加载的话会是这个结果），这里却引入了 659 个！获取过多的无用数据肯定会影响性能，不管是内存使用还是查询时间上都会有影响，这里只是每个用户 100 篇文章，如果是 1000、10000 篇呢，需要加载成千上万条记录，显然需要优化这一块的查询逻辑。

那么该如何优化呢？回到懒惰式加载的老路？这肯定不行，好马不吃回头草，何况也不是好草，会引入查询性能的问题，下面我们一起来看看怎么解决这个问题。

通过子查询优化内存使用

我们从 Laravel Debugbar 工具条中把相关的 SQL 查询语句拷贝出来：

select `id`, `name`, `email`, `created_at` from `users` order by `id` desc limit 20 offset 0

select `id`, `title`, `created_at`, `user_id` from `posts` where `posts`.`user_id` in (9981, 9982, 9983, 9984, 9985, 9986, 9987, 9988, 9989, 9990, 9991, 9992, 9993, 9994, 9995, 9996, 9997, 9998, 9999, 10000) and `status` = 2

有没有一种方式把每个用户对应的最新发布文章和用户数据记录查询合并在一起返回呢？

使用连接查询（用户与文章之间通过 left join 进行连接查询）是不行的，因为还要涉及被驱动表 posts 的排序和限定查询问题，否则的话我们在上面的关联查询代码中就可以实现了（你可以自行去测试下，在渴求式加载的关联模型闭包函数查询代码中，不能对关联模型进行限定查询，否则出来的结果是不对的）。

我们再探索别的思路。

如果要将两张表的查询结果合并到一条 SQL 查询语句获取，除了连接查询外，还可以使用子查询，我们可以将用户 ID 映射到的关联文章记录通过子查询进行过滤，获取该用户发布的最新文章，然后将返回结果作为用户表查询的一个字段返回，不过子查询的话只能返回一条记录、一个字段：

使用子查询的话需要避免引入额外的查询性能问题，你可以通过 explain 语句分析其执行计划：

由于可以使用 users 表的主键和 posts 表的 user_id 索引，所以整体查询性能还是不错的。

下面我们按照上面给出的子查询语句重构 UserController 的 index 方法查询代码如下：

class UserController extends Controller
{
    public function index()
    {
        $columns = ['id', 'name', 'email', 'created_at'];
        $users = User::addSelect([
            'last_post_title' => Post::select(['title'])
                ->whereColumn('user_id', 'users.id')
                ->where('status', Post::STATUS_NORMAL)
                ->orderByDesc('created_at')
                ->limit(1)
        ])->orderByDesc('id')->paginate(20, $columns);
        return view('user.index', ['users' => $users]);
    }
}

addSelect 方法可用于添加一个查询字段到已存在的查询实例，我们通过传递一个数组 —— 数组键是返回的查询字段名，即 SQL 语句中的 last_post_title，数组值是对应的子查询逻辑，注意外键关联需要通过 whereColumn 方法设置，其他和正常 Eloquent 查询一样。

将视图模板中渲染文章标题的代码调整如下：

<td class="border px-4 py-2">{{ $user->last_post_title }}</td>

刷新用户列表，也可以正常渲染列表数据了：

可以看到现在的用户信息和文章标题查询已经合并为一条 SQL 查询语句获取，而且由于不再加载文章模型数据，数据库性能有了显著提升，应用整体的响应时间也大幅提升（不需要额外的集合处理逻辑），最后，最重要的是，不再需要加载多余的文章模型数据了：

我们的优化目标至此就通过数据库子查询完全实现了。

通过子查询创建动态关联

当然，上述实现带来的一个问题是只能获取文章标题，不能获取其他信息，要获取文章其他信息，则需要再经历一次查询，这仿佛又回到了懒惰式加载的老路，针对这个问题，有没有什么好的解决方案呢？

如果你想要获取的是整个文章实例，而不仅仅是一个标题字段，可以结合子查询和查询作用域创建动态关联关系来实现。

在 User 模型类中定义一个 lastPost 方法使其与该用户最新发布的文章记录建立一对一关联关系：

public function lastPost()
{
    return $this->belongsTo(Post::class);
}

根据 Eloquent 默认使用的外键关联字段名称规则，上面的关联关系对应的外键字段应该是 last_post_id（即 users 表中的 last_post_id 映射到 posts 表的 id 字段建立关联），如果是常规的关联关系，需要在 users 表中包含 last_post_id，不过我们要建立的是运行时的动态关联关系，这个「动态」不依赖于具体的数据表字段，那要如何在运行时的 users 表中提供这个字段呢，结合上面的子查询实现，我们很容易想到，可以通过子查询动态提供这个字段。

我们继续在 User 模型类中新增一个本地查询作用域 scopeWithLastPost 方法来定义对应的子查询实现逻辑：

public function scopeWithLastPost($query)
{
    return $query->addSelect([
        'last_post_id' => Post::select(['id'])
            ->whereColumn('user_id', 'users.id')
            ->where('status', Post::STATUS_NORMAL)
            ->orderByDesc('created_at')
            ->limit(1)
    ])->with('lastPost:id,title,user_id,created_at');
}

这里我们将 posts 表子查询字段改为 id，将查询得到的字段别名设置为 users 表的 last_post_id，就可以在运行时动态创建 users 表的 last_post_id 字段了，我们接着在查询构建器上通过 with('lastPost') 对 lastPost 关联进行渴求式加载（这里还指定了关联查询的查询字段），由于 users 表此时包含了 last_post_id 字段，也就可以顺理成章的进行对应的一对一关联查询了，这样一来，就可以在使用该查询作用域的 User 模型实例上获取到与之关联的 最新发布的一个 Post 模型实例了。

修改 UserController 关联查询代码如下：

class UserController extends Controller
{
    public function index()
    {
        $columns = ['id', 'name', 'email'];
        $users = User::select($columns)->withLastPost()->orderByDesc('id')->paginate(20);
        return view('user.index', ['users' => $users]);
    }
}

这一次，我们通过查询作用域获取动态的 lastPost 关联关系，打印获取到的 $users 变量，可以看到数据结构中包含了关联的 lastPost 关联对象，即 Post 模型实例：

在视图模板中修改获取最新文章标题的代码：

<td class="border px-4 py-2">{{ $user->lastPost->title }}</td>

这一次，我们可以通过 $user->lastPost 对象获取文章模型上指定的所有查询字段，不再是只能获取文章标题了。

刷新用户列表页，得到的列表数据和之前一样：

由于新引入了一个关联查询，查询性能略有损耗，不过为业务功能开发带来的灵活性和扩展性则完全可以覆盖掉这点瑕疵，另外，也解决了引入大量无用 Post 模型实例的问题：

所以，这个解决方案是兼顾查询性能、内存使用和业务开发灵活性的最佳方案。