[扩展包] 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 前端工作流等。
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 23

建议给laravel 提交PR

1个月前 评论
Makia98 1个月前

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

1个月前 评论
yangtaihua (作者) 1个月前

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

1个月前 评论

感觉还不错, 可以尝试下

1个月前 评论
黑将军

大佬,牛X

1个月前 评论

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

1个月前 评论
zero风来 3周前

解决了之前查询慢的问题

1个月前 评论
xiaopi

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

file

2周前 评论

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

2周前 评论
Jiangqh (作者) (楼主) 2周前
xiaopi 2周前

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

2周前 评论

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

2周前 评论
Bowens

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

2周前 评论

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

file

file

使用whereHasIn耗时 0.335 秒

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

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

User::query()->whereHas('profile')->count();
2周前 评论
TonyF

学习一波.

2周前 评论

这种关联写法的时候失效了? 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周前 评论

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!