故障分析 | MySQL 派生表优化

来到golang学习网的大家，相信都是编程学习爱好者，希望在这里学习数据库相关编程知识。下面本篇文章就来带大家聊聊《故障分析 | MySQL 派生表优化》，介绍一下MySQL，希望对大家的知识积累有所帮助，助力实战开发！

作者：xuty

一、问题 SQL

原 SQL 如下：

select name,count(name) from bm_id a left JOIN
    (select TaskName from up_pro_accept_v3_bdc 
        union all select TaskName from up_pro_accept_v3_hsjs
        union all select TaskName from up_pro_accept_v3_hszjj
        union all select TaskName from up_pro_accept_v3_hzl
        union all select TaskName from up_pro_accept_v3_kjyw
        union all select TaskName from up_pro_accept_v3_kpzzzxwx
        union all select TaskName from up_pro_accept_v3_qdzc
        union all select TaskName from up_pro_accept_v3_rsj
        union all select TaskName from up_pro_accept_v3_sjba
        union all select TaskName from up_pro_accept_v3_spk
        union all select TaskName from up_pro_accept_v3_test
        union all select TaskName from up_pro_accept_v3_wygl
        union all select TaskName from up_pro_accept_v3_yms
        union all select TaskName from up_pro_accept_v3_zjj
        union all select TaskName from up_pro_accept_v3w) t 
    on  a.zxi = t.TaskName  group by name

这是一个统计类的 SQL，直接执行跑了好几个小时都没有结束，所以暂时不知道实际耗时，因为实在是太久了~

二、执行计划

老步骤，我们先看下执行计划，如下图：

这里 SQL 执行主要分为 2 个步骤：

1. 顺序扫描每个

   select * from (select * from up_pro_accept_v3_bdc) a 
   where a.rowguid = '185c44aa-c23f-4e6f-bcd2-a38df16e2cc3'

   
   

   四、SQL 优化

   简单介绍了下派生表，下面我们开始尝试优化这个 SQL，步骤分 2 步：

   1. 解决多张派生子表

      /* 改写后 SQL */
      SELECT NAME
          ,count(NAME)
      FROM (
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_bdc bdc ON bm_id.zxi = bdc.TaskName
          UNION ALL 
          SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_hsjs hsjs ON bm_id.zxi = hsjs.TaskName
          UNION ALL 
          SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_hszjj hszjj ON bm_id.zxi = hszjj.TaskName
          UNION ALL 
          SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_hzl hzl ON bm_id.zxi = hzl.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_kjyw kjyw ON bm_id.zxi = kjyw.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_kpzzzxwx kp ON bm_id.zxi = kp.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_qdzc qdzc ON bm_id.zxi = qdzc.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_rsj rsj ON bm_id.zxi = rsj.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_sjba sjba ON bm_id.zxi = sjba.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_spk spk ON bm_id.zxi = spk.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_test test ON bm_id.zxi = test.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_wygl wygl ON bm_id.zxi = wygl.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_yms yms ON bm_id.zxi = yms.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3_zjj zjj ON bm_id.zxi = zjj.TaskName
          UNION ALL
          SELECT NAME    FROM bm_id LEFT JOIN up_pro_accept_v3w v3w ON bm_id.zxi = v3w.TaskName
          ) t
      GROUP BY t.name

      再来看下改写后的 SQL 执行计划，发现确实如我们预想的，在子查询中可以通过索引来进行表关联（

      CREATE TABLE `tmp_up` (
        `taskname` varchar(500) DEFAULT NULL,
        KEY `idx_taskname` (`taskname`));

      1. 将子查询结果插入至临时表

      insert into tmp_up 
      select taskname from up_pro_accept_v3_bdc 
      union all select taskname up_pro_accept_v3_hsjs
      ......

      1. 使用临时表代替子查询

      select name,count(name) from bm_id a left JOIN
                  (select TaskName from tmp_up )t 
          on  a.zxi = t.TaskName  group by name

      1. 对比下查询结果是否一致

      惊讶的发现改写 SQL 的结果集会多出来很多？这里可以确认走临时表的结果集是肯定没问题的，那么问题肯定出在改写 SQL 上！

      

      回头再仔细想一下，结合小测试，发现这样改写 SQL 确实会改变语义，问题主要是出在

      LEFT JOIN

      ，原本 bm_id 只作了

      一次表关联

      ，而改写 SQL 后，要做

      多次表关联

      ，导致最后的结果集会多出来一部分因为

      LEFT JOIN

      而产生的重复数据。

      如果是

      INNER JOIN

      ，其实就不会产生重复数据，我们也测试下，结果确实如所想，内联是没问题的~
      

      六、个人总结

      这次 SQL 优化案例个人感觉是比较有难度的，很多点自己一开始也没有想到。就比如 SQL 改写，一开始以为是没有语义上的区别，直到做了测试才知道，所以啊，很多时候不能盲目自信啊。

      针对这个 SQL 来说，想要直接通过改写 SQL 优化还是比较难的，当然这里说的是不改变语义的情况下，我暂时没有想到好的改写方式，也许是火候还不够。

      解决方式总结有 2 个：

      1. 用

         内联

         替代

         左联

         ，然后使用上述的改写 SQL，优点是

         比较方便且查询速度较快

         ，但是

         结果集会变化

         。
      2. 通过

         临时表

         代替

         子查询

         ，缺点是

         比较繁琐，需要多个步骤实现

         ，优点是

         速度也较快

         且

         结果集不会变化

         。

      附录：

      http://mysql.taobao.org/month...
      https://blog.csdn.net/sun_ash...
      https://imysql.com/node/103
      https://dev.mysql.com/doc/ref...

      今天关于《故障分析 | MySQL 派生表优化》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！