站長資訊網(wǎng)SQL教程欄目介紹如何更有效理解MySQL
推薦（免費(fèi)）：SQL教程
屬性表(product_props)結(jié)構(gòu)如下
數(shù)據(jù)量800W以上
| 字段名 | 類型 | 說明 |
|---|---|---|
| id | int | id |
| pn_id | int | 屬性類型 |
| pv_id | int | 屬性值 |
| product_id | int | 產(chǎn)品ID |
其中product_id與pn_id,pv_id是一對(duì)多的關(guān)系。
數(shù)據(jù)類似這樣:
| product_id | pn_id | pv_id |
|---|---|---|
| 10970 | 5 （型號(hào)） | 135 （蘋果9） |
| 10970 | 11 （內(nèi)存） | 23 （512G） |
| 10970 | 10 （顏色） | 17 （土豪金） |
| 10970 | 8 （網(wǎng)絡(luò)） | 6（5G） |
| 10980 | 5 | 135 |
| 10980 | 11 | 24 （1024G） |
| 10980 | 10 | 16 （極光藍(lán)） |
產(chǎn)品表(product)結(jié)構(gòu)如下
數(shù)據(jù)量40W以上
| 字段名 | 類型 | 說明 |
|---|---|---|
| product_id | int | product_id |
| type_id | int | 類型id |
| brand_id | int | 品牌id |
| model_id | int | 型號(hào)id |
| status | tinyint | 狀態(tài) |
數(shù)據(jù)類似以下：
| product_id | type_id | brand_id | model_id | status |
|---|---|---|---|---|
| 10970 | 1（手機(jī)） | 1(蘋果) | 1(Iphone8) | 1(正常) |
| 10980 | 1（手機(jī)） | 1(蘋果) | 1(Iphone8X) | 3(已售) |
| 10981 | 1（手機(jī)） | 1(蘋果) | 1(Iphone8XP) | 1(正常) |
問題
找出型號(hào)為蘋果9同時(shí)內(nèi)存為512G，顏色為土豪金，狀態(tài)為正常的產(chǎn)品總數(shù)，
ps : 屬性條件可能會(huì)有超過10組。
要求
性能第一，杜絕聚合函數(shù)等
原問題的解決方案性能排行
- 來自 @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 （單獨(dú)查屬性表）
- 來自 @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；
耗時(shí)1.5-1.56 （執(zhí)行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 ))
耗時(shí)0.69-0.72（執(zhí)行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 ) );
耗時(shí)5.7-5.85 （執(zhí)行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
耗時(shí)5.7-6.0（執(zhí)行10次的范圍）
- explain分析：
可以看到如果單純查屬性表，第一位的速度是最快的，可要查產(chǎn)品狀態(tài)后，速度反而不如子查詢。
經(jīng)explain分析，第一個(gè)子查詢速度之所以快是因?yàn)樗膕ql簡單，select_type皆為simple。
而不管是join還是exists的方式，select_type大多為DERIVED，DEPENDENT SUBQUERY。
相關(guān)免費(fèi)學(xué)習(xí)推薦：mysql視頻教程
