La résolution des problèmes SQL fera certainement progresser votre compréhension de MySQL !

La colonne

Tutoriel SQL présente comment comprendre MySQL plus efficacement

Recommandé (gratuit) : Tutoriel SQL

La structure de la table attributaire (product_props) est la suivante

Le le volume de données est supérieur à 800 W

字段名	类型	说明
id	int	id
pn_id	int	属性类型
pv_id	int	属性值
product_id	int	产品ID

Le product_id, pn_id et pv_id sont des relations un-à-plusieurs.
Les données sont similaires à ceci :

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 （极光蓝）

La structure de la table des produits (produit) est la suivante

Le volume de données est supérieur à 40W

字段名	类型	说明
product_id	int	product_id
type_id	int	类型id
brand_id	int	品牌id
model_id	int	型号id
status	tinyint	状态

Les données sont similaires ci-dessous :

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(正常)

Question

Découvrez que le modèle est Apple 9 et la mémoire est 512G, et la couleur est Or local, le nombre total de produits avec statut normal,
ps : Il peut y avoir plus de 10 groupes de conditions d'attribut.

Exigences

La performance d'abord, éviter les fonctions d'agrégation, etc.

Classement des performances des solutions au problème d'origine

1. Schéma existant de @Kamicloud

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 （单独查属性表）

2. Schéma de sous-requête de @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 (新问题之前)

Classement des performances après une nouvelle question

1. Schéma de sous-requête de @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；

Prendre 1,5-1,56 (plage d'exécution 10 fois)

• analyse 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 ))

prend 0,69-0,72 (plage d'exécution 10 fois)

• expliquer l'analyse :
  

2. solution existante de @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 `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 ) 
            );

A pris 5,7-5,85 (plage de 10 exécutions)

• expliquer l'analyse :

    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

Il faut 5,7-6,0 (plage d'exécution 10 fois)

• expliquer l'analyse :

---

Vous pouvez voir que si vous vérifiez simplement la table attributaire, la vitesse de la première position est la plus rapide, mais lorsque vous souhaitez vérifier l'état du produit, la vitesse n'est pas aussi bonne que la sous-requête .

Après l'analyse expliquée, la raison pour laquelle la première sous-requête est rapide est que son SQL est simple et que le select_type est simple.

Quelle que soit la méthode join ou exist, select_type est principalement une SOUS-REQUÊTE DERIVED et DEPENDENT.

Recommandations d'apprentissage gratuites associées : Tutoriel vidéo MySQL

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!