[扩展包] 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 前端工作流等。
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
讨论数量: 57

建议给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年前 评论

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是什么问题呀

1年前 评论
Jiangqh (楼主) 1年前
984054610 (作者) 1年前
zhangkch 1年前
984054610 (作者) 1年前
woniu123 1年前

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

file

file

1年前 评论
还不出来 1年前
huanjun (作者) 1年前

whereHasIn 里不能使用groupBy havingRaw

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

拓展包安装没有用

10个月前 评论

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

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

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

file

file

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

我在 mysql 8.0.26 做测试时,发现无论是 Exists 还是 IN 的子查询最终都会被优化成 JOIN,具体测试方式如下:

EXPLAIN select users.name from users where exists(select * from user_profiles where users.id = user_profiles.id);
SHOW WARNINGS;

EXPLAIN select users.name from users where users.id in(select user_profiles.id from user_profiles where users.id = user_profiles.id);
SHOW WARNINGS;

以上两种方式最终,都会被转换成以下语句:

select `users`.`name` from `user_profiles` join `users` where (`user_profiles`.`id` =  `users`.`id`);

另外,我通过使用 EXPLAINExistsIN 进行分析,在 mysql 8.0.26 中它们都使用了 主键 索引

上述测试针对的是 一对一 关系,其它关系没有测试

4个月前 评论

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