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

LARAVEL HASIN

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

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

简介

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

select * from A where exists (select * from B where A.id=B.a_id)

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

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

select * from A where A.id in (select B.a_id from B)

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

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

<?php
/**
 * SQL:
 * 
 * select * from `product` 
 * where exists 
 *   ( 
 *      select * from `product_skus` 
 *      where `product`.`id` = `product_skus`.`p_id` 
 *      and `product_skus`.`deleted_at` is null 
 *   ) 
 * and `product`.`deleted_at` is null 
 * limit 10 offset 0
 */
$products = Product::has('skus')->paginate(10);

/**
 * SQL:
 * 
 * select * from `product` 
 * where `product`.`id` IN  
 *   ( 
 *      select `product_skus`.`p_id` from `product_skus` 
 *      and `product_skus`.`deleted_at` is null 
 *   ) 
 * and `product`.`deleted_at` is null 
 * limit 10 offset 0
 */
$products = Product::hasIn('skus')->paginate(10);

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

环境

  • PHP >= 7.1
  • laravel >= 5.8

安装

composer require biiiiiigmonster/hasin

使用

在配置文件app.php添加配置,自动注册服务

<?php
    // ...

    'providers' => [
        // ...

        BiiiiiigMonster\Hasin\HasinServiceProvider::class,// hasin扩展包引入
    ],

此扩展hasIn(hasMorphIn)支持Laravel ORM中的所有关联关系,入参及调用方式与has(hasMorph)完全一致,可安全使用或替换

hasIn

// hasIn
Product::hasIn('skus')->get();

// orHasIn
Product::where('name', 'like', '%拌饭酱%')->orHasIn('skus')->get();

// doesntHaveIn
Product::doesntHaveIn('skus')->get();

// orDoesntHaveIn
Product::where('name', 'like', '%拌饭酱%')->orDoesntHaveIn('skus')->get();

whereHasIn

// whereHasIn
Product::whereHasIn('skus', function ($query) {
    $query->where('sales', '>', 10);
})->get();

// orWhereHasIn
Product::where('name', 'like', '%拌饭酱%')->orWhereHasIn('skus', function ($query) {
    $query->where('sales', '>', 10);
})->get();

// whereDoesntHaveIn
Product::whereDoesntHaveIn('skus', function ($query) {
    $query->where('sales', '>', 10);
})->get();

// orWhereDoesntHaveIn
Product::where('name', 'like', '%拌饭酱%')->orWhereDoesntHaveIn('skus', function ($query) {
    $query->where('sales', '>', 10);
})->get();

hasMorphIn

Image::hasMorphIn('imageable', [Product::class, Brand::class])->get();

嵌套关联

Product::hasIn('attrs.values')->get();

自关联

Category::hasIn('children')->get();

鸣谢

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

本作品采用《CC 协议》,转载必须注明作者和本文链接
本帖由 Epona 于 3个月前 加精
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
讨论数量: 5

感谢 mark一下

4个月前 评论
biiiiiigmonster (楼主) 4个月前

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

3个月前 评论
biiiiiigmonster (楼主) 3个月前

感谢 mark 一下

3个月前 评论
biiiiiigmonster (楼主) 3个月前
baidudaxing

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

3个月前 评论
Epona

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

可以加个 package discovery 的feature

3个月前 评论
biiiiiigmonster (楼主) 3个月前

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