SQL有外連接的時(shí)候注意過(guò)濾條件位置否則會(huì)導(dǎo)致網(wǎng)頁(yè)慢
更新時(shí)間：2013年05月03日 17:13:33 作者：
這個(gè)SQL之所以跑得慢是因?yàn)殚_(kāi)發(fā)人員把SQL的條件寫(xiě)錯(cuò)位置了
正確的寫(xiě)法應(yīng)該是下面這樣的，感興趣的朋友可以參考下
奶奶的，為啥現(xiàn)在五一節(jié)只放3天，5月的天氣最適合出游了，不過(guò)俺們這些苦逼的IT男是沒(méi)法享受了。
一來(lái)到公司，項(xiàng)目經(jīng)理就找到開(kāi)發(fā)leader，說(shuō)我們網(wǎng)站 頁(yè)面很慢，讓他排查原因。
一聽(tīng)說(shuō) 網(wǎng)站慢，頁(yè)面慢哥就來(lái)精神了，哥的老本行就是 解決“慢”的問(wèn)題。
開(kāi)發(fā)leader 很郁悶的說(shuō)，我們已經(jīng)加了 memcache了，20分鐘 cache一次，咋個(gè)還是慢呢，
于是哥就問(wèn)，那個(gè)網(wǎng)頁(yè)跑了哪些SQL？ 能抓出來(lái)讓我看看嗎？ 開(kāi)發(fā)Leader 果斷的把SQL 抓了出來(lái)。
經(jīng)過(guò)排查，我們發(fā)現(xiàn)了一個(gè)SQL確實(shí)跑得慢。該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;
執(zhí)行計(jì)劃如下
執(zhí)行計(jì)劃
----------------------------------------------------------
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)
大家能發(fā)現(xiàn)這個(gè)SQL 的問(wèn)題嗎？ 這個(gè) SQL 之所以跑得慢是因?yàn)殚_(kāi)發(fā)人員把SQL的條件寫(xiě)錯(cuò)位置了
正確的寫(xiě)法應(yīng)該是 下面這樣的
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;
執(zhí)行計(jì)劃如下
執(zhí)行計(jì)劃
----------------------------------------------------------
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秒以上，現(xiàn)在0.1秒能出結(jié)果。
各位童鞋，SQL 有外連接的時(shí)候，要注意過(guò)濾條件的位置，記住啦！?。?
有SQL 需要優(yōu)化的 歡迎加入 QQ 群 220761024 申請(qǐng)注明 來(lái)自CSDN
一來(lái)到公司，項(xiàng)目經(jīng)理就找到開(kāi)發(fā)leader，說(shuō)我們網(wǎng)站 頁(yè)面很慢，讓他排查原因。
一聽(tīng)說(shuō) 網(wǎng)站慢，頁(yè)面慢哥就來(lái)精神了，哥的老本行就是 解決“慢”的問(wèn)題。
開(kāi)發(fā)leader 很郁悶的說(shuō)，我們已經(jīng)加了 memcache了，20分鐘 cache一次，咋個(gè)還是慢呢，
于是哥就問(wèn)，那個(gè)網(wǎng)頁(yè)跑了哪些SQL？ 能抓出來(lái)讓我看看嗎？ 開(kāi)發(fā)Leader 果斷的把SQL 抓了出來(lái)。
經(jīng)過(guò)排查，我們發(fā)現(xiàn)了一個(gè)SQL確實(shí)跑得慢。該SQL 如下
復(fù)制代碼 代碼如下:
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;
執(zhí)行計(jì)劃如下
復(fù)制代碼 代碼如下:
執(zhí)行計(jì)劃
----------------------------------------------------------
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)
大家能發(fā)現(xiàn)這個(gè)SQL 的問(wèn)題嗎？ 這個(gè) SQL 之所以跑得慢是因?yàn)殚_(kāi)發(fā)人員把SQL的條件寫(xiě)錯(cuò)位置了
正確的寫(xiě)法應(yīng)該是 下面這樣的
復(fù)制代碼 代碼如下:
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;
執(zhí)行計(jì)劃如下
復(fù)制代碼 代碼如下:
執(zhí)行計(jì)劃
----------------------------------------------------------
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秒以上，現(xiàn)在0.1秒能出結(jié)果。
各位童鞋，SQL 有外連接的時(shí)候，要注意過(guò)濾條件的位置，記住啦！?。?
有SQL 需要優(yōu)化的 歡迎加入 QQ 群 220761024 申請(qǐng)注明 來(lái)自CSDN
相關(guān)文章
本節(jié)主要介紹了sql取代游標(biāo)該如何寫(xiě)，需要的朋友可以參考下2014-07-07
SQL Server 數(shù)據(jù)庫(kù)管理常用的SQL和T-SQL語(yǔ)句
SQL Server 數(shù)據(jù)庫(kù)管理常用的SQL和T-SQL語(yǔ)句...2006-08-08
有些時(shí)候你想讓SQL Server 返回一個(gè)聚集結(jié)果集合，而不是一個(gè)詳細(xì)的結(jié)果集。SQL Server的GROUPBY子句，為你提供了一種聚合SQL Server數(shù)據(jù)的方式。GROUPBY子句允許你在一列或多列數(shù)據(jù)甚至是表達(dá)式上進(jìn)行分組操作，在這篇文章中，我將討論如何使用GROUPBY子句來(lái)匯總數(shù)據(jù)。2015-09-09
SQLServer 2000定時(shí)執(zhí)行SQL語(yǔ)句
如果你維護(hù)的數(shù)據(jù)表一方面依靠其他模塊的數(shù)據(jù)表，另一方面又對(duì)其他模塊進(jìn)行數(shù)據(jù)支持，并且這些表都是實(shí)時(shí)更新的，那么就不能通過(guò)頁(yè)面調(diào)用SQL語(yǔ)句等方式來(lái)更新數(shù)據(jù)表，只能讓數(shù)據(jù)表定時(shí)自動(dòng)更新。2011-04-04
SQL SERVER的數(shù)據(jù)類(lèi)型...
2006-07-07 
本文主要介紹了SQL Server里書(shū)簽查找的性能傷害，具有很好的參考價(jià)值。下面跟著小編一起來(lái)看下吧
2017-03-03 
需要管理sql server的朋友，需要掌握的，大家可以收藏下，方便以后使用
2008-09-09 
SQLSERVER對(duì)加密的存儲(chǔ)過(guò)程、視圖、觸發(fā)器進(jìn)行解密(推薦)
這篇文章主要介紹了SQLSERVER對(duì)加密的存儲(chǔ)過(guò)程、視圖、觸發(fā)器進(jìn)行解密,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
2020-01-01 
今天下午忙于將access數(shù)據(jù)庫(kù)中的交叉表轉(zhuǎn)到SQL server數(shù)據(jù)庫(kù)中，在SQL server中，不支持transfrom方法，查找相關(guān)資料，找到使用case方法進(jìn)行轉(zhuǎn)換
2011-12-12