使用MySQL的示例分析

这篇文章主要介绍了使用MySQL的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

属性表(product_props)结构如下

数据量800W以上

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

产品表(product)结构如下

数据量40W以上

数据类似以下：

问题

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

要求

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

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

1. 来自 @Kamicloud的 exist方案

SELECT     sql_no_cache `product_id`FROM    `zx_tests` AS aWHERE    `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的示例分析"这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!