Shulou(Shulou.com)06/01 Report--

This article mainly introduces the use of MySQL example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

The structure of the property product_props is as follows

More than 800W of data

Field name type description idintidpn_idint attribute type pv_idint attribute value product_idint product ID

There is an one-to-many relationship between product_id and pn_id,pv_id.

The data looks like this:

Product_idpn_idpv_id109705 (Model) 135 (Apple 9) 1097011 (memory) 23 (512g) 1097010 (Color) 17 (Tuhao Jin) 109708 (Network) 6 (5G) 109805135109801124 (1024g) 109801016 (Aurora Blue)

The product list (product) is as follows

The amount of data is over 40W

Field name type description product_idintproduct_idtype_idint type idbrand_idint brand idmodel_ int model idstatustinyint status

The data is similar to the following:

Product_idtype_idbrand_idmodel_idstatus109701 (Mobile) 1 (Apple) 1 (Iphone8) 1 (normal) 109801 (Mobile) 1 (Apple) 1 (Iphone8X) 3 (sold) 109811 (Mobile) 1 (Apple) 1 (Iphone8XP) 1 (normal)

problem

Find out the total number of products with a model of Apple 9 with 512GB of memory, a color of Tuhaojin and a normal status.

Ps: there may be more than 10 sets of attribute conditions.

Request

Performance first, put an end to aggregate functions, etc.

The performance ranking of the solution to the original problem

Exist scheme from @ Kamicloud

SELECT sql_no_cache `product_ id`FRom `zx_ tests` AS aWHERE `pn_ id` = 101 AND `Tests` = 59 AND EXISTS (SELECT sql_no_cache * FROM `zx_ tests`WHERE a.product_id = product_id and `pn_ id` = 101 AND `pv_ id` = 171) under 2 groups, 0.657, 3 groups, 0.695, 4 groups, 0.759, 5 groups, 0.743 (check the property sheet separately)

Subquery scheme from @ Elijah_Wang

SELECT `pv_ 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); under 2 groups, 0.729 Magi, 3 groups, 0.75 Magi, 4 groups, 0.730 Magi, 5 groups 0.757 (before the new problem)

Performance ranking after new problems

Subquery scheme from @ 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_pro ps`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`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

Time-consuming 1.5-1.56 (range of 10 executions)

Expain Analysis:

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 `AND id` IN (SELECT `product_ id`FROM `product_ props`props`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))

Time-consuming 0.69-0.72 (range of 10 executions)

Explain Analysis:

Exist scheme from @ Kamicloud

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 `AND EXISTS 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))

Time 5.7-5.85 (range of 10 executions)

Explain Analysis:

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` = 127AND EXISTS (SELECT product_id FROM `product_ props`WHERE a.product_id = product_id AND `pn_ id` = 11 AND `propsid` = 22) AND EXISTS (SELECT product_ Id FROM `product_ props`WHERE a.product_id = product_id AND `props` = 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

Time-consuming 5.7-6.0 (range of 10 executions)

Explain Analysis:

You can see that if you simply look up the property table, the first place is the fastest, but after you want to check the status of the product, the speed is not as fast as the sub-query.

According to explain analysis, the speed of the first sub-query is fast because its sql is simple and its select_type is simple.

Whether it's join or exists, select_type is mostly DERIVED,DEPENDENT SUBQUERY.

Thank you for reading this article carefully. I hope the article "sample Analysis of using MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.