[扩展包] Laravel-wherehasin 提升 ORM 关联关系查询性能 (优化 whereHas 性能)

前言

前几天在QQ群里看到有位同学请人帮忙根据社区里的文章(给 Eloquent 的 whereHas 加个 where in 的优化)给EloquentwhereHas方法进行性能优化,因为这篇文章里面提供的代码还是有些细节问题,并不能支持所有的关联关系,所以我抽空写了这个扩展包,支持了所有关联关系。并且我也做了一些小测试,发现在数据量大的情况下优化过后的性能提升非常惊人,下面是一个简单的测试,如果有不正确之处欢迎大家拍砖指正:

主表test_users写入130002条数据,关联表test_user_profiles写入1002条数据,查询代码如下

<?php
/**
 * 未优化sql
 * 
 * select * from `test_users` where exists
 *   (
 *     select * from `test_user_profiles` 
 *     where `test_users`.`id` = `test_user_profiles`.`user_id`
 *  ) 
 * limit 10
 */
$users1 = User::whereHas('profile')->limit(10)->get();

/**
 * 优化后的sql
 * 
 * select * from `test_users` where `test_users`.`id` in 
 *   (
 *     select `test_user_profiles`.`user_id` from `test_user_profiles` 
 *     where `test_users`.`id` = `test_user_profiles`.`user_id`
 *   ) 
 * limit 10
 */
$users1 = User::whereHasIn('profile')->limit(10)->get();

最终耗时如下,可以看出性能相差还是不小的,如果数据量更多一些,这个差距还会更大

whereHas (优化前)   0.50499701499939whereHasIn (优化后) 0.027166843414307

多测试几次之后不难发现:

当主表数据量较多的情况下,where id in会有明显的性能提升;当主表数据量较少的时候,两者性能相差无几。

简介

Laravel wherehasin是一个可以提升Laravel ORM关联关系查询性能的扩展包,可以替代Laravel ORM中的whereHas以及whereHasMorphIn查询方法。

Github (如果喜欢这个项目不妨点个star,谢谢支持~)

环境

  • PHP >= 7
  • laravel >= 5.5

安装

composer require dcat/laravel-wherehasin

使用

whereHasIn

此方法已支持Laravel ORM中的所有关联关系,可以替代whereHas

User::whereHasIn('profile')->get();

User::whereHasIn('profile', function ($q) {
    $q->where('id', '>', 10);
})->get();

orWhereHasIn

User::where('name', 'like', '%laravel%')->orWhereHasIn('profile')->get();

多级关联关系

User::whereHasIn('painters.paintings', function ($q) {
    $q->whereIn('id', [600, 601]);
})->orderBy('id')->get()->toArray();

需要注意的是,如果是BelongsTo类型的关联关系,使用whereHasIn时使用的不是主键,而是外键

<?php

/**
 * 这里用的是"user_id in",而不是"id in"
 * 
 * select * from `test_user_profiles` where `test_user_profiles`.`user_id` in 
 *   (
 *     select `test_users`.`id` from `test_users` where `test_user_profiles`.`user_id` = `test_users`.`id`
 *   )
 */
$profiles = Profile::whereHasIn('user')->get();

whereHasMorphIn

此方法已支持Laravel ORM中的所有关联关系,可以替代whereHasMorph

Image::whereHasMorphIn('imageable', Post::class, function ($q) {
    $q->where('id', '>', 10);
})->get();

鸣谢

给 Eloquent 的 whereHas 加个 where in 的优化

评论区下面有同学留言说当副表数据过大时mysql可能会报错,所以我增加了一个小测试,主表写入50W数据,副表写入100W数据,结论是副表数据过大时也影响不大,

file

file

使用whereHasIn耗时 0.335 秒

User::query()->whereHasIn('profile')->count();

如果是whereHas则页面直接卡死了,等了十几分钟都出不来结果

User::query()->whereHas('profile')->count();
本作品采用《CC 协议》,转载必须注明作者和本文链接
Jiangqh
本帖由系统于 1年前 自动加精
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
讨论数量: 31

建议给laravel 提交PR

1年前 评论
Makia98 1年前

个人觉得没必要,人家用wherehas 是要用到关系里面的字段的,你这是只需要关联查询匹配的完全不需要使用关系查询。

1年前 评论
yangtaihua (作者) 1年前

@yangtaihua 你没仔细看吧,支持关联关系字段查询,第二个参数传闭包不就好了

1年前 评论

感觉还不错, 可以尝试下

1年前 评论
黑将军

大佬,牛X

1年前 评论

我记得in查询的参数是不是有限制啊

1年前 评论
zero风来 1年前

解决了之前查询慢的问题

1年前 评论
xiaopi

请问如何去除phpstrom的错误提示?这个影响到所有子类继承都不能很好的使用Illuminate\Database\Eloquent\Model中的方法,已经尝试把整个包Excluded,但是还是会加载包中的.ide-helper.php

file

1年前 评论

@xiaopi 严格来说这个并不算错误提示,本意是让IDE有个自动补全功能,我发布个新版本移除helper文件这个model相关内容吧

1年前 评论
Jiangqh (作者) (楼主) 1年前
xiaopi 1年前

自定义一个表单,使用form组件,自定义了action,比如提交到 postRecharge 这个方法 ,怎么在 这个方法中验证数据并返回验证提示

1年前 评论

@zerodegree 评论错地方了吧,验证数据你可以用laravel内置validation功能,返回格式可以参考正常表单提交的接口返回

1年前 评论
Bowens

mysql 最大可执行 4M 的 SQL,因此关联表数据量大的时候 where in 估计 Mysql 会报错

1年前 评论

@Bowens 经过测试,影响不大,主表50W数据,附表100W

file

file

使用whereHasIn耗时 0.335 秒

User::query()->whereHasIn('profile')->count();

如果是whereHas则页面直接卡死了,等了十几分钟都出不来结果

User::query()->whereHas('profile')->count();
1年前 评论

这种关联写法的时候失效了? file

1年前 评论

@huanjun 感谢反馈确实有点问题,我晚上修复一下

1年前 评论

@huanjun 已修复,升级一下就可以了

1年前 评论

您好,我用了扩展包,使用了whereHasIn方法去查询,反而要比whereHas要慢,这是为什么呢,是我用的方法不对吗。 用whereHas去查询接口,接口响应时间1.5s左右,用whereHasIn响应时间变5s了。 主表500多条数据,副表4900多数据。

file

1年前 评论
Jiangqh (楼主) 1年前
Jiangqh (楼主) 1年前
AugensternDZ (作者) 1年前
AugensternDZ (作者) 1年前
Jiangqh (楼主) 1年前
AugensternDZ (作者) 1年前

这个是使用whereHas和whereHasIn的sql语句截图: whereHas: file whereHasIn: file

1年前 评论

file

大佬帮看看 关联嵌套问题. belongsToMany 可以指定多参数

Laravel

第三个参数 house_id 无效. 直接还是找的主键. 第三,四个参数 要如何加入到 whereHasIn

1年前 评论
Jiangqh (楼主) 1年前
Jiangqh (楼主) 1年前

这种关联好像也有问题,用的是id in ,而不是product_no in
file
Laravel

1年前 评论
Jiangqh (楼主) 1年前
984054610

使用这个包出现报错

"message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'has_in' in 'where clause' (SQL: select `id`, `course_id`, `group_price`, `group_num`, `group_time`, `updated_at`, `status`, `updated_admin_id` from `preschool_groups` where `has_in` = course and exists (select * from `preschool_course` where `preschool_groups`.`course_id` = `preschool_course`.`id` and exists (select * from `preschool_course_level` where `preschool_course`.`id` = `preschool_course_level`.`course_id` and `level` in (1, 2))) and `preschool_groups`.`deleted_at` is null order by `status` desc, `updated_at` desc)"

has_in是什么问题呀

11个月前 评论
Jiangqh (楼主) 11个月前
984054610 (作者) 11个月前
zhangkch 9个月前
984054610 (作者) 9个月前
woniu123 8个月前

hasOneThrough 这个关联方法使用whereHasIn的速度和whereHas的区别不大,效果失效了吗?

file

file

9个月前 评论
biiiiiigmonster 9个月前
huanjun (作者) 9个月前

whereHasIn 里不能使用groupBy havingRaw

 $query->whereHasIn('storeProduct', function ($q) {
         $q->groupBy('goods_id')->havingRaw('sum(total) = 0');
 });
8个月前 评论
Jiangqh (楼主) 8个月前

拓展包安装没有用

3个月前 评论

请问php5.6想用wherehasin怎么引入呀

3个月前 评论
Jiangqh (楼主) 3个月前
qinshimingyue (作者) 3个月前

composer 安装完毕以后 查不到任何数据 没安装之前 whereHas 可以查到 现在whereHas 也查不到了

file

file

2个月前 评论
Jiangqh (楼主) 2个月前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!