文档章节

[慢查优化]联表查询注意谁是驱动表 & 你搞不清楚谁join谁更好时请放手让mysql自行判定

旁观者-郑昀
 旁观者-郑昀
发布于 2013/09/16 12:27
字数 1592
阅读 56
收藏 4

写在前面的话:

   不要求每个人一定理解 联表查询(join/left join/inner join)时的mysql运算过程;

   不要求每个人一定知道线上(现在或未来)哪张表数据量大,哪张表数据量小;

    但把mysql客户端(如SQLyog,如HeidiSQL)放在桌面上,时不时拿出来 explain 一把,这是一种美德

在实例讲解之前,我们先回顾一下联表查询的基础知识。

——联表查询的基础知识——

引子:为什么第一个查询using temporary,第二个查询不用临时表呢?

下面两个查询,它们只差了一个order by,效果却迥然不同。

第一个查询:

EXPLAIN extended

SELECT ads.id

FROM ads, city 

WHERE

   city.city_id = 8005

   AND ads.status = 'online'

   AND city.ads_id=ads.id

ORDER BY ads.id desc

执行计划为:

    id  select_type  table   type    possible_keys   key      key_len  ref                     rows  filtered  Extra                          
------  -----------  ------  ------  --------------  -------  -------  --------------------  ------  --------  -------------------------------
     1  SIMPLE       city    ref     ads_id,city_id  city_id  4        const                   2838    100.00  Using temporary; Using filesort
     1  SIMPLE       ads     eq_ref  PRIMARY         PRIMARY  4        city.ads_id       1    100.00  Using where                   

第二个查询:

EXPLAIN extended

SELECT ads.id

FROM ads,city 

WHERE

   city.city_id =8005

   AND ads.status = 'online'

   AND city.ads_id=ads.id

ORDER BY city.ads_id desc

执行计划里没有了using temporary:
    id  select_type  table   type    possible_keys   key      key_len  ref                     rows  filtered  Extra                      
------  -----------  ------  ------  --------------  -------  -------  --------------------  ------  --------  ---------------------------
     1  SIMPLE       city    ref     ads_id,city_id  city_id  4        const                   2838    100.00  Using where; Using filesort
     1  SIMPLE       ads    eq_ref  PRIMARY         PRIMARY  4        city.ads_id       1    100.00  Using where               
为什么?
 
DBA告诉我们:
    MySQL 表关联的算法是 Nest Loop Join,是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。
 
EXPLAIN 结果中,第一行出现的表就是驱动表(Important!)
 
以上两个查询语句,驱动表都是 city,如上面的执行计划所示!
 
对驱动表可以直接排序对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序 (Important!)
因此,order by ads.id desc 时,就要先 using temporary 了!
 
驱动表的定义
wwh999  在 2006年总结说,当进行多表连接查询时,  [驱动表] 的定义为:
1)指定了联接条件时, 满足查询条件的记录行数少的表为[驱动表];
2) 未指定联接条件时,行数少的表为[驱动表] (Important!)。
 
忠告:如果你搞不清楚该让谁做驱动表、谁 join 谁,请让 MySQL 运行时自行判断
既然“未指定联接条件时, 行数少的表为[驱动表]”了,
而且你也对自己写出的复杂的 Nested Loop Join 不太有把握(如下面的实例所示),
就别指定谁 left/right join 谁了,
请交给 MySQL优化器 运行时决定吧。
如果您对自己特别有信心,可以 像火丁一样做优化
 
小结果集驱动大结果集
de.cel  在2012年总结说,不管是你,还是 MySQL,
优化的目标是尽可能减少JOIN中Nested Loop的循环次数,
以此保证:
永远用小结果集驱动大结果集 (Important!)
——实例讲解——
 
Nested Loop Join慢查SQL语句
先了解一下 mb 表有 千万级记录,mbei 表要少得多。慢查实例如下:
explain
SELECT mb.id, ……
FROMmb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid  
WHERE 1=1  
ORDER BY mbei.apply_time DESC
limit 0,10
够复杂吧。Nested Loop Join 就是这样,
以驱动表的结果集作为循环的基础数据,然后将结果集中的数据作为过滤条件一条条地到下一个表中查询数据,最后合并结果;此时还有第三个表,则将前两个表的 Join 结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此反复。
这条语句的执行计划如下:
    id  select_type  table   type    possible_keys   key             key_len  ref                     rows  Extra                                       
------  -----------  ------  ------  --------------  --------------  -------  -------------------  -------  --------------------------------------------
     1  SIMPLE       mb      index   userid          userid          4        (NULL)               6060455  Using index; Using temporary; Using filesort
     1  SIMPLE       mbei    eq_ref  mb_id  mb_id  4        mb.id             1                                              
     1  SIMPLE       u       eq_ref  PRIMARY         PRIMARY         4        mb.uid        1  Using index                                
由于动用了“LEFT JOIN”,所以攻城狮已经指定了驱动表,虽然这张驱动表的结果集记录数达到百万级!
.
.
如何优化?
.
.
优化第一步:LEFT JOIN改为JOIN
干嘛要 left join 啊?直接 join!
explain
SELECT mb.id……
FROM mb JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid  
WHERE 1=1  
ORDER BY mbei.apply_time DESC
limit 0,10
立竿见影,驱动表立刻变为小表 mbei 了, Using temporary 消失了,影响行数少多了:
    id  select_type  table   type    possible_keys   key      key_len  ref                             rows  Extra         
------  -----------  ------  ------  --------------  -------  -------  ----------------------------  ------  --------------
     1  SIMPLE       mbei    ALL     mb_id  (NULL)   (NULL)   (NULL)                         13383  Using filesort
     1  SIMPLE       mb      eq_ref  PRIMARY,userid  PRIMARY  4        mbei.mb_id       1                
     1  SIMPLE       u       eq_ref  PRIMARY         PRIMARY  4        mb.uid                1  Using index  

优化第一步之分支1:根据驱动表的字段排序,好吗?
left join不变。干嘛要根据非驱动表的字段排序呢?我们前面说过“对驱动表可以直接排序,对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序!”的。
explain
SELECT mb.id……
FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid  
WHERE 1=1  
ORDER BY mb.id DESC
limit 0,10
也满足业务场景,做到了rows最小:
    id  select_type  table   type    possible_keys   key             key_len  ref                    rows  Extra      
------  -----------  ------  ------  --------------  --------------  -------  -------------------  ------  -----------
     1  SIMPLE       mb      index   userid          PRIMARY         4        (NULL)                   10             
     1  SIMPLE       mbei    eq_ref  mb_id  mb_id  4        mb.id            1  Using index
     1  SIMPLE       u       eq_ref  PRIMARY         PRIMARY         4        mb.uid       1  Using index
 

优化第二步:去除所有JOIN,让MySQL自行决定!
写这么多密密麻麻的 left join/inner join 很开心吗?
explain
SELECT mb.id……
FROM mb,mbei,u   
WHERE
    mb.id=mbei.mb_id
    and mb.uid=u.user_id
order by mbei.apply_time desc
limit 0,10
立竿见影,驱动表一样是小表 mbei:
    id  select_type  table   type    possible_keys   key      key_len  ref                             rows  Extra         
------  -----------  ------  ------  --------------  -------  -------  ----------------------------  ------  --------------
     1  SIMPLE       mbei    ALL     mb_id  (NULL)   (NULL)   (NULL)                         13388  Using filesort
     1  SIMPLE       mb      eq_ref  PRIMARY,userid  PRIMARY  4        mbei.mb_id       1                
     1  SIMPLE       u       eq_ref  PRIMARY         PRIMARY  4        mb.uid                1  Using index  

最后的总结:
强调再强调:
不要过于相信你的运气!
不要相信你的开发环境里SQL的执行速度!
请拿起 explain 武器,
如果你看到以下现象,请优化:
  • 出现了Using temporary;
  • rows过多,或者几乎是全表的记录数;
  • key 是 (NULL);
  • possible_keys 出现过多(待选)索引。
 
记住,explain 是一种美德!
 
 
参考资源:
2)de.cel,2012, MySQL中的Join 原理及优化思路  

赠图一枚:
http://ww3.sinaimg.cn/bmiddle/97f224aagw1e8fffdvbtkg20b404qu0x.gif

© 著作权归作者所有

旁观者-郑昀
粉丝 100
博文 77
码字总数 162700
作品 0
朝阳
私信 提问
[慢查优化]慎用MySQL子查询,尤其是看到DEPENDENT SUBQUERY标记时

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

旁观者-郑昀
2013/11/29
208
1
[慢查优化]建索引时注意字段选择性 & 范围查询注意组合索引的字段顺序

写在前面的话: 之前曾说过“不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程”,但对于字段选择性差意味着什么,组合索引字段顺序意味着什么,要求每个人必须...

旁观者-郑昀
2013/09/22
594
1
MySQL JOIN连接查询知识点

MySQL JOIN连接查询知识点 MySQL支持的join操作:JOIN、STRAIGHT_JOIN、LEFT JOIN、RIGHT JOIN。不支持FULL OUTER JOIN。 在MySQL中,JOIN, CROSS JOIN, 和INNER JOIN相互等价。但是在正常的...

Gen_zhou
2016/08/12
244
0
MySQL的JOIN(四):JOIN优化实践之快速匹配

这篇博文讲述如何优化扫描速度。我们通过MySQL的JOIN(二):JOIN原理得知了两张表的JOIN操作就是不断从驱动表中取出记录,然后查找出被驱动表中与之匹配的记录并连接。这个过程的实质就是查...

文文1
2018/04/01
60
0
【黑魔法】Covering Indexes、STRAIGHT_JOIN

今天给大家介绍两个黑魔法,这都是压箱底的法宝。大家在使用时,一定要弄清他们的适用场景及用法,用好了,就是一把开天斧,用不好那就是画蛇添足。自从看过耗子哥(左耳朵耗子)的博客,都会...

夏雪冬日
2018/08/13
0
0

没有更多内容

加载失败,请刷新页面

加载更多

代理模式之JDK动态代理 — “JDK Dynamic Proxy“

动态代理的原理是什么? 所谓的动态代理,他是一个代理机制,代理机制可以看作是对调用目标的一个包装,这样我们对目标代码的调用不是直接发生的,而是通过代理完成,通过代理可以有效的让调...

code-ortaerc
56分钟前
4
0
学习记录(day05-标签操作、属性绑定、语句控制、数据绑定、事件绑定、案例用户登录)

[TOC] 1.1.1标签操作v-text&v-html v-text:会把data中绑定的数据值原样输出。 v-html:会把data中值输出,且会自动解析html代码 <!--可以将指定的内容显示到标签体中--><标签 v-text=""></......

庭前云落
今天
7
0
VMware vSphere的两种RDM磁盘

在VMware vSphere vCenter中创建虚拟机时,可以添加一种叫RDM的磁盘。 RDM - Raw Device Mapping,原始设备映射,那么,RDM磁盘是不是就可以称作为“原始设备映射磁盘”呢?这也是一种可以热...

大别阿郎
今天
10
0
【AngularJS学习笔记】02 小杂烩及学习总结

本文转载于:专业的前端网站☞【AngularJS学习笔记】02 小杂烩及学习总结 表格示例 <div ng-app="myApp" ng-controller="customersCtrl"> <table> <tr ng-repeat="x in names | orderBy ......

前端老手
昨天
14
0
Linux 内核的五大创新

在科技行业,创新这个词几乎和革命一样到处泛滥,所以很难将那些夸张的东西与真正令人振奋的东西区分开来。Linux内核被称为创新,但它又被称为现代计算中最大的奇迹,一个微观世界中的庞然大...

阮鹏
昨天
18
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部