`
zhengyun_ustc
  • 浏览: 82830 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

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

阅读更多

写在前面的话:

   不要求每个人一定理解 联表查询(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
0
1
分享到:
评论

相关推荐

    mysql Join使用以及优化

    此外,使用straight_join代替普通的JOIN语句可以强制MySQL使用指定的表作为驱动表进行查询,这样可以更明确地控制查询计划,有助于分析和优化Join操作。这种控制有时候能够帮助DBA找到更高效的查询路径。 在实际...

    MySQL优化篇:慢查询日志.pdf

    为了让慢查询日志永久生效,需要修改MySQL的配置文件`***f`,在`[mysqld]`部分加入慢查询日志相关的配置,例如:`slow_query_log=1`表示开启慢查询日志,`long_query_time=3`设置慢查询阈值为3秒,`log_output=FILE`...

    MySQL索引原理及慢查询优化1

    MySQL索引原理及慢查询优化是数据库管理中的重要主题,尤其是在高并发、大数据量的互联网环境中,优化查询性能对于系统的整体效能至关重要。MySQL作为广泛使用的开源关系型数据库,其索引机制和查询优化技巧是开发者...

    二、mysql优化技术-定位慢查询

    "二、mysql优化技术-定位慢查询"这一主题聚焦于如何识别和解决性能瓶颈,提升数据库的响应速度。这篇博文可能详细介绍了如何诊断和解决MySQL中的慢查询问题,而`sql.txt`文件很可能包含了一些示例SQL语句或者慢查询...

    Mysql慢查询日志查看

    Mysql 慢查询日志查看是指在 Mysql 数据库中,记录和分析执行时间较长的查询语句,以便优化数据库性能和提高查询效率。慢查询日志查看可以帮助数据库管理员和开发者快速地定位和优化数据库中的性能瓶颈。 一、慢...

    mysql性能优化-慢查询分析、优化索引和配置.doc

    ### MySQL性能优化详解 #### 一、优化概述 MySQL作为一种广泛使用的开源关系型数据库管理系统,在很多应用场景中都可能遇到性能瓶颈的问题。常见的性能瓶颈主要分为两大类:**CPU瓶颈**和**I/O瓶颈**。 - **CPU...

    sql优化 联表查询

    在SQL优化领域,联表查询(也称为JOIN查询)是数据操作的重要部分,尤其是在大型数据库系统中。当需要从多个表中提取相关数据时,联表查询就显得尤为重要。本篇将深入探讨“sql优化”与“左连接查询”的相关知识点,...

    mysql多表查询和EXISTS查询性能对比

    ### MySQL多表查询和EXISTS查询性能对比 #### 测试环境 在进行MySQL多表查询与EXISTS查询的性能对比之前,首先需要了解测试环境的相关配置。虽然原文中并未给出具体的测试环境细节,但在实际操作中,这一步骤至关...

    mysql子查询与连表查询的效率比较及优化

    ### MySQL 子查询与连表查询的效率比较及优化 #### 一、子查询与连表查询概述 在SQL查询语言中,子查询与连表查询是两种非常重要的查询技术,广泛应用于各种复杂的业务场景中。为了更好地理解这两种查询方式的特点...

    mysql查询优化之索引优化

    MySQL查询优化是数据库管理中的关键环节,特别是在大数据量的场景下,索引优化能显著提升查询性能。本文将深入探讨“mysql查询优化之索引优化”这一主题。 首先,了解索引的基本概念至关重要。索引是数据库为了快速...

    MySQL InnoDB 查询优化实现分析

    这些改进包括但不限于对多表 JOIN 的优化,能够更有效地利用索引和其他优化技术。 #### 五、统计信息 **5.1 统计信息收集** InnoDB 存储引擎会自动收集表的统计信息,包括行数、列的分布情况等。这些信息对于优化...

    Mysql多表联合查询

    MySql,多表联合查询加AS语句,多余的就不说了,自己看,绝对简单!

    2.mysql查询性能优化1

    MySQL 提供了多种查询优化技术,包括使用 UNION 优化、使用子查询优化、使用 JOIN 优化等。 6.3 MySQL 查询优化工具 MySQL 提供了多种查询优化工具,包括 EXPLAIN、SHOW PROFILE 等。这些工具可以帮助数据库管理员...

    MySQL海量数据查询优化策略.

    在处理MySQL海量数据查询优化时,我们需要关注的策略包括但不限于以下几点: 1. 优化索引使用:避免全表扫描至关重要。为此,应当在查询条件(WHERE)和排序(ORDER BY)涉及的列上创建索引。索引有助于数据库管理...

    MySQL多表查询详解

    ### MySQL多表查询详解 #### 一、多表查询的基本概念与操作 多表查询是在实际业务场景中非常常见的需求之一。它允许我们从多个表中检索数据,并将这些数据组合在一起以形成有意义的信息。在MySQL中,多表查询通常...

    MySQL查询优化浅析

    ### MySQL查询优化浅析 #### 一、查询优化概述 查询优化是数据库管理系统(DBMS)中的一个重要组成部分,其主要目标是在接收到一个SQL查询后,寻找最高效的执行计划,以尽可能快的速度返回查询结果。这一过程涉及到...

    基于Solr的多表join查询加速方法

    在处理多表join查询时,传统的关系型数据库如MySQL等通常能很好地应对,但Solr作为一个非关系型的搜索引擎,其原生功能并不支持复杂的数据关联操作。本文将围绕"基于Solr的多表join查询加速方法"这一主题,深入探讨...

    MYSQL查询、优化原理

    ### MySQL 查询与优化原理 #### 一、SQL 查询原理 MySQL 的 SQL 查询处理流程非常关键,涉及到了查询的解析、优化以及执行等多个阶段。当 MySQL 收到 SQL 语句时,它首先会通过解析器对 SQL 语句进行解析,生成...

    mysql慢查询工具Anemometer

    MySQL的慢查询日志是数据库性能优化的重要工具,它记录了执行时间超过预设阈值的所有SQL查询。Anemometer,作为一个专为MySQL设计的慢查询分析工具,可以帮助数据库管理员更有效地理解和解决性能问题。 Anemometer...

    mysql临时表用法分析【查询结果可存在临时表中】

    INTO` 语句,但MySQL不支持直接将查询结果存入临时表,所以通常采用 `CREATE TABLE AS SELECT` 或 `INSERT INTO ... SELECT` 的方式: ```sql -- 创建临时表并填充数据 CREATE TABLE tmp_table_name AS SELECT ...

Global site tag (gtag.js) - Google Analytics