Laravel's N+1 problem solution

Object-relational mapping (ORM) makes working with data surprisingly easy. Since defining relationships between data in an object-oriented manner makes it easy to query related model data, developers do not need to pay attention to the underlying calls of the data.

The standard data optimization of ORM is to eagerly load related data. We'll set up some example relationships and then step through how queries change with eager and non-eager loading. I like to experiment with things directly using code and walk through some examples to illustrate how eager loading works, which will further help you understand how to optimize your queries.

Introduction

At a basic level, the ORM is "lazy" in loading relevant model data. But how is the ORM supposed to know your intent? After querying a model, you may never actually use the data from the related model. Not optimizing a query is known as the "N 1" problem. When you use objects to represent queries, you may be querying without knowing it.

Imagine that you receive 100 objects from the database, and each record has 1 associated model (i.e. belongsTo). Using the ORM will result in 101 queries by default; one query for the original 100 records, and additional queries for each record if relevant data on the model object is accessed. In pseudocode, let's say you want to list the publishing authors of all published posts. From a set of posts (each post has an author), you can get a list of author names like this:

$posts = Post::published()->get(); // 一次查询
$authors = array_map(function($post) {
    // 生成对作者模型的查询
    return $post->author->name;
}, $posts);

We are not telling the model that we need all authors, so each time from the individual Post model instance A separate query occurs when retrieving the author's name.

Preloading

As I mentioned, the ORM is "lazy" about loading associations. If you plan to use associated model data, you can use eager loading to reduce 101 queries to 2 queries. You just need to tell the model what you want it to load.

The following is an example from the Rails Active Record guide using preloading. As you can see, this concept is very similar to Laravel's eager loading concept.

# Rails
posts = Post.includes(:author).limit(100)
# Laravel
$posts = Post::with('author')->limit(100)->get();

By exploring from a broader perspective, I find that I gain a better understanding. The Active Record documentation covers some examples that can further help the idea resonate.

Laravel’s Eloquent ORM

Laravel’s ORM, called Eloquent, can easily preload models, even preload nested relational models. Let’s take the Post model as an example to learn how to use eager loading in Laravel projects.

We'll use this project build and then dive deeper into some preloading examples to wrap up.

Build

Let's build some database migrations, models, and database seeds to experience preloading. If you want to follow along, I'm assuming you have access to the database and have completed a basic Laravel installation.

Using the Laravel installer, create a new project:

laravel new blog-example

Edit the .env file according to your database and selections.

Next, we'll create three models so you can try preloading nested relationships. This example is simple so we can focus on eager loading, and I've omitted things you might use like indexes and foreign key constraints.

php artisan make:model -m Post
php artisan make:model -m Author
php artisan make:model -m Profile

The -m flag creates a migration for use with the model that will be used to create the table schema.

The data model will have the following associations:

Post -> belongsTo -> Author

Author -> hasMany -> Post

Author -> hasOne -> Profile

Migration

Let us create a profile structure for each data table. I only added the up() method because Laravel will automatically add the down() method for new tables. These migration files are placed in the database/migrations/ directory:

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreatePostsTable extends Migration
{
    /**
     * 执行迁移
     *
     * @return void
     */
    public function up()
    {
        Schema::create(&#39;posts&#39;, function (Blueprint $table) {
            $table->increments(&#39;id&#39;);
            $table->unsignedInteger(&#39;author_id&#39;);
            $table->string(&#39;title&#39;);
            $table->text(&#39;body&#39;);
            $table->timestamps();
        });
    }
    /**
     * 回滚迁移
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists(&#39;posts&#39;);
    }
}

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateAuthorsTable extends Migration
{
    /**
     * 执行迁移
     *
     * @return void
     */
    public function up()
    {
        Schema::create(&#39;authors&#39;, function (Blueprint $table) {
            $table->increments(&#39;id&#39;);
            $table->string(&#39;name&#39;);
            $table->text(&#39;bio&#39;);
            $table->timestamps();
        });
    }
    /**
     * 回滚迁移
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists(&#39;authors&#39;);
    }
}

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateProfilesTable extends Migration
{
    /**
     * 执行迁移
     *
     * @return void
     */
    public function up()
    {
        Schema::create(&#39;profiles&#39;, function (Blueprint $table) {
            $table->increments(&#39;id&#39;);
            $table->unsignedInteger(&#39;author_id&#39;);
            $table->date(&#39;birthday&#39;);
            $table->string(&#39;city&#39;);
            $table->string(&#39;state&#39;);
            $table->string(&#39;website&#39;);
            $table->timestamps();
        });
    }
    /**
     * 回滚迁移
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists(&#39;profiles&#39;);
    }
}

Model

You need to define model associations and perform more experiments with eager loading. When you run the php artisan make:model command, it will create the model file for you.

The first model is app/Post.php:

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
    public function author()
    {
        return $this->belongsTo(Author::class);
    }
}

Next, the app\Author.php model has two relationships:

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Author extends Model
{
    public function profile()
    {
        return $this->hasOne(Profile::class);
    }
    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

Through model and migration , you can run the migration and continue trying to preload with some seed model data.

php artisan migrate
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table
Migrating: 2017_08_04_042509_create_posts_table
Migrated:  2017_08_04_042509_create_posts_table
Migrating: 2017_08_04_042516_create_authors_table
Migrated:  2017_08_04_042516_create_authors_table
Migrating: 2017_08_04_044554_create_profiles_table
Migrated:  2017_08_04_044554_create_profiles_table

If you look at the database, you will see all the data tables that have been created!

Factory Model

In order for us to run the query statement, we need to create some fake data to provide the query. Let's add some factory models and use these models to The database provides test data.

Open the database/factories/ModelFactory.php file and add the following three factory models to the existing User factory model file:

/** @var \Illuminate\Database\Eloquent\Factory $factory */
$factory->define(App\Post::class, function (Faker\Generator $faker) {
    return [
        'title' => $faker->sentence,
        'author_id' => function () {
            return factory(App\Author::class)->create()->id;
        },
        'body' => $faker->paragraphs(rand(3,10), true),
    ];
});
/** @var \Illuminate\Database\Eloquent\Factory $factory */
$factory->define(App\Author::class, function (Faker\Generator $faker) {
    return [
        'name' => $faker->name,
        'bio' => $faker->paragraph,
    ];
});
$factory->define(App\Profile::class, function (Faker\Generator $faker) {
    return [
        'birthday' => $faker->dateTimeBetween('-100 years', '-18 years'),
        'author_id' => function () {
            return factory(App\Author::class)->create()->id;
        },
        'city' => $faker->city,
        'state' => $faker->state,
        'website' => $faker->domainName,
    ];
});

These factory models can easily fill in some of our Data that can be queried; we can also use them to create and generate the data required for relational models.

Open the database/seeds/DatabaseSeeder.php file and add the following content to the DatabaseSeeder::run() method:

public function run()
{
    $authors = factory(App\Author::class, 5)->create();
    $authors->each(function ($author) {
        $author
            ->profile()
            ->save(factory(App\Profile::class)->make());
        $author
            ->posts()
            ->saveMany(
                factory(App\Post::class, rand(20,30))->make()
            );
    });
}

你创建了五个 author 并遍历循环每一个 author ，创建和保存了每个 author 相关联的 profile 和 posts （每个 author 的 posts 的数量在 20 和 30 个之间）。

我们已经完成了迁移、模型、工厂模型和数据库填充的创建工作，将它们组合起来可以以重复的方式重新运行迁移和数据库填充：

php artisan migrate:refresh
php artisan db:seed

你现在应该有一些已经填充的数据，可以在下一章节使用它们。注意在 Laravel 5.5 版本中包含一个 migrate:fresh 命令，它会删除表，而不是回滚迁移并重新应用它们。

尝试使用预加载

现在我们的前期工作终于已经完成了。 我个人认为最好的可视化方式就是将查询结果记录到 storage/logs/laravel.log 文件当中查看。

要把查询结果记录到日志中，有两种方式。第一种，可以开启 MySQL 的日志文件，第二种，则是使用 Eloquent 的数据库调用来实现。通过 Eloquent 来实现记录查询语句的话，可以将下面的代码添加到 app/Providers/AppServiceProvider.php boot () 方法当中：

namespace App\Providers;
use DB;
use Log;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        DB::listen(function($query) {
            Log::info(
                $query->sql,
                $query->bindings,
                $query->time
            );
        });
    }
    // ...
}

我喜欢把这个监听器封装在配置检查的时候，以便可以控制记录查询日志的开关。你也可以从 Laravel Debugbar 获取到更多相关的信息。

首先，尝试一下在不使用预加载模型的时候，会发生什么情况。清除你的 storage/log/laravel.log 文件当中的内容然后运行 "tinker" 命令：

php artisan tinker
>>> $posts = App\Post::all();
>>> $posts->map(function ($post) {
...     return $post->author;
... });
>>> ...

这个时候检查你的 laravel.log 文件，你会发现一堆查询作者的查询语句：

[2017-08-04 06:21:58] local.INFO: select * from `posts`
[2017-08-04 06:22:06] local.INFO: select * from `authors` where `authors`.`id` = ? limit 1 [1]
[2017-08-04 06:22:06] local.INFO: select * from `authors` where `authors`.`id` = ? limit 1 [1]
[2017-08-04 06:22:06] local.INFO: select * from `authors` where `authors`.`id` = ? limit 1 [1]
....

然后，再次清空 laravel.log 文件，, 这次使用 with() 方法来用预加载查询作者信息：

php artisan tinker
>>> $posts = App\Post::with('author')->get();
>>> $posts->map(function ($post) {
...     return $post->author;
... });
...

这次你应该看到了，只有两条查询语句。一条是对所有帖子进行查询，以及对帖子所关联的作者进行查询：

[2017-08-04 07:18:02] local.INFO: select * from `posts`
[2017-08-04 07:18:02] local.INFO: select * from `authors` where `authors`.`id` in (?, ?, ?, ?, ?) [1,2,3,4,5]

如果你有多个关联的模型，你可以使用一个数组进行预加载的实现：

$posts = App\Post::with(['author', 'comments'])->get();

在 Eloquent 中嵌套预加载

嵌套预加载来做相同的工作。在我们的例子中，每个作者的 model 都有一个关联的个人简介。因此，我们将针对每个个人简介来进行查询。

清空 laravel.log 文件，来做一次尝试：

php artisan tinker
>>> $posts = App\Post::with('author')->get();
>>> $posts->map(function ($post) {
...     return $post->author->profile;
... });
...

你现在可以看到七个查询语句，前两个是预加载的结果。然后，我们每次获取一个新的个人简介时，就需要来查询所有作者的个人简介。

通过预加载，我们可以避免嵌套在模型关联中的额外的查询。最后一次清空 laravel.log 文件并运行一下命令：

>>> $posts = App\Post::with('author.profile')->get();
>>> $posts->map(function ($post) {
...     return $post->author->profile;
... });

现在，总共有三个查询语句：

[2017-08-04 07:27:27] local.INFO: select * from `posts`
[2017-08-04 07:27:27] local.INFO: select * from `authors` where `authors`.`id` in (?, ?, ?, ?, ?) [1,2,3,4,5]
[2017-08-04 07:27:27] local.INFO: select * from `profiles` where `profiles`.`author_id` in (?, ?, ?, ?, ?) [1,2,3,4,5]

懒人预加载

你可能只需要收集关联模型的一些基础的条件。在这种情况下，可以懒惰地调用关联数据的一些其他查询：

php artisan tinker
>>> $posts = App\Post::all();
...
>>> $posts->load('author.profile');
>>> $posts->first()->author->profile;
...

你应该只能看到三条查询，并且是在调用 $posts->load() 方法后。

总结

希望你能了解到更多关于预加载模型的相关知识，并且了解它是如何在更加深入底层的工作方式。 预加载文档 是非常全面的，我希望额外的一些代码实现可以帮助您更好的优化关联查询。

推荐教程：《Laravel教程》《PHP教程》

The above is the detailed content of Laravel's N+1 problem solution. For more information, please follow other related articles on the PHP Chinese website!