使用MySQL的示例分析

这篇文章主要介绍了使用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 （单独查属性表）

2. 来自 @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分析：

        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分析：
  

2. 来自 @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分析：

    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分析：

---

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

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

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

感谢你能够认真阅读完这篇文章，希望小编分享的“使用MySQL的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持创新互联，关注创新互联行业资讯频道，更多相关知识等着你来学习!

文章题目：使用MySQL的示例分析
文章出自：http://mswzjz.cn/article/jodsee.html