解决 SQL 问题绝对能让你对 MySQL 的理解更进一步！

SQL教程 栏目介绍如何更有效理解MySQL 推荐（免费）：SQL教程 属性表(product_props)结构如下 数据量800W以上 字段名 类型 说明 idintidpn_idint属性类型pv_idint属性值product_idint产品ID 其中product

SQL教程栏目介绍如何更有效理解MySQL

推荐（免费）：SQL教程

属性表(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`
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分析：

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。

推荐：mysql视频教程

以上就是解决 SQL 问题绝对能让你对 MySQL 的理解更进一步！的详细内容，更多请关注每日运维其它相关文章！