讨论数量:
如果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
, 噢啦
推荐文章: