慢sql优化记录1

慢sql为：

select count(*) from t_wf_process p left join t_wf_core_dofile dofile on p.wf_instance_uid = dofile.instanceid join zwkj_department d on p.userdeptid = d.department_guid ,t_wf_core_item i,wf_node n where (p.IS_DUPLICATE != 'true' or p.IS_DUPLICATE is null) and p.is_show = 1 and (p.is_back is null or (p.is_back != '2' and p.is_back != '4')) and i.id = p.wf_item_uid and p.wf_node_uid=n.wfn_id and p.user_uid = $1 and p.is_over= 'OVER' and p.finsh_time in (select max(p2.finsh_time) from t_wf_process p2 where p2.process_title is not null and (p2.is_back is null or (p2.is_back != '2' and p2.is_back != '4')) and p2.user_uid = $2 and p2.is_over = 'OVER' and p2.is_show = 1 and p2.finsh_time is not null group by p2.wf_instance_uid) and decode((select count(1) from t_wf_core_end_instanceid t where p.wf_instance_uid = t.instanceId), 0, 0, 1) = 1 order by p.apply_time desc;

优化1：可以看出子查询和负查询都用了同样的查询条件来过滤大表t_wf_process（约600w记录，15G），这样不可避免会多次访问大表，可以采用CTE临时表的方式减少对大表的访问（finish_time子查询有is not null过滤，父查询因为限制了finish_time在子查询中，所以也可以有这个过滤条件）

with t1 as (select wf_instance_uid,userdeptid,IS_DUPLICATE,wf_item_uid , wf_node_uid,finsh_time,apply_time,process_title from t_wf_process where user_uid = $1 and is_show = 1 AND (is_back IS NULL OR (is_back != '2' AND is_back != '4')) AND is_over = 'OVER' and finsh_time IS NOT NULL)

优化2： decode((select count(1) from t_wf_core_end_instanceid t where p.wf_instance_uid = t.instanceId), 0, 0, 1) = 1

这种decode包含两表的关联，较耗cpu，可以改成exists方式（简单的表达式具有最优的性能）

exists (select 1 from t_wf_core_end_instanceid t where p.wf_instance_uid = t.instanceId)

优化3：条件里p.finsh_time in (select max(p2.finsh_time) from t_wf_process p2 where p2.process_title is not null ...)这里还有子查询，可以使用any(array())代替p.finsh_time =any(array (select max(p2.finsh_time) from t_wf_process p2 where p2.process_title is not null ...))

优化4：同时还可以给t_wf_process的user_uid、is_show、is_back、is_over、finish_time加上联合索引

create index idx_process_multiple on t_wf_process(user_uid,is_show,is_back,is_over,finsh_time);

优化

with t1 as (select wf_instance_uid,userdeptid,IS_DUPLICATE,wf_item_uid , wf_node_uid,finsh_time,apply_time,process_title from t_wf_processwhere user_uid = $1 and is_show = 1 AND (is_back IS NULL OR (is_back != '2' AND is_back != '4')) AND is_over = 'OVER' and finsh_time IS NOT NULL) SELECT count (*) FROM t1 p LEFT JOIN t_wf_core_dofile dofile ON p.wf_instance_uid = dofile.instanceid JOIN zwkj_department d ON p.userdeptid = d.department_guid, t_wf_core_item i, wf_node n WHERE (p.IS_DUPLICATE != 'true' OR p.IS_DUPLICATE IS NULL) AND i.id = p.wf_item_uid AND p.wf_node_uid = n.wfn_id AND p.finsh_time = any(array( SELECT max (p2.finsh_time) FROM t1 p2 WHERE p2.process_title IS NOT NULL GROUP BY p2.wf_instance_uid) ) and exists (select 1 from t_wf_core_end_instanceid t where p.wf_instance_uid = t.instanceId) ;

之前的索引看看使用情况要不要删除

优化前的执行计划cost较大，但执行时间较少

优化后的执行计划cost很小，执行时间很长，主要耗时在cte表的访问

去掉cte表再试下：

select count(*) from t_wf_process p left join t_wf_core_dofile dofile on p.wf_instance_uid = dofile.instanceid join zwkj_department d on p.userdeptid = d.department_guid ,t_wf_core_item i,wf_node n where (p.IS_DUPLICATE != 'true' or p.IS_DUPLICATE is null) and p.is_show = 1 and (p.is_back is null or (p.is_back != '2' and p.is_back != '4')) and i.id = p.wf_item_uid and p.wf_node_uid=n.wfn_id and p.user_uid = $1 and p.is_over= 'OVER' and p.finsh_time = any(array(select max(p2.finsh_time) from t_wf_process p2 where p2.process_title is not null and (p2.is_back is null or (p2.is_back != '2' and p2.is_back != '4')) and p2.user_uid = $2 and p2.is_over = 'OVER' and p2.is_show = 1 and p2.finsh_time is not null group by p2.wf_instance_uid) ) and exists (select 1 from t_wf_core_end_instanceid t where p.wf_instance_uid = t.instanceId);

这时候cost和执行时间都较优，可以选择优化2、3、4，优化子查询和加索引的方法