SQL チュートリアル このコラムでは、MySQL をより効果的に理解する方法を紹介します
属性テーブル (product_props) の構造は次のとおりです
データ音量が 800 W を超えていますタイプ | 説明 | |
---|---|---|
int | id | |
int | 属性タイプ | |
int | 属性値 | |
int | Product ID |
pn_id | pv_id | |
---|---|---|
5 (モデル) | 135 (Apple 9) | |
11 (メモリ) | 23 (512G) | |
10 (カラー) | 17 (ローカルゴールド) | |
8 (ネットワーク) | 6(5G) | |
5 | 135 | |
11 | 24 (1024G) | |
10 | 16 (オーロラブルー) |
##データ量が 40W を超える
説明 | ||
---|---|---|
product_id | type_id | |
typeid | brand_id | |
brandid | model_id | |
モデル id | ステータス | |
ステータス |
brand_id | model_id | status | ||
---|---|---|---|---|
1(Apple) | 1(iPhone8) | 1(通常) | 10980 | |
1(Apple) | 1(iPhone8X) | 3 (販売済み) | 10981 | |
1(Apple) | 1 (Iphone8XP) | 1(正常) |
モデル番号が であることを確認します。 Apple 9
同時に、メモリは512G、色は ローカル ゴールド、ステータスは normal、製品の総数は 、ps: 属性条件は 10 グループを超える可能性があります。 要件
元の問題に対する解決策のパフォーマンス ランキング
@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 (单独查属性表)
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;
分析の説明:
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 ))
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 ) );
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
分析の説明:
Explain 分析後、最初のサブクエリが速い理由は、その SQL が単純であり、select_type が単純であるためです。
mysql ビデオ チュートリアル
以上がSQL の問題を解決すると、MySQL についての理解が確実に一歩前進します。の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。