SQL有外连接的时候注意过滤条件位置否则会导致网页慢

时间:2021-11-21 21:17:03

奶奶的,为啥现在五一节只放3天,5月的天气最适合出游了,不过俺们这些苦逼的IT男是没法享受了。 
一来到公司,项目经理就找到开发leader,说我们网站 页面很慢,让他排查原因。 
一听说 网站慢,页面慢哥就来精神了,哥的老本行就是 解决“慢”的问题。 
开发leader 很郁闷的说,我们已经加了 memcache了,20分钟 cache一次,咋个还是慢呢, 
于是哥就问,那个网页跑了哪些SQL? 能抓出来让我看看吗? 开发Leader 果断的把SQL 抓了出来。 
经过排查,我们发现了一个SQL确实跑得慢。该SQL 如下 

复制代码代码如下:


select * 
from (select u.NAME UniversityName, 
u.id UniversityId, 
count(a.SIGNUPNUMBER) playercnt 
from T_B_UNIVERSITY u 
left join T_D_EDUCATION e 
on e.UNIVERSITY_ID = u.id 
left join T_D_VIDEO_PLAYER a 
on a.USER_ID = e.user_id 
and e.ISDEFAULT = 1 
and e.ISVALID = 1 
and a.AUDITSTATUS = 1 
and a.ISVALID = 1 
left join T_D_USER c 
on a.USER_ID = c.id 
and c.ISVALID = 1 
where u.REGION_CODE like '43%' 
group by u.NAME, u.id) 
order by playercnt desc; 


执行计划如下 

复制代码代码如下:


执行计划 
---------------------------------------------------------- 
Plan hash value: 3938743742 
-------------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
-------------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 142 | 10366 | 170 (3)| 00:00:03 | 
| 1 | SORT ORDER BY | | 142 | 10366 | 170 (3)| 00:00:03 | 
| 2 | HASH GROUP BY | | 142 | 10366 | 170 (3)| 00:00:03 | 
|* 3 | HASH JOIN RIGHT OUTER| | 672 | 49056 | 168 (2)| 00:00:03 | 
|* 4 | TABLE ACCESS FULL | T_D_USER | 690 | 5520 | 5 (0)| 00:00:01 | 
| 5 | NESTED LOOPS OUTER | | 672 | 43680 | 162 (1)| 00:00:02 | 
|* 6 | HASH JOIN OUTER | | 672 | 37632 | 14 (8)| 00:00:01 | 
|* 7 | TABLE ACCESS FULL | T_B_UNIVERSITY | 50 | 2050 | 8 (0)| 00:00:01 | 
| 8 | TABLE ACCESS FULL | T_D_EDUCATION | 672 | 10080 | 5 (0)| 00:00:01 | 
| 9 | VIEW | | 1 | 9 | 0 (0)| 00:00:01 | 
|* 10 | FILTER | | | | | | 
|* 11 | TABLE ACCESS FULL| T_D_VIDEO_PLAYER | 1 | 15 | 3 (0)| 00:00:01 | 
-------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
3 - access("A"."USER_ID"="C"."ID"(+)) 
4 - filter("C"."ISVALID"(+)=1) 
6 - access("E"."UNIVERSITY_ID"(+)="U"."ID") 
7 - filter("U"."REGION_CODE" LIKE '43%') 
10 - filter("E"."ISVALID"=1 AND "E"."ISDEFAULT"=1) 
11 - filter("A"."USER_ID"="E"."USER_ID" AND "A"."AUDITSTATUS"=1 AND 
"A"."ISVALID"=1) 


大家能发现这个SQL 的问题吗? 这个 SQL 之所以跑得慢是因为开发人员把SQL的条件写错位置了 
正确的写法应该是 下面这样的 

复制代码代码如下:


select * 
from (select u.NAME UniversityName, 
u.id UniversityId, 
count(a.SIGNUPNUMBER) playercnt 
from T_B_UNIVERSITY u 
left join T_D_EDUCATION e 
on e.UNIVERSITY_ID = u.id 
and e.ISDEFAULT = 1 
and e.ISVALID = 1 
left join T_D_VIDEO_PLAYER a 
on a.USER_ID = e.user_id 
and a.AUDITSTATUS = 1 
and a.ISVALID = 1 
left join T_D_USER c 
on a.USER_ID = c.id 
and c.ISVALID = 1 
where u.REGION_CODE like '43%' 
group by u.NAME, u.id) 
order by playercnt desc; 


执行计划如下 

复制代码代码如下:


执行计划 
---------------------------------------------------------- 
Plan hash value: 2738827747 
--------------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
--------------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 142 | 11218 | 25 (16)| 00:00:01 | 
| 1 | SORT ORDER BY | | 142 | 11218 | 25 (16)| 00:00:01 | 
| 2 | HASH GROUP BY | | 142 | 11218 | 25 (16)| 00:00:01 | 
|* 3 | HASH JOIN RIGHT OUTER | | 301 | 23779 | 23 (9)| 00:00:01 | 
|* 4 | TABLE ACCESS FULL | T_D_USER | 690 | 5520 | 5 (0)| 00:00:01 | 
|* 5 | HASH JOIN RIGHT OUTER| | 301 | 21371 | 17 (6)| 00:00:01 | 
|* 6 | TABLE ACCESS FULL | T_D_VIDEO_PLAYER | 78 | 1170 | 3 (0)| 00:00:01 | 
|* 7 | HASH JOIN OUTER | | 301 | 16856 | 14 (8)| 00:00:01 | 
|* 8 | TABLE ACCESS FULL | T_B_UNIVERSITY | 50 | 2050 | 8 (0)| 00:00:01 | 
|* 9 | TABLE ACCESS FULL | T_D_EDUCATION | 301 | 4515 | 5 (0)| 00:00:01 | 
--------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
3 - access("A"."USER_ID"="C"."ID"(+)) 
4 - filter("C"."ISVALID"(+)=1) 
5 - access("A"."USER_ID"(+)="E"."USER_ID") 
6 - filter("A"."AUDITSTATUS"(+)=1 AND "A"."ISVALID"(+)=1) 
7 - access("E"."UNIVERSITY_ID"(+)="U"."ID") 
8 - filter("U"."REGION_CODE" LIKE '43%') 
9 - filter("E"."ISDEFAULT"(+)=1 AND "E"."ISVALID"(+)=1) 


之前SQL要跑至少5秒以上,现在0.1秒能出结果。 
各位童鞋,SQL 有外连接的时候,要注意过滤条件的位置,记住啦!!! 
有SQL 需要优化的 欢迎加入 QQ 群 220761024 申请注明 来自CSDN