写在前面的话:
在慢查优化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 会递归执行这些子查询,把结果放在临时表里。
DBA观点引用:MySQL 子查询的弱点
hidba 论述道(参考资源3):
mysql 在处理子查询时,会改写子查询。
通常情况下,我们希望由内到外,先完成子查询的结果,然后再用子查询来驱动外查询的表,完成查询。
例如:
select * from test where tid in(select fk_tid from sub_test where gid=10)
通常我们会感性地认为该 sql 的执行顺序是:
sub_test 表中根据 gid 取得 fk_tid(2,3,4,5,6)记录,
然后再到 test 中,带入 tid=2,3,4,5,6,取得查询数据。
但是实际mysql的处理方式为:
select * from test where exists (
select * from sub_test where gid=10 and sub_test.fk_tid=test.tid
)
mysql 将会扫描 test 中所有数据,每条数据都将会传到子查询中与 sub_test 关联,子查询不会先被执行,所以如果 test 表很大的话,那么性能上将会出现问题。
《高性能MySQL》一书的观点引用
《高性能MySQL》的第4.4节“MySQL查询优化器的限制(Limitations of the MySQL Query Optimizer)”之第4.4.1小节“关联子查询(Correlated Subqueries)”也有类似的论述:
MySQL有时优化子查询很糟,特别是在WHERE从句中的IN()子查询。……
比如在sakila数据库sakila.film表中找出所有的film,这些film的actoress包括Penelope Guiness(actor_id = 1)。可以这样写:
mysql> SELECT * FROM sakila.film
-> WHERE film_id IN(
-> SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
根据EXPLAIN的输出,MySQL将全表扫描film表,对找到的每行执行子查询,这是很不好的性能。幸运的是,很容易改写为一个join查询:
mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
另外一个方法是通过使用GROUP_CONCAT()执行子查询作为一个单独的查询,手工产生IN()列表。有时候比join还快。(注:你不妨在我们的库上试试看 SELECT goods_id,GROUP_CONCAT(cast(id as char))
FROM bee_shop_goods
WHERE shop_id IN (1519066,1466114,1466110,1466102,1466071,1453929)
GROUP BY goods_id;)
MySQL已经因为这种特定类型的子查询执行计划而被批评。
何时子查询是好的
MySQL并不总是把子查询优化得很糟。有时候还是很优化的。下面是个例子:
mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
-> WHERE NOT EXISTS(
-> SELECT * FROM sakila.film_actor
-> WHERE film_actor.film_id = film.film_id
-> )G
……(注:具体文字还是请阅读《高性能MySQL》吧)
是的,子查询并不是总是被优化得很糟糕,具体问题具体分析,但别忘了 explain 。
参考资源:
1,2011,wudongxu,mysql子查询(in)的实现;
2,2012,iteye,MySQL子查询很慢的问题;
3,2011,hidba,mysql子查询的弱点 和 生产库中遇到mysql的子查询;
慢查系列:
[慢查优化]建索引时注意字段选择性 & 范围查询注意组合索引的字段顺序
[慢查优化]联表查询注意谁是驱动表 & 你搞不清楚谁join谁更好时请放手让mysql自行判定
http://www.sql8.net
分享到:
相关推荐
2,select_type 可以为一下任何一种类型simple 简单select(不使用union或子查询)primary 最外面的selectunion union中的第二个或后面的select语句dependent union union中的第二个或后面的select语句,取决于...
composer require mpyw/eloquent-has-by-non-dependent-subquery 动机 假设您具有以下关系: class Post extends Model { use SoftDeletes ; public function comments (): HasMany { return $ this -> has...
Layout Dependent Effect.Layout Dependent Effect. Layout Dependent Effect.Layout Dependent Effect.
Time dependent problems frequently pose challenges in areas of science and engineering dealing with numerical analysis, scientific computation, mathematical models, and most importantly—numerical ...
long range dependent alaysis long range dependent alaysis
affected by the time-dependent behavior of packet delays and losses and can differ from an idealized stability region derived from an aggregate view of the loss and delay processes
用到GROUP BY 语句查询时com.MySQL.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘col_user_6.a.START_TIME’ ...
总是提示opencv_java410.dll: Can't find dependent libraries,搜索N多资源无解,最终通过Dependency Walker 取得缺失的DLL列表,然后百度逐个下载不同版本的DLL测试,最终找到了完整的DLL包,分享给大家。
Parcellation Dependent Small World Brain.pdf
This is a book that, from my knowledge, is the best practice book to talk about programing in the industry-strength proof assistant Coq which a dependent type system. It is good start to understand ...
Hardware-dependent Software (HdS) plays a key role in desktop computers and servers for many years. Mainly due to its flexibility, the possibility of late change, and the quick adaptability, the ...
We study the scheduling decision for an application consisting of dependent tasks, in a generic cloud computing system comprising a network of heterogeneous local processors and a remote cloud server.
前端项目-dependent-dropdown,允许嵌套依赖项的多级依赖下拉jquery插件。
解决libarcsoft_face.dll:Cant‘t find dependent library报错,相关文章:https://blog.csdn.net/chw0629/article/details/122557038
最近在将mysql升级到mysql 5.7后,进行一些group by 查询时,比如下面的 SELECT *, count(id) as count FROM `news` GROUP BY `group_id` ORDER BY `inputtime` DESC LIMIT 20 就会报如下错误: SELECT list is ...
1、Error in Virtual User 1: Error: Failed to load mysqltcl - couldn't load library "D:/Program Files/HammerDB-4.6/lib/mysqltcl3.052/libmysqltcl.dll": this library or a dependent library could not be ...
1、Error in Virtual User 1: Error: Failed to load mysqltcl - couldn't load library "D:/Program Files/HammerDB-4.6/lib/mysqltcl3.052/libmysqltcl.dll": this library or a dependent library could not be ...
学习coq用的书
理解参数依赖查找! 加深对C++的理解! 我从这里开始理解ADL!