分类: Mysql

[慢查优化]慎用MySQL子查询，尤其是看到DEPENDENT SUBQUERY标记时

[ 2015-03-20 13:05:42 | 作者: admin ]
字号: 大 | 中 | 小
很早发现这个现象，但不明就里，原来是重复执行A表倍数的查询，难怪这种查询的类型奇慢无比
http://www.cnblogs.com/zhengyun_ustc/archive/2013/11/29/slowquery3.html

写在前面的话：
在慢查优化1和2里都反复强调过 explain 的重要性，但有时候肉眼看不出 explain 结果如何指导优化，这时候还需要有一些其他基础知识的佐助，甚至需要了解 MySQL 实现原理，如子查询慢查优化。
看到 SQL 执行计划中 select_type 字段中出现“DEPENDENT SUBQUERY”时，要打起精神了！
——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，取决于外面的查询 。
换句话说，就是 子查询对 g2 的查询方式依赖于外层 g1 的查询。
什么意思呢？它意味着两步：
第一步，MySQL 根据 select gid,count(id) from shop_goods where status=0 group by gid; 得到一个大结果集 t1，其数据量就是上图中的 rows=850672 了。
第二步，上面的大结果集 t1 中的每一条记录，都将与子查询 SQL 组成新的查询语句：select gid from shop_goods where sid in (15...blabla..29) and gid=%t1.gid%。等于说，子查询要执行85万次……即使这两步查询都用到了索引，但不慢才怪。
如此一来，子查询的执行效率居然受制于外层查询的记录数，那还不如拆成两个独立查询顺序执行呢。
 
优化策略1：
你不想拆成两个独立查询的话，也可以与临时表联表查询，如下所示：
SELECT g1.gid,count(1)
FROM shop_goods g1,(select gid from shop_goods WHERE sid in (1519066,1466114,1466110,1466102,1466071,1453929)) g2
where g1.status=0 and g1.gid=g2.gid
GROUP BY g1.gid;
也能得到同样的结果，且是毫秒级。
它的执行计划为：
         id select_type table type possible_keys key key_len ref rows Extra
------ ----------- -------------- ------ ------------------------- ------------- ------- ----------- ------ -------------------------------
           1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 30 Using temporary; Using filesort
           1 PRIMARY g1 ref idx_gid idx_gid 5 g2.gid 1 Using where
           2 DERIVED shop_goods range id_shop_goods,idx_sid id_shop_goods 5 (NULL) 30 Using where; Using index

DERIVED 的官方含义为：
DERIVED：用于 from 子句里有子查询的情况。MySQL 会递归执行这些子查询，把结果放在临时表里。
[最后修改由 admin， 于 2015-03-20 13:08:26]
评论Feed 评论Feed: http://blog.xg98.com/feed.asp?q=comment&id=2268

这篇日志没有评论。

此日志不可发表评论。