Laravel 模型：什么是 N+1 问题？请使用代码演示如何避免此问题？

如果user表关联了profile表，n+1大概就是在循环里面查关联这样吧

foreach($users as $user){
    $user->xxx = $user->profile->xxx;
}

避免n+1用$users = User::query()->with('profile')->get()，先加载一下关联模型，with只会执行两次查询，一个select * from user，一个select * from profile id in ($user->pluck('id'))

这个问题应该是 预加载的问题, 在模型 Model中建立一对多或多对多的关系, 使用预加载 with 可以起到优化Sql的作用.

如何优化的呢, 看下小demo.

DB::connection()->enableQueryLog();

// 获取10个用户信息
// 不使用with
$users = User::query()->take(10)->get();

// 使用with
// $users = User::query()->with('certificate')->take(10)->get();

foreach ($users as $user) {

    // 获取实名认证信息
    $user->certificate;
    // todo
}

$logs = DB::connection()->getQueryLog();

foreach ($logs as $log) {
    Log::info('sql-log', $log);
}

打印出来的日志, 分别是

[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `users` limit 10","bindings":[],"time":2.05} 
[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` = ? and `user_certificates`.`user_id` is not null limit 1","bindings":[1000000],"time":0.64} 
[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` = ? and `user_certificates`.`user_id` is not null limit 1","bindings":[1000001],"time":0.48} 
[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` = ? and `user_certificates`.`user_id` is not null limit 1","bindings":[1000002],"time":0.41} 
[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` = ? and `user_certificates`.`user_id` is not null limit 1","bindings":[1000003],"time":0.5} 
[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` = ? and `user_certificates`.`user_id` is not null limit 1","bindings":[1000004],"time":0.35} 
[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` = ? and `user_certificates`.`user_id` is not null limit 1","bindings":[1000005],"time":0.39} 
[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` = ? and `user_certificates`.`user_id` is not null limit 1","bindings":[1000006],"time":0.36} 
[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` = ? and `user_certificates`.`user_id` is not null limit 1","bindings":[1000007],"time":0.33} 
[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` = ? and `user_certificates`.`user_id` is not null limit 1","bindings":[1000008],"time":0.3} 
[2021-11-26 09:31:41] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` = ? and `user_certificates`.`user_id` is not null limit 1","bindings":[1000009],"time":0.34} 

上面结果是1+10

[2021-11-26 09:34:56] local.INFO: sql-log {"query":"select * from `users` limit 10","bindings":[],"time":1.67} 
[2021-11-26 09:34:56] local.INFO: sql-log {"query":"select * from `user_certificates` where `user_certificates`.`user_id` in (1000000, 1000001, 1000002, 1000003, 1000004, 1000005, 1000006, 1000007, 1000008, 1000009)","bindings":[],"time":0.56}

哦豁, 这个结果是1+1, 噢啦

以常用的列表分页查询为例：假设查询A表返回了N条数据，同时需要关联另一个表B的数据。原始的方法是循环遍历A表的N条数据，查询关联的B表数据，产生了N+1条语句。

避免这个问题可以直接使用laravel的with方法，本质上是使用一条whereIn代替N次查询。