MySQL性能优化之复杂SQL优化实践

  前几篇对于Mysql性能优化分别进行了探索阐述,本文将对前述要点进行模拟实践,如下:

  环境准备:

    新建表tuser1和tuser2:

      MySQL性能优化之复杂SQL优化实践

    通过查询test_insert存储过程,创建类似存储过程初始化tuser1,tuser2数据:  

      select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE|FUNCTION'' //存储过程或函数
      show procedure|function status; //存储过程或函数

      show create procedure proc_name;
      show create function func_name;

      MySQL性能优化之复杂SQL优化实践

       MySQL性能优化之复杂SQL优化实践

   业务场景:tuser表按照地区分组统计求和,要求是在tuser1表和tuser2表中出现过的地区

    select address, count(*) from tuser where address in (select address from tuser1 union select address from tuser2)group by address;MySQL性能优化之复杂SQL优化实践

   tuser中adress只有beijing,所以结果是正确的,但是这个查询花的时间太长了。修改测试数据是查询有数据看看是什么效果,还花这么长时间么?

    MySQL性能优化之复杂SQL优化实践

     MySQL性能优化之复杂SQL优化实践

   一样耗时较长。使用explain分析为啥耗时如此之长:MySQL性能优化之复杂SQL优化实践

     type:为ALL 说明没有索引,全表扫描
    Using temporary:说明使用了临时表
    Using where:没有索引下推,在Server层进行了全表扫描和过滤
  第一次优化:给address加索引

      MySQL性能优化之复杂SQL优化实践

       MySQL性能优化之复杂SQL优化实践加索引之后速度不到原来的一半,可谓事半功倍。

      MySQL性能优化之复杂SQL优化实践

     explain分析使用了添加的索引,但是联合查询即union还是舍弃了索引的使用,那就这点进行优化。

  第二次优化:不使用union

    select address, count(*) from tuser where address in (select distinct address from tuser1) or address in (select distinct address from tuser2)group by address; 

      MySQL性能优化之复杂SQL优化实践

     可以看到查询时间只有个位数了,为什么呢?通过explain分析可知没有使用临时表了——这是表象,本质是union需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟;不使用当然就不存在这部分开销了。故一般不推荐使用union关键字。

   第三次优化:过滤无效数据

    还可以在优化么?通过explain分析执行计划可以看出,rows=9747263——索引只是使用了覆盖索引,说明还是几乎扫描了全表的行。

      MySQL性能优化之复杂SQL优化实践

     可以先过滤掉不满足条件的数据是遍历tuser的数据量成数据量级减少提高效率,当然过滤的时间不能耗时否则过滤就没有意义了。

    具体使用join,left join或rignt join可以通过数据少的过滤掉数据量多的。

    select x.address, count(*) from  (select b.* from tuser1 a left join tuser b on a.address = b.address union select d.* from tuser2 c left join tuser d on c.address = d.address ) x group by x.address;

      MySQL性能优化之复杂SQL优化实践

     此次优化使用了union,与第二次优化对比似乎可知减少遍历次数比不适用union效果更好。

  最后一次优化:派生表

        MySQL性能优化之复杂SQL优化实践

     上次优化还是使用到了临时表,如果将将派生表写成视图,是否能提高查询效率呢?

      create view v_tuser as select b.* from tuser1 a left join tuser b on a.address = b.address union select distinct d.* from rom tuser2 c left join tuser d on c.address = d.address ;

        MySQL性能优化之复杂SQL优化实践

      两个不同的查询语句:

         MySQL性能优化之复杂SQL优化实践

     通过测试可以发现使用count(address),查询熟读可以是0.00sec——毫秒级。

  总结:

    优化结果:从最初的将近2分钟优化到不到1秒。

    优化总结:
      1)开启慢查询日志,定位运行慢的SQL语句
      2)利用explain执行计划,查看SQL执行情况
      3)关注索引使用情况:type
      4)关注Rows:行扫描
      5)关注Extra:没有信息最好
      6)加索引后,查看索引使用情况,index只是覆盖索引,并不算很好的使用索引
      7)如果有关联尽量将索引用到eq_ref或ref级别
      8)复杂SQL可以做成视图,视图在MySQL内部有优化,而且开发也比较友好
      9)对于复杂的SQL要逐一分析,找到比较费时的SQL语句片段进行优化

   

 

 



上一篇:Rocket - core - ibuf vs. imem


下一篇:JavaWeb课程复习资料(七)——select服务查询所有功能编写