What is the N + 1 problem and how to solve it

Time:2021-3-31

N+1It is a problem in reading the related data of ORM (object relation mapping).

What do you meanN+1First, think about a question:

Suppose there is a user table and a balance table, which are passed through theuser_idMake a connection. Now there is a needQuery users older than 18 and their respective balances

This problem is not difficult, but for novices, one of the common mistakes is to query in a loop.

$users = User::where("age", ">", 18)->select();
foreach($users as $user){
  $balance = User::getFieldByUserId($user->user_id, "balance");
  $user['balance'] = $balance;
}

This is very bad, the amount of data is small, in the case of large amount of data, it is very consumption of database performance.

Through the MySQL query log, you can see that the user table is queried once, because there are four users who meet the condition, and the balance table associated with the user table is queried four times.

What is the N + 1 problem and how to solve it

N+1This is how the problem arises: the main table is queried once, and n records are found. According to the n records, it takes n times to query the associated secondary (slave) table. So, it should be called1+NThe question is more appropriate.

In fact, if you know a little bit about SQL, you don’t need to use it directlyJOINIt’s done in one go.

For this kind of problem, ORM actually provides us with the corresponding solution, that is to use the “preload function”.

Preload function

usewith()Method to specify the association you want to preload:

$users = User::where("age", ">", 18)
        ->with("hasBalance")
        ->select();

hasBalanceWhat is it?

It’s in theUserA method defined in the model:

class User extends Model
{
    //  ...
    
    //One to one association between user model and balance model
    public function hasBalance()
    {
          return $this->hasOne(Balance::class, "user_id", "user_id");
    }
}

In this way, letUserModel andBalanceThe model is one-to-one associated.

Now let’s look at the query log of MySQL

What is the N + 1 problem and how to solve it

It can be clearly seen that the total number of queries has changed from the original one1+NIt’s what it is now1+1

summary

N+1What’s the problem? What is the impact? How to solve it?

  1. After a query is executed to obtain n pieces of master data, the execution of N times of query slave data caused by association
  2. It brings unnecessary query overhead
  3. You can use thewithTo solve it