一来到公司,项目经理就找到开发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) |