实战演练（二）：运行20小时的报表SQL优化后秒出

 导 读

作者：松华老师&小鹿

松华老师：《SQL优化课程》讲师

小鹿：《SQL优化课程》嫡传弟子||知数堂助教妹纸||DBA一枚

戳下方，看SQL优化高手的实战演练（一）：

知数堂只分享干货，各精品课程讲授的都是职场实用技能

一、概述

这是我们SQL优化班的一个学员，据说该SQL在生产环境中已经运行了20个小时，快把服务器的磁盘资源耗尽了。这20个小时，我们可爱的学员就是靠着删除一些不重要的文件才能够勉强度过。 
据了解，该SQL为一个月运行一次的跑报表的SQL，主要问题是随着SQL的运行时间越来越长，所需的临时表空间也越来越大，导致磁盘资源用尽。

二、先看慢SQL

• SQL 执行时长 ，目前已经20小时，还在继续执行

1. SELECT

2. COUNT( * ) AS totalNum,

3. sum( CASE WHEN F.ALARM_LEVEL = 1 THEN 1 ELSE 0 END ) AS LEVELS1,

4. sum( CASE WHEN F.ALARM_LEVEL = 2 THEN 1 ELSE 0 END ) AS LEVELS2,

5. sum( CASE WHEN F.ALARM_LEVEL = 3 THEN 1 ELSE 0 END ) AS LEVELS3,

6. sum( CASE WHEN F.DEAL_STATE = 0 THEN 1 ELSE 0 END ) AS DESTS

7. FROM

8. F

9. LEFT JOIN  DC ON DC.ID = F.CONST_ID

10. LEFT JOIN  V ON V.ID = F.VEHICLE_ID

11. LEFT JOIN  AREA ON AREA.ID = V.SYS_DIVISION_ID

12. WHERE

13. DC.ID IS NOT NULL

14. AND V.ID IS NOT NULL

15. AND F.DEAL_STATE = 0

16. AND ALARM_LEVEL IN ( 1, 2, 3 )

17. AND F.VEHICLE_ID IN (

18. SELECT

19. VEHICLE_ID

20. FROM

21. GVLK

22. WHERE

23. GROUP_ID IN ( SELECT GROUP_ID FROM GULK WHERE USER_ID = 'ff8080816091b09c0161f9b825750a9a' )

24. UNION

25. SELECT

26. VEHICLE_ID

27. FROM

28. UVLK

29. WHERE

30. USER_ID = 'ff8080816091b09c0161f9b825750a9a'

31. )

32. AND date( F.ALARM_TIME ) BETWEEN '2000-01-01'

33. AND '2018-08-14'

34. AND AREA.PATH LIKE CONCAT( ( SELECT ARE.PATH FROM  ARE WHERE ARE.ID = '0' ), '%' )

上述SQL的含义：将F左连DC ，V，AREA表的结果进行where过滤，where中存在子查询，并且还有like函数

• 查看执行计划 
  
  该执行计划如何看: 
  id=1的列开始，可以看出 F表，DC表，V表，AREA表都属于id=1的，原因是他们使用了left join，都属于同一个层级。 
  id=2的部分，分成两块，先看靠前的那块， select type 是DEPENDENT SUBQUERY,看table部分是 是一个子查询，注意subquery3，所以具体的子查询是id=3的部分。 靠后的部分是一个简单的SQL。 
  id=3的部分，select type是MATERIALIZED ，table 是 UVLK，发生物化视图的是子查询部分中的子查询部分，物化子查询，一般出现物化视图说明子查询中存在嵌套子查询，且是与SQL主体部分完全无关的表，且子查询中并未使用到索引。 
  id=4部分，是一个DEPENDENT UNION ，将id=2的DEPENDENT SUBQUERY进行union 
  null部分，union result，是将union的结果集，使用了 using tempory。 
  id=5部分，是一个子查询，是concat函数的主体部分

• 问题定位 
  执行计划中可以看出,GVLK, GULK, UVLK 部分均使用了DEPEND SUBQUERY,是性能的瓶颈，DEPEND SUBQUERY是依赖于SQL的主体部分，它的执行次数最大可能和SQL主体部分结果的行数(448612行)一样多，同时，,GVLK, GULK,UVLK几张表的type都为all，并为使用到索引，MySQL中关联未走索引的表，只有nested loop join（将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。），多表 join的结果就是，临时结果集会非常非常的大。

三、SQL优化过程

• 验证驱动表F的条件过滤性

1. SELECT

2. COUNT( * ) AS totalNum

3. FROM

4.  F  

5. WHERE F.DEAL_STATE = 0

6. AND ALARM_LEVEL IN ( 1, 2, 3 )  

7. AND F.ALARM_TIME  BETWEEN '2000-01-01' AND '2018-08-14'  

上面的sql 运行10s 结果集为393653条数据，说明where 条件中的过滤条件的选择率不是很好 不适合创建索引。

• 验证子查询的过滤性

1.  SELECT

2.  VEHICLE_ID

3.  FROM

4.  GVLK

5.  WHERE GROUP_ID IN ( SELECT GROUP_ID FROM GULK WHERE USER_ID = 'ff8080816091b09c0161f9b825750a9a' )

6. UNION

7.  SELECT

8.  VEHICLE_ID

9.  FROM

10.  UVLK

11.  WHERE  USER_ID = 'ff8080816091b09c0161f9b825750a9a'

上面的SQL 运行了0.5s 结果为1200条 。in里的结果速度快 结果集很小 ，F表 就该结果进行in操作，也会有大幅度的过滤。

• DEPEND SUBQUERY 改写

1. SELECT

2. COUNT( * ) AS totalNum

3. FROM

4. F

5. join (

6. SELECT

7. VEHICLE_ID

8. FROM

9. GVLK

10. WHERE

11. GROUP_ID IN ( SELECT GROUP_ID FROM GULK  WHERE USER_ID = 'ff8080816091b09c0161f9b825750a9a' )

12. UNION

13. SELECT

14. VEHICLE_ID

15. FROM

16. UVLK

17. WHERE

18. USER_ID = 'ff8080816091b09c0161f9b825750a9a'

19. ) s

20. WHERE F.DEAL_STATE = 0

21. AND ALARM_LEVEL IN ( 1, 2, 3 )

22. AND F.VEHICLE_ID = s.VEHICLE_ID

23. AND F.ALARM_TIME  BETWEEN '2000-01-01' AND '2018-08-14'

最后运行了如下sql 结果集为 88696 速度为0.5s。采用join的方式替代in的方式，因为 DEPEND SUBQUERY是依赖于SQL的主体部分，执行的次数与被依赖表结果集一致。

四、SQL优化结果

1. explain extended

2. SELECT

3. COUNT( * ) AS totalNum,

4. sum( CASE WHEN F.ALARM_LEVEL = 1 THEN 1 ELSE 0 END ) AS LEVELS1,

5. sum( CASE WHEN F.ALARM_LEVEL = 2 THEN 1 ELSE 0 END ) AS LEVELS2,

6. sum( CASE WHEN F.ALARM_LEVEL = 3 THEN 1 ELSE 0 END ) AS LEVELS3,

7. sum( CASE WHEN F.DEAL_STATE = 0 THEN 1 ELSE 0 END ) AS DESTS

8. FROM

9. F

10. straight_join (

11. SELECT

12. VEHICLE_ID

13. FROM

14. GVLK

15. WHERE

16. GROUP_ID IN ( SELECT GROUP_ID FROM GULK WHERE USER_ID = 'ff8080816091b09c0161f9b825750a9a' )

17. UNION

18. SELECT

19. VEHICLE_ID

20. FROM

21. UVLK

22. WHERE

23. USER_ID = 'ff8080816091b09c0161f9b825750a9a'

24. ) s on F.VEHICLE_ID = s.VEHICLE_ID

25. straight_join DC ON DC.ID = F.CONST_ID

26. straight_join V ON V.ID = F.VEHICLE_ID

27. straight_join AREA ON AREA.ID = V.SYS_DIVISION_ID

28. WHERE

29. DC.ID IS NOT NULL

30. AND V.ID IS NOT NULL

31. AND F.DEAL_STATE = 0

32. AND ALARM_LEVEL IN ( 1, 2, 3 )  

33. AND date( F.ALARM_TIME ) BETWEEN '2000-01-01'

34. AND '2018-08-14'

35. AND AREA.PATH LIKE CONCAT( ( SELECT ARE.PATH FROM ARE WHERE ARE.ID = '0' ), '%' )

优化后的SQL的执行时间几乎是秒出。 
优化的原理已经在优化过程中详细讲解,速度提升上万倍，原因如下：

1、使用join 代替in的方式；

2、修改后的语句可以使用到索引，索引为F.const_id，table为。这里值得一提的是，采用straight_join 代替 了 join，保证了SQL执行顺序一定是按照我们SQL书写的顺序。

四、后记

本文主要在于优化DEPEND SUBQUERY,另外让SQL能够用得上索引，让SQL的速度有着显著的提升。 

希望给大家漫漫的SQL优化之旅，带来不一样的火花。 
感谢大家细心观看，如果大家要学习SQL优化，可以来「知数堂」跟着SQL优化班授课老师-松华老师，你知道的，不知道的，他都能教你。

加入知数堂

挑战40万+年薪！

知数堂

叶金荣与吴炳锡联合打造

领跑IT精英培训

行业资深专家强强联合，倾心定制

MySQL实战/MySQL优化/MongoDB / Python/ SQL优化

数门精品课程

“阅读原文”可获更多正课试听视频

密码：hg3h

紧随技术发展趋势，定期优化培训教案

融入大量生产案例，贴合企业一线需求

社群陪伴学习，一次报名，可学1年

DBA、开发工程师必修课

上千位学员已华丽转身，薪资翻番，职位提升

改变已悄然发生，你还在等什么？

加入QQ技术交流群

扫码加入知数堂4群-王者峡谷

（QQ群号：650149401）