子查询与性能问题，！真线下降为什么！？

netkiller

下面是我的SQL，性能很慢。所引都优化过了。

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

如果分开运行性能非常之好。如。

先取出部分数据
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 ( 上面的结果 ) AND product_attribute_value.product_type_attribute_id = '8' ) ORDER BY list_order ASC


为什么？是不是mysql subquery有层数限制。。我套太多层了？

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

猪知猪之道

原帖由 netkiller 于 2008-5-28 17:00 发表
下面是我的SQL，性能很慢。所引都优化过了。

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的子查询本来效率就很低的,更何况你嵌套了多个in,查询出来的结果集数量是几何增长的,把in查询转换成left join就效率高了

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

netkiller

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

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

可以这样写吗？

voxxu

原帖由 netkiller 于 2008-5-29 09:58 发表
left join 就要很多表，并且很多条件。可读性就差了。

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

可以这样写吗？

可读性是差了点,但是性能会高很多,看你怎么取舍了
如果一定要用in的子查询,就把子查询的结果尽量最小化

yueliangdao0608

Just replace your subquery with joins and unions.

chuhongze

原帖由 voxxu 于 2008-5-29 10:08 发表



可读性是差了点,但是性能会高很多,看你怎么取舍了
如果一定要用in的子查询,就把子查询的结果尽量最小化

赞同!

cxczqfrx

这也是mysql的特性了
简单查询飞速
复杂查询就慢点了

yueliangdao0608

原帖由 cxczqfrx 于 2008-5-30 07:30 发表

这也是mysql的特性了
简单查询飞速
复杂查询就慢点了

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

是不是因为MyISAM 问题。
以前用Innodb 我写过更复杂的都没有问题。相当的好。
SQL 打印出来可能好几页A4纸。