问题SQL优化:从超过300s优化到1s案例分析

发表于:2019-7-03 10:25  作者:波波说运维   来源:今日头条

字体: | 上一篇 | 下一篇 |我要投稿 | 推荐标签: 数据库 SQL

  概述
  今天发现有个项目应用每次一启动后使用就会导致另外一个应用服务直接崩溃,而这两个应用使用的是同个数据库,经过排查可以发现是报表应用的某个查询功能导致,而咨询开发但却查不出是哪条sql,那就只能靠自己排查了..下面是解决的大致过程。
  1、开启慢查询
  修改my.ini配置,增加参数
   slow-query-log=1
  slow_query_log_file="epms-slow.log"
  long_query_time=10
  修改后重启,观察epms-slow.log日志内容。
  2、定位慢sql
  重新点击报表模块,选择日期后点击查询,等系统崩溃后,观察日志涉及的慢查询sql
  发现问题sql如下:
   select id, parent, project, name
  from zentao.zt_task
  where parent = 0
  /*and exists (select t.parent from zentao.zt_task t where t.parent > 0)*/
  and id in (
  select t.parent from zentao.zt_task t where t.parent > 0
  );
  3、查看执行计划
   explain select id, parent, project, name
  from zentao.zt_task
  where parent = 0
  /*and exists (select t.parent from zentao.zt_task t where t.parent > 0)*/
  and id in (
  select t.parent from zentao.zt_task t where t.parent > 0
  );
  
    这里可以看到因为走的全扫,每次都扫5万条,产生笛卡尔积,5万*5万就导致数据库崩溃了。
  4、考虑用exists改写sql
   explain select id, parent, project, name
  from zentao.zt_task t
  where parent = 0 and exists (
  select a.parent from zentao.zt_task a where a.parent = t.id
  )
  
    这里改写后问题还是没解决。
  5、考虑with改写
  后来发现zt_task表查询了两次,所以考虑with改写来简化,只查一次
   WITH tmp AS ( SELECT * FROM zt_task ) SELECT
  *
  FROM
  tmp t1
  JOIN tmp t2 ON t1.id = t2.parent
  
   好吧,mysql5.7还不支持with改写,只有到mysql 8版本才支持,所以这里只能放弃这种办法了
  6、用子查询join改写
   SELECT
  distinct t1.parent,
  t1.id,
  t1.project,
  t1.NAME
  FROM
  zentao.zt_task t1
  JOIN ( SELECT t.parent FROM zentao.zt_task t WHERE t.parent > 0 ) ta ON t1.id = ta.parent
  AND t1.parent =0
  
   这里要记得去重,改写后查询在1秒内得出结果,满足需求。
  总结
  通过这道案例一定要记住,多表查询的性能是很差的,当然,性能差是有一个前提的:数据量大。子查询 = 简单查询 + 限定查询 + 多表查询 + 统计查询的综合体;
  在之前强调过多表查询不建议大家使用,因为性能很差,但是多表查询最有利的替代者就是子查询,所以子查询(子查询指的就是在一个查询之中嵌套了其他的若干查询)在实际的工作之中使用的相当的多。

      上文内容不用于商业目的,如涉及知识产权问题,请权利人联系博为峰小编(021-64471599-8017),我们将立即处理

【大佬说】测试员跳槽时,如何高效地准备面试?

评 论

论坛新帖

顶部 底部


建议使用IE 6.0以上浏览器,800×600以上分辨率,法律顾问:上海瀛东律师事务所 张楠律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2019, 沪ICP备05003035号
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪公网安备 31010102002173号

51Testing官方微信

51Testing官方微博

扫一扫 测试知识全知道