这个 SQL 问题绝对能让你对 MySQL 的理解更进一步!

属性表(product_props)结构如下

数据量800W以上

字段名 类型 说明
id int id
pn_id int 属性类型
pv_id int 属性值
product_id int 产品ID

其中product_id与pn_id,pv_id是一对多的关系。
数据类似这样:

product_id pn_id pv_id
10970 5 (型号) 135 (苹果9)
10970 11 (内存) 23 (512G)
10970 10 (颜色) 17 (土豪金)
10970 8 (网络) 6(5G)
10980 5 135
10980 11 24 (1024G)
10980 10 16 (极光蓝)

产品表(product)结构如下

数据量40W以上

字段名 类型 说明
product_id int product_id
type_id int 类型id
brand_id int 品牌id
model_id int 型号id
status tinyint 状态

数据类似以下:

product_id type_id brand_id model_id status
10970 1(手机) 1(苹果) 1(Iphone8) 1(正常)
10980 1(手机) 1(苹果) 1(Iphone8X) 3(已售)
10981 1(手机) 1(苹果) 1(Iphone8XP) 1(正常)

问题

找出型号为苹果9同时内存为512G,颜色为土豪金,状态为正常产品总数
ps : 属性条件可能会有超过10组。

要求

性能第一,杜绝聚合函数等

原问题的解决方案性能排行

  1. 来自 @Kamicloud的 exist方案
SELECT 
    sql_no_cache `product_id`
FROM
    `zx_tests` AS a
WHERE
    `pn_id` = 101 AND `pv_id` = 59
        AND EXISTS( SELECT 
           sql_no_cache  *
        FROM
            `zx_tests`
        WHERE
        a.product_id = product_id and
            `pn_id` = 101 AND `pv_id` = 171);

    2 组条件下 0.657,3 组 0.695,4 组 0.759,5 组 0.743 (单独查属性表)
  1. 来自 @Elijah_Wang的子查询方案
SELECT `product_id` FROM `product` WHERE `pn_id` = 5 AND `pv_id` = 135 AND `product_id` IN (SELECT `product_id` FROM `product` WHERE `pn_id` = 11 AND `pv_id` = 23);

    2 组条件下 0.729,3 组 0.75,4 组 0.730,5 组 0.757 (新问题之前)

新问题之后的性能排行

  1. 来自 @Elijah_Wang的子查询方案

    select SQL_NO_CACHE count(1) from pdi_product a join  (
        SELECT
             distinct product_id
        FROM
            `product_props` 
        WHERE
            `pn_id` = 5 
            AND `pv_id` = 127
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 11 AND `pv_id` = 22 ) 
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 10 AND `pv_id` = 18 ) 
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 8 AND `pv_id` = 6 )  
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 9 AND `pv_id` = 1 )
            ) b on a.product_id = b.product_id 
            where  a.status = 1;    

耗时1.5-1.56 (执行10次的范围)

  • expain分析:

SQL 性能实战来了,机不可失!

        select SQL_NO_CACHE count(1) from pdi_product a 
            where  a.status = 1 and a.product_id in (SELECT
             distinct product_id
        FROM
            `product_props` 
        WHERE
            `pn_id` = 5 
            AND `pv_id` = 127
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 11 AND `pv_id` = 22 ) 
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 10 AND `pv_id` = 18 ) 
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 8 AND `pv_id` = 6 )  
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 9 AND `pv_id` = 1 ))

耗时0.69-0.72(执行10次的范围)

  • explain分析:
    SQL 性能实战来了,机不可失!
  1. 来自 @Kamicloud的 exist方案
    SELECT SQL_NO_CACHE
            count(1) 
        FROM
            product a
        WHERE
            a.STATUS = 1 
            AND a.product_id IN (
        SELECT DISTINCT
            `product_id` 
        FROM
            `product_props` AS a 
        WHERE
            a.`pn_id` = 5 
            AND a.`pv_id` = 127 
            AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 11 AND `pv_id` = 22 ) 
            AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 10 AND `pv_id` = 18 ) 
            AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 9 AND `pv_id` = 1 ) 
            AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 8 AND `pv_id` = 6 ) 
            );

耗时5.7-5.85 (执行10次的范围)

  • explain分析:

SQL 性能实战来了,机不可失!


    SELECT SQL_NO_CACHE
        count(1) 
    FROM
        pdi_product a
        join (SELECT DISTINCT
        `product_id` 
    FROM
        `product_props` AS a 
    WHERE
        a.`pn_id` = 5 
        AND a.`pv_id` = 127 
        AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 11 AND `pv_id` = 22 ) 
        AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 10 AND `pv_id` = 18 ) 
        AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 9 AND `pv_id` = 1 ) 
        AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 8 AND `pv_id` = 6 ) ) b
    on a.product_id = b.product_id
    WHERE
        a.STATUS = 1 

耗时5.7-6.0(执行10次的范围)

  • explain分析:

SQL 性能实战来了,机不可失!


可以看到如果单纯查属性表,第一位的速度是最快的,可要查产品状态后,速度反而不如子查询。

经explain分析,第一个子查询速度之所以快是因为它的sql简单,select_type皆为simple。

而不管是join还是exists的方式,select_type大多为DERIVED,DEPENDENT SUBQUERY。

结果

经讨论后,决定迁移到ES。

PS:你的赞是我创作的动力!

为什么同是9年义务教育别人就那么优秀?

想知道请关注订阅号:Buger(关注送 laravel,linux,nginx 等学习资料!!!)

回复'学习',推荐你2本书。

三海

本帖由系统于 1个月前 自动加精
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
讨论数量: 40

select product_id from product where pv_id = 135
union all
select product_id from product where pv_id = 23

1个月前 评论

@xiucai 这种也试过,当只有2个左右条件时,查询时间在5S左右(有索引的情况下),一旦条件增加到5个以上,所需要的时间长到没法用。数据量目前在800W左右。

1个月前 评论

@Rekkles or不行,查询是查出满足所有条件的,or的话不满足其中一个的也会被查询出来

1个月前 评论
巴啦啦

如果是我的话,分两次查询吧,whereIn

1个月前 评论

@巴啦啦 用in的话也是和or一样的效果,某些不符合的也会查询出来

1个月前 评论

????直接 IN 就完事了

1个月前 评论

@huijiewei in的逻辑是满足其中一个就查询出来了,而我们要的是全都满足条件才查询出来

1个月前 评论
韩众

试试

select product_id,pv_id, count(distinct(pv_id)) as disCount from product where pv_id in (135,23)  group by product_id 
 having disCount > 1
1个月前 评论

表示没看懂需求,为啥要join自身?不是直接 where and就完了么

1个月前 评论
Elijah_Wang

MySQL 之 嵌套查询 或许有所助益:

mysql > SELECT `product_id` FROM `product` WHERE `pn_id` = 5 AND `pv_id` = 135 AND `product_id` IN (SELECT `product_id` FROM `product` WHERE `pn_id` = 11 AND `pv_id` = 23);
# OR:
mysql > SELECT `product_id` FROM `product` WHERE `pn_id` = 5 AND `pv_id` = 135 AND `product_id` = ANY (SELECT `product_id` FROM `product` WHERE `pn_id` = 11 AND `pv_id` = 23);

以上 SQL 查询语句,亲测可用,符合贵司需求,然目测效率未必如愿。可以小试一下。

PS:期待 MySQL 达人可以给出高分答案,坐等ing ~

1个月前 评论

你要给个可以模拟你们数据特征的migration和seeder

1个月前 评论

@simpleT 他们是让你第二步whereIn主键

1个月前 评论

@韩众 这个试了下,不行。用in的话,是或的情况,某些产品不具有该属性也会被查出来

1个月前 评论

@Elijah_Wang 这个满足要求,性能比join,union等要好很多

1个月前 评论
2 Bug_tu: @Elijah_Wang 这个可以考虑,等线上稳定后,再做个分享 1个月前
Elijah_Wang: 本地测试可用,但没有百万级别的测试数据,因此,不知道二者性能差距究竟会有多大。方便的话,可以给一下具体执行时间吗? 1个月前
Bug_tu: @Elijah_Wang 826W数据,0.7S左右,目前性能最快的,之前使用join自身做的,0.8S左右,上线后就经常超时。。 1个月前
Bug_tu: @Elijah_Wang 2种耗时相差0.01 - 0.02左右 ,第二种要慢一点 1个月前
Elijah_Wang: 那这样的话,就应该满足需求了。多组查询,就用多级嵌套 subquery 好了。聚合函数的使用,确实对性能影响很大,百万级别的数据,group-by 一次也很要命的。 1个月前
Bug_tu: @Elijah_Wang 是的,实在没更好的就打算用这个了,具体还要看线上运行的情况 1个月前
Elijah_Wang: 讲真,这只是两组查询条件的 AND 组合,我在想,多组查询条件并列使用时,效率又将如何,嵌套查询与 join 的使用,或许还当另行斟酌。取决于线上运行的表现了。 1个月前
Bug_tu: @Elijah_Wang 是的,目前搞了6组的条件,查询耗时依然是0.7秒左右。但又怕上线后和join的一样。 1个月前
Elijah_Wang: 另外,建议:等 LZ 有空了,可以就此 SQL 查询问题,写个小总结,附带分享一下运行数据,以说明各种方案的性能优劣。 1个月前

@韩众 跑了一下,800W数据量,耗时16S。我更新了一下问题描述,可能之前描述不够清晰

1个月前 评论

用子查询咯:SELECT product_id FROM product WHERE product_id in(SELECT product_id FROM product WHERE pv_id =135) and pv_id =23;

1个月前 评论

@guofenle 子查询是作为最后的手段,目前在找有没有更好的 :joy:

1个月前 评论

0.几秒已经不错了 不行。对于并发大的一般都不这么查了,你再加个 redis 做缓存 妥妥的

1个月前 评论

@_Daniel 是的,这个也可以考虑,但还想看看在没有这些外部的支持下能做到什么程度

1个月前 评论

@Kamicloud 这个可能用不了laravel自带的功能,目前公司用的是4.1版本的 :joy:

1个月前 评论

看你的需求,只是要product_id去重不就好了 :select distinct product_id from product where pv_id in (135,23);

1个月前 评论
韩众

优化表结构 , 通过增加细分类把一级分类索引降到万级

1个月前 评论

我拿250w数据试了下,where exists表现最好

1s


SELECT 
    sql_no_cache `product_id`
FROM
    `zx_tests`
WHERE
    `pn_id` = 101 AND `pv_id` = 59
        AND `product_id` = ANY (SELECT 
            sql_no_cache `product_id`
        FROM
            `zx_tests`
        WHERE
            `pn_id` = 101 AND `pv_id` = 171);

0.7s


SELECT 
    sql_no_cache *
FROM
    `zx_tests` AS a
WHERE
    `pn_id` = 101 AND `pv_id` = 59
        AND product_id in ( SELECT 
           sql_no_cache product_id
        FROM
            `zx_tests`
        WHERE
            `pn_id` = 101 AND `pv_id` = 171);

0.15s


SELECT 
    sql_no_cache `product_id`
FROM
    `zx_tests` AS a
WHERE
    `pn_id` = 101 AND `pv_id` = 59
        AND EXISTS( SELECT 
           sql_no_cache  *
        FROM
            `zx_tests`
        WHERE
        a.product_id = product_id and
            `pn_id` = 101 AND `pv_id` = 171);
1个月前 评论

@guofenle 是可以,但是where条件不满足要求。

1个月前 评论

@simpleT 你试试我发的最后一个,改下查询

1个月前 评论

@Kamicloud 2组条件下0.657,3组0.695,4组0.759,5组0.743,目前来看性能比子查询快一点点。

1个月前 评论

@simpleT 子查询关缓存了吗?

1个月前 评论

@Kamicloud 这个没有,周一再全部测试一遍

1个月前 评论

这种复杂的条件过滤查询+较大的数据量,不应该放mysql上去查了,可以上ES。

1个月前 评论

@eddy8 是的,目前正在考虑全部迁移到es上

1个月前 评论
TimJuly

file

你这里的model_id都是1的情况下代表了 iPhone 的各个型号,想要找 iPhone 8 就必须去属性表里查pn_id=5 and pv_id=8的ID,想要找 iPhone 8 Plus 就必须去属性表里查pn_id=5 and pv_id=8Plus的ID,那么model_id这列就相当于没用了

正常情况下一种手机(例如华为 P30 Pro),按颜色和内存大小一共就那么几种组合,如果你product表设计得好,那么只看这一张表一共也查不出来几条记录,最多50种组合就已经很了不起了,这些组合我用计算器都能筛选出来用户要的指定颜色指定内存的,自然不会慢吧。

所以,一个设计的不合理的数据库会导致后续很多的问题

1个月前 评论
TimJuly

再来说说另一个你担忧的问题

属性条件可能会有超过 10 组

既然你们用mysql进行搜索,想必肯定是下拉框让用户选。这里先抛开下拉框联动带来的一系列开发难度和用户的懵逼程度。

就算超过了10组,例如有 形状,颜色,内存,网络类型,运营商 等等等等。如果你是用户你会在搜索界面里把所有的搜索条件都勾全了么?用户都是傻逼么?

想想你在京东购物是啥搜索流程,再想想即使你给用户提供了20种下拉菜单让用户选搜索条件,用户会选么?

既然是搜索,那么还是直接一个搜索框输入完了就出结果是最好的体验,下拉框那种都是上个世纪的功能了,所以还是直接上搜索引擎吧,别纠结mysql了。

1个月前 评论

@TimJuly 是的,这个表目前这样设计是不合理的,这也是历史原因。现在的业务都围绕这个表去跑的。目前准备全部使用es去做了。

1个月前 评论

期待后续替换ES过程的分享,大数据筛选应该是很多菜鸟关心的问题

1个月前 评论

@lovecn 莫名其妙,编辑下看到多出了很多。

1个月前 评论

我其实有点疑问 大家的做法?

找出型号为苹果 9 同时内存为 512G,颜色为土豪金,状态为正常的产品总数,

苹果9 是确认的,属性内存为 512G 和 颜色也是确认的

SELECT
    count(product_id)
FROM
    product 
WHERE
    product_id = (
SELECT
    product_id 
FROM
    product_props 
WHERE
    pn_id = 5 
    AND pv_id = 135 
    AND product_id = ( SELECT product_id FROM `product_props` WHERE pn_id = 11 AND pv_id = 23 ) 
    AND product_id = ( SELECT product_id FROM `product_props` WHERE pn_id = 10 AND pv_id = 17 ) 
    )
7小时前 评论

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