精品国产一区二区三区久久蜜臀,狠狠人妻久久久久久综合,欧美激情精品久久久久

sql教程欄目介紹如何更有效理解mysql

推薦（免費(fèi)）：sql教程

屬性表(product_props)結(jié)構(gòu)如下

數(shù)據(jù)量800W以上

字段名	類型	說(shuō)明
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以上

字段名	類型	說(shuō)明
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(正常)

問(wèn)題

找出型號(hào)為蘋果9同時(shí)內(nèi)存為512G，顏色為土豪金，狀態(tài)為正常的產(chǎn)品總數(shù)，
ps : 屬性條件可能會(huì)有超過(guò)10組。

要求

性能第一，杜絕聚合函數(shù)等

原問(wèn)題的解決方案性能排行

來(lái)自 @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ú)查屬性表）

來(lái)自 @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 (新問(wèn)題之前)

新問(wèn)題之后的性能排行

來(lái)自 @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分析：

解決 SQL 問(wèn)題絕對(duì)能讓你對(duì) MySQL 的理解更進(jìn)一步！

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

來(lái)自 @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分析：

解決 SQL 問(wèn)題絕對(duì)能讓你對(duì) MySQL 的理解更進(jìn)一步！

    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次的范圍）