一次sql語句優化的反思

5.1節前現網反饋資料庫效能很低下，statspack報告發回來後top 5

的兩個突出的sql語句如下：

1、SELECT COUNT(*)
FROM (SELECT t0.id,
t0.parentid,
t0.path,
t0.TYPE,
t5.NAME,
t5.catename,
t5.icpcode,
t5.icpservid,
t5.spname,
t5.auditionurl,
t5.downloadurl,
t5.createdate,
t5.expire,
t5.coloringprice,
t5.singer,
t5.special,
t5.editontype,
t5.contenttag,
t5.audiotype,
t5.gameinfourl,
t5.imagename,
t5.servdesc,
t5.chargetype,
t5.price,
t5.accessmodeid,
t5.paymode,
t5.downloadtimes,
t5.nameletter
FROM t_r_base t0, v_gcontent t5
WHERE t0.TYPE LIKE '
AND t5.id = t0.id
AND t0.TYPE = '
AND EXISTS (SELECT 1
FROM t_r_base opt0, t_r_reference t4
WHERE opt0.TYPE LIKE '
AND t4.id = opt0.id
AND opt0.path LIKE '
AND t0.id = refnodeid)
ORDER BY t5.downloadtimes DESC);

2、SELECT t0.id,
t0.parentid,
t0.path,
t0.TYPE,
t5.NAME,
t5.catename,
t5.icpcode,
t5.icpservid,
t5.spname,
t5.auditionurl,
t5.downloadurl,
t5.createdate,
t5.expire,
t5.coloringprice,
t5.singer,
t5.special,
t5.editontype,
t5.contenttag,
t5.audiotype,
t5.gameinfourl,
t5.imagename,
t5.servdesc,
t5.chargetype,
t5.price,
t5.accessmodeid,
t5.paymode,
t5.downloadtimes,
t5.nameletter
FROM t_r_base t0, v_gcontent t5
WHERE t0.TYPE LIKE '
AND t5.id = t0.id
AND t0.TYPE = '
AND EXISTS (SELECT 1
FROM t_r_base opt0, t_r_reference t4
WHERE opt0.TYPE LIKE '
AND t4.id = opt0.id
AND opt0.path LIKE '
AND t0.id = refnodeid)
ORDER BY t5.downloadtimes DESC;

其中v_gcontent是個龐大的普通檢視。

該語句發生在使用者每次高階搜尋時執行，對應的分析結果如下：

CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------

225,840,266 95 2,377,266.0 11.1 0.00 6939.53 2689214047
Module: JDBC Thin Client
select t0.id,t0.parentID,t0.path,t0.type,t5.name,t5.cateName,t5.
icpCode,t5.icpServId,t5.spName,t5.auditionUrl,t5.downLoadUrl,t5.
createDate,t5.expire,t5.coloringPrice,t5.singer,t5.special,t5.ed
itonType,t5.contentTag,t5.audioType,t5.gameInfoUrl,t5.imageName,
t5.SERVDESC,t5.CHARGETYPE,t5.PRICE,t5.ACCESSMODEID,t5.PAYMODE,t5

共2,377,266.0個塊，總共拿了2,377,266次，包括重複的塊讀，可見資料是驚人的。

下面是具體的優化細節：

1、在求count時沒必要order by，導致排序的記錄多時產生大量的buffer和IO；

2、將FROM後語句改寫為表關聯，不用EXISTS；

3、t0.TYPE LIKE '是完全多餘的條件；

4、將v_gcontent這個龐大的普通檢視改為實體化檢視並建立索引。

經過上述的優化問題得到解決！

由此給廣大的資料庫程式開發者和系統設計架構者一些建議，如下：

1、少用巢狀，層次複雜，功能冗餘的sql查詢語句，多用簡單的經過分解或中間轉化的sql查詢語句；

2、少用子查詢（in，exists，not in，not exists等）多用表關聯，充分利用表與表之間的索引；

3、普通檢視就是一個子查詢，每次select檢視都是一次子查詢；而實體化檢視就不一樣，它本身就是一張表，有自己的物理儲存和索引，效能上比普通檢視高。在報表，查詢等功能上建議多用；

4、從應用角度上出發，對於一些門戶級平臺級的系統，併發訪問高，強烈不建議每一次訪問都去跟資料庫互動，這樣會把資料庫給搞塌掉，建議採用應用級上的資料快取或本地資料檔案形式來提高效能。

[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/38542/viewspace-913980/，如需轉載，請註明出處，否則將追究法律責任。