这篇文章主要介绍了使用MySQL的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。
网站设计制作、成都做网站服务团队是一支充满着热情的团队，执着、敏锐、追求更好，是创新互联的标准与要求，同时竭诚为客户提供服务是我们的理念。
创新互联公司把每个网站当做一个产品来开发，精雕细琢，追求一名工匠心中的细致，我们更用心！
属性表(product_props)结构如下
数据量800W以上
| 字段名 | 类型 | 说明 |
|---|
| id | int | id |
| pn_id | int | 属性类型 |
| pv_id | int | 属性值 |
| product_id | int | 产品ID |
其中product_id与pn_id,pv_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)结构如下
数据量40W以上
| 字段名 | 类型 | 说明 |
|---|
| 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(正常) |
问题
找出型号为苹果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次的范围）
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次的范围）
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分析，第一个子查询速度之所以快是因为它的sql简单，select_type皆为simple。
而不管是join还是exists的方式，select_type大多为DERIVED，DEPENDENT SUBQUERY。
感谢你能够认真阅读完这篇文章，希望小编分享的“使用MySQL的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持创新互联网站建设公司，，关注创新互联行业资讯频道，更多相关知识等着你来学习!
文章标题：使用MySQL的示例分析-创新互联
URL分享：
http://cdkjz.cn/article/djcihc.html
