Grandma's, why now 51 knots only 3 days, May weather is the most suitable for travel, but we are these bitter it men can not enjoy.
The first came to the company, the project manager to find the development of leader, said our site page is very slow, let him troubleshoot the reason.
One hears the website slow, the page slow elder brother comes to the spirit, elder brother's old line is solves "the slow" question.
Development leader very depressed said, we have added memcache, 20 minutes cache, why did or slow,
So brother asked, that Web page ran which SQL? Can you grab it and let me see it? Development leader decisively to grasp the SQL out.
After the investigation, we found that a SQL did run slowly. The SQL is as follows
Copy Code code as follows:
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;
The implementation plan is as follows
Copy Code code as follows:
Execution plan
----------------------------------------------------------
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)
Can you see the problem with this SQL? The reason this SQL runs slow is because the developer wrote the SQL condition incorrectly.
The right kind of writing should be the following
Copy Code code as follows:
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;
The implementation plan is as follows
Copy Code code as follows:
Execution plan
----------------------------------------------------------
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 has to run for at least 5 seconds and now 0.1 seconds to produce results.
Children's shoes, SQL outside the connection, pay attention to the location of the filter conditions, remember!!!
There are SQL needs to optimize the Welcome to join QQ Group 220761024 applications annotated from CSDN
