千万级数据关联查询会卡死,求调优方法
业务代码
$list = AdminUserList::with([
'adminGroupList'=>function ( $query) use ($groupLevel) {
$query->where('group_level', '<', $groupLevel);
}
]);
if(!empty($params['login_time'])){
$list = $list->with([
'baseLog'=>function($query)use($params){
$query->where([
['log_type','=',1],
['log_create_time','>',intval($params['login_time'])],
['log_create_time','<',strtotime('+1 day',intval($params['login_time']))]
]);
}
])->whereHas('baseLog',function (Builder $query)use($params){
$query->where([
['log_type','=',1],
['log_create_time','>',intval($params['login_time'])],
['log_create_time','<',strtotime('+1 day',intval($params['login_time']))]
]);
}
);
}
if(!empty($params['name']))$list=$list->where('nickname','like',"%{$params['name']}%");
if(!empty($params['username']))$list=$list->where('username','like',"%{$params['username']}%");
if (!empty($params['group_id']))$list=$list->where('group_id',$params['group_id']);
$list = $list->where('id','<>',$uid)->select($select)->orderBy('id', 'desc')->paginate(perPage: $params['limit'], page: $params['page']);
$count = $list->total();
$list = $list->toArray()['data'];
求调优方法,还是我这种写法有问题?用户数据大概200万,日志数据有好几千万,
推荐文章: