解決 SQL 問題絕對能讓你對 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組。

要求

效能第一，杜絕聚合函數等

原問題的解決方案效能排行

來自 @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 （單獨查屬性表）

來自 @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 (新問題之前)

新問題之後的效能排行

來自 @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分析：

來自 @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。

相關免費學習推薦：

以上就是解決 SQL 問題絕對能讓你對 MySQL 的理解更進一步！的詳細內容，更多請關注TW511.COM其它相關文章！