[慢查优化]慎用MySQL子查询,尤其是看到DEPENDENT SUBQUERY标记时
案例梳理时间:2013-9-25写在前面的话:
——MySQL 的子查询为什么有时候很糟糕——
引子:这样的子查询为什么这么慢?
下面的例子是一个慢查,线上执行时间相当夸张。为什么呢?
SELECT gid,COUNT(id) as count?
FROM shop_goods g1
WHERE status =0 and gid IN (?
SELECT gid FROM shop_goods g2 WHERE sid IN ?(1519066,1466114,1466110,1466102,1466071,1453929)
)
GROUP BY gid;
它的执行计划如下,请注意看关键词“DEPENDENT SUBQUERY”:
??? id? select_type???????? table?? type??????????? possible_keys?????????????????????????? key?????????? key_len? ref?????? rows? Extra???? ?
------? ------------------? ------? --------------? --------------------------------------? ------------? -------? ------? ------? -----------
???? 1? PRIMARY???????????? g1????? index?????????? (NULL)????????????????????????????????? idx_gid? 5??????? (NULL)? 850672? Using where
???? 2? DEPENDENT SUBQUERY? g2????? index_subquery? id_shop_goods,idx_sid,idx_gid? idx_gid? 5??????? func???????? 1? Using where
?
基础知识:Dependent Subquery意味着什么
官方含义为:
SUBQUERY:子查询中的第一个SELECT;
DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 。
换句话说,就是
?
-over-