国产91色综合久久免费,久久无码AV中文出轨人妻,欧美一区二区久久精品

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

字段名	類型	說明
id	int	id
pn_id	int	屬性類型
pv_id	int	屬性值
product_id	int	產品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_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(正常)

要求

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

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

來自 @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 問題絕對能讓你對 MySQL 的理解更進一步！

        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 問題絕對能讓你對 MySQL 的理解更進一步！

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