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

栏目: 数据库 · 发布时间: 5年前

内容简介:今天发现有个项目应用每次一启动后使用就会导致另外一个应用服务直接崩溃,而这两个应用使用的是同个数据库,经过排查可以发现是报表应用的某个查询功能导致,而咨询开发但却查不出是哪条sql,那就只能靠自己排查了..下面是解决的大致过程。修改my.ini配置,增加参数修改后重启,观察epms-slow.log日志内容。

概述

今天发现有个项目应用每次一启动后使用就会导致另外一个应用服务直接崩溃,而这两个应用使用的是同个数据库,经过排查可以发现是报表应用的某个查询功能导致,而咨询开发但却查不出是哪条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  
 ); 

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

这里可以看到因为走的全扫,每次都扫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   
) 

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

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

5、考虑with改写

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

WITH tmp AS ( SELECT * FROM zt_task ) SELECT  
*   
FROM  
 tmp t1  
 JOIN tmp t2 ON t1.id = t2.parent 

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

好吧,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 

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

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

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

总结

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

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


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

网红经济3.0 自媒体时代的掘金机会

网红经济3.0 自媒体时代的掘金机会

王先明、陈建英 / 当代世界出版社 / 2016-9-1 / 42

深入剖析网红经济的商业模式和整体产业链! 正在崛起的网红经济,打造出多元化的盈利模式,催生了众多新兴的产业投资机会,成为移动互联网时候的资本新风口一起来看看 《网红经济3.0 自媒体时代的掘金机会》 这本书的介绍吧!

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

URL 编码/解码
URL 编码/解码

URL 编码/解码

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器