使用MySQL的示例分析

152次閱讀

沒有評論

共計 4161 個字符，預計需要花費 11 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章主要介紹了使用 MySQL 的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

屬性表 (product_props) 結構如下

數據量 800W 以上

字段名類型說明 idintidpn_idint 屬性類型 pv_idint 屬性值 product_idint 產品 ID

其中 product_id 與 pn_id,pv_id 是一對多的關系。
數據類似這樣:

product_idpn_idpv_id109705 （型號）135（蘋果 9）1097011 （內存）23（512G）1097010 （顏色）17（土豪金）109708 （網絡）6（5G）109805135109801124（1024G）109801016（極光藍）

產品表 (product) 結構如下

數據量 40W 以上

字段名類型說明 product_idintproduct_idtype_idint 類型 idbrand_idint 品牌 idmodel_idint 型號 idstatustinyint 狀態

數據類似以下：

product_idtype_idbrand_idmodel_idstatus109701（手機）1(蘋果)1(Iphone8)1(正常)109801（手機）1(蘋果)1(Iphone8X)3(已售)109811（手機）1(蘋果)1(Iphone8XP)1(正常)

問題

找出型號為蘋果 9 同時內存為 512G，顏色為土豪金，狀態為正常的產品總數，
ps : 屬性條件可能會有超過 10 組。

要求

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

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

來自 @Kamicloud 的 exist 方案

SELECT 
 sql_no_cache `product_id`
 `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 分析：

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

 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。

感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“使用 MySQL 的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!

向 AI 問一下細節

正文完