成长

小军军

博客园 首页 联系 订阅 管理

image

下面我们来具体分析一下查询处理的每一个阶段MySQL的语句一共分为11步,如下图所标注的那样,最先执行的总是FROM操作,最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表,这个虚拟的表作为一个处理的输入,只是这些虚拟的表对用户来说是透明的,但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句,那么将会跳过相应的步骤

  • FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
  • ON: 对虚表VT1进行ON筛选,只有那些符合<join-condition>的行才会被记录在虚表VT2中。
  • JOIN: 如果指定了OUTER JOIN(比如left join、 right join),那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中,产生虚拟表VT3, rug from子句中包含两个以上的表的话,那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤,一直到处理完所有的表为止。
  • WHERE: 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
  • GROUP BY: 根据group by子句中的列,对VT4中的记录进行分组操作,产生VT5.
  • CUBE | ROLLUP: 对表VT5进行cube或者rollup操作,产生表VT6.
  • HAVING: 对虚拟表VT6应用having过滤,只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。
  • SELECT: 执行select操作,选择指定的列,插入到虚拟表VT8中。
  • DISTINCT: 对VT8中的记录进行去重。产生虚拟表VT9.
  • ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作,产生虚拟表VT10.
  • LIMIT:取出指定行的记录,产生虚拟表VT11, 并将结果返回。
  • 子查询的执行

    子查询又称内部查询,而包含子查询的语句称之外部查询(又称主查询)。

    所有的子查询可以分为两类,即相关子查询和非相关子查询。

    非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。

    相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。

     

    使用过oracle或者其他关系数据库的DBA或者开发人员都有这样的经验,在子查询上都认为数据库已经做过优化,能够很好的选择驱动表执行,然后在把该经验移植到mysql数据库上,但是不幸的是,mysql在子查询的处理上有可能会让你大失所望,在我们的生产系统上就碰到过一些案例,例如:

    SELECT i_id,
           sum(i_sell) AS i_sell
    FROM table_data
    WHERE i_id IN
        (SELECT i_id
         FROM table_data
         WHERE Gmt_create >= '2011-10-07 00:00:00')
    GROUP BY i_id;

    (备注:sql的业务逻辑可以打个比方:先查询出10-07号新卖出的100本书,然后在查询这新卖出的100本书在全年的销量情况)。

    这条sql之所以出现的性能问题在于mysql优化器在处理子查询的弱点,mysql优化器在处理子查询的时候,会将将子查询改写。通常情况下,我们希望由内到外,先完成子查询的结果,然后在用子查询来驱动外查询的表,完成查询;但是mysql处理为将会先扫描外面表中的所有数据,每条数据将会传到子查询中与子查询关联,如果外表很大的话,那么性能上将会出现问题;
    针对上面的查询,由于table_data这张表的数据有70W的数据,同时子查询中的数据较多,有大量是重复的,这样就需要关联近70W次,大量的关联导致这条sql执行了几个小时也没有执行完成,所以我们需要改写sql:

    SELECT t2.i_id,
           SUM(t2.i_sell) AS sold
    FROM
      (SELECT DISTINCT i_id
       FROM table_data
       WHERE gmt_create >= '2011-10-07 00:00:00') t1,
                                                  table_data t2
    WHERE t1.i_id = t2.i_id
    GROUP BY t2.i_id;

    我们将子查询改为了关联,同时在子查询中加上distinct,减少t1关联t2的次数;
    改造后,sql的执行时间降到100ms以内。
    mysql的子查询的优化一直不是很友好,一直有受业界批评比较多,也是我在sql优化中遇到过最多的问题之一,mysql在处理子查询的时候,会将子查询改写,通常情况下,我们希望由内到外,也就是先完成子查询的结果,然后在用子查询来驱动外查询的表,完成查询,但是恰恰相反,子查询不会先被执行;今天希望通过介绍一些实际的案例来加深对mysql子查询的理解。

    posted on 2016-11-21 15:26  houqijun  阅读(2512)  评论(0编辑  收藏  举报