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

概述

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

总结

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

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