实战演练（二）：运行20小时的报表SQL优化后秒出id=1的列开始，可以看出 F表，DC表，V表，AREA表都属于id=

该执行计划如何看: 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的条件过滤性

SELECT
COUNT( * ) AS totalNum
FROM
F
WHERE F.DEAL_STATE = 0
AND ALARM_LEVEL IN ( 1, 2, 3 )
AND F.ALARM_TIME BETWEEN '2000-01-01' AND '2018-08-14'

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

● 验证子查询的过滤性

SELECT
VEHICLE_ID
FROM
GVLK
WHERE GROUP_ID IN ( SELECT GROUP_ID FROM GULK WHERE USER_ID = 'ff8080816091b09c0161f9b825750a9a' )
UNION
SELECT
VEHICLE_ID
FROM
UVLK
WHERE USER_ID = 'ff8080816091b09c0161f9b825750a9a'

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

● DEPEND SUBQUERY 改写

SELECT
COUNT( * ) AS totalNum
FROM
F
join (
SELECT
VEHICLE_ID
FROM
GVLK
WHERE
GROUP_ID IN ( SELECT GROUP_ID FROM GULK WHERE USER_ID = 'ff8080816091b09c0161f9b825750a9a' )
UNION
SELECT
VEHICLE_ID
FROM
UVLK
WHERE
USER_ID = 'ff8080816091b09c0161f9b825750a9a'
) s
WHERE F.DEAL_STATE = 0
AND ALARM_LEVEL IN ( 1, 2, 3 )
AND F.VEHICLE_ID = s.VEHICLE_ID
AND F.ALARM_TIME BETWEEN '2000-01-01' AND '2018-08-14'

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

四、SQL优化结果

explain extended
SELECT
COUNT( * ) AS totalNum,
sum( CASE WHEN F.ALARM_LEVEL = 1 THEN 1 ELSE 0 END ) AS LEVELS1,
sum( CASE WHEN F.ALARM_LEVEL = 2 THEN 1 ELSE 0 END ) AS LEVELS2,
sum( CASE WHEN F.ALARM_LEVEL = 3 THEN 1 ELSE 0 END ) AS LEVELS3,
sum( CASE WHEN F.DEAL_STATE = 0 THEN 1 ELSE 0 END ) AS DESTS
FROM
F
straight_join (
SELECT
VEHICLE_ID
FROM
GVLK
WHERE
GROUP_ID IN ( SELECT GROUP_ID FROM GULK WHERE USER_ID = 'ff8080816091b09c0161f9b825750a9a' )
UNION
SELECT
VEHICLE_ID
FROM
UVLK
WHERE
USER_ID = 'ff8080816091b09c0161f9b825750a9a'
) s on F.VEHICLE_ID = s.VEHICLE_ID
straight_join DC ON DC.ID = F.CONST_ID
straight_join V ON V.ID = F.VEHICLE_ID
straight_join AREA ON AREA.ID = V.SYS_DIVISION_ID
WHERE
DC.ID IS NOT NULL
AND V.ID IS NOT NULL
AND F.DEAL_STATE = 0
AND ALARM_LEVEL IN ( 1, 2, 3 )
AND date( F.ALARM_TIME ) BETWEEN '2000-01-01'
AND '2018-08-14'
AND AREA.PATH LIKE CONCAT( ( SELECT ARE.PATH FROM ARE WHERE ARE.ID = '0' ), '%' )

3777b68c3cdb748f48159f87763ef9e6aaf032ad

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

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

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

四、后记

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

原文发布时间为：2018-10-31

本文作者：郑松华老师&小鹿

本文来自云栖社区合作伙伴“老叶茶馆”，了解相关信息可以关注“老叶茶馆”。