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

数据库 其他数据库
今天发现有个项目应用每次一启动后使用就会导致另外一个应用服务直接崩溃,而这两个应用使用的是同个数据库,经过排查可以发现是报表应用的某个查询功能导致,而咨询开发但却查不出是哪条sql,那就只能靠自己排查了..下面是解决的大致过程。

概述

今天发现有个项目应用每次一启动后使用就会导致另外一个应用服务直接崩溃,而这两个应用使用的是同个数据库,经过排查可以发现是报表应用的某个查询功能导致,而咨询开发但却查不出是哪条sql,那就只能靠自己排查了..下面是解决的大致过程。

1、开启慢查询

修改my.ini配置,增加参数 

  1. slow-query-log=1  
  2. slow_query_log_file="epms-slow.log"  
  3. long_query_time=10 

修改后重启,观察epms-slow.log日志内容。

2、定位慢sql

重新点击报表模块,选择日期后点击查询,等系统崩溃后,观察日志涉及的慢查询sql

发现问题sql如下: 

  1. select id, parent, project, name  
  2.  from zentao.zt_task  
  3.  where parent = 0  
  4.  /*and exists (select t.parent from zentao.zt_task t where t.parent > 0)*/  
  5.  and id in (  
  6.  select t.parent from zentao.zt_task t where t.parent > 0  
  7.  ); 

3、查看执行计划 

  1. explain select id, parent, project, name  
  2.  from zentao.zt_task  
  3.  where parent = 0  
  4.  /*and exists (select t.parent from zentao.zt_task t where t.parent > 0)*/  
  5.  and id in (  
  6.  select t.parent from zentao.zt_task t where t.parent > 0  
  7.  ); 

这里可以看到因为走的全扫,每次都扫5万条,产生笛卡尔积,5万*5万就导致数据库崩溃了。

4、考虑用exists改写sql 

  1. explain select id, parent, project, name  
  2. from zentao.zt_task t  
  3. where parent = 0 and exists (  
  4. select a.parent from zentao.zt_task a where a.parent = t.id   

这里改写后问题还是没解决。

5、考虑with改写

后来发现zt_task表查询了两次,所以考虑with改写来简化,只查一次 

  1. WITH tmp AS ( SELECT * FROM zt_task ) SELECT  
  2. *   
  3. FROM  
  4.  tmp t1  
  5.  JOIN tmp t2 ON t1.id = t2.parent 

好吧,mysql5.7还不支持with改写,只有到mysql 8版本才支持,所以这里只能放弃这种办法了

6、用子查询join改写 

  1. SELECT  
  2.  distinct t1.parent,  
  3.  t1.id,  
  4.  t1.project,  
  5.  t1.NAME   
  6. FROM  
  7.  zentao.zt_task t1  
  8.  JOIN ( SELECT t.parent FROM zentao.zt_task t WHERE t.parent > 0 ) ta ON t1.id = ta.parent   
  9.  AND t1.parent =0 

这里要记得去重,改写后查询在1秒内得出结果,满足需求。

总结

通过这道案例一定要记住,多表查询的性能是很差的,当然,性能差是有一个前提的:数据量大。子查询 = 简单查询 + 限定查询 + 多表查询 + 统计查询的综合体;

在之前强调过多表查询不建议大家使用,因为性能很差,但是多表查询最有利的替代者就是子查询,所以子查询(子查询指的就是在一个查询之中嵌套了其他的若干查询)在实际的工作之中使用的相当的多。

责任编辑:庞桂玉 来源: 今日头条
相关推荐

2021-09-10 08:31:36

技术Prometheus监控

2022-09-27 08:40:44

慢查询MySQL定位优化

2022-07-05 10:50:31

数据库查询实战

2020-02-23 17:15:29

SQL分析查询

2023-05-14 17:16:22

分类树SpringBoot

2023-12-25 08:24:03

双异步数据库Excel

2022-06-29 09:43:14

SQL优化数据库

2020-01-16 18:30:07

技术SQL优化

2022-09-19 08:41:02

数据查询分离

2023-09-27 08:21:00

查询分离数据API

2022-06-30 19:40:36

查询接口索引优化

2022-08-14 14:32:06

接口优化

2024-03-04 08:29:33

数据定制化Java

2017-07-18 11:12:39

环境设置内存分析Python

2022-07-14 14:46:51

数据库SQL系统设计

2017-05-08 17:40:23

Oracle视图优化案例分析

2019-09-27 17:24:26

数据库优化sql

2015-07-17 16:23:14

MySQL优化

2022-01-10 08:50:13

URL前端页面

2013-02-26 09:15:56

小辣椒M1手机通信网络
点赞
收藏

51CTO技术栈公众号