文档章节

mysql 子查询优化一例

Linland
 Linland
发布于 2015/04/28 10:34
字数 1273
阅读 206
收藏 18
点赞 0
评论 0
写在前面的话:
  1. 在慢查优化12里都反复强调过 explain 的重要性,但有时候肉眼看不出 explain 结果如何指导优化,这时候还需要有一些其他基础知识的佐助,甚至需要了解 MySQL 实现原理,如子查询慢查优化
  2. 看到 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 。

© 著作权归作者所有

共有 人打赏支持
Linland
粉丝 30
博文 50
码字总数 15762
作品 0
西安
数据库管理员
Mysql——子查询

子查询的位置: select 中、from 后、where 中.group by 和order by 中无实用意义。 子查询分为如下几类: 1,标量子查询:返回单一值的标量,最简单的形式。 2,列子查询:返回的结果集是 ...

youxizhe ⋅ 2014/08/03 ⋅ 1

MySQL order by 与 limit 的优化

在web应用中,提倡sql简单,所以在我们公司的应用中看不到jon,子查询等语句的存在,所以间接oprder by 与 limit的使用占据大多数,其实很多技巧,别人都是总结过的,仔细分析,仔细学习别人的经验才...

xrzs ⋅ 2013/04/22 ⋅ 1

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

写在前面的话: ——MySQL 的子查询为什么有时候很糟糕—— 引子:这样的子查询为什么这么慢? 下面的例子是一个慢查,线上执行时间相当夸张。为什么呢? SELECT gid,COUNT(id) as count FR...

旁观者-郑昀 ⋅ 2013/11/29 ⋅ 1

一例 Hive join 优化实战

由于 hive 与传统关系型数据库面对的业务场景及底层技术架构都有着很大差异,因此,传统数据库领域的一些技能放到 Hive 中可能已不再适用。关于 hive 的优化与原理、应用的文章,前面也陆陆续...

xrzs ⋅ 2014/08/29 ⋅ 2

浅谈 MySQL 子查询及其优化

使用过oracle或者其他关系数据库的DBA或者开发人员都有这样的经验,在子查询上都认为数据库已经做过优化,能够很好的选择驱动表执行,然后在把该经验移植到mysql数据库上,但是不幸的是,mys...

xrzs ⋅ 2014/07/09 ⋅ 1

MySQL 子查询及其优化

使用过oracle或者其他关系数据库的DBA或者开发人员都有这样的经验,在子查询上都认为数据库已经做过优化,能够很好的选择驱动表执行,然后在把该经验移植到mysql数据库上,但是不幸的是,mys...

architect刘源源 ⋅ 03/08 ⋅ 0

MySQL · 性能优化 · 条件下推到物化表

背景 MySQL引入了Materialization(物化)这一关键特性用于子查询(比如在IN/NOT IN子查询以及 FROM 子查询)优化。 具体实现方式是:在SQL执行过程中,第一次需要子查询结果时执行子查询并将...

阿里云RDS-数据库内核组 ⋅ 2016/07/09 ⋅ 0

生产库中遇到mysql的子查询

使用过oracle或者其他关系数据库的DBA或者开发人员都有这样的经验,在子查询上都认为数据库已经做过优化,能够很好的选择驱动表执行,然后在把该经验移植到mysql数据库上,但是不幸的是,mys...

SANSOM ⋅ 2013/04/26 ⋅ 3

MySQL优化案例:半连接(semi join)优化方式导致的查询性能低下

以下是来自DBA+社群MySQL领域原创专家李海翔分享的MySQL优化案例,关于MySQL V5.6.x/5.7.x SQL查询性能问题。 专家简介 一、简单创建一表,并使用存储过程插入一部分数据...

李海翔 ⋅ 2015/12/07 ⋅ 0

SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方 ⋅ 2017/12/25 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

RabbitMQ学习以及与Spring的集成(三)

本文介绍RabbitMQ与Spring的简单集成以及消息的发送和接收。 在RabbitMQ的Spring配置文件中,首先需要增加命名空间。 xmlns:rabbit="http://www.springframework.org/schema/rabbit" 其次是模...

onedotdot ⋅ 23分钟前 ⋅ 0

JAVA实现仿微信红包分配规则

最近过年发红包拜年成为一种新的潮流,作为程序猿对算法的好奇远远要大于对红包的好奇,这里介绍一种自己想到的一种随机红包分配策略,还请大家多多指教。 算法介绍 一、红包金额限制 对于微...

小致dad ⋅ 35分钟前 ⋅ 0

Python 数电表格格式化 xlutils xlwt xlrd的使用

需要安装 xlutils xlwt xlrd 格式化前 格式化后 代码 先copy读取的表格,然后按照一定的规则修改,将昵称中的学号提取出来替换昵称即可 from xlrd import open_workbookfrom xlutils.copy ...

阿豪boy ⋅ 今天 ⋅ 0

面试题:使用rand5()生成rand7()

前言 读研究生这3 年,思维与本科相比变化挺大的,这几年除了看论文、设计方案,更重要的是学会注重先思考、再实现,感觉更加成熟吧,不再像个小P孩,人年轻时总会心高气傲。有1 道面试题:给...

初雪之音 ⋅ 今天 ⋅ 0

Docker Toolbox Looks like something went wrong

Docker Toolbox 重新安装后提示错误:Looks like something went wrong in step ´Checking if machine default exists´ 控制面板-->程序与应用-->启用或关闭windows功能:找到Hyper-V,如果处......

随你疯 ⋅ 今天 ⋅ 0

Guacamole 远程桌面

本文将Apache的guacamole服务的部署和应用,http://guacamole.apache.org/doc/gug/ 该链接下有全部相关知识的英文文档,如果水平ok,可以去这里仔细查看。 一、简介 Apache Guacamole 是无客...

千里明月 ⋅ 今天 ⋅ 0

nagios 安装

Nagios简介:监控网络并排除网络故障的工具:nagios,Ntop,OpenVAS,OCS,OSSIM等开源监控工具。 可以实现对网络上的服务器进行全面的监控,包括服务(apache、mysql、ntp、ftp、disk、qmail和h...

寰宇01 ⋅ 今天 ⋅ 0

AngularDart注意事项

默认情况下创建Dart项目应出现以下列表: 有时会因为不知明的原因导致列表项缺失: 此时可以通过以下步骤解决: 1.创建项目涉及到的包:stagehand 2.执行pub global activate stagehand或pub...

scooplol ⋅ 今天 ⋅ 0

Java Web如何操作Cookie的添加修改和删除

创建Cookie对象 Cookie cookie = new Cookie("id", "1"); 修改Cookie值 cookie.setValue("2"); 设置Cookie有效期和删除Cookie cookie.setMaxAge(24*60*60); // Cookie有效时间 co......

二营长意大利炮 ⋅ 今天 ⋅ 0

【每天一个JQuery特效】淡入淡出显示或隐藏窗口

我是JQuery新手爱好者,有时间就练练代码,防止手生,争取每天一个JQuery练习,在这个博客记录下学习的笔记。 本特效主要采用fadeIn()和fadeOut()方法显示淡入淡出的显示效果显示或隐藏元...

Rhymo-Wu ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部