whereHas性能调优——采用 where in 语法实现优化 查询关联

LARAVEL HASIN#

hasin 是一个基于 where in 语法实现的 Laravel ORM 关联关系查询的扩展包,部分业务场景下可以替代 Laravel ORM 中基于 where exists 语法实现的 has,以获取更高的性能。

Github (喜欢这个项目点个 star 吧,灰常感谢~)

简介#

Laravel ORM 的关联关系非常强大,基于关联关系的查询 has 也给我们提供了诸多灵活的调用方式,然而某些情形下,has 使用了 where exists 语法实现

例如:

// User hasMany Post
User::has('posts')->get();

select * from users where exists (select * from posts where users.id=posts.user_id)#

exists 是对外表做 loop 循环,每次 loop 循环再对内表(子查询)进行查询,那么因为对内表的查询使用的索引(内表效率高,故可用大表),而外表有多大都需要遍历,不可避免(尽量用小表),故内表大的使用 exists,可加快效率。

但是当 A 表数据量较大的时候,就会出现性能问题,那么这时候用 where in 语法将会极大的提高性能

select * from users where users.id in (select posts.user_id from posts)#

in 是把外表和内表做 hash 连接,先查询内表,再把内表结果与外表匹配,对外表使用索引(外表效率高,可用大表),而内表多大都需要查询,不可避免,故外表大的使用 in,可加快效率。

因此在代码中使用 has(hasMorph) 或者 hasIn(hasMorphIn) 应由数据体量来决定……

<?php
/**
 * SQL:
 * 
 * select * from `users` 
 * where exists 
 *   ( 
 *      select * from `posts` 
 *      where `users`.`id` = `posts`.`user_id` 
 *   ) 
 * limit 10 offset 0
 */
$users = User::has('posts')->paginate(10);

/**
 * SQL:
 * 
 * select * from `users` 
 * where `users`.`id` in  
 *   ( 
 *      select `posts`.`user_id` from `posts` 
 *   ) 
 * limit 10 offset 0
 */
$users = User::hasIn('posts')->paginate(10);

Laravel ORM 十种关联关系案例 sql 输出可查看有道云笔记

环境#

  • PHP >= 7.1
  • laravel >= 5.8

安装#

composer require biiiiiigmonster/hasin

使用#

此扩展方法 hasIn(hasMorphIn) 支持 Laravel ORM 中的所有关联关系,入参调用及内部实现流程与框架的 has(hasMorph) 完全一致,可安全使用或替换

hasIn

// hasIn
User::hasIn('posts')->get();

// orHasIn
User::where('age', '>', 18)->orHasIn('posts')->get();

// doesntHaveIn
User::doesntHaveIn('posts')->get();

// orDoesntHaveIn
User::where('age', '>', 18)->orDoesntHaveIn('posts')->get();

whereHasIn

// whereHasIn
User::whereHasIn('posts', function ($query) {
    $query->where('votes', '>', 10);
})->get();

// orWhereHasIn
User::where('age', '>', 18)->orWhereHasIn('posts', function ($query) {
    $query->where('votes', '>', 10);
})->get();

// whereDoesntHaveIn
User::whereDoesntHaveIn('posts', function ($query) {
    $query->where('votes', '>', 10);
})->get();

// orWhereDoesntHaveIn
User::where('age', '>', 18)->orWhereDoesntHaveIn('posts', function ($query) {
    $query->where('votes', '>', 10);
})->get();

hasMorphIn

Image::hasMorphIn('imageable', [Post::class, Comment::class])->get();

嵌套关联#

User::hasIn('posts.comments')->get();

鸣谢#

给 Eloquent 的 whereHas 加个 where in 的优化
[扩展包] Laravel-wherehasin 提升 ORM 关联关系查询性能 (优化 whereHas 性能)
感谢这两篇博文网友的讨论以及提供的思路

本作品采用《CC 协议》,转载必须注明作者和本文链接
本帖由 Epona 于 4年前 加精
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
讨论数量: 5

感谢 mark 一下

4年前 评论
还不出来 (楼主) 4年前

感谢,Github 看到你的回复了,先点波赞在看,我当时用数据表增加字段 + whereHasIn 替代了 whereDoesntHave

4年前 评论
还不出来 (楼主) 4年前

感谢 mark 一下

4年前 评论
还不出来 (楼主) 4年前
baidudaxing

感谢。正好遇到这个问题,60 万条主表数据需要查是否被另一个表引用耗时太久。

4年前 评论
Epona

learnku.com/docs/laravel/8.x/packa...

可以加个 package discovery 的 feature

4年前 评论
还不出来 (楼主) 4年前