解決 SQL 問題絕對能讓你對 MySQL 的理解更進一步！

SQL教學欄位介紹如何更有效理解MySQL

推薦（免費）：SQL教學

屬性表(product_props)結構如下

資料量800W以上

##int產品ID

欄位名稱	類型	說明
id	int	id
pn_id	int	屬性類型
pv_id	int	屬性值
#product_id

#其中product_id與pn_id,pv_id是一對多的關係。

資料類似這樣:

product_id#pn_idpv_id##109705  （型號）135 （蘋果9）1097011    （記憶體） 23 （512G）1097010    （色）17 （土豪金）109708    （網路）#6（5G）

##10980	#5	135
10980	11	24 （1024G）
10980	10	16 （極光藍）

#產品表(product)結構如下

##數據量40W以上

欄位名稱類型說明product_idintproduct_idtype_id#brand_id#model_id##int型號idstatustinyint#狀態##資料類似以下：

##int	類型id
int	#品牌id

product_idtype_id#109701（手機）##1(正常) 109801（手機）#1(蘋果)1(Iphone8X)#3 (已售)109811（手機）1(蘋果)1(Iphone8XP)1(正常)問題

#brand_id	model_id	status
1(蘋果)	#1(Iphone8)

#找出型號為

蘋果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的子查詢方案

1. 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次的範圍）

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

• 
  來自@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次的範圍）

2. 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影片教學

以上是解決 SQL 問題絕對能讓你對 MySQL 的理解更進一步！的詳細內容。更多資訊請關注PHP中文網其他相關文章！