子查詢與性能問(wèn)題，！真線下降為什么��？

netkiller

下面是我的SQL，性能很慢。所引都優(yōu)化過(guò)了。

SELECT product_type_attribute_value.id,        product_type_attribute_value_locale.* FROM product_type_attribute_value      LEFT JOIN product_type_attribute_value_locale ON       product_type_attribute_value.id =        product_type_attribute_value_locale.product_type_attribute_value_id AND         product_type_attribute_value_locale.locale_language_id = '1' WHERE product_type_attribute_value.product_type_attribute_id = '8' AND       product_type_attribute_value.id in (SELECT        product_attribute_value.product_type_attribute_value_id FROM         product_attribute_value WHERE product_attribute_value.product_id IN (         SELECT product_item_unsold.product_id FROM product_item_unsold WHERE          product_item_unsold.product_id IN (SELECT id FROM product WHERE status           = 'ac' and master_product_id = '324') GROUP BY            product_item_unsold.product_id) AND             product_attribute_value.product_type_attribute_id = '8') ORDER BY list_order ASC

如果分開(kāi)運(yùn)行性能非常之好。如。

先取出部分?jǐn)?shù)據(jù)
SELECT product_item_unsold.product_id FROM product_item_unsold WHERE product_item_unsold.product_id IN ( SELECT id FROM product WHERE status='ac' and master_product_id = '324' ) GROUP BY product_item_unsold.product_id



SELECT product_type_attribute_value.id, product_type_attribute_value_locale.* FROM product_type_attribute_value LEFT JOIN product_type_attribute_value_locale ON product_type_attribute_value.id = product_type_attribute_value_locale.product_type_attribute_value_id AND product_type_attribute_value_locale.locale_language_id = '1' WHERE product_type_attribute_value.product_type_attribute_id = '8' AND product_type_attribute_value.id in ( SELECT product_attribute_value.product_type_attribute_value_id FROM product_attribute_value WHERE product_attribute_value.product_id IN ( 上面的結(jié)果 ) AND product_attribute_value.product_type_attribute_id = '8' ) ORDER BY list_order ASC


為什么？是不是mysql subquery有層數(shù)限制。。我套太多層了？

[ 本帖最后由 netkiller 于 2008-5-28 17:19 編輯 ]

豬知豬之道

原帖由 netkiller 于 2008-5-28 17:00 發(fā)表
下面是我的SQL，性能很慢。所引都優(yōu)化過(guò)了。

SELECT product_type_attribute_value.id, product_type_attribute_value_locale.* FROM product_type_attribute_value LEFT JOIN product_type_attribute_value ...

我給你整理一下

SELECT   PRODUCT_TYPE_ATTRIBUTE_VALUE.ID,          PRODUCT_TYPE_ATTRIBUTE_VALUE_LOCALE.* FROM     PRODUCT_TYPE_ATTRIBUTE_VALUE          LEFT JOIN PRODUCT_TYPE_ATTRIBUTE_VALUE_LOCALE          ON PRODUCT_TYPE_ATTRIBUTE_VALUE.ID = PRODUCT_TYPE_ATTRIBUTE_VALUE_LOCALE.PRODUCT_TYPE_ATTRIBUTE_VALUE_ID          AND PRODUCT_TYPE_ATTRIBUTE_VALUE_LOCALE.LOCALE_LANGUAGE_ID = '1' WHERE    PRODUCT_TYPE_ATTRIBUTE_VALUE.PRODUCT_TYPE_ATTRIBUTE_ID = '8'          AND PRODUCT_TYPE_ATTRIBUTE_VALUE.ID IN          (              SELECT     PRODUCT_ATTRIBUTE_VALUE.PRODUCT_TYPE_ATTRIBUTE_VALUE_ID                       FROM   PRODUCT_ATTRIBUTE_VALUE                      WHERE  PRODUCT_ATTRIBUTE_VALUE.PRODUCT_ID IN                           (                           SELECT       PRODUCT_ITEM_UNSOLD.PRODUCT_ID                                       FROM     PRODUCT_ITEM_UNSOLD                                       WHERE    PRODUCT_ITEM_UNSOLD.PRODUCT_ID IN                                       (                                       SELECT     ID FROM   PRODUCT                                             WHERE  STATUS = 'ac'                                             AND MASTER_PRODUCT_ID = '324'                                     ) GROUP BY PRODUCT_ITEM_UNSOLD.PRODUCT_ID                           ) AND PRODUCT_ATTRIBUTE_VALUE.PRODUCT_TYPE_ATTRIBUTE_ID = '8'              ) ORDER BY LIST_ORDER ASC

[ 本帖最后由 豬知豬之道 于 2008-5-28 17:18 編輯 ]

voxxu

in的子查詢本來(lái)效率就很低的,更何況你嵌套了多個(gè)in,查詢出來(lái)的結(jié)果集數(shù)量是幾何增長(zhǎng)的,把in查詢轉(zhuǎn)換成left join就效率高了

[ 本帖最后由 voxxu 于 2008-5-29 09:47 編輯 ]

netkiller

left join 就要很多表，并且很多條件。可讀性就差了。

select * from (select * from table) as tmp left join table on xxx=xxx

可以這樣寫(xiě)嗎？

voxxu

原帖由 netkiller 于 2008-5-29 09:58 發(fā)表
left join 就要很多表，并且很多條件�？勺x性就差了。

select * from (select * from table) as tmp left join table on xxx=xxx

可以這樣寫(xiě)嗎？

可讀性是差了點(diǎn),但是性能會(huì)高很多,看你怎么取舍了
如果一定要用in的子查詢,就把子查詢的結(jié)果盡量最小化

yueliangdao0608

Just replace your subquery with joins and unions.

chuhongze

原帖由 voxxu 于 2008-5-29 10:08 發(fā)表



可讀性是差了點(diǎn),但是性能會(huì)高很多,看你怎么取舍了
如果一定要用in的子查詢,就把子查詢的結(jié)果盡量最小化

贊同!

cxczqfrx

這也是mysql的特性了
簡(jiǎn)單查詢飛速
復(fù)雜查詢就慢點(diǎn)了

yueliangdao0608

原帖由 cxczqfrx 于 2008-5-30 07:30 發(fā)表

這也是mysql的特性了
簡(jiǎn)單查詢飛速
復(fù)雜查詢就慢點(diǎn)了

The document said.
The main advantages of subqueries are:

They allow queries that are structured so that it is possible to isolate each part of a statement.

They provide alternative ways to perform operations that would otherwise require complex joins and unions.

They are, in many people's opinion, more readable than complex joins or unions. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL “Structured Query Language.”


So I think you should read it more seriously.

netkiller

是不是因?yàn)镸yISAM 問(wèn)題。
以前用Innodb 我寫(xiě)過(guò)更復(fù)雜的都沒(méi)有問(wèn)題。相當(dāng)?shù)暮谩?br /> SQL 打印出來(lái)可能好幾頁(yè)A4紙。